ParamQuery grid support forum
General Category => Help for ParamQuery Pro => Topic started by: vijay on November 05, 2025, 12:03:02 pm
-
Hi team,
We have upgraded pqgrid from 9 to 11 version.
After upgrading below functionality is not working now.
While Exporting data to excel we have done some formatting which is not working.
Refer below code and suggest alternative for same.
//----------- Remove HTML tags and keep text. --------------------------------------
pq.excel.eachCell(blob.sheets, function (cell) {
var val = cell.value;
if (typeof val == "string" && val.indexOf("<") != -1) {
if (val.indexOf("<input") != -1) {
if ($(val)) {
val += ' ' + $(val).val();
}
}
var $p = $("<p>").html(val);
cell.value = $p.text();
}
if (typeof val == "string") {
cell.value = " " + cell.value;
}
if (cell.hasOwnProperty("format") && !!cell.format && dateFormats.indexOf(cell.format.toLowerCase()) >= 0) {
cell.value = formatDateTime(val);
}
});
//----------------------- Insert Title in First Row
blob.sheets[0].rows.insert(0, title);
// ----------------- Increase frozen rows
blob.sheets[0].frozenRows += 1;
// ----------------- Increase header rows
blob.sheets[0].headerRows += 1;
*Note : We have converted export function to async after which file is exported but above formatting is not working.
-
How did you obtain the value of blob.sheets?
Please debug your code to check the value of blob.sheets, and set a breakpoint inside the eachCell callback to verify how it’s working.
Kindly share your complete code for better understanding—or even better, provide a minimal reproducible example on JSFiddle.
-
using below code we get access to blob.sheets
var blob = this.exportData({
format: 'xlsx',
nopqdata: true,
render: true,
workbook: true
});
Full export code for reference
listener: function () {
var format = 'xlsx';
var blob = this.exportData({
format: format,
nopqdata: true,
render: true,
workbook: true
});
var fileName = 'download';
//-------- Header Row Print in excel
if (format == 'xlsx') {
if (config.exportTitle == true && config.titleData.length > 0) {
var titleCount = config.titleData.length;
$(config.titleData).each(function (tIndex, title) {
blob.sheets[0].rows.insert(tIndex, title);
});
blob.sheets[0].frozenRows += titleCount; // Increase frozen rows
blob.sheets[0].headerRows += titleCount; // Increase header rows
// As we have added header shift merging cells with the number of header lines
if (blob.sheets[0]['mergeCells'] && blob.sheets[0].mergeCells.length > 0) {
$(blob.sheets[0].mergeCells).each(function (i, v) {
var m = v.split(":");
var d1 = m[0].replace(/\D+/g, ''); // get digit;
var a1 = m[0].replace(/\d+/g, ''); // get alphabet;
d1 = parseInt(d1) + titleCount;
var d2 = m[0].replace(/\D+/g, ''); // get digit;
var a2 = m[0].replace(/\d+/g, ''); // get alphabet;
d2 = parseInt(d2) + titleCount;
blob.sheets[0].mergeCells = [a1 + d1 + ":" + a2 + d2];
});
}
}
//-------------------------
//----------- Remove HTML tags and keep text.
pq.excel.eachCell(blob.sheets, function (cell) {
var val = cell.value;
if (typeof val == "string" && val.indexOf("<") != -1) {
if (val.indexOf("<input") != -1) {
if ($(val)) {
val += ' ' + $(val).val();
}
}
var $p = $("<p>").html(val);
cell.value = $p.text();
}
if (typeof val == "string") {
cell.value = " " + cell.value;
}
if (cell.hasOwnProperty("format") && !!cell.format && dateFormats.indexOf(cell.format.toLowerCase()) >= 0) {
cell.value = formatDateTime(val);
}
});
blob = pq.excel.exportWb({ workbook: blob });
}
saveAs(blob, fileName + "." + format);
}
* Note : Above code was working properly in v9.0.1
-
I have attached debugging image for blob.sheets
please check.
-
What's formatDateTime function?
please check the returned value of formatDateTime(val) in your code. is it expected/ correct value?
cell.value = formatDateTime(val);
-
formatDateTime(val); it returns the formatted date and time value.
Please provide me code or syntax for following:
1. Iterate cell value and format it before export.
2. Add additional row (title row) in excel sheet before export.
Check below demo with version 9.0.1
https://stackblitz.com/edit/paramquery-demo-chr1mszs?file=index.js
-
I've updated your code in the stackblitz to demonstrate how to
1) format cells before export and
2) add new rows
with pqgrid version 11.0.0
https://stackblitz.com/edit/paramquery-demo-lnk3r1wd?file=index.js,index.html
-
Thank you very much, now it is working properly.