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
SriniBAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.