Menu
I have a jqGrid where I get data at once from server (java) in JSON format. I want the data in the jqGrid to be exported into Excel format.
Till now I saw this page which gives me an error in IE 'o.url is null or not an object' grid.import.js
Also I saw this demo where on the tool tip of export button it says Export To Excel but the file saved is in xml format.
So I would like any suggestions that can either transform my JSON string into excel using javascript or jquery plugin or using jqgrid's inbuilt feature.
My jqGrid
My jqGrid Code
grid = jQuery("#list2");
grid.jqGrid({
datastr : comparePatchData,
datatype: 'jsonstring',
colNames:['Name',starheader, header1, header2],
colModel:[
{name:'elementName',index:'elementName', width:90},
{name:'isPrasentinXml1',index:'isPrasentinXml1', width:100, align:'center', formatter: patchPresent},
{name:'isPrasentinXml2',index:'isPrasentinXml2', width:100, align:'center', formatter: patchPresent},
{name:'isPrasentinXml3',index:'isPrasentinXml3', width:100, align:'center', formatter: patchPresent}
],
pager : '#gridpager2',
rowNum:12,
scrollOffset:0,
height: 320,
autowidth:true,
viewrecords: true,
gridview: true,
loadonce:true,
jsonReader: {
repeatitems: false,
page: function() { return 1; },
root: "response"
},
subGrid: true,
// define the icons in subgrid
subGridOptions: {
"plusicon" : "ui-icon-triangle-1-e",
"minusicon" : "ui-icon-triangle-1-s",
"openicon" : "ui-icon-arrowreturn-1-e",
//expand all rows on load
"expandOnLoad" : false
},
subGridRowExpanded: function(subgrid_id, row_id) {
//console.info(subgrid_id+", "+row_id);
var subgrid_table_id, pager_id, iData = -1;
subgrid_table_id = subgrid_id+"_t";
//pager_id = "p_"+subgrid_table_id;
$("#"+subgrid_id).html("<table id='"+subgrid_table_id+"' style='overflow-y:auto' class='scroll'></table><div id='"+pager_id+"' class='scroll'></div>");
$.each(comparePatchData.response,function(i,item){
if(item.id === row_id) {
iData = i;
return false;
}
});
if (iData == -1) {
return; // no data for the subgrid
}
jQuery("#"+subgrid_table_id).jqGrid({
datastr : comparePatchData.response[iData],
datatype: 'jsonstring',
colNames: ['Name','Value1','Value2','Value3'],
colModel: [
{name:"name",index:"name",width:90},
{name:"firstValue",index:"firstValue",width:100},
{name:"secondValue",index:"secondValue",width:100},
{name:"thirdValue",index:"thirdValue",width:100}
],
rowNum:10,
//pager: pager_id,
sortname: 'name',
sortorder: "asc",
height: 'auto',
autowidth:true,
jsonReader: {
repeatitems: false,
//page: function() { return 1; },
root: "attribute"
}
});
jQuery("#"+subgrid_table_id).jqGrid('navGrid',{edit:false,add:false,del:false});
}
});
grid.jqGrid('navGrid','#gridpager2',{add:false,edit:false,del:false});
grid.jqGrid('navButtonAdd','#gridpager2',{
caption:"Export to Excel",
onClickButton : function () {
jQuery("#list2").excelExport();
}
});
Part of my Json
{
"response": [
{
"id": "1",
"elementName": "libgtop2-devel-2.14.4-3.el5",
"subCategory": "patch",
"isEqual": false,
"isPrasentinXml1": false,
"isPrasentinXml2": false,
"isPrasentinXml3": true,
"attribute": [
{
"name": "name",
"thirdValue": "libgtop2-devel-2.14.4-3.el5"
}
]
},
{
"id": "2",
"elementName": "ifd-egate-0.05-15",
"subCategory": "patch",
"isEqual": false,
"isPrasentinXml1": false,
"isPrasentinXml2": false,
"isPrasentinXml3": true,
"attribute": [
{
"name": "name",
"thirdValue": "ifd-egate-0.05-15"
}
]
},
{
"id": "3",
"elementName": "libXScrnSaver-devel-1.1.0-3.1",
"subCategory": "patch",
"isEqual": false,
"isPrasentinXml1": false,
"isPrasentinXml2": false,
"isPrasentinXml3": true,
"attribute": [
{
"name": "name",
"thirdValue": "libXScrnSaver-devel-1.1.0-3.1"
}
]
},
{
"id": "4",
"elementName": "kde-i18n-Chinese-Big5-3.5.4-1",
"subCategory": "patch",
"isEqual": false,
"isPrasentinXml1": true,
"isPrasentinXml2": true,
"isPrasentinXml3": true,
"attribute": [
{
"name": "name",
"firstValue": "kde-i18n-Chinese-Big5-3.5.4-1",
"secondValue": "kde-i18n-Chinese-Big5-3.5.4-1"
}
]
},
{
"id": "5",
"elementName": "cpio-2.6-20",
"subCategory": "patch",
"isEqual": false,
"isPrasentinXml1": true,
"isPrasentinXml2": true,
"isPrasentinXml3": true,
"attribute": [
{
"name": "name",
"firstValue": "cpio-2.6-20",
"secondValue": "cpio-2.6-20",
"thirdValue": "cpio-2.6-20"
}
]
},
{
"id": "6",
"elementName": "grep-2.5.1-54.2.el5",
"subCategory": "patch",
"isEqual": false,
"isPrasentinXml1": true,
"isPrasentinXml2": true,
"isPrasentinXml3": true,
"attribute": [
{
"name": "name",
"firstValue": "grep-2.5.1-54.2.el5",
"secondValue": "grep-2.5.1-54.2.el5",
"thirdValue": "grep-2.5.1-54.2.el5"
}
]
},
{
"id": "7",
"elementName": "avahi-compat-libdns_sd-0.6.16-1.el5",
"subCategory": "patch",
"isEqual": false,
"isPrasentinXml1": true,
"isPrasentinXml2": true,
"isPrasentinXml3": true,
"attribute": [
{
"name": "name",
"firstValue": "avahi-compat-libdns_sd-0.6.16-1.el5",
"secondValue": "avahi-compat-libdns_sd-0.6.16-1.el5",
"thirdValue": "avahi-compat-libdns_sd-0.6.16-1.el5"
}
]
},
{
"id": "8",
"elementName": "gpm-devel-1.20.1-74.1",
"subCategory": "patch",
"isEqual": false,
"isPrasentinXml1": true,
"isPrasentinXml2": true,
"isPrasentinXml3": true,
"attribute": [
{
"name": "name",
"firstValue": "gpm-devel-1.20.1-74.1",
"secondValue": "gpm-devel-1.20.1-74.1",
"thirdValue": "gpm-devel-1.20.1-74.1"
}
]
},
{
"id": "9",
"elementName": "esc-1.0.0-39.el5",
"subCategory": "patch",
"isEqual": false,
"isPrasentinXml1": false,
"isPrasentinXml2": false,
"isPrasentinXml3": true,
"attribute": [
{
"name": "name",
"thirdValue": "esc-1.0.0-39.el5"
}
]
},
{
"id": "10",
"elementName": "kde-i18n-Spanish-3.5.4-1",
"subCategory": "patch",
"isEqual": false,
"isPrasentinXml1": true,
"isPrasentinXml2": true,
"isPrasentinXml3": true,
"attribute": [
{
"name": "name",
"firstValue": "kde-i18n-Spanish-3.5.4-1",
"secondValue": "kde-i18n-Spanish-3.5.4-1"
}
]
}
]
}
495
To enable it, you can trigger export by invoking the exportToExcel, exportToPdf and exportToCsv methods from the client API of the grid. Additionally, you have the option to customize the rows/columns and cells of the exported file by intercepting the export event.
jqGrid is an Ajax-enabled JavaScript control that provides solutions for representing and manipulating tabular data on the web.
The Grid provides options for exporting its data to Excel. To enable the Excel export, import the ExcelModule and add the kendo-grid-excel component to the Kendo UI Grid for Angular. To initiate the export, use the kendoGridExcelCommand directive or the saveAsExcel method.
You don't have to export a file using the Excel format in order to get the data into Excel. It is generally much easier to export to CSV. CSV files should be associated with Excel by default, so it should have the Excel icon by it and everything. XML would work the same way, I think, but the CSV format is much lighter, and does the same job in this case. Converting JSON to CSV is simple:
var response = JSON.parse(responseJSON).response;
var csv = arrayToCSV(response);
function arrayToCSV(arr) {
var columnNames = [];
var rows = [];
for (var i=0, len=arr.length; i<len; i++) {
// Each obj represents a row in the table
var obj = arr[i];
// row will collect data from obj
var row = [];
for (var key in obj) {
// Don't iterate through prototype stuff
if (!obj.hasOwnProperty(key)) continue;
// Collect the column names only once
if (i === 0) columnNames.push(prepareValueForCSV(key));
// Collect the data
row.push(prepareValueForCSV(obj[key]));
}
// Push each row to the main collection as csv string
rows.push(row.join(','));
}
// Put the columnNames at the beginning of all the rows
rows.unshift(columnNames.join(','));
// Return the csv string
return rows.join('\n');
}
// This function allows us to have commas, line breaks, and double
// quotes in our value without breaking CSV format.
function prepareValueForCSV(val) {
val = '' + val;
// Escape quotes to avoid ending the value prematurely.
val = val.replace(/"/g, '""');
return '"' + val + '"';
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
