Data Export

SpreadSheet data can be exported to Excel, PDF, CSV, and PNG formats. Call the corresponding method: toPDF, toPNG, toExcel, or toCSV to export data from SpreadSheet into the file of the necessary format. For example, for Excel:

webix.ui({
    id:"ss1",
    view:"spreadsheet",
    data: sheet1_data
});
 
webix.toExcel("ss1");

By default, SpreadSheet exports data with the following settings:

  • header:false - doesn't export the sheet header
  • rawValues:true - exports raw cell values instead of the rendered ones (false for PDF export)
  • spans:true - exports column and row spans (column spans only for PDF export)
  • styles:true - exports styles
  • conditions:true - exports the styles of conditional formatting
  • heights:true - exports custom row heights
  • math:true - exports math formulas (false for PDF export)
  • ignore:{ rowId:true } - doesn't export the column with row ids
  • hidden:true - exports hidden rows and columns as hidden, so that users can show them via Excel controls (Excel export only; other formats don't export hidden rows and columns)

You can change this behavior by specifying the desired settings as an object in the second parameter of the export methods. For example:

webix.toExcel("ss1", options);

Related sample:  Export to .xlsx, .pdf and .png

Export to Excel Settings

SpreadSheet provides a range of settings for exporting data into an Excel file.

  • math formulas

Spreadsheet exports math formulas by default. To export formula results instead, set the math option to false in the second parameter of the toExcel method.

webix.toExcel($$("ss1"),{ math:false });

Spreadsheet supports sheet-specific named ranges only; global named ranges will not be exported.

  • cell styles

The styles are exported by default. If you do not want to, set the styles option to false in the second parameter of the toExcel method.

webix.toExcel($$("ss1"), { styles:false });
  • conditional formatting

It is possible to export styles of conditional formatting if the conditions option is set to true (default).

webix.toExcel($$("ss1"), { conditions:true });
  • column and row spans

The spans are exported by default. If you do not want to, set the spans:false property in the second parameter of the toExcel method:

webix.toExcel($$("ss1"), { spans:false });
  • hidden columns and rows

Hidden columns and rows are exported as hidden to Excel and can be shown in the file by its controls. If you want to ignore them, set the hidden property to false:

webix.toExcel($$("ss1"), { hidden:false });
  • row heights

The heights option defaults to true and can take the following values:

  • true - default, exports only custom row heights (different from the default rowHeight);
  • "all" - exports both custom and default row heights;
  • false - doesn't export row heights.
webix.toExcel($$("ss1"), {
   heights:"all"
});
  • multiple sheets

By default, Spreadsheet exports the currently active sheet to an Excel file. You can have three more possible options:

1) to export all the sheets, set the sheets:true option in the second parameter of the toExcel method:

webix.toExcel($$("ss1"), { sheets:true });

2) to export separate sheets, set an array with sheet ids as a value of the sheets option:

webix.toExcel($$("ss1"), { sheets:[ "s1","s2" ] });

3) to export a certain sheet, set its id as a value of the sheets option:

webix.toExcel($$("ss1"),{sheets:"s2"});
  • the header of a sheet

By default the header of a sheet is not exported. To export the content of a header, you need to use the header option and set it to true:

webix.toExcel($$("ss1"), {
   header:true
});
  • stub cells

Spreadsheet stubs empty cells by default. A stubbed cell keeps its space empty in the exported file, and the content of the adjacent cells cannot overflow it. If you do not want to stub cells, set the stubCells property to false:

webix.toExcel($$("ss1"), {
   stubCells:false
});
  • images export

By default, Spreadsheet exports images and charts (as images) to Excel. If you want to switch the export of images off, you need to set the images option to false:

webix.toExcel($$("ss1"), { images: false });
  • frozen rows/columns

Spreadsheet exports frozen rows and columns to Excel by default. If you do not want to export them, set the freeze option to false:

webix.toExcel($$("ss1"), { freeze:false });
  • formula editor, gridlines, headers

You can adjust the settings to define whether the formula editor, gridlines and headers will be visible or hidden in the exported file. For this, use the formulasMode, gridlines and headers properties in the second parameter of the toExcel method, correspondingly.

They can be set to true/false or to the "auto" value. If the "auto" setting is specified, the visibility of the formula editor, gridlines or headers will depend on the current state.

webix.toExcel($$("ss1"), {
  formulasMode: true,
  gridlines: true,
  headers: true
});
  • zoom

You can specify the necessary zooming level when exporting SpreadSheet to an Excel file with the help of the zoom property. It can be set as a number to define the percentage of the zooming level or as the "auto" value to export SpreadSheet in the current state.

webix.toExcel($$("ss1"), {
  zoom: "auto"
});

Supported elements/settings for Excel export

  • data (including math and dates)
  • font
  • font size (converts px into pt)
  • bold, italic, underline, strike
  • background
  • color
  • borders
  • horizontal align
  • vertical align
  • rows/cols sizes (converts px into pt)
  • hidden rows/cols
  • merge
  • wrap
  • indent
  • number format
  • named ranges
  • conditional formats (saves color and background-color)
  • links
  • sheet visibility
  • checkbox/radio (exported as TRUE/FALSE)
  • images
  • frozen rows/columns
  • zoom
  • header visibility
  • gridlines
  • formulas mode
  • row and column grouping

You can compare elements and settings supported by SpreadSheet for import and export to Excel.

Exporting SpreadSheet with a custom skin

In case you use a custom skin for SpreadSheet, you need to specify the default styles for exporting it to Excel. It means you should use the copy of your custom styles from the CSS file while creating a skin, so that they will be exported to Excel correctly. For example:

webix.skin[skin_name].spreadsheet = {
    "color": "#666666",
    "background" : "#ffffff",
    "font-family": "'PT Sans', Tahoma",
    "font-size": "15",
    "text-align": "left",
    "vertical-align": "middle",
    "white-space": "nowrap",
    "format": "common"
}

Export to PDF Settings

While exporting SpreadSheet to a PDF document you can configure the following settings:

  • math formulas

You need to set the math:true property in the second parameter of the toPDF method.

webix.toPDF($$("ss1"),{ math:true});

Note that if you set the option to false, the formula value will be exported - not the formula itself.

  • cell styles

Spreadsheet exports cell styles by default. If you do not want to export them, set the styles option to false in the second parameter of the toPDF method.

webix.toPDF($$("ss1"), { styles:false });
  • conditional formatting

It is possible to export conditional formatting if the conditions option is set to true (default).

webix.toPDF($$("ss1"), { conditions:true });
  • column spans

Spreadsheet exports column spans by default. If you do not want to export them, set the spans option to false in the second parameter of the toPDF method:

webix.toPDF($$("ss1"), { spans:false });

Row spans aren't exported to PDF.

  • hidden columns and rows

By default hidden columns and rows are not included during export. You can include them by setting the hidden property to true:

webix.toPDF($$("ss1"), { hidden:true });

The hidden columns/rows will be visible in the exported file.

  • row heights

The heights option defaults to true and can take the following values:

  • true - default, exports only custom row heights (different from the default rowHeight);
  • "all" - exports both custom and default row heights;
  • false - doesn't export row heights.
webix.toPDF($$("ss1"), {
   heights:false
});
  • multiple sheets

By default, Spreadsheet exports the currently active sheet to a PDF file. You can have three more possible options:

1) to export all the sheets, set the sheets:true option in the second parameter of the toPDF method:

webix.toPDF($$("ss1"), { sheets:true });

2) to export separate sheets, set an array with sheet IDs or sheet objects as a value of the sheets option:

webix.toPDF($$("ss1"), { 
    sheets: [
        {id:"Sheet1", options:{display:"image"}},
        {id:"Sheet2"},
        "Sheet3"
    ] 
});

3) to export a certain sheet, set its id as a value of the sheets option:

webix.toPDF($$("ss1"),{sheets:"s2"});
  • the header of a sheet

By default the header of a sheet is not exported. To export the content of a header, you need to use the header option and set it to true:

webix.toPDF($$("ss1"), {
   header:true
});

Exporting Data Offline

You can export SpreadSheet data offline. To enable this possibility, you should follow the common way for all data components.

Back to top
Join Our Forum
We've retired comments here. Visit our forum for faster technical support, connect with other developers, and share your feedback there.
If you have not checked yet, be sure to visit site of our main product Webix javascript ui framework and page of web based spreadsheet product.