Multiple header thead rows in excel export?
Multiple header thead rows in excel export?
jackawardls
Posts: 1Questions: 1Answers: 0
This is a problem that I have seen spanning all the way back from 2012 online.
I have a table with 1 <thead></thead> tag at the top containing multiple header rows. When I export the spreadsheet, it only exports the last row in the table header. Is there any code I can inject into the source code to get this feature working?
I have seen previous attempts at modifying the course code from various other threads on this site and Stack Overflow and none of them seem to work for me. I found the threads quite vague in their implementation of their code fixes, and I haven't been able to get it to work on my end. And, when I paste the code in I am not even sure what I am changing.
Any ideas on what I can do to get all header rows included in the Excel export?
Answers
Yep, as you say there's several threads on this - see here for example. It's not officially supported still, but some community members have suggested solutions. I can only suggest trying those until support is added.
Colin
Since this is a top search result for this issue, I wanted to share my work-around.
The database export functions will take from last header row, as you've said.
It will do this even if the last row is hidden!
So an extremely simple solution to this issue is to add one more row to the header, give it the column names you want exported, and then hide that row.
As in:
It still only exports a single header row, but at least this way you can control what those headers say, while still keeping your browser table nice and tidy.
Edit: Just realized that sorting is also applied to the last column, so if it's hidden you won't be able to sort. Adding "bSortCellsTop: true" to the config should fix that.
@arlenreyb. Thank you! This approach works well for me, including adding "bSortCellsTop: true".
The one issue I found is that if you have "exportOptions: { columns: [ ':visible' ] }". Then, nothing will export, since the bottom export row is hidden.
Not sure if there is a way to change which row is used to determine the visible columns in this export option.
@chbocca - I'm not following, sorry, as the columns exported shouldn't be affected by a row. We're happy to take a look, but please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.
Cheers,
Colin
Thank you Colin.
Yes, demos always reveal the error of my ways!
Here is demo of issue I tried to highlight. Basically, if you hide the bottom export-friendly header, adding "exportOptions: { columns: [ ':visible' ] }" will result in an empty export table, presumably because dt uses this bottom header row to determine column visibility. Try clicking "Export (Invisible)" vs "Export (Visible)". Unfortunately, I've not yet added "bSortCellsTop: true", so there is no column sorting.
When I add "bSortCellsTop: true" to this demo, column sort now works, as does export with or without "exportOptions: { columns: [ ':visible' ] }". Unfortunately, the top header column now gets exported.
So, I think we are back to square one on this issue of exporting tables with complex headers.
Yes, the export will always use the header cell that DataTables picks for the sorting trigger. We haven't had a chance yet to work on the complex header export - an API to support this kind of thing is going to be one of the corner stones of DataTables 2.
Allan
Bummer. Will look forward to DataTables 2. Thank you!
please help me how to export mutiple header in excel format
@Trinav - please see comments above, that currently isn't possible.
Colin
@colin Is there a solution on this issue now?
Not yet, but I'm working on DataTables 2 which will present an API that Buttons can use for multi-row header export. So it is getting closer.
Allan