Jqgrid documentation pdf download

When using Guriddo jqGrid, it is often useful and important to export your data to MS Excel, PDF or CSV file formats in order to make it accessible offline, share it with other users, etc.

The Guriddo jqGrid JS grid provides client Excel, PDF an CSV export functionality (server-agnostic) which can be directly utilized to serve the purpose to share data in the aforementioned ways. To enable it, you can trigger export by invoking the exportToExcel, exportToPdf and exportToCsv methods from the client API of the grid.

Additionally, you have the option to customize the rows/columns and cells of the exported file by intercepting the export event.

Common rules¶

Below are common rules which are valid for all export methods.

The methods export data if datatype parameter is set to local or the loadonce parameter is set to true.
The methods does not export data which is requested at server - datatype is json or xml.
The methods export the current data set, which means that it exports filtered, sorted and etc data.
The hidden columns are not included into the export
The columns with property exportcol : false in colModel will be not exported. If set to true the column is exported instead that hidden : true is set
When a custom formatter is used, options parameter is extended with a properties: isExported which in this case is set to true and exporttype which can be pdf, csv or excel . This allow custom formatting when export occurred for a different export type.

Export to CSV¶

A CSV is a comma separated values file/string which allows data to be saved in a table structured format. CSVs look like a garden-variety spreadsheet but with a .csv extension (Traditionally they take the form of a text file containing information separated by commas, hence the name).

CSV files can be used with any spreadsheet program, such as Microsoft Excel, Open Office Calc, or Google Spreadsheets. They differ from other spreadsheet file types in that you can only have a single sheet in a file, they can not save cell, column, or row styling, and can not save formulas

This method does not require additional plugin or external programs. When called the method either propmt to save the exported data to a CSV file or it can return the exported data as CSV string depending on configuration (see below).

To export to CSV file format simple call the method:

$("#grid_id").jqGrid('exportToCsv', options);

Where options is a object with a following default properties:

options =  separator: ",", separatorReplace : " ", quote : '"', escquote : '"', newLine : "\r\n", replaceNewLine : " ", includeCaption : true, includeLabels : true, includeGroupHeader : true, includeFooter: true, includeHeader: true, fileName : "jqGridExport.csv", mimetype : "text/csv;charset=utf-8", onBeforeExport : null, returnAsString : false, loadIndicator : true, treeindent : ' ' >

separator - string - defines the separator between the fields
separatorReplace - string - string which replaces the separator which can be contained into the field string
quote - string - the test which enclosed the field
newLine - string - the characters put at end of each row for new line
replaceNewLine - string - replace the new line if it is contained into the data field
includeCaption - boolean - if false the grid caption will be not exported. Default is true
includeLabels - boolean - if false the grid header columns will be not exported. Default is true.
includeGroupHeader boolean - if set to false the group header will be not exported if they are activated. Default is true.
includeFooter - boolean - if false the footer row will be not included into the export if it is defined. Default is true.
includeHeader - boolean - if false the header row will be not included into the export if it is defined. Default is true.
fileName - string - the file name to save when data is exported. Default jqGridExport.csv
mimetype - string - a way of identifying files on the Internet according to their nature and format in our case CSV type of data. Usually this parameter should not be changed.
event - The event raises before building the csv file and is used as custom function to modify the text data before it is exported. Parameter passed to this event is the exported csv string
returnAsString - boolean - if set to true the data is returned as CSV string
loadIndicator - mixed. If the value is boolean when set to true shows the loading indicator message during the export. If the option is function it is called with parameter 'show' when the export begin and with parameter 'hide' when the export is ended.
treeindent - string - indent the ExpandColumn when treegrid is exporting. Default space

Export to Excel¶

A file with the XLSX file extension is a Microsoft Excel Open XML Format Spreadsheet file. It's an XML-based spreadsheet file created by Microsoft Excel version 2007 and later.

XLSX files organize data in cells that are stored in worksheets, which are in turn stored in workbooks, which are files that contain multiple worksheets. The cells are positioned by rows and columns and can contain styles, formatting, math functions, and more.

Spreadsheet files made in earlier versions of Excel are saved in the XLS format. Excel files that support macros are XLSM files

jqGrid export the data to Microsoft Excel Open XML Format only.

In order to use the method additional module is needed to be loaded. The name of the module is JSZip. The module is included into the package. More about the installation of this module can be seen here. The JSZip can be loaded via cdnjs.

If the JSZip module is not loaded the export to Excel will fail.

To export to Excel it is needed to load JSZip

script type="text/javascript" language="javascript" src="//cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js">script>

and call the method

$("#grid_id").jqGrid('exportToExcel', options);

Where options is a object with the following default properties:

options =  includeLabels : true, includeGroupHeader : true, includeFooter: true, includeHeader: true, fileName : "jqGridExport.xlsx", mimetype : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", maxlength : 40, onBeforeExport : null, replaceStr : null, customizeData : null, loadIndicator : true, treeindent : ' ' >

includeLabels - boolean - if false the grid header columns will be not exported. Default is true.
includeGroupHeader - boolean - if set to false the group header will be not exported if they are activated. Default is true.
includeFooter - boolean - if false the footer row will be not included into the export if it is defined. Default is true.
includeHeader - boolean - if false the header row will be not included into the export if it is defined. Default is true.
fileName - string - the file name to save when data is exported. Default jqGridExport.xlsx
mimetype - string - a way of identifying files on the Internet according to their nature and format in our case Excel Open XML type of data. Usually this parameter should not be changed.
maxlength - integer - set the max length of the visible string data
onBeforeExport (xlsx, rowPos)- event - The event raises before building the zip file and is used as custom function to modify the XML document before it is exported. Parameter passed to this event is the exported XML document and the rowPos (last inserted row index).
replaceStr (value) - event - custom event to parse the string data before inserted into the XML document. Parameter passed to this event is a string value. If this function is not defined we use internal parser to replace certain character which are not accepted in the XML document. The build in replace function has the following code:

function _replStrFunc (v)  return v.replace(/, '<') .replace(/>/g, '>') .replace(/[\x00-\x09\x0B\x0C\x0E-\x1F\x7F-\x9F]/g, ''); >

customizeData (data) - event - the event raises when the data is prepared for export and before any formatting. The passed parameter is a data ob]ect which contain the following: data.body - array containing objects of the rowdata
data.header - array containing the name property in colModel
data.footer - array containing footer data if set
data.labels - array contatining the label values of the header
data.map - contain the mapping of the header columns (special columns like multibox are ommited in export)
data.parser - contain the exportoptions of column if it is defined.
Any value of this object can be manipulated.
loadIndicator - mixed. If the value is boolean when set to true shows the loading indicator message during the export. If the option is function it is called with parameter 'show' when the export begin and with parameter 'hide' when the export is ended.
treeindent - string - indent the ExpandColumn when treegrid is exporting. Default space

When called the method open a save dialog with the exported data. Select Save to save the file or Open to open it with the associated application.

The standard behavior of export to Excel method recognizes data types with help of parser and format it according to defined formats. Currently we recognize the following formats - string, Percent with d.p., Percent, Dollars, Pounds, Euros, Numbers without thousand separators, Numbers 2 d.p. without thousands separators, Negative numbers indicated by brackets, Negative numbers indicated by brackets - 2d.p., Numbers with thousand separators, Numbers with 2 d.p. and thousands separators and dates. The format in Excel is as follow:

formatCode="#,##0.00_-\ [$$-45C]" formatCode=""£"#,##0.00" formatCode="[$€-2]\ #,##0.00" formatCode="0.0%" formatCode="#,##0;(#,##0)" formatCode="#,##0.00;(#,##0.00)" formatCode="yyyy/mm/dd;@"

If this is not acceptable, then it is possible to disable the parser for certain field. This can be done with the help of the option excel_parser set to false in colModel exportoptions object like this:

$("#grid").jqGrid( . colModel: [ .  name :'field'. exportoptions: excel_parser: false>. >, . ] >)

In this case we detect numbers not matching leading zeros or a negative and text. If this is not acceptable a custom export formatting can be defined - see above.

By default the string fields are parsed for "" and these are replaced with their HTML equivalent < and > with build in replaceStr function. See option replaceStr above.

Custom export to Excel¶

Custom export to excel allow to format the data as defined in exportoptions property in colModel by the developer. The default properties in export options have the following values:

exportoptions :  excel_parser : true, excel_format : '', replace_format : null >

excel_parser - this is Boolean property with default value true. In this case the build in parses are used to detect the values and format it according to the formatCode definitions - see above. In case this value is false the build in parsing is disabled and the behavior depend on the next parameter excel_format - see below.
excel_format - this is a string value. If the string is empty and the option above excel_parser is false the program detects only numeric and text fields. In case this option is set it try to format the value according to the rules of formatting in excel. With other words this string is a Excel formatting code.
replace_format - if defined this function can manipulate the value and make some replacement on it.

Example: Our standard formatting of dollars places a dollar sign at end of value. In case there is a need to put it at the beginning of the value the following code do this:

 $("#jqGrid").jqGrid( . colModel: [  label: 'Price', name: 'Price', formatter: 'number', align: 'right', exportoptions :  excel_parsers: false, excel_format : "[$$-45C]\ #,##0.00_-" > >, . ], . >);

Add custom colors and fonts in excel export¶

Added is a new function which enable adding custom styles (font and color only) during export - i.e the function can be called in onbeforeExport event and will return the new added style number which then can be used in the export.

The new function is called newExcelStyle and should be called like this within onbeforeExport event

var new_style_id = $.jgrid.newExcelStyle( xlsx, options);

The parameter xlsx is the Excel xml document with the data and styles - this parameter is assed to onBeforeExport event.

The options parameter is object has the following options.

options =  font :  size : 11, name : 'Calibri', options :"">, // options   color :  patternType : "solid", fgColor : "FFFFFFF", bgColor : 64 > // bgColor if number 0-64 >

The font option:

size: is the size of the font
name: is valid font name used in Excel
options: is a string ehich can describe property like bold italic undrline or combination of them

The color property has the following parameters

patternType can be "solid" or "none"
fgColor specifying a hex ARGB value
bgColor is value 64 which is default value (usually do not change it or specifying a hex ARGB value)

using our demo example we can change the color to red with font DejaVu Sans which is bold

$("#export").on("click", function() $("#jqGrid").jqGrid("exportToExcel",  includeLabels : true, includeGroupHeader : true, includeFooter: true, fileName : "jqGridExport.xlsx", onBeforeExport : function( xlsx )  var mystyle_id = $.jgrid.newExcelStyle(xlsx,  font :  name : 'DejaVu Sans', options : ''>, color : fgColor : 'ff1a1a'> >); var sheet = xlsx.xl.worksheets['sheet1.xml']; // Loop over the cells in column 'D' $('row c[r^="D"]', sheet).each( function ()  //Get the value and strip the non numeric characters if ( $(this).text()* 1 >= 50 )  // apply style definition 20 for the cell // we have 50 predefined styles $(this).attr( 's', mystyle_id + "" ); > >); >, maxlength : 40 // maxlength for visible string data >); >);

The returned id mystyle_id is the new style id which then can be used for cell formatting

Export to Pdf¶

(Portable Document Format)

Portable Document Format (PDF) is a file format used to present and exchange documents reliably, independent of software, hardware, or operating system. Invented by Adobe, PDF is now an open standard maintained by the International Organization for Standardization (ISO). PDFs can contain links and buttons, form fields, audio, video, and business logic.

Today PDF is the most used exchange document format.

jqGrid support export of its data to PDF.

In order to use the method additional module is needed to be loaded. The name of the module is pdfmake. The module is included into the package. More about the installation of this module can be seen here. The pdfmake and vfs_fonts can be loaded via cdn.

Since the pdf creation is a heavy work, please use the method in relative small data set. Using the script on data set with more than 300 rows can cause problems and memory leaks.

To export to PDF it is needed to load the following javascripts

script type="text/javascript" language="javascript" src="//cdn.rawgit.com/bpampuch/pdfmake/0.1.26/build/pdfmake.min.js"> script> script type="text/javascript" language="javascript" src="//cdn.rawgit.com/bpampuch/pdfmake/0.1.26/build/vfs_fonts.js">script>

and call the method

$("#grid_id").jqGrid('exportToPdf', options);

where the options is a object with the following properties and default values:

options =  title: null, orientation: 'portrait', pageSize: 'A4', description: null, onBeforeExport: null, download: 'download', includeLabels : true, includeGroupHeader : true, includeFooter: true, includeHeader: true, fileName : "jqGridExport.pdf", mimetype : "application/pdf", loadIndicator : true, treeindent : "-" >

title - string - The title of the exported data. Printed only once at top of the first page.
orientation - string - defines the orientation of the page. Can be 'portrait' or 'landscape'. Default is 'portrait'.
pageSize - string - defines the size of the page. Default is 'A4'. For all possible values see here
description - string - Free text which is printed after the title at first page. Default null.
download - string - defines how to handle the PDF. Available two values - 'open' - open the PDF in a new window or 'download' - download the PDF with file name set in fileName (see below)
includeLabels - boolean - if false the grid header columns will be not exported. Default is true.
includeGroupHeader - boolean - if set to false the group header will be not exported if they are activated. Default is true.
includeFooter - boolean - if false the footer row will be not included into the export if it is defined. Default is true.
includeHeader - boolean - if false the header row will be not included into the export if it is defined. Default is true.
fileName - string - the file name to save when data is exported. Default jqGridExport.pdf
mimetype - string - a way of identifying files on the Internet according to their nature and format in our case PDF type of data. Usually this parameter should not be changed.
onBeforeExport - event - The event raises before building the PDF file and is used as custom function to modify the PDF document definition before it is exported. Parameter passed to this event is the document definition object. For more information refer the pdfmake documentation here. See below the default settings for document definition
loadIndicator - mixed. If the value is boolean when set to true shows the loading indicator message during the export. If the option is function it is called with parameter 'show' when the export begin and with parameter 'hide' when the export is ended.
treeindent - string - indent the ExpandColumn when treegrid is exporting. Default "-"

The default settings for the document before PDF export are follow:

var documentDefinition =  pageSize: options.pageSize, pageOrientation: ooptions.orientation, content: [  style : 'tableExample', widths : jqgrid.widths, table:  headerRows: 0, //or 1 body: jqgrid.rows > > ], styles:  tableHeader:  bold: true, fontSize: 11, color: '#2e6e9e', fillColor: '#dfeffc', alignment: 'center' >, tableBody:  fontSize: 10 >, tableFooter:  bold: true, fontSize: 11, color: '#2e6e9e', fillColor: '#dfeffc' >, title:  alignment: 'center', fontSize: 15 >, description: <> >, defaultStyle:  fontSize: 10 > >;

This definition is passed to the onBeforeExport event and it can be modified for custom purposes.

Below example shows how to make the body font of the table body smaller using the onBeforeExport event.

$("#export").on("click", function() $("#jqGrid").jqGrid("exportToPdf", . onBeforeExport : function( doc )  doc.styles.tableBody.fontSize = 8; >, . >); >);

Export to Html¶

HTML is the standard markup language for creating web pages and web applications. With Cascading Style Sheets (CSS) and JavaScript it forms a triad of cornerstone technologies for the World Wide Web. Web browsers receive HTML documents from a web server or from local storage and render them into multimedia web pages. HTML describes the structure of a web page semantically and originally included cues for the appearance of the document.

This method does not require additional plugin or external programs. When called the method either shows a new html page (with option to print) or it can return the exported data as HTML string depending on configuration (see below).

To export to HTML format simple call the method:

$("#grid_id").jqGrid('exportToHtml', options);

Where options is a object with a following default properties:

options =  title: '', onBeforeExport: null, includeLabels : true, includeGroupHeader : true, includeFooter: true, includeHeader: true, tableClass : 'jqgridprint', autoPrint : false, topText : '', bottomText : '', returnAsString : false, treeindent : ' ' >

onBeforeExport - event - The event raises before building the HTML file and is used as custom function to modify the HTML document before it is exported. Parameter passed to this event is the exported html document.
includeLabels - boolean - if false the grid header columns will be not exported. Default is true.
includeGroupHeader - boolean - if set to false the group header will be not exported if they are activated. Default is true.
includeFooter - boolean - if false the footer row will be not included into the export if it is defined. Default is true.
includeHeader - boolean - if false the header row will be not included into the export if it is defined. Default is true.
tableClass - string - class to be added in the table when exported. Can be added more classes separated with space
autoPrint - boolean - if set to true after creating the html document in new browser window a print dialog appear automatically.
topText - string - text included at the top of the document
bottomText - string - text included at the bottom of the document
returnAsString - boolean - if set to true the html document is returned as string
treeindent - string - indent the ExpandColumn when treegrid is exporting. Default " "