从零开始学习pandas输出Excel的详细教程包含格式设置多表导出数据处理等实用技巧助你快速提升数据分析能力

威震华夏关云长

引言

在数据分析和处理领域，Excel一直是最受欢迎的工具之一，而Python的pandas库则是数据科学家的得力助手。将这两者结合起来，能够极大地提升我们的数据处理和分析效率。本教程将带你从零开始，全面学习如何使用pandas将数据输出到Excel文件，包括格式设置、多表导出、数据处理等实用技巧，帮助你快速提升数据分析能力。

pandas基础

在开始学习pandas输出Excel之前，我们需要先了解pandas的基础知识。

安装pandas和openpyxl

首先，确保你已经安装了pandas库。如果还没有安装，可以通过以下命令安装：

2. pip install pandas

复制代码

为了能够将数据输出到Excel文件，我们还需要安装openpyxl或xlsxwriter库：

2. pip install openpyxl
3. # 或者
4. pip install xlsxwriter

复制代码

pandas的基本数据结构

pandas有两个主要的数据结构：Series和DataFrame。

• Series：一维标记数组，能够保存任何数据类型。
• DataFrame：二维标记数据结构，类似于表格或Excel工作表。

下面是创建这两个数据结构的简单示例：

2. import pandas as pd
3. import numpy as np
5. # 创建Series
6. s = pd.Series([1, 3, 5, np.nan, 6, 8])
7. print("Series示例:")
8. print(s)
10. # 创建DataFrame
11. data = {
12.     '姓名': ['张三', '李四', '王五', '赵六'],
13.     '年龄': [25, 30, 35, 40],
14.     '城市': ['北京', '上海', '广州', '深圳']
15. }
16. df = pd.DataFrame(data)
17. print("\nDataFrame示例:")
18. print(df)

复制代码

基础Excel导出

使用to_excel()方法

pandas提供了to_excel()方法，可以将DataFrame导出为Excel文件。下面是一个简单的例子：

2. import pandas as pd
4. # 创建DataFrame
5. data = {
6.     '姓名': ['张三', '李四', '王五', '赵六'],
7.     '年龄': [25, 30, 35, 40],
8.     '城市': ['北京', '上海', '广州', '深圳']
9. }
10. df = pd.DataFrame(data)
12. # 导出到Excel
13. df.to_excel('output.xlsx', index=False)  # index=False表示不保存索引

复制代码

运行上述代码后，会在当前目录下生成一个名为output.xlsx的Excel文件。

to_excel()方法的常用参数

to_excel()方法有许多参数可以控制输出格式，下面是一些常用参数：

2. df.to_excel(
3.     excel_writer,          # 文件路径或ExcelWriter对象
4.     sheet_name='Sheet1',   # 工作表名称
5.     na_rep='',             # 缺失值的表示方式
6.     float_format='%.2f',   # 浮点数的格式
7.     columns=None,          # 要导出的列
8.     header=True,           # 是否包含列名
9.     index=True,            # 是否包含索引
10.     index_label=None,      # 索引列的标签
11.     startrow=0,            # 数据的起始行
12.     startcol=0,            # 数据的起始列
13.     engine=None,           # 使用的引擎（'openpyxl'或'xlsxwriter'）
14.     encoding=None,         # 编码方式
15.     inf_rep='inf',         # 无穷大的表示方式
16.     verbose=True,          # 是否显示详细信息
17.     freeze_panes=None      # 冻结窗格的位置
18. )

复制代码

下面是一个使用这些参数的示例：

2. import pandas as pd
3. import numpy as np
5. # 创建包含各种数据类型的DataFrame
6. data = {
7.     '产品': ['A', 'B', 'C', 'D'],
8.     '价格': [12.5, 23.756, 8.99, 15.0],
9.     '销量': [100, 150, None, 200],
10.     '库存': [50, 75, 30, np.inf]
11. }
12. df = pd.DataFrame(data)
14. # 导出到Excel，使用多种参数
15. df.to_excel(
16.     'products.xlsx',
17.     sheet_name='产品信息',
18.     na_rep='N/A',           # 缺失值显示为N/A
19.     float_format='%.2f',    # 浮点数保留两位小数
20.     columns=['产品', '价格', '销量'],  # 只导出这三列
21.     index=False,            # 不导出索引
22.     startrow=2,             # 从第3行开始写入数据
23.     startcol=1              # 从第2列开始写入数据
24. )

复制代码

格式设置

仅仅将数据导出到Excel是不够的，我们通常还需要设置单元格的格式，使数据更加美观和易读。

使用ExcelWriter设置格式

要设置Excel单元格的格式，我们需要使用ExcelWriter对象，并结合openpyxl或xlsxwriter库的功能。

2. import pandas as pd
3. from openpyxl import load_workbook
4. from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
6. # 创建DataFrame
7. data = {
8.     '产品': ['A', 'B', 'C', 'D'],
9.     '价格': [12.5, 23.756, 8.99, 15.0],
10.     '销量': [100, 150, 80, 200]
11. }
12. df = pd.DataFrame(data)
14. # 导出到Excel
15. with pd.ExcelWriter('formatted_output.xlsx', engine='openpyxl') as writer:
16.     df.to_excel(writer, sheet_name='产品信息', index=False)
17.    
18.     # 获取工作簿和工作表对象
19.     workbook = writer.book
20.     worksheet = writer.sheets['产品信息']
21.    
22.     # 设置标题行的格式
23.     header_font = Font(name='Arial', bold=True, color='FFFFFF')
24.     header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')
25.     header_alignment = Alignment(horizontal='center', vertical='center')
26.    
27.     # 设置边框
28.     thin_border = Border(
29.         left=Side(style='thin'),
30.         right=Side(style='thin'),
31.         top=Side(style='thin'),
32.         bottom=Side(style='thin')
33.     )
34.    
35.     # 应用格式到标题行
36.     for cell in worksheet[1]:  # 第一行是标题行
37.         cell.font = header_font
38.         cell.fill = header_fill
39.         cell.alignment = header_alignment
40.         cell.border = thin_border
41.    
42.     # 设置数据行的格式
43.     data_font = Font(name='Arial', size=10)
44.     data_alignment = Alignment(horizontal='center', vertical='center')
45.    
46.     # 应用格式到数据行
47.     for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row):
48.         for cell in row:
49.             cell.font = data_font
50.             cell.alignment = data_alignment
51.             cell.border = thin_border
52.    
53.     # 设置列宽
54.     worksheet.column_dimensions['A'].width = 10
55.     worksheet.column_dimensions['B'].width = 10
56.     worksheet.column_dimensions['C'].width = 10
57.    
58.     # 设置价格列为货币格式
59.     for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row, min_col=2, max_col=2):
60.         for cell in row:
61.             cell.number_format = '"¥"#,##0.00'

复制代码

2. import pandas as pd
4. # 创建DataFrame
5. data = {
6.     '产品': ['A', 'B', 'C', 'D'],
7.     '价格': [12.5, 23.756, 8.99, 15.0],
8.     '销量': [100, 150, 80, 200]
9. }
10. df = pd.DataFrame(data)
12. # 创建ExcelWriter对象
13. with pd.ExcelWriter('formatted_output_xlsxwriter.xlsx', engine='xlsxwriter') as writer:
14.     df.to_excel(writer, sheet_name='产品信息', index=False, startrow=1)
15.    
16.     # 获取工作簿和工作表对象
17.     workbook = writer.book
18.     worksheet = writer.sheets['产品信息']
19.    
20.     # 定义格式
21.     header_format = workbook.add_format({
22.         'bold': True,
23.         'text_wrap': True,
24.         'valign': 'top',
25.         'fg_color': '#4F81BD',
26.         'font_color': 'white',
27.         'border': 1,
28.         'align': 'center'
29.     })
30.    
31.     data_format = workbook.add_format({
32.         'border': 1,
33.         'align': 'center'
34.     })
35.    
36.     price_format = workbook.add_format({
37.         'num_format': '¥#,##0.00',
38.         'border': 1,
39.         'align': 'center'
40.     })
41.    
42.     # 写入标题行
43.     for col_num, value in enumerate(df.columns.values):
44.         worksheet.write(0, col_num, value, header_format)
45.    
46.     # 应用格式到数据行
47.     for row_num in range(1, len(df) + 1):
48.         # 产品列
49.         worksheet.write(row_num, 0, df.iloc[row_num-1, 0], data_format)
50.         # 价格列
51.         worksheet.write(row_num, 1, df.iloc[row_num-1, 1], price_format)
52.         # 销量列
53.         worksheet.write(row_num, 2, df.iloc[row_num-1, 2], data_format)
54.    
55.     # 设置列宽
56.     worksheet.set_column('A:C', 12)

复制代码

条件格式设置

条件格式可以根据单元格的值自动应用不同的格式，使数据更加直观。

2. import pandas as pd
3. from openpyxl import load_workbook
4. from openpyxl.styles import PatternFill
5. from openpyxl.formatting.rule import CellIsRule
7. # 创建DataFrame
8. data = {
9.     '产品': ['A', 'B', 'C', 'D'],
10.     '价格': [12.5, 23.756, 8.99, 15.0],
11.     '销量': [100, 150, 80, 200]
12. }
13. df = pd.DataFrame(data)
15. # 导出到Excel
16. with pd.ExcelWriter('conditional_formatting.xlsx', engine='openpyxl') as writer:
17.     df.to_excel(writer, sheet_name='产品信息', index=False)
18.    
19.     # 获取工作簿和工作表对象
20.     workbook = writer.book
21.     worksheet = writer.sheets['产品信息']
22.    
23.     # 定义条件格式
24.     # 价格大于15的单元格填充为绿色
25.     green_fill = PatternFill(start_color='90EE90', end_color='90EE90', fill_type='solid')
26.     worksheet.conditional_formatting.add(
27.         'B2:B5',
28.         CellIsRule(operator='greaterThan', formula=['15'], fill=green_fill)
29.     )
30.    
31.     # 销量大于等于150的单元格填充为蓝色
32.     blue_fill = PatternFill(start_color='ADD8E6', end_color='ADD8E6', fill_type='solid')
33.     worksheet.conditional_formatting.add(
34.         'C2:C5',
35.         CellIsRule(operator='greaterThanOrEqual', formula=['150'], fill=blue_fill)
36.     )

复制代码

2. import pandas as pd
4. # 创建DataFrame
5. data = {
6.     '产品': ['A', 'B', 'C', 'D'],
7.     '价格': [12.5, 23.756, 8.99, 15.0],
8.     '销量': [100, 150, 80, 200]
9. }
10. df = pd.DataFrame(data)
12. # 创建ExcelWriter对象
13. with pd.ExcelWriter('conditional_formatting_xlsxwriter.xlsx', engine='xlsxwriter') as writer:
14.     df.to_excel(writer, sheet_name='产品信息', index=False)
15.    
16.     # 获取工作簿和工作表对象
17.     workbook = writer.book
18.     worksheet = writer.sheets['产品信息']
19.    
20.     # 添加条件格式
21.     # 价格大于15的单元格填充为绿色
22.     worksheet.conditional_format(
23.         'B2:B5',  # 应用范围
24.         {
25.             'type': 'cell',
26.             'criteria': '>',
27.             'value': 15,
28.             'format': workbook.add_format({'bg_color': '#90EE90'})
29.         }
30.     )
31.    
32.     # 销量大于等于150的单元格填充为蓝色
33.     worksheet.conditional_format(
34.         'C2:C5',  # 应用范围
35.         {
36.             'type': 'cell',
37.             'criteria': '>=',
38.             'value': 150,
39.             'format': workbook.add_format({'bg_color': '#ADD8E6'})
40.         }
41.     )
42.    
43.     # 添加数据条
44.     worksheet.conditional_format(
45.         'C2:C5',  # 应用范围
46.         {
47.             'type': 'data_bar',
48.             'bar_color': '#63C384'
49.         }
50.     )

复制代码

多表导出

在实际应用中，我们经常需要将多个DataFrame导出到同一个Excel文件的不同工作表中。

基本多表导出

2. import pandas as pd
4. # 创建多个DataFrame
5. data1 = {
6.     '产品': ['A', 'B', 'C', 'D'],
7.     '价格': [12.5, 23.756, 8.99, 15.0],
8.     '销量': [100, 150, 80, 200]
9. }
10. df1 = pd.DataFrame(data1)
12. data2 = {
13.     '月份': ['1月', '2月', '3月', '4月'],
14.     '收入': [12500, 23756, 8990, 15000],
15.     '成本': [8000, 15000, 6000, 10000]
16. }
17. df2 = pd.DataFrame(data2)
19. data3 = {
20.     '地区': ['北京', '上海', '广州', '深圳'],
21.     '客户数': [120, 150, 80, 200],
22.     '平均消费': [500, 600, 450, 700]
23. }
24. df3 = pd.DataFrame(data3)
26. # 导出到同一个Excel文件的不同工作表
27. with pd.ExcelWriter('multiple_sheets.xlsx') as writer:
28.     df1.to_excel(writer, sheet_name='产品信息', index=False)
29.     df2.to_excel(writer, sheet_name='财务数据', index=False)
30.     df3.to_excel(writer, sheet_name='地区统计', index=False)

复制代码

多表导出并设置格式

2. import pandas as pd
4. # 创建多个DataFrame
5. data1 = {
6.     '产品': ['A', 'B', 'C', 'D'],
7.     '价格': [12.5, 23.756, 8.99, 15.0],
8.     '销量': [100, 150, 80, 200]
9. }
10. df1 = pd.DataFrame(data1)
12. data2 = {
13.     '月份': ['1月', '2月', '3月', '4月'],
14.     '收入': [12500, 23756, 8990, 15000],
15.     '成本': [8000, 15000, 6000, 10000]
16. }
17. df2 = pd.DataFrame(data2)
19. # 创建ExcelWriter对象
20. with pd.ExcelWriter('multiple_sheets_formatted.xlsx', engine='xlsxwriter') as writer:
21.     # 导出第一个DataFrame
22.     df1.to_excel(writer, sheet_name='产品信息', index=False)
23.    
24.     # 获取工作簿和第一个工作表对象
25.     workbook = writer.book
26.     worksheet1 = writer.sheets['产品信息']
27.    
28.     # 定义格式
29.     header_format = workbook.add_format({
30.         'bold': True,
31.         'text_wrap': True,
32.         'valign': 'top',
33.         'fg_color': '#4F81BD',
34.         'font_color': 'white',
35.         'border': 1,
36.         'align': 'center'
37.     })
38.    
39.     data_format = workbook.add_format({
40.         'border': 1,
41.         'align': 'center'
42.     })
43.    
44.     price_format = workbook.add_format({
45.         'num_format': '¥#,##0.00',
46.         'border': 1,
47.         'align': 'center'
48.     })
49.    
50.     # 设置第一个工作表的格式
51.     for col_num, value in enumerate(df1.columns.values):
52.         worksheet1.write(0, col_num, value, header_format)
53.    
54.     for row_num in range(1, len(df1) + 1):
55.         worksheet1.write(row_num, 0, df1.iloc[row_num-1, 0], data_format)
56.         worksheet1.write(row_num, 1, df1.iloc[row_num-1, 1], price_format)
57.         worksheet1.write(row_num, 2, df1.iloc[row_num-1, 2], data_format)
58.    
59.     worksheet1.set_column('A:C', 12)
60.    
61.     # 导出第二个DataFrame
62.     df2.to_excel(writer, sheet_name='财务数据', index=False)
63.    
64.     # 获取第二个工作表对象
65.     worksheet2 = writer.sheets['财务数据']
66.    
67.     # 设置第二个工作表的格式
68.     for col_num, value in enumerate(df2.columns.values):
69.         worksheet2.write(0, col_num, value, header_format)
70.    
71.     currency_format = workbook.add_format({
72.         'num_format': '¥#,##0',
73.         'border': 1,
74.         'align': 'center'
75.     })
76.    
77.     for row_num in range(1, len(df2) + 1):
78.         worksheet2.write(row_num, 0, df2.iloc[row_num-1, 0], data_format)
79.         worksheet2.write(row_num, 1, df2.iloc[row_num-1, 1], currency_format)
80.         worksheet2.write(row_num, 2, df2.iloc[row_num-1, 2], currency_format)
81.    
82.     worksheet2.set_column('A:C', 12)
83.    
84.     # 添加一个汇总工作表
85.     summary_data = {
86.         '指标': ['总产品数', '平均价格', '总销量', '总收入', '总成本'],
87.         '值': [
88.             len(df1),
89.             df1['价格'].mean(),
90.             df1['销量'].sum(),
91.             df2['收入'].sum(),
92.             df2['成本'].sum()
93.         ]
94.     }
95.     df_summary = pd.DataFrame(summary_data)
96.    
97.     df_summary.to_excel(writer, sheet_name='汇总', index=False)
98.    
99.     # 获取汇总工作表对象
100.     worksheet_summary = writer.sheets['汇总']
101.    
102.     # 设置汇总工作表的格式
103.     for col_num, value in enumerate(df_summary.columns.values):
104.         worksheet_summary.write(0, col_num, value, header_format)
105.    
106.     value_format = workbook.add_format({
107.         'num_format': '#,##0.00',
108.         'border': 1,
109.         'align': 'center'
110.     })
111.    
112.     for row_num in range(1, len(df_summary) + 1):
113.         worksheet_summary.write(row_num, 0, df_summary.iloc[row_num-1, 0], data_format)
114.         worksheet_summary.write(row_num, 1, df_summary.iloc[row_num-1, 1], value_format)
115.    
116.     worksheet_summary.set_column('A:B', 15)

复制代码

数据处理技巧

在将数据导出到Excel之前，我们通常需要进行一些数据处理操作。下面是一些常见的数据处理技巧。

数据清洗

2. import pandas as pd
3. import numpy as np
5. # 创建包含脏数据的DataFrame
6. data = {
7.     '产品': ['A', 'B', 'C', 'D', 'E', 'F'],
8.     '价格': [12.5, 23.756, 8.99, 15.0, np.nan, 18.5],
9.     '销量': [100, 150, 80, 200, 120, None],
10.     '分类': ['电子产品', '家电', '电子产品', '家电', '家具', '家具']
11. }
12. df = pd.DataFrame(data)
14. print("原始数据:")
15. print(df)
17. # 1. 处理缺失值
18. # 删除包含缺失值的行
19. df_dropna = df.dropna()
20. print("\n删除缺失值后的数据:")
21. print(df_dropna)
23. # 填充缺失值
24. df_fillna = df.fillna({
25.     '价格': df['价格'].mean(),  # 用平均价格填充缺失的价格
26.     '销量': 0                   # 用0填充缺失的销量
27. })
28. print("\n填充缺失值后的数据:")
29. print(df_fillna)
31. # 2. 处理重复值
32. # 添加一些重复行
33. df_with_duplicates = pd.concat([df, df.iloc[0:2]], ignore_index=True)
34. print("\n包含重复值的数据:")
35. print(df_with_duplicates)
37. # 删除重复行
38. df_drop_duplicates = df_with_duplicates.drop_duplicates()
39. print("\n删除重复值后的数据:")
40. print(df_drop_duplicates)
42. # 3. 数据类型转换
43. # 将价格转换为整数
44. df['价格_整数'] = df['价格'].astype('Int64')  # Int64支持NaN值
45. print("\n转换数据类型后的数据:")
46. print(df)
48. # 4. 字符串处理
49. # 在产品名称前添加"产品-"前缀
50. df['产品_新名称'] = '产品-' + df['产品']
51. print("\n字符串处理后的数据:")
52. print(df)
54. # 5. 数据筛选
55. # 筛选价格大于15的产品
56. df_filtered = df[df['价格'] > 15]
57. print("\n价格大于15的产品:")
58. print(df_filtered)
60. # 6. 数据分组
61. # 按分类分组并计算平均价格和总销量
62. df_grouped = df.groupby('分类').agg({
63.     '价格': 'mean',
64.     '销量': 'sum'
65. })
66. print("\n按分类分组统计:")
67. print(df_grouped)

复制代码

数据转换和计算

2. import pandas as pd
4. # 创建DataFrame
5. data = {
6.     '产品': ['A', 'B', 'C', 'D'],
7.     '成本': [10, 15, 8, 12],
8.     '售价': [15, 20, 12, 18],
9.     '销量': [100, 150, 80, 200]
10. }
11. df = pd.DataFrame(data)
13. print("原始数据:")
14. print(df)
16. # 1. 添加计算列
17. # 计算利润
18. df['利润'] = df['售价'] - df['成本']
19. # 计算利润率
20. df['利润率'] = (df['利润'] / df['成本']) * 100
21. # 计算总收入
22. df['总收入'] = df['售价'] * df['销量']
23. # 计算总利润
24. df['总利润'] = df['利润'] * df['销量']
26. print("\n添加计算列后的数据:")
27. print(df)
29. # 2. 应用函数
30. # 定义一个函数来评估产品表现
31. def evaluate_performance(row):
32.     if row['利润率'] > 50:
33.         return '优秀'
34.     elif row['利润率'] > 30:
35.         return '良好'
36.     elif row['利润率'] > 10:
37.         return '一般'
38.     else:
39.         return '较差'
41. # 应用函数
42. df['表现评估'] = df.apply(evaluate_performance, axis=1)
44. print("\n应用函数后的数据:")
45. print(df)
47. # 3. 数据排序
48. # 按总利润降序排序
49. df_sorted = df.sort_values('总利润', ascending=False)
51. print("\n按总利润降序排序:")
52. print(df_sorted)
54. # 4. 数据排名
55. # 添加利润排名列
56. df['利润排名'] = df['总利润'].rank(ascending=False, method='dense').astype(int)
58. print("\n添加排名后的数据:")
59. print(df)
61. # 5. 数据透视表
62. # 创建一个更大的数据集以便演示透视表
63. data_large = {
64.     '产品': ['A', 'B', 'C', 'D', 'A', 'B', 'C', 'D', 'A', 'B', 'C', 'D'],
65.     '地区': ['北京', '北京', '北京', '北京', '上海', '上海', '上海', '上海', '广州', '广州', '广州', '广州'],
66.     '季度': ['Q1', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1'],
67.     '销量': [100, 150, 80, 200, 120, 180, 90, 220, 80, 140, 70, 190],
68.     '收入': [1500, 3000, 960, 3600, 1800, 3600, 1080, 3960, 1200, 2800, 840, 3420]
69. }
70. df_large = pd.DataFrame(data_large)
72. # 创建透视表
73. pivot_table = pd.pivot_table(
74.     df_large,
75.     values=['销量', '收入'],
76.     index=['地区'],
77.     columns=['产品'],
78.     aggfunc='sum',
79.     fill_value=0
80. )
82. print("\n数据透视表:")
83. print(pivot_table)

复制代码

数据合并

2. import pandas as pd
4. # 创建两个DataFrame
5. df1 = pd.DataFrame({
6.     '产品ID': [1, 2, 3, 4],
7.     '产品名称': ['A', 'B', 'C', 'D'],
8.     '成本': [10, 15, 8, 12]
9. })
11. df2 = pd.DataFrame({
12.     '产品ID': [1, 2, 3, 4, 5],  # 注意这里多了一个产品ID为5的行
13.     '售价': [15, 20, 12, 18, 25],
14.     '分类': ['电子产品', '家电', '电子产品', '家电', '家具']
15. })
17. print("DataFrame 1:")
18. print(df1)
20. print("\nDataFrame 2:")
21. print(df2)
23. # 1. 内连接 (inner join)
24. # 只保留两个DataFrame中都存在的产品ID
25. df_inner = pd.merge(df1, df2, on='产品ID', how='inner')
26. print("\n内连接结果:")
27. print(df_inner)
29. # 2. 左连接 (left join)
30. # 保留左边的DataFrame中的所有行
31. df_left = pd.merge(df1, df2, on='产品ID', how='left')
32. print("\n左连接结果:")
33. print(df_left)
35. # 3. 右连接 (right join)
36. # 保留右边的DataFrame中的所有行
37. df_right = pd.merge(df1, df2, on='产品ID', how='right')
38. print("\n右连接结果:")
39. print(df_right)
41. # 4. 外连接 (outer join)
42. # 保留两个DataFrame中的所有行
43. df_outer = pd.merge(df1, df2, on='产品ID', how='outer')
44. print("\n外连接结果:")
45. print(df_outer)
47. # 5. 纵向合并 (concat)
48. # 创建两个具有相同列的DataFrame
49. df3 = pd.DataFrame({
50.     '产品ID': [5, 6],
51.     '产品名称': ['E', 'F'],
52.     '成本': [18, 20]
53. })
55. # 纵向合并
56. df_concat = pd.concat([df1, df3], ignore_index=True)
57. print("\n纵向合并结果:")
58. print(df_concat)

复制代码

高级技巧

使用Excel公式

2. import pandas as pd
4. # 创建DataFrame
5. data = {
6.     '产品': ['A', 'B', 'C', 'D'],
7.     '成本': [10, 15, 8, 12],
8.     '售价': [15, 20, 12, 18],
9.     '销量': [100, 150, 80, 200]
10. }
11. df = pd.DataFrame(data)
13. # 创建ExcelWriter对象
14. with pd.ExcelWriter('excel_formulas.xlsx', engine='xlsxwriter') as writer:
15.     # 导出DataFrame
16.     df.to_excel(writer, sheet_name='产品信息', index=False)
17.    
18.     # 获取工作簿和工作表对象
19.     workbook = writer.book
20.     worksheet = writer.sheets['产品信息']
21.    
22.     # 添加公式列
23.     # 添加利润列（售价-成本）
24.     worksheet.write(1, 5, '利润')  # 列标题
25.     for row_num in range(2, len(df) + 2):
26.         worksheet.write_formula(
27.             row_num, 5,  # F列
28.             f'=C{row_num}-B{row_num}',  # 售价-成本
29.             workbook.add_format({'num_format': '#,##0.00'})
30.         )
31.    
32.     # 添加利润率列（利润/成本）
33.     worksheet.write(1, 6, '利润率')  # 列标题
34.     for row_num in range(2, len(df) + 2):
35.         worksheet.write_formula(
36.             row_num, 6,  # G列
37.             f'=F{row_num}/B{row_num}',  # 利润/成本
38.             workbook.add_format({'num_format': '0.00%'})
39.         )
40.    
41.     # 添加总收入列（售价*销量）
42.     worksheet.write(1, 7, '总收入')  # 列标题
43.     for row_num in range(2, len(df) + 2):
44.         worksheet.write_formula(
45.             row_num, 7,  # H列
46.             f'=C{row_num}*D{row_num}',  # 售价*销量
47.             workbook.add_format({'num_format': '¥#,##0'})
48.         )
49.    
50.     # 添加总利润列（利润*销量）
51.     worksheet.write(1, 8, '总利润')  # 列标题
52.     for row_num in range(2, len(df) + 2):
53.         worksheet.write_formula(
54.             row_num, 8,  # I列
55.             f'=F{row_num}*D{row_num}',  # 利润*销量
56.             workbook.add_format({'num_format': '¥#,##0'})
57.         )
58.    
59.     # 添加汇总行
60.     last_row = len(df) + 2
61.     worksheet.write(last_row, 0, '总计')
62.    
63.     # 使用SUM函数计算总销量
64.     worksheet.write_formula(
65.         last_row, 3,  # D列
66.         f'=SUM(D2:D{last_row-1})',
67.         workbook.add_format({'bold': True, 'num_format': '#,##0'})
68.     )
69.    
70.     # 使用SUM函数计算总收入
71.     worksheet.write_formula(
72.         last_row, 7,  # H列
73.         f'=SUM(H2:H{last_row-1})',
74.         workbook.add_format({'bold': True, 'num_format': '¥#,##0'})
75.     )
76.    
77.     # 使用SUM函数计算总利润
78.     worksheet.write_formula(
79.         last_row, 8,  # I列
80.         f'=SUM(I2:I{last_row-1})',
81.         workbook.add_format({'bold': True, 'num_format': '¥#,##0'})
82.     )
83.    
84.     # 设置列宽
85.     worksheet.set_column('A:A', 10)
86.     worksheet.set_column('B:I', 12)

复制代码

添加图表

2. import pandas as pd
4. # 创建DataFrame
5. data = {
6.     '月份': ['1月', '2月', '3月', '4月', '5月', '6月'],
7.     '销售额': [12000, 15000, 18000, 14000, 20000, 22000],
8.     '成本': [8000, 10000, 12000, 9000, 13000, 14000],
9.     '利润': [4000, 5000, 6000, 5000, 7000, 8000]
10. }
11. df = pd.DataFrame(data)
13. # 创建ExcelWriter对象
14. with pd.ExcelWriter('excel_charts.xlsx', engine='xlsxwriter') as writer:
15.     # 导出DataFrame
16.     df.to_excel(writer, sheet_name='销售数据', index=False)
17.    
18.     # 获取工作簿和工作表对象
19.     workbook = writer.book
20.     worksheet = writer.sheets['销售数据']
21.    
22.     # 定义图表格式
23.     chart_format = workbook.add_format({
24.         'bold': True,
25.         'font_color': 'white',
26.         'bg_color': '#4F81BD',
27.         'border': 1,
28.         'align': 'center'
29.     })
30.    
31.     # 设置标题行格式
32.     for col_num, value in enumerate(df.columns.values):
33.         worksheet.write(0, col_num, value, chart_format)
34.    
35.     # 设置数据行格式
36.     data_format = workbook.add_format({
37.         'border': 1,
38.         'align': 'center'
39.     })
40.    
41.     currency_format = workbook.add_format({
42.         'num_format': '¥#,##0',
43.         'border': 1,
44.         'align': 'center'
45.     })
46.    
47.     for row_num in range(1, len(df) + 1):
48.         worksheet.write(row_num, 0, df.iloc[row_num-1, 0], data_format)
49.         worksheet.write(row_num, 1, df.iloc[row_num-1, 1], currency_format)
50.         worksheet.write(row_num, 2, df.iloc[row_num-1, 2], currency_format)
51.         worksheet.write(row_num, 3, df.iloc[row_num-1, 3], currency_format)
52.    
53.     # 设置列宽
54.     worksheet.set_column('A:D', 12)
55.    
56.     # 创建柱状图
57.     chart_column = workbook.add_chart({'type': 'column'})
58.    
59.     # 配置柱状图数据系列
60.     chart_column.add_series({
61.         'name': '销售数据!$B$1',
62.         'categories': '销售数据!$A$2:$A$7',
63.         'values': '销售数据!$B$2:$B$7',
64.         'fill': {'color': '#4F81BD'},
65.     })
66.    
67.     chart_column.add_series({
68.         'name': '销售数据!$C$1',
69.         'categories': '销售数据!$A$2:$A$7',
70.         'values': '销售数据!$C$2:$C$7',
71.         'fill': {'color': '#9BBB59'},
72.     })
73.    
74.     chart_column.add_series({
75.         'name': '销售数据!$D$1',
76.         'categories': '销售数据!$A$2:$A$7',
77.         'values': '销售数据!$D$2:$D$7',
78.         'fill': {'color': '#C0504D'},
79.     })
80.    
81.     # 设置图表标题和样式
82.     chart_column.set_title({'name': '月度销售数据'})
83.     chart_column.set_x_axis({'name': '月份'})
84.     chart_column.set_y_axis({'name': '金额 (¥)'})
85.     chart_column.set_legend({'position': 'top'})
86.    
87.     # 插入柱状图
88.     worksheet.insert_chart('F2', chart_column, {'x_offset': 25, 'y_offset': 10})
89.    
90.     # 创建折线图
91.     chart_line = workbook.add_chart({'type': 'line'})
92.    
93.     # 配置折线图数据系列
94.     chart_line.add_series({
95.         'name': '销售数据!$B$1',
96.         'categories': '销售数据!$A$2:$A$7',
97.         'values': '销售数据!$B$2:$B$7',
98.         'line': {'color': '#4F81BD', 'width': 3},
99.         'marker': {'type': 'circle', 'size': 6, 'border': {'color': '#4F81BD'}, 'fill': {'color': '#4F81BD'}}
100.     })
101.    
102.     chart_line.add_series({
103.         'name': '销售数据!$C$1',
104.         'categories': '销售数据!$A$2:$A$7',
105.         'values': '销售数据!$C$2:$C$7',
106.         'line': {'color': '#9BBB59', 'width': 3},
107.         'marker': {'type': 'square', 'size': 6, 'border': {'color': '#9BBB59'}, 'fill': {'color': '#9BBB59'}}
108.     })
109.    
110.     chart_line.add_series({
111.         'name': '销售数据!$D$1',
112.         'categories': '销售数据!$A$2:$A$7',
113.         'values': '销售数据!$D$2:$D$7',
114.         'line': {'color': '#C0504D', 'width': 3},
115.         'marker': {'type': 'triangle', 'size': 6, 'border': {'color': '#C0504D'}, 'fill': {'color': '#C0504D'}}
116.     })
117.    
118.     # 设置图表标题和样式
119.     chart_line.set_title({'name': '月度销售趋势'})
120.     chart_line.set_x_axis({'name': '月份'})
121.     chart_line.set_y_axis({'name': '金额 (¥)'})
122.     chart_line.set_legend({'position': 'top'})
123.    
124.     # 插入折线图
125.     worksheet.insert_chart('F20', chart_line, {'x_offset': 25, 'y_offset': 10})
126.    
127.     # 创建饼图
128.     chart_pie = workbook.add_chart({'type': 'pie'})
129.    
130.     # 配置饼图数据系列
131.     chart_pie.add_series({
132.         'name': '销售数据!$B$1',
133.         'categories': '销售数据!$A$2:$A$7',
134.         'values': '销售数据!$B$2:$B$7',
135.         'data_labels': {'value': True, 'percentage': True},
136.     })
137.    
138.     # 设置图表标题和样式
139.     chart_pie.set_title({'name': '销售额分布'})
140.     chart_pie.set_legend({'position': 'right'})
141.    
142.     # 插入饼图
143.     worksheet.insert_chart('F38', chart_pie, {'x_offset': 25, 'y_offset': 10})

复制代码

数据验证

2. import pandas as pd
4. # 创建DataFrame
5. data = {
6.     '产品': ['A', 'B', 'C', 'D'],
7.     '价格': [12.5, 23.756, 8.99, 15.0],
8.     '库存': [100, 150, 80, 200]
9. }
10. df = pd.DataFrame(data)
12. # 创建ExcelWriter对象
13. with pd.ExcelWriter('data_validation.xlsx', engine='xlsxwriter') as writer:
14.     # 导出DataFrame
15.     df.to_excel(writer, sheet_name='产品信息', index=False)
16.    
17.     # 获取工作簿和工作表对象
18.     workbook = writer.book
19.     worksheet = writer.sheets['产品信息']
20.    
21.     # 设置标题行格式
22.     header_format = workbook.add_format({
23.         'bold': True,
24.         'text_wrap': True,
25.         'valign': 'top',
26.         'fg_color': '#4F81BD',
27.         'font_color': 'white',
28.         'border': 1,
29.         'align': 'center'
30.     })
31.    
32.     # 设置数据行格式
33.     data_format = workbook.add_format({
34.         'border': 1,
35.         'align': 'center'
36.     })
37.    
38.     # 应用格式
39.     for col_num, value in enumerate(df.columns.values):
40.         worksheet.write(0, col_num, value, header_format)
41.    
42.     for row_num in range(1, len(df) + 1):
43.         for col_num in range(len(df.columns)):
44.             worksheet.write(row_num, col_num, df.iloc[row_num-1, col_num], data_format)
45.    
46.     # 设置列宽
47.     worksheet.set_column('A:C', 12)
48.    
49.     # 添加数据验证
50.     # 1. 产品列：下拉列表
51.     worksheet.data_validation(
52.         'A2:A100',  # 应用范围
53.         {
54.             'validate': 'list',
55.             'source': ['A', 'B', 'C', 'D', 'E', 'F'],
56.             'input_title': '选择产品',
57.             'input_message': '请从下拉列表中选择产品',
58.         }
59.     )
60.    
61.     # 2. 价格列：数值范围
62.     worksheet.data_validation(
63.         'B2:B100',  # 应用范围
64.         {
65.             'validate': 'decimal',
66.             'criteria': 'between',
67.             'minimum': 0,
68.             'maximum': 100,
69.             'input_title': '输入价格',
70.             'input_message': '请输入0到100之间的价格',
71.             'error_title': '输入错误',
72.             'error_message': '价格必须在0到100之间'
73.         }
74.     )
75.    
76.     # 3. 库存列：整数范围
77.     worksheet.data_validation(
78.         'C2:C100',  # 应用范围
79.         {
80.             'validate': 'integer',
81.             'criteria': 'between',
82.             'minimum': 0,
83.             'maximum': 1000,
84.             'input_title': '输入库存',
85.             'input_message': '请输入0到1000之间的整数库存',
86.             'error_title': '输入错误',
87.             'error_message': '库存必须是0到1000之间的整数'
88.         }
89.     )
90.    
91.     # 添加一个新工作表，用于数据输入
92.     worksheet_input = workbook.add_worksheet('数据输入')
93.    
94.     # 设置表头
95.     headers = ['产品', '价格', '库存']
96.     for col_num, header in enumerate(headers):
97.         worksheet_input.write(0, col_num, header, header_format)
98.    
99.     # 设置列宽
100.     worksheet_input.set_column('A:C', 12)
101.    
102.     # 应用数据验证
103.     # 产品列：下拉列表
104.     worksheet_input.data_validation(
105.         'A2:A100',  # 应用范围
106.         {
107.             'validate': 'list',
108.             'source': ['A', 'B', 'C', 'D', 'E', 'F'],
109.             'input_title': '选择产品',
110.             'input_message': '请从下拉列表中选择产品',
111.         }
112.     )
113.    
114.     # 价格列：数值范围
115.     worksheet_input.data_validation(
116.         'B2:B100',  # 应用范围
117.         {
118.             'validate': 'decimal',
119.             'criteria': 'between',
120.             'minimum': 0,
121.             'maximum': 100,
122.             'input_title': '输入价格',
123.             'input_message': '请输入0到100之间的价格',
124.             'error_title': '输入错误',
125.             'error_message': '价格必须在0到100之间'
126.         }
127.     )
128.    
129.     # 库存列：整数范围
130.     worksheet_input.data_validation(
131.         'C2:C100',  # 应用范围
132.         {
133.             'validate': 'integer',
134.             'criteria': 'between',
135.             'minimum': 0,
136.             'maximum': 1000,
137.             'input_title': '输入库存',
138.             'input_message': '请输入0到1000之间的整数库存',
139.             'error_title': '输入错误',
140.             'error_message': '库存必须是0到1000之间的整数'
141.         }
142.     )
143.    
144.     # 添加一些示例数据
145.     worksheet_input.write(1, 0, 'A', data_format)
146.     worksheet_input.write(1, 1, 15.5, data_format)
147.     worksheet_input.write(1, 2, 100, data_format)
148.    
149.     worksheet_input.write(2, 0, 'B', data_format)
150.     worksheet_input.write(2, 1, 25.0, data_format)
151.     worksheet_input.write(2, 2, 150, data_format)

复制代码

实际案例

下面是一个完整的实际案例，展示如何从数据收集、处理、分析到最终导出为格式化的Excel报告的整个过程。

2. import pandas as pd
3. import numpy as np
4. from datetime import datetime, timedelta
6. # 1. 数据生成和收集
7. # 模拟生成销售数据
8. def generate_sales_data(start_date, end_date):
9.     date_range = pd.date_range(start=start_date, end=end_date)
10.     products = ['A', 'B', 'C', 'D', 'E']
11.     regions = ['北京', '上海', '广州', '深圳', '成都']
12.    
13.     data = []
14.     for date in date_range:
15.         for product in products:
16.             for region in regions:
17.                 # 生成随机数据
18.                 base_price = np.random.uniform(10, 50)
19.                 quantity = np.random.randint(10, 100)
20.                 discount = np.random.choice([0, 0.05, 0.1, 0.15, 0.2], p=[0.5, 0.2, 0.15, 0.1, 0.05])
21.                
22.                 # 计算销售额和利润
23.                 cost = base_price * 0.6  # 成本是售价的60%
24.                 price = base_price * (1 - discount)
25.                 sales = price * quantity
26.                 profit = (price - cost) * quantity
27.                
28.                 data.append({
29.                     '日期': date,
30.                     '产品': product,
31.                     '地区': region,
32.                     '成本': cost,
33.                     '售价': price,
34.                     '折扣': discount,
35.                     '销量': quantity,
36.                     '销售额': sales,
37.                     '利润': profit
38.                 })
39.    
40.     return pd.DataFrame(data)
42. # 生成过去一年的销售数据
43. end_date = datetime.now().date()
44. start_date = end_date - timedelta(days=365)
45. sales_data = generate_sales_data(start_date, end_date)
47. print("生成的销售数据示例:")
48. print(sales_data.head())
50. # 2. 数据清洗和预处理
51. # 检查缺失值
52. print("\n缺失值检查:")
53. print(sales_data.isnull().sum())
55. # 检查数据类型
56. print("\n数据类型:")
57. print(sales_data.dtypes)
59. # 转换日期列为datetime类型
60. sales_data['日期'] = pd.to_datetime(sales_data['日期'])
62. # 添加月份和季度列
63. sales_data['月份'] = sales_data['日期'].dt.to_period('M')
64. sales_data['季度'] = sales_data['日期'].dt.to_period('Q')
66. print("\n添加月份和季度后的数据示例:")
67. print(sales_data.head())
69. # 3. 数据分析
70. # 按产品分析
71. product_analysis = sales_data.groupby('产品').agg({
72.     '销量': 'sum',
73.     '销售额': 'sum',
74.     '利润': 'sum',
75.     '折扣': 'mean'
76. }).reset_index()
78. # 添加利润率列
79. product_analysis['利润率'] = (product_analysis['利润'] / product_analysis['销售额']) * 100
81. print("\n产品分析:")
82. print(product_analysis)
84. # 按地区分析
85. region_analysis = sales_data.groupby('地区').agg({
86.     '销量': 'sum',
87.     '销售额': 'sum',
88.     '利润': 'sum'
89. }).reset_index()
91. # 添加利润率列
92. region_analysis['利润率'] = (region_analysis['利润'] / region_analysis['销售额']) * 100
94. print("\n地区分析:")
95. print(region_analysis)
97. # 按月份分析
98. monthly_analysis = sales_data.groupby('月份').agg({
99.     '销量': 'sum',
100.     '销售额': 'sum',
101.     '利润': 'sum'
102. }).reset_index()
104. # 将月份转换为字符串格式
105. monthly_analysis['月份'] = monthly_analysis['月份'].astype(str)
107. # 添加利润率列
108. monthly_analysis['利润率'] = (monthly_analysis['利润'] / monthly_analysis['销售额']) * 100
110. print("\n月度分析:")
111. print(monthly_analysis.head())
113. # 按季度分析
114. quarterly_analysis = sales_data.groupby('季度').agg({
115.     '销量': 'sum',
116.     '销售额': 'sum',
117.     '利润': 'sum'
118. }).reset_index()
120. # 将季度转换为字符串格式
121. quarterly_analysis['季度'] = quarterly_analysis['季度'].astype(str)
123. # 添加利润率列
124. quarterly_analysis['利润率'] = (quarterly_analysis['利润'] / quarterly_analysis['销售额']) * 100
126. print("\n季度分析:")
127. print(quarterly_analysis)
129. # 4. 导出Excel报告
130. # 创建ExcelWriter对象
131. with pd.ExcelWriter('sales_report.xlsx', engine='xlsxwriter') as writer:
132.     # 定义格式
133.     header_format = writer.book.add_format({
134.         'bold': True,
135.         'text_wrap': True,
136.         'valign': 'top',
137.         'fg_color': '#4F81BD',
138.         'font_color': 'white',
139.         'border': 1,
140.         'align': 'center'
141.     })
142.    
143.     data_format = writer.book.add_format({
144.         'border': 1,
145.         'align': 'center'
146.     })
147.    
148.     currency_format = writer.book.add_format({
149.         'num_format': '¥#,##0',
150.         'border': 1,
151.         'align': 'center'
152.     })
153.    
154.     percentage_format = writer.book.add_format({
155.         'num_format': '0.00%',
156.         'border': 1,
157.         'align': 'center'
158.     })
159.    
160.     # 导出原始数据
161.     sales_data.to_excel(writer, sheet_name='原始数据', index=False)
162.     worksheet_raw = writer.sheets['原始数据']
163.    
164.     # 设置原始数据格式
165.     for col_num, value in enumerate(sales_data.columns.values):
166.         worksheet_raw.write(0, col_num, value, header_format)
167.    
168.     for row_num in range(1, len(sales_data) + 1):
169.         for col_num, col_name in enumerate(sales_data.columns):
170.             if col_name in ['成本', '售价', '销售额', '利润']:
171.                 worksheet_raw.write(row_num, col_num, sales_data.iloc[row_num-1, col_num], currency_format)
172.             elif col_name == '折扣':
173.                 worksheet_raw.write(row_num, col_num, sales_data.iloc[row_num-1, col_num], percentage_format)
174.             else:
175.                 worksheet_raw.write(row_num, col_num, sales_data.iloc[row_num-1, col_num], data_format)
176.    
177.     # 设置列宽
178.     worksheet_raw.set_column('A:A', 12)  # 日期
179.     worksheet_raw.set_column('B:B', 8)   # 产品
180.     worksheet_raw.set_column('C:C', 8)   # 地区
181.     worksheet_raw.set_column('D:G', 10)  # 成本、售价、折扣、销量
182.     worksheet_raw.set_column('H:I', 12)  # 销售额、利润
183.    
184.     # 导出产品分析
185.     product_analysis.to_excel(writer, sheet_name='产品分析', index=False)
186.     worksheet_product = writer.sheets['产品分析']
187.    
188.     # 设置产品分析格式
189.     for col_num, value in enumerate(product_analysis.columns.values):
190.         worksheet_product.write(0, col_num, value, header_format)
191.    
192.     for row_num in range(1, len(product_analysis) + 1):
193.         worksheet_product.write(row_num, 0, product_analysis.iloc[row_num-1, 0], data_format)  # 产品
194.         worksheet_product.write(row_num, 1, product_analysis.iloc[row_num-1, 1], data_format)  # 销量
195.         worksheet_product.write(row_num, 2, product_analysis.iloc[row_num-1, 2], currency_format)  # 销售额
196.         worksheet_product.write(row_num, 3, product_analysis.iloc[row_num-1, 3], currency_format)  # 利润
197.         worksheet_product.write(row_num, 4, product_analysis.iloc[row_num-1, 4], percentage_format)  # 利润率
198.    
199.     # 设置列宽
200.     worksheet_product.set_column('A:A', 8)   # 产品
201.     worksheet_product.set_column('B:B', 10)  # 销量
202.     worksheet_product.set_column('C:E', 12) # 销售额、利润、利润率
203.    
204.     # 添加产品分析图表
205.     chart_product = writer.book.add_chart({'type': 'column'})
206.    
207.     # 配置图表数据系列
208.     chart_product.add_series({
209.         'name': '产品分析!$C$1',
210.         'categories': '产品分析!$A$2:$A$6',
211.         'values': '产品分析!$C$2:$C$6',
212.         'fill': {'color': '#4F81BD'},
213.     })
214.    
215.     chart_product.add_series({
216.         'name': '产品分析!$D$1',
217.         'categories': '产品分析!$A$2:$A$6',
218.         'values': '产品分析!$D$2:$D$6',
219.         'fill': {'color': '#9BBB59'},
220.     })
221.    
222.     # 设置图表标题和样式
223.     chart_product.set_title({'name': '产品销售额与利润'})
224.     chart_product.set_x_axis({'name': '产品'})
225.     chart_product.set_y_axis({'name': '金额 (¥)'})
226.     chart_product.set_legend({'position': 'top'})
227.    
228.     # 插入图表
229.     worksheet_product.insert_chart('G2', chart_product, {'x_offset': 25, 'y_offset': 10})
230.    
231.     # 导出地区分析
232.     region_analysis.to_excel(writer, sheet_name='地区分析', index=False)
233.     worksheet_region = writer.sheets['地区分析']
234.    
235.     # 设置地区分析格式
236.     for col_num, value in enumerate(region_analysis.columns.values):
237.         worksheet_region.write(0, col_num, value, header_format)
238.    
239.     for row_num in range(1, len(region_analysis) + 1):
240.         worksheet_region.write(row_num, 0, region_analysis.iloc[row_num-1, 0], data_format)  # 地区
241.         worksheet_region.write(row_num, 1, region_analysis.iloc[row_num-1, 1], data_format)  # 销量
242.         worksheet_region.write(row_num, 2, region_analysis.iloc[row_num-1, 2], currency_format)  # 销售额
243.         worksheet_region.write(row_num, 3, region_analysis.iloc[row_num-1, 3], currency_format)  # 利润
244.         worksheet_region.write(row_num, 4, region_analysis.iloc[row_num-1, 4], percentage_format)  # 利润率
245.    
246.     # 设置列宽
247.     worksheet_region.set_column('A:A', 8)   # 地区
248.     worksheet_region.set_column('B:B', 10)  # 销量
249.     worksheet_region.set_column('C:E', 12) # 销售额、利润、利润率
250.    
251.     # 添加地区分析图表
252.     chart_region = writer.book.add_chart({'type': 'pie'})
253.    
254.     # 配置图表数据系列
255.     chart_region.add_series({
256.         'name': '地区分析!$C$1',
257.         'categories': '地区分析!$A$2:$A$6',
258.         'values': '地区分析!$C$2:$C$6',
259.         'data_labels': {'value': True, 'percentage': True},
260.     })
261.    
262.     # 设置图表标题和样式
263.     chart_region.set_title({'name': '地区销售额分布'})
264.     chart_region.set_legend({'position': 'right'})
265.    
266.     # 插入图表
267.     worksheet_region.insert_chart('G2', chart_region, {'x_offset': 25, 'y_offset': 10})
268.    
269.     # 导出月度分析
270.     monthly_analysis.to_excel(writer, sheet_name='月度分析', index=False)
271.     worksheet_monthly = writer.sheets['月度分析']
272.    
273.     # 设置月度分析格式
274.     for col_num, value in enumerate(monthly_analysis.columns.values):
275.         worksheet_monthly.write(0, col_num, value, header_format)
276.    
277.     for row_num in range(1, len(monthly_analysis) + 1):
278.         worksheet_monthly.write(row_num, 0, monthly_analysis.iloc[row_num-1, 0], data_format)  # 月份
279.         worksheet_monthly.write(row_num, 1, monthly_analysis.iloc[row_num-1, 1], data_format)  # 销量
280.         worksheet_monthly.write(row_num, 2, monthly_analysis.iloc[row_num-1, 2], currency_format)  # 销售额
281.         worksheet_monthly.write(row_num, 3, monthly_analysis.iloc[row_num-1, 3], currency_format)  # 利润
282.         worksheet_monthly.write(row_num, 4, monthly_analysis.iloc[row_num-1, 4], percentage_format)  # 利润率
283.    
284.     # 设置列宽
285.     worksheet_monthly.set_column('A:A', 10)  # 月份
286.     worksheet_monthly.set_column('B:B', 10)  # 销量
287.     worksheet_monthly.set_column('C:E', 12) # 销售额、利润、利润率
288.    
289.     # 添加月度分析图表
290.     chart_monthly = writer.book.add_chart({'type': 'line'})
291.    
292.     # 配置图表数据系列
293.     chart_monthly.add_series({
294.         'name': '月度分析!$C$1',
295.         'categories': '月度分析!$A$2:$A$13',
296.         'values': '月度分析!$C$2:$C$13',
297.         'line': {'color': '#4F81BD', 'width': 3},
298.         'marker': {'type': 'circle', 'size': 6, 'border': {'color': '#4F81BD'}, 'fill': {'color': '#4F81BD'}}
299.     })
300.    
301.     chart_monthly.add_series({
302.         'name': '月度分析!$D$1',
303.         'categories': '月度分析!$A$2:$A$13',
304.         'values': '月度分析!$D$2:$D$13',
305.         'line': {'color': '#9BBB59', 'width': 3},
306.         'marker': {'type': 'square', 'size': 6, 'border': {'color': '#9BBB59'}, 'fill': {'color': '#9BBB59'}}
307.     })
308.    
309.     # 设置图表标题和样式
310.     chart_monthly.set_title({'name': '月度销售趋势'})
311.     chart_monthly.set_x_axis({'name': '月份'})
312.     chart_monthly.set_y_axis({'name': '金额 (¥)'})
313.     chart_monthly.set_legend({'position': 'top'})
314.    
315.     # 插入图表
316.     worksheet_monthly.insert_chart('G2', chart_monthly, {'x_offset': 25, 'y_offset': 10})
317.    
318.     # 导出季度分析
319.     quarterly_analysis.to_excel(writer, sheet_name='季度分析', index=False)
320.     worksheet_quarterly = writer.sheets['季度分析']
321.    
322.     # 设置季度分析格式
323.     for col_num, value in enumerate(quarterly_analysis.columns.values):
324.         worksheet_quarterly.write(0, col_num, value, header_format)
325.    
326.     for row_num in range(1, len(quarterly_analysis) + 1):
327.         worksheet_quarterly.write(row_num, 0, quarterly_analysis.iloc[row_num-1, 0], data_format)  # 季度
328.         worksheet_quarterly.write(row_num, 1, quarterly_analysis.iloc[row_num-1, 1], data_format)  # 销量
329.         worksheet_quarterly.write(row_num, 2, quarterly_analysis.iloc[row_num-1, 2], currency_format)  # 销售额
330.         worksheet_quarterly.write(row_num, 3, quarterly_analysis.iloc[row_num-1, 3], currency_format)  # 利润
331.         worksheet_quarterly.write(row_num, 4, quarterly_analysis.iloc[row_num-1, 4], percentage_format)  # 利润率
332.    
333.     # 设置列宽
334.     worksheet_quarterly.set_column('A:A', 10)  # 季度
335.     worksheet_quarterly.set_column('B:B', 10)  # 销量
336.     worksheet_quarterly.set_column('C:E', 12) # 销售额、利润、利润率
337.    
338.     # 添加季度分析图表
339.     chart_quarterly = writer.book.add_chart({'type': 'column'})
340.    
341.     # 配置图表数据系列
342.     chart_quarterly.add_series({
343.         'name': '季度分析!$C$1',
344.         'categories': '季度分析!$A$2:$A$5',
345.         'values': '季度分析!$C$2:$C$5',
346.         'fill': {'color': '#4F81BD'},
347.     })
348.    
349.     chart_quarterly.add_series({
350.         'name': '季度分析!$D$1',
351.         'categories': '季度分析!$A$2:$A$5',
352.         'values': '季度分析!$D$2:$D$5',
353.         'fill': {'color': '#9BBB59'},
354.     })
355.    
356.     # 设置图表标题和样式
357.     chart_quarterly.set_title({'name': '季度销售对比'})
358.     chart_quarterly.set_x_axis({'name': '季度'})
359.     chart_quarterly.set_y_axis({'name': '金额 (¥)'})
360.     chart_quarterly.set_legend({'position': 'top'})
361.    
362.     # 插入图表
363.     worksheet_quarterly.insert_chart('G2', chart_quarterly, {'x_offset': 25, 'y_offset': 10})
364.    
365.     # 添加汇总工作表
366.     summary_data = {
367.         '指标': [
368.             '总销售额',
369.             '总利润',
370.             '平均利润率',
371.             '总销量',
372.             '最畅销产品',
373.             '最佳销售地区',
374.             '最佳销售月份',
375.             '最佳销售季度'
376.         ],
377.         '值': [
378.             sales_data['销售额'].sum(),
379.             sales_data['利润'].sum(),
380.             (sales_data['利润'].sum() / sales_data['销售额'].sum()),
381.             sales_data['销量'].sum(),
382.             product_analysis.loc[product_analysis['销售额'].idxmax(), '产品'],
383.             region_analysis.loc[region_analysis['销售额'].idxmax(), '地区'],
384.             monthly_analysis.loc[monthly_analysis['销售额'].idxmax(), '月份'],
385.             quarterly_analysis.loc[quarterly_analysis['销售额'].idxmax(), '季度']
386.         ]
387.     }
388.     df_summary = pd.DataFrame(summary_data)
389.    
390.     df_summary.to_excel(writer, sheet_name='汇总', index=False)
391.     worksheet_summary = writer.sheets['汇总']
392.    
393.     # 设置汇总格式
394.     for col_num, value in enumerate(df_summary.columns.values):
395.         worksheet_summary.write(0, col_num, value, header_format)
396.    
397.     for row_num in range(1, len(df_summary) + 1):
398.         worksheet_summary.write(row_num, 0, df_summary.iloc[row_num-1, 0], data_format)  # 指标
399.         
400.         # 根据指标类型设置不同的格式
401.         if row_num in [1, 2, 4]:  # 数值指标
402.             worksheet_summary.write(row_num, 1, df_summary.iloc[row_num-1, 1], currency_format)
403.         elif row_num == 3:  # 百分比指标
404.             worksheet_summary.write(row_num, 1, df_summary.iloc[row_num-1, 1], percentage_format)
405.         else:  # 文本指标
406.             worksheet_summary.write(row_num, 1, df_summary.iloc[row_num-1, 1], data_format)
407.    
408.     # 设置列宽
409.     worksheet_summary.set_column('A:A', 15)  # 指标
410.     worksheet_summary.set_column('B:B', 15)  # 值
412. print("\nExcel报告已生成: sales_report.xlsx")

复制代码

总结与建议

通过本教程，我们全面学习了如何使用pandas将数据输出到Excel文件，包括基础导出、格式设置、多表导出、数据处理以及高级技巧。下面是一些总结和建议：

关键要点总结

1. 基础导出：使用to_excel()方法可以轻松将DataFrame导出为Excel文件，通过参数可以控制导出的各个方面。
2. 格式设置：通过ExcelWriter对象结合openpyxl或xlsxwriter库，可以设置单元格的格式，包括字体、颜色、边框、对齐方式等。
3. 多表导出：使用同一个ExcelWriter对象，可以将多个DataFrame导出到同一个Excel文件的不同工作表中。
4. 数据处理：在导出Excel之前，通常需要进行数据清洗、转换、计算等操作，pandas提供了丰富的功能来处理这些任务。
5. 高级技巧：包括添加Excel公式、图表、数据验证等，可以使Excel报告更加专业和交互式。

基础导出：使用to_excel()方法可以轻松将DataFrame导出为Excel文件，通过参数可以控制导出的各个方面。

格式设置：通过ExcelWriter对象结合openpyxl或xlsxwriter库，可以设置单元格的格式，包括字体、颜色、边框、对齐方式等。

多表导出：使用同一个ExcelWriter对象，可以将多个DataFrame导出到同一个Excel文件的不同工作表中。

数据处理：在导出Excel之前，通常需要进行数据清洗、转换、计算等操作，pandas提供了丰富的功能来处理这些任务。

高级技巧：包括添加Excel公式、图表、数据验证等，可以使Excel报告更加专业和交互式。

最佳实践建议

1. 选择合适的引擎：openpyxl：适合处理现有的Excel文件，支持更多的Excel功能。xlsxwriter：适合创建新的Excel文件，性能更好，支持更多的格式选项。
2. openpyxl：适合处理现有的Excel文件，支持更多的Excel功能。
3. xlsxwriter：适合创建新的Excel文件，性能更好，支持更多的格式选项。
4. 优化大数据导出：对于大数据集，考虑分块导出或使用chunksize参数。如果不需要格式，可以考虑导出为CSV格式，然后再用Excel打开。
5. 对于大数据集，考虑分块导出或使用chunksize参数。
6. 如果不需要格式，可以考虑导出为CSV格式，然后再用Excel打开。
7. 代码组织：将格式定义和导出逻辑分离，使代码更易于维护。使用函数封装常用的导出操作，提高代码复用性。
8. 将格式定义和导出逻辑分离，使代码更易于维护。
9. 使用函数封装常用的导出操作，提高代码复用性。
10. 错误处理：添加适当的错误处理，以应对文件权限、路径不存在等问题。在导出前检查数据的有效性，避免导出错误的数据。
11. 添加适当的错误处理，以应对文件权限、路径不存在等问题。
12. 在导出前检查数据的有效性，避免导出错误的数据。
13. 性能考虑：避免在循环中频繁调用Excel操作，尽量批量处理。对于复杂的格式设置，考虑先创建模板文件，然后填充数据。
14. 避免在循环中频繁调用Excel操作，尽量批量处理。
15. 对于复杂的格式设置，考虑先创建模板文件，然后填充数据。

选择合适的引擎：

• openpyxl：适合处理现有的Excel文件，支持更多的Excel功能。
• xlsxwriter：适合创建新的Excel文件，性能更好，支持更多的格式选项。

优化大数据导出：

• 对于大数据集，考虑分块导出或使用chunksize参数。
• 如果不需要格式，可以考虑导出为CSV格式，然后再用Excel打开。

代码组织：

• 将格式定义和导出逻辑分离，使代码更易于维护。
• 使用函数封装常用的导出操作，提高代码复用性。

错误处理：

• 添加适当的错误处理，以应对文件权限、路径不存在等问题。
• 在导出前检查数据的有效性，避免导出错误的数据。

性能考虑：

• 避免在循环中频繁调用Excel操作，尽量批量处理。
• 对于复杂的格式设置，考虑先创建模板文件，然后填充数据。

进阶学习建议

1. 深入学习pandas：掌握更多高级数据处理技巧，如数据透视表、时间序列分析等。学习如何优化pandas操作，提高数据处理效率。
2. 掌握更多高级数据处理技巧，如数据透视表、时间序列分析等。
3. 学习如何优化pandas操作，提高数据处理效率。
4. 探索其他Excel库：学习xlwings，它提供了与Excel的更深层次集成。了解pyxlsb，用于处理二进制Excel文件。
5. 学习xlwings，它提供了与Excel的更深层次集成。
6. 了解pyxlsb，用于处理二进制Excel文件。
7. 自动化报告生成：结合Python的调度工具（如schedule或APScheduler）自动生成定期报告。学习如何通过邮件或其他方式自动分发生成的Excel报告。
8. 结合Python的调度工具（如schedule或APScheduler）自动生成定期报告。
9. 学习如何通过邮件或其他方式自动分发生成的Excel报告。
10. 数据可视化：学习如何使用matplotlib和seaborn创建更复杂的图表。探索如何将交互式图表（如plotly）嵌入到Excel中。
11. 学习如何使用matplotlib和seaborn创建更复杂的图表。
12. 探索如何将交互式图表（如plotly）嵌入到Excel中。

深入学习pandas：

• 掌握更多高级数据处理技巧，如数据透视表、时间序列分析等。
• 学习如何优化pandas操作，提高数据处理效率。

探索其他Excel库：

• 学习xlwings，它提供了与Excel的更深层次集成。
• 了解pyxlsb，用于处理二进制Excel文件。

自动化报告生成：

• 结合Python的调度工具（如schedule或APScheduler）自动生成定期报告。
• 学习如何通过邮件或其他方式自动分发生成的Excel报告。

数据可视化：

• 学习如何使用matplotlib和seaborn创建更复杂的图表。
• 探索如何将交互式图表（如plotly）嵌入到Excel中。

通过本教程的学习，你应该已经掌握了使用pandas输出Excel的基本技能和高级技巧。继续实践和探索，你将能够创建更加专业和高效的Excel报告，提升你的数据分析能力。