Excel操作之写操作---基于xlsxwriter模块

这里主要是介绍Excel的以下操作：新建工作表、在表格里写数据、合并单元格、设置行高、列宽等操作。其一般的操作步骤如下：
一、导入模块：import xlsxwriter
二、创建workbook对象：workbook = xlsxwriter.Workbook(文件名)
三、创建工作表对象：worksheet = workbook.add_worksheet(<表名>) # 表名省略时就默认为sheet1
四、对工作表的相关操作
五、关闭并保存workbook：workbook.close()

新建工作表

例子如下：

import xlsxwriter

# 创建工作表
workbook = xlsxwriter.Workbook('test.xlsx')
# 默认创建sheet1
worksheet = workbook.add_worksheet()
# 创建sheet2
worksheet = workbook.add_worksheet('sheet2')
workbook.close()

向单元格写数据

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write(0, 0, '你好')  # 行号和列标均是从0开始
workbook.close()

合并单元格

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()
worksheet.merge_range('A1:H1', '合并单元格')
workbook.close()

设置行高、列宽

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()
worksheet.set_row(0, 70)
worksheet.set_col('A:A', 10)
workbook.close()

单元格样式设置

import xlsxwriter

header = '测试标题'
workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()
worksheet.merge_range('A1:H1', '')
f = workbook.add_format(({
    'align': 'center',
    'valign': 'top',
    'font_size': 20,
    'text_wrap': 1,  # 自动换行
}))
worksheet.write('A1', header, f)
worksheet.set_row(0, 70)
workbook.close()