Get tables from a worksheet

[adam.ca...@gmail.com]

Been banging my head against the desk for a while now trying to figure this out.  I'm trying to pull data out of an excel file and then using that to populate a Word doc.  Reading the values of individual cells in the excel sheet is easy but how do I read tables? I have 2 tables that I want to get the cell values of (and formatting if possible). The documentation goes over creating tables but not how to read them or even how to actually list which ones exist.  For some reason Openpyxl refuses to show the tables using defined names (even though excel does have names defined for the table ranges and it does show other defined names for cells).

Any help is appreciated!

[Charlie Clark]

The tables are in the ._tables attribute of worksheets. To inspect them
you can currently use the following:

for table in ws._tables:
print(table.ref)

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[ottomi...@gmail.com]

Hi Charlie,

Many thanks for your answer, but when I try this I get an attribute Error: 'str' object has no attribute 'ref'

Complete script is:

from openpyxl import load_workbook

from openpyxl.worksheet.table import Table

wb = load_workbook(filename='file.xlsx')

ws = wb['sheet']

for table in ws._tables:

    print(table.ref)

Do you have any additional advise?

Regards

Otto

[Charlie Clark]

On 10 Aug 2020, at 13:13, ottomi...@gmail.com wrote:

> Hi Charlie,
> Many thanks for your answer, but when I try this I get an attribute

> Error: '*str'
> object has no attribute 'ref'*

>
> Complete script is:
>
> from openpyxl import load_workbook
> from openpyxl.worksheet.table import Table
>
> wb = load_workbook(filename='file.xlsx')
> ws = wb['sheet']
>
> for table in ws._tables:
> print(table.ref)
>
> Do you have any additional advise?

You're using a newer version with a more sophisticated API:

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

[ottomi...@gmail.com]

Thanks Charlie for the quick reply.

Then the question is, how can I iterate through the tables in a sheet in the new version?

Thanks

[Charlie Clark]

On 10 Aug 2020, at 17:27, ottomi...@gmail.com wrote:

> Thanks Charlie for the quick reply.
>
> Then the question is, how can I iterate through the tables in a sheet in
> the new version?

What don't you understand in the new documentation?

[ottomi...@gmail.com]

Ok I got it, this works properly:

for table in ws.tables.values():
    print(table.headerRowCount)
    print(table.ref)

    print(len(table.tableColumns))

Many thanks to you!