uweziegenhagen.de

import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl.utils.cell import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.worksheet.table import Table, TableStyleInfo
 
 
dataframe = pd.DataFrame(np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), columns=['a', 'b', 'c'])
rows = dataframe_to_rows(dataframe, index=False, header=True)
 
wb = Workbook()
ws = wb.active
 
for r_idx, row in enumerate(rows, 1):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=value)
 
# Set column widths based on title width or fixed number
widths = {}
for column in ws.columns:
    if column[0].value is None: # no column header => Fixed with
        widths[column[0].column] = 10.5
    else: # if column header is present => min width resp. maximum
        widths[column[0].column] = max(len(str(column[0].value)) * 1.45, 10.5)
 
ws.column_dimensions[get_column_letter(column[0].column)].width = widths[column[0].column]
 
# Insert formatted table from A1 to max column/max row
tab = Table(displayName="MeineTabelle", ref='A1:' + get_column_letter(ws.max_column) + str(ws.max_row))
style = TableStyleInfo(name="TableStyleLight9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
 
wb.save('05.xlsx')
wb.close()