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
– JB Nizet, Commented Sep 9, 2017 at 9:04

Jacek Cz · Accepted Answer · 2017-09-09 09:05:05Z

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 9, 2017 at 9:05

Jacek Cz

1,9061 gold badge17 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gayathri Over a year ago

I tried it in Servlet also..But it not working..It leave same empty page

Collectives™ on Stack Overflow

Export excel from MySQL database using JSP

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related