Python快速搞定 Excel 文档

最新推荐文章于 2024-08-09 16:01:19 发布

lau_jw

最新推荐文章于 2024-08-09 16:01:19 发布

阅读量260

点赞数

文章标签： python excel

本文链接：https://blog.csdn.net/lau_jw/article/details/114588227

版权

Microsoft Office 被广泛用于商务和运营分析中，其中 Excel 尤其受欢迎。Excel 可以用于存储表格数据、创建报告、图形趋势等。在深入研究用 Python 处理 Excel 文档之前，让我们先了解一些基本术语：

Spreadsheet（电子表格）或者 Workbook（工作簿） – 指文件本身(.xls or .xlsx).
Worksheet（工作表）或者 Sheet（表）–工作簿中的单个内容表，电子表格可以包含多个工作表。
Column（列） – 用英文字母标记的垂直数列，以“ A”开头。
Row（行） – 从1开始以数字标记的水平数列。
Cell（单元格） – 列和行的组合，例如“ A1”。

在本文中，我们来使用Python处理Excel电子表格。您将了解以下内容：

Python 读写 Excel 的第三方库
从工作簿中获取工作表
读取单元格数据
遍历行和列
写入 Excel 电子表格
添加和删除工作表
添加和删除行和列

大多数公司和大学都使用Excel，它可以用多种不同方式使用，并可以使用Visual Basic for Applications（VBA）进行增强。但是，VBA有点笨拙，这就是为什么要学习如何将 Excel 与 Python 结合使用。

现在让我们了解如何使用 Python 处理 Microsoft Excel 电子表格！

Python 处理 Excel 的第三方库

您可以使用 Python 创建、读取和编写 Excel 电子表格。但是，Python 的标准库不支持使用 Excel，为此您需要安装第三方软件包。其中最受欢迎的是OpenPyXL，您可以在此处阅读其文档：

https://openpyxl.readthedocs.io/en/stable/

OpenPyXL 并不是您唯一的选择，其实还有其他几个支持 Microsoft Excel 的软件包：

xlrd – 用于读取旧格式的 Excel (.xls) 文件
xlwt – 用于写入旧格式的 Excel (.xls) 文件
xlwings – 用于新格式的Excel格式并具有宏功能

几年前，前两个曾经是 Python 操作 Excel 文档的最受欢迎的库。然而，这些软件包的作者已停止维护它们。xlwings软件包潜力很大，但是不能在所有平台上都起作用，并且需要安装 Microsoft Excel。

您将在本文中使用 OpenPyXL，因为它是在持续开发和维护的。OpenPyXL 不需要安装 Microsoft Excel，并且可以在所有平台上使用。

你可以用 pip 命令来安装 OpenPyXL:

$ python -m pip install openpyxl

安装完成后，让我们了解如何使用 OpenPyXL 读取 Excel 电子表格！

从工作簿中获取工作表

第一步是找到一个与 OpenPyXL 一起使用的Excel文件，本文项目的Github存储库中为您提供了一个books.xlsx文件。您可以通过以下网址下载它：

https://github.com/driscollis/python101code/tree/master/chapter38_excel

您也可以用自己的文件，尽管您自己文件的输出内容与本文中的示例并不一样。

下一步是编写一些代码来打开电子表格。为此请创建一个名为open_workbook.py的新文件，并将以下代码添加到其中：

# open_workbook.py
from openpyxl import load_workbook
def open_workbook(path):
    workbook = load_workbook(filename=path)
    print(f'Worksheet names: {workbook.sheetnames}')
    sheet = workbook.active
    print(sheet)
    print(f'The title of the Worksheet is: {sheet.title}')
if __name__ == '__main__':
    open_workbook('books.xlsx')

在上述示例中，您从 openpyxl 导入 load_workbook（）函数，然后创建open_workbook（）函数，以将其导入Excel电子表格的路径中。接下来，使用load_workbook（）创建一个openpyxl.workbook.workbook.Workbook对象。该对象使您可以访问电子表格中的工作表和单元格。它确实确实具有双重工作簿的名称，那不是错字！

open_workbook（）函数的其余部分演示了如何打印出电子表格中所有当前定义的工作表，如何获取当前活动的工作表以及如何打印该工作表的标题。

运行此代码时，将看到以下输出：

Worksheet names: ['Sheet 1 - Books']
<Worksheet "Sheet 1 - Books">
The title of the Worksheet is: Sheet 1 - Books

既然已经知道如何访问电子表格中的工作表，下面就可以继续访问单元格数据了！

读取单元格数据

使用Microsoft Excel时，数据存储在单元格中。您需要使 Python 能访问这些单元格，以便提取该数据。OpenPyXL使这个过程变得很简单。

创建一个名为workbook_cells.py的新文件，并添加以下代码：

# workbook_cells.py
from openpyxl import load_workbook
def get_cell_info(path):
    workbook = load_workbook(filename=path)
    sheet = workbook.active
    print(sheet)
    print(f'The title of the Worksheet is: {sheet.title}')
    print(f'The value of {sheet["A2"].value=}')
    print(f'The value of {sheet["A3"].value=}')
    cell = sheet['B3']
    print(f'{cell.value=}')
if __name__ == '__main__':
    get_cell_info('books.xlsx')

此脚本将在 OpenPyXL 工作簿中加载 Excel 文件。您将获取当前工作表，然后打印出其标题和几个不同的单元格值。您可以通过以下方式访问单元格：使用工作表对象，后跟方括号以及其中的列名和行号。例如，sheet ["A2"]将为您获取第2行 A列的单元格。要获取该单元格的值，请使用value属性。

注意：这段代码使用的是 Python 3.8 中f-字符串格式化的新功能。如果使用较早的版本运行它，将会收到报错消息。

运行此代码时，将获得以下输出：

<Worksheet "Sheet 1 - Books">
The title of the Worksheet is: Sheet 1 - Books
The value of sheet["A2"].value='Title'
The value of sheet["A3"].value='Python 101'
cell.value='Mike Driscoll'

您可以尝试使用它的某些其他属性来获取有关单元格的其他信息。将以下函数添加到文件中，并在最后更新条件语句来运行它：

def get_info_by_coord(path):
    workbook = load_workbook(filename=path)
    sheet = workbook.active
    cell = sheet['A2']
    print(f'Row {cell.row}, Col {cell.column} = {cell.value}')
    print(f'{cell.value=} is at {cell.coordinate=}')
if __name__ == '__main__':
    get_info_by_coord('books.xlsx')

在此示例中，您将使用单元格对象的行和列属性来获取行和列信息。注意，“ A”列映射为“ 1”，“ B”映射为“ 2”，等等。如果要遍历Excel文档，则可以使用坐标属性来获取单元格名称。

运行此代码时，输出如下所示：

Row 2, Col 1 = Title
cell.value='Title' is at cell.coordinate='A2'

说到遍历，让我们来看一下接下来该下一步的方法！

遍历行和列

有时，您将需要遍历整个Excel电子表格或电子表格的某些部分。OpenPyXL允许您以几种不同的方式执行此操作。创建一个名为iterating_over_cells.py的新文件，并向其中写入以下代码：

# iterating_over_cells.py
from openpyxl import load_workbook
def iterating_range(path):
    workbook = load_workbook(filename=path)
    sheet = workbook.active
    for cell in sheet['A']:
        print(cell)
if __name__ == '__main__':
    iterating_range('books.xlsx')

在这里，您加载了电子表格，然后遍历“ A”列中的所有单元格。对于每个单元格，将打印出单元格对象。如果要更精细地格式化输出，则可以使用在上一节中提到的一些单元格属性。

这是通过运行此代码得到的：

<Cell 'Sheet 1 - Books'.A1>
<Cell 'Sheet 1 - Books'.A2>
<Cell 'Sheet 1 - Books'.A3>
<Cell 'Sheet 1 - Books'.A4>
<Cell 'Sheet 1 - Books'.A5>
<Cell 'Sheet 1 - Books'.A6>
<Cell 'Sheet 1 - Books'.A7>
<Cell 'Sheet 1 - Books'.A8>
<Cell 'Sheet 1 - Books'.A9>
<Cell 'Sheet 1 - Books'.A10>
# output truncated for brevity

输出被截断，因为默认情况下它将打印出很多单元格。OpenPyXL通过使用iter_rows（）和iter_cols（）函数提供了其他遍历行和列的方法。这些方法接受下面几个参数：

min_row
max_row
min_col
max_col

您还可以添加一个values_only参数，该参数告诉OpenPyXL返回单元格而不是单元格对象的值。继续创建一个名为iterating_over_cell_values.py的新文件，并将以下代码添加到其中：

# iterating_over_cell_values.py
from openpyxl import load_workbook
def iterating_over_values(path):
    workbook = load_workbook(filename=path)
    sheet = workbook.active
    for value in sheet.iter_rows(
            min_row=1, max_row=3,
            min_col=1, max_col=3,
            values_only=True,
        ):
        print(value)
if __name__ == '__main__':
    iterating_over_values('books.xlsx')

此代码演示了如何使用iter_rows（）迭代Excel电子表格中的行并打印出这些行的值。运行此代码时，将获得以下输出：

('Books', None, None)
('Title', 'Author', 'Publisher')
('Python 101', 'Mike Driscoll', 'Mouse vs Python')

输出是一个Python元组，其中包含每一列中的数据。至此，您已经了解了如何打开电子表格并从特定单元格以及通过迭代读取数据。现在，您准备学习如何使用OpenPyXL创建Excel电子表格！

写入 Excel 电子表格

使用OpenPyXL写入Excel电子表格不需要很多代码。您可以使用Workbook（）类创建电子表格。继续创建一个名为Writing_hello.py的新文件，并添加以下代码：

# writing_hello.py
from openpyxl import Workbook
def create_workbook(path):
    workbook = Workbook()
    sheet = workbook.active
    sheet['A1'] = 'Hello'
    sheet['A2'] = 'from'
    sheet['A3'] = 'OpenPyXL'
    workbook.save(path)
if __name__ == '__main__':
    create_workbook('hello.xlsx')

在这里，初始化Workbook（）并获取当前工作表。然后将“ A”列中的前三行设置为不同的字符串。最后，调用save（）函数并向其传递新文档保存到的路径。恭喜你！您刚刚使用Python创建了一个Excel电子表格。

接下来，让我们看一下如何在工作簿中添加和删除工作表！

添加和删除工作表

许多人喜欢在工作簿中的多个工作表中处理数据。OpenPyXL支持通过其create_sheet（）方法向Workbook（）对象添加新工作表。

创建一个名为creating_sheets.py的新文件，并添加以下代码：

# creating_sheets.py
import openpyxl
def create_worksheets(path):
    workbook = openpyxl.Workbook()
    print(workbook.sheetnames)
    # Add a new worksheet
    workbook.create_sheet()
    print(workbook.sheetnames)
    # Insert a worksheet
    workbook.create_sheet(index=1,
                          title='Second sheet')
    print(workbook.sheetnames)
    workbook.save(path)
if __name__ == '__main__':
    create_worksheets('sheets.xlsx')

在这里，您使用了两次create_sheet（）将两个新的工作表添加到工作簿中。第二个示例显示了如何设置工作表的标题以及在哪个索引处插入工作表。参数index = 1表示该工作表将在第一个现有工作表之后添加，因为它们的索引从0开始。

运行此代码时，将看到以下输出：

['Sheet']
['Sheet', 'Sheet1']
['Sheet', 'Second sheet', 'Sheet1']

您可以看到新工作表已逐步添加到您的工作簿中。保存文件后，可以通过打开Excel或另一个与Excel兼容的应用程序来验证是否存在多个工作表。

在完成自动工作表创建过程之后，突然有了太多的工作表，因此让我们来删除一些工作表。有两种方法可以删除工作表，继续并创建delete_sheets.py文件，以了解如何使用 Python 的 del方法删除工作表：

# delete_sheets.py
import openpyxl
def create_worksheets(path):
    workbook = openpyxl.Workbook()
    workbook.create_sheet()
    # Insert a worksheet
    workbook.create_sheet(index=1,
                          title='Second sheet')
    print(workbook.sheetnames)
    del workbook['Second sheet']
    print(workbook.sheetnames)
    workbook.save(path)
if __name__ == '__main__':
    create_worksheets('del_sheets.xlsx')

此代码将创建一个新的工作簿，然后向其中添加两个新的工作表。再使用Python的del方法删除workbook['Second sheet']。您可以通过查看在使用del命令之前和之后工作表列表的打印输出来验证它是否按预期工作：

['Sheet', 'Second sheet', 'Sheet1']
['Sheet', 'Sheet1']

从工作簿中删除工作表的另一种方法是使用remove（）方法。创建一个名为remove_sheets.py的新文件，并输入以下代码以了解其工作原理：

# remove_sheets.py
import openpyxl
def remove_worksheets(path):
    workbook = openpyxl.Workbook()
    sheet1 = workbook.create_sheet()
    # Insert a worksheet
    workbook.create_sheet(index=1,
                          title='Second sheet')
    print(workbook.sheetnames)
    workbook.remove(sheet1)
    print(workbook.sheetnames)
    workbook.save(path)
if __name__ == '__main__':
    remove_worksheets('remove_sheets.xlsx')

此时您可以通过将结果分配给sheet1来保留对所创建的第一个工作表的引用。然后稍后在代码中将其删除。另外，您也可以使用与之前相同的语法删除该工作表，如下所示：

workbook.remove(workbook['Sheet1'])

无论选择哪种方法删除工作表，输出内容都将相同：

['Sheet', 'Second sheet', 'Sheet1']
['Sheet', 'Second sheet']

现在，继续学习如何添加和删除行和列。

添加、删除行和列

OpenPyXL具有几种的方法，可用于在电子表格中添加、删除行和列。这是在本节中将要学习的四种方法：

.insert_rows()
.delete_rows()
.insert_cols()
.delete_cols()

每一个都可以使用下面两个参数：

idx –插入行或列的索引
amount–要添加的行数或列数

要查看其工作原理，请创建一个名为insert_demo.py的文件，并向其中添加以下代码：

# insert_demo.py
from openpyxl import Workbook
def inserting_cols_rows(path):
    workbook = Workbook()
    sheet = workbook.active
    sheet['A1'] = 'Hello'
    sheet['A2'] = 'from'
    sheet['A3'] = 'OpenPyXL'
    # insert a column before A
    sheet.insert_cols(idx=1)
    # insert 2 rows starting on the second row
    sheet.insert_rows(idx=2, amount=2)
    workbook.save(path)
if __name__ == '__main__':
    inserting_cols_rows('inserting.xlsx')

在这里，您将创建一个工作表，并在“ A”列之前插入一个新列。列的索引从1开始，而工作表的索引从0开始。这有效地将A列中的所有单元格移到B列。然后从第2行开始插入两个新行。

现在您知道了如何插入列和行，是时候来了解如何删除它们了。

要了解如何删除列或行，请创建一个名为delete_demo.py的新文件并添加以下代码：

# delete_demo.py
from openpyxl import Workbook
def deleting_cols_rows(path):
    workbook = Workbook()
    sheet = workbook.active
    sheet['A1'] = 'Hello'
    sheet['B1'] = 'from'
    sheet['C1'] = 'OpenPyXL'
    sheet['A2'] = 'row 2'
    sheet['A3'] = 'row 3'
    sheet['A4'] = 'row 4'
    # Delete column A
    sheet.delete_cols(idx=1)
    # delete 2 rows starting on the second row
    sheet.delete_rows(idx=2, amount=2)
    workbook.save(path)
if __name__ == '__main__':
    deleting_cols_rows('deleting.xlsx')

此代码在多个单元格中创建文本，然后使用delete_cols（）删除A列。它还通过delete_rows（）从第二行开始删除两行。在处理数据时，能够添加、删除列和行会非常有用。

总结

由于Excel在许多行业中得到广泛使用，因此能够使用Python与Excel文件进行交互是一项非常有用的技能，比如帮妹纸处理运营数据。在本文中，您掌握了以下内容：