the contentType attribute

[ashiers]

Hi there,

I'm working on a JSP page that displays a report and wanted to allow the user to display the content either in HTML (the default) or as an Excel spreadsheet. According to Marty Halls book, Core Servlets and JavaServer Pages, Excel is able to interpret HTML tables and all one needs to do is simply set the contentType to "application/vnd.ms-excel".

<%
String format = request.getParameter("format");
if((format != null) && (format.equals("excel")))
{
response.setContentType("application/vnd.ms-excel");
}
%>

So I did, and it worked, except for one little glitch. In the first column labelled "Days Missed", in the report, I have strings that are either 1-3, or 4-6 or 8-10 (obviously these indicate a range of days missed). When Excel loads these values it mistakenly displays them as date fields: 01-Mar, 04-Jun, 07-Sep.

Is there some way to rig this so that Excel doesn't interpret the strings as dates?

Alan

[ractoc]

ad a tick (`) in front of the 1-3 value
this tells excell to interpret it as a string

[ashiers]

Hi ractoc,

I tried your suggestion. It worked, kinda. I placed the tick character in front of the values like so: `1-3, or `4-6, etc.

I thought the tick "`" was some kind of escape character and wouldn't appear when Excel parsed it. But it does appear. I can probably live with this but if there is a better way to make Excel interpret 1-3, or 4-6 as strings without any additional characters showing, I'd prefer that.

Please advise,

Alan

[ractoc]

you could also look up the jxl.ja library
This lets you create real excell file, instead of comma seperated files or HTML collumns
Using this library, you can actually tell excell what type of data is in each cell.