Using java to create Excel spreadsheet

[Chris Voyles]

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]

have you tried formatting your zip codes as CDATA ?

i.e.
<zip>
  <![CDATA[08227]]>
</zip>

[Chris Voyles]

I just tried putting that inside the <td> tags, and excel didn't recogneze it at all. This html:
<table>
<tr>
<td>01659
</td>
</tr>
</table>

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

[Paul Clapham]

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]

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:
collapse;table-layout:fixed;width:48pt'>
<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>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>

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

[Jan Groth]

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"

cheers,
jan

[Ulf Dittmer]

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.