CODEWITHSUNDEEP
pythonexcelopenpyxl
Ronald Janssen
Ronald Janssen

Reputation: 41

Create table in excel - Python openpyxl

Is there a way to create a table based on all rows and columns which cointain data? Normally a table is created by putting in a fixed reference (eg: ref="A1:E5")) What I need is the script to find the last row on the sheet and use this as the reference. This is because the sheet I need to edit contain different amount of rows each time and if I set a fixed reference it will include empty rows in the table.

If have this as a macro in excel but want to convert this to python with openpyxl

Excel Macro

Sub A2_SelectAllMakeTable()
lrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(lrow, lCol)), ,        xlYes).Name = "Masterdata"
End Sub

python code start:

from openpyxl import load_workbook
wb = load_workbook('export1.XLSX')
ws1 = wb["Sheet1"]
ws1.title = "Masterdata"

Upvotes: 3

Views: 12510

Answers (2)

vishnufka
vishnufka

Reputation: 154

You can use get_column_letter, max_column, and max_row on your worksheet sheet, like so:

from openpyxl.worksheet.table import Table
from openpyxl.utils import get_column_letter

table = Table(displayName="Table1", ref="A1:" + get_column_letter(sheet.max_column) + str(sheet.max_row))

sheet.add_table(table)

Upvotes: 7

Chetan Patel
Chetan Patel

Reputation: 782

You can create table like this - 

tab = Table(displayName="Table1", ref="A1:E5")

# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
ws1.add_table(tab)

Example from official Doc - https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html

Upvotes: 4

Related Questions