html5 excel,excelHtml5

excelHtml5

Since: Buttons 1.0.0

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.

DescriptionRequires

In order to operate, this button requires the following:

Buttons' HTML5 export button plug-in.

A browser that supports local file saving (IE10+, Chrome, Firefox, Opera).

To ensure that all files required for Excel HTML5 export are included, the DataTables download builder is recommend - select the HTML5 export and JSZip options.

This button provides the end user with the ability to save the table's data into a locally created Excel 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.

This button requires a reasonably modern browser in order to operate (IE8 and 9 are not supported). For older browsers, the excelFlash button provides similar file creation and save options using the Flash plug-in. The excel button can be used to automatically select between this and the excelFlash buttons based on the browser's capabilities.

In addition to this, the JSZip library must also be available on the page. This MIT licensed library provides the ability to create a ZIP file in the browser, which is required to build a valid XLSX file.

Browser support

The API methods used by this button are supported in the following browsers. If the browser being used does not support the requirements for this button, it simply will not be displayed.

Internet Explorer 10+

IE9 and older are not supported as they do not support the FileReader or XML parsing APIs available in newer browsers.

Edge: Yes

Chrome: Yes

Opera: Yes

Safari: Yes (tech preview 18+) Safari 10.0.x and earlier are not supported.

Customisation

XLSX file format

It is important to note that Buttons does not provide any kind of abstraction for the XLSX XML! If you want to modify the output Excel file you need to understand the XML used for the spreadsheet. If you require an abstraction layer, consider creating a custom button that uses js-xlsx.

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: 5, max: 52)

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:NameTypeDefaultaction

Create and save a Excel XLSX file.

autoFilter

Since: 1.5.4false

Enable Excel's auto filter feature for the header cells in the table allowing the user to quickly filter and sort the exported spreadsheet in Excel. Note that this does not operate in LibreOffice (although the spreadsheet is still readable).

classNamebuttons-excel buttons-html5

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

createEmptyCells

Since: 1.5.0false

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

Since: 1.2.0undefined

This method can be used to modify the XLSX 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 XLSX file. Customisation of the XLSX 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{}

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.xlsx

The extension to give the created file name.

filename*

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.

footerfalse

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

headertrue

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

messageBottom

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

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.

sheetNameSheet1

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

textExcel

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

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 HTML5 Excel button:$('#myTable').DataTable( {

buttons: [

'excelHtml5'

]

} );

DataTables initialisation: Use the excel button type to automatically select between the Flash and HTML button options.:$('#myTable').DataTable( {

buttons: [

'excel'

]

} );

DataTables initialisation: Use the exportOptions to save only the data shown on the current DataTable page:$('#myTable').DataTable( {

buttons: [

{

extend: 'excelHtml5',

text: 'Save current page',

exportOptions: {

modifier: {

page: 'current'

}

}

}

]

} );

Enable the auto filter option in Excel:$('#myTable').DataTable( {

buttons: [

{

extend: 'excelHtml5',

autoFilter: true

}

]

} );