JQuery Excel export issue

Question

I have some data from JQGrid that should be exported to excel. So, we have written a java servlet to write the data to excel and send it back. From the client side we are using an AJAX JSONP request by sending JSON data. I am able to hit the servlet and servlet sending the created excel back to the client. But i am not able to see excel or any kind of output from client side.

When i use the Fiddler and observed the http calls, i found that application received the result. but still it is not showing the result.

Here is my result header, that i have received.

HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Disposition: attachment; filename=PistonData.xls
Content-Type: application/vnd.ms-excel
Content-Length: 6144
Date: Tue, 27 Mar 2012 08:49:04 GMT

How to open this result as Excel using JQuery? Can somebody please suggest me a way to fix this issue.

Update #1 Forgot to include request

$.ajax({
        type: "POST",
        dataType: "jsonp",
        contentType:'application/vnd.ms-excel',
        url: "http://devmachine:9010/axis/SPSServlet",
        data: param,
        success: function (dataToSend) {
            alert(dataToSend);
        }
    });

Update #2 As per Oleg's suggestions i worked out a solution for this problem.

Here is my code:

    <form id="frmExcelExport" style="display:none;">
       <input type=hidden id="partId" name="partId" />
       <input type=hidden id="columnNames" name="columnNames" />
       <input type=hidden id="data" name="data" />
    </form> 

$('#columnNames').val(colModStr);
$('#partId').val(currentPartID);
$('#data').val(dataStr);

var urlForExport = "http://devmachine:9010/axis/SPSServlet";        
$('#frmExcelExport').attr("method", "post");
$('#frmExcelExport').attr("action", urlForExport); 
$('#frmExcelExport').submit();

and it is working very good. Thanks a bunch to Oleg!!!!

score 1 · Accepted Answer · edited May 23 '17 at 11:48

1

I think that you can't open Excel if you use HTTP POST. The way which will work is to use HTTP GET and encode parameters which you need in the URL:

window.location = "http://devmachine:9010/axis/StdPartSearchServlet?" +
    $.param({someParamName: "someValue", anotherParam: 123});

In the way the web browser will opens the returned data as PistonData.xls specified in Content-Disposition header with respect of the application registered for application/vnd.ms-excel (see Content-Type header). See the answer for more details.

If you need to prevent uncontrolled caching of the XLS data returned from the server I would recommend you to set additional HTTP header "Cache-Control: max-age=0" or better "Cache-Control: private, max-age=0" to prevent caching without re-validation on the HTTP proxy. See here, here for additional information.

edited May 23 '17 at 11:48

Community

1
1

answered Mar 27 '12 at 10:56

Oleg

220,925
34
403
798

but with get method, we won't be able to send much records to the server. right? In worst case, i need to send to 500 records to service. – Dinesh Mar 27 '12 at 11:22
@Dinesh: You need to send binary stream of data. Typically one set `Content-Encoding` to `UTF-8` and send the `PistonData.xls` data encoded in `UTF-8`. So I don't understand what records you mean. – Oleg Mar 27 '12 at 11:32
@Dinesh: I am not sure that it's really help you, but in [the answer](http://stackoverflow.com/a/9349688/315935) I posted full C# code which uses Open XML SDK 2.0, generate xlsx on the server and returned like I described in my answer. – Oleg Mar 27 '12 at 11:35
from client to server, i need to send some records. for some requests i might need to send 2 records, for some requests i might need to send 5000 records. Can we hit excel export servlet with this many records using GET? – Dinesh Mar 28 '12 at 03:49
@Dinesh: I doesn't matter which HTTP command you use: GET or POST. The size of response will be the same. The most difference is in the HTTP specification. Web browser can cache the GET response and it can save the response in the file PistonData.xls and open the results with respect of another application like Excel.exe. The data returned by POST request can't be used to start new external process. I explained this in [the answer](http://stackoverflow.com/a/5759889/315935) which I referenced in my answer. – Oleg Mar 28 '12 at 05:15
The problem is with REQUEST. We can't send 5000 records to server using GET request. right? – Dinesh Mar 28 '12 at 05:27
@Dinesh: Is the 5000 records are the data typed by the user and which are not still saved on the server? Is the data not already on the server? You don't need send the data one more time to the server. – Oleg Mar 28 '12 at 05:45
we have some filters applied on the client side. so, we are sending the filtered data back to the server to export to excel. – Dinesh Mar 28 '12 at 06:53
@Dinesh: Sending of the filter from `postData.filters` takes not so much size as 5000 records of data. In any way like I wrote you before the usage of Ajax will not allow you to open the Excel with exported data from web browser. – Oleg Mar 28 '12 at 07:20
Simple form submit with POST will work? I am now trying it as my last option. – Dinesh Mar 28 '12 at 08:09
1

This could probably work. You can submit form in different target window. See [here](http://stackoverflow.com/a/2064957/315935) for example for the question and the answer. – Oleg Mar 28 '12 at 08:24

score 0 · Answer 2 · answered Mar 27 '12 at 09:19

0

If you are trying to use jquery to download the file, maybe you should look here:

http://www.filamentgroup.com/lab/jquery_plugin_for_requesting_ajax_like_file_downloads 1

This has a working snippet to do what you want.

answered Mar 27 '12 at 09:19

Steve

2,988
2
30
47

I wanted to export JQGrid contents to Excel. – Dinesh Mar 27 '12 at 09:25
If that is the case, the code snippet mentioned above would more than likely be what you are looking for. – Steve Mar 27 '12 at 09:27
It should be. But it is not opening up the excel or at least it not showing signs of receiving result. – Dinesh Mar 27 '12 at 09:51
1

Here's a thread saying you cannot use POST with JSONP http://stackoverflow.com/q/2699277/1211981 – Eric Bridger Mar 27 '12 at 10:44

JQuery Excel export issue

2 Answers2

Linked