Automatically create pivot tables with Excel export

0
Hello. I'm currently looking for a way to automatically create a pivot table within Excel based on the data inserted into the Excel export. How can I do this?
asked
1 answers
2

Hi bosung kim

 

There are two methods

 

method1:Use an Excel template that already contains the pivot

Idea: create an .xlsx (or .xlsm) file with:

  • A sheet named Data (or whatever name you pick) that contains a formatted Excel Table (Insert → Table).

  • A PivotTable built from that table.

  • Configure the PivotTable to refresh on open — or add a small Workbook_Open VBA macro that calls ThisWorkbook.RefreshAll (if you use .xlsm).

Why this often wins

  • No server-side 3rd-party libs needed.

  • Minimal Mendix code: you only fill the table rows with your exported data and return the file.

  • Excel handles pivot creation and refresh; users always see up-to-date pivot when opening the file.

How to integrate in Mendix

  1. Create template Excel file (e.g. pivot-template.xlsx):

    • Sheet Data has a header row matching the attributes you export.

    • Convert header+one row to an Excel Table (Name it e.g. ExportTable).

    • Create a PivotTable on another sheet referencing ExportTable.

    • In PivotTable Options → Data → check Refresh data when opening the file (if available).

    • Alternatively (if you want guaranteed refresh), save as .xlsm and add this VBA in ThisWorkbook:

      Private Sub Workbook_Open() 
ThisWorkbook.RefreshAll 
End Sub

      (Note: macros require .xlsm; some organizations block macros — weigh that.)

Method 2: Generate the pivot server-side using Apache POI (Java action)

Method 2 is more like you have to build a custom java action to create a pivot table and the entire excel sheet, This will be little hard  and you may need jars to do this.

answered