Python金融大数据分析——第12章 Excel集成笔记

淡淡的烟草味﹌ 2022-05-20 00:46 239阅读 0赞

*  第12章 Excel集成
    
     *  12.1 基本电子表格交互
        
         *  12.1.1 生成工作簿（.xls）
         *  12.1.2 生成工作簿（.xslx）
         *  12.1.3 从工作簿中读取
         *  12.1.4 使用OpenPyxl
         *  12.1.5 使用pandas读写
     *  12.2 用Python编写Excel脚本

# 第12章 Excel集成 #

## 12.1 基本电子表格交互 ##

### 12.1.1 生成工作簿（.xls） ###

import numpy as np
    import pandas as pd
    import xlrd, xlwt
    import xlsxwriter
    
    path = 'E:/Projects/DEMO/Py/MyPyTest/MyPyTest/python_for_finance/data/'
    
    wb = xlwt.Workbook()
    wb.add_sheet('first_sheet', cell_overwrite_ok=True)
    wb.get_active_sheet()
    ws_1 = wb.get_sheet(0)
    ws_2 = wb.add_sheet('second_sheet', cell_overwrite_ok=True)
    
    data = np.arange(1, 65).reshape((8, 8))
    
    # 使用 write 方法提供行/列信息（使用零基索引）
    ws_1.write(1, 0, 100)  # wirte 100 in cell "A1"
    ws_2.write(0, 0, 100)
    
    for c in range(data.shape[0]):
        for r in range(data.shape[1]):
            ws_1.write(r, c, int(data[c, r]))
            ws_2.write(r, c, int(data[r, c]))
    
    wb.save(path + 'workbook.xls')

![xls][]

### 12.1.2 生成工作簿（.xslx） ###

# 创建一个工作簿
    wb = xlsxwriter.Workbook(path + 'workbook.xlsx')
    # 创建工作表对象
    ws_1 = wb.add_worksheet('first_sheet')
    ws_2 = wb.add_worksheet('second_sheet')
    # 将数据写入到工作表对象
    for c in range(data.shape[0]):
        for r in range(data.shape[1]):
            ws_1.write(r, c, data[c, r])
            ws_2.write(c, r, data[r, c])
    
    wb.close()

# 生成图表
    wb = xlsxwriter.Workbook(path + 'chart.xlsx')
    ws = wb.add_worksheet()
    
    # write cumsum of random values in first column
    values = np.random.standard_normal(15).cumsum()
    ws.write_column('A1', values)
    
    # create a new chart object
    chart = wb.add_chart({
       'type': 'line'})
    
    # add a series to the chart
    chart.add_series({
       'values': '=Sheet1!$A$1:$A$15',
                      'marker': {
       'type': 'diamond'}, })
    
    # insert the chart
    ws.insert_chart('C1', chart)
    wb.close()

![生成图表][70]

### 12.1.3 从工作簿中读取 ###

book = xlrd.open_workbook(path + 'workbook.xlsx')
    book.sheet_names()
    # ['first_sheet', 'second_sheet']
    sheet_1 = book.sheet_by_name('first_sheet')
    sheet_2 = book.sheet_by_index(1)
    
    sheet_2.name
    # 'second_sheet'
    
    #  ncols 和 nrows 分别表示包含数据的列数和行数
    sheet_1.ncols, sheet_1.nrows
    # (8, 8)
    
    cl = sheet_1.cell(0, 0)
    cl.value
    # 1.0
    
    cl.ctype
    # 2

Excel单元格类型

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>编号</th> 
   <th>Python类型</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>XL_CELL_EMPTY</td> 
   <td>0</td> 
   <td>空字符串</td> 
  </tr> 
  <tr> 
   <td>XL_CELL_TEXT</td> 
   <td>1</td> 
   <td>Unicoe字符串</td> 
  </tr> 
  <tr> 
   <td>XL_CELL_NUMBER</td> 
   <td>2</td> 
   <td>float</td> 
  </tr> 
  <tr> 
   <td>XL_CELL_DATE</td> 
   <td>3</td> 
   <td>foat</td> 
  </tr> 
  <tr> 
   <td>XL_CELL_BOLEAN</td> 
   <td>4</td> 
   <td>int(l=TRUE,0=FALSE)</td> 
  </tr> 
  <tr> 
   <td>XL_CELL_ERROR</td> 
   <td>5</td> 
   <td>int表示Excel内部编码</td> 
  </tr> 
  <tr> 
   <td>XL_CELL_BLANK</td> 
   <td>6</td> 
   <td>空字符串，仅当formatting_info=True</td> 
  </tr> 
 </tbody> 
</table>

# 可以为row方法提供行号，访问整行
    sheet_2.row(3)
    # [number:4.0,
    #  number:12.0,
    #  number:20.0,
    #  number:28.0,
    #  number:36.0,
    #  number:44.0,
    #  number:52.0,
    #  number:60.0]
    
    # 访问整列
    sheet_2.col(3)
    # [number:25.0,
    #  number:26.0,
    #  number:27.0,
    #  number:28.0,
    #  number:29.0,
    #  number:30.0,
    #  number:31.0,
    #  number:32.0]
    
    # row_values 和 col_values 只提供包含在对应行或者列的值
    sheet_1.col_values(3, start_rowx=3, end_rowx=7)
    # [28.0, 29.0, 30.0, 31.0]
    sheet_1.row_values(3, start_colx=3, end_colx=7)
    # [28.0, 36.0, 44.0, 52.0]
    
    # 读出工作簿对象中的所有数据
    for c in range(sheet_1.ncols):
        for r in range(sheet_1.nrows):
            print('%i' % sheet_1.cell(r, c).value)

### 12.1.4 使用OpenPyxl ###

import openpyxl as oxl
    
    # 生成一个工作簿对象
    wb = oxl.Workbook()
    # 创建一个工作表对象
    ws = wb.create_sheet(index=0, title='oxl_sheet')
    # 将数据写入工作表
    for c in range(data.shape[0]):
        for r in range(data.shape[1]):
            # 索引从1开始
            ws.cell(row=r+1, column=c+1).value = data[c, r]
            # create a Cell object and assigns a value
    
    wb.save(path+'oxl_book.xlsx')

wb = oxl.load_workbook(path + 'oxl_book.xlsx')
    ws = wb.get_active_sheet()
    cell = ws['B4']
    cell.column
    # 'B'
    
    cell.row
    # 4
    
    cell.value
    # 12
    
    ws['B1':'B4']
    # ((<Cell 'oxl_sheet'.B1>,),
    #  (<Cell 'oxl_sheet'.B2>,),
    #  (<Cell 'oxl_sheet'.B3>,),
    #  (<Cell 'oxl_sheet'.B4>,))
    
    for cell in ws['B1':'B4']:
        print(cell[0].value)
    # 9
    # 10
    # 11
    # 12

### 12.1.5 使用pandas读写 ###

df_1 = pd.read_excel(path + 'workbook.xlsx', 'first_sheet', header=None)
    df_2 = pd.read_excel(path + 'workbook.xlsx', 'second_sheet', header=None)
    
    import string
    
    columns = []
    for c in range(data.shape[0]):
        columns.append(string.ascii_uppercase[c])
    
    columns
    # ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H']
    
    df_1.columns = columns
    df_2.columns = columns
    
    df_1
    #    A   B   C   D   E   F   G   H
    # 0  1   9  17  25  33  41  49  57
    # 1  2  10  18  26  34  42  50  58
    # 2  3  11  19  27  35  43  51  59
    # 3  4  12  20  28  36  44  52  60
    # 4  5  13  21  29  37  45  53  61
    # 5  6  14  22  30  38  46  54  62
    # 6  7  15  23  31  39  47  55  63
    # 7  8  16  24  32  40  48  56  64
    
    df_2
    #    A   B   C   D   E   F   G   H
    # 0  1   9  17  25  33  41  49  57
    # 1  2  10  18  26  34  42  50  58
    # 2  3  11  19  27  35  43  51  59
    # 3  4  12  20  28  36  44  52  60
    # 4  5  13  21  29  37  45  53  61
    # 5  6  14  22  30  38  46  54  62
    # 6  7  15  23  31  39  47  55  63
    # 7  8  16  24  32  40  48  56  64
    
    df_1.to_excel(path + 'new_book_1.xlsx', 'my_sheet')
    
    # 将多个DataFrame对象写人单个电子表格文件
    
    wbw = pd.ExcelWriter(path + 'new_book_2.xlsx')
    df_1.to_excel(wbw, 'first_sheet')
    df_2.to_excel(wbw, 'second_sheet')
    
    wbw.save()
    
    # 检查一下， 是否已经在单一电子表格文件中生成了两个工作表：
    wbn = xlrd.open_workbook(path + 'new_book_2.xlsx')
    wbn.sheet_names()
    # ['first_sheet', 'second_sheet']
    
    # 更大量数据的读写
    data = np.random.rand(20, 100000)
    data.nbytes
    # 16000000
    
    df = pd.DataFrame(data)
    %time df.to_excel(path+'data.xlsx','data_sheet')
    # Wall time: 58.6 s
    %time np.save(path+'data',data)
    # Wall time: 8 ms
    
    %time df=pd.read_excel(path+'data.xlsx','data_sheet')
    # Wall time: 6.53 s
    %time data=np.load(path+'data.npy')
    # Wall time: 10 ms

## 12.2 用Python编写Excel脚本 ##

参考 DataNitro 和 xlwings

[xls]: /images/20220520/7f7f9a3d7bb840debba0ab135bc50318.png
[70]: /images/20220520/e4e26a3f61de41869e57331c5c3f205b.png