Excel export with RowGroup

kthorngren · February 2022

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

kthorngren · February 2022

Funny already had to fix the code

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:

          for (const [key, value] of Object.entries(data)) {
            temp.splice(dt.col.index(key), 1, value)
          }

The same code works for array based data.

Kevin

colin · February 2022

Very nice, that works really well.

Colin

kthorngren · February 2022

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

Kevin

kthorngren · March 2022

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 of columns.data was being exported in the first column.

Fixed this loop in getTableData():

        // Insert object into array based on DT column index
        for (const [key, value] of Object.entries(data)) {
          var rowIdx = dt.col.index(key);
          if (rowIdx !== null) {
            temp.splice(rowIdx, 1, value)
          }
        }

I updated the relevant examples above with this fix.

Kevin

Excel export with RowGroup

Excel export with RowGroup

Replies

Howdy, Stranger!

Categories

DataTables

Excel export with RowGroup

Excel export with RowGroup

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables