使用Python从Excel工作表的ListObject打开和获取数据 [英] Open and Fetch data from a ListObject of an Excel sheet with Python

问题描述

问题: 在python环境中打开Excel文件的ListObject(excel表).

The Problem: Open a ListObject (excel table) of an Excel file from y python environment.

原因: 有多种解决方案可在python中打开excel文件.从熊猫开始:

The why: There are multiple solutions to open an excel file in python. Starting with pandas:

import pandas as pd
mysheetName="sheet1"
df = pd.read_excel(io=file_name, sheet_name=mysheetName)

这会将sheet1传递到熊猫数据框. 到目前为止一切顺利.

This will pass the sheet1 into a pandas data frame. So far so good.

其他更详细的解决方案是使用特定的库.这是一个堆栈溢出问题的代码.

Other more detailed solution is using specific libraries. This one being a code of a stack overflow question.

from openpyxl import load_workbook
wb2 = load_workbook('test.xlsx')
print wb2.get_sheet_names()
['Sheet2', 'New Title', 'Sheet1']

worksheet1 = wb2['Sheet1'] # one way to load a worksheet
worksheet2 = wb2.get_sheet_by_name('Sheet2') # another way to load a worksheet
print(worksheet1['D18'].value)

到目前为止也很好.

但是: 如果您在工作表中有一个ListObject(excel表)，则找不到任何访问Listobject数据的方法. Excel的高级用户经常使用ListObjects.最重要的是在VBA中对宏进行编程时.它非常方便，可以看作是Excel中的pandas数据框.在Excel Listobject和pandas数据框之间架起一座桥梁似乎很合逻辑.不过，到目前为止，我还没有找到任何解决方案，库或解决方法.

BUT: If you have a ListObject (excel table) in a sheet I did not find any way to access the data of the Listobject. ListObjects are often used by a bit more advance users of Excel; above all when programming macros in VBA. There are very convenient and could be seen as the equivalent of a pandas dataframe in Excel. Having a bridge between Excel Listobject and a pandas data frame seems like super logical. Nevertheless I did not find so far any solution, library or workaround for doing that.

问题. 有谁知道一些从Python表格直接提取Listobjects的python库/解决方案?

The question. Does anyone know about some python lybrary/solution to directly extract Listobjects form Excel sheets?.

注意1:不好的解决方案 当然，知道Listobject的位置"是可以引用开始和最后一个单元格的，但这是一个非常糟糕的解决方案，因为不允许您修改excel文件中的Listobject(必须修改python.马上).只要ListObject的位置发生变化，或者ListObject本身变大，Python代码就会被破坏.

NOTE1: Not nice solution Of course knowing the "placement" of the Listobject it is possible to refer to the start and last cell, but this is a really bad solution because does not allow you to modify the Listobject in the excel file (the python would have to be modified straight away). As soon as the placement of the ListObject changes, or the listobject itself gets bigger, the python code would be broken.

注意2:我当前的解决方案: 我将Excel中的listObject(带有宏)导出到JSON文件中，然后从python中读取它.但是额外的工作是显而易见的. VBA代码，其他文件等.

NOTE2: My current solution: I export the listObject from excel (with a macro) into a JSON file and read it from python. But the extra work is obvious. VBA code, extra file etc etc.

最后评论:如果有人对此问题感兴趣，但仍然不知道excel中的ListObject是什么，请

Last comment: If someone is interested about this issue but still don't have a clue what is a ListObject in excel here click and see here:

推荐答案

詹姆斯是对的:

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

https://openpyxl.readthedocs.io/en /stable/api/openpyxl.worksheet.table.html

openpyxl中有一个用于读取表的类.另外按ID:

There is a class in openpyxl to read tables. Also by id:

class openpyxl.worksheet.table.Table(id=1,...

id = 1表示工作表的第一张表.

id=1 would mean the first table of the worksheet.

请始终记住，Excel中的ListObjects称为表.那就很奇怪了(在VBA中经常出现).如果使用VBA，您可能会忘记ListObject = Table.

Remember always that ListObjects in Excel are called Tables. Thats weird (as oft with VBA). If you work with VBA you might forget that the ListObject=Table.

也可以使用xlwings.该API有所不同:

With xlwings is also possible. The API is a bit different:

import xlwings as xw
wb = xw.Workbook.active()
xw.Range('TableName[ColumnName]').value

或者要获取包括标题和总计行的列，您可以执行以下操作:

Or to get the column including header and Total row, you could do:

xw.Range('TableName[[#All], [ColumnName]]').value

这篇关于使用Python从Excel工作表的ListObject打开和获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！