def to_excel_table(df, writer, sheet_name, column_widths=None, max_col_width=50, startrow=0, do_column_widths=True, **kwargs): df.to_excel(writer, sheet_name=sheet_name, startrow=startrow+1, header=False, index=False, **kwargs) if column_widths is None: column_widths = ( df.apply(lambda x: x.astype(str).str.len().max(), axis=0) .apply(lambda x: min(x, max_col_width)) .to_dict() ) # Get the xlsxwriter workbook and worksheet objects. workbook = writer.book worksheet = writer.sheets[sheet_name] # Get the dimensions of the dataframe. (max_row, max_col) = df.shape max_row = startrow + max_row # Create a list of column headers, to use in add_table(). column_settings = [{"header": column} for column in df.columns] # Add the Excel table structure. Pandas will add the data. worksheet.add_table(startrow, 0, max_row, max_col - 1, {"columns": column_settings}) # Make the columns wider for clarity. if do_column_widths: for col_index, column in enumerate(df.columns): worksheet.set_column( col_index, col_index, min(max(column_widths.get(column, 12), len(column)), max_col_width) )