jqGrid example and demo for excel export in JavaScript

by Vincy. Last modified on February 11th, 2024.

jqGrid is a JavaScript library that helps to display feature-packed tabular data in the UI. It is an AJAX-enabled tool that supports server-side processing to load table data from the database.

An example code is created for displaying a basic table with jqGrid AJAX in a previous article. This article will use this library to enable data table export controls on the UI.
View Demo

Basic knowledge about the jqGrid data export

It has advanced export controls that allow customization based on configurable options.

  • It applied export on the filtered, sorted data presently on the jqGrid.
  • It includes/excludes particular cells or columns.
  • ColumnModel->{column options} with exportcol:false or hidden:false will be excluded for export.

Once we implemented automatic column hiding for responsive table by setting priority with CSS classes.
jqgrid example excel export javascript

Create jqGrid target and load required dependencies

This HTML code loads the jqGrid and other dependent libraries required to enable the export controls.

It loads jQuery and jqGrid assets and a custom JS named export.js. This custom JS file contains the export handler with jqGrid initiator.

At the end of this HTML code, it calls the jqGrid initiator to load the grid data from the database. Let us see the PHP endpoint and the Database script later in this article.

index.php

<!DOCTYPE html>
<html>

<head>
	<meta charset="utf-8">
	<title>jqGrid UI with Excel CSV and PDF Export</title>
	<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.13.2/themes/base/jquery-ui.min.css" integrity="sha512-ELV+xyi8IhEApPS/pSj66+Jiw+sOT1Mqkzlh8ExXihe4zfqbWkxPRi8wptXIO9g73FSlhmquFlUOuMSoXz5IRw==" crossorigin="anonymous" referrerpolicy="no-referrer" />
	<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqgrid/5.8.2/css/ui.jqgrid.min.css" integrity="sha512-1tk2HnJ0rlO7C6UpPGU0N8eDX1UB0IekyUlv8UjrApkwedOrlrq8M7SMZpj0Xp6CLz3BFGCBB9iTb9fPv7azsA==" crossorigin="anonymous" referrerpolicy="no-referrer" />
	<link rel="stylesheet" href="style.css" type="text/css" />
	<link rel="stylesheet" href="form.css" type="text/css" />
</head>

<body>
	<div class="phppot-container">
		<h1>jqGrid UI with Excel CSV and PDF Export</h1>
		<!-- The target table element to load jqGrid data -->
		<table id="jqgrid-html-table"></table>

		<!-- Buttons to trigger export to PDF, CSV and Excel formats -->
		<div class="row">
			<button name="pdf-export" onClick="exportToPDF();">Export to PDF</button>
			<button id="export-csv" onClick="exportToCSV();">Export to CSV</button>
			<button id="export-excel" onClick="exportToExcel();">Export to Excel</button>
		</div>
	</div>

	<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.0/jquery.min.js" integrity="sha512-3gJwYpMe3QewGELv8k/BX9vcqhryRdzRMxVfq6ngyWXwo03GFEzjsUm8Q7RZcHPHksttq7/GFoxjCVUjkjvPdw==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/jqgrid/5.8.2/js/jquery.jqGrid.min.js" integrity="sha512-MMPWQuKgra1rVM2EEgZDWD3ZKmaNALAfKEb+zgkDgET/AS8bCWecd12zqYWoYQ+gpBqoIebb4k/686xHO4YkLQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>

	<!-- Libraries to support data export -->
	<script type="text/javascript" language="javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.26/build/pdfmake.min.js"> </script>
	<script type="text/javascript" language="javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.26/build/vfs_fonts.js"></script>
	<script type="text/javascript" language="javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>

	<script type="text/javascript" language="javascript" src="export.js"></script>

	<script>
		$(document).ready(function() {
			initjQGrid();
		});
	</script>
</body>

</html>

JavaScript export handlers and jqGrid initiator

This is the main section of this tutorial that explains the export stuff about the jqGrid library. If you want to enable export with the Datatables library, the link has the code to download.

The initjQGrid() function calls the library class by setting the data request option.

The jqGrid library supports JSON and XML datatypes to parse and load to the HTML table.

It defines the table header fields with the colNames and configures the keys with colModel to parse and load the response data.

The colModel property sets the alignment, column width, and more. It can exclude columns by setting hidden: true.

About export event handling

The export event can be triggered by stating exportToPdfexportToCsvexportToExcel with corresponding parameters.

The three export events have some common properties fileName, includeHeader, includeLabels, and more.

There are exclusive attributes varies based on the export format expected.

  • PDF export requires orientation, pageSize and download attributes.
  • CSV export requires specifying the column and line delimiters with separator and newLine properties.
  • Excel export has more or less the same properties as the CSV export. It allows setting the maxLength to limit the column data display area.

export.js

function initjQGrid() {
    $("#jqgrid-html-table").jqGrid({
        url: "get-jqgrid-data-ep.php",
        datatype: "json",
        mtype: "GET",
        colNames: ["User Id", "User Name", "First Name", "Last Name"],
        colModel: [
            { name: "userId", align: "right", width: 50 },
            { name: "userName", width: 90 },
            { name: "firstName", width: 90 },
            { name: "lastName", width: 90 }
        ],
        viewrecords: true,
        caption: "",
        loadonce: true,
        footerrow: true,
        userDataOnFooter: true,
        width: 780,
        height: 200
    });
}

function exportToPDF() {
    $("#jqgrid-html-table").jqGrid("exportToPdf", {
        title: 'jqGrid Export to PDF',
        orientation: 'portrait',
        pageSize: 'A4',
        description: 'This table is exported from the jqGrid data table',
        customSettings: null,
        download: 'download',
        includeLabels: true,
        includeGroupHeader: true,
        includeFooter: true,
        fileName: "jqGridExport.pdf"
    })
}

function exportToCSV() {
    $("#jqgrid-html-table").jqGrid("exportToCsv", {
        separator: ",",
        separatorReplace: "", // in order to interpret numbers
        quote: '"',
        escquote: '"',
        newLine: "\r\n", // navigator.userAgent.match(/Windows/) ?	'\r\n' : '\n';
        replaceNewLine: " ",
        includeCaption: true,
        includeLabels: true,
        includeGroupHeader: true,
        includeFooter: true,
        fileName: "jqGridExport.csv",
        returnAsString: false
    })
}

function exportToExcel() {
    $("#jqgrid-html-table").jqGrid("exportToExcel", {
        includeLabels: true,
        includeGroupHeader: true,
        includeFooter: true,
        fileName: "jqGridExport.xlsx",
        maxlength: 40 // maxlength for visible string data
    });
}

Database script

This SQL contains the create statement and all required keys. It replicates the database structure created for this example and lets it run in your environment.

database.sql

--
-- Database: `db_jqgrid_export`
--

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `userId` int(11) NOT NULL,
  `userName` varchar(255) NOT NULL,
  `firstName` varchar(255) NOT NULL,
  `lastName` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`userId`, `userName`, `firstName`, `lastName`) VALUES
(1, 'vincy', 'Vincy', 'J Gift'),
(2, 'ken', 'Ken', 'De Jane'),
(3, 'ellen', 'Ellen', 'De Souza'),
(4, 'kim', 'Kim', 'L Jupiter'),
(5, 'viola', 'Viola', 'S Tom'),
(6, 'joseph', 'Joseph', 'M Jeff'),
(7, 'seth', 'Seth', 'T Kings');

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`userId`);

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `userId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

PHP MySQL fetches and loads data to the jqGrid

This PHP script connects the database and accesses the table to fetch the record set.

The resultant array is encoded to JSON using PHP json_encode(). This will be read at the AJAX callback from where the request is initiated.

get-jqgrid-data-ep.php

<?php
$conn = mysqli_connect("localhost", "root", "", "db_jqgrid_export") or die("Connection Error: " . mysqli_error($conn));

$page = $_GET['page'];
$limit = $_GET['rows'];
$sidx = $_GET['sidx'];
$sord = $_GET['sord'];

if (!$sidx) {
    $sidx = "userId"; // Default sort index
}

$result = mysqli_query($conn, "SELECT COUNT(*) AS count FROM users");
$row = mysqli_fetch_array($result);

$count = $row['count'];

if ($count > 0 && $limit > 0) {
    $total_pages = ceil($count / $limit);
} else {
    $total_pages = 0;
}

if ($page > $total_pages) {
    $page = $total_pages;
}

$start = $limit * $page - $limit;
if ($start < 0) {
    $start = 0;
}

$SQL = "SELECT * FROM users ORDER BY $sidx $sord LIMIT $start, $limit";
$result = mysqli_query($conn, $SQL) or die("Couldn't execute query: " . mysqli_error($conn));

$response = new stdClass();
$response->page = $page;
$response->total = $total_pages;
$response->records = $count;
$response->rows = array(); 
$i = 0;
while ($row = mysqli_fetch_array($result)) {
    $response->rows[$i]['id'] = $row['userId'];
    $response->rows[$i]['cell'] = array(
        $row['userId'],
        $row['userName'],
        $row['firstName'],
        $row['lastName']
    );
    $i++;
}

echo json_encode($response);
?>

View Demo Download

Comments to “jqGrid example and demo for excel export in JavaScript”

Leave a Reply

Your email address will not be published. Required fields are marked *

↑ Back to Top

Share this page