Expand All

Basics

Formatting

Context menu

Drag n Drop

Spreadsheet

Tabs

Export

Layouts

RTL Layout

Rows

Paging

Big data

Columns

Cells

Inline editing

Row Grouping

Pivot

Sorting

Filter

Selections

Nesting / Row details

Tree grid

Charts

Angular

React React

Vue Vue

Knockout

;

Export of javascript grid to Excel spreadsheet in xlsx format

Export to Excel in xlsx format has been implemented to cover any possible use case scenario with help of configuration options which include both declarative and imperative methods.

API

Most of the export to Excel functionality configuration is based on parameters passed to exportData method, and values of column.skipExport, column.exportRender, rowData.pq_hidden, rowData.pq_selected properties.

Header

Header of grid is exported by default. It can be skipped by passing header: false parameter to exportData method.

There is an eachCellHead callback parameter to exportData method to alter properties (value, link, format, comment, color, bgColor, underline, italic, bold, font, fontSize, wrap, border ) of every exported header cell.

There is an eachRowHead callback parameter to exportData method to alter properties (format, comment, color, bgColor, underline, italic, bold, font, fontSize, wrap, border ) of every exported header row.

Columns

Specific columns can be excluded from exported data by setting the property skipExport to true. This property can be applied to grouped as well as ungrouped columns. When this property is applied to grouped columns, it prevents all their children columns from being exported. Child's defined value of this property overrides that of the parent when deciding the export of the column.

Hidden columns remain hidden in the exported Excel spreadsheet, they can be completely removed by passing skipHiddenColumns: true to exportData method.

There is an eachCol callback parameter to exportData method to alter properties ( hidden, format, comment, color, bgColor, underline, italic, bold, font, fontSize, wrap, border ) of every exported column.

Rows

Only selected rows or rows with property pq_rowselect: true can be exported by passing selection: 'row' parameter to exportData method.

Hidden rows remain hidden in the exported Excel spreadsheet, they can be completely removed by passing skipHiddenRows: true to exportData method.

There is an eachRow callback parameter to exportData method to alter properties (format, comment, color, bgColor, underline, italic, bold, font, fontSize, wrap, border) of every exported row.

Filtering

Only filtered rows are exported by default. Unfiltered rows are exported as hidden rows when filterModel.hideRows: true

Cells

Cells data rowData[ dataIndx ] along with formatting and static styles are exported by default. To include the rendered values and styles returned by column.render callback ( if any ) for all columns, pass render: true to exportData method.

To include the rendered values of few columns only, don't pass render: true to exportData method, rather set column.exportRender to true for those columns. Vice versa to exclude the rendered or formatted values of few columns only, pass render: true to exportData method and set column.exportRender to false for those columns.

ui.Export is true inside the column.render callback to help render the cells differently in exported spreadsheet than in grid

There is an eachCell callback parameter to exportData method to alter properties (value, link, format, comment, color, bgColor, underline, italic, bold, font, fontSize, wrap, border ) of every exported cell.

Pictures

Images can be displayed inside grid cells in the following ways:

  • Define the image content using a URL or Base64-encoded data.
  • Set the cell type to Pic by assigning rowData.pq_cellprop[dataIndx].inst = "Pic".

In the example below, the JSON data contains the picture name. Inside the dataModel.getData callback, the cell content is converted to a full image URL, and its cell type is set to Pic. 


if (rd.image) {
  // Convert to a valid URL
  rd.image = window.location.origin + "/pro2/content/images/" + rd.image;

  // Set the cell type to 'Pic'
  rd.pq_cellprop = {
    image: {
      inst: 'Pic'
    }
  };
}

Note: Pictures inserted via the toolbar using the Range().picCell() API are automatically assigned the Pic cell type.

Hyperlinks

For quick reference, hyperlinks can be defined in the grid by any of the following:

  • set rowData.pq_cellprop[ dataIndx ].link property to url
  • grid.Range( "A2" ).link( "http://paramquery.com" ) method
  • HYPERLINK spreadsheet formula
  • return hyperlink html e.g., <a href="https://foo.com..">Bar</a> in column.render callback.

Hyperlinks created with above ways are exported by default except the last one in which case it's required to use column.exportRender to true or pass render: true to exportData method to export the hyperlinks.

Styling of hyperlinks

Hyperlinks inherit the style of their parent cells. linkStyle parameter can be passed to exportData method to apply styles to hyperlinks if cells have no required styles. Note that styles passed through linkStyle parameter have lower priority than corresponding cell styles.

Top level flow diagram of xlsx export process

grid.exportData() returns javascript workbook of the grid when workbook: true is passed to the method. javascript workbook can be finally exported to Excel spreadsheet with pq.excel.exportWb({ workbook: wb }) method.

javascript workbook is the javascript object representation of exported spreadsheet data

Any customization which is not possible or convenient through above configuration options can be done at this stage by altering the javascript workbook itself. It opens up lot more possibilities like combining of multiple worksheets, add, remove, update new cells, rows, columns. apply formatting, styles to any cell, etc In order to do this, please check structure of workbook.

Third party libraries

Export to Excel spreadsheet functionality of ParamQuery grid is dependent upon following third party libraries: