Python写入Excel表格模块:XLsxWriter(功能最强的Excel写入模块)

前面介绍过一个Excel写入模块：xlwt，此模块虽然可以实现Excel的写入，但是它功能还是不够强大，而且只支持xls后缀的文件，因此，如果针对要求比较高，且要接近Excel本身一样的操作，xlwt还是不够胜任，那有没有功能跟Excel本身操作一样的呢，答案是肯定的，那就是我们接下来要介绍的模块：XLsxWriter。

XLsxWriter相比于其他的Excel操作模块，其具有如下的优点：

1、它具备几乎全部的Excel写入操作功能，这一点其他模块是达不到的。

2、它生成的Excel文件基本与Excel生成的文件一样。

3、它速度快，即使输出大的Excel文件，其内存占用也很小。

备注：XLsxWriter只支持Excel文件的写入，并不支持打开或者修改现有的Excel文件，且其只支持后缀为 .xlsx 文件，不支持后缀为 .xls 文件。

一、XLsxWriter模块的安装及升级

如果自身Python没有XLsxWriter模块，则需要安装，可以使用命令行或者编译器进行升级，输入以下代码即可：

pip install xlsxwriter  # 注意全部小写

另外，Python3本身内置安装XLsxWiter模块，如果版本过低，可以使用命令行或者编译器进行升级，输入以下代码即可：

pip  install --upgrade xlsxwriter # 注意全部小写

二、XLsxWriter模块创建Excel文件4步法

2.1、Workbook("filename")：创建 Excel 文件

此函数为创建一个 Excel 文件对象，filename为文件的名称及存储路径，可以利用此对象创建 Excel 工作表，其代码如下：

import  xlsxwriter  as  xw   # 导入模块
wb = xw.Workbook("file/数据统计.xlsx")  #  创建 Excel 文件

2.2、add_workbook("filename")：创建 Excel 工作表

此函数为创建一张Excel工作表，filename为工作表的名称，如果不指定，则为默认的sheet1，可以利用这个对象往工作表里添加数据，其代码如下：

sheet1 = wb.add_workbook() # 创建 Excel 工作表，不写名称则为 sheet1

备注：如果想创建多个工作表，可以多次创建，如：

shee1 = wb.add_workbook()  # 创建工作表 sheet1
sheel2 = wb.add_workbook("基本数据")  # 创建工作表 基本数据
sheet3 = wb.add_workbook() # 创建工作表 sheet3

2.3、write(row, col, *args)：向 Excel 工作表写入数据

此函数为向 Excel 工作表写入数据，row 为行的索引，col 为列的索引（索引从 0 开始），*args 为写入的内容，格式等，其代码如下：

# 方法一
sheet1.write(0, 0, "hello")  #  向 0 行 0 列（即A1） 写入 hello

# 方法二
sheet1.write(A1, "hello")  # 向 A1 单元格（即 0 行 0 列）写入 hello

此时本地还没有Excel文件，需要使用close()函数关闭后，才在本地生成 Excel 文件。

备注：write() 函数如果写入成功，返回一个数字：0，可以据此判断是否写入成功。

2.4、close()：关闭 Excel 文件，即为保存。

close()函数为关闭 Excel 文件，可以理解为保存，当我们做完所有的操作后，代码最后需要使用wb.close()，方能在本地保存所创建的 Excel 文件，其相当于 xlwt 模块的 save() 函数，其代码如下：

wb.close()  #  关闭 Excel 文件，即保存

执行结果：

以上为 XLsxWriter 模块写入Excel文件的最基本的4个步骤，只要按照以上4个步骤，即可创建一个 Excel 表格。至于字体、边框、颜色、背景这些样式，可以在第三步 write() 函数中扩展。

三、XLsxWriter模块进阶用法

3.1、add_format()：设置单元格样式

此函数为设置单元格样式（样式很多，在此不一一列举，只列举几种，感兴趣的朋友可以查查官方文档），有2种方式：

1、通过字典的方式设置，如

import xlsxwriter as xw  # 导入模块

wb = xw.Workbook("file/数据统计.xlsx") #  注意 xlsxwriter 模块只能操作后缀为.xlsx的Excel文件
sheet1 = wb.add_worksheet() # 创建 Excel 工作表，不写名称则为 sheet1

cell_format = wb.add_format({
    'bold' : True, # 设置字体为 粗体
    'font_name' : '黑体', # 设置字体为 黑体
    'font_size': 20, # 设置字号为 20
    'align' : 'center', # 设置字体 水平居中
    'valign' : 'vcenter', # 设置字体 垂直居中
    'bg_color' : 'green', # 设置单元格背景色为 绿色
    'border' : 1 # 设置单元格边框为 1
})

sheet1.write("A1","I Love Python",cell_format) # 写入单元格
wb.close() # 保存

执行效果：

2、通过对象调用方法的方式设置，如：

除了通过字典的方式设置单元格样式外，XLsxWriter 模块还为每个样式设置了对应的方法，如下表：

以上只是列举了字体的部分样式，还有很多，如对齐、边框、图案等，在此就不一一列举了，感兴趣的可以查阅官方文档。

import xlsxwriter as xw  # 导入模块

wb = xw.Workbook("file/数据统计.xlsx") #  注意 xlsxwriter 模块只能操作后缀为.xlsx的Excel文件
sheet1 = wb.add_worksheet() # 创建 Excel 工作表，不写名称则为 sheet1

cell_format = wb.add_format() # 创建单元格样式对象
cell_format.set_bold(True)  # 设置字体为 粗体
cell_format.set_font_name("黑体") # 设置字体为 黑体
cell_format.set_font_size(20) # 设置字号为 20
cell_format.set_align("center") # 设置字体 水平居中
cell_format.set_valign("vcenter") # 设置字体 垂直居中
cell_format.set_bg_color("yellow") # 设置单元格背景色为 黄色
cell_format.set_border(2) # 设置单元格边框为 2

sheet1.write("A2","I Love Python",cell_format) # 写入单元格
wb.close() # 保存

执行结果：

3.2、write_rich_string()：写入多种样式的字符串

如果我们想在单元格中写入多种样式的字符串，可以使用 write_rich_string() 这个方法，比如不同颜色，不同字体，不同大小等，如：

import xlsxwriter as xw  # 导入模块

wb = xw.Workbook("file/数据统计.xlsx") #  注意 xlsxwriter 模块只能操作后缀为.xlsx的Excel文件
sheet1 = wb.add_worksheet() # 创建 Excel 工作表，不写名称则为 sheet1

font1 = wb.add_format({ # 设置字体格式1
    'font_size':12,
    'font_color':'blue',
    'bold':True
})

font2 = wb.add_format({ #设置字体格式2
    'font_size':16,
    'font_color':'green',
    'italic':True
})

sheet1.write_rich_string("A1","这是字体 ",font1,"蓝色粗体","和 字体",font2,"绿色斜体") # 写入单元格
wb.close() # 保存

执行结果：

3.3、设置行高和列宽

XLsxWriter 模块对于设置行高和列宽比较简单，使用set_row(row, height, cell_format, options) 和 set_column(first_col, last_col, width, cell_format, options)，这两个方法即可，其中 height 和 width 就是行高和列宽，如：

import xlsxwriter as xw  # 导入模块

wb = xw.Workbook("file/xw/设置行高和列宽.xlsx") #  注意 xlsxwriter 模块只能操作后缀为.xlsx的Excel文件
sheet1 = wb.add_worksheet() # 创建 Excel 工作表，不写名称则为 sheet1

sheet1.set_row(0,30,wb.add_format({'bg_color':'yellow'})) # 设置行高
sheet1.set_column(0,3,50) # 设置列宽

sheet1.write("A1","hello,I love python") # 写入单元格
wb.close() # 保存

执行结果：

说明：

1、如果想设置行的样式不改变行高，height 可设置为 None 或者 15（Excel默认行高值。）

2、如果想设置1列，可以用 first_col = last_col，即开始列和结束列一样即可。

3、还有两个方法set_row_pixels和set_column_pixels()，功能与set_row()和set_column()一样，只是前者是以字符为单位的，而后者是以像素为单位的。

3.4、merge_range()：合并单元格

我们操作Excel表格时，经常会合并单元格，在XLsxWriter也可以合并单元格，使用merge_range(first_row, first_col, last_row, last_col, data[,cell_format])方法即可，其中：

first_row：开始行

first_col：开始列

last_row：结束行

last_col：结束列

data：需要写入的数据

cell_format：合并后单元格样式

import xlsxwriter as xw  # 导入模块

wb = xw.Workbook("file/xw/合并单元格.xlsx") #  注意 xlsxwriter 模块只能操作后缀为.xlsx的Excel文件
sheet1 = wb.add_worksheet() # 创建 Excel 工作表，不写名称则为 sheet1
# 方法一
sheet1.merge_range(2,1,3,3,"合并单元格一",wb.add_format({'bg_color':'yellow',"align":"center","valign":"vcenter","border":2}))

# 方法二
sheet1.merge_range("B6:D8","合并单元格二",wb.add_format({'bg_color':'#bbddee',"align":"center","valign":"vcenter","border":2}))

wb.close() # 保存

执行结果：

3.5、insert_image(row, col, image_path[,options])：插入图片

XLsxWriter 模块插入图片很简单，且支持 PNG,JPEG,GIF,BMP 等格式的插入，不需要转换，使用 insert_image() 方法即可实现图片插入。

import xlsxwriter as xw  # 导入模块

wb = xw.Workbook("file/xw/插入图片.xlsx") #  注意 xlsxwriter 模块只能操作后缀为.xlsx的Excel文件
sheet1 = wb.add_worksheet() # 创建 Excel 工作表，不写名称则为 sheet1

# 图片格式设置
image_format = {
    'x_offset' : 0,  # x 方向位移
    'y_offset' : 0,  # y 方向位移
    'x_scale' : 0.5, # x 方向缩放
    'y_scale' : 0.5, # y 方向缩放
    'boject_position' : 2, # 对象位置，1-随单元格移动和大小而变，2-随单元格移动而移动，不随大小改变而改变，3-不随单元格移动和大小而变，4-与1小腿，但插入图片后隐藏单元格
    'image_data' : None, # 图片数据信息
    'url' : 'http://www.baidu.com', # 图片超链接
    'description' : "this is a image", # 图片说明
    'decorative' : False # 图片装饰
}

sheet1.insert_image(2,1,"file/test.jpg",image_format) # 插入图片

wb.close() # 保存

执行结果：

3.6、insert_chart(row, col, chart[,options])：插入图表

除了插入图片外，XLsxWriters 模块还可以插入图表，使用 insert_chart() 方法即可实现，如：

import xlsxwriter as xw  # 导入模块

wb = xw.Workbook("file/xw/插入图表.xlsx") #  注意 xlsxwriter 模块只能操作后缀为.xlsx的Excel文件
sheet1 = wb.add_worksheet() # 创建 Excel 工作表，不写名称则为 sheet1
chart = wb.add_chart({'type' : 'column'}) # 设置图表类型：柱状图

# 设置图表数据
data = [
    [1,2,3,4,5],
    [2,4,6,7,10],
    [3,6,9,12,15]
]

# 写入表格数据
sheet1.write_column('A1',data[0])
sheet1.write_column('B1',data[1])
sheet1.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'})

# 插入图表
sheet1.insert_chart('A7',chart)

wb.close() # 保存

执行结果：

备注：图表的方法很多，可以设置图表的属性，如类型，标题，x轴，y轴，背景，系列名称等，感兴趣的朋友可以参考官方文档参考。

3.7、insert_table(first_row,first_col,last_row,last_col,data)：插入数据筛选表格

insert_table()方法可以向Excel表里插入可筛选数据的表格，如：

import xlsxwriter as xw  # 导入模块

wb = xw.Workbook("file/xw/数据筛选.xlsx") #  注意 xlsxwriter 模块只能操作后缀为.xlsx的Excel文件
sheet1 = wb.add_worksheet() # 创建 Excel 工作表，不写名称则为 sheet1

data = [
    ['Apples',10000,5000,8000,6000],
    ['Pears',2000,3000,4000,6000],
    ['Bananas',6000,6000,6500,6000],
    ['Oranges',500,300,200,700]
]

sheet1.add_table('B3:F7',{'data' : data,
                          'columns' : [{'header' : 'Product'},
                                       {'header' : 'Dollar1'},
                                       {'header' : 'Dollar2'},
                                       {'header' : 'Dollar3'},
                                       {'header' : 'Dollar4'},
                                       {'header' : 'Year','formula' : '=SUM(Sheet1[@Dollar1]:[Dollar4])'}
                                      ]                        
                         })
wb.close() # 保存

执行结果：

3.8、data_validation(first_row, first_col, last_row, last_col, options)：数据验证

在 XLsxWriter 模块中，可以使用 data_validation() 方法来限制单元格输入数据的范围，如：

import xlsxwriter as xw  # 导入模块

wb = xw.Workbook("file/xw/数据验证.xlsx") #  注意 xlsxwriter 模块只能操作后缀为.xlsx的Excel文件
sheet1 = wb.add_worksheet() # 创建 Excel 工作表，不写名称则为 sheet1

sheet1.data_validation('B2',{'validate' : 'integer', # 验证类型
                             'criteria' : 'between', # 验证方式
                             'minimum' : 1, # 最小值
                             'maximum' : 10 # 最大值
                            })

sheet1.write_row('A1',['数据说明','数值']) # 按行输入数据
sheet1.write('A2','请输入1-10的数据')

wb.close() # 保存

执行结果：

3.9、add_sparkline(row, col, options)：插入迷你图表

XLsxWriter 模块可以用 add_sparkline() 方法插入迷你图表，如：

import xlsxwriter as xw  # 导入模块

wb = xw.Workbook("file/xw/迷你图表.xlsx") #  注意 xlsxwriter 模块只能操作后缀为.xlsx的Excel文件
sheet1 = wb.add_worksheet() # 创建 Excel 工作表，不写名称则为 sheet1

data = [
    [-2,2,3,-1,0],
    [30,20,33,20,15],
    [1,-1,-1,1,-1]
]

# 写入表格数据
sheet1.write_row('A1',data[0])
sheet1.write_row('A2',data[1])
sheet1.write_row('A3',data[2])

# 折线图，默认图表，可省略 type
sheet1.add_sparkline('F1',{'range':'A1:E1',
                           'type':'line', 
                           'style':2,
                           'markers':True
                          })
# 柱状图
sheet1.add_sparkline('F2',{'range':'A2:E2',
                           'type':'column',
                           'style':12                           
                          })
# 输赢图
sheet1.add_sparkline('F3',{'range':'A3:E3',
                           'type':'win_loss',
                           'negative_points':True
                          })

wb.close() # 保存

执行结果：

3.10、write_comment(row, col, comment[,options])：单元格注释

在 XLsxWriter 模块中，单元格注释使用 write_comment() 方法，如：

import xlsxwriter as xw  # 导入模块

wb = xw.Workbook("file/xw/单元格注释.xlsx") #  注意 xlsxwriter 模块只能操作后缀为.xlsx的Excel文件
sheet1 = wb.add_worksheet() # 创建 Excel 工作表，不写名称则为 sheet1

sheet1.write('A1','hello world')
sheet1.write_comment('A1','这是一个注释')

wb.close() # 保存

执行结果：

以上即为 XLsxWriter 模块的一些简单介绍，可以说 XLsxWriter 模块是最接近Excel表格操作的，Excel表格能做的，它基本都可以做，当然还有很多的功能，感兴趣的朋友可以自己去研究。