Yes, this is a common challenge when automating Excel file generation—the default export (especially from pandas or Databricks) does not auto-fit column widths, resulting in cramped columns when viewed or emailed. Auto-fitting columns typically requires an explicit command or script in your workflow. Reliable solutions exist in both Python and Power Automate/Office Scripts contexts.
Python-Based Solutions: openpyxl / xlsxwriter
When exporting to Excel from Databricks using pandas’ to_excel, you can post-process the output file with openpyxl or xlsxwriter to auto-size columns:
-
openpyxl: Load the file, then iterate through columns to set the width based on max content length.
-
xlsxwriter: When writing the file, use worksheet.set_column() to fit columns.
Example using openpyxl:
from openpyxl import load_workbook
def autofit_columns(filename):
wb = load_workbook(filename)
ws = wb.active
for col in ws.columns:
max_length = 0
col_letter = col[0].column_letter
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except:
pass
adjusted_width = max_length + 2
ws.column_dimensions[col_letter].width = adjusted_width
wb.save(filename)
# After pandas to_excel:
autofit_columns('your_file.xlsx')
This reliably fixes the column width issue regardless of export source.
Power Automate / Office Scripts
If your Excel file is generated and stored in OneDrive/SharePoint, you can use an Office Script in Power Automate to auto-fit columns before the email is sent.
Example Office Script:
function main(workbook: ExcelScript.Workbook) {
let ws = workbook.getActiveWorksheet();
ws.getUsedRange().getFormat().autofitColumns();
}
You can run this script in Power Automate using the "Run Script" action after the file is created and before the file is emailed.
Best Practice Workflow
-
Python method: Generate Excel → run autofit post-process → upload/send.
-
Power Automate method: Generate Excel → upload to cloud storage → run Office Script to autofit → email file.
Community and Reliability
Many users in forums (Microsoft, Stack Overflow, Databricks) confirm both methods are reliable fixes. Office Scripts are especially robust for cloud-based workflows; Python works well for on-prem or hybrid flows.
In summary:
Auto-fit is not applied by default; use openpyxl or xlsxwriter in Python or Office Scripts in Power Automate to reliably fix column widths in Excel exports before sending.
