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/excludesparticular cells or columns. ColumnModel->{column options}withexportcol:falseorhidden:falsewill be excluded for export.
Once we implemented automatic column hiding for responsive table by setting priority with CSS classes.
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 exportToPdf, exportToCsv, exportToExcel 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
maxLengthto 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);
?>
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Comments to “jqGrid example and demo for excel export in JavaScript”
Leave a Reply
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.
As always, great content lady.
Thank you.
Welcome!