I think Excel is still important in so many ways. Over 50% of my users want to be able to easily export their data to Excel. And they want it in a way that they can work with the data.
If you are not living in America or the UK you know the issue: It is hardly ever possible to work with exported numeric data. Formatted European numeric data get mutilated by Excel in a terrible way. I used the features that Data Tables provides to get it done - somehow.
But then the next problem emerged: How to do it,
a) if the user wants only selected columns (selected with the colvis button), or
b) the user wants wysiwyg: only what is displayed on the screen (not including child-rows hidden by the "responsive" extension)
- and still get the export formatting done in order to prevent Excel from destroying part of the data.
This was hard to develop and my code for this is ugly, I think.
I would strongly encourage a better solution for this. If anyone is interested how I resolved this with my ugly solution I can share it.
Presumably the ugly bit is in a
customizecallback to determine which columns need to get the formatting applied to?
This is where Buttons does its Excel special data type detection and formatting. At the moment there is no concept in there of comma decimal and thousands period. Is that basically the issue?
No, it is more than that, Allan.
I assign classes to the column headers in my HTML to indicate whether or not they require special formatting. If the user has flexibility to choose columns she wants to export, this requires that I know their position in the exported columns array.
First I had to learn that
- all columns are passed into "exportOptions.columns",
- only the columns to be exported are passed into "exportOptions.format.body"
- in "customize" you have to deal with the columns to be exported only but you need to address them by Excel column letter, not by number.
In order to still know where my columns are that I need to format I had to use a couple of global variables.
Here they are:
Then I needed to capture the initial position of my columns that require special formatting relative to all columns of the data table:
I also needed to capture which columns are shown in the main view of the table excluding the columns hidden by the responsive extension. I didn't find anything in the api to check the "responsive status" by column hence I took the array of the event handler.
To capture which columns are eventually exported and where my special columns are I build a function that is called from "exportOptions.columns"
Based on all of this here are my three buttons:
To make the Excel column letter matching as simple as possible I used another variable. I know there other ways to achieve this but I found this one the simplest and most intuitive one:
What makes this so difficult is that you can't use the same column number throughout the process. Just knowing that column 10 needs special formatting doesn't work if the user can select the columns to be exported.
Column 10 is column 10 in the first step, but it can be column 5 and column letter E in the following steps. Or, it might not be part of the exported columns at all.
Oof! That's a lot of code to do what you need, although I do see why. If you want to pass through export configuration options, there just is no way to do that at the moment. Perhaps if we passed in an array of the column indexes (or an Api instance of the columns) that are used in the export into
customizethat would help?
Not much actually. What would really help if you could address a column by its id or class regardless of its current position after filtering the columns for export. And also by numeric position based on all table columns. That would require Data Tables to do the repositioning based on user selection internally.
That would mean that column 10 for the developer is always column 10 - even if it is column 5 in column letter E after filtering.
I use the code on "init" plus this code to do the trick, but that could be built in, I guess:
(From the function above)
Thanks Allan, I had overlooked that one!
Yup, I see what you mean. The
customizecallback was meant to be for final tweaks to the document that is produced - i.e. everything is where it will be in the output (so no sparse arrays).
I need to have a bit of a think about this!
Many people still want everything in Excel especially to share and fiddle around with large tables. And I am trying to avoid the overhead of doing this all on the server using PHPSpreadsheet etc.. I think any effort is well invested in this. I'd be willing to pay a license fee for an "Excel premium package", Allan.