how to add openwith and save as dialogue box while exporting to .excel on button click

Question

I am exporting data displayed in jqgrid into .excel file by clicking button . Here is my code for export to excel button click..

 $('#excel').click(function(){
            var fromdate=$('#fromdate').val();
            var todate=$('#todate').val();
            if(fromdate && todate)
            {
                var URL='excel.jsp?fromdate='+$('#fromdate').val()+'&todate='+$('#todate').val();
                $.ajax({
                   url:URL,
                   type:'GET',
                   success:function(data){
                       alert('Exported To Excel');
                   }

                });
            }
        });

Now this button will direct to excel.jsp page .Below is my excel.jsp page code which is directly giving path to save the generated .excel file in local disk D://.Now Asper my need when the user click on export to excel button a openwith and save as dialogue box should popup which gives user ability to give desired name and save to desired position..

excel.jsp

    String datum1 = request.getParameter("fromdate");
    String datum2 = request.getParameter("todate");
    SimpleDateFormat sdfSource = new SimpleDateFormat("dd-MM-yyyy");
    Date date = sdfSource.parse(datum1);
    Date date2 = sdfSource.parse(datum2);
    SimpleDateFormat sdfDestination = new SimpleDateFormat("yyyy-MM-dd");
    datum1 = sdfDestination.format(date);
    System.out.println(datum1);
    datum2 = sdfDestination.format(date2);
    System.out.println(datum2);




    String filename = "d:/excel.xls";
    HSSFWorkbook hwb = new HSSFWorkbook();
    HSSFSheet sheet = hwb.createSheet("CallBillingSystem");

    HSSFRow rowhead = sheet.createRow((short) 0);
    rowhead.createCell((short) 0).setCellValue("calldate");
    rowhead.createCell((short) 1).setCellValue("src");
    rowhead.createCell((short) 2).setCellValue("dst");
    rowhead.createCell((short) 3).setCellValue("dstchannel");
    rowhead.createCell((short) 4).setCellValue("lastapp");
    rowhead.createCell((short) 5).setCellValue("duration");
    rowhead.createCell((short) 6).setCellValue("disposition");
    rowhead.createCell((short) 7).setCellValue("amaflags");
    rowhead.createCell((short) 8).setCellValue("cdrcost");


    String strQuery = "";
    ResultSet rs = null;

    conexion conexiondb = new conexion();
    conexiondb.Conectar();

    strQuery = "SELECT * FROM cdrcost where date(calldate) between '" + datum1 + "' and '" + datum2 + "'";



    rs = conexiondb.Consulta(strQuery);
    int i = 1;
    while (rs.next()) {
        HSSFRow row = sheet.createRow((short) i);
        row.createCell((short) 0).setCellValue(rs.getString("calldate"));
        row.createCell((short) 1).setCellValue(rs.getString("src"));
        row.createCell((short) 2).setCellValue(rs.getString("dst"));
        row.createCell((short) 3).setCellValue(rs.getString("dstchannel"));
        row.createCell((short) 4).setCellValue(rs.getString("lastapp"));
        row.createCell((short) 5).setCellValue(rs.getString("duration"));
        row.createCell((short) 6).setCellValue(rs.getString("disposition"));
        row.createCell((short) 7).setCellValue(rs.getString("amaflags"));
        row.createCell((short) 8).setCellValue(rs.getString("cdrcost"));
        i++;
    }
    FileOutputStream fileOut = new FileOutputStream(filename);
    hwb.write(fileOut);
    fileOut.close();
    System.out.println("Your excel file has been generated!");

} catch (Exception ex) {
    System.out.println(ex);

}

I don't know the java implementation, so I'm not putting this as an answer, but there should be no need to write anything to the filesystem at all; can send the appropriate headers and output the content directly in the response and the user will be prompted to save it. You can see the appropriate headers and get the idea from [this answer in PHP](http://stackoverflow.com/questions/4345322/how-can-i-allow-a-user-to-download-a-file-which-is-stored-outside-of-the-webroot). — El Yobo, Oct 30 '12 at 05:17
Yobo Question related to jsp and servlets not related to PHP. — sunleo, Oct 30 '12 at 05:25

score 2 · Accepted Answer · edited May 23 '17 at 12:31

You should set several HTTP headers and write content of Excel file directly to the OutputStream of HttpServletResponse.

response.setHeader("Content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Transfer-Encoding", "binary");
response.setHeader("Content-Length", String.valueOf(fileSize));
response.addHeader("Content-Disposition", String.format("attachment; filename=%s", fileName));

"Content-Type" header tells what type of stream you are sending to browser. Mime type depends on format of your Excel file. See What is correct content-type for excel files?.

"Content-Length" is not a mandatory but it allows browser to show progress of download.

"Content-Disposition" tells browser initial file name to save as (without path).

Response with this headers automatically opens "Save as…" dialogue in your browser or start download to your "Downloads" folder immediately depending on your browser settings.

Also consider using servlet instead of JSP like this: import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse;

public class ExcelServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        File tempFile = File.createTempFile(getClass().getName(), ".xls");
        try {
            FileOutputStream fos = new FileOutputStream(tempFile);
            try {
                generateExcel(fos);
            } finally {
                fos.close();
            }

            response.setHeader("Content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Transfer-Encoding", "binary");
            response.setHeader("Content-Length", String.valueOf(tempFile.length()));
            response.addHeader("Content-Disposition", String.format("attachment; filename=%s", tempFile.getName()));

            OutputStream outputStream = response.getOutputStream();
            FileInputStream fis = new FileInputStream(tempFile);
            try {
                int n = 0;
                byte[] buffer = new byte[1024];
                while ((n = fis.read(buffer)) != -1) {
                    outputStream.write(buffer, 0, n);
                }
                outputStream.flush();
            } finally {
                fis.close();
            }
        } finally {
            tempFile.delete();
        }
    }

    private void generateExcel(OutputStream outputStream) throws IOException {
        // TODO This is stub. Place you excel generation code here
        outputStream.write("Hellol, world!".getBytes());
    }
}

Update You can't download file in the way described by me using jQuery.ajax function. I usually use a hidden iframe:

$('#excel').click(function(){
    var fromdate = $('#fromdate').val();
    var todate = $('#todate').val();
    if(fromdate && todate) {
        var url = 'excel?fromdate=' + fromdate + '&todate=' + todate;
        $('#download-frame').attr('src', url);
    }
});

Include in your page markup:

<iframe id="download-frame" src="" width="1" height="1" style="display: none"></iframe>

Thank you very much for ur valuable suggestion but what to write in filesize and filename — Adarsh, Oct 30 '12 at 11:23
fileSize supposed to be actual length of your Excel file stream. You may export not directly to output stream nut first in temporary stream for example FileOutputStream or ByteArrayOutputStream. As I said before, it is not mandatory header, but it's a good practice to tell the length of response. — artplastika, Oct 30 '12 at 11:34
i did as you directed but getting http method get is not supported by this url — Adarsh, Oct 30 '12 at 11:35
fileName is up to you. It could depend on user request parameters, for example, I mean fromdate and todate. Extension should be ".xls" (or .xlsx) depending on format of your generated file and corresponding mime type. — artplastika, Oct 30 '12 at 11:36
"get is not supported by this url" thank you, I fixed the code. You should just override doGet() method instead of service(). — artplastika, Oct 30 '12 at 11:39
thank you Sir ,will you plz implement this in my code ..as i am getting http method get is not supported by url eroor — Adarsh, Oct 30 '12 at 11:39
Your code doesn't affect "http method get is not supported by url eroor". — artplastika, Oct 30 '12 at 12:23
sir get method error has been resolved ..plz sir put my excel generating code to the appropriate place in servlet class with response header ..I am getting problem in setting response headers with my excel code Sir plz plz help me — Adarsh, Oct 30 '12 at 12:25
I have updated servlet code. Also you should change the way you make request in javascript (see update). — artplastika, Oct 30 '12 at 14:58
Sorry to interrupt you again ,,i need to know what to add in src of code because when i am clicking without jquery on button its happening but not with jquery — Adarsh, Nov 22 '12 at 08:18

score -1 · Answer 2 · edited May 18 '16 at 13:16

-1

To get a save dialog box in browser on download file in java:

Instead of using 'onclick()' function, just use href="".

<script type="text/javascript" charset="utf-8">
var contextPath = "<%=request.getContextPath()%>";
</script>

var downloadUrl=contextPath+"/web/cms/downloadDocument.html?docId=" +docId;

<a> href='"+downloadUrl+"'>Download <a>

edited May 18 '16 at 13:16

Jan Doggen

8,154
13
56
117

answered May 18 '16 at 12:56

Mohit Parley

1

how to add openwith and save as dialogue box while exporting to .excel on button click

2 Answers2