Dear all,
To go straight to the topic, an undocumented record type (1C1h) seems
to be crucial for Excel when dealing with cells that reference other
cells, in order to decide whether they should be recomputed or not.
While this may sounds truly bizarre to you, there is a difference
between an unmodified file saved either ONE or TWO (or more) times
within Excel:
File saved one time:
============================================
Offset 0x5bd (1469)
recordid = 0x1c1, size =8
[UNKNOWN RECORD]
.id = 1c1
[/UNKNWON RECORD]
-----UNKNOWN----------------------------------
00000000 C1 01 00 00 80 38 01 00 .....8..
============================================
File saved two times:
============================================
Offset 0x5bd (1469)
recordid = 0x1c1, size =8
[UNKNOWN RECORD]
.id = 1c1
[/UNKNWON RECORD]
-----UNKNOWN----------------------------------
00000000 C1 01 00 00 60 69 01 00 ....`i..
============================================
Background information
----------------------
I am creating a reporting system that takes an Excel template as
input, modifies some values within one sheet (named InvestView Link in
my examples), and produce a XLS file. A second sheet contains
references (style 'InvestView Link'!A1) to the POI generated sheet.
The problem is that when cell values are modified using POI, the value
of the cell that reference these cells are not updated.
I did run all my test with both POI v. 1.5.1 and v. 1.8.0-dev. All the
files I am referring to are available at:
http://www.macrofocus.com/tmp/1c1/
Use case
--------
I create a template file and saved it ONE time (template-good.xls) or
TWO times (template-bad.xls) with Excel 2000.
Then I use POI to load these two templates, get the sheet named
'InvestView Link' and modify some values. The files I am producing are
modified-good-1.X.xls and modified-bad-1.X.xls whether I am using the
first or second template as input. When you open these files with
Excel, you will see that the values of the cells with references (in
Sheet1) are correct in the first case, but not in the second (that's
the problem I would like to have solved).
To analyze what has changed during the previous process, I produced
the files untouch-*.xls, which have been produced using the same
process, except that no value has been modified (i.e. they have
been set using the same value).
Note that files generated with POI v. 1.8-dev have problems when
opened in Excel (File error: data may have been lost). I guess this
problem is unrelated, but you may want to have a look at it too...
Resolution
----------
I believe the record 1C1h is holding values to decide which cell has
to be recomputed. It would be good to have POI handle this properly,
either automatically or by providing a method to force the update the
content of a sheet. What do you think? I am desperately seeking help
on this issue.
So long,
Luc
--
Luc Girardin Macrofocus GmbH, Wasserwerkstr. 10, CH-8006 Zurich
email: [EMAIL PROTECTED]
phone: +41 1 350 52 18
fax: +41 1 350 52 19
web: http://www.macrofocus.com/
Where in the world should you travel - or perhaps even relocate - to
pay less, earn more, and improve your living conditions? Find it out
with City'O'Scope, available at http://www.macrofocus.com/cityoscope/
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
