SriniB
asked on
Opening an EXCEL Spreadsheet in JSP
Following is the code that I have:
String file = "Acorn.xls";
response.reset();
response.setContentType("a pplication /vnd.ms-ex cel");
response.setHeader("Conten t-disposit ion","inli ne;filenam e="+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
String file = "Acorn.xls";
response.reset();
response.setContentType("a
response.setHeader("Conten
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
ASKER
Thanks, Unfortunately that did not work either. Let me know if you figure out something.
Thanks
Thanks
it should work. Would u mind posting your complete code?
ASKER
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;cha rset=euc-k r"%>
<head>
<%@ page import = "java.sql.*,
com.rage.xim.commons.dal.D BPool,
com.rage.xim.commons.dal.D AL,
java.util.ArrayList,
com.rage.xim.commons.cache .Cache,
com.rage.xim.commons.admin .UserProfi le,
java.util.Map,
java.math.BigDecimal"%>
<%@ page import = "java.text.DateFormat" %>
<%@ page import = "com.jamonapi.*" %>
<%
String fileName = "Acorn.xls";
//response.reset();
response.setContentType("a pplication /vnd.ms-ex cel");
response.setHeader("Conten t-disposit ion","atta chement;fi lename=\"" + fileName + "\";");
%>
</head>
</html>
Thanks for trying to help me
Srini
<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;cha
<head>
<%@ page import = "java.sql.*,
com.rage.xim.commons.dal.D
com.rage.xim.commons.dal.D
java.util.ArrayList,
com.rage.xim.commons.cache
com.rage.xim.commons.admin
java.util.Map,
java.math.BigDecimal"%>
<%@ page import = "java.text.DateFormat" %>
<%@ page import = "com.jamonapi.*" %>
<%
String fileName = "Acorn.xls";
//response.reset();
response.setContentType("a
response.setHeader("Conten
%>
</head>
</html>
Thanks for trying to help me
Srini
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
FYI, I am using Tomcat 5.0
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks
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/v nd.ms-exce l"%>
<%@page pageEncoding="UTF-8"%>
<%@page import="javax.sql.*, java.sql.*" %>
<%!
private java.sql.Connection getDBCon() throws javax.naming.NamingExcepti on , java.sql.SQLException{
javax.naming.Context c = new javax.naming.InitialContex t();
return ((javax.sql.DataSource) c.lookup("java:yourDS")).g etConnecti on();
}
%>
<%
response.setHeader("conten t-disposit ion", "attachment;filename=yourf ilename.xl s");
//'''''''''''''''''''''''' '''''''''' '''''''''' '''''''''
//'''''''''''''''''''''''' '''''''''' '''''''''' ''''''''' get the details
//'''''''''''''''''''''''' '''''''''' '''''''''' '''''''''
Connection con = getDBCon();
String strSQL = "SELECT * FROM yourtable";
PreparedStatement stmt = con.prepareStatement(strSQ L);
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></h ead><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(" ");
out.println("</font></td>" );
}
out.println("</tr>");
rs.next();
}
out.println("</table>");
}
out.println("</body></html >");
stmt.close(); rs.close(); con.close();
%>
************************** ********
**************************
<%@page contentType="application/v
<%@page pageEncoding="UTF-8"%>
<%@page import="javax.sql.*, java.sql.*" %>
<%!
private java.sql.Connection getDBCon() throws javax.naming.NamingExcepti
javax.naming.Context c = new javax.naming.InitialContex
return ((javax.sql.DataSource) c.lookup("java:yourDS")).g
}
%>
<%
response.setHeader("conten
//''''''''''''''''''''''''
//''''''''''''''''''''''''
//''''''''''''''''''''''''
Connection con = getDBCon();
String strSQL = "SELECT * FROM yourtable";
PreparedStatement stmt = con.prepareStatement(strSQ
ResultSet rs = stmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
rs.last();
int size = rs.getRow();
rs.first();
//''''''''''''''''''''''''
//''''''''''''''''''''''''
//''''''''''''''''''''''''
//''''''''''''''''''''''''
out.write("<html><head></h
if (size > 0){
out.println("<table cellpadding=1 cellspacing=0 border=1><tr>");
for (int i=1; i<rsmd.getColumnCount()+1;
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;
out.println("<td><font size=2>");
if (!rs.getString(j).equals("
out.println(rs.getString(j
else
out.println(" ");
out.println("</font></td>"
}
out.println("</tr>");
rs.next();
}
out.println("</table>");
}
out.println("</body></html
stmt.close(); rs.close(); con.close();
%>
**************************
ASKER
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
Thanks
Srini
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__
colr__
i believe, Srini just want a filled excel to be displayed.
To write an excel sheet, use parts of the code above, like the following:
<%@page contentType="application/v nd.ms-exce l"%>
<%@page pageEncoding="UTF-8"%>
<%@page import="javax.sql.*, java.sql.*" %>
<%
response.setHeader("conten t-disposit ion", "attachment;filename=yourf ilename.xl s");
//'''''''''''''''''''''''' '''''''''' '''''''''' '''''''''
//'''''''''''''''''''''''' '''''''''' '''''''''' ''''''''' Start writing to the excel file
//'''''''''''''''''''''''' '''''''''' '''''''''' '''''''''
out.write("<html><head></h ead><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__
<%@page contentType="application/v
<%@page pageEncoding="UTF-8"%>
<%@page import="javax.sql.*, java.sql.*" %>
<%
response.setHeader("conten
//''''''''''''''''''''''''
//''''''''''''''''''''''''
//''''''''''''''''''''''''
out.write("<html><head></h
// output the content of the ecel sheet using html tables.
ou.println("<table><tr><td
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__
This post appears to have been solved,. Reccomend split fargo, colr__.
ASKER
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
I started an inline frame and opened up the Excel in that iframe. The code is as follows:
<iframe src="file://c:/<%=filename
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
ASKER
Thanks
try the following and do make sure that the file Acorn.xls exists.
response.setContentType("a
response.setHeader("Conten