jQuery UI Widgets Forums Grid Export corrupted xls when using aggregates with aggregates renderer

This topic contains 3 replies, has 2 voices, and was last updated by  Dimitar 8 years, 8 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author

  • mcseemcher
    Participant

    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?


    Dimitar
    Participant

    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,
    Dimitar

    jQWidgets team
    http://www.jqwidgets.com/


    mcseemcher
    Participant

    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…


    Dimitar
    Participant

    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,
    Dimitar

    jQWidgets team
    http://www.jqwidgets.com/

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.