jqGrid is a JavaScript library that helps to show feature-packed tabular knowledge within the UI. It’s an AJAX-enabled software that helps server-side processing to load desk knowledge from the database.
An instance code is created for displaying a fundamental desk with jqGrid AJAX in a earlier article. This text will use this library to allow knowledge desk export controls on the UI.
View Demo
Primary information in regards to the jqGrid knowledge export
It has superior export controls that permit customization based mostly on configurable choices.
- It utilized export on the filtered, sorted knowledge presently on the jqGrid.
- It consists of/excludes specific cells or columns.
- ColumnModel->{column choices} with exportcol:false or hidden:false will probably be excluded for export. As soon as we carried out automated column hiding for responsive desk by setting precedence with CSS courses.
Create jqGrid goal and cargo required dependencies
This HTML code masses the jqGrid and different dependent libraries required to allow the export controls.
It masses jQuery and jqGrid belongings and a customized JS named export.js. This practice JS file incorporates the export handler with jqGrid initiator.
On the finish of this HTML code, it calls the jqGrid initiator to load the grid knowledge from the database. Allow us to see the PHP endpoint and the Database script later on this article.
index.php
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>jqGrid UI with Excel CSV and PDF Export</title>
<hyperlink 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="nameless" referrerpolicy="no-referrer" />
<hyperlink rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqgrid/5.8.2/css/ui.jqgrid.min.css" integrity="sha512-1tk2HnJ0rlO7C6UpPGU0N8eDX1UB0IekyUlv8UjrApkwedOrlrq8M7SMZpj0Xp6CLz3BFGCBB9iTb9fPv7azsA==" crossorigin="nameless" referrerpolicy="no-referrer" />
<hyperlink rel="stylesheet" href="type.css" sort="textual content/css" />
<hyperlink rel="stylesheet" href="kind.css" sort="textual content/css" />
</head>
<physique>
<div class="phppot-container">
<h1>jqGrid UI with Excel CSV and PDF Export</h1>
<!-- The goal desk component to load jqGrid knowledge -->
<desk id="jqgrid-html-table"></desk>
<!-- Buttons to set off export to PDF, CSV and Excel codecs -->
<div class="row">
<button title="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="nameless" 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="nameless" referrerpolicy="no-referrer"></script>
<!-- Libraries to assist knowledge export -->
<script sort="textual content/javascript" language="javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.26/construct/pdfmake.min.js"> </script>
<script sort="textual content/javascript" language="javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.26/construct/vfs_fonts.js"></script>
<script sort="textual content/javascript" language="javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
<script sort="textual content/javascript" language="javascript" src="export.js"></script>
<script>
$(doc).prepared(perform() {
initjQGrid();
});
</script>
</physique>
</html>
JavaScript export handlers and jqGrid initiator
That is the principle part of this tutorial that explains the export stuff in regards to the jqGrid library. If you wish to allow export with the Datatables library, the hyperlink has the code to obtain.
The initjQGrid() perform calls the library class by setting the information request choice.
The jqGrid library helps JSON and XML datatypes to parse and cargo to the HTML desk.
It defines the desk header fields with the colNames and configures the keys with colModel to parse and cargo the response knowledge.
The colModel property units the alignment, column width, and extra. It might exclude columns by setting hidden: true.
About export occasion dealing with
The export occasion may be triggered by stating exportToPdf, exportToCsv, exportToExcel with corresponding parameters.
The three export occasions have some widespread properties fileName, includeHeader, includeLabels, and extra.
There are unique attributes varies based mostly on the export format anticipated.
- PDF export requires orientation, pageSize and obtain attributes.
- CSV export requires specifying the column and line delimiters with separator and newLine properties.
- Excel export has roughly the identical properties because the CSV export. It permits setting the maxLength to restrict the column knowledge show space.
 export.js
perform 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,
top: 200
});
}
perform exportToPDF() {
$("#jqgrid-html-table").jqGrid("exportToPdf", {
title: 'jqGrid Export to PDF',
orientation: 'portrait',
pageSize: 'A4',
description: 'This desk is exported from the jqGrid knowledge desk',
customSettings: null,
obtain: 'obtain',
includeLabels: true,
includeGroupHeader: true,
includeFooter: true,
fileName: "jqGridExport.pdf"
})
}
perform exportToCSV() {
$("#jqgrid-html-table").jqGrid("exportToCsv", {
separator: ",",
separatorReplace: "", // with a view to interpret numbers
quote: '"',
escquote: '"',
newLine: "rn", // navigator.userAgent.match(/Home windows/) ? 'rn' : 'n';
replaceNewLine: " ",
includeCaption: true,
includeLabels: true,
includeGroupHeader: true,
includeFooter: true,
fileName: "jqGridExport.csv",
returnAsString: false
})
}
perform exportToExcel() {
$("#jqgrid-html-table").jqGrid("exportToExcel", {
includeLabels: true,
includeGroupHeader: true,
includeFooter: true,
fileName: "jqGridExport.xlsx",
maxlength: 40 // maxlength for seen string knowledge
});
}
Database script
This SQL incorporates the create assertion and all required keys. It replicates the database construction created for this instance and lets it run in your atmosphere.
database.sql
--
-- Database: `db_jqgrid_export`
--
-- --------------------------------------------------------
--
-- Desk construction for desk `customers`
--
CREATE TABLE `customers` (
`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 knowledge for desk `customers`
--
INSERT INTO `customers` (`userId`, `userName`, `firstName`, `lastName`) VALUES
(1, 'vincy', 'Vincy', 'J Present'),
(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 desk `customers`
--
ALTER TABLE `customers`
ADD PRIMARY KEY (`userId`);
--
-- AUTO_INCREMENT for desk `customers`
--
ALTER TABLE `customers`
MODIFY `userId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
PHP MySQL fetches and masses knowledge to the jqGrid
This PHP script connects the database and accesses the desk to fetch the report set.
The resultant array is encoded to JSON utilizing PHP json_encode(). This will probably be learn on the AJAX callback from the place 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));
$web page = $_GET['page'];
$restrict = $_GET['rows'];
$sidx = $_GET['sidx'];
$sord = $_GET['sord'];
if (!$sidx) {
$sidx = "userId"; // Default kind index
}
$end result = mysqli_query($conn, "SELECT COUNT(*) AS depend FROM customers");
$row = mysqli_fetch_array($end result);
$depend = $row['count'];
if ($depend > 0 && $restrict > 0) {
$total_pages = ceil($depend / $restrict);
} else {
$total_pages = 0;
}
if ($web page > $total_pages) {
$web page = $total_pages;
}
$begin = $restrict * $web page - $restrict;
if ($begin < 0) {
$begin = 0;
}
$SQL = "SELECT * FROM customers ORDER BY $sidx $sord LIMIT $begin, $restrict";
$end result = mysqli_query($conn, $SQL) or die("Could not execute question: " . mysqli_error($conn));
$response = new stdClass();
$response->web page = $web page;
$response->complete = $total_pages;
$response->data = $depend;
$response->rows = array();
$i = 0;
whereas ($row = mysqli_fetch_array($end 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);
?>
