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:

List of t types:

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);
    }
  }
]