Python实现Excel自动化办公的6种方法！

众所周知，Python在自动化办公方面有着先天性的优势。一方面是Python拥有庞大的第三方库，可以满足任何不同的需求，一方面Python的语法简单，易于学习，易于使用。

本文将带您了解使用Python自动化操作Excel的6中方法，以及对应的使用场景，希望对您在自动化办公方面有所帮助。

使用Python自动化处理Excel，在日常工作中有很多应用场景，本文列举以下6种：

数据分析

Excel中通常存储着各种各样的数据，使用Python可以对这些数据进行筛选、排序、汇总、统计、分析等操作，提高数据分析效率和精度。

接下来我们列举一个简单的Python数据分析实例：通过读取一个CSV文件、清洗和预处理数据，计算并可视化出不同房屋类型的平均售价。

代码如下：

import pandas as pd
import matplotlib.pyplot as plt

# 读取CSV文件
data = pd.read_csv('house_prices.csv')

# 数据清洗和预处理
data.dropna(inplace=True)   # 删除缺失值
data['price'] = data['price'].apply(lambda x: float(x.replace('$', '').replace(',', '')))  # 将价格字符串转换为浮点数
data = data.groupby('type').mean()  # 按房屋类型计算平均售价

# 数据可视化
data.plot(kind='bar', y='price')
plt.ylabel('Price ($)')
plt.title('Average House Prices by Type')
plt.show()

通过以上代码，可以生成一个柱状图，展示不同房屋类型的平均售价。

数据清洗

Excel表格中的数据往往有重复、缺失、错误等问题，使用Python可以自动化处理这些问题，提高数据质量。

现在假设我们有一个CSV文件，其中包含有关电影的数据，如下所示：

Title	Director	Year	Length
肖申克的救赎	弗兰克·达拉邦	1994	142
教父	弗朗西斯·福特·科波拉	1972	175
黑暗骑士	克里斯托弗·诺兰	2008	152
低俗小说	昆汀·塔伦蒂诺	1994	154
指环王	彼得·杰克逊	2001	178
阿甘正传	罗伯特·泽梅基斯	1994	142

现在，我们想要进行一些数据清洗，以便更好地进行分析。我们需要执行以下任务：

删除重复的数据行。
将年份转换为整数。
将电影长度转换为分钟。

以下是Python代码实现：

import pandas as pd

# 读取CSV文件
df = pd.read_csv('movies.csv')

# 删除重复的数据行
df = df.drop_duplicates()

# 将年份转换为整数
df['Year'] = df['Year'].astype(int)

# 将电影长度转换为分钟
df['Length'] = df['Length'].apply(lambda x: int(x) if str(x).isdigit() else None)

# 保存清洗后的数据到新的CSV文件中
df.to_csv('cleaned_movies.csv', index=False)

在这段代码中，我们使用了Pandas库来读取和处理CSV文件。

首先我们删除重复的行，然后将“年份”列转换为整数，将“时长”列转换为整数（如果可能），最后将清洗后的数据保存到新的CSV文件中。

数据导入导出

Excel表格可以方便地导入导出数据，使用Python可以实现自动化导入导出，提高数据处理效率。

这方面我们可以使用padas库来操作。以下是一些实例演示：

#导入CSV文件
import pandas as pd
df = pd.read_csv('data.csv')
print(df.head())

#导出CSV文件
import pandas as pd
df = pd.read_csv('data.csv')
df.to_csv('new_data.csv', index=False)

#导入Excel文件
import pandas as pd
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
print(df.head())

#导出Excel文件
import pandas as pd
df = pd.read_csv('data.csv')
df.to_excel('new_data.xlsx', sheet_name='Sheet1', index=False)

这些例子中，我们使用了pandas库来导入、导出CSV和Excel文件。

数据可视化

Excel可以制作各种各样的图表，使用Python可以实现自动化生成图表，进一步提高数据的可视化效果。

常用的数据可视化库有openpyxl和matplotlib库，同样的，我们使用这两个库进行Excel数据可视化的演示：

首先，我们使用openpyxl库读取Excel数据，并将其转换为Pandas DataFrame，然后使用matplotlib库绘制图表。

假设我们有一个名为“data.xlsx”的Excel文件，其中包含以下数据：

学生姓名	数学成绩	英语成绩
Tom	80	90
Jerry	75	85
Peter	90	95
Mary	85	80

下面是Python代码实现：

import pandas as pd
from openpyxl import load_workbook
import matplotlib.pyplot as plt

# 读取Excel文件并转换为Pandas DataFrame
wb = load_workbook(filename='data.xlsx', read_only=True)
ws = wb['Sheet1']
data = ws.values
columns = next(data)
df = pd.DataFrame(data, columns=columns)

# 绘制柱状图
plt.bar(df['学生姓名'], df['数学成绩'], label='数学成绩')
plt.bar(df['学生姓名'], df['英语成绩'], label='英语成绩')
plt.xlabel('学生姓名')
plt.ylabel('成绩')
plt.title('学生成绩统计')
plt.legend()
plt.show()

运行代码后，将显示一个柱状图，显示每个学生的数学成绩和英语成绩。如下：

在这个示例代码中，我们使用openpyxl库读取Excel数据，并将其转换为Pandas DataFrame。

然后，我们使用matplotlib库绘制了柱状图，显示了每个学生的数学成绩和英语成绩。

自动生成Excel表格

使用Python也可以实现将数据自动化生成各种Excel报表，如销售报表、财务报表等。

同样的，我们创建一个演示示例，这个示例使用了openpyxl库来自动创建一些学生成绩的Excel表格：

import openpyxl

# 创建一个新的工作簿
workbook = openpyxl.Workbook()

# 选择默认的工作表
sheet = workbook.active

# 向工作表中添加标题行
sheet.append(['姓名', '数学成绩', '英语成绩', '总分'])

# 添加一些学生数据
students = [
    ('张三', 90, 80),
    ('李四', 85, 95),
    ('王五', 70, 75),
    ('赵六', 60, 85)
]

# 将学生数据写入工作表中
for student in students:
    name, math_score, english_score = student
    total_score = math_score + english_score
    sheet.append([name, math_score, english_score, total_score])

# 将工作簿保存为Excel文件
workbook.save('scores.xlsx')

在这段代码中，创建一个包含四列数据的Excel表格：姓名、数学成绩、英语成绩和总分。然后，它使用一个包含学生数据的列表来填充这些列。最后，它将工作簿保存为一个名为“scores.xlsx”的Excel文件。

数据库操作

Excel表格中的数据可以通过Python自动化地导入到数据库中，或者从数据库中提取数据并导出到Excel表格中。

接下来我们演示一下使用pandas读取Excel文件，并使用mysql-connector库将数据插入到MySQL数据库中的使用方法。

以下是完整代码：

import pandas as pd
import mysql.connector

# 创建数据库连接
cnx = mysql.connector.connect(user='your_username', password='your_password',
                              host='your_host', database='your_database')
cursor = cnx.cursor()

# 创建表格
TABLES = {}
TABLES['students'] = (
    "CREATE TABLE `students` ("
    "  `id` int(11) NOT NULL AUTO_INCREMENT,"
    "  `name` varchar(50) NOT NULL,"
    "  `age` int(11) NOT NULL,"
    "  PRIMARY KEY (`id`)"
    ") ENGINE=InnoDB")

for table_name in TABLES:
    table_description = TABLES[table_name]
    try:
        print("Creating table {}: ".format(table_name), end='')
        cursor.execute(table_description)
    except mysql.connector.Error as err:
        if err.errno == mysql.connector.errorcode.ER_TABLE_EXISTS_ERROR:
            print("already exists.")
        else:
            print(err.msg)
    else:
        print("OK")

# 读取 Excel 文件
df = pd.read_excel('students.xlsx', sheet_name='Sheet1')

# 将数据转换为列表或元组
data = df.values.tolist()

# 插入数据
add_student = ("INSERT INTO students "
               "(name, age) "
               "VALUES (%s, %s)")

for student in data:
    cursor.execute(add_student, student)

cnx.commit()
cursor.close()
cnx.close()

这段代码中，我们执行了以下步骤：