Exporting to Excel from DataTables
Contents
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:
|
|
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:
|
|
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:
|
|
Workbook
The workbook.xml
file contains a list of worksheets:
|
|
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:
|
|
Worksheets
The sheet1.xml
file contains worksheet data:
|
|
Rows and Cells
In a row such as the following:
|
|
The meanings for each part are as follows:
Item | Description |
---|---|
<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:
Type | Description |
---|---|
b | boolean |
d | date |
e | error |
inlineStr | an inline string (i.e., not stored in the shared strings part, but directly in the cell) |
n | number, stored directly in the cell |
s | shared string (so stored in the shared strings part and not in the cell) |
str | a formula (a string representing the formula) |
The following example shows a cell (B2) containing the number 400, stored in the cell itself:
|
|
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 theexportData
section below for details.filename
andextension
- for the Excel file.header
andfooter
- whether or not to include the table’s header and footer.messageTop
andmessageBottom
- additional line of data to be included in the spreadsheet.sheetName
- Excel sheet name.title
- added to the Excel sheet. See theexportInfo
section below for details.
Example:
|
|
Here, options such as filename
can also be functions:
|
|
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:
|
|
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:
|
|
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 thedata
object which contains aheader
array, afooter
array and abody
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
andextension
- for the Excel file.messageTop
andmessageBottom
- 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:
|
|
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:
|
|
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:
|
|
Change column B’s formatting:
|
|
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…):
|
|
Get the last column in the sheet:
|
|
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:
|
|
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):
|
|
Author northCoder
LastMod 01-May-2021