RE: POI/HSSF default cell formatting

Peter,

 Have you seen Scott Klement's article on parsing Excel sheets in the Jan
2004 iSeries Network newsletter?
http://www.iseriesnetwork.com/resources/artarchive/index.cfm?fuseaction=view
article&CO_ContentID=17839&channel=art&subart=issue&issueid=917

 Although the actual techniques are beyond my ken, conceptually it seems
that one could extends Scott's 'ParseSheetListener.java' program to also
monitor for the 'ColumnInfoRecord's. For each record returned, use
getXFIndex to retrieve the ExtendedFormatRecord that contains all the juicy
bits. Use that data to populate the array in your RPG program. Ugh.

 The larger question in my mind is how to ensure that the ColumnInfoRecord
is accurate for the entire column. My understanding is that this record
contains the column's defaults - which could be entirely different than how
the data in the body of the sheet is formatted. Assuming the sheet already
has some data, perhaps it would be safer to search out the last populated
row, clone it and overwrite the data?

Good luck, JK

-----Original Messa
date: Tue, 19 Jul 2005 10:34:30 -0700
from: "Peter Dow \(ML\)" <maillist@xxxxxxxxxxxxxxx>
subject: RE: POI/HSSF default cell formatting

Hi Leif,

Thanks, I've done that too (thanks Scott!) when I'm in control of the
formatting.  In this case however, I'm reading an existing spreadsheet where
a user has set up some formatting and I have no idea what it might be, it's
done in Excel by selecting the columns then doing ctl-1 (or going to the
Format, Cell menu).  Since the rows and cells have not been created yet, I
can't extract any existing HSSFCellStyle object from them.  The information
appears to be in ColumnInfo records in the spreadsheet, and the HSSF
usermodel doesn't have any classes or methods to extract those records or
get the formatting.  Nor have I seen anything on the jakarta website that
does.  I think I may be in the "to do" section of the project...

Peter Dow
Dow Software Services, Inc.
www.dowsoftware.com
909 793-9050 voice
909 793-4480 fax



-----Original Message-----
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Leif Guldbrand
Sent: Tuesday, July 19, 2005 6:30 AM

I'll try to show what I have done, formatting new cells.
I have five different formats and the standard format for cells.
I have used Scott Klements articles and examples - and his help to do it :-)