python操作excel让工作自动化

下面我将为你讲解“Python操作Excel让工作自动化”的完整攻略。

1. Excel是什么？

Excel是微软的一款电子表格软件，广泛用于数据处理、统计分析、报表生成等场景，深受各行各业的喜爱。Excel支持多种数据格式，能够方便地进行数据输入、筛选、排序、图表制作等操作。在日常工作中，使用Excel处理数据、制作报表已经成为许多人的必修技能。

2. Python操作Excel的库

Python常用的Excel操作库有很多，其中比较常用的有openpyxl、pandas、xlwt和xlrd，本攻略重点介绍openpyxl和pandas两种库。openpyxl是Python处理Excel文件的第三方扩展库，可以实现Excel读写、修改等操作。pandas是基于NumPy的数据分析库，它可将Excel的数据载入数据表中，进行数据分析、处理和可视化等操作。

3. openpyxl库操作Excel

3.1 安装openpyxl库

在命令行中输入如下命令进行安装：

pip install openpyxl

3.2 创建Excel文件、工作表和单元格

在Python中，使用openpyxl库创建Excel文件、工作表和单元格可以通过以下步骤实现：

# 导入openpyxl库
import openpyxl

# 创建工作簿
wb = openpyxl.Workbook()

# 获取工作表
ws = wb.active

# 写入数据
ws['A1'] = 'Name'
ws['B1'] = 'Age'
ws['C1'] = 'Gender'

# 保存Excel文件
wb.save('example.xlsx')

上述代码中，使用openpyxl.Workbook()创建了一个新的工作簿，使用wb.active获取工作表对象，然后使用ws['A1']等方式指定单元格写入数据，并使用wb.save('example.xlsx')将工作簿保存为Excel文件。

3.3 读取Excel文件中的数据

如果需要读取Excel文件中的数据，可以使用openpyxl库中的load_workbook方法读取Excel文件，然后获取对应的工作表，读取指定的单元格中的数据。示例代码如下：

# 导入openpyxl库
import openpyxl

# 读取Excel文件
wb = openpyxl.load_workbook('example.xlsx')

# 获取工作表
ws = wb.active

# 读取数据
name = ws['A1'].value
age = ws['B1'].value
gender = ws['C1'].value

# 输出数据
print(name, age, gender)

上述代码中，使用openpyxl.load_workbook('example.xlsx')方法读取Excel文件，然后使用wb.active获取工作表对象，最后使用ws['A1'].value等方式获取指定单元格中的数据。

4. pandas库操作Excel

4.1 安装pandas库

在命令行中输入如下命令进行安装：

pip install pandas

4.2 读取Excel文件中的数据

使用pandas库读取Excel文件中的数据可以使用pandas.read_excel方法，示例代码如下：

# 导入pandas库
import pandas as pd

# 读取Excel文件
df = pd.read_excel('example.xlsx')

# 输出数据
print(df.head())

上述代码中，使用pd.read_excel('example.xlsx')方法读取Excel文件，并将读取结果保存在数据表df中。使用print(df.head())输出数据表的前五行数据。

4.3 将数据导出至Excel文件

使用pandas库将数据导出至Excel文件可以使用df.to_excel方法，示例代码如下：

# 导入pandas库
import pandas as pd

# 创建数据表
df = pd.DataFrame({
    'Name': ['Tom', 'Jerry', 'Mike', 'Lucy'],
    'Age': [23, 25, 28, 30],
    'Gender': ['Male', 'Male', 'Male', 'Female']
})

# 将数据导出至Excel文件
df.to_excel('example.xlsx', index=False)

# 输出数据
print(df.head())

上述代码中，使用pd.DataFrame方法创建数据表df，使用df.to_excel('example.xlsx', index=False)将数据表导出到Excel文件中，并指定index=False表示不导出索引列。

5. 示例说明

下面来举两个例子，讲解Python如何操作Excel实现自动化工作：

5.1 示例1：Excel批量处理

需求：假设公司每天会生成50个Excel文件，包含20列的数据，需要统计每列数据的平均值，并将结果保存在一个新的Excel文件中。

实现：可以使用Python的pandas库实现批量处理Excel文件，具体步骤如下：

# 导入pandas库
import pandas as pd
import os

# 获取Excel文件列表
file_list = os.listdir(r"D:\excels")

# 将文件中的数据读取到数据表中并合并
dfs = [pd.read_excel(os.path.join(r"D:\excels", file)) for file in file_list]
df = pd.concat(dfs)

# 计算每列数据的平均值
avg_df = df.mean()

# 将结果保存至Excel文件中
avg_df.to_excel('summary.xlsx')

上述代码中，使用os.listdir获取指定目录下的Excel文件列表，然后使用pd.read_excel方法读取每个Excel文件中的数据，并将它们合并为一个数据表。接着，使用df.mean()计算每列数据的平均值，并保存到一个新的数据表avg_df中。最后，使用avg_df.to_excel方法将计算结果保存至Excel文件中。

5.2 示例2：Excel报表自动生成

需求：假设公司每周需要制作销售报表，需要从数据库中取出销售数据，并将数据按周、月、年等不同维度进行统计并制作成报表文件。

实现：可以使用Python的pandas库实现自动生成Excel报表，具体步骤如下：

# 导入pandas库
import pandas as pd

# 查询数据库中的销售数据
query_sql = "SELECT * FROM sales WHERE date BETWEEN '2022-01-01' AND '2022-12-31'"
df_sales = pd.read_sql(query_sql, conn)

# 按周、月、年等维度统计销售额
week_sales = df_sales.groupby(pd.Grouper(key='date', freq='W-MON'))['sales'].sum()
month_sales = df_sales.groupby(pd.Grouper(key='date', freq='M'))['sales'].sum()
year_sales = df_sales.groupby(pd.Grouper(key='date', freq='Y'))['sales'].sum()

# 将统计结果保存至Excel文件中
with pd.ExcelWriter('sales_report.xlsx') as writer:
    week_sales.to_excel(writer, sheet_name='Weekly Sales')
    month_sales.to_excel(writer, sheet_name='Monthly Sales')
    year_sales.to_excel(writer, sheet_name='Yearly Sales')

上述代码中，使用pd.read_sql方法从数据库中取出销售数据，并将数据保存到数据表df_sales中。接着，使用df_sales.groupby方法按周、月、年等维度统计销售额，并分别保存到变量week_sales、month_sales和year_sales中。最后，使用pd.ExcelWriter和to_excel方法将统计结果保存至Excel文件中。