separate excel sheet for each row grouping

montoyam

going off of this example to add sheets to an export: http://live.datatables.net/qinoxinu/6/edit

I am grouping my table and I would like to create a new sheet for each grouping.

    var UsersByModuleTable = $('#UsersByModule').DataTable({ //http://live.datatables.net/qinoxinu/6/edit
        dom: 'Bfrtip',
        ajax: 'api/UsersByModule',
        rowGroup: {
            dataSrc: 'Module'
        },
        scrollY: '50vh',
        scrollCollapse: true,
        paging: false,
        order: [[0, "asc"]],
        columns: [
            { data: "Module", title: "Module", visible: false },
            { data: "UserName", title: "User Name" },
            { data: "EMAIL_ADDRESS", title: "Email Address" },
            { data: "DEPARTMENT_DESCRIPTION", title: "Department" }
        ],
        select: { style: 'single' },
        buttons: {
            buttons: [
                {
                    extend: 'excelHtml5',
                    text: '<span class="fa fa-file-excel-o fa-2x icon-purple"></span>', className: 'btn', titleAttr: 'Export to Excel',
                    customize: function (xlsx) {
                        //first group
                            setSheetName(xlsx, groupName);
                        //then for each group after that
                            addSheet(xlsx, '#example2', 'My Sheet2', groupName, '2');
                    }
                }
            ],
            dom: {
                button: { tag: 'i', className: '' }
            }
        }
    });

my question is how do I loop through the groups in the block of code that starts at line 24?

kthorngren

I don't believe there is a stored data structure for the RowGourp groups. You will need to first get an array of unique values from the Module column which are the groups. Then call the AddSheet function with each unique group name instead of the table id.

AddSheet calls getTableData(table, title). Instead of passing the table pass the group name.

See this example showing how to get row data broken up into the groups:
http://live.datatables.net/bugulaqu/1/edit

The forEach loop is used in the customize function to call addSheet with each group name. The rows().every() in the getTableData function will need the addition row-selector as a function to only process the rows matching the group name.

You will need to combine the example you linked with this example from this [thread]https://datatables.net/forums/discussion/58960/how-to-add-rows-to-excel-export-or-why-are-my-child-rows-not-exported#latest). The reason is you will need to control the first sheet data to limit it to one of the groups.

Also there may be a bug with the example you linked when adding more that the second sheet. It had something to do with the IDs and there is a bit of hard coding for the sheet name in the addSheet function. There are some threads about this on the forum.

If you want to build an example test case we can help with any issues. Hope this makes sense. Ask if not

Kevin

kthorngren

Look at the last example in this thread for some of the fixes to the example you linked.

Kevin

montoyam

thanks for that link on the updated 'addSheets' code. I knew I needed to tweak the example they had so I could pass groups instead of tables. It looks like they made their 'addSheets' more flexible and that will help me out in my requirements.

montoyam

let me see how far I get with all the links you sent. I may be back for more assistance

kthorngren

It was actually easier than I thought. Checkout this example:
http://live.datatables.net/jeduxela/364/edit

Kevin

kthorngren

I updated the getTableData function to use the rowGroup().dataSrc() API so that it can be used in the rows().every() row selector function:

return data[ dataSrc ] === groupName ?

http://live.datatables.net/lumaragi/1/edit

Note this solution only supports one level of grouping.

EDIT: Added the rowGroup.dataSrc to this statement:

var groupNames = [... new Set( dt.column( dataSrc ).data().toArray() )];

Kevin

montoyam

I can not say enough how awesome you are!!! I started working on modifying the code from the initial examples/threads you provided. But when I was no where near finished I came back to this post and saw you had already solved it! You are crazy quick!!

I can see where others could benefit from this functionality you created. You should create a blog entry for it!!

kthorngren

Thanks. And this example can be adapted to insert the rowGroup rows in the one sheet. This question is asked a lot too.

Kevin

colin

I think that works well, thanks. I'll add it as a blog post or an example, so easy for people to find.

Colin

kthorngren

@colin I have a couple improvements that I would like to make first.

Kevin

colin

OK, tops - I'll fully credit you when I knock out the example/blog post. I'll leave it for you to post back with your new&improved!

Colin

kthorngren

@colin I posted a new thread with the updated example. Feel free to create a blog or whatever you like with it. It certainly was interesting to make the changes and like that the button and dt objects are now available to the customize function. Feel free to make any changes you see fit to the code. The original code was created years ago and could stand some improvements

Kevin

montoyam

In my case, my data is object based. I always have trouble with javascript syntax when it comes to arrays and objects. How do I change this:

var dataSrc = getDataSrc( dt );
// Get unique values from rowGroup.dataSrc column
var groupNames = [... new Set( dt.column( dataSrc ).data().toArray() )];

to work with dataSrc being the name of the field not the index number?

montoyam

after many google searches I put this together:

                        var dataSrc = dt.rowGroup().dataSrc();
                        var propNames = Object.getOwnPropertyNames(dt.data()[0]);
                        var propIndex = propNames.indexOf(dataSrc);
                        var groupNames = [... new Set(dt.column(propIndex).data().toArray())];

but for some reason, the property names are in a different order than the data in the object...very odd.

kthorngren

but for some reason, the property names are in a different order than the data in the object...very odd.

Well, not sure its always guaranteed to be in order. I created an API Plugin to get the column index of the value passed in. Here is the plugin:
http://live.datatables.net/vohahake/1/edit

Then I noticed that the object based data causes empty rows as the function that builds the rows expects an array. Simply converting the array doesn't guarantee the order - we both learned something new I use the API I made to get the order fo all the columns and build the array from that. Here is the updated export code with objects:
http://live.datatables.net/rutatuka/1/edit

Kevin

montoyam

awesome. are you going to update the other thread you created: https://datatables.net/forums/discussion/71503/excel-export-with-rowgroup#latest

kthorngren

Yes, its updated.

Kevin

montoyam

for some reason with your latest code I had to change:

var groupNames = [... new Set(dt.column(dt.col.index(dataSrc)).data().toArray())];

to

var groupNames = [... new Set(dt.column().data().toArray())];

you used d.col.index(dataSrc) a couple times and I had to make that change each time. I am still getting an error where it is not picking up the other columns so still working through your code.

kthorngren

Are you using the API plugin I created (col.index())?
http://live.datatables.net/vohahake/1/edit

You might need to debug it. The goal of that statement is to programmatically get the rowGroup.dataSrc column. You can debug the code I created or simply not use like you have done.

I am still getting an error where it is not picking up the other columns so still working through your code.

What is the error?

Kevin

kthorngren

Thinking about it - if the plugin I created isn't working for you then building the rows on each page won't work. So we need to figure out why the plugin doesn't work. It could be a bug. Try this in initComplete of the code you first posted:

var api = this.api();
console.log( api.col.index("Module") );
console.log( api.col.index("UserName") );
console.log( api.col.index("EMAIL_ADDRESS") );
console.log( api.col.index("DEPARTMENT_DESCRIPTION") );

The results should be:

0
1
2
3

Let me know what you get.

Kevin

montoyam

No, sorry, I did not have the plugin at the top of the code before the dataTables were initialized. All is good now.