Apache的POI - 与XSSFWorkbok + servlet响应工作 [英] Apache POI - Working with XSSFWorkbok + servlet response

问题描述

我有我的Java应用程序，让下载XLSX文件的问题。

在下面这个链接显示的例子：<一href=\"http://stackoverflow.com/questions/11226603/create-an-excel-file-for-users-to-download-using-apache-poi-jsp\">create为用户Excel文件使用Apache POI，JSP 下载，我尝试了两种配置，以下载/保存A S preadsheet。

先用.xls文件：

  response.setContentType（应用程序/ MS-Excel的）;
response.setHeader（内容处置，附件;文件名= testxls.xls）;HSSFWorkbook WB =新HSSFWorkbook（）;
HSSFSheet片= wb.createSheet（）;
HSSFRow行= sheet.createRow（0）;
HSSFCell细胞= row.createCell（0）;
cell.setCellValue（一些文字）;ByteArrayOutputStream outByteStream =新ByteArrayOutputStream（）;
wb.write（outByteStream）;字节[] = outArray outByteStream.toByteArray（）;
的OutputStream outStream = response.getOutputStream（）;
outStream.write（outArray）;
outStream.flush（）;
 

这工作。

然后我试图用一个XLSX文件：

<$p$p><$c$c>response.setContentType(\"application/vnd.openxmlformats-officedocument.s$p$padsheetml.sheet\");
response.setHeader（内容处置，附件;文件名= testxls.xlsx）;XSSFWorkbook WB =新XSSFWorkbook（）;
XSSFSheet片= wb.createSheet（）;
XSSFRow行= sheet.createRow（0）;
XSSFCell细胞= row.createCell（0）;
cell.setCellValue（一些文字）;ByteArrayOutputStream outByteStream =新ByteArrayOutputStream（）;
wb.write（outByteStream）;字节[] = outArray outByteStream.toByteArray（）;
的OutputStream outStream = response.getOutputStream（）;
outStream.write（outArray）;
outStream.flush（）;

当我尝试这个，我收到消息：？ Excel中发现，在testxls.xlsx不可读的内容是否要恢复此工作簿的内容......

尽管如此消息，S preadsheet正常打开，但我真的要删除此消息。

任何想法？

解决方案

使用该JSP code和生成excel文件succesfully.I已给定的输入通过数据库excel文件，你也可以给手动输入。

 ＆LT;％HSSFWorkbook WB =新HSSFWorkbook（）;
    HSSFSheet片= wb.createSheet（）;
       尝试{
        java.sql.Connection中的骗子;
        的Class.forName（com.mysql.jdbc.Driver）;
        CON =的DriverManager.getConnection（JDBC的：mysql：//本地主机：3306 / custinfo，根，ABC）;
            声明ST = con.createStatement（）;
            通过out.println（世界你好）;
        结果集RS = st.executeQuery（选择名称，状态，平衡，从customerdata描述，其中客户id ='+编号+'）;        HSSFRow行= sheet.createRow（（短）0）;
        row.createCell（（短）0）.setCellValue（NAME）;
        row.createCell（（短）1）.setCellValue（国家）;
        row.createCell（（短）2）.setCellValue（平衡）;
        row.createCell（（短）3）.setCellValue（说明）;
        而（rs.next（））
        {
             通过out.println（世界你好数据）;
            HSSFRow ROW1 = sheet.createRow（（短）一）;
            row1.createCell（（短）0）.setCellValue（rs.getString（名字））;
            row1.createCell（（短）1）.setCellValue（rs.getString（国家））;
         row1.createCell（（短）2）.setCellValue（rs.getString（3））;
         row1.createCell（（短）3）.setCellValue（rs.getString（4））;
         I = I + 1;
        sheet.autoSizeColumn（（短）1）;        }       }
      赶上（的SQLException E）{
        通过out.println（的SQLException捕获：+ e.getMessage（））;
      }％GT;
    //创建一个小s preadsheet
    ＆LT;％    ％GT;
    ＆LT;％    ByteArrayOutputStream outByteStream =新ByteArrayOutputStream（）;
    wb.write（outByteStream）;
    字节[] = outArray outByteStream.toByteArray（）;
    response.setContentType（应用程序/ MS-Excel的）;
    response.setContentLength（outArray.length）;
    response.setHeader（到期日：，0）; //消除浏览器缓存
    response.setHeader（内容处置，附件;文件名= testxls.xls）;
    的OutputStream outStream = response.getOutputStream（）;
    outStream.write（outArray）;
    outStream.flush（）;    ％GT;
 

I'm having problems in my java application to enable downloading XLSX files.

following the example displayed in this link: create an excel file for users to download using apache poi, jsp, I tried two configurations to download/save a spreadsheet.

First with a .XLS file:

response.setContentType("application/ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=testxls.xls");

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue("Some text");

ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
wb.write(outByteStream);

byte[] outArray = outByteStream.toByteArray();
OutputStream outStream = response.getOutputStream();
outStream.write(outArray);
outStream.flush();

This works.

Then i tried with a XLSX file:

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename=testxls.xlsx");

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
cell.setCellValue("Some text");

ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
wb.write(outByteStream);

byte[] outArray = outByteStream.toByteArray();
OutputStream outStream = response.getOutputStream();
outStream.write(outArray);
outStream.flush();

When i try this, i receive the message: "Excel found unreadable content in 'testxls.xlsx'. Do you want to recover the contents of this workbook? ...."

Despite this message, the spreadsheet opens normally, but i really want to remove this message.

Any ideas?

解决方案

Use this JSP code and generate the excel file succesfully.I have given input to excel file through the database you can also give manual inputs.

<%HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
       try {
        java.sql.Connection con;
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/custinfo","root","abc");
            Statement st= con.createStatement(); 
            out.println("hello world");
        ResultSet rs=st.executeQuery("select name ,state ,balance,description from customerdata where customerid='"+Id+"'"); 

        HSSFRow row = sheet.createRow((short)0);
        row.createCell((short)0).setCellValue("NAME");
        row.createCell((short)1).setCellValue("STATE");
        row.createCell((short)2).setCellValue("BALANCE");
        row.createCell((short)3).setCellValue("DESCRIPTION");
        while(rs.next())
        {
             out.println("hello world data");       
            HSSFRow row1 = sheet.createRow((short)i);
            row1.createCell((short)0).setCellValue(rs.getString("name"));
            row1.createCell((short)1).setCellValue(rs.getString("state"));
         row1.createCell((short)2).setCellValue(rs.getString(3));
         row1.createCell((short)3).setCellValue(rs.getString(4));
         i=i+1;
        sheet.autoSizeColumn((short)1); 

        }

       }
      catch(SQLException e) {
        out.println("SQLException caught: " +e.getMessage());
      }%>
    // create a small spreadsheet
    <%

    %>
    <% 

    ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
    wb.write(outByteStream);
    byte [] outArray = outByteStream.toByteArray();
    response.setContentType("application/ms-excel");
    response.setContentLength(outArray.length);
    response.setHeader("Expires:", "0"); // eliminates browser caching
    response.setHeader("Content-Disposition", "attachment; filename=testxls.xls");
    OutputStream outStream = response.getOutputStream();
    outStream.write(outArray);
    outStream.flush();

    %>

这篇关于Apache的POI - 与XSSFWorkbok + servlet响应工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！