excelHtml5

Create and save an Excel XLSX file that contains the data from the table (HTML5).
Please note - this property requires the Buttons extension for DataTables.

Description

This button provides the end user with the ability to save the table's data into a locally created Excel XLSX file.

The JSZip library must be available on the page or registered with DataTable.Buttons.jszip() if you are importing modules. JSZip is an MIT licensed library provides the ability to create a ZIP file in the browser, which is required to build a valid XLSX file.

At this time, although an XLSX file is created, data formatting, colours, etc are not retained. Only the raw data from the table is included in the exported file. For complete control over the generated file, a custom button could be constructed using the SheetJS library.

If your table has a header or footer with multiple rows, these will all be included in the export. If the header or footer contains colspan or rowspan cells, they will automatically be migrated to the Excel document for export.

Customisation

The Excel file that Buttons creates is intentionally quite simple - the default styling is:

• Calibri font, size 11 (matching Excel default)
• Header and footer are bold
• Column widths are auto sized to fit their contents (min: 6, max: 54)

However, you may wish to add additional information or formatting to the document to suit your output requirements. This ability is provided by the customize option of this button type.

The customize method is passed a single parameter - an object with the following structure (note that xml is simply a place holder to represent an XML document - each XML document is of course different):

{
    "_rels": {
        ".rels": xml
    },
    "xl": {
        "_rels": {
            "workbook.xml.rels": xml
        },
        "workbook.xml": xml,
        "styles.xml": xml,
        "worksheets": {
            "sheet1.xml": xml
        }

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

If you've developed with XLSX files before you will notice that this object's structure mimics the file structure of the XLSX file. When zipped this file structure will create an XLSX file - which is exactly what this button type does. The customize method provides you with the ability to modify any of the XML documents inside it, and even to add extra files (these are automatically detected in the structure and will be included in the zip).

As an example, let's modify the text shown in cell A1:

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

    $('c[r=A1] t', sheet).text( 'Custom text' );
}

On line two we get the XML document used for the spreadsheet's data. Then on line four a little bit of jQuery is used to select the correct cell's text node (the r attribute of the c element is where the cell will be shown and the t element is the text node). Then set the text for the cell. You could just as readily use DOM methods if you prefer.

We can use the a similar method to customize the styling of the cells in the document. This is done by adding the s attribute to the c element(s), where the attribute value is the style index you wish to use. The XLSX file created by Buttons has a number of built in styles which are documented below.

This is only a brief summary of how to customise the XLSX files. Full details of the XLSX file format and its features are outside the scope of this documentation. Please refer to the Microsoft and Office Open XML documentation for details.

Built in styles

The following indexes are available form the styles that are predefined in the Editor XLSX style file. These indexes can be applied to any cells in the generated spreadsheet, altering their appearance.

• 0 - Normal text
• 1 - White text
• 2 - Bold
• 3 - Italic
• 4 - Underline
• 5 - Normal text, grey background
• 6 - White text, grey background
• 7 - Bold, grey background
• 8 - Italic, grey background
• 9 - Underline, grey background
• 10 - Normal text, red background
• 11 - White text, red background
• 12 - Bold, red background
• 13 - Italic, red background
• 14 - Underline, red background
• 15 - Normal text, green background
• 16 - White text, green background
• 17 - Bold, green background
• 18 - Italic, green background
• 19 - Underline, green background
• 20 - Normal text, blue background
• 21 - White text, blue background
• 22 - Bold, blue background
• 23 - Italic, blue background
• 24 - Underline, blue background
• 25 - Normal text, thin black border
• 26 - White text, thin black border
• 27 - Bold, thin black border
• 28 - Italic, thin black border
• 29 - Underline, thin black border
• 30 - Normal text, grey background, thin black border
• 31 - White text, grey background, thin black border
• 32 - Bold, grey background, thin black border
• 33 - Italic, grey background, thin black border
• 34 - Underline, grey background, thin black border
• 35 - Normal text, red background, thin black border
• 36 - White text, red background, thin black border
• 37 - Bold, red background, thin black border
• 38 - Italic, red background, thin black border
• 39 - Underline, red background, thin black border
• 40 - Normal text, green background, thin black border
• 41 - White text, green background, thin black border
• 42 - Bold, green background, thin black border
• 43 - Italic, green background, thin black border
• 44 - Underline, green background, thin black border
• 45 - Normal text, blue background, thin black border
• 46 - White text, blue background, thin black border
• 47 - Bold, blue background, thin black border
• 48 - Italic, blue background, thin black border
• 49 - Underline, blue background, thin black border
• 50 - Left aligned text (since 1.2.2)
• 51 - Centred text (since 1.2.2)
• 52 - Right aligned text (since 1.2.2)
• 53 - Justified text (since 1.2.2)
• 54 - Text rotated 90° (since 1.2.2)
• 55 - Wrapped text (since 1.2.2)
• 56 - Percentage integer value (automatically detected and used by buttons - since 1.2.3)
• 57 - Dollar currency values (automatically detected and used by buttons - since 1.2.3)
• 58 - Pound currency values (automatically detected and used by buttons - since 1.2.3)
• 59 - Euro currency values (automatically detected and used by buttons - since 1.2.3)
• 60 - Percentage with 1 decimal place (automatically detected and used by buttons - since 1.2.3)
• 61 - Negative numbers indicated by brackets (automatically detected and used by buttons - since 1.2.3)
• 62 - Negative numbers indicated by brackets - 2 decimal places (automatically detected and used by buttons - since 1.2.3)
• 63 - Numbers with thousand separators (automatically detected and used by buttons - since 1.2.3)
• 64 - Numbers with thousand separators - 2 decimal places (automatically detected and used by buttons - since 1.2.3)
• 65 - Numbers without thousand separators (automatically detected and used by buttons - since 1.2.4)
• 66 - Numbers without thousand separators - 2 decimal places (automatically detected and used by buttons - since 1.2.4)

Notes:

• Grey is #d9d9d9
• Red is #d99795
• Green is #6efce
• Blue is #c6cfef

Options

This button can have the following options set in its configuration object to customise its actions and display, in addition to those options which are available for all buttons (e.g. buttons.buttons.text):

action

autoFilter

className

createEmptyCells

customize

exportOptions

extension

filename

footer

header

messageBottom

messageTop

sheetName

text

title

Examples

new DataTable('#myTable', {
	layout: {
		topStart: {
			buttons: ['excelHtml5']
		}
	}
});

new DataTable('#myTable', {
	layout: {
		topStart: {
			buttons: ['excel']
		}
	}
});

new DataTable('#myTable', {
	layout: {
		topStart: {
			buttons: [
				{
					extend: 'excelHtml5',
					text: 'Save current page',
					exportOptions: {
						modifier: {
							page: 'current'
						}
					}
				}
			]
		}
	}
});

new DataTable('#myTable', {
	layout: {
		topStart: {
			buttons: [
				{
					extend: 'excelHtml5',
					autoFilter: true
				}
			]
		}
	}
});