全网最全Python 办公主动化之 Excel 做表主动化

更多学习内容：ipengtao.com

Microsoft Excel 是一款强壮的办公东西，广泛用于数据剖析、陈述制造、预算办理等各种使命。然而，当触及很多数据、复杂计算和主动化时，手动操作 Excel 可能会变得耗时且简略出错。在本文中，将深入讨论怎么运用 Python 进行 Excel 表格的主动化，从而进步作业效率。

准备作业

在开端之前，确保现已装置了 Python 和所需的库。

主要运用以下库：

  • openpyxl：用于读取和写入 Excel 文件。
  • pandas：用于数据处理和剖析。
  • xlwings：用于将 Python 与 Excel 连接，完成双向通信。

能够运用以下指令装置这些库：

pip install openpyxl pandas xlwings

读取 Excel 文件

首要，看看怎么运用 Python 读取 Excel 文件。假定有一个名为 “data.xlsx” 的 Excel 文件，其间包含了一些数据。能够运用 openpyxl 库来读取它。

import openpyxl
# 翻开 Excel 文件
workbook = openpyxl.load_workbook('data.xlsx')
# 挑选作业表
sheet = workbook.active
# 读取单元格的值
cell_value = sheet['A1'].value
print(f'单元格 A1 的值为: {cell_value}')
# 遍历整个作业表
for row in sheet.iter_rows(min_row=2, values_only=True):
    print(row)

上述代码演示了怎么翻开 Excel 文件、挑选作业表、读取单元格的值以及遍历整个作业表。能够依据需求进行数据处理和剖析。

写入 Excel 文件

接下来，将看看怎么运用 Python 写入 Excel 文件。假定现已处理了一些数据，现在要将结果写入新的 Excel 文件。

import openpyxl
# 创立一个新的 Excel 作业簿
workbook = openpyxl.Workbook()
# 创立一个新的作业表
sheet = workbook.active
sheet.title = '数据'
# 写入数据到单元格
sheet['A1'] = '名字'
sheet['B1'] = '年纪'
data = [('Alice', 25), ('Bob', 30), ('Carol', 28)]
for row_index, (name, age) in enumerate(data, start=2):
    sheet[f'A{row_index}'] = name
    sheet[f'B{row_index}'] = age
# 保存作业簿到文件
workbook.save('result.xlsx')

上述代码创立了一个新的 Excel 作业簿、一个新的作业表，并将数据写入单元格。最终，它将作业簿保存为 “result.xlsx” 文件。

数据处理与剖析

Python 的 pandas 库为数据处理和剖析供给了强壮的功能。能够运用 pandas 从 Excel 文件中读取数据、进行过滤、排序、聚合等操作。

import pandas as pd
# 从 Excel 文件读取数据
df = pd.read_excel('data.xlsx')
# 打印前几行数据
print(df.head())
# 进行数据剖析操作
mean_age = df['年纪'].mean()
max_age = df['年纪'].max()
print(f'平均年纪: {mean_age}')
print(f'最大年纪: {max_age}')

上述代码运用 pandas 从 Excel 文件中读取数据，然后进行了一些简略的数据剖析操作。你能够依据需求进行更复杂的数据处理。

Excel 与 Python 的双向通信

xlwings 是一个强壮的库，它能够在 Excel 中运转 Python 脚本，以及从 Python 脚本中操控 Excel。这种双向通信使得 Excel 主动化变得更加灵活和强壮。

首要，需求在 Excel 中启用 xlwings 插件。然后，能够运用以下示例演示 Excel 和 Python 之间的互动。

import xlwings as xw
# 连接到 Excel
app = xw.App(visible=True, add_book=False)
# 翻开 Excel 文件
workbook = app.books.open('data.xlsx')
# 挑选作业表
sheet = workbook.sheets['Sheet1']
# 读取单元格的值
cell_value = sheet.range('A1').value
print(f'单元格 A1 的值为: {cell_value}')
# 在 Excel 中运转公式
sheet.range('B1').formula = '=SUM(B2:B4)'
# 从 Excel 中获取数据到 Python
data_range = sheet.range('A2').expand('down').value
print('从 Excel 中获取的数据:')
print(data_range)
# 写入数据到 Excel
new_data = [['David', 35], ['Eve', 27]]
sheet.range('A6').value = new_data
# 保存 Excel 文件
workbook.save()
workbook.close()
app.quit()

上述代码演示了怎么连接到 Excel、读取和写入单元格、运转公式以及在 Excel 和 Python 之间传输数据。

主动化使命示例

当触及到主动化使命时，Python 和 Excel 的组合能够大大进步作业效率。

1. 数据汇总和剖析

使命描绘： 假定有多个 Excel 文件，每个文件包含某个月份的出售数据。需求主动汇总这些数据，并生成每月的出售陈述，以便更好地了解出售趋势。

示例代码：

import pandas as pd
import os
# 创立一个空的 DataFrame 以存储一切月份的数据
all_data = pd.DataFrame()
# 遍历文件夹中的一切 Excel 文件
folder_path = 'sales_data'
for filename in os.listdir(folder_path):
    if filename.endswith('.xlsx'):
        file_path = os.path.join(folder_path, filename)
        # 从每个文件中读取数据并添加到总数据中
        data = pd.read_excel(file_path)
        all_data = all_data.append(data)
# 汇总数据
monthly_sales = all_data.groupby('Month')['Sales'].sum()
# 生成出售陈述
monthly_sales.to_excel('sales_report.xlsx', sheet_name='Monthly Sales')

上述代码会将多个 Excel 文件中的出售数据汇总到一个数据框中，然后按月份进行分组并计算总出售额。最终，将月度出售陈述保存到新的 Excel 文件中。

2. 数据清洗和转换

使命描绘： 数据存储在 Excel 中，但需求进行清洗和转换，以便进行进一步的剖析。这可能触及删去重复行、处理缺失值、更改数据类型等操作。

示例代码：

import pandas as pd
# 从 Excel 文件中读取原始数据
raw_data = pd.read_excel('raw_data.xlsx')
# 删去重复行
cleaned_data = raw_data.drop_duplicates()
# 处理缺失值
cleaned_data['Age'].fillna(0, inplace=True)
# 更改数据类型
cleaned_data['Date'] = pd.to_datetime(cleaned_data['Date'])
# 保存清洗后的数据到新的 Excel 文件
cleaned_data.to_excel('cleaned_data.xlsx', index=False)

上述代码演示了怎么删去重复行、处理缺失值和更改数据类型，最终将清洗后的数据保存到新的 Excel 文件中。

3. 数据可视化

使命描绘： 想要从 Excel 数据中创立图表和图形，以便更好地舆解数据。这能够包含柱状图、折线图、散点图等。

示例代码：

import pandas as pd
import matplotlib.pyplot as plt
# 从 Excel 文件中读取数据
data = pd.read_excel('data.xlsx')
# 创立柱状图
plt.figure(figsize=(8, 6))
plt.bar(data['Category'], data['Sales'])
plt.xlabel('Category')
plt.ylabel('Sales')
plt.title('Sales by Category')
plt.xticks(rotation=45)
plt.show()

上述代码运用 pandas 读取 Excel 数据，然后运用 matplotlib 创立了一个柱状图，以可视化不同类别的出售数据。

4. 主动发送电子邮件

使命描绘： 想要依据 Excel 表格中的某些条件主动发送电子邮件通知。

示例代码：

import pandas as pd
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
# 从 Excel 文件中读取数据
data = pd.read_excel('email_list.xlsx')
# 连接到 SMTP 服务器
smtp_server = 'smtp.example.com'
smtp_port = 587
sender_email = 'your_email@example.com'
sender_password = 'your_password'
server = smtplib.SMTP(smtp_server, smtp_port)
server.starttls()
server.login(sender_email, sender_password)
# 遍历数据并发送电子邮件
for index, row in data.iterrows():
    recipient_email = row['Email']
    subject = 'Important Update'
    message = f'Hello {row["Name"]},nnThis is an important update.'
    msg = MIMEMultipart()
    msg['From'] = sender_email
    msg['To'] = recipient_email
    msg['Subject'] = subject
    msg.attach(MIMEText(message, 'plain'))
    server.sendmail(sender_email, recipient_email, msg.as_string())
# 封闭 SMTP 连接
server.quit()

上述代码演示了怎么运用 smtplib 和 email 库来连接到 SMTP 服务器并发送电子邮件。能够依据 Excel 数据中的收件人信息主动发送电子邮件通知。

总结

本文讨论了怎么运用 Python 进行 Excel 表格的主动化，包含读取和写入 Excel 文件、数据处理与剖析、Excel 与 Python 的双向通信，以及一些实践的主动化使命示例。经过结合 Python 的强壮功能和 Excel 的灵活性，能够大大进步作业效率，减少重复性作业，同时更好地办理和剖析数据。无论是数据剖析师、财政专业人员还是项目经理，这些技巧都能协助更好地使用 Excel 进行办公主动化。

Python学习路线

更多学习内容：ipengtao.com

全网最全Python 办公主动化之 Excel 做表主动化