application/vnd.ms-excel and leading 0's

[brownjenkn]

I see it's been posted before but haven't seen a good solution for this
one:

I've got a "Results.jsp" that outputs some query results from a DB2 DB.
I actually have a custom tag in the .jsp that does the actual query.
I output everything with a PrintWriter object and set the response
content-type to application/vnd.ms-excel. Some of these values may
look like "01010" or "01234" but they're not integers. I treat them as
String(s) in the code. When the output gets sent to excel, the leading
0 always gets dropped off so I have "1010" or "1234".

Anybody have some advice on how to fix this?

Your help is much appreciated.

TIA,

Marc

 

[Roland]

I see it's been posted before but haven't seen a good solution for this
one:

I've got a "Results.jsp" that outputs some query results from a DB2 DB.
I actually have a custom tag in the .jsp that does the actual query.
I output everything with a PrintWriter object and set the response
content-type to application/vnd.ms-excel. Some of these values may
look like "01010" or "01234" but they're not integers. I treat them as
String(s) in the code. When the output gets sent to excel, the leading
0 always gets dropped off so I have "1010" or "1234".

Anybody have some advice on how to fix this?

Your help is much appreciated.

TIA,

Marc

Try to precede them with a single quote. When you enter a cell value in
Excel that starts with a quote, Excel treats it as a string instead of a
number, e.g.
'01234 (displayed left aligned 01234)
'00010 (similar: left aligned)
[I'm not sure whether you are actually creating an Excel file, i.e. in
binary .XLS format, or a comma/tab separated text file (.CSV). A
PrintWriter would be OK for the latter, but inappropriate for the
former, IMO.]
--
Regards,

Roland de Ruiter
` ___ ___
`/__/ w_/ /__/
/ \ /_/ / \

 

[Joan]

I see it's been posted before but haven't seen a good solution for this
one:

I've got a "Results.jsp" that outputs some query results from a DB2 DB.
I actually have a custom tag in the .jsp that does the actual query.
I output everything with a PrintWriter object and set the response
content-type to application/vnd.ms-excel. Some of these values may
look like "01010" or "01234" but they're not integers. I treat them as
String(s) in the code. When the output gets sent to excel, the leading
0 always gets dropped off so I have "1010" or "1234".

Anybody have some advice on how to fix this?

Your help is much appreciated.

I might be wrong, but doesn't a PrintWriter output character data?
Excel's normal file type has file extension "xls" and is binary, BIFF they
call it.
If you know enough about BIFF to get Excel to open your binary file
you probably know what your problem is. The format specifier for the
cell in question is not set to display leading zeros for numeric data types.
I suspect you are writing character data in CSV format. When Excel reads
a CSV file it converts it to internal format and chooses the "general"
numeric
type for cells that contain numbers. This format does not display leading
zeros.
You can change the format once Excel opens the file using the "format" menu
item.
If text format rather than numeric is ok, you can ouptput your numbers as
strings enclosed by double quotes.