How to add rows to Excel export or why are my Child Rows not exported?

kthorngren

I have a need to export Child Rows to Excel. I want them to be inserted below their parent row in Excel. Basically looking the same as the Child rows show in Datatables. Not sure this is the most efficient way but this solution fits my needs:
http://live.datatables.net/jeduxela/16/edit

I used the excelHtml5 customize function to insert the rows. Basically the function gets the sheetData which is all the rows that Datatables will export. It iterates through the rows inserting the additional child row(s) as it goes. The data to be inserted is part of the original Datatables row data.

This table:

Ends up as this spreadsheet:

Not much formatting, etc in the spreadsheet. More information about formatting can be found in the customize docs.

If anyone has a better way to do this please post an example or suggestions on how to improve.

Kevin

kthorngren

I found that my simple example didn't completely work with my app. Using :eq() to select the row works but only if the table is not filtered. Once filtered the wrong row is selected. Made changes to use meta.row in the last column which is hidden. The row ID is pulled from the cell data and that index column is not exported to the spreadsheet. Here is the updated example:
http://live.datatables.net/jeduxela/21/edit

Kevin

colin

Hi Kevin,

I think there's another problem with it. If you filter on "31" and then export - it's export the child for the first row, not the filtered row.

But it looks good though still!

Cheers,

Colin

kthorngren

Thanks for pointing that out. Its strange, not all the changes were saved. The meta.row column was there but the row selector code wasn't saved. I was having some connectivity issues with live.datatable.net at that time. Anyway here is the working code:
http://live.datatables.net/jeduxela/22/edit

Kevin

colin

Nice, I think this will be a good reference post on how to export child rows!

zmdavis

Hello,
I'm trying to get this to work by taking data from an SQL database (Heidi SQL). I've got the data showing OK in my table, and I see the Excel button, but I can't figure out the variables to get the Excel button to actually work properly to convert the column & child data into an excel doc. Right now, the rows won't expand because "d.results" is undefined.

But everything works great if I don't have any of this export code meshed in.

Here is the error I get:
Uncaught TypeError: table.row(...).data() is undefined

var childData = table.row(DT_row, {search: 'none', order: 'index'}).data().results;
That's where it is saying it's undefined.... I'm guessing 'results' is undefined, because results seems to be referring to the child data set.... but I'm not entirely sure. should I be creating a variable in server_processing.php that contains all of my data and calling it 'results'?

This is my index.php code:
http://live.datatables.net/fatixibo/1/edit

my data is referenced via server_processing.php template which taps into my SQL database... so unfortunately the table isn't populated since I'm testing this via Xampp/LocalHost

I also was looking at a similar post that used PDF export:
https://datatables.net/forums/discussion/61324/export-child-rows-using-pdfhtml5

And I also see that there is a sort of workaround using the Responsive extension, but I have 23 columns of data so I am trying to organize them cleanly as child rows so they are easily legible.

Any help is appreciated, thanks.

kthorngren

my data is referenced via server_processing.php template which taps into my SQL database... so unfortunately the table isn't populated since I'm testing this via Xampp/LocalHost

There was a syntax error causing the test case to not run. I fixed that here:
http://live.datatables.net/fatixibo/2/edit

You can simulate your data by using a subset of the JSON response you receive. Use the browser's network inspector tool. The JSON response is in the Response tab. You can copy it from there and create a variable in the test case that contains some of the response. Replace the ajax option with data. Like this example and at my example.

Right now, the rows won't expand because "d.results" is undefined.

This loop in my example:

    for (i=0; i<d.results.length; i++) {
        result = d.results[i];

Is specific to the structure of the example's data set. For example:

  var data = [
    {
      name: 'Test 1',
      date: '06/15/2018',
      description: 'This is test 1',
      results: [
        {
          result: 'passed',
          note: 'note 1'
        }
      ]
    },

Likely you don't have the same results array and don't need this. If you still need help then update http://live.datatables.net/fatixibo/2/edit with an example of your data.

Kevin

zmdavis

Hi Kevin,

Thanks for your response. I pulled data from the JSON response tab via Firefox, and created a variable. So now I have the data in the table working here:
http://live.datatables.net/fatixibo/8/edit
Unfortunately I'm still stuck on what to do next to get it to export to Excel. Sorry to ask, but do you think you can help me on this, please?

kthorngren

My example uses this hidden column to get the row index:

      {
        data: null,
        visible: false,
        render: function (data, type, row, meta) {
          return meta.row;
        }
      }

Its the last column in the row. You moved it to the first column. I changed the if statement looking for this column to this:

              //if (colCount === numColumns) {
              if (colCount === 0) {

Updated example:
http://live.datatables.net/fatixibo/9/edit

You will need to update this code to fit your needs:

              // Prepare Excel formated row
              childRow = '<row r="' + rowCount + 
                        '"><c t="inlineStr" r="A' + rowCount + 
                        '"><is><t>' + 
                        '</t></is></c><c t="inlineStr" r="B' + rowCount + 
                        '"><is><t>' + child.result + 
                        '</t></is></c><c t="inlineStr" r="C' + rowCount + 
                        '"><is><t>' + child.note + 
                        '</t></is></c></row>';

Kevin

zmdavis

This did indeed work. Now I'll just have to find out how to make it dynamic w/ ajax. Thank you for the assistance!

dirkomatic

I encountered an issue with the "id" row

{
  data: null,
  visible: false,
  render: function (data, type, row, meta) {
    return meta.row;
  }
}

when it was at the end and there were nulls in the data. Best to put it in the front.

Thanks for this! I had to modify it for my child rows, which were a

<

div> containing a

<

table> instead of standard JSON. It took a while but I got it working thanks to this post.

dirkomatic

Also, after spending most of 2 days on this, I have discovered 2 things $.each does not handle sparse data and this does not work with Internet Explorer as is. I finally have some code which works for my instance which I will post sometime next week.