有两组数据，希望将它们保存到 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 xlsxwriterworkbook = 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 pddata1 = 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 xlsxwriterworkbook = 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()

❝

文章创作不易，如果您喜欢这篇文章，请关注、点赞并分享给朋友。如有意见和建议，请在评论中反馈！

❞