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.
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 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.
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.
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.
Only filtered rows are exported by default. Unfiltered rows are exported as hidden rows
when filterModel.hideRows: true
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.
For quick reference, hyperlinks can be defined in the grid by any of the following:
rowData.pq_cellprop[ dataIndx ].link property to urlgrid.Range( "A2" ).link( "http://paramquery.com" ) methodHYPERLINK spreadsheet formula<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.
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.
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.Export to Excel spreadsheet functionality of ParamQuery grid is dependent upon following third party libraries: