openpyxl read tables from existing data book example?

Question

In the openpyxl documentation there is an example of how to place a table into a workbook but there are no examples of how to find back the tables of a workbook. I have an XLS file that has named tables in it and I want to open the file, find all of the tables and parse them. I cannot find any documentation on how to do this. Can anyone help? In the meantime I worked it out and wrote the following class to work with openpyxl: ``` class NamedArray(object): ''' Excel Named range object Reproduces the named range feature of Microsoft Excel Assumes a definition in the form <Worksheet PinList!$A$6:$A$52 provided by openpyxl Written for use with, and initialised by the get_names function After initialisation named array can be used in the same way as for VBA in excel Written for openpyxl version 2.4.1, may not work with earlier versions ''' C_CAPS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' def __init__(self, wb, named_range_raw): ''' Initialise a NameArray object from the named_range_raw information in…

Accepted Answer

I agree that the documentation does not really help, and the public API also seems to have only `add_table()` method. But then I found an openpyxl Issue 844 asking for a better interface, and it shows that worksheet has an `_tables` property.

This is enough to get a list of all tables in a file, together with some basic properties:

```
from openpyxl import load_workbook
wb = load_workbook(filename = 'test.xlsx')
for ws in wb.worksheets:
    print("Worksheet %s include %d tables:" % (ws.title, len(ws._tables)))
    for tbl in ws._tables:
        print(" : " + tbl.displayName)
        print("   -  name = " + tbl.name)
        print("   -  type = " + (tbl.tableType if isinstance(tbl.tableType, str) else 'n/a')
        print("   - range = " + tbl.ref)
        print("   - #cols = %d" % len(tbl.tableColumns))
        for col in tbl.tableColumns:
            print("     : " + col.name)
```

Note that the if/else construct is required for the `tableType`, since it can return `NoneType` (for standard tables), which is not convertible to `str`.

openpyxl read tables from existing data book example?

Solution

Problem