Python与excel交互--xlsxwriter模块

本文作者：张孟晗，中南财经政法大学统计与数学学院

本文编辑：周一鸣

技术总编：王玉婷

Stata and Python 数据分析

爬虫俱乐部Stata基础课程、Stata进阶课程和Python课程可在小鹅通平台查看，欢迎大家多多支持订阅！如需了解详情，可以通过课程链接（https://appbqiqpzi66527.h5.xiaoeknow.com/homepage/10）或课程二维码进行访问哦~

引言

说到表格，大家首先想到的是不是打开office中的excel去创建呢？其实，借助Python也可以实现excel表的一系列操作，今天小编带大家一起来了解Python中一个名叫xlsxwriter的强大模块，通过Python代码轻松玩转excel。

1. 模块介绍

xlsxwriter可以用于将文本，数字和公式写入多个工作表，支持格式设置、图像、图表、页面设置、条件格式设置、vba写入等功能，大多情况下，生成的文件和excel生成的文件100%相同。

大家可能疑惑，Python操作excel还有例如openpyxl、xlwings，那么这几个库之间有什么区别呢？一起来看：

openpyxl：只允许读取和写入.xlsx格式文件并进行增删改查。

xlwings：允许读取和写入.xlsx和.xls两种格式文件并进行增删改查。

xlsxwriter：只允许写入.xlsx格式的文件。

大家会发现，xlsxwriter不支持读取和修改，并且还不支持XLS格式文件，那xlsxwriter这个库也太不行了吧？其实不是的，在写入这方面前两个库是比不上它的，它的优势在于：可以写入多张样式图表、图片，而且支持表格样式修改；启用constant memory模式后支持大文件写入，这是一种顺序写入模式，得到一行数据就立刻写入一行，而不会把所有的数据都保持在内存中。

2. 模块实战

(1) 安装xlsxwriter

pip install xlsxwriter(2) 创建excel文件

只有文件名的话，创建的文件会在程序运行所在的文件夹，当然我们可以给出绝对路径，指定路径存放。

excel = xlsxwriter.Workbook("tuiwen.xlsx")excel = xlsxwriter.Workbook(r"‪C:\Users\ZMH\Desktop\xlsxwriter\tuiwen.xlsx)(3) 创建工作表

表创建完后，需要给表添加具体的工作表，我们可以使用excel对象的add_worksheet()方法，如果没有指定sheet的名称，默认以Sheet1命名，后续再添加工作表，继续以Sheet2，Sheet3等自动命名，如需指定sheet名，可在方法中增加名字参数。

#工作表名字为Sheet1sheet = excel.add_worksheet()#工作表名字为指定名字sheet = excel.add_worksheet("指定名字")(4) 写入数据

工作表创建完后，我们可以继续写入数据，使用write(单元格位置,数据,格式)方法写入单条数据。其中，单元格位置有两种表示方式，一种是以行,列表示，行列均是从0开始计数，例如0，0代表第一行第一列，另一种是用单元格名称表示，例如以A2表示，直接对应excel表的相应位置。

sheet.write(0,0,"数据")sheet.write("A2","data")

如果要一次性写入多条数据，我们可以使用write_row或者write_column方法。

#从A1单元格开始插入数据，按行插入sheet.write_row(“A1”,[0,1,2,3,4])#A1:从A1单元格开始插入数据，按列插入sheet.write_column(“A1”,[0,1,2,3,4])格式为可选参数，如需使用格式，则需使用add_format()方法定义后添加。同时，添加格式方式有两种，一种是以字典作为参数传入（推荐），另一种是以格式化方法的方式。

#字典传值format = { 'bold' : True, #粗体 'font_name' : '微软雅黑', 'font_color' : "red", #红色 'border' : True, #边框线 'align' : 'center', #水平居中 'valign' : 'vcenter', #垂直居中 'bg_color' : '#66DD00' #背景颜色绿色}fm = excel.add_format(format)sheet.write('A3','数据',fm)#格式化方法fm = excel.add_format()fm.set_bold(True)fm.set_font_name('微软雅黑')fm.set_font_color("red")fm.set_border(True)fm.set_align("center")fm.set_valign("vcenter")fm.set_bg_color("#66DD00")sheet.write('A3','数据',fm)

具体的相关属性及方法名称如下：

(5) 插入图片

插入图片使用insert_image（单元格位置,路径）方法，第一个参数是你要指定哪个单元格插入图片，第二个参数是存放图片的绝对路径。

sheet.insert_image('A1',r'‪C:\Users\ZMH\Pictures\2022.jpg')

(6) 插入图表

插入图表是xlsxwriter模块的最大闪光点，我们将详细介绍插入图表的代码及其解释。

workbook.add_chartsheet({'type': 'column','subtype': 'stacked'})函数是最经典的插入图表函数，字典里的第一个键type参数指的是放入的图表类型。而第二个键指的是某些图表类型中的图表子类型。

支持的图表类型有以下：

area：创建一个Area（实线）样式表。

bar：创建条形样式（转置直方图）图表。

column：创建列样式图表。

line：创建线型图表。

pie：创建一个饼图样式图表。

doughnut：创建一个甜甜圈样式表。

scatter：创建散点图样式图。

stock：创建一个股票样式图。

radar：创建雷达样式表。

设置了图表类型接下来就是插入数据，插入数据我们用chart.add_series(options)函数。这里的options是以字典形式的图表数据，在Excel中图表系列是一组信息(值、轴标签、格式等)。接下来就是将创建好的chart对象放入到Excel表格中，这里我们需要使用insert_chart()函数将图表插入到工作表指定的位置，第一个参数为单元格位置信息，第二个参数为选定的图表。

接下来我们来创建一个条形图：

chart = excel.add_chart({'type': 'column'})
data = [
    [3, 6, 9, 12, 15],
    [2, 4, 6, 8, 10],
    [1, 2, 3, 4, 5],
]

sheet.write_column('A1', data[0])
sheet.write_column('B1', data[1])
sheet.write_column('C1', data[2])

chart.add_series({'values': '=Sheet1!$A$1:$A$5'})
chart.add_series({'values': '=Sheet1!$B$1:$B$5'})
chart.add_series({'values': '=Sheet1!$C$1:$C$5'})

sheet.insert_chart('A7', chart)

(7) 关闭excel

上面的例子其实最后都调用了这句代码，使用这个代码关闭excel之后才会保存哦。

excel.close()

希望通过以上讲解能让大家对xlsxwriter模块有一个初步的了解，大家是不是觉得很有趣呢，不妨动手试试吧！

END

重磅福利！为了更好地服务各位同学的研究，爬虫俱乐部将在小鹅通平台上持续提供金融研究所需要的各类指标，包括上市公司十大股东、股价崩盘、投资效率、融资约束、企业避税、分析师跟踪、净资产收益率、资产回报率、国际四大审计、托宾Q值、第一大股东持股比例、账面市值比、沪深A股上市公司研究常用控制变量等一系列深加工数据，基于各交易所信息披露的数据利用Stata在实现数据实时更新的同时还将不断上线更多的数据指标。我们以最前沿的数据处理技术、最好的服务质量、最大的诚意望能助力大家的研究工作！相关数据链接，请大家访问：(https://appbqiqpzi66527.h5.xiaoeknow.com/homepage/10)或扫描二维码：

最后，我们为大家揭秘雪球网(https://xueqiu.com/)最新所展示的沪深证券和港股关注人数增长Top10。

对我们的推文累计打赏超过1000元，我们即可给您开具发票，发票类别为“咨询费”。用心做事，不负您的支持！

往期推文推荐  Python中的异常处理 Python交互式数据可视化——酷炫的Altair库 hk系列命令（3）—— hktrade hk系列命令（2）—— hkar hk系列命令（1）—— hkstock 超好用的字符串方法 基于Python的假设检验实现Stata与MySQL交互--基础操作 Jupyter Notebook中的魔术命令《Stata正则表达式》由中国金融出版社出版发行   匿名函数lambda到底怎么用？Stata绘图系列—NBER Working paper仿图Camelot:从PDF中提取表格数据Stata之计算财务指标——融资约束 列表生成式|让你的代码更简洁Stata绘图系列——玩转绘图通用选项之坐标轴玩转地图的好帮手--pyecharts    关于我们 

   微信公众号“Stata and Python数据分析”分享实用的Stata、Python等软件的数据处理知识，欢迎转载、打赏。我们是由李春涛教授领导下的研究生及本科生组成的大数据处理和分析团队。

   武汉字符串数据科技有限公司一直为广大用户提供数据采集和分析的服务工作，如果您有这方面的需求，请发邮件到[e-mail]，或者直接联系我们的数据中台总工程司海涛先生，电话：[phone]，wechat: super4ht。海涛先生曾长期在香港大学从事研究工作，现为知名985大学的博士生，爬虫俱乐部网络爬虫技术和正则表达式的课程负责人。

此外，欢迎大家踊跃投稿，介绍一些关于Stata和Python的数据处理和分析技巧。

投稿邮箱：[e-mail]投稿要求：1）必须原创，禁止抄袭；2）必须准确，详细，有例子，有截图；注意事项：1）所有投稿都会经过本公众号运营团队成员的审核，审核通过才可录用，一经录用，会在该推文里为作者署名，并有赏金分成。

2）邮件请注明投稿，邮件名称为“投稿+推文名称”。3）应广大读者要求，现开通有偿问答服务，如果大家遇到有关数据处理、分析等问题，可以在公众号中提出，只需支付少量赏金，我们会在后期的推文里给予解答。