[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2467
  • Last Modified:

Opening an EXCEL Spreadsheet in JSP

Following is the code that I have:
String file = "Acorn.xls";
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition","inline;filename="+file);

This ends up opening a blank excel spreadsheet and not the one that I have store in the path.  Can someone help me.  I am not a servlet guy. Is there any easy way to accomplish this.

Thanks in advance.
Srini
0
SriniB
Asked:
SriniB
  • 7
  • 5
  • 4
  • +1
2 Solutions
 
fargoCommented:
there is nothing like response.reset();

try the following and do make sure that the file Acorn.xls exists.
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment; filename=\"" + fileName + "\";");
0
 
SriniBAuthor Commented:
Thanks, Unfortunately that did not work either.  Let me know if you figure out something.

Thanks
0
 
fargoCommented:
it should work. Would u mind posting your complete code?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SriniBAuthor Commented:
This is the calling jsp
<tr>
 <td>
<iframe src="RR.jsp" width="400" height="400" />>
 </iframe></td>
 </tr>

RR.jsp looks like this
<html>
<%@ page language="java" contentType="text/html;charset=euc-kr"%>

<head>

 <%@ page import = "java.sql.*,
                   com.rage.xim.commons.dal.DBPool,
                   com.rage.xim.commons.dal.DAL,
                   java.util.ArrayList,
                   com.rage.xim.commons.cache.Cache,
                   com.rage.xim.commons.admin.UserProfile,
                   java.util.Map,
                   java.math.BigDecimal"%>
<%@ page import = "java.text.DateFormat" %>
<%@ page import = "com.jamonapi.*" %>

<%
String fileName = "Acorn.xls";
//response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition","attachement;filename=\"" + fileName + "\";");
%>

</head>

</html>

Thanks for trying to help me
Srini
0
 
fargoCommented:
Use the following for RR.jsp and is there any errors or exception in the logs?? Is the file Acorn.jsp is in the same folder where RR.jsp is ?

<html>
<%@ page language="java" contentType="application/vnd.ms-excel;charset=euc-kr"%>

<head>

 <%@ page import = "java.sql.*,
                   com.rage.xim.commons.dal.DBPool,
                   com.rage.xim.commons.dal.DAL,
                   java.util.ArrayList,
                   com.rage.xim.commons.cache.Cache,
                   com.rage.xim.commons.admin.UserProfile,
                   java.util.Map,
                   java.math.BigDecimal"%>
<%@ page import = "java.text.DateFormat" %>
<%@ page import = "com.jamonapi.*" %>

<%
String fileName = "Acorn.xls";
//response.reset();
response.setContentType("application/vnd.ms-excel;charset=euc-kr");
response.setHeader("Content-disposition","attachement;filename=\"" + fileName + "\";");
%>

</head>

</html>
0
 
SriniBAuthor Commented:
The Acorn.xls is in the same directory.  And the above changes did not help either.  Anything else that I could try?
FYI, I am using Tomcat 5.0

Thanks
0
 
colr__Commented:
The reason you are getting a blank document is because you are not writing any data.

The line:
response.setContentType("application/vnd.ms-excel;charset=euc-kr");
Tells the brower what type of content to expect, i.e. an excel document.

This line:
response.setHeader("Content-disposition","attachement;filename=\"" + fileName + "\";");
Tells the browser what the file name should be.

At no stage is the actuall content sent. Once these above steps have been executed, you wil then have to start writing data to the browser using out.println("") - this is the data that forms the exel document.

You would be far better doing this in a sevlet b(as opposed to a JSP). Set the headres first and only then start writging content to the browser. I dont think the code youve got above will compile, does it?

colr__
0
 
SriniBAuthor Commented:
Thanks for the info.  The code does not throw any errors.  Can you throw a sample at me as to how the servlet would be written.  I am new to this and am not very familiar.  The above invoked servlets have been written by my counter parts.  Any help would be appreciated.

Thanks
0
 
colr__Commented:
My apologies, you can in fact do this in a JSP. Here is what I have used befre. The content for the file comes from a database, but you can get this from elswhere depending on how your application works.



**********************************
<%@page contentType="application/vnd.ms-excel"%>
<%@page pageEncoding="UTF-8"%>
<%@page import="javax.sql.*, java.sql.*" %>

<%!
private java.sql.Connection getDBCon() throws javax.naming.NamingException , java.sql.SQLException{
    javax.naming.Context c = new javax.naming.InitialContext();
    return ((javax.sql.DataSource) c.lookup("java:yourDS")).getConnection();
}
%>
<%

response.setHeader("content-disposition", "attachment;filename=yourfilename.xls");
//'''''''''''''''''''''''''''''''''''''''''''''''''''''
//''''''''''''''''''''''''''''''''''''''''''''''''''''' get the details
//'''''''''''''''''''''''''''''''''''''''''''''''''''''
Connection con = getDBCon();
String strSQL = "SELECT * FROM yourtable";
PreparedStatement stmt = con.prepareStatement(strSQL);
ResultSet rs = stmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
rs.last();
int size = rs.getRow();
rs.first();
//'''''''''''''''''''''''''''''''''''''''''''''''''''''
//''''''''''''''''''''''''''''''''''''''''''''''''''''' Start writing to the excel file
//''''''''''''''''''''''''''''''''''''''''''''''''''''' The SQL above can be changed w/o having to change the script below
//'''''''''''''''''''''''''''''''''''''''''''''''''''''
out.write("<html><head></head><body>");

if (size > 0){      

      out.println("<table cellpadding=1 cellspacing=0 border=1><tr>");
      for (int i=1; i<rsmd.getColumnCount()+1; i++)
            out.println("<td><font size=2><b>" + rsmd.getColumnName(i) +  "</b></font></td>");
       
      out.println("</tr>");
      
        for (int i=0; i<size; i++){
            out.println("<tr>");
                for (int j=1; j<rsmd.getColumnCount()+1; j++){
                    out.println("<td><font size=2>");
                        if (!rs.getString(j).equals(""))
                            out.println(rs.getString(j));
                        else
                            out.println("&nbsp;");
                    out.println("</font></td>");
                }
            out.println("</tr>");
            rs.next();
        }
        out.println("</table>");
}

out.println("</body></html>");

stmt.close(); rs.close(); con.close();
%>
**********************************
0
 
SriniBAuthor Commented:
Thanks for the code.  However there is a small problem.  I already have a spreadsheet that has all the data and is filled with Macros.  All I am interested in is displaying this spreadsheet in the inline frame.  Any ideas?

Thanks
Srini
0
 
colr__Commented:
I dont understand what you mean here. Are you trying to write an excel file dynamically, or are you trying to open data that is contained in an excel sheet?

colr__
0
 
fargoCommented:
i believe, Srini just want a filled excel to be displayed.
0
 
colr__Commented:
To write an excel sheet, use parts of the code above, like the following:

<%@page contentType="application/vnd.ms-excel"%>
<%@page pageEncoding="UTF-8"%>
<%@page import="javax.sql.*, java.sql.*" %>

<%
response.setHeader("content-disposition", "attachment;filename=yourfilename.xls");
//'''''''''''''''''''''''''''''''''''''''''''''''''''''
//''''''''''''''''''''''''''''''''''''''''''''''''''''' Start writing to the excel file
//'''''''''''''''''''''''''''''''''''''''''''''''''''''
out.write("<html><head></head><body>");

// output the content of the ecel sheet using html tables.
ou.println("<table><tr><td>hello</td><tdthere</td></tr></table>");

out.println("</body></html>");
%>

This code should actually work, so if you put this in a JSP and call it, you should get an excel sheet open up with the content filled in. You should be able to adapt this to your own content quite easily f you already have a referance to it in your code.

colr__
0
 
raj3060Commented:
0
 
colr__Commented:
This post appears to have been solved,. Reccomend split fargo, colr__.
0
 
SriniBAuthor Commented:
The above solutions work but not to the extent I wanted.  Alternatively, what I did is as follows:
I started an inline frame and opened up the Excel in that iframe. The code is as follows:

      <iframe src="file://c:/<%=filename%>" width="800" height="450" />
and this helped me open up an existing excel in a JSP page.

Guys thanks for you help.  and for the help, I recommend the split as Colr recommends

Thanks
0
 
SriniBAuthor Commented:
Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now