I’ve extensively used the Elixlsx package to generate complex Excel exports for a cashier app, covering various aspects like VAT, sales, discounts, and more (10 sheets export). While the library is functional, it has some limitations, such as lacking support for pivot tables, filters, and sorting. Additionally, the performance of the library is not great.

Looking back, I wish someone had shared a more efficient method for creating complex Excel exports before I began this project. Here’s an approach that could save you significant time and effort:

• Start by working closely with the accountant or the person who requires the export.
• Create a Manual Excel Template: Instead of programmatically generating the entire Excel sheet, manually create a template in Excel that includes all necessary calculations using pivot tables. The template should have one or more sheets dedicated to raw data.
• Programmatically Feed Raw Data into the Template: Use your database to populate the raw data sheets within the template. The pre-built formulas and pivot tables in the template will automatically handle all calculations and formatting.

If there’s ever an issue with the calculations, it’s easier to debug. Either it’s a problem with the raw data or an issue with the Excel template’s formula.

The primary downside of this approach is the presence of an extra sheet dedicated to raw data. However, I would choose this method 100% if I had to redo any complex Excel export in the future.

You can waste a lifetime editing HTML by hand
– Anonymous

I know and worked on two types of Excel reports (there could be more I dont know)

1. Data Tab
The excel document have (usually a hidden) tab, that have the data
and other tabs act as reports on this data, usually pivot tables, the data table may include calculated columns, but make sure you know the difference between measure and calculated column

2. External Data source
I add a connection to an external data source , and create sheets or pivot tables that use this source

In both types, you create the Excel report using Excel and you only automate updating the data source, whether external in a database or the data tab inside the excel document

So basically you are creating an ETL job, that is it

The need to dynamically create the report, suggest you are creating an excel report writer in Elixir, so this is a completely different thing

Reporting writer vs Report

Don’t create a report writer, when all you want is a report
(Editing HTML by hand is an example of solving the wrong problem)