Exporting


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
  • When a custom formatter is used, options parameter is extended with a property isExported which in this case is set to true. This allow custom formatting when export occured.

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,
  fileName : "jqGridExport.csv",
  mimetype : "text/csv;charset=utf-8",
  returnAsString : false
}
  • 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.
  • 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.
  • returnAsString - boolean - if set to true the data is returned as CSV string

Export to Excel

(Excel Open XML)

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,
  fileName : "jqGridExport.xlsx",
  mimetype : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
  maxlength : 40,
  onBeforeExport : null,
  replaceStr : null
}
  • 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.
  • 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 - 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.
  • replaceStr - event - custom event to parse the string data before inserted into the XML document. Parameter passed to this event is a string value.

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 export to Excel method recognizes two data types when exported - numeric and string. Note that the data is exported as it is seen into the grid - i.e formatted with formatters if they are used.

By default the string fields are parsed for "<" and ">" and these are replaced with their HTML equivalent &lt; and &gt;

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.

Warning

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,
  fileName : "jqGridExport.pdf",
  mimetype : "application/pdf"  
}
  • 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.
  • 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

The default settiing for the document before PDF export is as 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;
    },
    ...
  });
});