Forum:

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

Ranch Hand

Posts: 47

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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.

Bear Bibeault

Sheriff

Posts: 67752

173

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I would write a servlet that emits a format that Excel understand. The link can hit that servlet.

[Asking smart questions] [About Bear] [Books by Bear]

HTML 5 (verified skill)
Skill verified by Jeanne Boyarsky

CSS 3 (verified skill)
Skill verified by Jeanne Boyarsky

JavaScript (verified skill)
Skill verified by Jeanne Boyarsky

rajiv jackz

Greenhorn

Posts: 29

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

HI
you can go head with display tag

/http://www.displaytag.org/1.2/

this link might be useful. export to excel

xsunil kumar

Ranch Hand

Posts: 147

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Bear, you are displaying the result in JSP page. It means that you are already records available in request/ session. Now on click on save button why not you call one servlet that servlet will do writing in excel file. After that you can give link to user to download that excel file.

Regards,
Sunil

Bear Bibeault

Sheriff

Posts: 67752

173

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

xsunil kumar wrote:Bear, you are displaying the result in JSP page. It means that you are already records available in request/ session.

No. The request is long gone by the time the page is displayed in the browser, and no data is automatically stored in the session.

The best solution is to have a servlet that reads the same data and formats it, not as HTML, but as a format that Excel understands.

[Asking smart questions] [About Bear] [Books by Bear]

Ulf Dittmer

Rancher

Posts: 43081

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Bear Bibeault wrote:The best solution is to have a servlet that reads the same data and formats it, not as HTML, but as a format that Excel understands.

I'd say that's debatable; storing the data in the session can work well. That's what the DisplayTag library suggested previously does, which is indeed worth a look if the data is both to be displayed as HTML and downloaded as XLS.

Bear Bibeault

Sheriff

Posts: 67752

173

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I did not mean to not store in the session -- just that it won't be there automatically. And trying to extract the data from the HTML page after it has been rendered is like trying to get a steak out of hamburger.

Caching the data in the session may be a viable path depending upon the expected usage patterns of the application

[Asking smart questions] [About Bear] [Books by Bear]

lisy jessica

Ranch Hand

Posts: 47

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I am trying to do this using javascript, but still its not working, here is my code

masterlist_fetch.jsp

<%@ page import="java.sql.*" %>
<% Class.forName("oracle.jdbc.driver.OracleDriver");%>
<HTML>
    <head>
        
    </head>
    <BODY bgcolor="#99CCFF">
        
           <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 = "hr";
                    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 gives me an error in the webpage when i click save as excel button, the error is detailsTable is undefined, Please can anyone help me, I am stuck here.

Thanks in advance.
Lissy.

Ulf Dittmer

Rancher

Posts: 43081

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Apparently you think that because you have an element with name "detailsTable" on your page (two elements, actually, which is a bad idea), that element would be defined as a JavaScript variable. Not so. Read up on the JavaScript methods getElementsByName and getElementById to learn about how to structure your HTML pages so that what you're trying to do becomes possible.

lisy jessica

Ranch Hand

Posts: 47

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Thanks for the reply, Will try this.

Politics n. Poly "many" + ticks "blood sucking insects". Tiny ad:

Gift giving made easy with the permaculture playing cards

https://coderanch.com/t/777758/Gift-giving-easy-permaculture-playing