DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

Excel export with RowGroup

Excel export with RowGroup

kthorngrenkthorngren Posts: 20,277Questions: 26Answers: 4,766
edited February 2022 in DataTables 1.10

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

  • kthorngrenkthorngren Posts: 20,277Questions: 26Answers: 4,766
    edited March 2022

    Funny already had to fix the code :smile:

    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

  • colincolin Posts: 15,142Questions: 1Answers: 2,586

    Very nice, that works really well.

    Colin

  • kthorngrenkthorngren Posts: 20,277Questions: 26Answers: 4,766
    edited March 2023

    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:

    Kevin

  • kthorngrenkthorngren Posts: 20,277Questions: 26Answers: 4,766
    edited March 2023

    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

Sign In or Register to comment.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.