Skip to content

Export class

Overview

The class extends jqGrid class

The class itself is a set of methods for exporting of different formats. The class is a base for Render class where the export is done automatically with minimal codding. In this chapter we will consider the class more deeper and explain how it can be used separately for more control in the code.

The class support export in the following formats - Excel (native and xml), Pdf, Csv, Html.

To be able to export to Excel (native) and Html it is needed to add additional class library. Its name is Phpspreadsheet

To be able to export to Pdf it is needed to add additional class library. Its name is TcPdf

By default Guriddo PHP does not include these packages in its installation. To install these packages see Installing packages for exporting

Note

Export to Excel in xml format does not require the Phpspreadsheet library, which means that the method is build in the Guriddo PHP package.

Common rules

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

  • The methods export data if datatype parameter is set to obtain data from server.
  • The methods does not export if the datatype is local - use the client side export in this case.
  • The methods export the current data set, which means that it exports filtered, sorted and etc data.
  • Initially the methods export only 2000 records due to memory limitations. See variable gSQLMaxRows
  • The hidden columns are not included into the export
  • Build-in formatters and custom formatters does not work in the export, since they are applied at client side and currently we do not have sync tool to make these equal. For each method there are additional ways to format the data.

Export to Excel

Description of all related methods and variables can be found here.

Below is the list of the available excel export options with the default settings:

    protected $EXCEL = array(
        "file_type"=>"Xlsx",    //Xslx,Xls,Html,Ods, xml
        "file"=>"",
        "save_to_disk"=>false,
        "save_to_disk_only"=>false,
        "disk_file"=>"",
        "start_cell" => "A1",
        "creator"=>"jqGrid",
        "author"=>"jqGrid",
        "title"=>"jqGrid Excel",
        "subject"=>"Office Document",
        "description"=>"Document created with Guriddo",
        "keywords"=>"Guriddo, jqGrid, Excel",
        "font"=>"Arial",
        "font_size"=>11,
        "header_cell"=>"", 
        "header_title"=>"",
        "protect" => false,
        "password"=>"Guriddo",
        "format_int"=>"#0",
        "format_num"=>"#.00",
        "format_text"=>"@",
        "format_date"=>"yyyy-mm-dd",
        "composer"=>true,
        "path_to_phpexcel_class"=>"External/phpexcel/PHPExcel.php",
        "empty_date"=>true
    );

To modify the excel options use the method setExcelOptions like this:

$grid->setExcelOptions( array(
    "file_type"=>"Ods"
));

The most of options are self explained, but in brief we will describe it:

  • file_type - determines the excel output type. The Xlsx type is the Office Open XML format, Xls is Excel 97 and above format, Ods is Open Document Format/OASIS, Html is a HTML format, xml is SpreadsheetML Excel 2003 format
  • file describes the file which will be send to the browser when export is performed.
  • save_to_disk - if set to true save the files to the disk too. See option disk_file
  • save_to_disk_only - save the file only to disk without to send it to browser. See disk_file
  • disk_file set a file which will be saved to the server, when the above two (or one of them) options are set to true. If the above options are set to true and the disk_file is not set, the file with name jqGrid_Root will be created in the temporary directory on the server using sys_get_temp_dir() function.
  • start_cell determines from where the export data will begin to be created.
  • creator, author, title, subject, description, keywords are properties which describes the exported document
  • font, font_size describes the default font and size of the created document
  • header_cell set the position cell on which the header_title will be print. If it is not set, and header_title is set the start_cell is used and the spreadsheet data will be one row below.
  • header_title set the header title of the spreadsheet.
  • protect if set to true, the spreadsheet will be protected with the password described below
  • password the password for the protected document if the above option is true.
  • format_int, format_num, format_text, format_date are the default format strings for the fields of different data types if none other is set
  • composer - this option mean that by default a composer is used for the installation of the Phpspreadsheet lib and the lib is available in the vendor/autoload file. If the option is false use the path_to_phpexcel_class to set the valid path to PhpSpreadsheet lib.
  • path_to_phpexcel_class path to PhpSpreadsheet lib in case not a composer is used
  • empty_date in case the date is null or 0000-00-00 or '' it will be exported as empty string, otherwise is exported as 1970-01-01

Formatting exported fields

As described above when none is specified for the formatting we use a default formatting for different field types described in the default excel options. These formats can be changed and they apply to all the fields in the export.

To apply format different to the defaults at certain field, use the formatoptions at specified column. The option is called excel_format and can be set using the setColProperty method.

By example to format a certain field as percent format use the following code:

$grid->setColProperty("PercentField", array(
    ...
    "formatoptions"=>array("excel_format"="0%"),
    ...
    )
);

You can apply any formatting you want to a certain field as long as it’s a format that Excel recognizes.

Hint

An easy way to get the syntax for a particular format is to look at the cell Format dialog box.

It will be possible similar to the Post Processing to modify the PhpSpreadsheet object in order to achieve certain otput. This can be done using two variables excelFunc and excelClass. They work the same way as customFunc and customClass. See Post Processing

To the excelFunc we pass only the $objPHPExcel instance created with the Phpspreadsheet class

$objPHPExcel = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

Notes and demo

When this method is used within jqGrid and Edit classes they should be called separatley in order to perform the export.

When used in Render class everything is done automatically when the export to excel is enabled. The only that need to be done in this case is to set the excel=>true in the navigator and eventually to change the Excel option.

// Enable navigator
$grid->navigator = true;
// Enable excel export
$grid->setNavOptions('navigator', array("excel"=>true,"add"=>false,"edit"=>false,"del"=>false,"view"=>false));
// Set different filename
$grid->exportfile = 'Report.xlsx';
$grid->setExcelOptions( array(
    "font_size"=>12
));

When used separately and you want to display different header, width and hide some fields a colmodel array should be configured and passed as parameter to the method. If none is set in this case only column names are used.

The array has the following structure and properties

Array( 
    [0]=>Array("label"=>"Some label", "width"=>100, "hidden"=>true, "name"=>"colname"), 
    [1]=>Array(...),
     ...
);

Where

  • label is the header displayed for this field
  • width is the width in pixels
  • hidden (boolean) if set does not export this column
  • name is the name from column model

and should be set as third parameter.

$colmodel = array(
    array("label"=>"Some label", "width"=>100, "hidden"=>false, "name"=>"colname"),
    ....
);
$grid->exportToExcel(null, null, $colmodel);

If used this way the length of this array should be equal to the number of fields in the SQL command used.

Let suppose that when we export to excel we want to show additionally the ShipAddress and ShipCity and perform a summary on the field Freight. We will use again our example.
For this purpose we will first create a custom button in the navigator and use the build in JavaScript method for this purpose - excelExport. When used the method passes additionally a variable oper=excel to identify that we want a export to Excel.

Actually all the operations of the grid are determined with the varaible oper which is send to the script and can be obtained with $grid->oper.

Below is the JavaScript and php code.

<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PHP jqGrid Class Example</title> 

<link rel="stylesheet" type="text/css" media="screen" href="path_to_dist_dir/css/ext/jquery-ui.css" />
<link rel="stylesheet" type="text/css" media="screen" href="path_to_dist_dir/css/ui.jqgrid.css" />

<script src="path_to_dist_dir/js/ext/jquery.min.js" type="text/javascript"></script>
<script src="path_to_dist_dir/js/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="path_to_dist_dir/js/jquery.jqGrid.min.js" type="text/javascript"></script>
<script type="text/javascript">
jQuery(document).ready(function(){
// Craeate the grid manually
jQuery("#grid").jqGrid({
"colModel":[
     {"name":"OrderID","index":"OrderID","label":"ID","width":60, "key":true},
     {"name":"OrderDate","index":"OrderDate"},
     {"name":"CustomerID","index":"CustomerID"},
     {"name":"Freight","index":"Freight"},
     {"name":"ShipName","index":"ShipName"}
],
// point to the grid.php file
"url":"grid.php",
"datatype":"json",
"jsonReader":{repeatitems:false},
"pager":"#pager"
});
// Set navigator with search enabled.
jQuery("#grid").jqGrid('navGrid','#pager',{add:false,edit:false,del:false});

// add custom button to export the data to excel
jQuery("#grid").jqGrid('navButtonAdd','#pager',{
       caption:"", 
       onClickButton : function () { 
           jQuery("#grid").jqGrid('excelExport',{"url":"grid.php"});
       } 
});

});
</script>

</head>
<body>
<table id="grid"></table>
<div id="pager"></div>
</body>
</html>

In PHP code we will use ExportCommand to perform the export.

<?php
require_once 'path_to_your_project_vendor_dir/autoload.php';

use Guriddo\Utils\Utils;

// Connection to the server
$conn = new PDO('mysql:host=localhost;dbname=northwind','user','password');
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

// Create the jqGrid instance
$grid = new Guriddo\jqGrid\Render\Render($conn, 'pdo');

// Write the SQL Query
$grid->SelectCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders';
// we want to export additinal data when excel
$grid->ExportCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName, ShipAddress, ShipCity FROM orders';

$grid->dataType = "json";
if($grid->oper == 'excel') {
    // let set summary field
   $grid->exportToExcel(array('Freight'=>'Freight'));
} else {
   $grid->queryGrid();
}

Export to PDF

Exporting to pdf is done via the external TcPdf library

For installing TcPdf lib, please refer Installing packages for exporting

All the variables and methods related to export to Pdf can be read here

When this method is used within jqGrid and/or Edit classes they should be called separatley in order to perform the export.

When used in Render class everything is done automatically when the export to pdf is enabled. The only that need to be done in this case is to set the pdf=>true in the navigator and eventually to change the Pdf options.

// Enable navigator
$grid->navigator = true;
// Enable excel export
$grid->setNavOptions('navigator', array("pdf"=>true,"add"=>false,"edit"=>false,"del"=>false,"view"=>false));
// Set different filename
$grid->exportfile = 'Report.pdf';
$grid->setPdfOptions( array(
    "page_orientation" => "L"
));

When used separately and you want to display different header, width and hide some fields a colModel array should be configured and passed as parameter to the method. If none is set in this case only column names are used

The array has the following structure and properties

Array(
[0]=>Array("label"=>"Some label", "width"=>100, "hidden"=>true, "name"=>"colname"),
[1]=>Array(...),
...
);

Where

  • label is the header displayed for this field
  • width is the width in pixels
  • hidden (boolean) if set does not export this column
  • name is the name from column model

If set the length of this array should be equal to the number of fields in the SQL command used.

In case you manually point to the pdf lib, and If the pdf subdirectory is not suitable for you needs, you can change it place. In this case the "path_to_pdf_class" should be changed according to the new place and composer should be set to false.

This can be done using the setPdfOptions method (see below).

Currently the lib has by default the following fonts:

$core_fonts = array('courier', 'courierB', 'courierI', 'courierBI', 'helvetica', 'helveticaB', 'helveticaI', 'helveticaBI', 'times', 'timesB', 'timesI', 'timesBI', 'symbol', 'zapfdingbats');

Bellow is the list of all available pdf options which can be set using setPdfOptions method

Option Description Values Default
page_orientation Determines the orientation of the page. Can be Portrait or Landscape P - Portrait; L - Landscape P (Portrait)
unit Set the measure of the export. Note that if you change this value all the related options which contain measures (except pixels) should be changed in the appropriate way mm - Millimeters, cm- Centimeters, in - Inchs, pt-Point mm (Millimetters)
page_format Set various format of the page. A, B, C, E, G (from 0 - 12), USLETTER, LETTER and a lot of otther formats A4
creator Set the creator of the pdf document Any string jqGrid
author Set the author of the document Any string jqGrid
title Set the title of the document. This is the title when see the properties of the document. Any String jqGrid PDF
subject Set the subject of the document Any string Subject
keywords Set the keywords for the document Any string table, grid
margin_left set the left margin of the page in measurement defined in unit option Number 15
margin_top set the top margin of the page in measurement defined in unit option Number 7
margin_right set the right margin of the page in measurment defined in unit option Number 15
margin_bottom set the bottom margin of the page in measurement defined in unit option Number 25
margin_header set the header margin of the page in measurement defined in unit option Number 25
margin_footer set the footer margin of the page in measurement defined in unit option Number 25
font_name_main Defines the main font. This font is used in the headers. Any valid font name (as set in the font subdirectory) helvetica
font_size_main Defines the size of the font_name_main font. This font is used in the headers. Number 10
header_logo If set defines the image which will be placed at the top left corner of the page. The image should be placed in the image directory of the pdf directory Name of the image Empty string
header_logo_width Defines the width od the header image Number 0
header_title Defines the the title which will be printed at top of the page near th image logo if defined Any string Empty string
header Enables or disables the printing of the header related data. Important: By default this option is disabled. If you want to print some header data it is a good idea to increase the margin_top option true or false false
footer Enables or disables the printing of the footer related data. true or false true
font_monospaced Defines the default mono-spaced font Any valid font name courier
font_name_data Defines the font for the printing of the table data Any valid font name helvetica
font_size_data Defines the size default mono-spaced font Number 8
margin_footer Defines the margin of the footer in measurement defined in unit option Number 10
grid_head_color Defines the table header color background Color defined as html value #dfeffc
grid_head_text_color Defines the table header text color Color defined as html value #2e6e9e
grid_draw_color Defines the table border color Color defined as html value #5c9ccc
grid_header_height Defines the height of the table header data in measurement defined in unit Color defined as html value 6
grid_row_color Defines the table row color background Color defined as html value #ffffff
grid_row_text_color Defines the table row text color Color defined as html value #000000
grid_row_height Defines the height of the table row data in measurement defined in unit Color defined as html value 5
grid_alternate_rows Enable or disable zebra striping in the export true or false false
path_to_pdf_class Set the path and filename to the tcpdf class. String External/tcpdf/tcpdf.php
composer Enables/disables the search path true/false true
shrink_cell Try to shrink the cell content to the specified width reflecting the font size true/false true
reprint_grid_header If set to true prints the grid header on every new page true/false false
shrink_header If set to true try shrink the header cell true/false true
unicode Support of Unicode. This parameter is set to the instance of tcpdf true/false true
encoding Set the encoding of the pdf document. The parameter is set to instance of tcpdf Valid encodinf string UTF-8
destination Set the output destination of the document I : send the file inline to the browser (default). The plug-in is used if available. The name given by name is used when one selects the "Save as" option on the link generating the PDF. D : send to the browser and force a file download with the name given by name. F : save to a local server file with the name given by name. S : return the document as a string (name is ignored). FI : equivalent to F + I option FD : equivalent to F + D option E : return the document as base64 mime multi-part email attachment (RFC 2045) D
save_to_disk Saves the document to disk and output the result to browser true/false false
save_to_disk_only Saves the document only to the disk without to perform any other action true/false false
disk_file File name where the document will be saved in case save_to_disk or save_to_disk_only are set to true Valid path and filename. Directory should have write permissions. Empty string

The example is similar as the Excel export example, but of place of exportToExcel the exportToPdf method will be used and a tag pdf will be added to the javascript method.

The change in html is only this

// add custom button to export the data to pdf
jQuery("#grid").jqGrid('navButtonAdd','#pager',{
       caption:"", 
       onClickButton : function () { 
           jQuery("#grid").jqGrid('excelExport',{"url":"grid.php", tag: "pdf"
       } 
});

The php changes in the script are:

...
if($grid->oper == 'pdf') {
    // let set summary field
   $grid->exportToPdf(array('Freight'=>'Freight'));
} else {
   $grid->queryGrid();
}

Formatting fields

Currently in the export to pdf there are no special methods to format the data and it comes raw to the pdf document. The only way to perform custom formatting when export to excel is to use the approach described in Post Processing

Export to CSV

Exporting to CSV is build in the script there is no need to install additional external library

All the variables and methods related to export to CSV can be read here

When this method is used within jqGrid and/or Edit classes they should be called separatley in order to perform the export.

When used in Render class everything is done automatically when the export to pdf is enabled. The only that need to be done in this case is to set the csv=>true in the navigator and eventually to change the Csv options.

// Enable navigator
$grid->navigator = true;
// Enable excel export
$grid->setNavOptions('navigator', array("csv"=>true,"add"=>false,"edit"=>false,"del"=>false,"view"=>false));
// Set different filename
$grid->exportfile = 'Report.csv';
$grid->setCsvOptions( array(
    ""separator"=> ","
));

When used separately and you want to display different header, width and hide some fields a colModel array should be configured and passed as parameter to the method. If none is set in this case only column names are used

The array has the following structure and properties

Array(
[0]=>Array("label"=>"Some label", "width"=>100, "hidden"=>true, "name"=>"colname"),
[1]=>Array(...),
...
);

Where

  • label is the header displayed for this field
  • width is the width in pixels
  • hidden (boolean) if set does not export this column
  • name is the name from column model

If set the length of this array should be equal to the number of fields in the SQL command used.

Bellow is the list of all available csv options which can be set using setCsvOptions method

    protected $CSV = array(
        "file" => "",
        "save_to_disk"=>false,
        "save_to_disk_only"=>false,
        "disk_file"=>"",
        "separator"=>";",
        "sepreplace"=>" ",
        "quote" => '"',
        "escquote"=> '"',
        "addtitles"=>true, 
        "replaceNewLine" => ' '
    );
Option Type Description Default
file string The file name which will be send to the browser empty
save_to_disk bool Enables disables saving the document to disk and output the result to browser false
save_to_disk_only bool Enables/disables saving the document only to the disk without to perform any other action false
disk_file string File name where the document will be saved in case save_to_disk or save_to_disk_only are set to true empty
separator string Default separator for the fields ;
sepreplace string In case the field value contain string equal to separator replaces it with the value specified space
quote string Quote the fields with this value "
escquote string With what to esc the quote "
addtitles boolean If set to true ouput the header titles true
replaceNewLine string Replaces the newline characters (\n or \r\n) from the value field space

Formatting data fields

Currently in the export to csv there are no special methods to format the data and it comes raw to the csv document. The only way to perform custom formatting when export to excel is to use the approach described in Post Processing.

Please note that formatting in csv should be treated with care it is possible that certain symbols can destroy the reading of document.

The example is similar as the Excel export example, but of place of exportToExcel the exportToCsv method will be used and a tag csv will be added to the javascript method.

The change in html is only this

// add custom button to export the data to pdf
jQuery("#grid").jqGrid('navButtonAdd','#pager',{
       caption:"", 
       onClickButton : function () { 
           jQuery("#grid").jqGrid('excelExport',{"url":"grid.php", tag: "csv"
       } 
});

The php changes in the script are:

...
if($grid->oper == 'csv') {
    // let set summary field
   $grid->exportToCsv(array('Freight'=>'Freight'));
} else {
   $grid->queryGrid();
}