Excel File from Databricks Not Auto-Adjusting Columns in Power Automate Email Attachment

[DataP1]

Hi community,

I've built an automation workflow using Databricks and Power Automate. The process runs a query in Databricks, exports the result to Excel, auto-adjusts the columns based on the header/content, and then Power Automate picks up the file and sends it via email.

Everything is working as expected except that the column auto-adjust in the Excel file is no longer applying correctly — the attachment comes through with unformatted (narrow) columns.

Has anyone faced a similar issue or found a reliable way to auto-fit Excel columns either within Databricks (Python) or as part of the Power Automate process?

Open to both Python-based solutions (e.g., using openpyxl or xlsxwriter) or Power Automate options like Office Scripts.

Thanks in advance!

[niteshm]

You can try, Office Scripts, "sheet.getUsedRange().getFormat().autofitColumns()" but it requires Microsoft 365 Business Standard, E3, E5, or Education license. Most enterprises would have this, please contact your IT dept to enable Office Scripts in the Microsoft 365 Admin Center.

Orelse, you can handle column auto-width, using openpyxl, entirely within Databricks before the file gets passed to Power Automate, no Office Scripts needed, no Excel Online dependency.

1. Convert Spark DataFrame to Pandas
2. Write headers and data to an Excel worksheet
3. Calculate max content length per column
4. Apply dynamic column widths using openpyxl.utils.get_column_letter
5. Save the file to DBFS or preferred location for PowerAutomate to pikup.

[Kayla]

I was actually playing with this almost this exact issue last week in Databricks/Python.
I found xlsxwriter was really easy to set up - the simplest way was to just convert it to a table. The code in their example Pandas table is very simple to update: https://xlsxwriter.readthedocs.io/example_pandas_table.html

Not sure if you need the full table or can get away with 

worksheet.autofit()

If you want to be very fancy, you can also specify column widths for each column. Since we're sending out Excel files for people to print, we did that so everything would be inside a standard page width.