Python处理Excel表格

考虑一个场景，要求在网站上为 30,000 名员工创建一个帐户。手动重复执行此任务会非常枯燥乏味。此外，这将花费太多时间，这不是一个明智的决定。

现在想象一下从事数据输入工作的员工的生活。他们的工作是从 Excel 表格中获取数据并将其插入其他位置。他们浏览不同的网站和杂志，从那里收集数据，然后将其插入数据库。他们还需要对条目进行计算。

使用Python自动化无需手动执行这些类型的任务，只需花一个小时写一段代码并自动化这些类型的事情。本文中我们将创建一个小项目来学习 Python 中的自动化。

任务介绍

手动处理或更新数以千计的电子表格将花费太多时间。这可能需要数小时、数天甚至数月的时间。我们将编写一个 Python 程序来自动执行此任务。我们将处理下图中给出的数据。

在这个电子表格中，我们有各种交易的记录，但假设由于错误（人为错误或系统错误），第三列中列出的产品的价格是错误的。假设我们需要将价格降低 0.9。可以使用第四列中的数学公式手动完成此任务，但如果有数千条记录，则会花费太多时间（可能需要 1 周或两周）。

我们将编写一个 python 程序来自动化这个过程。此外，我们将为它添加一个图表。我们的 Python 程序将在几秒钟内为我们完成这项任务。

写一个简单的代码

为了处理这个 Excel 工作表，我们将使用openpyxl库 。在Centos8中执行下面命令安装openpyxl：

# yum -y install python3-openpyxl

现在我们可以导入这个包来处理我们的电子表格。在此之前，将电子表格添加到项目文件夹中。现在在你的文件夹中创建一个文件 prod.py 并写下下面给出的代码。

[root@localhost data]# vim prod.py#!/usr/bin/python3import timeimport openpyxl as xlfrom openpyxl.chart import BarChart, Referencedef process_workbook(filename):tm = time.strftime(‘%Y%m%d%H%M‘)wb = xl.load_workbook(filename)sheet = wb[‘Sheet1‘]for row in range(2, sheet.max_row + 1):cell = sheet.cell(row, 3)corrected_price = float(cell.value) * 0.9corrected_price_cell = sheet.cell(row, 4)corrected_price_cell.value = corrected_pricevalues = Reference(sheet, min_row=2, max_row=sheet.max_row, min_col=4 , max_col=4)chart = BarChart()chart.add_data(values)sheet.add_chart(chart,‘G2‘)wb.save(filename + " " + tm + ‘.xlsx‘)process_workbook(‘/data/prod.xlsx‘)

解释代码的意思

我们将逐步解释上面编写的代码以了解完整的过程。

步骤 1. 要处理我们的电子表格导入 openpyxl 包（我们使用 xl 别名使我们的代码更简洁更短）。此外，要将图表添加到我们的电子表格，我们需要导入两个类 BarChart 和 Reference。导入time库，目的是另存表格文件，防止修改源文件。

import timeimport openpyxl as xlfrom openpyxl.chart import BarChart, Reference

步骤2. 创建一个函数，方便后续调用。

def process_workbook(filename):

步骤3. 现在我们需要加载 Excel 工作簿。写下下面给出的代码。tm保存当前时间，为后续使用。wb 返回对象，使用这个对象，我们访问 Sheet1工作表。

tm = time.strftime(‘%Y%m%d%H%M‘)wb = xl.load_workbook(filename)sheet = wb[‘Sheet1‘]

步骤4. 要访问第三列（价格列的条目）中第 2 行到最后一行有数据的条目，我们需要添加一个 for 循环。我们将此条目保存在可变单元格中。

for row in range(2, sheet.max_row + 1):cell = sheet.cell(row,3)

步骤5. 现在我们需要计算修正后的价格。所以我们将保存在单元格变量中的值乘以 0.9。计算完成后，我们需要在第 4 列中添加所有更正后的价格。添加新列，引用第四列的单元格。创建单元格后，我们需要在此单元格中设置更正后的价格值。

corrected_price = float(cell.value) * 0.9corrected_price_cell = sheet.cell(row, 4)corrected_price_cell.value = corrected_price

步骤6. 工作已经完成了一半。我们已经计算了更新后的价格，并将其添加到第四列中。现在我们需要向当前工作表添加一个图表。要创建图表，我们需要选择一个值的范围。

在这个项目中，我们将选择第四列中的值（更新后的价格），我们将在我们的图表中使用它（我们只需要一堆数字来创建一个图表。

我们需要使用Reference()类来选择一个范围的值。我们将向这个构造函数添加五个参数。第一个参数是我们正在处理的工作表。接下来的两个参数 min_row = 2 和 max_row= sheet.max_row 将选择从第 2 行到最后一个有数据的单元格。要仅从第四列中选择条目，我们需要传递另外两个参数 min_col=4 和 max_col=4。将结果存储在变量“值”中。

values = Reference(sheet, min_row=2, max_row=sheet.max_row, min_col=4 , max_col=4)

步骤7. 现在我们已准备好创建图表。我们将为 BarChart() 类创建一个实例，变量名为chart。创建后，在此图表中添加值。之后，将此图表添加到工作表的G2单元格中。

chart = BarChart()chart.add_data(values)sheet.add_chart(chart,‘G2‘)

步骤8. 现在我们需要保存所有更新的条目和我们在上面的代码中创建的图表。我们将把它保存在一个用文件名+时间命名的文件中，因为我们不想在我们的程序有错误的情况下意外覆盖源文件。

wb.save(filename + " " + tm + ‘.xlsx‘)

步骤9. 执行process_workbook()函数，括号里面写如prod.xlsx文件的位置：

process_workbook(‘/data/prod.xlsx‘)

执行prod.py脚本

运行下面命令为脚本添加执行权限，并执行脚本：

[root@localhost data]# chmod +x /data/prod.py [root@localhost data]# /data/prod.py

总结

这只是使用 Python 自动执行重复性无聊任务的一个示例。但请记住，自动化不仅仅与 Excel 电子表格有关。