HSSF Sheet DV corruption

[Mahesh Ganapathy <mg...@gmail.com>]

Hello All,
I am generating a sheet with a lot of named references one one sheet
which is very hidden. In the second sheet which is visible to the
users, the cells have data validation referencing (direct references
and indirect references) the named cells on the very hidden sheet.
This provides the user's with value lists for each of the cells to
assist in data entry. Now when the user's try to regenerate the sheet,
the system has to update the values in the named ranges, while
retaining the data in the user editable worksheet. I took the easiest
approach which was to delete this very hidden sheet and create a new
one with the same name and same named ranges. But in the resulting
excel document, the datavalidation using direct references seems to be
corrupted. For example, a column called country, has a data validation
list that refers to the named range "country" and another column
callled project, which depends on the country selected in the previous
colum therefore having the validation =INDIRECT(SUBSTITUTE($C$7"
","")) . In the regenerated worksheet, the country validation (direct
reference) points to a random named range while the project data
validation (using the indirect reference) stays the way it is.
I am using POI 3.2 FINAL with JDK 1.4.2_10 on the server.

My questions are:
1. Is this a bug in POI or is this how things are expected to work
with this approach.
2. If this is not a bug, what would a better approach be?

Thanks,
Mahesh Ganapathy

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: HSSF Sheet DV corruption

[MSB <ma...@tiscali.co.uk>]

Hello Mahesh,

If you are still struggling with this problem drop then I am quite happy to
try and help you out; just reply if you need help. Forgive me for saying
this but I will need to ask you quite a few questions because I am not at
all sure from your original post where the problem lies; I suspect that it
has to do with your formulae if I do understand correctly.

Yours

Mark B


Mahesh Ganapathy wrote:
> 
> Hello All,
> I am generating a sheet with a lot of named references one one sheet
> which is very hidden. In the second sheet which is visible to the
> users, the cells have data validation referencing (direct references
> and indirect references) the named cells on the very hidden sheet.
> This provides the user's with value lists for each of the cells to
> assist in data entry. Now when the user's try to regenerate the sheet,
> the system has to update the values in the named ranges, while
> retaining the data in the user editable worksheet. I took the easiest
> approach which was to delete this very hidden sheet and create a new
> one with the same name and same named ranges. But in the resulting
> excel document, the datavalidation using direct references seems to be
> corrupted. For example, a column called country, has a data validation
> list that refers to the named range "country" and another column
> callled project, which depends on the country selected in the previous
> colum therefore having the validation =INDIRECT(SUBSTITUTE($C$7"
> ","")) . In the regenerated worksheet, the country validation (direct
> reference) points to a random named range while the project data
> validation (using the indirect reference) stays the way it is.
> I am using POI 3.2 FINAL with JDK 1.4.2_10 on the server.
> 
> My questions are:
> 1. Is this a bug in POI or is this how things are expected to work
> with this approach.
> 2. If this is not a bug, what would a better approach be?
> 
> Thanks,
> Mahesh Ganapathy
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
> 
> 
> 

-- 
View this message in context: http://old.nabble.com/HSSF-Sheet-DV-corruption-tp27163527p27198254.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org