separate excel sheet for each row grouping

separate excel sheet for each row grouping

montoyammontoyam Posts: 568Questions: 136Answers: 5
edited February 2022 in DataTables 1.10

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?

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734

    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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734

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

    Kevin

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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.

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734

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

    Kevin

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734
    edited February 2022 Answer ✓

    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

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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!!

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734

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

    Kevin

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

    Colin

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734

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

    Kevin

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734

    @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 :smile:

    Kevin

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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?

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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.

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734

    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 :smile: 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

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734

    Yes, its updated.

    Kevin

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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.

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734
    edited February 2022

    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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734

    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

  • montoyammontoyam Posts: 568Questions: 136Answers: 5
    edited February 2022

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

Sign In or Register to comment.