Export to excel has empty cells

[njaParticipant]

Hi there,
I have a requirement to export a grid to excel and have to manipulate two date columns for the output. I created two “fake” date columns which I use the ‘setcellvalue’ to populate and hide the real date columns, then export to excel. Works great for one grid, but not the other. These two grids are very similar (one handles deposits and the other reversals).

In the second grid when I export the grid to excel the fake date columns are empty (though they are filled onscreen).

This is the code I am using for the ‘setcellvalue’ and the code for the gird after. Obviously, please let me know if you see anything obvious wrong.

Also, does the ‘setcellvalue’ method return any value when run for testing?

thanks,
nja

            function setFakeDates(){

                // ============ set Fake Deposit and Posting Dates ===================
                // gets all rows loaded from the data source.
                var rows = $('#jqxgrid').jqxGrid('getdisplayrows');

                if (rows.length > 0 ){
                    // gets the first displayed row.
                    var rowData = rows[0];

                    // gets the ID of the first displayed row.
                    var rowID = rowData.uid;

                    var documentdate = $('#jqxgrid').jqxGrid('getcellvalue', rowID, "DocumentDate");
                    var postingdate = $('#jqxgrid').jqxGrid('getcellvalue', rowID, "PostingDate");
                    documentdate = $.jqx.dataFormat.formatdate(documentdate, 'MM/dd/yy');
                    postingdate = $.jqx.dataFormat.formatdate(postingdate, 'MM/dd/yy');

                    // set dates for each Summary line
                    var summaryLineID =  @ViewBag.DetailSummaryID;
                    var x = 0;
                    $.each(rows, function() {
                        var rowData = rows[x];
                        if (rowData.vraSourceID  == summaryLineID) {
                            $("#jqxgrid").jqxGrid('setcellvalue', rowData.uid, "FakeDocumentDate", documentdate);
                            $("#jqxgrid").jqxGrid('setcellvalue', rowData.uid, "FakePostingDate", postingdate);
                        }

                        x++;
                    });

                }
            }

            // initialize jqxGrid
            $("#jqxgrid").jqxGrid(
            {
                source: dataAdapter,
                width: '100%',
                height: '98%',
                sortable: true,
                filterable: true,
                autoshowloadelement: true,
                showfiltermenuitems: true,
                autoshowfiltericon: true,
                showsortmenuitems: false,
                columnsresize: true,
                theme: theme,
                altrows: true,
                enabletooltips: true,
                enableellipsis: true,
                showaggregates: true,
                showaggregates: true,
                showstatusbar: true,
                statusbarheight: 27,
                selectionmode: 'singlerow',
                editmode: 'click',
                editable: true,
                handlekeyboardnavigation: function (event) {
                    var key = event.charCode ? event.charCode : event.keyCode ? event.keyCode : 0;
                    if (key == 37) {
                        // get our current position....
                        var position = $('#jqxgrid').jqxGrid('scrollposition');
                        var left = position.left;
                        var top = position.top;

                        //param Number. Top position.
                        //param Number. Left position.
                        $('#jqxgrid').jqxGrid('scrolloffset', top, (left - 200));
                        return true;
                    }
                    else if (key == 39) {
                        // get our current position....
                        var position = $('#jqxgrid').jqxGrid('scrollposition');
                        var left = position.left;
                        var top = position.top;

                        //param Number. Top position.
                        //param Number. Left position.
                        $('#jqxgrid').jqxGrid('scrolloffset', top, (left + 200));
                        return true;
                    }
                    else if (key == 112) {
                        //F1 key pressed.. open nav/edit tips

                        // show modal dialog box
                        $('#myNavEditTipModal').modal({ keyboard: false });

                        return true;

                    }
                },
                columns: [
                     { text: 'VRA ID', datafield: 'DepositRecordId', width: 60, hidden: false, editable: false, cellclassname: cellclass,},
                     { text: 'vraExportDate', datafield: 'vraExportDate', width: 110, hidden: true, editable: false, },
                     { text: 'vraSentDate', datafield: 'vraSentDate', width: 110, hidden: true, editable: false, },
                     { text: 'vraCreatedUserID', datafield: 'vraCreatedUserID', width: 110, hidden: true, editable: false, },
                     { text: 'vraCreatedBy', datafield: 'vraCreatedBy', width: 110, hidden: true, editable: false, },
                     { text: 'vraCreatedDate', datafield: 'vraCreatedDate', width: 110, hidden: true, editable: false, },
                     { text: 'DefaultSortOrder', datafield: 'DefaultSortOrder', width: 50, cellclassname: cellclass, hidden: true, editable: false, },
                     { text: 'Rec Type', datafield: 'vraRecordType', width: 80, cellclassname: cellclass, hidden: false, editable: false, },
                     { text: 'vraSourceID (from)', datafield: 'vraSourceID', width: 50, cellclassname: cellclass, hidden: true, editable: false, },
                     { text: 'From', datafield: 'vraSourceName', width: 60, cellclassname: cellclass, editable: false, },
                     { text: 'ID #', datafield: 'vraUniqueId', width: 70, cellclassname: cellclass, hidden: false, editable: false,
                         rendered: function (element) {
                             $(element).jqxTooltip({ position: 'mouse', content: "This is the Genesis Receipt Number or Cubs Record ID." });
                         }
                     },
                     { text: 'StatusID', datafield: 'vraStatusID', width: 50, cellclassname: cellclass, hidden: true, editable: true, },
                     { text: 'Status', datafield: 'vraStatusCode', width: 50, cellclassname: cellclass, editable: true,
                         cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue) {
                             return newvalue.toUpperCase();
                         }
                     },
                     {
                         text: 'Note', datafield: 'vraNote', columntype: 'button', hidden: false, width: 80, cellclassname: cellclass, cellsrenderer: function () {
                             return "Note";
                         }, buttonclick: function (row) {
                             // open the popup window when the user clicks a button.
                             editrow = row;
                             var offset = $("#jqxgrid").offset();
                             $("#popupWindow").jqxWindow({ position: { x: parseInt(offset.left) + 60, y: parseInt(offset.top) + 60 } });
                             // get the clicked row's data and initialize the input fields.
                             var dataRecord = $("#jqxgrid").jqxGrid('getrowdata', editrow);
                             $("#vraNote").val(dataRecord.vraNote);

                             // show the popup window.
                             $("#popupWindow").jqxWindow('open');

                         }
                     },
                     { text: 'Rec Id', datafield: 'ExportRecordID', width: 70, aggregates: ['count'], cellclassname: cellclass, editable: false, pinned: true},
                     { text: 'Bus Area', datafield: 'BusinessArea', width: 70, cellclassname: cellclass, editable: false,},
                     { text: 'Real Doc Date', datafield: 'DocumentDate', width: 75, cellsformat: 'MM/dd/yy', cellclassname: cellclass, hidden: true, editable: false,},
                     { text: 'Doc Date', datafield: 'FakeDocumentDate', width: 75, cellsformat: 'MM/dd/yy', cellclassname: cellclass, editable: false,},
                     { text: 'Real Post Date', datafield: 'PostingDate', width: 75, cellsformat: 'MM/dd/yy', cellclassname: cellclass, hidden: true, editable: false,},
                     { text: 'Post Date', datafield: 'FakePostingDate', width: 75,  cellsformat: 'MM/dd/yy', cellclassname: cellclass, editable: false,},
                     { text: 'Doc Type', datafield: 'DocumentType', width: 70, cellclassname: cellclass, editable: true,},
                     { text: 'Reference', datafield: 'ReferenceField', width: 90, cellclassname: cellclass, editable: true,},
                     { text: 'Doc Header', datafield: 'DocumentHeaderText', width: 175, cellclassname: cellclass, editable: true,},
                     { text: 'Post Key', datafield: 'PostingKey', width: 60, cellclassname: cellclass, editable: false,},
                     { text: 'Account', datafield: 'Glaccount', width: 80, cellclassname: cellclass, editable: false, },
                     { text: 'CC', datafield: 'CostCenter', width: 30, cellclassname: cellclass, editable: false, },
                     { text: 'WBS', datafield: 'WBS', width: 40, cellclassname: cellclass, editable: false, },
                     { text: 'Order', datafield: 'Order', width: 50, cellclassname: cellclass, editable: false, },
                     { text: 'FA', datafield: 'FunctionalArea', width: 30, cellclassname: cellclass, editable: false, },
                     { text: 'Fund', datafield: 'Fund', width: 80, cellclassname: cellclass, editable: false, },
                     {
                         text: 'Real Amount', datafield: 'Amount', width: 90, cellsformat: 'f2', cellsalign: 'right', hidden: true, editable: false,
                         aggregates: [{
                             'Total': function (aggregatedValue, currentValue, column, record) {
                                 @*// adding our non template rows
                                 var rowtest =  record['PostingKey'];
                                 var rowSource =  record['vraSourceID']
                                 if (( rowSource == @ViewBag.DetailSourceID) && ( rowtest == '40')){
                                     aggregatedValue = aggregatedValue + currentValue;

                                 }
                                 return aggregatedValue;*@
                                 // adding our non template rows
                                 var rowtest =  record['PostingKey'];
                                 var rowSource =  record['vraSourceID']
                                 var realValue = record['Amount']*-1;
                                 if (( rowSource == 4) && ( rowtest == '40')){
                                     //aggregatedValue = aggregatedValue + currentValue;
                                     aggregatedValue = aggregatedValue + realValue;

                                 }
                                 return aggregatedValue;

                             }
                         }],
                         aggregatesrenderer: function (aggregates) {
                             var renderstring = aggregates["Total"];
                             return '<span class="allViewSummary">' + renderstring + '</span>';
                         }, cellclassname: cellclass,
                     },
                         {
                             text: 'Amount', datafield: 'FakeAmount', width: 90, cellsformat: 'f2', cellsalign: 'right', editable: false,
                             aggregates: [{
                                 'Total': function (aggregatedValue, currentValue, column, record) {
                                     // adding our non template rows
                                     var rowtest =  record['PostingKey'];
                                     var rowSource =  record['vraSourceID']
                                     var realValue = record['Amount']*-1;
                                     if (( rowSource == 4) && ( rowtest == '40')){
                                         //aggregatedValue = aggregatedValue + currentValue;
                                         aggregatedValue = aggregatedValue + realValue;

                                     }
                                     return aggregatedValue;

                                 }
                             }],
                             aggregatesrenderer: function (aggregates) {
                                 var renderstring = aggregates["Total"];
                                 return '<span class="allViewSummary">' + renderstring + '</span>';
                             }, cellclassname: cellclass,
                         },
                     { text: 'Line Item Text', datafield: 'LineItemText', width: 250, cellclassname: cellclass, editable: true,},
                     { text: 'Assignment', datafield: 'AssignmentField', width: 120, cellclassname: cellclass, editable: true,},
                     { text: 'Pay Method', datafield: 'PaymentMethod', width: 90, cellclassname: cellclass,  editable: false, },
                     { text: 'Inv Date', datafield: 'InvoiceDate', width: 75, cellsformat: 'MM/dd/yy', cellclassname: cellclass, editable: false,},
                     { text: 'W/H Base', datafield: 'WithholdingBase', width: 90, cellclassname: cellclass,  editable: false, },
                     { text: 'Pay Supp', datafield: 'PaymentSupplement', width: 90,  cellclassname: cellclass,editable: false, },
                     { text: 'Tax Code', datafield: 'TaxCode', width: 90, cellclassname: cellclass, editable: false,},
                ],
            });

[HristoParticipant]

Hello nja,

We cannot reproduce this issue from that code.
Could you provide us one example (in https://www.jseditor.io/ or http://jsfiddle.net/) that demonstrate this.

Best Regards,
Hristo Hristov

jQWidgets team
http://www.jqwidgets.com

[njaParticipant]

Hi,
Thanks. That is good to know, points to something else on the page causing it to act different from the first grid.

I am traveling and will try to get you sample code later in the week.

Thanks,
nja

[Author]

Posts