Peter Dow (DSS) wrote:
Hi Andy,
This is definitely a learning experience for me -- learning a little about
Java through an RPG lens. I'm getting the idea that an instantiation of a
Java object is really just a pointer to data in the stream file that is an
Excel spreadsheet; my initial understanding was that it was a copy of the
data. For example, an HSSFCell object -- is it a pointer to the Cell record
in the stream, or is it a copy of the Cell record, or is it something else
entirely?
Well it is a copy, but the HSSFCellStyle is just a reference to the
underlying cell style record.
My idea was that I could make one pass through the worksheet by using the
HSSFEventFactory, saving HSSFCellStyle objects, then open the same workbook
and apply those saved cell style objects to the new cells. Hmm. Seems like
in either case, the cell styles are in the worksheet (since it's the same
one) and I'd just have to apply them to the new cell with the setCellStyle
method. Am I anywhere close to reality with this?
Nope, you can't do that, IIRC. The HSSFCellStyle records point to
styles in the underlying record structures (our low
level representation of the binary structures). Since cell styles are
actually stored at the workbook level, its just an
abstraction to keep you from having to go get the style off of the
workbook. The actual cell binary record just has an index
to the styles in the workbook (0...1...2...3...there is no 4 don't ask
why!...5...6...7...). The copying the HSSFCellStyle record would be
meaningless because it would point to an index of a style that might be
in a different place.....it would probably work much of the time.
What you really need to accomplish what you're trying to accomplish
(preserve default column style for new columns):
1. us to fix the bug with new cells always overriding the default column
style/row style
2. us to expose default column style/default row style
3. to use use usermodel for modify operations.
Apparently Java can call an RPG program (I found an example but haven't
tried it); so it seems possible that it could also call a method (aka a
procedure). The main issue is converting data formats -- e.g. Java strings
are not the same as RPG variable length character fields (the closest thing
to a string in RPG); same for the various numeric data types RPG supports
(packed decimal, zoned decimal, floating point, signed & unsigned integers,
etc.)
Yes, you could create an event listener in Java that called your RPG
method. Munging between data types could probably be done
in Java. (meaning turn it into char arrays)
Amol Deshmukh has also replied to this thread, noting a possible related bug
(issue #35799), for which he has a patch. I'm going to see if I can figure
out how to apply the patch and build from the source (or build from the
source and apply the patch).
yes.
One last note, while reading "OpenOffice.org's Documentation of the
Microsoft Excel File Format", I came across section 5.6.2:
5.6.2 Default Formatting
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.
Default column formatting is stored in the COLUMNDEFAULT record
(BIFF2, ->6.19) or in the COLINFO record
(BIFF3-BIFF8, ->6.18). The ROW record (->6.83) contains the default
format
of a specific row. The default cell format
is always present in an Excel file, described by the XF record with the
fixed index 15 (0-based). By default, it uses the
worksheet/workbook default cell style, described by the very first XF
record (index 0).
which seems to imply that if I create a cell and do not apply a specific
style to it, it will use the default row or column format if one or the
other is available. Is this something Excel is doing? Or is it what's
supposed to happen when a cell is created?
That is an excel thing. We're defaulting to overriding it by setting
cell style on each cell. You're then trying to "un-override" it.
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!
-Andy
Peter Dow
Dow Software Services, Inc.
www.dowsoftware.com
909 793-9050 voice
909 793-4480 fax
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 19, 2005 2:35 PM
To: [email protected]
Subject: Re: ColumnInfo records
Peter Dow (DSS) wrote:
Hi Andy,
Btw, thanks for all the work you and the other people have done on this
project! It's a lot of fun using it.
Thanks!
My intent was to read (only) the ColumnInfoRecords and save the
HSSFCellStyle objects for each column when I initially open the worksheet.
Later I'd use that information to apply the cell style to a newly created
HSSFCell object. I assumed I'd need the number of ColumnInfo records, but
it looks like with the eventmodel I just register a listener for the
ColumnInfo record only (recordsid = 0x7d, right?) and let the event
factory
call me whenever it reads a ColumnInfo record in the stream.
So if you're MODIFYING a sheet you CANNOT use the event model at all.
If you're just reading a sheet for some styles to apply then you can't
just use the objects (they're reference objects to things in the actual
sheet, you can't just apply them in a new sheet). You'd need to create
styles. You can get and set the column width, for example, using the
HSSFSheet.
It looks like we have not exposed the default column style (I for some
reason hallucinate that I coded this some time ago) through the user
model. That should be corrected.
You are correct that RPG has no concept of objects, but I am able to
perform
methods in Java classes and receive values from them; what I'm not certain
of is registering a listener. You're probably right in saying that I'll
have to have some Java wrapper that acts as the listener and somehow gets
the data back to my RPG program
Can the JAva code call an RPG method?
.
This has me curious though -- how does Excel do this? When an empty
worksheet has column formatting applied to it, and I put a value in a cell
in one of those columns, the formatting is applied. Is Excel doing
something similar do you suppose? It would be nice if the createCell
method
did this for me.
There is "default column style", "default row style" and "cell style".
When you apply a style to HSSFCell it is the latter. Curious that no
one ever added a way to manipulate the former two. If someone wants an
easy task in order to get involved this is a good one. Otherwise I'll
get around to it shortly.
-Andy
Peter Dow
Dow Software Services, Inc.
www.dowsoftware.com
909 793-9050 voice
909 793-4480 fax
-----Original Message-----
From: Andrew C. Oliver [mailto:[EMAIL PROTECTED]
You can't get the ColumnInfoRecordsAggregate. You get the
ColumnInfoRecord. However you shouldn't be programming at this level
generally for non-read only uses. Meaning just use "usermodel" for all
of your read/write apps and only use the event stuff for read only and
don't worry about the record, eventmodel, etc packages (stay usermodel).
I have no knowledge of RPG (came from C and assembler), however its just
a callback w/interface. Not sure how you call to RPG from Java or vice
versa, and I imagine RPG has no concept of "objects" and "interfaces" so
I'd imagine you'd create a small wrapper in Java that called your RPG
program passing the value back.
--
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
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
.
--
Andrew C. Oliver
SuperLink Software, Inc.
Java to Excel using POI
http://www.superlinksoftware.com/services/poi
Commercial support including features added/implemented, bugs fixed.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.9.2/52 - Release Date: 7/19/2005
--
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
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
.
--
Andrew C. Oliver
SuperLink Software, Inc.
Java to Excel using POI
http://www.superlinksoftware.com/services/poi
Commercial support including features added/implemented, bugs fixed.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
