excel

Since: Buttons 1.0.0

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

Description

This button type serves as a proxy that will automatically detect if the excelHtml5 button can be used based on the browser's capabilities and the libraries available on the page.

This is provided as an option since the HTML5 button type requires a reasonably modern browser which may not always be available to the end user.

If the requirements of the excelHtml5 button are not satisfied, no button will be presented to the end user. For the requirements of each button type, please refer to its own documentation.

Styling and customisation

For information on how you can customise the created XLSX file, please refer to the Customisation section in the excelHtml5 button documentation.

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`

Type: function
Default: Unset

Create and save a Excel file.

`className`

Type: string
Default: buttons-excel

The button's class name. See buttons.buttons.className for details.

`createEmptyCells`

Type: boolean
Default: false
Since: 1.5.0

Option to instruct the Excel export to create empty cells. By default if a cell contains null or empty data, it will not be created in the exported spreadsheet. You may however wish for it to do so, based on styling requirements.

`customize`

Type: function
Default: undefined
Since: 1.2.0

This method can be used to modify the XSLX file that is created by Buttons. The first parameter that is passed in is an object that contains the XML files and the object structure matches the file system paths for those files in an XSLX file. Customisation of the XSLX file is a complex topic - please refer to the Customisation section in the excelHtml5 button documentation for full details.

As of Buttons 1.5.2 this function is passed three parameters:

An object that contains the XML files in the ZIP file structure used by Excel
The button configuration object
A DataTables API instance for the table the button belongs to.

`exportOptions`

Type: object
Default: {}

Select the data to be gathered from the DataTable for export. This includes options for which columns, rows, ordering and search. Please see the buttons.exportData() method for full details - the object given by this parameter is passed directly into that action to gather the required data.

`extension`

Type: string
Default: .xlsx

The extension to give the created file name.

`filename`

Type: string|function
Default: *

File name to give the created file (plus the extension defined by the extension option). The special character * is automatically replaced with the value read from the host document's title tag.

`footer`

Type: boolean
Default: false

Indicate if the table footer should be included in the exported data or not.

`header`

Type: boolean
Default: true

Indicate if the table header should be included in the exported data or not.

`messageBottom`

Type: string|function
Default: *
Since: 1.4.0

Message to be shown at the bottom of the table, or the caption tag if displayed at the bottom of the table.

`messageTop`

Type: string|function
Default: *
Since: 1.4.0

Message to be shown at the top of the table, or the caption tag if displayed at the top of the table.

`sheetName`

Type: string
Default: Sheet1

Name for the worksheet in Excel file created. The characters [] \ /: *? : are not allowed and will be removed if present.

`text`

Type: string
Default: Excel

The button's display text. The text can be configured using this option (see buttons.buttons.text) or the buttons.copy option of the DataTables language object.

`title`

Type: string|function
Default: *
Since: 1.4.0

Title of the table that will be included in the exported data. Please see buttons.exportInfo() for all options relating to this parameter.

Examples

DataTables initialisation: Use the excel button type to automatically alias the HTML button options.:

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

DataTables initialisation: Use the exportOptions to save only the data shown on the current DataTable page:

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