0

Is there any way of exporting JQGrid data to Excel/PDF. I am using SQL server 2008 R2 as database and WCF service for HTTP Request/response. Client is written using JavaScript and AJAX calls are made to interact with SQL database through WCF service.

Will 'excelExport' function of jqgrid work?

Here is the code to collect Grid Data and store:

enter code here
function ExportExcel() {
    var mya=new Array();
    mya = $("#PrjBudgetGrid").getDataIDs();  // Get All IDs
    var data = $("#PrjBudgetGrid").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 (i = 0; i < mya.length; i++) {
        data = $("#PrjBudgetGrid").getRowData(mya[i]); // get each row
        for (j = 0; j < colNames.length; j++) {
            html = html + data[colNames[j]] + "\t"; // output each column as tab delimited
        }
        html = html + "\n";  // output each row with end of line

    }
    html=html+"\n";  // end of line at the end
}
Nitin Chhabra
  • 109
  • 2
  • 8

1 Answers1

1

You can use the code from the answer or even better from another more recent answer. The part of the code which export data to Excel you can easy change to WCF code. See here an example how to use Stream as the output of WCF method.

Community
  • 1
  • 1
Oleg
  • 220,925
  • 34
  • 403
  • 798
  • Thanks Oleg, I shall try your solution and revert back. – Nitin Chhabra Jan 16 '13 at 17:09
  • Oleg, how can i take JQGrid data to WCF service using AJAX call to export it to excel? – Nitin Chhabra Jan 18 '13 at 22:49
  • @user1724302: What is your current problem: calling of WCF service or returning binary Excel data as stream from the WCF service? – Oleg Jan 18 '13 at 22:56
  • Calling of WCF service with Grid Data in JSON format is the problem. – Nitin Chhabra Jan 18 '13 at 23:04
  • @user1724302: Sorry, but I so sense in the code which you posted. You get the data which are in the grid **from the server**. Why you need send any data back to the server instead of getting it *from the server code* directly from the database? – Oleg Jan 18 '13 at 23:18
  • @user1724302: What you need to do is just assigning of new value to `window.location`. I use export to Excel *from WCF or MVC* permanently. Mostly the code in the client side is just adding bottom which has something like `onClickButton: function () {window.location = '/MyExportToExcel/ExportSwPV';}`. In seldom cases I add additional parameters at the end of the URL. – Oleg Jan 18 '13 at 23:22
  • You are right Oleg, i should not send from Grid back to WCF service. The service can take same data from database. So, All i have to do is to build stream of data the way i want to see in excel when exported..Am i right? – Nitin Chhabra Jan 18 '13 at 23:23
  • @user1724302: Yes. If you use `{window.location = '/MyExportToExcel/MyExport';` and the URL `'/MyExportToExcel/MyExport';` return binary stream and set some additional HTTP headers (see my answer) then the web browser will automatically open the steam with Excel.exe. So the user click on the button and excel with the data will be opened on the client side. – Oleg Jan 18 '13 at 23:26
  • i am not using MVC, so, i have to make AJAX call to Excel Export method in WCF service and utilize Open XML SDK APIs to export data..Am i right? – Nitin Chhabra Jan 18 '13 at 23:26
  • Oleg, can i show the cell in currency format, how to do that? – Nitin Chhabra Jan 23 '13 at 17:08
  • @user1724302: you need just add `formatter: "currency"` to the column definition. See [the documentation](http://www.trirand.com/jqgridwiki/doku.php?id=wiki:predefined_formatter) – Oleg Jan 23 '13 at 18:51
  • Oleg, i am asking about when i export the data to excel how should i set cell format as currency, in your ExportToExcel example its only string, integer and date as dataTypes. I need general and currency cell formatting too for excel. Do you have any idea? – Nitin Chhabra Jan 23 '13 at 19:53
  • @user1724302: I shown in the demo from [the answer](http://stackoverflow.com/a/13957161/315935) how to create custom format and included examples of formatting of `Integer`, `Date`, `Text`, I shown how to choose custom font, background, alignment and rotating. I can't implement on any request one more feature. You should create new `CellFormats` which you need and use it. I can't do all your job. Sorry! – Oleg Jan 23 '13 at 20:37
  • Thats ok Oleg, i will do it. But how to add comments to excel cells. Do you have any idea about that? – Nitin Chhabra Jan 23 '13 at 21:22
  • @user1724302: It's not so simple: see [here](http://stackoverflow.com/a/3494858/315935). You can use Open XML Productivity Tool. – Oleg Jan 24 '13 at 06:29
  • Thanks Oleg for your reply, I have to do it anyway. Thanks for the leads. – Nitin Chhabra Jan 24 '13 at 16:26