Apache POI - 使用XSSFWorkbok + servlet响应 [英] Apache POI - Working with XSSFWorkbok + servlet response

问题描述

按照此链接中显示的示例：创建一个Excel文件供用户使用Apache POI下载，I尝试两种配置下载/保存电子表格。

首先使用.XLS文件：

  response.setContentType（ 应用程序/ 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（）; 
  

这样做。

然后我试过使用XLSX文件：

  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（）; 
  

当我尝试这个，我收到消息： Excel发现不可读的内容在'testxls .xlsx'你想恢复这个工作簿的内容吗？....

尽管有这个消息，电子表格正常打开，但我真的想删除这个消息。

任何想法？

解决方案

使用此JSP代码并成功生成excel文件。我已经通过数据库给Excel输入文件，还可以给出手动输入。

 <％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）; 
语句st = con.createStatement（）; 
 out.println（你好世界）; 
 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 catch：+ e.getMessage（））; 
}％> 
 //创建一个小电子表格
<％
 
％> 
<％
 
 ByteArrayOutputStream outByteStream = new ByteArrayOutputStream（）; 
 wb.write（outByteStream）; 
 byte [] outArray = outByteStream.toByteArray（）; 
 response.setContentType（application / ms-excel）; 
 response.setContentLength（outArray.length）; 
 response.setHeader（Expires：，0）; //消除浏览器缓存
 response.setHeader（Content-Disposition，attachment; filename = testxls.xls）; 
 OutputStream outStream = response.getOutputStream（）; 
 outStream.write（outArray）; 
 outStream.flush（）; 
 
％> 
  

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, 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屋！