Bug 40283 - Cells which control radio buttons get reset when read by Excel
Summary: Cells which control radio buttons get reset when read by Excel
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.5-FINAL
Hardware: Other All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2006-08-18 10:39 UTC by Gareth Smith
Modified: 2014-09-01 10:34 UTC (History)
1 user (show)

XLS file configured to expose this behaviour (8.00 KB, application/octet-stream)
2006-08-18 10:40 UTC, Gareth Smith
Test class which will expose the problem (2.13 KB, text/plain)
2006-08-18 10:41 UTC, Gareth Smith

Note You need to log in before you can comment on or make changes to this bug.
Description Gareth Smith 2006-08-18 10:39:33 UTC
To reproduce this behaviour: Create a workbook containing a named range on one
worksheet and a group of radio buttons which are governed by the named range on
another worksheet.

Read the workbook using POI, set the value of the named cell to be 1 (this
should then update the radio buttons and result in one of them having been
selected) and then write the file out again. When you load up the output file in
Excel the radio button is set correctly, however the named cell now has the same
value which the input file was saved with (if the input file had no value set
then the cell withh have the value 0). If you save the .xls file with the named
cell selected then when you open the file after it has been passed through POI
it is sometimes possible to observe the cell having the value 1 in it before the
sheet is updated by Excel and the value is reverted to what it was before POI
read the file.

The radio buttons on the output file still function correctly; the only issue is
that the named range has its value reverted when the file is opened. I assume
that this issue is caused by the synchronisation between the cell setting the
value of the radio buttons and the radio buttons setting the value of the cell.

It appears that this is probably an issue with the way Excel synchronises form
controls rather than POI having corrupted the data, however I will attach an
example file and test which exposes this behaviour just in case and if there is
a known work-around for this issue then any feedback would be greatly appreciated.

Thanks for your continued work on this project,
Comment 1 Gareth Smith 2006-08-18 10:40:50 UTC
Created attachment 18732 [details]
XLS file configured to expose this behaviour
Comment 2 Gareth Smith 2006-08-18 10:41:46 UTC
Created attachment 18733 [details]
Test class which will expose the problem
Comment 3 Gareth Smith 2006-08-18 10:52:50 UTC
I forgot to mention: This test uses POI 2.5.1 final (poi-2.5.1-final-20040804.jar). 

I tried using the latest POI alpha (poi-3.0-alpha2-20060616.jar) however this
caused Excel to fail to load the file propely and "repair" the workbook,
resulting in the form controls and all cell formatting being lost.
Comment 4 Dominik Stadler 2014-09-01 10:34:41 UTC
Still happens with current poi 3.11-beta2/trunk, one difference in the BiffViewer output seems to be the type of record for the number-value:

Originally it is a RKRecord

Offset=0x00001077(4215) recno=192 sid=0x027E size=0x000A(10)
    .row    = 0x0001
    .col    = 0x0001
    .xfindex= 0x001E
  .value= 2.0

but POI only supports reading those and thus converts this to a 

Offset=0x0000105E(4190) recno=192 sid=0x0203 size=0x000E(14)
    .row    = 0x0001
    .col    = 0x0001
    .xfindex= 0x001E
  .value= 3

Not sure if this is the culprit here, but it seems to be related as no other big change is visible int he Biff-output