Call `createdCell` on Export?

lisarush

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

Is there a way to initiate a call to createdCell callback when a table is exported?
We add cell highlighting (think status: red/yellow/green) that is useful for the export. (A user can select to "Show all rows" first before the export to effect this, but that is not intuitive.)

kthorngren

Use the customize option of the export button to customize the cell styling. Assuming you are referring to Excel export see this excelHTML5 doc. Here are a couple examples:
http://live.datatables.net/jijumeji/1/edit
http://live.datatables.net/golezopo/1/edit

Kevin

lisarush

OK, we use the customize method currently for Print (but it's just simple to add header/footer).

In our case, we have already expanded on the Excel functionality to convert any classes, e.g. red_highlight to the appropriate Excel style. (We've had this in place a long time.)

However, I could use some help figuring out how to call createdCell on the entire table. Any help would be greatly appreciated! Essentially something like:

customize: function( xlsx, button, api ) {
   api.columns().forEach( col => {
      if ( col.fnCreatedCell ) {
         // iterate through all the rows & add styling to this column
         api.rows().forEach( row => {
            let cell = api.cell( row, col );
            cell.call( ....????....);
         });
      }
   });
}

We are using deferred rendering in this case, so a portion of our config:

$('#table').DataTable( {
   deferRender: true,
   columns: [ 
      { data: "dataAccessor", type: "num", render: _renderDataCell, createdCell: _highlightDataCell } 
   ]
});

kthorngren

However, I could use some help figuring out how to call createdCell on the entire table.

You can't call createdCell from the export customize function. I'm not sure what you mean by entire table. Are you using server side processing and want to export all the rows? If this is the case see this FAQ.

Kevin

lisarush

No server side, everything is done on the client.

We have a large table with 1000 rows. We use deferred rendering to speed things up and only initially show 25 rows on the screen, with pagination to view the other rows. Our customers want to download the whole table to Excel. But if choosing to download Excel when the table is first drawn, it will download, of course, all the "data" but only highlighting on the first 25 rows. We would like to have the highlighting added to all 1000 rows of the table before the Excel file is generated. Attached is an example screenshot, where you an see the red-highlighted columns (values we flag as outside our thresholds).

kthorngren

Oh, I see. You will need to do something like the second example I linked:
http://live.datatables.net/golezopo/1/edit

Essentially you will need to recreate your columns.createdCell code to compare the data as appropriate.

Kevin

kthorngren

Depending on what _highlightDataCell is doing you might be able to break the logic out in a function and have it return the appropriate values to the calling function. This way you can call it from _highlightDataCell for columns.createdCell and from the customize function to use the same logic.

Kevin

lisarush

I'm sorry; I missing a connection here... We have our own extension to DataTables that we use -- so that we can centralize the styling and functionality of all tables the same across our site. The customize function would be in this extension, so it does not have knowledge about a specific table (to be able to check e.g. === London). The specific page's table has the createdCell function, so it's in another place.

Example createdCell method (it adds a highlight CSS class):

   function _highlightDataCell( cell, cellData ) {
      if ( cellData !== null && cellData.alarm === 'RED' || cellData.alarm === 'YELLOW') {
         $(cell).addClass( cellData.alarm.toLowerCase() + '_cell_highlight' );
      }
   }

I had thought about passing the createdCell (highlightDataCell) method as another option to our extension & be able to call this inside customize. However, I'm realizing that customize is called after the Excel is generated...

Is there a different callback or way we could hook in? that's called before the Excel is generated?
Or... a way to hook into the "button press" for "export to Excel" and have it add the styles there/earlier...?

allan

No - if you want to modify the data exported to the Excel file, you need to use orthogonal data or an export formatting function.

Allan

kthorngren

However, I'm realizing that customize is called after the Excel is generated...

I don't think that is the case. It is called to allow for customizing the row/cell data.

I was trying to workout an example to show you but Allan is fixing some networking issues with live.datatables.net. I meant something like this:

  function processCell(cell, cellData) {
    if (cellData === 'London') {
      return 'red';
    }
    
    return null;
  }
  
  var table = $('#example').DataTable({
      dom: 'Bfrtip',
      "columns": [
        { "data": "name" },
        { "data": "position" },
        { "data": "office",
         createdCell: function ( cell, cellData ) {
           var highlight = processCell( cell, cellData );
           if ( highlight ) {
             $(cell).addClass( highlight + '_cell_highlight' );
           }
         }
        },
        { "data": "extn" },
        { "data": "start_date" },
        { "data": "salary" }
      ],
    buttons: [{
      extend: 'excelHtml5',
      customize: function ( xlsx ) {
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
         

        $('row c[r^="C"]', sheet).each(function() {
          var cell = $(this);
          var cellData = $(this).text();
                    
          var highlight = processCell(cell, cellData);
          if ( highlight === 'red' ) {
            $(this).attr('s', '39');
          }
        });
      }
    }]
  });
});

I wasn't able to finish to make sure it works. Might get back to it later once Allan is done with the networking fixes.

Kevin

kthorngren

Here is the running code:
https://live.datatables.net/jamesewu/1/edit

Kevin

lisarush

OK, thank you very much. I have not had time to come back and look at this... and will be out tomorrow... I will take a closer look and play with it next week.
Again, thank you. You are all quick to respond and help, and it is very much appreciated. Have a great weekend!

lisarush

I have looked at your example and played with the code some...
In your example, the processCell method is called both from createdCell and from customize. However, the values passed to it are not the same.

When passed from createdCell, it receives html/object data like:
* cell: <td> (html element)
* cellData: object with attributes, e.g. in our case, it has value and alarm
When passed from customize, it receives Excel cell data like:
* cell: <c t="n" r="H9" s="73"> (xlsx element)
* cellData: just the raw text inside the cell (which is a number in our case)

I essentially need access to the cellData object as passed from createdCell, so I can access the alarm attribute. I haven't found any place that seems to have that data -- since customize receives the XLSX structure and not the HTML table structure. I need processCell to look more like this:

function processCell(cell, cellData) {
    return (cellData.alarm === 'RED') ? 'red' : null;
  }

Thoughts:
* Way to use the api() parameter of the customize method to access the original element?
* A "pre" callback hook (before the xls is generated, passing the dataTables objects) would be really nice... as I would much prefer to just add CSS styling, instead of having to muck with Excel style numbers.

So... I started playing with this using the api parameter, but it definitely needs some work... (making sure I'm correctly matching up the cells... particularly dealing with table header rows & potentially? if columns are not visible on the screen) And maybe there's an easier way to do this... thought I'd see if you had any suggestions while I'm working on it, as you definitely know the api better...

customize: function( xlsx, buttonConfig, api ) {
    let sheet = xlsx.xl.worksheets['sheet1.xml'];
    let cellContext = api.cell(0,0).context[0];  // get context from first cell
    let numDataRows = cellContext.aoData.length;

    $('row c', sheet).each(function() {
        console.log('--------------------' );
        let excelCell = $(this);
        let excelData  = $(this).text();
        let cellIdx = $(this).attr('r');
        let rowPosition = cellIdx.search(/[0-9]/);
        // Excel is 1-based, dataTables is 0-based
        let rowIdx = Number.parseInt(cellIdx.substring(rowPosition)) - 1;
        let colIdx = letterToColumn(cellIdx.substring(0, rowPosition)) - 1;
        console.log("cellIdx = " + cellIdx + ", row = " + rowIdx + ", col = " + colIdx);

        let objCell = api.cell(rowIdx, colIdx);
        console.log({msg:'objCell = ', obj:objCell});
        // TODO: way to tell if it's a header row, so those can be skipped?

        let aoColumns = cellContext.aoColumns;
        let createdCellCallback = aoColumns[colIdx].createdCell;   // diff between createdCell & fnCreatedCell?

        let objCellData = objCell.data();
        console.log({msg:'objCellData = ', obj:objCellData});

        if ( typeof createdCellCallback !== 'undefined' && createdCellCallback !== null ) {
           let highlight = createdCellCallback( objCell, objCellData );
           if ( highlight === 'red' ) {
               console.log( {msg:'adding red styling to ', obj:objCellData, excelData:excelData, excelCell:excelCell} );
               excelCell.attr('s', '39');
            }
        }
   });
}

kthorngren

I'm not sure without seeing your data structure. Can you update the test case to show examples of your data? Currently it uses ajax loaded data but you can remove the ajax option and use Javascript loaded data like this example.

Kevin

kthorngren

// TODO: way to tell if it's a header row, so those can be skipped?

You can add an index counter like this:

$('row c', sheet).each(function( index ) {

If the index is 0 then that is the header.

Kevin

lisarush

try this: https://live.datatables.net/jamesewu/3/edit

lisarush

We use multi-row headers, so it's not always just header row 0.

lisarush

However, I was able to calculate the # of header rows this way, though it might not be the most efficient:

   let sheetData = $('sheetData', sheet);
   let numExcelRows = $(sheetData).children().length;
   let cellContext = api.cell(0,0).context[0];  // get context from first cell
   let numDataRows = cellContext.aoData.length;
   let numHeaderRows = numExcelRows - numDataRows;

kthorngren

You can use orthogonal data for the export, like this example. You will need to determine, in the processCell() function, if the cellData is from option columns.createdCell or from the Excel export. I simply added a flag so the data can be parsed properly.
https://live.datatables.net/vajoridu/1/edit

Kevin

lisarush

OK... I guess I'm wondering how this works (and if it works on a multi-page table)... Wouldn't the render function have to be called to get the number value?

Also, we already use the orthogonal attribute to specify the filter value. So, how would that work?

lisarush

And actually, it doesn't work... the Excel output contains 'RED' and not the number value.

kthorngren

I set the pageLength to 2 for multiple pages and added a row to have two RED alarms; one on each page. I added a console.log statement to the export orthogonal data.
https://live.datatables.net/vajoridu/2/edit

You will see the export orthogonal data is executed when the export button is clicked. It iterates all the rows and is independent of the other orthogonal data.

Kevin

lisarush

OK, but the Excel output still doesn't have the # value. The cell reads RED instead of e.g. 100,000.

lisarush

What about something like this? It's pretty "hacky" though...
https://live.datatables.net/gebicicu/5/edit
... though I don't know how to properly set the updated excelCell.text value?

I've also been playing with using the api parameter inside customize (as in post from 8:00am) to get the cellData/object values there -- but unfortunately, they don't match up with the Excel table and I'm not sure how to go about trying to do that.

kthorngren

See if this is better:
https://live.datatables.net/qorovupu/1/edit

It creates a JSON string of the data object in the export orthogonal data. In customize it parse the JSON string, passes that to the function and changes the cell text with $('is t', this).text( cellData.value );.

Kevin

lisarush

I was just heading down the JSON path... Our code for customize is generic across all tables. So, I added
* calculation of # of header rows (we often use multi-row headers)
* check for when it's not JSON
* converted numeric cell values back to a Number cell (for nice display & sorting in Excel)
* don't have access to createdCell method. Instead, created a convention for us to use, expecting values of value and highlight inside the returned JSON, e.g.: exportData = { value: 123, highlight: 'red' }

The 'is t' selector on the xml doc was helpful!

Need to do a little more testing, but here is my updated version:
https://live.datatables.net/gebicicu/6/edit

The only thing that would be nice... is if I can also figure out how to make the Excel column width be appropriate to the width of the updated/real data value.

kthorngren

In this thread I used

        $(this).attr('ht', 60);
        $(this).attr('customHeight', 1);

I found something here that seems to document these cell settings. Looks like there is a corresponding column setting for width, found here.

I updated my example to show a hard coded way to set the column width:
https://live.datatables.net/qamorima/1/edit

It has a rudimentary calculation for column width of longest string + 1. Or you can try deciphering the formulas in the above link

Kevin

lisarush

Actually, looking at it, I think it's fine as-is. Thanks so much!

kthorngren

You posted just as I updated the example. Take another look if interested.

Kevin

kthorngren

Good work with your example. Looks like you are now the resident expert on OpenXML with Datatables

Kevin

lisarush

Ha! Thanks, feels a little kludgy, but works for our case. Again, thanks for your help! DataTables is a great plugin.

A "pre" callback hook before building the Excel table would be a good addition.