13-用 Python 读写 Excel 文件

清疚 2022-12-11 09:24 248阅读 0赞

在以前，商业分析对应的英文单词是Business Analysis，大家用的分析工具是Excel，后来数据量大了，Excel应付不过来了（Excel最大支持行数为1048576行），人们开始转向python和R这样的分析工具了

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th><a href="https://xlsxwriter.readthedocs.io/" rel="nofollow">XlsxWriter</a></th> 
   <th><a href="http://www.python-excel.org/" rel="nofollow">xlrd&amp;xlwt</a></th> 
   <th><a href="http://openpyxl.readthedocs.org/" rel="nofollow">OpenPyXL</a></th> 
   <th><a href="http://msdn.microsoft.com/en-us/library/fp179694.aspx" rel="nofollow">Microsoft Excel API</a></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>介绍</td> 
   <td>可以创建 Excel 2007 或更高版本的 XLSX 文件</td> 
   <td>即 <a href="http://www.python-excel.org/" rel="nofollow">python-excel</a>，含 <a href="https://pypi.python.org/pypi/xlrd" rel="nofollow">xlrd</a>、<a href="https://pypi.python.org/pypi/xlwt" rel="nofollow">xlwt</a> 和 <a href="https://pypi.python.org/pypi/xlutils" rel="nofollow">xlutils</a> 三大模块，分别提供读、写和其他功能</td> 
   <td>可以读写 Excel 2007 XLSX 和 XLSM 文件</td> 
   <td>直接通过 COM 组件与Microsoft Excel 进程通信，调用其各种功能实现对 Excel 文件的操作</td> 
  </tr> 
  <tr> 
   <td>读</td> 
   <td>❌</td> 
   <td>✅</td> 
   <td>✅</td> 
   <td>✅</td> 
  </tr> 
  <tr> 
   <td>写</td> 
   <td>✅</td> 
   <td>✅</td> 
   <td>✅</td> 
   <td>✅</td> 
  </tr> 
  <tr> 
   <td>修改</td> 
   <td>❌</td> 
   <td>❌</td> 
   <td>⚠️</td> 
   <td>✅</td> 
  </tr> 
  <tr> 
   <td>.xls</td> 
   <td>❌</td> 
   <td>✅</td> 
   <td>❌</td> 
   <td>✅</td> 
  </tr> 
  <tr> 
   <td>.xlsx</td> 
   <td>✅</td> 
   <td>⚠️</td> 
   <td>✅</td> 
   <td>✅</td> 
  </tr> 
  <tr> 
   <td>大文件</td> 
   <td>✅</td> 
   <td>❌</td> 
   <td>✅</td> 
   <td>❌</td> 
  </tr> 
  <tr> 
   <td>功能</td> 
   <td>强</td> 
   <td>弱</td> 
   <td>一般</td> 
   <td>超强</td> 
  </tr> 
  <tr> 
   <td>速度</td> 
   <td>快</td> 
   <td>快</td> 
   <td>快</td> 
   <td>超慢</td> 
  </tr> 
  <tr> 
   <td>系统</td> 
   <td>无限制</td> 
   <td>无限制</td> 
   <td>无限制</td> 
   <td>Windows + Excel</td> 
  </tr> 
  <tr> 
   <td>使用场景</td> 
   <td>要创建 XLSX 文件<br> 不需要读取已有文件<br> <br> 需要实现比较复杂的功能<br> 数据量可能会很大<br> 需要跨平台</td> 
   <td>要读取 XLS 或 XLSX 文件<br> 要生成 XLS 文件<br>需要的功能不太复杂<br> 需要跨平台</td> 
   <td>要处理 XLSX 文件<br> 需要修改已有文件，或者在写入过程中需要不断修改<br> 需要的功能比较复杂<br> 数据量可能会很大<br> 需要跨平台</td> 
   <td>需要处理各种文件格式<br> 需要用到特别复杂的功能<br> 在修改文件时，不希望对原有信息造成任何意外破坏<br> 数据量很小，或者愿意等待<br> 仅在 Windows 中使用</td> 
  </tr> 
 </tbody> 
</table>

## [XlsxWriter][] ##

Xlsx是python用来构造xlsx文件的模块，可以向excel2007+中写text，numbers，formulas 公式以及hyperlinks超链接。

可以完成xlsx文件的自动化构造，包括：合并单元格，制作excel图表等功能：

#### 1.优点 ####

一、功能比较强

相对而言，这是除Excel自身之外功能最强的工具了。比如我就用到了它提供的：字体设置、前景色背景色、border设置、视图缩放（zoom）、单元格合并、autofilter、freeze panes、公式、data validation、单元格注释、行高和列宽设置等等。

二、支持大文件写入

如果数据量非常大，可以启用constant memory模式，这是一种顺序写入模式，得到一行数据就立刻写入一行，而不会把所有的数据都保持在内存中。

#### 2.缺点 ####

一、不支持读取和修改

作者并没有打算做一个XlsxReader来提供读取操作。不能读取，也就无从修改了。它只能用来创建新的文件。我是利用xlrd把需要的信息读入后，用XlsxWriter创建全新的文件。

另外，即使是创建到一半Excel文件，也是无法读取已经创建出来的内容的（信息应该在，但是并没有相应的接口）。因为它的主要方法是write而不是set。当你在某个单元格写入数据后，除非你自己保存了相关的内容，否则还是没有办法读出已经写入的信息。从这个角度看，你无法做到读出->修改->写回，只能是写入->写入->写入。

二、不支持XLS文件

XLS是Office 2013或更早版本所使用的格式，是一种二进制格式的文件。XLSX则是用一系列XML文件组成的（最后的X代表了XML）一个压缩包。如果非要创建低版本的XLS文件，就请移步xlwt吧。

三、暂时不支持透视表（Pivot Table)

#### 3.基本操作方法 ####

*  创建excel文件

import xlsxwriter
    
    f = xlsxwriter.Workbook()    # 创建excel文件

*  创建工作表

worksheet1 = f.add_worksheet('操作日志')  # 括号内为工作表表名

*  添加工作表样式

样式有很多属性，更多具体样式请参考[官方文档 https://xlsxwriter.readthedocs.io][XlsxWriter]。

bold = f.add_format({
            'bold':  True,  # 字体加粗
            'border': 1,  # 单元格边框宽度
            'align': 'left',  # 水平对齐方式
            'valign': 'vcenter',  # 垂直对齐方式
            'fg_color': '#F4B084',  # 单元格背景颜色
            'text_wrap': True,  # 是否自动换行
        })

*  写入单个单元格数据

//row:行， col：列， data:要写入的数据, bold:单元格的样式
    worksheet1.write(row, col, data, bold)

*  写入一整行，一整列

// A1:从A1单元格开始插入数据，按行插入， data:要写入的数据（格式为一个列表), bold:单元格的样式
    worksheet1.write_row(“A1”,data,bold)
    
    // A1:从A1单元格开始插入数据，按列插入， data:要写入的数据（格式为一个列表), bold:单元格的样式
    worksheet1.write_column(“A1”,data,bold)

*  插入图片

// 第一个参数是插入的起始单元格，第二个参数是图片你文件的绝对路径
    worksheet1.insert_image('A1','f:\\1.jpg')

*  写入超链接

worksheet1.write_url(row, col, "internal:%s!A1" % ("要关联的工作表表名"), string="超链接显示的名字")

*  插入图表

workbook.add_chartsheet（type=""）
    
    参数中的type指的是图表类型，图表类型示例如下：
    [area：面积图,bar：条形图,column：直方图,doughnut：环状图,line：折线图,pie：饼状图,scatter：散点图,radar：雷达图,stock:箱线图]

*  获得当前excel文件的所有工作表

workbook.worksheets()

workbook.worksheets()用于获得当前工作簿中的所有工作表，这个函数的存在便利了对于工作表的循环操作，如果你想在当前工作簿的所有工作表的A1单元格中输入一个字符创‘Hello xlsxwriter’，那么这个命令就派上用场了。

*  关闭excel文件

workbook.close()

这个命令是使用xlsxwriter操作Excel的最后一条命令，一定要记得关闭文件。

> 参考 https://www.jianshu.com/nb/32754082

# 案例 #

![案例][085e48abba4f5e441404b3204b3a915f.png]

import xlsxwriter
    
    # 新建excel表
    workbook = xlsxwriter.Workbook('D:/hello.xlsx')
    
    # 新建sheet（sheet的名称为"sheet1"）
    worksheet = workbook.add_worksheet()
    
    # 定义表头内容
    Title = ['实例ID', '实例配置', '实例名', '地域', '安全组', 'EIP']
    
    # 定义标题格式
    merge_format = workbook.add_format({
        'bold': True,
        'border': 1,
        'align': 'center',
        'valign': 'vcenter',
        'fg_color': '#E0FFFF'
    })
    
    # 定义表头格式
    title_format = workbook.add_format({
        'bold': True,
        'border': 1,
        'align': 'center',
        'valign': 'vcenter',
        'fg_color': '#87CEFF'
    })
    
    # 定义内容格式
    data_format = workbook.add_format({
        'border': 1,
        'align': 'center',
        'valign': 'vcenter',
        'fg_color': '#EED8AE'
    })
    
    # 拟数据
    data = [['i-1', '1C1G', 'demo1', '上海', 'sg-1', '172.20.2.10'],
            ['i-2', '1C1G', 'demo2', '郑州', 'sg-2', '172.20.2.11'],
            ['i-3', '1C1G', 'demo3', '北京', 'sg-3', '172.20.2.12']]
    
    # 合并 A1 - F1 单元格作为标题，传入参数：1：合并单元格，2：标题文字，3：标题格式
    worksheet.merge_range('A1:F1', 'ECS信息表', merge_format)
    
    # 设置列宽，指定 A - F 的列宽为 25
    worksheet.set_column('A:F', 25)
    
    # 设置行号，指定行数，高度，A1 为 0，A2 为 1，以此类推
    worksheet.set_row(0, 60)
    
    # write_row ，写行，传入参数：1：行，2：内容，3：格式
    worksheet.write_row('A2', Title, title_format)
    i = 3
    for ECS in data:
        worksheet.write_row('A' + str(i), ECS, data_format)
        i += 1
    
    # 将excel文件保存关闭，如果没有这一行运行代码会报错
    workbook.close()

[在线练习：https://www.520mg.com/it][https_www.520mg.com_it]  
![IT 入门感谢关注][IT]

[XlsxWriter]: https://xlsxwriter.readthedocs.io/
[085e48abba4f5e441404b3204b3a915f.png]: /images/20221123/e4c2b54eb9e0470d8e2576f7d3d82899.png
[https_www.520mg.com_it]: https://www.520mg.com/it
[IT]: /images/20221123/4b4a04de1f674bb8b18d769ff8f0a1d2.png