有两组数据，希望将它们保存到 Excel 文件的不同工作表中，并对 Excel 中的数据格式进行美化。可以使用 xlsxwriter 来解决问题。
# 测试数据
data1 = pd.DataFrame(
{'日期': ['2023/1/1', '2023/2/10', '2023/2/21', '2023/2/13'], '整数': [196382, 287393, 5274984, 638164],
'千分位整数': [8145132, 128145, 536278, 463816], '文本': ['Python', 'Hello', 'For', 'Print'],
'浮点数': [18.45, 75.24, 90, 80.12], '百分数': [0.5678, 0.723, 0.3345, 0.2521]})
data2 = pd.DataFrame(
{'日期': ['2022/3/1', '2022/2/1', '2022/5/1', '2022/6/1'], '整数': [233211, 24321, 35345, 23223],
'千分位整数': [920478, 23454, 123749, 2345875],'文本': ['While', 'For', 'Else', 'If'],
'浮点数': [93.45, 67.24, 80, 80.56],'百分数': [0.4884, 0.9694, 0.6752, 0.2633]})
导入 xlsxwriter 包并创建 Excel 文件
Workbook() 用于创建新的 Excel 文件。
import xlsxwriter
workbook = xlsxwriter.Workbook("new_excel.xlsx")
创建工作表
add_worksheet() 用于创建工作表。
worksheet1 = workbook.add_worksheet('sheet01')
worksheet2 = workbook.add_worksheet('sheet02')
设置表头
使用 write_row() 设置表头。
write_row()语法格式：
worksheet.write_row（row， col， data， cell_format）。
# 设置表头样式
header_format = workbook.add_format({
'valign': 'top',
'fg_color': '#1b75da',
'border': 1,
'font_color': 'white'})
worksheet1.write_row(0, 0, df1.columns, header_format)
worksheet2.write_row(0, 0, df2.columns, header_format)
创建某种格式
format.set_num_format()：此方法用于在 Excel 中定义数字的格式。控制数字是显示为整数、浮点数、日期、货币值还是其他用户定义的格式。
可以使用格式字符串或 Excel 内置格式的索引来指定单元格的数字格式。
# 日期格式
format_datetime = workbook.add_format({'border': 1})
format_datetime.set_num_format(14) # 索引 14 代表格式 "m/d/yy"
format_datetime.set_font_size(12) # 字号
# 通用格式
format_general = workbook.add_format({'border': 1})
format_general.set_num_format(0) # 索引 0 代表通用格式
format_general.set_font_size(12)
# 整数格式
format_integer = workbook.add_format({'border': 1})
format_integer.set_num_format(1)
format_integer.set_font_size(12)
# 浮点数格式
format_float = workbook.add_format({'border':1})
format_float.set_num_format(2)
format_float.set_font_size(12)
# 包含千位分隔符的整数格式
format_integer_separator = workbook.add_format({'border': 1})
format_integer_separator.set_num_format(3)
format_integer_separator.set_font_size(12)
# 百分数格式
format_percent = workbook.add_format({'border':1})
format_percent.set_num_format(10)
format_percent.set_font_size(12)
将数据保存到 Excel 中
write_column() 将数据保存到 Excel 中。
worksheet1.write_column(1, 0, df1.iloc[:, 0], format_datetime)
worksheet1.write_column(1, 1, df1.iloc[:, 1], format_integer)
worksheet1.write_column(1, 2, df1.iloc[:, 2], format_integer_separator)
worksheet1.write_column(1, 3, df1.iloc[:, 3], format_general)
worksheet1.write_column(1, 4, df1.iloc[:, 4], format_float)
worksheet1.write_column(1, 5, df1.iloc[:, 5], format_percent)
worksheet2.write_column(1, 0, df2.iloc[:, 0], format_datetime)
worksheet2.write_column(1, 1, df2.iloc[:, 1], format_integer)
worksheet2.write_column(1, 2, df2.iloc[:, 2], format_integer_separator)
worksheet2.write_column(1, 3, df2.iloc[:, 3], format_general)
worksheet2.write_column(1, 4, df2.iloc[:, 4], format_float)
worksheet2.write_column(1, 5, df2.iloc[:, 5], format_percent)
设置列宽
为了更好地显示数据，还可以使用 set_column() 设置列宽。
worksheet1.set_column('A:A', 15)
worksheet1.set_column('B:B', 15)
worksheet1.set_column('C:C', 15)
worksheet1.set_column('D:D', 15)
worksheet1.set_column('E:E', 15)
worksheet1.set_column('F:F', 15)
worksheet2.set_column('A:A', 15)
worksheet2.set_column('B:B', 15)
worksheet2.set_column('C:C', 15)
worksheet2.set_column('D:D', 15)
worksheet2.set_column('E:E', 15)
worksheet2.set_column('F:F', 15)
完成写入
workbook.close()
「完整代码：」
import pandas as pd
data1 = pd.DataFrame(
{'日期': ['2023/1/1', '2023/2/10', '2023/2/21', '2023/2/13'], '整数': [196382, 287393, 5274984, 638164],
'千分位整数': [8145132, 128145, 536278, 463816], '文本': ['Python', 'Hello', 'For', 'Print'],
'浮点数': [18.45, 75.24, 90, 80.12], '百分数': [0.5678, 0.723, 0.3345, 0.2521]})
data2 = pd.DataFrame(
{'日期': ['2022/3/1', '2022/2/1', '2022/5/1', '2022/6/1'], '整数': [233211, 24321, 35345, 23223],
'千分位整数': [920478, 23454, 123749, 2345875], '文本': ['While', 'For', 'Else', 'If'],
'浮点数': [93.45, 67.24, 80, 80.56], '百分数': [0.4884, 0.9694, 0.6752, 0.2633]})
print(data1)
print(data2)
import xlsxwriter
workbook = xlsxwriter.Workbook("new_excel.xlsx")
worksheet1 = workbook.add_worksheet('sheet01')
worksheet2 = workbook.add_worksheet('sheet02')
header_format = workbook.add_format({
'valign': 'top',
'fg_color': '#1b75da',
'border': 1,
'font_color': 'white'})
worksheet1.write_row(0, 0, data1.columns, header_format)
worksheet2.write_row(0, 0, data2.columns, header_format)
format_datetime = workbook.add_format({'border': 1})
format_datetime.set_num_format(14)
format_datetime.set_font_size(12)
format_general = workbook.add_format({'border': 1})
format_general.set_num_format(0)
format_general.set_font_size(12)
format_integer = workbook.add_format({'border': 1})
format_integer.set_num_format(1)
format_integer.set_font_size(12)
format_float = workbook.add_format({'border':1})
format_float.set_num_format(2)
format_float.set_font_size(12)
format_integer_separator = workbook.add_format({'border': 1})
format_integer_separator.set_num_format(3)
format_integer_separator.set_font_size(12)
format_percent = workbook.add_format({'border':1})
format_percent.set_num_format(10)
format_percent.set_font_size(12)
worksheet1.write_column(1, 0, data1.iloc[:, 0], format_datetime)
worksheet1.write_column(1, 1, data1.iloc[:, 1], format_integer)
worksheet1.write_column(1, 2, data1.iloc[:, 2], format_integer_separator)
worksheet1.write_column(1, 3, data1.iloc[:, 3], format_general)
worksheet1.write_column(1, 4, data1.iloc[:, 4], format_float)
worksheet1.write_column(1, 5, data1.iloc[:, 5], format_percent)
worksheet2.write_column(1, 0, data2.iloc[:, 0], format_datetime)
worksheet2.write_column(1, 1, data2.iloc[:, 1], format_integer)
worksheet2.write_column(1, 2, data2.iloc[:, 2], format_integer_separator)
worksheet2.write_column(1, 3, data2.iloc[:, 3], format_general)
worksheet2.write_column(1, 4, data2.iloc[:, 4], format_float)
worksheet2.write_column(1, 5, data2.iloc[:, 5], format_percent)
worksheet1.set_column('A:A', 15)
worksheet1.set_column('B:B', 15)
worksheet1.set_column('C:C', 15)
worksheet1.set_column('D:D', 15)
worksheet1.set_column('E:E', 15)
worksheet1.set_column('F:F', 15)
worksheet2.set_column('A:A', 15)
worksheet2.set_column('B:B', 15)
worksheet2.set_column('C:C', 15)
worksheet2.set_column('D:D', 15)
worksheet2.set_column('E:E', 15)
worksheet2.set_column('F:F', 15)
workbook.close()
