topic Re: Excel File from Databricks Not Auto-Adjusting Columns in Power Automate Email Attachment in Data Engineering

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

DataP1 — Sun, 01 Jun 2025 22:42:13 GMT

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!

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

niteshm — Sat, 07 Jun 2025 07:56:52 GMT

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.

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

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

Kayla — Sat, 07 Jun 2025 14:24:12 GMT

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.

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

mark_ott — Wed, 29 Oct 2025 20:17:37 GMT

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:

python

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:

typescript

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.