Results 1 to 4 of 4
  1. 05-24-2011, 10:57 AM #1
    techsing14
    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.
    Reply With Quote Reply With Quote
  2. 05-24-2011, 11:07 AM #2
    Petr's Avatar
    Petr
    Petr is offline Senior Member
    Join Date
    Jan 2011
    Location
    Russia
    Posts
    616
    Rep Power
    3

    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
    Reply With Quote Reply With Quote
  3. 05-25-2011, 11:03 AM #3
    techsing14
    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.
    Reply With Quote Reply With Quote
  4. 05-25-2011, 11:08 AM #4
    Petr's Avatar
    Petr
    Petr is offline Senior Member
    Join Date
    Jan 2011
    Location
    Russia
    Posts
    616
    Rep Power
    3

    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
    Reply With Quote Reply With Quote
« how to get list of files stored on url location | Browser's back button refresh the pages »

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

Bookmarks

Posting Permissions

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

Forum Rules