Export to pivoted Excel inconsistent formatting

[carlosaran667]

Bug description

I tried the new "Export to Pivoted Excel" feature for pivot tables and found an inconsistency in number formatting when using a custom D3_FORMAT.

I am using standard Spanish formatting in config.py:
D3_FORMAT: D3Format = {
"decimal": ",",
"thousands": ".",
"grouping": [3],
"currency": ["", "€"]
}

Steps to reproduce

1. Swap decimal and thousands separators in D3_FORMAT in the configuration file.
2. Restart Superset.
3. Create a "pivot table" graph.
4. Select a metric with decimal values.
5. Click on Download -> Export to Pivoted Excel

Expected behavior

Exported decimal values should respect the decimal separator defined in D3_FORMAT.

Actual behavior

When exporting with Export to Pivoted Excel, plain decimal numbers do not respect the decimal separator configured in D3_FORMAT.

Percentages and currency values are exported correctly (although percentage values with decimals seem to have a separate issue, which I will report independently).

In my case, since I switched from the default American-style formatting to Spanish formatting, this causes wrong values to be exported to Excel.

Additional context

I tried modifying "EXCEL_EXPORT: dict[str, Any] = {}" in config.py, but it looks like this export path is using a front-end hard-coded configuration instead of EXCEL_EXPORT.

Screenshots/recordings

Superset UI:

Excel:

Superset version

6.0.0

Python version

3.10

Node version

I don't know

Browser

Firefox

Additional context

Tested on both Firefox and Microsoft Edge.

FEATURE_FLAGS: dict[str, bool] = {
"DASHBOARD_RBAC": True,
"ENABLE_TEMPLATE_PROCESSING": True,
"CACHE_QUERY_BY_USER": True,
"CACHE_IMPERSONATION": True,
}

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

Hi @carlosaran667! I'm Dosu and I’m helping the superset team.

This is a known architectural limitation in Superset. D3_FORMAT only affects frontend UI rendering and does not apply to CSV/Excel exports.

Why percentages/currency work but plain numbers don't: Percentages and currency appear to be pre-formatted in the frontend before being sent to the export pipeline, while plain decimal numbers are sent as raw values and rely on the Excel export logic, which uses pandas' to_excel() method without custom number formatting.

Why EXCEL_EXPORT doesn't help: This config only passes arguments to pandas' to_excel() method, which doesn't have parameters for locale-specific number formatting like D3_FORMAT provides.

Related developments:

• PR #38170 fixes CSV decimal separator issues but does not address Excel exports
• Issue #35524 tracks a related D3_FORMAT regression in 6.0.0-RC2

Workarounds (limited):

1. Post-process exported files using external tools (openpyxl, Excel macros)
2. Use CSV export instead (once PR fix(csv): respect CSV_EXPORT config for decimal separator and delimiter #38170 is merged) and import into Excel with correct locale settings
3. Code modification: Patch superset/utils/excel.py to access xlsxwriter's formatting API and apply custom number formats based on D3_FORMAT

This would require mapping D3 format patterns to Excel number format codes and applying them via workbook.add_format() - a non-trivial change that isn't currently on the roadmap.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}

   

[carlosaran667]

@dosu Since it's my first time working on Superset's backend files (other than config.py), walk me through what I should try to change in utils/excel.py.
Let's start simple by hard-coding my D3_FORMAT directly, not reading it from the config file.

[dosubot]

Here's how to modify superset/utils/excel.py to hardcode Spanish number formatting:

Current code (lines 43-53):

def df_to_excel(df: pd.DataFrame, **kwargs: Any) -> Any:
    output = io.BytesIO()
    df = quote_formulas(df)
    with pd.ExcelWriter(output, engine="xlsxwriter") as writer:
        df.to_excel(writer, **kwargs)
    return output.getvalue()

Replace with:

def df_to_excel(df: pd.DataFrame, **kwargs: Any) -> Any:
    output = io.BytesIO()
    df = quote_formulas(df)
    
    with pd.ExcelWriter(output, engine="xlsxwriter") as writer:
        df.to_excel(writer, **kwargs)
        
        # Access xlsxwriter objects for custom formatting
        workbook = writer.book
        worksheet = writer.sheets['Sheet1']
        
        # Spanish format: period for thousands, comma for decimal
        spanish_format = workbook.add_format({
            'num_format': '#.##0,00'
        })
        
        # Apply to numeric columns
        numeric_cols = df.select_dtypes(include=['float64', 'int64', 'float32', 'int32']).columns
        for col_idx, col_name in enumerate(df.columns):
            if col_name in numeric_cols:
                # +1 offset because pandas writes index as first column by default
                excel_col_idx = col_idx + 1 if kwargs.get('index', True) else col_idx
                from xlsxwriter.utility import xl_col_to_name
                col_letter = xl_col_to_name(excel_col_idx)
                worksheet.set_column(f'{col_letter}:{col_letter}', None, spanish_format)
    
    return output.getvalue()

Key points:

• The format string '#.##0,00' uses period for thousands and comma for decimals (Spanish style)
• Access the workbook/worksheet after df.to_excel() writes the data
• The +1 offset accounts for pandas writing the DataFrame index as column A by default

After modifying, restart Superset and test the export. Let me know if you hit any issues!

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}

   

[carlosaran667]

@dosu nothing changed. It didn't throw any error either.
Maybe we can add some log messages to check what's actually going on or try something else.

[dosubot]

I found an important issue - looking at the actual excel.py source, the sheet name is "Sheet 1" (with a space), not 'Sheet1'. My earlier suggestion was wrong.

Let's add logging to verify your changes are being executed. Update your df_to_excel() function in superset/utils/excel.py:

import logging  # Add at top of file if not present

logger = logging.getLogger(__name__)

def df_to_excel(df: pd.DataFrame, **kwargs: Any) -> Any:
    logger.warning("=== df_to_excel called ===")  # Using warning so it shows regardless of log level
    logger.warning("DataFrame shape: %s, columns: %s", df.shape, list(df.columns))
    
    output = io.BytesIO()
    df = quote_formulas(df)
    
    with pd.ExcelWriter(output, engine="xlsxwriter") as writer:
        df.to_excel(writer, **kwargs)
        
        workbook = writer.book
        worksheet = writer.sheets["Sheet 1"]  # Note: "Sheet 1" with space!
        
        spanish_format = workbook.add_format({
            'num_format': '#.##0,00'
        })
        
        numeric_cols = df.select_dtypes(include=['float64', 'int64', 'float32', 'int32']).columns
        logger.warning("Numeric columns found: %s", list(numeric_cols))
        
        for col_idx, col_name in enumerate(df.columns):
            if col_name in numeric_cols:
                excel_col_idx = col_idx + 1 if kwargs.get('index', True) else col_idx
                from xlsxwriter.utility import xl_col_to_name
                col_letter = xl_col_to_name(excel_col_idx)
                worksheet.set_column(f'{col_letter}:{col_letter}', None, spanish_format)
                logger.warning("Applied Spanish format to column %s (%s)", col_name, col_letter)
    
    logger.warning("=== Excel generation complete ===")
    return output.getvalue()

After restarting Superset:

1. Perform the export and check your server logs for the === df_to_excel called === messages
2. If you don't see these log messages, the code isn't being reached (might be caching or a different export path)
3. If you do see them but no formatting change, let me know what the logs say about numeric columns

Also, make sure to fully restart Superset (not just reload) after making changes.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}

   

[carlosaran667]

Yeah, as I thought, excel.py is used on "Export to Excel", but not on the "Export to pivoted Excel" button.
@dosu can you check what does the button "Export to pivoted Excel" actually executes?

[dosubot]

That explains it! The "Export to Pivoted Excel" feature runs entirely in the browser - it doesn't go through the Python backend at all.

Based on PR #33569, this feature:

1. Uses the SheetJS (xlsx) JavaScript library in the browser
2. Reads the rendered HTML pivot table directly from the DOM (the .pvtTable element you see on screen)
3. Converts it to Excel using XLSX.utils.table_to_book() client-side
4. Downloads the file directly without any API calls

This is why modifying superset/utils/excel.py has no effect - that code is never executed for pivoted Excel exports.

To customize the number formatting for pivoted Excel exports, you'd need to modify the frontend JavaScript code. The relevant file would be something like downloadAsPivotExcel.ts in the superset-frontend directory. You can verify this is what's happening by:

1. Opening browser DevTools (F12) → Network tab
2. Clicking "Export to Pivoted Excel"
3. You should see no network request to the backend - it's all happening client-side

If you want to fix the formatting, the approach would be to find where SheetJS generates the Excel file in the frontend code and configure the number format there. Would you like me to help trace the exact frontend code location?

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}

   

[carlosaran667]

Since I cannot modify the frontend in my installation, I don't think I can go much further on my side.

If anyone wants to take it from here, I would be happy to provide any additional context that may help.