Export all columns in data, regardless of what's visible in the table

errorous

Using datatables jquery plugin, I've table that's been populated( created, actually ), upon success on ajax call. Here's sample code, where d is js variable with data:

if ( ! $.fn.DataTable.isDataTable( '#DataTables_Table_0' ) ) {
    $('#DataTables_Table_0').DataTable ({
        "data" : d,
        "dom": "Blfrtip",
        "searching": false,
        "order": [],
        "paging": false,
        "columns" : [
            { "data" : "country" },
            { "data" : "round" },
            { "data" : "sector" },
            { "data" : "size" },
        ],
        "columnDefs": [{
            render: $.fn.dataTable.render.number(',', '.', 2)
        }],
        "buttons": [
            {
              extend: 'excelHtml5',
              text: 'Excel',
              customize: function( xlsx ) {
                setSheetName(xlsx, 'Data');
                addSheet(xlsx, '#DataTables_Table_1', 'Meta Data', 'Meta', '2');
              }

            },
            "csv"
        ]
    });
} else {
    $('#DataTables_Table_0').dataTable().fnClearTable();
    $('#DataTables_Table_0').dataTable().fnDestroy();

    let tu = last_column_name;

    $('#DataTables_Table_0').DataTable ({
        "destroy": true,
        "searching": false,
        "order": [],
        "paging": false,
        "data" : current_table_data,
        "dom": "Bfrtip",
        "columns" : [
            { "data" : "country" },
            { "data" : "round" },
            { "data" : "sector" },
            { "data" : tu},
        ],
        "buttons": [
            {
              extend: 'excelHtml5',
              text: 'Excel',
              customize: function( xlsx ) {
                setSheetName(xlsx, 'Data');
                addSheet(xlsx, '#DataTables_Table_1', 'Meta Data', 'Meta', '2');
              }

            },
            "csv"
        ]
    });

Aside from this, I'm also using buttons, to export my data to Excel and CSV. Everything works properly, I think, the only issue is that it only exports columns that are visible in the table, or actually defined.

What I want to do is to actually export all the columns that I passed along with data object, and not only those displayed in the table. Also, this was working until some time ago, until I changed the code. The problem is that I've changed much, and didn't really test export all that much, and when I did, didn't really think about columns in the exported doc.

If it changes anything, here's a tad more code, functions used in customize:

function getHeaderNames(table) {
      // Gets header names.
      //params:
      //  table: table ID.
      //Returns:
      //  Array of column header names.

      var header = $(table).DataTable().columns().header().toArray();

      var names = [];
      header.forEach(function(th) {
       names.push($(th).html());
      });

      return names;
    }

    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 ( var i=0; i<data.length; i++) {
        var colNum = i + 1;
        cols += '<col min="' + colNum + '" max="' + colNum + '" width="20" customWidth="1"/>';
      }

      cols += '</cols>';

      return cols;
    }

    function buildRow(data, rowNum, styleNum) {
      // Builds row XML.
      //Params:
      //  data: Row data.
      //  rowNum: Excel row number.
      //  styleNum: style number or empty string for no style.
      //Returns:
      //  String of XML formatted row.

      var style = styleNum ? ' s="' + styleNum + '"' : '';

      var row = '<row r="' + rowNum + '">';
        if( rowNum > 2 ) {
            data.shift();
        }


      for (var i=0; i<data.length; i++) {
        var colNum = (i + 10).toString(36).toUpperCase();  // Convert to alpha

        var cr = colNum + rowNum;

        row += '<c t="inlineStr" r="' + cr + '"' + style + '>' +
                '<is>' +
                  '<t>' + data[i] + '</t>' +
                '</is>' +
              '</c>';
      }

      row += '</row>';

      return row;
    }

    function getTableData(table, title) {
      // Processes Datatable row data to build sheet.
      //Params:
      //  table: table ID.
      //  title: Title displayed at top of SS or empty str for no title.
      //Returns:
      //  String of XML formatted worksheet.

      var header = getHeaderNames(table);
      var table = $(table).DataTable();
      var rowNum = 1;
      var mergeCells = '';
      var ws = '';

      ws += buildCols(header);
      ws += '<sheetData>';

      if (title.length > 0) {
        ws += buildRow([title], rowNum, 51);
        rowNum++;

        var mergeCol = ((header.length - 1) + 10).toString(36).toUpperCase();

        mergeCells = '<mergeCells count="1">'+
          '<mergeCell ref="A1:' + mergeCol + '1"/>' +
                     '</mergeCells>';
      }

      ws += buildRow(header, rowNum, 2);
      rowNum++;

      // Loop through each row to append to sheet.    
      table.rows().every( function ( rowIdx, tableLoop, rowLoop ) {
        let secondtabledata = $('#DataTables_Table_1').DataTable();
        var data = secondtabledata.data();

        var tmp_arr = [];

        $.each( data, function(k, v) {
            tmp_arr.push( Object.values(v) );
        });

        // If data is object based then it needs to be converted 
        // to an array before sending to buildRow()
        ws += buildRow(tmp_arr[rowNum-3], rowNum, '');

        rowNum++;
      } );

      ws += '</sheetData>' + mergeCells;

      return ws;

    }

    function setSheetName(xlsx, name) {
      // Changes tab title for sheet.
      //Params:
      //  xlsx: xlxs worksheet object.
      //  name: name for sheet.

      if (name.length > 0) {
        var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];
        source.setAttribute('name', name);
      }
    }

    function addSheet(xlsx, table, title, name, sheetId) {
      //Clones sheet from Sheet1 to build new sheet.
      //Params:
      //  xlsx: xlsx object.
      //  table: table ID.
      //  title: Title for top row or blank if no title.
      //  name: Name of new sheet.
      //  sheetId: string containing sheetId for new sheet.
      //Returns:
      //  Updated sheet object.

      //Add sheet2 to [Content_Types].xml => <Types>
      //============================================
      var source = xlsx['[Content_Types].xml'].getElementsByTagName('Override')[1];
      var clone = source.cloneNode(true);
      clone.setAttribute('PartName','/xl/worksheets/sheet2.xml');
      xlsx['[Content_Types].xml'].getElementsByTagName('Types')[0].appendChild(clone);

      //Add sheet relationship to xl/_rels/workbook.xml.rels => Relationships
      //=====================================================================
      var source = xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationship')[0];
      var clone = source.cloneNode(true);
      clone.setAttribute('Id','rId3');
      clone.setAttribute('Target','worksheets/sheet2.xml');
      xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationships')[0].appendChild(clone);

      //Add second sheet to xl/workbook.xml => <workbook><sheets>
      //=========================================================
      var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];
      var clone = source.cloneNode(true);
      clone.setAttribute('name', name);
      clone.setAttribute('sheetId', sheetId);
      clone.setAttribute('r:id','rId3');
      xlsx.xl['workbook.xml'].getElementsByTagName('sheets')[0].appendChild(clone);

      //Add sheet2.xml to xl/worksheets
      //===============================
      var newSheet = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'+
        '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">'+
        getTableData(table, title) +

        '</worksheet>';

      xlsx.xl.worksheets['sheet2.xml'] = $.parseXML(newSheet);

    }

kthorngren

Its not clear to me exactly what the problem is. Let me try to clarify.

1. You have a table #DataTables_Table_0 with 4 columns, are all of thsee columns exported properly?
2. You are using code to add a second sheet for the table #DataTables_Table_1, are all these columns exported properly?

Please describe in more detail the exact problem.

That export code is a lot to look through if the problem is with exporting the second sheet. Please post a link to your page or a test case replicating the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

kthorngren

The original code for creating the second sheet is in this thread. Maybe you can try it to see if it works.

Kevin

errorous

@kthorngren I really don't know how to explain it simpler. I pass data to datatables; I also define columns in datatables; data object has more columns than what's displayed in the table; I want all the data from data object to be in the export...

kthorngren

I also define columns in datatables; data object has more columns than what's displayed in the table; I want all the data from data object to be in the export...

So this has nothing to do with the second worksheet you are creating with addSheet(xlsx, '#DataTables_Table_1', 'Meta Data', 'Meta', '2')?

If the extra data is in the DataTables_Table_0 table then the easiest way is to add the extra columns using columns.data then use columns.visible to hide those columns. If you don't want to do that then it would take customization of the Excel export function to grab the extra data and populate the sheet.

Kevin