uweziegenhagen.de

import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
 
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]
 
wb.save('04.xlsx')
wb.close()