python - 检查工作簿中的工作表并在缺少时添加

Question

我只是想简单地检查 .xlsx 文件中是否存在工作表，如果不存在，我想添加它。

book = load_workbook('test.xlsx')
writer = pd.ExcelWriter('test.xlsx', engine = 'openpyxl')
writer.book = book

if 'testSheet' in book.sheetnames:
    pass
else:
    book.add_sheet(book['testSheet'])

关于为什么这不起作用的任何想法？

Answer 1

如果您只使用扩展名为 *.xlsx 的 Excel 文件, 然后 openpyxl 具有有用的功能，允许您创建、访问、重命名、向 Excel 工作表添加/删除数据。虽然使用 openpyxl 访问工作簿的工作表似乎相当简单，但使用 Python 的异常处理可以帮助在工作表一开始不存在时捕获错误。考虑下面的示例，如果工作簿“test.xlsx”不存在名为“invalidSheet”的工作表，则会引发 KeyError。 try/except block 的工作是在工作表不存在时引发异常。这个简单示例的目的是仅识别 openpyxl 引发的异常的类型。

In [1]: import openpyxl

In [2]: book = openpyxl.load_workbook("test.xlsx")

In [3]: try:
   ...:     ws = book["invalidSheet"]  #try to access a non-existent worksheet
   ...: except:
   ...:     raise
   ...:
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-3-4f4ac71a4f19> in <module>
      1 try:
----> 2     ws = book["invalidSheet"]
      3 except:
      4     raise
      5

~\Anaconda3\lib\site-packages\openpyxl\workbook\workbook.py in __getitem__(self, key)
    275             if sheet.title == key:
    276                 return sheet
--> 277         raise KeyError("Worksheet {0} does not exist.".format(key))
    278
    279     def __delitem__(self, key):

KeyError: 'Worksheet invalidSheet does not exist.'

这有助于我们形成一个更明确的 try/except block 来捕获不存在的工作表。稍后，我们将改进此示例，但首先让我们找出此 Excel 电子表格中的工作表名称。我们使用 sheetnames Workbook 对象的属性 book我们之前创建的:

In [15]: book.sheetnames
Out[15]: ['testSheet1', 'testSheet2']

In [16]: type(book.sheetnames)
Out[16]: list

这将返回工作表名称列表。稍后我们将使用此信息来验证工作表名称。回到上面的例子，下面的改进版本捕获了 KeyError对于不存在的工作表，如果不存在则创建一个新工作表。然而，工作表不会出现在实际的 Excel 电子表格中，除非我们 save()它。另一方面，工作表名称仍将为该对象更新。您可以在执行代码段后验证这一点:

In [20]: try:
    ...:     filename = "test.xlsx"
    ...:     sheet_name = "invalidSheet"
    ...:     ws = book[sheet_name]
    ...: except KeyError:
    ...:     print("The worksheet '{}' does not exist for workbook '{}'. Creating one...".format(
    ...:                                                                                         sheet_name,
    ...:                                                                                         filename))
    ...:     book.create_sheet(sheet_name)
    ...:     print("Worksheet '{}' created successfully for workbook '{}'.".format(sheet_name, filename))
    ...:
The worksheet 'invalidSheet' does not exist for workbook 'test.xlsx'. Creating one...
Worksheet 'invalidSheet' created successfully for workbook 'test.xlsx'.

In [21]: book.sheetnames
Out[21]: ['testSheet1', 'testSheet2', 'invalidSheet']

现在我们已经添加了工作表“invalidSheet”，让我们添加一些数据并用更有意义的名称保存它。 Openpyxl 还提供了 Pandas dataframe support .我们将首先创建一个数据框，然后使用 dataframe_to_rows() 将数据框中的每一行(包括标题)附加到工作表中。方法，然后重命名工作表，最后保存。

In [23]: import pandas as pd

In [24]: df = pd.DataFrame({"Name": ["John", "Val", "Katie"], 
                           "Favorite Pet":["dog", "cat", "guinea pig"]})   #create dataframe

In [25]: df
Out[25]:
    Name Favorite Pet
0   John          dog
1    Val          cat
2  Katie   guinea pig

In [26]: from openpyxl.utils.dataframe import dataframe_to_rows #import method

In [27]: ws = book["invalidSheet"] #create a worksheet object for the existing sheet "invalidSheet"

In [29]: for r in dataframe_to_rows(df, index=False, header=True):
    ...:     ws.append(r)    #append each df row to the worksheet
    ...:                                    
In [31]: ws['A2'].value    #verify value at cell 'A2'. Remember, the first row will be the header
Out[31]: 'John'

In [32]: ws.title = "favPetSheet" #rename the worksheet

In [33]: book.sheetnames  #verify whether the sheet was added & renamed
Out[33]: ['testSheet1', 'testSheet2', 'favPetSheet']

In [35]: book.save("test.xlsx")  #save the workbook

在理想情况下，单个函数应该为工作簿和数据框的特定工作簿和工作表执行所有这些任务。

In [45]: def check_sheet_add_data(filename, sheetname, df):
    ...:     """Check if sheet exists for an xlsx spreadsheet and add data from dataframe to the sheet
    ...:        :param: filename - The filename of the xlsx spreadsheet
    ...:        :param: sheetname - Name of the worksheet to search for
    ...:        :param: df - A Pandas dataframe object"""
    ...:
    ...:     wb = openpyxl.load_workbook(filename)
    ...:     try:
    ...:         ws = wb[sheetname]
    ...:         print("Sheet '{}' found in workbook '{}'".format(sheetname, filename))
    ...:     except KeyError:
    ...:         print("Worksheet '{}' not found for workbook '{}'.Adding...".format(sheetname, filename))
    ...:         wb.create_sheet(sheetname)
    ...:         ws = wb[sheetname]
    ...:         print()
    ...:         print("Current sheetnames: {}".format(wb.sheetnames))
    ...:         print()
    ...:         print("Worksheet '{}' added successfully for workbook '{}'".format(sheetname, filename))
    ...:     finally:
    ...:         print()
    ...:         print("Adding data to worksheet '{}'...".format(sheetname))
    ...:         print()
    ...:         for r in dataframe_to_rows(df, index=False, header=True):
    ...:             ws.append(r)
    ...:         wb.save(filename)
    ...:         print("Workbook '{}' saved successfully.".format(filename))
    ...:         print()
    ...:         print("***End***")

准备好这个函数后，让我们测试所有条件。首先让我们添加一些新数据，比如为我们的老 friend John、Val 和 Katie 说“最喜欢的专辑”。

In [39]: df2 = pd.DataFrame({"Name":["John", "Val", "Katie"], 
                         "Favorite Album": ["Thriller", "Stairway to Heaven", "Abbey Road"]})

In [40]: df2
Out[40]:
    Name      Favorite Album
0   John            Thriller
1    Val  Stairway to Heaven
2  Katie          Abbey Road

我们的工作簿将是相同的“test.xlsx”，我们的新工作表将称为“favAlbumSheet”。测试现有和不存在的工作表的所有条件:

#Condition 1: Worksheet does not exist
In [44]: check_sheet_add_data(filename="test.xlsx", sheetname="favAlbumSheet", df=df2)
Worksheet 'favAlbumSheet' not found for workbook 'test.xlsx'.Adding...

Current sheetnames: ['testSheet1', 'testSheet2', 'favPetSheet', 'favAlbumSheet']

Worksheet 'favAlbumSheet' added successfully for workbook 'test.xlsx'

Adding data to worksheet 'favAlbumSheet'...

Workbook 'test.xlsx' saved successfully.

***End***

#Condition 2: Worksheet exists
In [46]: check_sheet_add_data(filename="test.xlsx", sheetname="favAlbumSheet", df=df2)
Sheet 'favAlbumSheet' found in workbook 'test.xlsx'

Adding data to worksheet 'favAlbumSheet'...

Workbook 'test.xlsx' saved successfully.

***End***

我们利用 Openpyxl 易于使用的功能来访问有效 Excel 工作簿中的工作表，并将数据帧中的数据添加到工作表。通过 Python 的异常处理，我们能够清楚地识别工作表(对于有效工作簿)的存在并在必要时添加一个。该功能可以进一步扩展以捕获其他错误，如无效文件名(FileNotFoundError)，无效数据框对象等。如果您不想每次都添加数据并且只检查工作表是否存在，请制作df可选参数:df=None并且只保存工作簿而不将任何数据附加到工作表，在 finally block 。