1:xlsxwriter模块介绍
xlsxwriter模块主要用来生成excel表格(后缀名为.xlsx)文件,插入数据、插入图标等表格操作。写大文件,速度快且只占用很小的内存空间;
2:xlsxwriter模块安装
pip install xlsxwriter
3:xlsxwriter模块常用操作
3.1:创建工作簿,工作表
# encoding=gbkimport xlsxwriter # 创建工作簿workbook = xlsxwriter.Workbook('test.xlsx')#创建一个excel文件 # 创建工作表worksheet = workbook.add_worksheet('test-sheet1')#在文件中创建一个名为test-sheet1的sheet,不加名字默认为sheet1worksheet2 = workbook.add_worksheet() # Sheet2worksheet3 = workbook.add_worksheet('test-sheet2')worksheet4 = workbook.add_worksheet() # Sheet4 workbook.close()
3.2:设置单元格的值
3.2.1:给单个单元格赋值
# encoding=gbkimport xlsxwriter # 一:创建工作簿workbook = xlsxwriter.Workbook('test.xlsx')#创建一个excel文件 # 二:创建工作表worksheet1 = workbook.add_worksheet('test-sheet1')#在文件中创建一个名为test-sheet1的sheet,不加名字默认为sheet1worksheet2 = workbook.add_worksheet() # Sheet2worksheet3 = workbook.add_worksheet('test-sheet2')worksheet4 = workbook.add_worksheet() # Sheet4 # 三:设置单元格的值worksheet1.write('A1', 'hello123') # 在A1单元格写上HELLOworksheet1.write('A2', '123测试456') # 在B2上写上中文加粗 # 使用行列方式,下标从0开始worksheet1.write(2, 0, 100) # 第3行第1列(即A3) 写入100worksheet1.write(3, 0, 99.8) # 第4行第1列(即A4) 写入99.8worksheet1.write(4, 0, '=SUM(A3:A4)') # 写上excel公式 # 合并单元格worksheet1.merge_range(1,2,3,4,'合并01')worksheet1.merge_range('C6:E7','合并02')# worksheet1.write_number:写入数字worksheet1.write_number(5, 0, 1001) # Int or float # worksheet1.write_blank:写入空格worksheet1.write_blank(6, 0,'xx') # 不管第三个参数是什么,都写入空格# worksheet1.write_formula:写入公式worksheet1.write_formula(7, 0, '=SUM(A3:A4)') # worksheet1.write_datetime:写入时间格式from datetime import datetimedate_format = workbook.add_format({
'num_format': 'yyyy-mm-dd H:M:S'})worksheet1.write_datetime(8,