RE: POI/HSSF default cell formatting

Hi John,

Thanks for the link.  Actually a couple of the POI/HSSF developers have
responded -- it's a bug (issue# 35799).  Apparently when you create a new
cell object, they are setting the cell style.  If they do NOT set the cell
style, Excel will use the proper default.

The column formats are stored in global records (i.e. part of the workbook,
not the worksheet), and are referred to by an index (more like a key than an
array index -- each format record has the index value in it and it has
nothing to do with their position within the substream).  The ColumnInfo
records contain a starting and ending column#, plus the format record index,
so yes,  they do apply to the entire column.  However, according to the
documentation

"Default formatting is applied to all cells which are not described by a
cell record. In this case the default format of the row or column may be
used (if defined). If an undefined cell contains a row and a column default
format, the row format will overwrite the column format. If there are no row
and column default formats available, the worksheet/workbook default cell
format will be used."

Or in the words of one developer:

"IIRC: Columns have styles, rows override column styles, cell styles
override both.  We're setting  a cell style on every cell we create...bad
us!"

He also echoed your sentiment ("ugh") of my solution, but agreed it would
work, although the idea of setting a particular cell style for each new cell
didn't quite seem right, since it basically continues their error of
assigning a cell style when none is needed.  And I can't use the formatting
from a previous row, since none exists at the time I'm adding cells.

I'm going to try getting the source, applying the patch and seeing if I can
build this thing since it looks like they didn't do a build last night.

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 John
>  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?fusea
> ction=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

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.9.2/52 - Release Date: 7/19/2005