This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JSP and the fly likes HTML table to an Excel Spreadsheet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "HTML table to an Excel Spreadsheet" Watch "HTML table to an Excel Spreadsheet" New topic
Author

HTML table to an Excel Spreadsheet

Steven Kors
Ranch Hand

Joined: Jan 30, 2002
Posts: 33
This is the code i tried to serve an HTML table to an excel spreadsheet.
<jsp:include page="/MyProject/servlet/HTMLTable" >
<jsp : param name="ft" value="9" />
<jsp : param name="cc" value="14" />
<jsp : param name="ft" value="excel" />
</jsp:include>
<%
response.setContentType("application/vnd.ms-excel");
%>
I have this above code in a jsp page and when it runs it does not work.
HTMLTable is a servlet that outputs a simple HTML Table
<TABLE BORDER=1>
<TR><TH></TH><TH>hubcaps<TH>bumbers
<TR><TH>First Day<TD>2307<TD>4706
<TR><TH>Second Day<TD>2982<TD>5104
<TR><TH>Third Day<TD>3011<TD>5220
<TR><TH>Fourth Day<TD>3055<TD>5287
</TABLE>
I can see the HTML being generated but it is not loaded into Excel. ( i see the HTMLcontent being generated on a Webclient snooping utility). I also know that this data DOES transfer into an Excel spreadsheet. If i were to hard code this table into an JSP or HTML page it laods into the Excel spreadsheet just fine.
I also tried loading this information into Excel using servlets (tried this first) but this did not work either. Here is the code;
<code>
public void sendToExcel(PrintWriter out,String table,HttpServletRequest request,HttpServletResponse response)
throws ServletException, IOException
{
try {
out.println(table);
response.setContentType("application/vnd.ms-excel");
} catch (Exception e) {
out.println("sendToExcel Error: " + e);
e.printStackTrace();
}
}
</code>
table= the HTML table i showed above that was exported nicely into excel if it was hard coded.
out=PrintWriter
I want to crreate an HTML table from selections that the user has made. I can do this and it works just fine BUT when i try to export it into Excel i get a blank page. Excel loads fine its just empty. I tried the positioning of the setContentType directive but that didnt help. I know that the HTML table is fine because if i hard codde the table then it loads into Ecel just fine.
Oh i should say that i am using Tomcat4 (windows) and the "MyProject" directory resides in the webapps directory.
PLEASE HELP !!! =)
Thank you =)
Please help =) Hints are welcome you dont have to answer the question completely just point me in the right direction =)
[ January 30, 2002: Message edited by: Mercenary_Steel ]

Thanks,
Steven
ersin eser
Ranch Hand

Joined: Feb 22, 2001
Posts: 1072
If i were to hard code this table into an JSP or HTML page

Can you try
<jsp:directive.include file="/MyProject/servlet/HTMLTable"> ?
[ January 30, 2002: Message edited by: ersin eser ]
Steven Kors
Ranch Hand

Joined: Jan 30, 2002
Posts: 33
I tried the jsp irective call and that didnt solve my problem
krussi rong
Ranch Hand

Joined: Jan 30, 2002
Posts: 62
I think you can just form a *.csv file,
this kind of file will automatically loaded
by Excel.
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15950
    
  19

A comma-separated-value file (CSV) is exactly that. A set of lines of text, one line per row, with the column values within each row separated by commas:
State,Democratic Votes,Republican Votes
NJ,15342,11291
NY,26874,22566
FL,11368,18405
and so forth. Obviously there's no place for fancy formatting or formulas, just raw data, but that's often enough.
Actually, there's no such thing as "automatically" when it comes to how data from a web server is handled by a client. The content-type header you send is looked up in the user's MIME-types table and handled according to what was found there. If NOTHINGis found there, the default is generally either an attempt to download the file locally or to display it in the browser as a text file.
In the case of Excel, for example, if Excel - or at least some sort of Excel viewer program isn't installed on the user's machine, there won't be a MIME table entry for application/vnd.ms-excel and the default action will be taken. It sounds like that's what's happening. If you can actually see the HTML tags in the browser window, I'll pretty much guarantee it - the browser ONLY worries about the Content-type/MIME matchup, it doesn't peek inside the data to figure out how to handle it.
[ February 01, 2002: Message edited by: Tim Holloway ]

Customer surveys are for companies who didn't pay proper attention to begin with.
Steven Kors
Ranch Hand

Joined: Jan 30, 2002
Posts: 33
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<%
response.setContentType("application/vnd.ms-excel");
%>
<jsp:include page="/MyProject/servlet/CSVTable" >
<jsp:param name="ft" value="9" />
<jsp:param name="cc" value="14" />
<jsp:param name="fp" value="excel" />
</jsp:include>

I put this code into a JSP page. The CSVTable servlet outputs this CSV format...
Day,hubcaps,bumbers
Second Day,2982,5104
Third Day,3011,5220
Fourth Day,3055,5287
My web client does not see the table though when executed through the JSP page. When i execute the servlet on its own i see the content.
Sigh please Help =(... What i am doing wrong is probably in plain sight but i just cant see it.
Why if i execute the servlet i see the content but when i execute the servlet through the JSP page i dont see it?

Here is the servlet Code from the CSVTable servlet .. its really simple.

public void sendToExcel(PrintWriter out,String table,HttpServletRequest request,HttpServletResponse response)
throws ServletException, IOException
{
try {
table = "Day,hubcaps,bumbers\n"+
"Second Day,2982,5104\n"+
"Third Day,3011,5220\n"+
"Fourth Day,3055,5287\n";
out.println(table);
//response.setContentType("application/vnd.ms-excel");
} catch (Exception e) {
out.println("sendToExcel Error: " + e);
e.printStackTrace();
}
}
I even tried removing the carriage returns and have it as one big long row but that didnt work either ...
Sheesh :p
[ February 01, 2002: Message edited by: Steven K ]
Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
Try the following on for size:

Kyle


Kyle Brown, Author of Persistence in the Enterprise and Enterprise Java Programming with IBM Websphere, 2nd Edition
See my homepage at http://www.kyle-brown.com/ for other WebSphere information.
Steven Kors
Ranch Hand

Joined: Jan 30, 2002
Posts: 33
Kyle,
Thanks for the snippet but i still could not get it to work. When i execute this snippet, it asks me to download the JSP file. I tried the code in a servlet and then i tried the code in a JSP page and it still didnt work.
Sigh thanks for you time,
Steven
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: HTML table to an Excel Spreadsheet
 
Similar Threads
Using response.setContentType to switch HTML to Word
Setting content Type in JSP
Export to excel
Problem in exporting the jsp in excel
HTML Table to an Excel Spreadsheet