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.
I think for that level of functionality you need a port to Python’s XlsxWriter or some other fullly featured lib in another language, or you drop down to XML.
Not sure if there is a lib in elixir that can already do something similar.
Here is an exemple with python and openpyxl write data in a specific excel sheet · GitHub
You write on the specific sheet your other sheet can reference it ex: =SUM('raw data'!B:B)
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)
Sure, it write over an existing sheet called "raw data".
@shishini, thanks for the interesting tips! I didn’t know you could hide tab .
The External Data Source approach is also interesting—I wasn’t aware of it either. I read a bit about it it seems you can pull data from a JSON Api that you could serve with Phoenix.
.
