Export jqgrid data into excel/pdf with currency format

Question

I need to export jqgrid data with currency format like $4,355.

My colmodel is below. I am calling function like exportGrid('griddtable',[1,2,3,4,5])

                name : 'totalSpend',
                index : 'totalSpend',
                align : 'right',
                sorttype : 'number',
                formatter : 'currency',
                formatoptions : {
                    prefix : '$',
                    thousandsSeparator : ','
                }

function exportGrid(table, filtered) {

var mya = new Array();
mya = $("#" + table).getDataIDs(); // Get All IDs
var data = $("#" + table).getRowData(mya[0]); // Get First row to get the
// labels
var colNames = new Array();
var ii = 0;
for ( var i in data) {
    colNames[ii++] = i;
} // capture col names
var html = "";
for ( var k = 0; k < colNames.length; k++) {
    if (filtered.indexOf(k) >= 0) {
            html = html + colNames[k] + "\t";
    }
}
html = html + "\n"; // Output header with end of line
for (i = 0; i < mya.length; i++) {
    data = $("#" + table).getRowData(mya[i]); // get each row
    for ( var j = 0; j < colNames.length; j++) {
        if (filtered.indexOf(j) >= 0) {
            html = html + data[colNames[j]] + "\t"; // output each Row as
            // tab delimited
        }
    }
    html = html + "\n"; // output each row with end of line
}
html = html + "\n"; // end of line at the end
var form = "<form name='csvexportform' action='exportData.do?method=excelExport' method='post'>";
form = form + "<input type='hidden' name='fileName' value='" + fileName
        + "'>";
form = form + "<input type='hidden' name='csvBuffer' value='" + html + "'>";
form = form + "</form><script>document.csvexportform.submit();</sc"
        + "ript>";
OpenWindow = window.open('', '');
OpenWindow.document.write(form);
OpenWindow.document.close();

   }

Any suggestions? Is there any possibility to get the row data with formatted value?

Where in the origin of the data which you need to export? The grid will be typically filled with the data from the backend (for example with the data from the database). So why one need to get the data from the HTML page and send it to the backend if the *server* **has** the data already? Is it not native to send just request request to the server to generate XLSX from the data already existing on the server. [The answer](http://stackoverflow.com/a/9349688/315935) and [this one](http://stackoverflow.com/a/13957161/315935) provide examples of such implementation. — Oleg, Sep 19 '13 at 15:43
@Oleg thanks for the reply. I also had column chooser to filter the columns. Is it possible to get the filtered columns and rows currently available in grid? — Vinoth Krishnan, Sep 19 '13 at 16:39
You can send to the server the list of non-hidden column names in the order in which jqGrid display it. You can send the sorting column and the sort order too. All other can do the server. — Oleg, Sep 19 '13 at 16:59
@Oleg Is there any useful demonstration/link for this scenario. I don't have any idea how to get the non-hidden columns. — Vinoth Krishnan, Sep 19 '13 at 17:48

score 0 · Accepted Answer · answered Oct 11 '13 at 19:08

Finally I got it. I used some tricks, but it is working great. I identified all money values by using their decimal places and formatted that value. Hence i got $ symbol on all monsy values. I knew it's not an optimal solution. But may be useful for someone.

var RE = new RegExp(/^\d*\.\d\d$/);           //Regular expression to find all 2 decimal values
for (i = 0; i < mya.length; i++) {
    data = $("#" + table).getRowData(mya[i]); // get each row
    for ( var j = 0; j < colNames.length; j++) {
        if (filtered.indexOf(j) >= 0) {
           if (RE.test(data[colNames[j]])) {       //If matches add $symbol
               html = html + "$" + data[colNames[j]] + "\t"; // output for money each Row as
                // tab delimited
           } else {
               html = html + data[colNames[j]] + "\t"; // output each Row as
                // tab delimited
           }
        }
    }
    html = html + "\n"; // output each row with end of line
}

Export jqgrid data into excel/pdf with currency format

1 Answers1

Linked