jQuery UI Widgets › Forums › Grid › Export corrupted xls when using aggregates with aggregates renderer
Tagged: aggregates, aggregatesrenderer, angular grid, corrupted, custom aggregates, excel, export, grid, jquery grid, jqxgrid, XLS
This topic contains 3 replies, has 2 voices, and was last updated by Dimitar 8 years, 8 months ago.
-
Author
-
Hello.
I have a column with aggregates property and aggregatesrenderer defined.
{ text: 'Process Name', columntype: 'string', datafield: 'WORKTITLE', width: "15%", aggregates: [{ 'Report Date<br>': function (aggregatedValue, currentValue, column, record) { var d = new Date(); return d.ddmmyyyyhhmm(); } }], aggregatesrenderer: function (aggregates) { var renderstring = ""; var d = new Date(); renderstring += '<div style="position: relative; margin: 4px; overflow: hidden;">Report Date<br>' + d.ddmmyyyyhhmm() + '</div>'; return renderstring; } }
But when I export grid to excel with this code
$("#jqxgrid").jqxGrid('exportdata', 'xls', 'jqxGrid');
it exports corrupted xls file.
I have to use aggregatesrenderer to get rid of colons. But unfortunately export does not look what renderer rendered and it exports just the aggregates column. But when those two defined together broken xls gets exported.
What do I do?
Hello mcseemcher,
We tested exporting a grid with custom aggregates and aggregatesrenderer. The resulting files are not corrupted. We could not, however, test your particular scenario, because of the use of the method .ddmmyyyyhhmm(). If you wish, please share a complete jsEditor example and we will test it on our side.
Best Regards,
DimitarjQWidgets team
http://www.jqwidgets.com/It’s not about “.ddmmyyyyhhmm()” method. When I put
return 'just a text';
and problem remains the same.It would be hard to share all project. All I can do is post full definition of grid:
var grid = []; var source1 = { datatype: 'json', localdata: grid, datafields: [ {name: 'TASKTITLE', type: 'string', map: 'TASKTITLE'}, {name: 'DATEREADY', type: 'date', map: 'DATEREADY'}, {name: 'STATUS', type: 'string', map: 'STATUS'}, {name: 'PERIOD', type: 'string', map: 'PERIOD'}, {name: 'PERFORMER', type: 'string', map: 'PERFORMER'}, {name: 'DPNAME', type: 'string', map: 'DPNAME'}, {name: 'WORKTITLE', type: 'string', map: 'WORKTITLE'}, {name: 'ITERNUM', type: 'string', map: 'ITERNUM'}, ] }; var dataAdapter = new $.jqx.dataAdapter(source1, { loadComplete: function () { console.log("loadcomplete fired!") } } ); var cellsrenderer = function (row, columnfield, value, defaulthtml, columnproperties, rowdata) { if (value < 20) { return '<span style="margin: 4px; float: ' + columnproperties.cellsalign + '; color: #ff0000;">' + value + '</span>'; } else { return '<span style="margin: 4px; float: ' + columnproperties.cellsalign + '; color: #008000;">' + value + '</span>'; } } // initialize jqxGrid $("#jqxgrid").jqxGrid( { width: "100%", height: "100%", source: dataAdapter, scrollmode: 'logical', pageable: true, //autoheight: true, sortable: true, altrows: true, //enabletooltips: false, //editable: false, autorowheight: true, columnsresize: true, pagesize: 50, //selectionmode: 'checkbox', pagermode: 'simple', showfilterrow: true, filterable: true, showaggregates: true, showstatusbar: true, //statusbarheight: 25, renderstatusbar: function (statusbar) { // appends buttons to the status bar. var container = $("<div style='overflow: hidden; position: relative; margin: 5px;'></div>"); var exportButton = $("<button>Export</button>"); //container.append(infotext); container.append(exportButton); statusbar.append(container); exportButton.jqxButton({ width: 60, height: 20 }); // add new row. exportButton.click(function (event) { $("#jqxgrid").jqxGrid('exportdata', 'xls', 'jqxGrid', false); }); }, columns: [ { text: '№ п/п', sortable: false, filterable: false, editable: false, groupable: false, draggable: false, resizable: false, datafield: '', columntype: 'number', width: 50, width:"3%", cellsrenderer: function (row, column, value) { return "<div style='margin:4px;'>" + (value + 1) + "</div>"; } }, { text: 'Задача', datafield: 'TASKTITLE', columntype: 'string', width: "15%" }, { text: 'Дата создания', datafield: 'DATEREADY', cellsformat: 'dd.MM.yyyy HH:mm', filtertype: 'range', width: "7%" }, { text: 'Статус', columntype: 'string', datafield: 'STATUS', width: "10%" }, { text: 'Время в обработке', columntype: 'string', datafield: 'PERIOD', width: "13%" }, { text: 'Исполнитель', columntype: 'string', datafield: 'PERFORMER', width: "7%" }, { text: 'Досье', columntype: 'string', datafield: 'DPNAME', width: "20%" }, { text: 'Процесс', columntype: 'string', datafield: 'WORKTITLE', width: "15%", aggregates: [{ 'Дата формирования': function (aggregatedValue, currentValue, column, record) { var d = new Date(); return d.ddmmyyyyhhmm(); } }], aggregatesrenderer: function (aggregates) { var renderstring = ""; var d = new Date(); renderstring += '<div style="position: relative; margin: 4px; overflow: hidden;">Дата формирования<br>' + d.ddmmyyyyhhmm() + '</div>'; return renderstring; } }, { text: 'Итерация', columntype: 'string', datafield: 'ITERNUM', width: "10%", aggregates: [{ 'Всего записей': function (aggregatedValue, currentValue, column, record) { return $("#jqxgrid").jqxGrid('getRows').length } }], aggregatesrenderer: function (aggregates) { var renderstring = ""; $.each(aggregates, function (key, value) { renderstring += '<div style="position: relative; margin: 4px; overflow: hidden;">Всего записей<br>' + $("#jqxgrid").jqxGrid('getRows').length + '</div>'; }); return renderstring; } } ] });
For the record, that is ddmmyyhhmm method definition:
Date.prototype.ddmmyyyyhhmm = function() { var yyyy = this.getFullYear().toString(); var mm = (this.getMonth()+1).toString(); // getMonth() is zero-based var dd = this.getDate().toString(); var hh = this.getHours().toString(); var min = this.getMinutes().toString(); return (dd[1]?dd:"0"+dd[0]) +"."+ (mm[1]?mm:"0"+mm[0]) +"."+ yyyy + " " + hh + ":" + min; // padding };
I hope this helps…
Hello mcseemcher,
We tried the following example based on yours:
<!DOCTYPE html> <html lang="en"> <head> <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" /> <script type="text/javascript" src="../../scripts/jquery-1.11.1.min.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxdata.export.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.aggregates.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.export.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxcheckbox.js"></script> <script type="text/javascript" src="../../scripts/demos.js"></script> <script type="text/javascript" src="generatedata.js"></script> <script type="text/javascript"> $(document).ready(function () { // prepare the data var data = generatedata(200); var source = { localdata: data, datatype: "array", datafields: [ { name: 'firstname', type: 'string' }, { name: 'lastname', type: 'string' }, { name: 'productname', type: 'string' }, { name: 'quantity', type: 'number' }, { name: 'price', type: 'number' } ], updaterow: function (rowid, rowdata, commit) { // synchronize with the server - send update command commit(true); } }; var dataAdapter = new $.jqx.dataAdapter(source); Date.prototype.ddmmyyyyhhmm = function () { var yyyy = this.getFullYear().toString(); var mm = (this.getMonth() + 1).toString(); // getMonth() is zero-based var dd = this.getDate().toString(); var hh = this.getHours().toString(); var min = this.getMinutes().toString(); return (dd[1] ? dd : "0" + dd[0]) + "." + (mm[1] ? mm : "0" + mm[0]) + "." + yyyy + " " + hh + ":" + min; // padding }; // initialize jqxGrid $("#jqxgrid").jqxGrid( { width: 850, source: dataAdapter, showstatusbar: true, statusbarheight: 50, altrows: true, showaggregates: true, columns: [ { text: 'First Name', columntype: 'textbox', datafield: 'firstname', width: 150 }, { text: 'Last Name', datafield: 'lastname', columntype: 'textbox', width: 150 }, { text: 'Product', datafield: 'productname', width: 200 }, { text: 'Quantity', datafield: 'quantity', width: 100, cellsalign: 'right', cellsformat: 'n2' }, { text: 'Price', datafield: 'price', cellsalign: 'right', cellsformat: 'c2', aggregates: [{ 'Дата формирования': function (aggregatedValue, currentValue, column, record) { var d = new Date(); return d.ddmmyyyyhhmm(); } }], aggregatesrenderer: function (aggregates) { var renderstring = ""; var d = new Date(); renderstring += '<div style="position: relative; margin: 4px; overflow: hidden;">Дата формирования<br>' + d.ddmmyyyyhhmm() + '</div>'; return renderstring; } } ] }); $('#exportGrid').click(function () { $("#jqxgrid").jqxGrid('exportdata', 'xls', 'jqxGrid', false); }); }); </script> </head> <body class='default'> <div id='jqxWidget'> <div id="jqxgrid"></div> </div> <br /> <button id="exportGrid">Export grid</button> </body> </html>
Here is a screenshot of the resulting .xls file: http://postimg.org/image/cn7dj9dff/. Showing “Дата формирования: NaN” is expected, because the string rendered by aggregatesrenderer is not exported, only the base value returned by the aggregates callback function (which is not a number, hence NaN).
Best Regards,
DimitarjQWidgets team
http://www.jqwidgets.com/ -
AuthorPosts
You must be logged in to reply to this topic.