摘要：• pandas：数据处理与分析，适合表格数据读取、清洗、转换。

处理Excel主要使用以下库，需先安装：

• pandas：数据处理与分析，适合表格数据读取、清洗、转换。

• openpyxl：操作Excel文件的单元格、样式、公式等细节。

• xlsxwriter：高效创建新Excel文件，支持图表和复杂格式。

• xlrd/xlwt：读取和写入旧版Excel（.xls）文件。

安装命令：

pip install pandas openpyxl xlsxwriter xlrd xlwt

1. 读取Excel文件（pandas）

import pandas as pd

# 读取文件

excel_File = pd.ExcelFile('文件路径.xlsx')

# 获取所有工作表名称

sheet_names = excel_file.sheet_names

print("工作表名称：", sheet_names)

# 读取指定工作表数据

df = excel_file.parse('工作表名') # 或 df = pd.read_excel('文件路径.xlsx', '工作表名')

# 查看数据基本信息

print("数据基本信息：")

df.info

# 查看前几行数据

print("数据前5行：")

print(df.head)

2. 写入数据到Excel（pandas）

import pandas as pd

# 创建示例数据

data = {

'姓名': ['张三', '李四', '王五'],

'年龄': [25, 30, 22],

'部门': ['技术部', '市场部', '人事部']

}

df = pd.DataFrame(data)

# 写入Excel（不保存索引列）

df.to_excel('新文件路径.xlsx', sheet_name='员工信息', index=False)

# 追加数据到已有文件（需用openpyxl辅助）

from openpyxl import load_workbook

# 先读取已有文件

book = load_workbook('新文件路径.xlsx')

writer = pd.ExcelWriter('新文件路径.xlsx', engine='openpyxl')

writer.book = book

# 追加新工作表或数据到已有工作表

df_new = pd.DataFrame({'姓名': ['赵六'], '年龄': [28], '部门': ['财务部']})

df_new.to_excel(writer, sheet_name='新员工', index=False)

# 保存修改

writer.save

1. 数据清洗与转换（pandas）

import pandas as pd

# 读取文件

df = pd.read_excel('数据文件.xlsx')

# 示例：处理缺失值（用0填充数值列，用'未知'填充文本列）

df = df.fillna({'年龄': 0, '部门': '未知'})

# 示例：筛选数据（如年龄>25的员工）

filtered_df = df[df['年龄'] > 25]

# 示例：分组统计（按部门计算平均年龄）

grouped_df = df.groupby('部门')['年龄'].mean.reset_index

# 示例：数据排序（按年龄降序）

sorted_df = df.sort_values('年龄', ascending=False)

# 保存处理后的数据

sorted_df.to_excel('处理后数据.xlsx', index=False)

2. 单元格样式与公式设置（openpyxl）

from openpyxl import load_workbook

from openpyxl.styles import Font, Alignment, Border, Side

from openpyxl.utils import get_column_letter

# 加载文件

wb = load_workbook('文件路径.xlsx')

ws = wb.active # 或指定工作表 wb['工作表名']

# 1. 设置单元格样式

# 标题行加粗、居中

for cell in ws[1]:

cell.font = Font(bold=True)

cell.alignment = Alignment(horizontal='center', vertical='center')

# 边框样式

thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),

top=Side(style='thin'), bottom=Side(style='thin'))

for row in ws.iter_rows:

for cell in row:

cell.border = thin_border

# 2. 插入公式（如计算总分）

ws['D2'] = '=SUM(B2:C2)' # 假设B列和C列为成绩，D列计算总分

ws['D2'].font = Font(color='FF0000') # 红色字体标记公式

# 3. 调整列宽（根据内容自动适应）

for column_cells in ws.columns:

length = max(len(str(cell.value)) for cell in column_cells)

ws.column_dimensions[get_column_letter(column_cells[0].column)].width = length + 2

# 保存修改

wb.save('格式优化后.xlsx')

场景：批量合并多个Excel文件中的数据

import pandas as pd

import os

# 1. 定义文件路径和输出路径

folder_path = 'Excel文件所在文件夹/' # 需包含所有Excel文件

output_path = '合并后文件.xlsx'

# 2. 遍历文件夹获取所有Excel文件

all_files = [f for f in os.listdir(folder_path) if f.endswith(('.xlsx', '.xls'))]

# 3. 初始化空列表存储数据

all_data =

# 4. 循环读取每个文件并合并数据

for file in all_files:

file_path = os.path.join(folder_path, file)

try:

# 读取文件（假设所有文件结构相同）

df = pd.read_excel(file_path)

all_data.append(df)

print(f"成功读取文件：{file}")

except Exception as e:

print(f"读取文件{file}失败，错误：{e}")

# 5. 合并所有数据

if all_data:

combined_df = pd.concat(all_data, ignore_index=True)

# 保存合并后的数据

combined_df.to_excel(output_path, index=False)

print(f"合并完成，数据已保存至：{output_path}")

else:

print("未读取到有效Excel文件")

场景：用xlsxwriter生成带图表的报表

import xlsxwriter

import pandas as pd

# 1. 创建示例数据

data = {

'月份': ['1月', '2月', '3月', '4月', '5月', '6月'],

'销售额': [12000, 15000, 13500, 18000, 20000, 22000],

'成本': [8000, 9500, 9000, 11000, 12000, 13000]

}

df = pd.DataFrame(data)

# 2. 创建Excel文件和工作表

workbook = xlsxwriter.Workbook('销售报表.xlsx')

worksheet = workbook.add_worksheet

# 3. 写入表头

worksheet.write_row(0, 0, df.columns)

# 4. 写入数据

for row_num, row_data in df.iterrows:

worksheet.write_row(row_num + 1, 0, row_data)

# 5. 计算利润列（销售额-成本）

worksheet.write_column(1, len(df.columns), df['销售额'] - df['成本'])

worksheet.write(0, len(df.columns), '利润')

# 6. 创建柱状图

chart = workbook.add_chart({'type': 'column'})

# 添加数据系列

chart.add_series({

'name': '销售额',

'values': '=Sheet1!$B$2:$B$7',

'fill': {'color': 'blue'}

})

chart.add_series({

'name': '成本',

'values': '=Sheet1!$C$2:$C$7',

'fill': {'color': 'red'}

})

# 设置图表标题和坐标轴

chart.set_title({'name': '上半年销售数据对比'})

chart.set_x_axis({'name': '月份'})

chart.set_y_axis({'name': '金额'})

# 将图表插入工作表

worksheet.insert_chart('E2', chart)

# 7. 关闭文件

workbook.close

1. 文件兼容性：pandas对复杂格式Excel支持有限，涉及样式细节建议用openpyxl；xlsxwriter适合新建文件，不支持修改已有文件。

2. 大数据处理：处理百万级数据时，pandas可能内存不足，可改用chunksize分块读取，或使用dask库。

3. 异常处理：操作文件时建议用try-except捕获异常，避免程序崩溃。

通过以上案例，可实现Excel文件的读取、写入、数据处理、格式优化及批量自动化操作，大幅提升办公效率~

来源：绿叶菜