Export excel from MySQL database using JSP

Question

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

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.

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

Don't use a JSP to do that. All it contains is Java code. Use a servlet, calling a class to generate the Excel? So that you can compile it and test it and debug it. And indent your code, so that you can read it and understand its structure. — JB Nizet, Sep 09 '17 at 09:04

score 0 · Answer 1 · answered Sep 09 '17 at 09:05

0

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

BTW doing such in JSP is the worst idea.

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

java servlet response returning data

Add

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

and JPS page should only have link

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

answered Sep 09 '17 at 09:05

Jacek Cz

1,872
1
15
22

I tried it in Servlet also..But it not working..It leave same empty page – gayathri Sep 12 '17 at 04:54

Export excel from MySQL database using JSP

1 Answers1