14-用Python 读写 Excel 文件

墨蓝 2022-12-18 15:55 223阅读 0赞

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

<table> 
 <thead> 
  <tr> 
   <td><br></td> 
   <td>XlsxWriter</td> 
   <td>xlrd&amp;xlwt<sup>[2]</sup></td> 
   <td>OpenPyXL<sup>[3]</sup></td> 
   <td>Microsoft Excel API<sup>[4]</sup></td> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>介绍</td> 
   <td>可以创建 Excel 2007 或更高版本的 XLSX 文件</td> 
   <td>即&nbsp;python-excel，含&nbsp;xlrd、xlwt&nbsp;和&nbsp;xlutils&nbsp;三大模块，分别提供读、写和其他功能</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\[9\] ##

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)**

![format_png][]

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

**•创建excel文件**

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

**•创建工作表**

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

**•添加工作表样式**

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

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

## 案例 ##

![null][]

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()

相关连接

`[1]` XlsxWriter: *https://xlsxwriter.readthedocs.io/*  
`[2]` xlrd&xlwt: *http://www.python-excel.org/*  
`[3]` OpenPyXL: *http://openpyxl.readthedocs.org/*  
`[4]` Microsoft Excel API: *http://msdn.microsoft.com/en-us/library/fp179694.aspx*  
`[5]` python-excel: *http://www.python-excel.org/*  
`[6]` xlrd: *https://pypi.python.org/pypi/xlrd*  
`[7]` xlwt: *https://pypi.python.org/pypi/xlwt*  
`[8]` xlutils: *https://pypi.python.org/pypi/xlutils*  
`[9]` XlsxWriter: *https://xlsxwriter.readthedocs.io/*  
`[10]` 官方文档 https://xlsxwriter.readthedocs.io:   
`[11]` 在线练习：*https://www.520mg.com/it*

《0基础python爬虫系列教程》

<table> 
 <tbody> 
  <tr> 
   <td><a href="http://mp.weixin.qq.com/s?__biz=MzI3MjM5MjE5OA%3D%3D&amp;chksm=eb320125dc458833960e697aa9cf6efd4beda9850e74d05c284f0a9946d98fa93bb12a4324b3&amp;idx=2&amp;mid=2247485516&amp;scene=21&amp;sn=bb0633aa198a9ac55bfa74060155ee74#wechat_redirect" rel="nofollow">01为什么要学习爬虫</a></td> 
   <td><a href="http://mp.weixin.qq.com/s?__biz=MzI3MjM5MjE5OA%3D%3D&amp;chksm=eb320103dc45881592fa51bd62b7d7820b4b506c4a3933a9fad6833d49ce360294f2fe0c6402&amp;idx=2&amp;mid=2247485546&amp;scene=21&amp;sn=9467b8493b4a252d7c39799e58813575#wechat_redirect" rel="nofollow">02-认识python爬虫</a></td> 
  </tr> 
  <tr> 
   <td><a href="http://mp.weixin.qq.com/s?__biz=MzI3MjM5MjE5OA%3D%3D&amp;chksm=eb320103dc4588155293377a2843fc247e3c404f19eb523b875e988e64bc544d98c1b8d7854b&amp;idx=3&amp;mid=2247485546&amp;scene=21&amp;sn=e588e1f9b369323817acb125a7a2fa13#wechat_redirect" rel="nofollow">03-爬虫基本原理</a></td> 
   <td><a href="http://mp.weixin.qq.com/s?__biz=MzI3MjM5MjE5OA%3D%3D&amp;chksm=eb320065dc458973634e1f97683d26b920c3b23dd975286c595065c0e44c3f13a8c74274f7a2&amp;idx=2&amp;mid=2247485708&amp;scene=21&amp;sn=02145cd8d7f0ba50be1ad4f83e60c12b#wechat_redirect" rel="nofollow">04-爬虫利器Fiddler</a></td> 
  </tr> 
  <tr> 
   <td><a href="http://mp.weixin.qq.com/s?__biz=MzI3MjM5MjE5OA%3D%3D&amp;chksm=eb320065dc4589730f0f40ed783f45d1a71974a3037ede904e68fe967752f2def4daf04a8e16&amp;idx=3&amp;mid=2247485708&amp;scene=21&amp;sn=9fe3f40863c13431a80abb6a11bc8f1a#wechat_redirect" rel="nofollow">05-HTTP协议-</a></td> 
   <td><a href="http://mp.weixin.qq.com/s?__biz=MzI3MjM5MjE5OA%3D%3D&amp;chksm=eb320047dc458951e91b4f5ac10d8580840e3fd8aa63ac8c217f8df2d9c643cbda75c22def9f&amp;idx=2&amp;mid=2247485742&amp;scene=21&amp;sn=6be1c823ade912410f109a7f42ffffd3#wechat_redirect" rel="nofollow">06-爬虫库urllib</a></td> 
  </tr> 
  <tr> 
   <td><a href="http://mp.weixin.qq.com/s?__biz=MzI3MjM5MjE5OA%3D%3D&amp;chksm=eb320047dc4589512a5d2062893b86474a037111067d05b57c04355b967c22b04ce3395ea96f&amp;idx=3&amp;mid=2247485742&amp;scene=21&amp;sn=7a54ccaa4836d2a7e5128f233e8c9e5d#wechat_redirect" rel="nofollow">07-TCP3</a></td> 
   <td><a href="http://mp.weixin.qq.com/s?__biz=MzI3MjM5MjE5OA%3D%3D&amp;chksm=eb32003bdc45892d17198bb56448b4600aabd63c84667dccd8fc032647300da843d0ea15be88&amp;idx=3&amp;mid=2247485778&amp;scene=21&amp;sn=f086472aa06577ab8c0567a927721994#wechat_redirect" rel="nofollow">08-页面解析之数据提取</a></td> 
  </tr> 
  <tr> 
   <td><a href="http://mp.weixin.qq.com/s?__biz=MzI3MjM5MjE5OA%3D%3D&amp;chksm=eb32003bdc45892dc9d2ae5b11e1ec1357a1d2884ad3d1de51775c02a3045c336068e2ba3b37&amp;idx=2&amp;mid=2247485778&amp;scene=21&amp;sn=adbe45fd796a285d8c54f38d2727c496#wechat_redirect" rel="nofollow">09-XPath 语言</a></td> 
   <td><a href="http://mp.weixin.qq.com/s?__biz=MzI3MjM5MjE5OA%3D%3D&amp;chksm=eb32000edc45891837fbcaa3dc21becf71e86dcd25f599e7d8f59158bf339ddaad9f4c75dd90&amp;idx=2&amp;mid=2247485799&amp;scene=21&amp;sn=6ed8d9212f4bfb4d710523dfb478e9c4#wechat_redirect" rel="nofollow">10-lxml库</a></td> 
  </tr> 
  <tr> 
   <td colspan="1"><a href="http://mp.weixin.qq.com/s?__biz=MzI3MjM5MjE5OA%3D%3D&amp;chksm=eb320010dc458906991372249d605f7ac28b1bf649ce55cf5ba1255b42d9b153b003d6c9ccfd&amp;idx=2&amp;mid=2247485817&amp;scene=21&amp;sn=fc3c41347968b123ed51d0c61508428c#wechat_redirect" rel="nofollow">11-Beautiful Soup</a><br></td> 
   <td colspan="1"><a href="http://mp.weixin.qq.com/s?__biz=MzI3MjM5MjE5OA%3D%3D&amp;chksm=eb3200d6dc4589c005e613206c66915ef25f8740c3947a1cdac2a3f20135dad3b464c64cf5df&amp;idx=2&amp;mid=2247485887&amp;scene=21&amp;sn=9140a4874523e0e2aec3252577ab12a6#wechat_redirect" rel="nofollow">12-正则表达式</a><br></td> 
  </tr> 
  <tr> 
   <td colspan="1"><a href="http://mp.weixin.qq.com/s?__biz=MzI3MjM5MjE5OA%3D%3D&amp;chksm=eb320081dc45899750dfcf9a04789b5a16668d03245d868f182b24b5e8f3914151895e423d25&amp;idx=2&amp;mid=2247485928&amp;scene=21&amp;sn=79e235db280e4faad16141a22c06e37a#wechat_redirect" rel="nofollow">13-python爬虫JSON操作</a><br></td> 
   <td colspan="1"><br></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td align="center"><p><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy85eTFUZUhZejFBenluRzJDaWFvTElXMDRZbmNMcmlhdUhIc2NBeWFUaWE2MnNPdHJCZmtzQ3F1ME93ZWliaWFMYkZUaWJCMzk0dXF5MUlJWHYxOGJyY3E5M0pTdy82NDA?x-oss-process=image/format,png"></p><p>IT入门&nbsp;感谢关注</p></td> 
   <td align="left"><p>一学就会&nbsp;Python入门教程 →</p><p>程序员用的单词表→</p><p>练习地址：www.520mg.com/it</p></td> 
  </tr> 
 </tbody> 
</table>

[format_png]: /images/20221122/30c47d6505bf4a638b31cb0acc03b8d7.png
[null]: /images/20221122/7161a2fa08d84135a47a945ec1bb36ef.png