python怎样操作excel表格进行自动化？

数据存储、分析和展示通常使用 Excel 完成。而另一方面，流行的编程语言 Python 因其易于使用、适应性和多功能性而闻名。Python 提供了一些库，可与 Excel 电子表格一起使用，用于各种任务自动化，我们可以轻松地利用 Python 实现 Excel 电子表格自动化。为了实现这一点，本文将介绍不同的方法。

方法

• 使用 openpyxl 库

• 使用 pandas 库

• 使用 xlwings 库

方法 1：使用 openpyxl 库

可以使用 Python 的 Openpyxl 包与 Excel 文件进行交互。这个库允许我们读取、写入和编辑 Excel 文件。

可以使用以下命令安装 openpyxl 模块:

pip install openpyxl

假设有以下内容的 Excel 文件如下:

现在，我们想自动添加某个员工的奖金+薪水的数据到新的字段中存储。为此，我们将编写一些基于以下算法的代码。

算法

• 导入必要的模块

• 使用 openpyxl.load_workbook() 函数加载 Excel 工作簿 ‘python_worksheet.xlsx’ ，并将其分配给变量 ‘wb’。

• 使用 wb [‘Sheet1’] 选择工作簿中的第一个表，并将其分配给名为 ‘sheet’ 的变量。

• 循环遍历每一行的工作表，从第二行开始。

  • 使用 sheet.cell(row, 2) 访问当前行中第二列中的单元格，并将其分配给变量 ‘cell’。

  • 使用 sheet.cell(row, 3) 访问当前行中第三列中的单元格，并将其分配给变量 ‘salary_bonus_cell’。

  • 将 500 加到’cell’的值中，使用 int(cell.value) + 500，并将结果分配给’salary_bonus_cell’的值，使用 salary_bonus_cell.value = int(cell.value) + 500。

• 使用 wb.save(‘python-spreadsheet2.xlsx’) 将修改后的工作簿保存在新的文件名’python-spreadsheet2.xlsx’中。

步骤 1−导入所需的模块

import openpyxl as xl
from openpyxl.chart import BarChart, Reference

步骤 2−加载工作簿并选择工作表

wb = xl.load_workbook('python_worksheet.xlsx')
sheet = wb['Sheet1']

步骤 3−从第二行开始循环每一行，并根据需要执行计算。

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

步骤 4−使用新的文件名保存修改后的工作簿

wb.save('python-spreadsheet2.xlsx')

示例

# 导入必要的模块
import openpyxl as xl

# 加载工作簿并选择表
wb = xl.load_workbook('python_worksheet.xlsx')
sheet = wb['Sheet1']

# 从第二行开始循环遍历表中每一行
for row in range(2, sheet.max_row + 1):
    # 访问当前行中第二列的单元格
    cell = sheet.cell(row, 2)

    # 访问当前行中第三列的单元格
    salary_bonus_cell = sheet.cell(row, 3)

    # 将500添加到二列中的单元格值，并更新三列中的值
    salary_bonus_cell.value = int(cell.value) + 500

# 使用新的文件名保存修改后的工作簿
wb.save('python-spreadsheet2.xlsx')

openpyxl的方法涉及从Excel文件中直接读取并使用openpyxl模块将数据写入Excel文件。我们知道如何打开Excel文件，编辑其数据并生成新的列值。尽管此方法可用于处理单个单元格以及单元格范围，但由于此库的一些限制，代码可能会比第二种方法冗长且效果低于处理更大数据集。

输出

方法2：使用pandas库

pandas是一个主要用于数据分析的Python包。它也提供读写Excel文件的能力。以下是使用pandas自动化Excel表格的示例：

我们可以使用以下命令在Python中安装pandas：

pip install pandas

假设有一个包含以下内容的Excel文件：

现在，我们希望执行与上一个示例中描述的相同操作，我们将按照以下算法进行操作：

算法

• 导入pandas库作为pd。

• 使用pd.read_excel（）方法从Excel文件中读取数据。

• 指定sheet名称“Sheet1”以从Excel文件中读取。

• 使用pandas中提供的各种方法对数据进行必要的操作。

• 在名为“Bonus”的DataFrame中创建一个新列，该列将1000添加到“Salary”列中。

• 使用“to_excel（）”方法将更新的数据写回到新的Excel文件中，并在输出文件中指定index = False以防止pandas写入DataFrame的索引。

步骤1−使用语句“import pandas as pd”导入pandas库。

import pandas as pd

步骤2−使用pd.read_excel（）方法读取输入的Excel文件“python_worksheet.xlsx”，并将其存储在名为“data”的变量中。

data = pd.read_excel('python_worksheet.xlsx')

步骤3−在pd.read_excel（）方法中将表格名称指定为“Sheet1”，以从输入Excel文件的Sheet1工作表中读取数据。

data = pd.read_excel('python_worksheet.xlsx', sheet_name='Sheet1')

步骤4−通过将1000添加到现有的“Salary”列并将结果保存在新列中，在名为“data”的DataFrame中创建名为“Bonus”的新列。

data['Bonus'] = data['Salary'] + 1000

步骤5−使用“to_excel（）”方法将更新后的数据写回名为“python-spreadsheet2.xlsx”的新Excel文件中。将DataFrame“data”作为第一个参数传递，并指定index=False以防止将DataFrame的索引写入输出文件。

data.to_excel('python-spreadsheet2.xlsx', index=False)

步骤6−运行代码并检查输出的Excel文件“python-spreadsheet2.xlsx”，以验证“Bonus”列已添加到原始数据中。

示例

# 导入pandas库
import pandas as pd

# 读取名为“python_worksheet.xlsx”的Excel文件，
# 并将工作表名指定为“Sheet1”，将其存储为DataFrame类型的“data”
data = pd.read_excel('python_worksheet.xlsx', sheet_name='Sheet1')

# 在“data”中创建名为“Bonus”的新列，
# 通过将现有的“Salary”列加上1000来实现
data['Bonus'] = data['Salary'] + 1000

# 将更新后的数据写入新的名为“python-spreadsheet2.xlsx”的Excel文件中，
# 将DataFrame“data”作为第一个参数传递，
# 并指定index=False以防止将DataFrame的索引写入输出文件
data.to_excel('python-spreadsheet2.xlsx', index=False)

pandas的方法涉及使用DataFrames在Excel文件中读取和写入数据。在我们的示例中，我们展示了如何从Excel文件中获取数据，自动添加新列，然后将所有新数据放入另一个Excel文件中的示例。由于pandas提供了广泛的功能集来操作、清洗和分析数据，即使处理大型数据集时，这种方法也非常有用。

输出

在这个例子中，我们使用python自动填充了Excel表格的奖金列。

方法3：使用xlwings库

Xlwings是一个Python库，它使得用户可以使用Python与Microsoft Excel通信和自动化处理过程。它能够使用户以编写Excel公式和函数的方式，自动化Excel活动，如使用Python创建和编辑Excel图表和表格，以及编写Excel公式和函数。

此外，我们可以使用Python函数和库对Excel数据进行计算和分析，然后将结果返回到Excel中。

我们可以使用以下命令在Python中安装 xlwings ：

pip install xlwings

假设有一个以下内容的Excel文件−

现在，我们要执行与上一个示例中描述的相同操作，我们将按照以下算法进行操作：

算法

• 导入 xlwings 模块。

• 将Excel文件加载到 xlwings 工作簿对象中。

• 选择工作表并确定“Salary”列的最后一行。

• 将“Salary”列中的每个值加上500以创建新的“Bonus”列。

• 将结果写入相应的“Bonus”单元格中。

• 使用新的文件名保存修改后的工作簿。

• 关闭工作簿。

步骤1−导入 xlwings 模块。

import xlwings as xw

步骤2−将Excel文件加载到 xlwings 工作簿对象中。

wb = xw.Book('python_worksheet.xlsx')

步骤3−选择工作表并将500添加到’Salary’列以创建新的’Bonus’列。

sheet = wb.sheets['Sheet1']
last_row = sheet.range('B' + str(sheet.cells.last_cell.row)).end('up').row
sheet.range('C2:C' + str(last_row)).value = [[cell.value + 500] for cell in sheet.range('B2:B' + str(last_row))]

步骤4−使用新的文件名保存修改后的工作簿。

wb.save('python-spreadsheet2.xlsx')

步骤5−关闭工作簿。

wb.close()

示例

# 导入xlwings模块
import xlwings as xw

# 将Excel文件加载到xlwings Workbook对象中
wb = xw.Book('python_worksheet.xlsx')

# 选择工作表并将“工资”列加上500以创建新的“奖金”列
sheet = wb.sheets['Sheet1']
last_row = sheet.range('B' + str(sheet.cells.last_cell.row)).end('up').row
sheet.range('C2:C' + str(last_row)).value = [[cell.value + 500] for cell in sheet.range('B2:B' + str(last_row))]

# 使用新文件名保存修改后的工作簿
wb.save('python-spreadsheet2.xlsx')

# 关闭工作簿
wb.close()

这个方法涉及到使用专门用于通过Python代码自动化和操作Excel的 xlwings 库。由于这个库专门用于操作Excel文件，我们可以轻松地使用这种方法来自动化大型Excel任务。

输出

我们已经成功地在Excel表中增加了一个工资+奖金的列。

结论

使用Python自动化Excel表格可以处理大量的数据集、简化冗余和重复的任务以及生成更大的报告，这是一个有用的工具。本文介绍了三种流行的方法来自动化Excel表格。最终，您选择的方法将取决于任务的要求以及数据的大小和复杂性。如果您使用正确的策略和工具，则使用Python自动化Excel表格可以帮助您节省时间，减少手动错误，并提高工作效率。