How to export web page to excel using java or jsp or servlets

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.

View Answers

May 23, 2011 at 10:55 AM

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);
}
%>

May 23, 2011 at 10:56 AM

You need poi api for the above code.

May 25, 2011 at 2:40 PM

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.

How to export web page to excel using java or jsp or servlets

Questions by Category