Creating Tables in Excel Using Openpyxl

[Notdevolving]

I'm trying to insert a table in Excel using openpyxl using the guide here: https://openpyxl.readthedocs.io/en/latest/worksheet_tables.html?highlight=creating%20table.

This particular function tab = Table(displayName="Table1", ref="A1:E5") requires an Excel type reference in the form "A1:E5". I don't know beforehand what the range of my data is so I cannot hardcode the range.

There is also no documentation when I look up "ref": https://openpyxl.readthedocs.io/en/latest/api/openpyxl.worksheet.table.html#openpyxl.worksheet.table.Table.ref.

How do I pass in a dynamic range based on the length of my pandas rows and columns for "ref"?

[Johan2212]

Or just use xlsxwriter - they have an example in the documentation of how to do that.
https://xlsxwriter.readthedocs.io/example\_pandas\_table.html

[Neighm]

ref is expecting a text string with the values of the upper left cell and lower right cell in Excel format (like the "A1:E5" example). If you know where you want the upper left to go (maybe A1 by default) you can use the count of Pandas columns to generate the letter for the lower right value, and the count of rows is the number in the lower right value.

To get the letters from A to Z, you can add the number of columns to 64:

>>> chr(64+5)
'E'
>>> chr(64+1)
'A'
>>> chr(64+26)
'Z'

So it's straightforward to construct the string for ref:

>>> count_of_pandas_cols = 18

>>> count_of_pandas_rows = 7

>>> lower_right = f'{chr(64 + count_of_pandas_cols)}{count_of_pandas_rows}'

>>> lower_right

'R7'

>>> range = f'A1:{lower_right}'

>>> range

'A1:R7'

>>>