使用JSP从MySQL数据库导出excel

This is my JSP coding, I am using link to redirect to JSP page :

这是我的JSP编码，我使用链接重定向到JSP页面：

Excel.jsp

Excel.jsp

   <%@page import="java.sql.*"%>
   <%@page import="java.sql.SQLException"%>
   <%@page import="org.apache.poi.hssf.usermodel.*"%>
   <%@page import="  java.io.*"%>  

   <%

     String url = "jdbc:mysql://localhost:3306/";
     String dbName = "login";
     String driver = "com.mysql.jdbc.Driver";
     String userName = "root";
    String password = "";
      try {
     Class.forName("driver");
   Connection con = DriverManager.getConnection("url + dbName", "userName", 
   "password");
 Statement st = con.createStatement();
 ResultSet rs = st.executeQuery("select * from openstock");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("fisocon");
HSSFRow rowhead = sheet.createRow((short) 0);
rowhead.createCell((short) 0).setCellValue("iname");
rowhead.createCell((short) 1).setCellValue("wname");
rowhead.createCell((short) 2).setCellValue("catname");
 rowhead.createCell((short) 3).setCellValue("class");
  rowhead.createCell((short) 4).setCellValue("unit");
   rowhead.createCell((short) 5).setCellValue("nname");
int i = 1;

  while (rs.next()){
     out.println("hai2");
    HSSFRow row = sheet.createRow((short) i);
    row.createCell((short) 
      0).setCellValue(Integer.toString(rs.getInt("iname")));
    row.createCell((short) 1).setCellValue(rs.getString("wname"));
    row.createCell((short) 2).setCellValue(rs.getString("catname"));
      row.createCell((short) 3).setCellValue(rs.getString("class"));
        row.createCell((short) 4).setCellValue(rs.getString("unit"));
         row.createCell((short) 5).setCellValue(rs.getString("nname"));
      i++;
   }
  String yemi = "d:/xls/test.xls";
  FileOutputStream fileOut = new FileOutputStream(yemi);
  workbook.write(fileOut);
  fileOut.close();
  } catch (ClassNotFoundException e1) {
   e1.printStackTrace();
  }
 catch (SQLException e1) {
    e1.printStackTrace();
  } catch (FileNotFoundException e1) {
    e1.printStackTrace();
  } catch (IOException e1) {
      e1.printStackTrace();
   }
 %>

In this coding, createcell is strikeout. I don't know why it is. There is no errors in coding. I also add poi 3.14 jar file in library.

在此编码中，createcell是删除线。我不知道为什么会这样。编码没有错误。我还在库中添加了poi 3.14 jar文件。

My output page displays as empty. Please help me. Thanks in advance.

我的输出页面显示为空。请帮帮我。提前致谢。

1 个解决方案

#1

Page is empty because everything You made is outside of page.

页面是空的，因为您创建的所有内容都在页面之外。

BTW doing such in JSP is the worst idea.

BTW中这样做是最糟糕的想法。

Move this code to servlet, here is tip how to return binary data.

将此代码移动到servlet，这里是如何返回二进制数据的提示。

java servlet response returning data

java servlet响应返回数据

Add

加

response.contentType = "application/vnd.ms-excel"

and JPS page should only have link

和JPS页面应该只有链接

The additional effect: servlet code is more, more friendly to debugging.

附加效果：servlet代码更多，更友好的调试。

#1