java - 如何在单击按钮时导出到 .excel 时添加 openwith 并另存为对话框

Question

我通过单击 按钮 将 jqgrid 中显示的数据导出到 .excel 文件中。这是我的导出到 Excel 按钮单击的代码..

 $('#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');
                   }

                });
            }
        });

现在这个按钮将定向到excel.jsp页面。下面是我的excel.jsp页面代码，它直接给出将生成的.excel文件保存在本地磁盘D://中的路径。现在根据我的需要，当用户单击时在“导出到 Excel”按钮上，应弹出“打开并另存为”对话框，使用户能够提供所需的名称并保存到所需的位置。

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);

}

Answer 1

您应该设置几个HTTP header 并将Excel文件的内容直接写入HttpServletResponse的OutputStream。

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 告诉您要发送到浏览器的流类型。 Mime 类型取决于 Excel 文件的格式。请参阅What is correct content-type for excel files? .

“Content-Length”不是强制性的，但它允许浏览器显示下载进度。

“Content-Disposition”告诉浏览器保存的初始文件名(不带路径)。

带有此 header 的响应会自动在浏览器中打开“另存为...”对话框，或根据您的浏览器设置立即开始下载到“下载”文件夹。

还可以考虑使用 servlet 而不是 JSP，如下所示: 导入java.io.File； 导入 java.io.FileInputStream; 导入 java.io.FileOutputStream; 导入java.io.IOException； 导入 java.io.OutputStream; 导入 javax.servlet.ServletException; 导入 javax.servlet.http.HttpServlet; 导入 javax.servlet.http.HttpServletRequest; 导入 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());
    }
}

更新您无法使用 jQuery.ajax 函数按照我描述的方式下载文件。我通常使用隐藏的 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);
    }
});

包含在您的页面标记中:

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