Results 1 to 4 of 4
  1. #1
    techsing14 is offline Member
    Join Date
    Oct 2010
    Posts
    24
    Rep Power
    0

    Default How to Export web page(jsp page) to excel using jsp or servlets or javascript

    Hi

    I am trying to retrieve records from database and then export to excel file, here is my code, I tried using javascript, but am getting errors,

    masterlist_fetch.jsp

    Java Code:
    <%@ page import="java.sql.*" %>
    <% Class.forName("oracle.jdbc.driver.OracleDriver");%>
    <HTML>
        <head>
            
        </head>
        <BODY bgcolor="#99CCFF">
            
           <br>
            <form method="post" action="excelFile.jsp">
            <%
                        String connectionURL = "jdbc:oracle:thin:@localhost:1521:xe";
                        String driver = "oracle.jdbc.driver.OracleDriver";
                        String user = "hr";
                        String pass = "hr1";
                        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 { 
                                
            %>
    <script language="javascript">
    function exportToExcel()
    {
    var oExcel = new ActiveXObject("Excel.Application");
    var oBook = oExcel.Workbooks.Add;
    var oSheet = oBook.Worksheets(1);
    
    for (var y=0;y<detailsTable.rows.length;y++)
    // detailsTable is the table where the content to be exported is
    {
    for (var x=0;x<detailsTable.rows(y).cells.length;x++)
    {
    oSheet.Cells(y+1,x+1) =
    detailsTable.rows(y).cells(x).innerText;
    }
    }
    oExcel.Visible = true;
    oExcel.UserControl = true;
    }
    </script>
            <TABLE name="detailsTable" cellpadding="15" border="1" style="background-color:#efefef" align="center">
                <TR>
                    <TH>Serial No</TH>
                     <TH>Part No</TH>
                    <TH>Material Number</TH>
                    <TH>Material Description</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 name="detailsTable" style="background-color:white">
                    <TD> <%= rs.getString(1)%> </TD>
                    <TD> <%= rs.getString(2)%> </TD>
                    <TD> <%= rs.getString(3)%> </TD>
                    <TD> <%= rs.getString(4)%> </TD>
                    
                </TR>
                <%                        rs.next();
                                 } while (rs.isAfterLast() != true);
                                 
    
                %>
            
            </TABLE>
            <BR>
            <%
                            }
                                                   
                            rs.close();
                            pst.close();
                            connection.close();
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
    
            %>
        
    <center><button onclick="exportToExcel();">Save as Excel</button></center>
     
    </form>
                </BODY>
    </HTML>
    This code gives me an error called detailsTable undefined, Please can anyone help me out. I am stuck here. If this code is incorrect then please tell me how to do using servlets or jsp.

    Thanks in advance
    Lissy.

  2. #2
    Petr's Avatar
    Petr is offline Senior Member
    Join Date
    Jan 2011
    Location
    Russia
    Posts
    618
    Rep Power
    4

    Default

    Hi. if you have not heavy restrictions in this task you will use CSV format for it. Hence you make a Servlet. where you get data from database. Then you write it in OutStream and to set especial header, which tell browser offers user open this file in Excel or I have other solve to use WebDav protocol.
    Skype: petrarsentev
    http://TrackStudio.com

  3. #3
    techsing14 is offline Member
    Join Date
    Oct 2010
    Posts
    24
    Rep Power
    0

    Default

    I generated this servlet but still am not able to get the data in excel,

    ExcelFile.java

    Java Code:
    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);
    }
       }
    }
    This is not creating any excel file on my pc, please can anyone help me out. I want the web page data to be export to excel file.

    Thanks in advance
    Lissy.

  4. #4
    Petr's Avatar
    Petr is offline Senior Member
    Join Date
    Jan 2011
    Location
    Russia
    Posts
    618
    Rep Power
    4

    Default

    Look at that Example file download servlet [java] [download] [file] [servlet]

    The above code creates locale file in this path c:/data.xsl. But you write data in OutStream for Servlet as like in example on link.
    Skype: petrarsentev
    http://TrackStudio.com

Similar Threads

  1. JSP/Javascript Page Pagination
    By maas in forum JavaServer Pages (JSP) and JSTL
    Replies: 4
    Last Post: 08-04-2010, 10:29 AM
  2. How to export data from web page to msword or pdf
    By verma1986 in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 04-04-2010, 09:26 AM
  3. form and results on same page using servlets
    By perplexingtrax in forum New To Java
    Replies: 3
    Last Post: 04-02-2009, 06:04 PM
  4. scriplet in javascript function in jsp page
    By Renjini in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 05-21-2008, 01:07 PM
  5. javascript sliderbar problem with JSP page
    By prakash.eng in forum JavaServer Faces (JSF)
    Replies: 0
    Last Post: 12-17-2007, 08:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •