Introduction

This assumes basic familiarity with DataTables, its buttons extension, and the related ability to export table data to various file formats, including Excel.

I already wrote some overview notes here, but this is a large subject, and needs a bit more space.

Here, I will focus on reference material which is useful when you want to customize Excel exports.

More specifically I will only consider Office Open XML (.xlsx) files. Older .xls files (up to Excel 2003) use a different format and are not considered here.

Excel File Structure

The official Microsoft reference documentation for Excel file structures is here:

Excel (.xlsx) Extensions to the Office Open XML SpreadsheetML File Format

The underlying standards on which the above extensions are based can be found here:

ECMA-376 - Office Open XML file formats

In a nutshell, an Excel file is a zip file containing a series of sub-folders and XML documents.

Using Open Office nomenclature, the zip file is a “package” which contains “parts” (the XML files) and optionally other files such as graphics files.

We will look at a very simplified version of that structure, as used by DataTables:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
{
    "_rels": {
        ".rels": xml
    },
    "xl": {
        "_rels": {
            "workbook.xml.rels": xml
        },
        "workbook.xml": xml,
        "styles.xml": xml,
        "worksheets": {
            "sheet1.xml": xml
        }

    },
    "[Content_Types].xml": xml
}

DataTables builds a default Excel file from a series of XML strings hard-coded into the following JavaScript resource:

https://cdn.datatables.net/buttons/1.6.5/js/buttons.html5.js

Search for var excelStrings to see these for yourself. Here you will see, for example, the 68 predefined built-in cell styles which you can refer to when exporting DataTables data.

Content Types

The [Content_Types].xml file contains a list of the different parts in the package, together with each part’s type - for example:

1
2
PartName="/xl/workbook.xml"
ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"

Relationships

The _rels\.rels file contains a set of relationship IDs. These are then used to define the relationships between different parts in the package.

As well as the top-level .rels file, there can be others - such as the workbook.xml.rels file shown above, which can contain relationships to the other parts of the overall content, such as sheet1.xml, sharedStrings.xml, styles.xml, and so on.

An example is:

1
2
3
4
<Relationship
  Id="rId1"
  Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"
/>

Workbook

The workbook.xml file contains a list of worksheets:

1
2
3
4
5
6
7
<workbook>
    ...
    <sheets>
        <sheet name="Sheet1" sheetId="1" r:id="rId1"/>
    </sheets>
    ...
</workbook>

Here we can see the rId1 relationship ID being used.

Styles

The styles.xml file contains information relating to fonts, borders, fills, alignment, and so on. These can then be re-used in different locations.

An example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<styleSheet>
    ...
    <numFmts count="2">
        <numFmt numFmtId="43" formatCode="_(* #,##0.00_);_(* \(#,##0.00\);_(* &quot;-&quot;??_);_(@_)"/>
        <numFmt numFmtId="164" formatCode="#,##0.00_-\ [$$-45C]"/>
    </numFmts>
    ...
    <fonts count="1" x14ac:knownFonts="1">
        <font>
            <sz val="11"/>
            <color theme="1"/>
            <name val="Calibri"/>
            <family val="2"/>
            <scheme val="minor"/>
        </font>
    </fonts>
    ...
    <borders count="1">
        <border>
            <left/>
            <right/>
            <top/>
            <bottom/>
            <diagonal/>
        </border>
    </borders>
    ...
    <cellStyleXfs count="1">
        <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
    </cellStyleXfs>
    ...
    <cellXfs count="2">
        <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
        <xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1" applyAlignment="1">
            <alignment horizontal="center"/>
        </xf>
    </cellXfs>
    ...
    <cellStyles count="1">
        <cellStyle name="Normal" xfId="0" builtinId="0"/>
    </cellStyles>
  ...
</styleSheet>

Worksheets

The sheet1.xml file contains worksheet data:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<worksheet>
    ...
    <cols>
        <col min="1" max="1" width="18.85546875" customWidth="1"/>
        <col min="2" max="2" width="24.28515625" customWidth="1"/>
    </cols>
    <sheetData>
        <row r="1" spans="1:2">
            <c r="A1" s="1" t="s">
                <v>0</v>
            </c>
            <c r="B1" s="1" t="s">
                <v>1</v>
            </c>
        </row>
        <row r="2" spans="1:2">
            <c r="A2" t="s">
                <v>2</v>
            </c>
            <c r="B2" t="s">
                <v>3</v>
            </c>
        </row>
    </sheetData>
    ...
</worksheet>

Rows and Cells

In a row such as the following:

1
2
3
4
5
6
<row r="13">
  <c r="A13" s="12" t="s">
    <v>25</v>
  </c>
  ...
</row>

The meanings for each part are as follows:

ItemDescription
<row>a row
r="13"row 13
<c>cell tag
r="A13"cell reference A13
s="12"the cell’s formatting is at <xf> index 12 in the <cellXfs> formatting list
t="s"the t type attribute for the cell, where s means “shared strings”, where the text is stored.
<v="25"/>the string’s index is 25 - it’s the 26th string in the shared strings list.

List of t types:

TypeDescription
bboolean
ddate
eerror
inlineStran inline string (i.e., not stored in the shared strings part, but directly in the cell)
nnumber, stored directly in the cell
sshared string (so stored in the shared strings part and not in the cell)
stra formula (a string representing the formula)

The following example shows a cell (B2) containing the number 400, stored in the cell itself:

1
2
3
<c r="B2" s="5" t="n">
    <v>400</v>
</c>

The above is just a small sample of the possible configuration options supported by Excel.

Documentation

Excel Options

The following page lists the main options which can be attached to an Excep export button.

https://datatables.net/reference/button/excel

Items of note are:

  • customize: function( xlsx ) { ... } - provides low-level access to the Excel file, where customizations can be made - typically by direct manipulation of the XML structures described in earlier sections of this post.
  • exportOptions - see the exportData section below for details.
  • filename and extension - for the Excel file.
  • header and footer - whether or not to include the table’s header and footer.
  • messageTop and messageBottom - additional line of data to be included in the spreadsheet.
  • sheetName - Excel sheet name.
  • title - added to the Excel sheet. See the exportInfo section below for details.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
var table = $('#example').DataTable( {
  dom: 'Brftip',
  buttons: [
    {
      extend: 'excel',
      text: 'Save as Excel',
      filename: 'custom_name',
      extension: '.xlsx'
    }
  ]
} );

Here, options such as filename can also be functions:

1
2
3
filename: function() {
  return 'custom_name2';
}

Exported Data

The following page covers various ways to control what specific data is exported from the DataTable to Excel.

https://datatables.net/reference/api/buttons.exportData()

If the export options are provided as part of the DataTable button configuration, then you use the exportOptions: { ... } option referred to in the previous section.

If you use this as a standalone processor, then you use the exportData( { ... } ) function as follows:

1
2
3
4
5
6
7
var table = $('#myTable').DataTable();

var data = table.buttons.exportData( {
    modifier: {
        selected: true
    }
} );

Items of note are:

  • rows - a row selector.
  • columns - a column selector.
  • orthogonal - what orthogonal data to include.
  • stripHtml - strips HTML by default.
  • stripNewlines - stripts newlines by default.
  • format - various cell formatting functions - see below.

For the format option, you can provide various functions as follows:

1
2
3
4
5
format: {
  header: function ( data, columnIdx ) {
    return columnIdx +': '+ data;
  }
}

Items of note:

  • header (innerHTML, colIdx, node) - format the header data.
  • footer (innerHTML, colIdx, node) - format the footer data.
  • body (innerHTML, rowIdx, colIdx, node) - format the body data.
  • customizeData (data) - a final chance to customize the overall data. Mutate the data object which contains a header array, a footer array and a body 2D array. No return value is used.

Export Info

The following page covers options which may be common to multiple buttons for one table:

https://datatables.net/reference/api/buttons.exportInfo()

As such, the options available here overlap with some of those presented in the “Excel Options” section above - for example:

  • filename and extension - for the Excel file.
  • messageTop and messageBottom - additional line of data to be included in the spreadsheet.
  • title - added to the Excel sheet. By default, this will use the web page’s <title> value.

This is generally used outside of a button or with a custom button.

Low-Level Changes

Consider the following button option:

1
2
3
4
5
6
buttons: [
  customize: function( xlsx ) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
    $( 'c[r=A1] t', sheet ).text( 'Custom text' );
  }
]

This accesses the underlying XML data for the Excel worksheet. It then uses jQuery to select the text content of cell A1. Finally it updates that content.

Some other examples:

Change row 1’s formatting:

1
$('row:first c', sheet).attr( 's', '42' );

NOTE: As of jQuery 3.4, the :first pseudo-class is deprecated. Remove it from your selectors and filter the results later using .first().

Change row 3’s formatting:

1
$('row[r=3] c', sheet).attr( 's', '25' );

Change column B’s formatting:

1
$('row c[r^='B']', sheet).attr( 's', '25' );

WARNING - if your spreadsheet has sufficient columns, you may find this also selects column BA, BB, and so on.

Change any row where the row number contains a 3 (3, 13, 23, 30, 31…):

1
$('row c[r*="3"]', sheet).attr( 's', '25' );

Get the last column in the sheet:

1
sheet.getElementsByTagName('col').length - 1

Here is an example which preserves very long numbers as text (and not, for example, as numbers in scientific notation):

https://stackoverflow.com/a/52047216/12567365

Here is an example which adds a new cell formatter:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
var table = $('#example').DataTable( {
  dom: 'Brftip',
  buttons: [
    customize: function( xlsx ) {
      addCustomNumberFormat(xlsx, '#,##0.0##');
    }
  ]
} );

function addCustomNumberFormat(xlsx, numberFormat) {
  // this adds a new custom number format to the Excel "styles" document:
  var numFmtsElement = xlsx.xl['styles.xml'].getElementsByTagName('numFmts')[0];
  // assume 6 custom number formats already exist, and next available ID is 176:
  var numFmtElement = '<numFmt numFmtId="176" formatCode="' + numberFormat + '"/>';
  $( numFmtsElement ).append( numFmtElement );
  $( numFmtsElement ).attr("count", "7"); // increment the count

  // now add a new "cellXfs" cell formatter, which uses our new number format (numFmt 176):
  var celXfsElement = xlsx.xl['styles.xml'].getElementsByTagName('cellXfs');
  var cellStyle = '<xf numFmtId="176" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"'
      + ' applyFont="1" applyFill="1" applyBorder="1"/>';
  // this will be the 8th "xf" element - and will therefore have an index of "7", when we use it later:
  $( celXfsElement ).append( cellStyle );
  $( celXfsElement ).attr("count", "69"); // increment the count
}

function formatTargetColumn(xlsx, col) {
  var sheet = xlsx.xl.worksheets['sheet1.xml'];
  // select all the cells whose addresses start with the letter prvoided
  // in 'col', and add a style (s) attribute for style number 68:
  $( 'row c[r^="' + col + '"]', sheet ).attr( 's', '68' );  
}

The following example shows the creation of additional sheets in the Excel file created from Datatables:

https://gist.github.com/northcoder-repo/05bde971c7879ea5ebc4907f323376fc

Custom Buttons

You can create completely customized buttons:

https://datatables.net/extensions/buttons/custom

https://datatables.net/reference/option/buttons.buttons.action

Consider this:

$.fn.DataTable.ext.buttons.excelHtml5.action.call(that, e, dt, node, config);

It uses the JavaScript call() method to call a function.

As used here, it allows us to provide a customized “please wait” pop-up modal (Swal is SweetAlert2):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
buttons: [
  {
    text: 'Excel',
    action: function(e, dt, node, config) {
      var that = this;
      Swal.fire('Data is being loaded...');
      setTimeout(function() {
        $.fn.DataTable.ext.buttons.excelHtml5.action.call(that, e, dt, node, config);
        Swal.close();
      }, 1000);
    }
  }
]