Hi
I am trying to export web page(jsp page ) to excel using jsp or servlets. I am retrieving records from database and displaying in the jsp page, In this page I have a save as excel button, when I click this button I need these displayed records to excel file. I tried with vbscript and javascript but am getting some errors. Please can anyone tell me how to do this using java or jsp or servlets??
Thanks in advance, Lissy.
1)retrieve.jsp:
<%@page import="java.sql.*"%> <form method="post" action="excelFile.jsp"> <table border=1> <tr><th>Name</th><th>Address</th><th>Contact No</th><th>Email</th></tr> <% Class.forName("com.mysql.jdbc.Driver").newInstance(); Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root"); Statement st=con.createStatement(); ResultSet rs=st.executeQuery("Select * from employee"); while(rs.next()){ %> <tr><td><input type="text" name="name" value="<%=rs.getString("name")%>"></td><td><input type="text" name="address" value="<%=rs.getString("address")%>"></td><td><input type="text" name="contact" value="<%=rs.getString("contactNo")%>"></td><td><input type="text" name="email" value="<%=rs.getString("contactNo")%>"></td></tr> <% } %> </table> <input type="submit" value="Export To Excel"> </form>
2)excelFile.jsp:
<%@page import=" java.io.*"%> <%@page import=" org.apache.poi.hssf.usermodel.*"%> <% String name[]=request.getParameterValues("name"); String address[]=request.getParameterValues("address"); String contact[]=request.getParameterValues("contact"); String email[]=request.getParameterValues("email"); try{ String filename="c:/data.xls" ; HSSFWorkbook hwb=new HSSFWorkbook(); HSSFSheet sheet = hwb.createSheet("sheet"); HSSFRow rowhead= sheet.createRow((short)0); rowhead.createCell((short) 0).setCellValue("Name"); rowhead.createCell((short) 1).setCellValue("Address"); rowhead.createCell((short) 2).setCellValue("Contact No"); rowhead.createCell((short) 3).setCellValue("E-mail"); for(int i=0;i<name.length;i++){ int j=i+1; HSSFRow row= sheet.createRow((short)j); row.createCell((short) 0).setCellValue(name[i]); row.createCell((short) 1).setCellValue(address[i]); row.createCell((short) 2).setCellValue(contact[i]); row.createCell((short) 3).setCellValue(email[i]); } FileOutputStream fileOut = new FileOutputStream(filename); hwb.write(fileOut); fileOut.close(); out.println("Your excel file has been generated!"); } catch( Exception ex ) { System.out.println(ex); } %>
You need poi api for the above code.
Masterlist_fetch.jsp [code] <%@ page import="java.sql.*" %> <% Class.forName("oracle.jdbc.driver.OracleDriver");%> <HTML> <BODY bgcolor="#99CCFF"> <br><br> <h2 align="center">Records for the selected Part No</h2> <form name="fetchform" action="/UserInvenApplication/excelFile" method="post"> <% String connectionURL = "jdbc:oracle:thin:@localhost:1521:xe"; String driver = "oracle.jdbc.driver.OracleDriver"; String user = "root"; String pass = "root"; Connection connection = null; PreparedStatement pst; try { Class.forName(driver); connection = DriverManager.getConnection(connectionURL, user, pass); String PartNo = request.getParameter("PartNo"); int ibl = PartNo.length(); String Last_char = PartNo.substring(ibl-1,ibl); String X; if (Last_char.equals("*")) { X = PartNo.substring(0,(ibl-1)) + '%'; pst = connection.prepareStatement("select SerialNo,PartNo,Material_Number,Material_Desc from InventoryDB_Main where PartNo like '"+X+"'"); } else { X = PartNo; pst = connection.prepareStatement("select SerialNo,PartNo,Material_Number,Material_Desc from InventoryDB_Main where PartNo = '"+X+"'"); } //PreparedStatement pst = connection.prepareStatement("select SerialNo,PartNo,Material_Number,Material_Desc from InventoryDB_Main where PartNo = '"+ PartNo +"'"); ResultSet rs = pst.executeQuery(); // while(rs.next()){ if (!rs.next()) { out.println("<br>"); out.println("<table align=\"center\" font=\"16\">"); out.println("<tr><th>Sorry, Could not find data</th></tr>"); out.println("</table>"); } else { %> <TABLE cellpadding="15" border="1" style="background-color:#6699CC" align="center"> <TR> <TH>Serial No</TH> <TH>Part No</TH> <TH>Material Number</TH> <TH>Material Description</TH> <TH>Update</TH> </TR> <% do { %> <style type="text/css"> a:link {color:#FF0000;} /* unvisited link */ a:visited {color:#FF0000;} /* visited link */ a:hover {color:#FF00FF;} /* mouse over link */ a:active {color:#0000FF;} /* selected link */ </style> <TR style="background-color:white"> <TD> <%= rs.getString(1)%> </TD> <TD> <%= rs.getString(2)%> </TD> <TD> <%= rs.getString(3)%> </TD> <TD> <%= rs.getString(4)%> </TD> <TD><a href="masterServlet?SerialNo=<%= rs.getString(1)%>">Update</a></TD> </TR> <% rs.next(); } while (rs.isAfterLast() != true); %> </TABLE> <BR> <% } rs.close(); pst.close(); connection.close(); } catch (Exception e) { e.printStackTrace(); } %> <center> <input type="submit" value="Save as Excel"/> </center> </form> </BODY> </HTML> [/code] I have created Servlet named ExcelFile.java ExcelFile.java [code] package saveasexcel; import java.io.*; import javax.servlet.*; import javax.servlet.http.*; import org.apache.poi.hssf.usermodel.*; public class ExcelFile extends HttpServlet{ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException,IOException{ response.setContentType("text/html"); PrintWriter out = response.getWriter(); String SerialNo[]=request.getParameterValues("SerialNo"); String PartNo[]=request.getParameterValues("PartNo"); String Material_Number[]=request.getParameterValues("Material_Number"); String Material_Desc[]=request.getParameterValues("Material_Desc"); try{ String filename="c:/data.xls" ; HSSFWorkbook hwb=new HSSFWorkbook(); HSSFSheet sheet = hwb.createSheet("sheet"); HSSFRow rowhead= sheet.createRow((short)0); rowhead.createCell((short) 0).setCellValue("SerialNo"); rowhead.createCell((short) 1).setCellValue("PartNo"); rowhead.createCell((short) 2).setCellValue("Material_Number"); rowhead.createCell((short) 3).setCellValue("Material_Desc"); for(int i=0;i<SerialNo.length;i++){ int j=i+1; HSSFRow row= sheet.createRow((short)j); row.createCell((short) 0).setCellValue(SerialNo[i]); row.createCell((short) 1).setCellValue(PartNo[i]); row.createCell((short) 2).setCellValue(Material_Number[i]); row.createCell((short) 3).setCellValue(Material_Desc[i]); } FileOutputStream fileOut = new FileOutputStream(filename); hwb.write(fileOut); fileOut.close(); out.println("Your excel file has been generated!"); } catch( Exception ex ) { System.out.println(ex); } } } [/code] This servlet is not generating any excel file, Please help me how to export the web page to excel, once I display the records in jsp page, when i click save as excel button, I want a new excel file open with all the web content in it. Please help me out. I am stuck here. Thanks in advance Lissy.