Excel export with RowGroup
Excel export with RowGroup
![kthorngren](https://secure.gravatar.com/avatar/f771e206ad0af801714b8375bc50eeb7/?default=https%3A%2F%2Fvanillicon.com%2Ff771e206ad0af801714b8375bc50eeb7_200.png&rating=g&size=120)
I found this question asking about exporting tables using the RowGroup extension with each group on a different sheet interesting. The question asked about using the Add a new sheet example. This example was created from code that F12Magic posted with a couple bugs fixed by others on the forum. Combining the final fixed example along with some code from this thread to customize the Sheet1 output resulted in this example:
http://live.datatables.net/hemelesi/1/edit
The rowGroup.dataSrc
can be configured to support one or more levels of grouping. This example supports exporting only the first group. The multiSheet
boolean variable controls whether the export is on one sheet or individual sheets for each group.
The example supports these standard button options:
title: '',
header: true,
messageTop: 'This is the top',
messageBottom: 'This is the bottom',
sheetName: 'Single Sheet', // This is only used in singe sheet mode
footer: true,
exportOptions: {
modifier: {
search: 'applied',
}
},
Use any desired selector-modifier
. The above is just an example.
The only setting needed inside the customize function is this varaible:
multiSheet = true; // Export all groups in one sheet or multiple
Please make improvements to the code and post them here if you do.
Kevin
Replies
Funny already had to fix the code![:smile: :smile:](https://datatables.net/forums/resources/emoji/smile.png)
The original didn't work with objects. Created an API plugin to map the objects to the Datatables column index. Here is an example of the plugin:
http://live.datatables.net/vohahake/1/edit
This fixes a couple issues. The plugin is used to build the array of object values in the proper order - Datatables column index order.
Updated example using objects:
http://live.datatables.net/voquqoso/1/edit
The example shows 28 syntax errors. Its due to this code - js bin isn't parsing correctly:
The same code works for array based data.
Kevin
Very nice, that works really well.
Colin
Updated the example, for this thread, to allow for generic row processing before exporting.
https://live.datatables.net/rawafezi/8/edit
The row data is passed into
customizeRowExport()
to allow for manipulating the row data before exporting. The developer can write any customization code in this function before the rows are processed into the worksheet.Just posting it here so I can find the example later![:smile: :smile:](https://datatables.net/forums/resources/emoji/smile.png)
Kevin
Made another update. This allows for exporting multiple tables on a page. It fixes previous examples that only supported array based data.
https://live.datatables.net/femerimi/19/edit
It is an update of the previous example with
customizeRowExport()
. Also fixed a bug where object data that isn't part ofcolumns.data
was being exported in the first column.Fixed this loop in
getTableData()
:I updated the relevant examples above with this fix.
Kevin
Hi Kevin,
Is there any way to export only visible columns?
Eugen
You can use the
exportOptions
for that - seepdfHtml5
. This example here is demonstrating this,Colin
@efintina
I don't believe this support was added to my example. You are certainly welcome to add it. The
getTableData()
function uses thebutton
parameter to see ifexportOptions.modifier
is supplied:I would look at updating the
buildRow()
function to support this by adding the parameterbutton
and checking forexportOptions.columns
. Before the for loop I would build a list of indexes, based on theexportOptions.columns
, that are not to be exported. Could be a bit complicated depending on the options you want to support. Datatables supports thecolumn-selector
options.In the for loop if the loop index is in the resulting
exportOptions.columns
indexes then don't process the cell.Kevin
@efintina
You can also create your own parameter, like I did with
sheetName
, to avoid any confusion with the expected options thatexportOptions.columns
supports.Kevin
Well...I'll try! Because my js knowledge is quite poor, I think it won't be easy at all. But at least I will definitely try. And maybe I will succeed.
Thank you very much for your suggestions!
Eugen
To my surprise, it wasn't too difficult to display columns that had exportOptions set to ":visible". Actually to hide others. I don't know if what I did is correct, so please correct me if necessary.
My problem now is that instead of the columns that were not selected in 'colvis', there are empty columns in the .xlsx file. So where should I look to remove empty columns? I kept trying, but without any success.
Instead of:
I replaced with:
It's ok?
It seems reasonable to update buildCols() to remove the hidden columns. Without seeing the issue its hard to imagine what you mean by
there are empty columns in the .xlsx file
. This could mean a lot of things. Can you update my last example to show the issue you are having?Kevin
Sorry for my delay.
Starting from: https://live.datatables.net/voquqoso/101/edit
I update to: https://live.datatables.net/mucewuca/4/edit
I made some modification:
1. Update version for Datatables and so on
2. Update function getHeaderNames(dt)
3. Update function function buildRow(data, rowNum, styleNum)
4. Update var table = $('#example').DataTable to
* remove dom definition
* add layout
* add colvis
Please read:
3. Update function buildCols(data)
Instead of:
3. Update function function buildRow(data, rowNum, styleNum)
Eugen
I added console.log statements in both buildCols and buildRow:
https://live.datatables.net/mucewuca/6/edit
Hide the Position column.
This is the column generation:
I think you need to put your code back into buildCols to check for invisible columns so only five columns are generated.
Here is the row generation:
Note that it is skipping placing placing the row data in
B3
. This is do to this in the for loop:When Position is hidden the value of
i
for(i + 10).
goes from 0 to 2, skipping 1. Maybe use a separate variable to handle this. Updated example:https://live.datatables.net/mucewuca/7/edit
Good job in putting this together.
Kevin
I'm always impressed to find that there are still people out there who simply want to help. It means there is still hope.
Thank you so much, Kevin! You're a good man.
All the best,
Eugen
Thanks, glad to be of help. Hope you learned some JS along the way.
Kevin