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

efintina · July 15

Hi Kevin,

Is there any way to export only visible columns?

Eugen

colin · July 15

You can use the exportOptions for that - see pdfHtml5. This example here is demonstrating this,

Colin

kthorngren · July 15

@efintina

Is there any way to export only visible columns?

I don't believe this support was added to my example. You are certainly welcome to add it. The getTableData() function uses the button parameter to see if exportOptions.modifier is supplied:

    var selectorModifier = {};
    
    if (button.exportOptions.hasOwnProperty('modifier') ) {
      selectorModifier = button.exportOptions.modifier;
    }

I would look at updating the buildRow() function to support this by adding the parameter button and checking for exportOptions.columns. Before the for loop I would build a list of indexes, based on the exportOptions.columns, that are not to be exported. Could be a bit complicated depending on the options you want to support. Datatables supports the column-selector options.

In the for loop if the loop index is in the resulting exportOptions.columns indexes then don't process the cell.

Kevin

kthorngren · July 15

@efintina

You can also create your own parameter, like I did with sheetName, to avoid any confusion with the expected options that exportOptions.columns supports.

Kevin

efintina · July 15

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

efintina · July 16

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:

  function buildCols(data) {
    // Builds cols XML.
    //To do: deifne widths for each column.
    //Params:
    //  data: row data.
    //Returns:
    //  String of XML formatted column widths.

    var cols = '<cols>';

    for (i=0; i<data.length; i++) {
      colNum = i + 1;
      cols += '<col min="' + colNum + '" max="' + colNum + '" width="20" customWidth="1"/>';
    }

    cols += '</cols>';

    return cols;
  }

I replaced with:

                    function buildCols(data, button) {
                        // Builds cols XML.
                        //To do: deifne widths for each column.
                        //Params:
                        //  data: row data.
                        //Returns:
                        //  String of XML formatted column widths.

                        var cols = '<cols>';

                        for (i = 0; i < data.length; i++) {
                            var isVisible = table.column(i).visible();
                            if ( isVisible ) {
                                colNum = i + 1;
                                cols += '<col min="' + colNum + '" max="' + colNum + '" width="20" customWidth="1"/>';
                            }
                        }
                        cols += '</cols>';
                        return cols;
                    }

It's ok?

kthorngren · July 16

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

efintina · July 18

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

efintina · July 18

Please read:
3. Update function buildCols(data)
Instead of:
3. Update function function buildRow(data, rowNum, styleNum)

Eugen

kthorngren · July 18

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:

<cols>
   <col min="1" max="1" width="20" customWidth="1"/>
   <col min="2" max="2" width="20" customWidth="1"/>
   <col min="3" max="3" width="20" customWidth="1"/>
   <col min="4" max="4" width="20" customWidth="1"/>
   <col min="5" max="5" width="20" customWidth="1"/>
   <col min="6" max="6" width="20" customWidth="1"/>
</cols>

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:

<row r="3">
   <c t="inlineStr" r="A3" s="2">
      <is>
         <t>Name</t>
      </is>
   </c>
   <c t="inlineStr" r="C3" s="2">
      <is>
         <t>Office</t>
      </is>
   </c>
   <c t="inlineStr" r="D3" s="2">
      <is>
         <t>Age</t>
      </is>
   </c>
   <c t="inlineStr" r="E3" s="2">
      <is>
         <t>Start date</t>
      </is>
   </c>
   <c t="inlineStr" r="F3" s="2">
      <is>
         <t>Salary</t>
      </is>
   </c>
</row>

Note that it is skipping placing placing the row data in B3. This is do to this in the for loop:

      if ( isVisible ) {
        colNum = (i + 10).toString(36).toUpperCase();  // Convert to alpha

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

efintina · July 18

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

kthorngren · July 18

Thanks, glad to be of help. Hope you learned some JS along the way.

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