jQuery datatable export to excel with cell() background color

Question

PFB is my code sample: https://codepen.io/avinash-reddy95/pen/eYzORmJ.

<script type="text/javascript">
        $(document).ready(function () {
            var table = $('#example').DataTable({
            "paging": false,
            "info": false,
            searching: false,
            dom: 'Bfrtip',
            buttons: [
                {
                    extend: 'excelHtml5',
                    title: 'custom'
                }
            ]
            });
        });
    </script>

I am using jQuery datable to render a table and using dataTable export to Excel feature. I am able to export the excel successfully but I want to export to excel with cell back ground color if cell have any background color.

In above example columns "Salary", "Age", "date" have color red to some of the cells, I want to export with the colors. Could some one please help me on this?

Answer 1

This approach automates the creation of new Excel styles - one of which can be used to recreate HTML table cells with red backgrounds.

The starting point is a DataTable like this:

And the end result is a sheet like this:

In my case, I chose to make the Excel font white for those cells with a red background, just for demonstration purposes, but a small change would restore the original black.

Here is the code:

$(document).ready(function() {

var table = $('#example').DataTable( {
  dom: "Brftip",
  buttons: [{
    extend: 'excelHtml5',
    customize: function(xlsx) {
      // set up new styles:
      let styles = xlsx.xl['styles.xml'];
      let stylesCount = parseInt($( 'cellXfs', styles ).attr("count"), 10);
      addCellColorStyles(styles, stylesCount);
  
      // use new styles:
      let sheet = xlsx.xl.worksheets['sheet1.xml'];
      //$( 'row c', sheet ).attr( 's', (stylesCount + 1).toString() );
      highlightCells(sheet, stylesCount);
    }
  }]
} );

var myFontColors = [
  '548235', // idx 0 - mustard
  'ffc000', // idx 1 - dark green
  '000000', // idx 2 - black
  'ffffff'  // idx 3 - white
];

var myFillColors = [
  '548235', // idx 0 - mustard
  'ffc000', // idx 1 - dark green
  'ff0000'  // idx 2 - red
];

var myCellStyles = [
  { fontIdx: 0, fillIdx: 1 }, // idx 0 - mustard on green
  { fontIdx: 1, fillIdx: 0 }, // idx 1 - green on mustard
  { fontIdx: 2, fillIdx: 2 }, // idx 2 - black on red
  { fontIdx: 3, fillIdx: 2 }  // idx 3 - white on red
];

function addCellColorStyles(styles, stylesCount) {
  
  // add font styles:
  let fontsCount = parseInt($( 'fonts', styles ).attr("count"), 10);
  myFontColors.forEach((color) => {
    $( 'fonts', styles ).append( fontTmplt(color.toUpperCase()) );
  });
  $( 'fonts', styles ).attr("count", (fontsCount + myFontColors.length).toString());

  // add fill styles:
  let fillsCount = parseInt($( 'fills', styles ).attr("count"), 10);
  myFillColors.forEach((color) => {
    $( 'fills', styles ).append( fillTmplt(color.toUpperCase()) );
  });
  $( 'fills', styles ).attr("count", (fillsCount + myFillColors.length).toString());
  
  // add cell styles:
  myCellStyles.forEach((style) => {
    $( 'cellXfs', styles ).append( cellXfTmplt(fontsCount + style.fontIdx, fillsCount + style.fillIdx) );
  });
  $( 'cellXfs', styles ).attr("count", (stylesCount + myCellStyles.length).toString());
}

function highlightCells(sheet, stylesCount) {
  $( '#example' ).dataTable().api().rows( { search: 'applied' } )
      .every( function ( rowIdx, tableLoop, rowLoop ) {
    var xlRow = rowLoop +3; // +1 for DT zero index; +1 for title row; +1 for row heading in Excel
    $.each( $( 'td', $(this.node()) ), function( index, trNode ) {
      var bgColor = $(trNode).css("background-color");
      if ( bgColor && bgColor === 'rgb(255, 0, 0)' ) { // just handle red backgrounds
        let xlCol = createXlColLetter(index);
        let xlRef = xlCol + xlRow;
        let cellSelector = 'c[r=' + xlRef + ']';
        let cellStyle = 3; // 3 is my custom style index for white on red
        let cellStyleID = (stylesCount + cellStyle).toString(); 
        $(cellSelector, sheet).attr( 's', cellStyleID );
      }
    } );
  } );
}

// to build an Excel column letter reference from an 
// integer (1 -> A, 2 -> B, 28 -> AB, and so on...); 
function createXlColLetter( n ){
  var ordA = 'A'.charCodeAt(0);
  var ordZ = 'Z'.charCodeAt(0);
  var len = ordZ - ordA + 1;
  var s = ""; 
  while( n >= 0 ) {
    s = String.fromCharCode(n % len + ordA) + s;
    n = Math.floor(n / len) - 1;
  }
  return s;
}

// style templates
function fontTmplt(color) {
  return `<font><sz val="11" /><name val="Calibri" /><color rgb="${'FF' + color}" /></font>`;
}
function fillTmplt(color) {
  return `<fill><patternFill patternType="solid"><fgColor rgb="${'FF' + color}" /><bgColor indexed="64" /></patternFill></fill>`;
}
function cellXfTmplt(fontIdx, fillIdx) {
  return `<xf numFmtId="0" fontId="${fontIdx}" fillId="${fillIdx}" borderId="0" applyFont="1" applyFill="1" applyBorder="1" />`;
}

} );

<!doctype html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Cell Colors Demo</title>

    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css"/>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/2.2.3/css/buttons.dataTables.min.css"/>
 
    <script type="text/javascript" src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/buttons/2.2.3/js/dataTables.buttons.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/buttons/2.2.3/js/buttons.html5.min.js"></script>

    <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

</head>

<body>

<div style="margin: 20px;">

    <table id="example" class="display dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office in Country</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>61</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
            </tr>
            <tr>
                <td>Garrett Winters</td>
                <td>Accountant</td>
                <td>Tokyo</td>
                <td>63</td>
                <td>2011/07/25</td>
                <td>$170,750</td>
            </tr>
            <tr>
                <td>Ashton Cox</td>
                <td>Junior "Technical" Author</td>
                <td>San Francisco</td>
                <td>66</td>
                <td>2009/01/12</td>
                <td>$86,000</td>
            </tr>
            <tr>
                <td>Cedric Kelly</td>
                <td>Senior Javascript Developer</td>
                <td style="background-color: red">Edinburgh</td>
                <td>22</td>
                <td>2012/03/29</td>
                <td>$433,060</td>
            </tr>
            <tr>
                <td>Airi Satou</td>
                <td>Accountant</td>
                <td>Tokyo</td>
                <td style="background-color: red">33</td>
                <td>2008/11/28</td>
                <td>$162,700</td>
            </tr>
            <tr>
                <td>Donna Snider</td>
                <td>Customer Support</td>
                <td>New York</td>
                <td>27</td>
                <td>2011/01/25</td>
                <td>$112,000</td>
            </tr>
        </tbody>
    </table>

</div>

</body>
</html>

Some of the code is the same as my original attempt, but there are also several differences. The main difference is that this code adds new styles to the Excel sheet created by DataTables automatically, in the addCellColorStyles function.

The user must first set up whatever background color(s) they want to use (see myFillColors) - and, optionally, foreground (font) colors.

These are then combined into new styles, in myCellStyles.

Because we are adding styles to a list of existing styles already in the sheet, we have to keep track of the style counts - so there is logic to do that - and to adjust the custom style indexes accordingly:

let cellStyle = 3; // 3 is my custom style index for white on red
let cellStyleID = (stylesCount + cellStyle).toString();

---

Overall, this approach also assumes there is a title in row 1 of the spreadsheet:

var xlRow = rowLoop +3; // +1 for DT zero index; +1 for title row; +1 for row heading in Excel

So, if the heading was suppressed, then the +3 would need to be adjusted to +2.

---

But with this approach, the entire spreadsheet generation process is automated.

Answer 2

Your use of cells like this (simplified)...

<td><a href="" style="background-color: red;"><i>$12,345</i></a></td>

...does make things more complicated.

To determine the background color used by your HTML, you can iterate over your table's cells and then extract the color that way.

Here is an example of doing that.

Assume you have all this logic in a function:

customize: function ( xlsx ) {
  var sheet = xlsx.xl.worksheets['sheet1.xml'];
  highlightCells(sheet);
}

The function is:

   function highlightCells(sheet) {
    $( '#example' ).dataTable().api().rows( { search: 'applied' } )
        .every( function ( rowIdx, tableLoop, rowLoop ) {
      var xlRow = rowLoop +2; // +1 for DT zero index; +1 for row heading in Excel
      $.each( $( 'td', $(this.node()) ), function( index, trNode ) {
        var bgColor = $('a', trNode).css("background-color");
        if ( bgColor ) {
          var xlCol = createXlColLetter(index);
          var xlRef = xlCol + xlRow;
          console.log( 'xlRef = ' + xlRef + ": bgColor = " + bgColor );
          var cellSelector = 'c[r=' + xlRef + ']';
          console.log( cellSelector );
          var cellStyleID = 10; // assume all are "rgb(255, 0, 0)" (red)
          $(cellSelector, sheet).attr( 's', cellStyleID );
        }
      } );
    } );
  }

It also uses a supporting function to translate column integers to Excel letters:

  // to build an Excel column letter reference from an 
  // integer (1 -> A, 2 -> B, 28 -> AB, and so on...); 
  function createXlColLetter( n ){
    var ordA = 'A'.charCodeAt(0);
    var ordZ = 'Z'.charCodeAt(0);
    var len = ordZ - ordA + 1;
    var s = "";
 
    while( n >= 0 ) {
        s = String.fromCharCode(n % len + ordA) + s;
        n = Math.floor(n / len) - 1;
    }
 
    return s;
  }

The main function locates a cell's background color using this selector:

var bgColor = $('a', trNode).css("background-color");

In my code above I assume it's always red (rgb(255, 0, 0)) and therefore I hard-code the formatting ID to be 10.

But you could use a switch statement to handle a range of colors.

However...

This may still only be a partial solution for you.

If you have cell data which is not plain text (such as an Excel currency value or a number) then applying one of the built-in styles may force that Excel cell to lose its number formatting. Each built-in style provided by DataTables overwrites any other styles you may want.

For example - if I start with this HTML table:

...I will end up with this Excel sheet:

And you can see that the currency formatting has been lost - because style 10 is Normal text, red background. Normal means the Excel "general" text format.

You cannot apply 2 or more DataTables styles to one cell.

So, this means you now have to prepare all the styles you need in advance, in an Excel spreadsheet, then unzip the Excel file, grab the relevant HTML from the relevant Excel styles file, and apply it to your DataTables Excel file.

This is what the OP does in the question I originally linked to. So I think that is what you will also need to do in your case.

You can still use my sample code above, after doing that - but you will be using style IDs which are from your style sheet - not the built-in ones available in DataTables.