python读取excel数字数据是object类型_使用Python从Excel工作表的ListObject打开和获取数据...

The Problem:

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

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)

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)

So far so good as well.

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.

The question.

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

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.

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.

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:

解决方案

James is right:

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

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

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

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.

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