jQuery in Action, 3rd edition
The moose likes Java in General and the fly likes Using java to create Excel spreadsheet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "Using java to create Excel spreadsheet" Watch "Using java to create Excel spreadsheet" New topic

Using java to create Excel spreadsheet

Chris Voyles

Joined: Mar 31, 2005
Posts: 7
I wrote a java program that inputs a text file that is delimited by asterisks, and outputs a text file containing an html table. Since Excel recognizes html, this works great. My problem is that a zip code field drops leading zeroes. Is there any sort of formatting that I can use to let Excel know to treat this cell as text?
Michael Valentino
Ranch Hand

Joined: Nov 01, 2005
Posts: 96
have you tried formatting your zip codes as CDATA ?

SCJP 1.4, SCWCD J2EE 1.4, SCJD J2SE 1.5, SCBCD J2EE 1.3, SCDJWS (In Progress)
Chris Voyles

Joined: Mar 31, 2005
Posts: 7
I just tried putting that inside the <td> tags, and excel didn't recogneze it at all. This html:

saved as a .xls file, opened in Excel, has one cell that shows 1659 as the data.
Paul Clapham

Joined: Oct 14, 2005
Posts: 19973

Have you tried reverse-engineering? Create a spreadsheet, input a number as text in cell A1. Save it as HTML and see what you get.
Chris Voyles

Joined: Mar 31, 2005
Posts: 7
Good idea. I don't have it completely figured out yet, but Excel created a header specifying that the table type was Excel, and I suspect that is what I'll need to do to fix it. Simply copying the table part of the file into a new .xls file didn't do it:

<table x:str border=0 cellpadding=0 cellspacing=0 width=64 style='border-collapse:
<col width=64 style='width:48pt'>
<tr height=17 style='height:12.75pt'>
<td height=17 width=64 style='height:12.75pt;width:48pt' x:str="'01234">01234</td>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>

Thanks for your suggestion. I feel as though I'm on the path towards a solution.
Jan Groth
Ranch Hand

Joined: Feb 03, 2004
Posts: 456
if you want / need to take the long road:

"apache poi" lets you create and modify office documents. i did some more or less complicated export / import functionality with it, the effort is reasonable.

might want to check their website, they have a quickstart guide for "coders in a hurry"

Ulf Dittmer

Joined: Mar 22, 2005
Posts: 42965
Creating an HTML file and calling it ".xls" is likely to be unstable at best. Using POI (which is not difficult to learn) you can specify that a particular cell contains text, not numbers, and thus avoid having Excel mess around with its contents unpredictably.
I agree. Here's the link: http://aspose.com/file-tools
subject: Using java to create Excel spreadsheet
It's not a secret anymore!