You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Lo...@log-net.com on 2011/01/25 21:50:24 UTC
Large list in data validation produces error when sheet opens
I'm running into an issue when I put a large number of items(>25) in the
data validation list. My code:
prompt = truncateString(prompt,250,true); //excel limits
to ~255
validationMsg = truncateString(validationMsg,250,true);
//excel limits to ~255
org.apache.poi.ss.util.CellRangeAddressList addressList =
new org.apache.poi.ss.util.CellRangeAddressList(firstRow, lastRow,
firstCol, lastCol);
constraint =
validationHelper.createExplicitListConstraint(valList); //BOOM
dataValidation =
validationHelper.createValidation(constraint, addressList);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createPromptBox("Valid Values", prompt);
dataValidation.setShowPromptBox(true);
dataValidation.setShowErrorBox(true);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.WARNING);
dataValidation.createErrorBox("Validation Error",
validationMsg);
sheet.addValidationData(dataValidation);
When the sheet opens, I get a generic error message about a problem with
the sheet. When I click to go on, it removes the validation and opens as
normal.
I tried removing the prompt box, but the issue still occurs.
POI 3.7 on Win, jdk 1.5
Any ideas?
-Lou
Re: Large list in data validation produces error when sheet opens
Posted by Lo...@log-net.com.
OK, just for some closure - this is an Excel issue - the "Source" of a
validation is limited to 256 characters. I believe you can get around
this by using a range of cells as the source.
Thanks,
Lou
Louis.Masters@log-net.com wrote on 01/26/2011 12:20:16 PM:
> Thanks - good idea. I think I'll try that tonight.
>
> -Lou
>
> Mark Beardsley <ma...@tiscali.co.uk> wrote on 01/26/2011 12:13:37
PM:
>
> >
> > Oh well, just a thought because I had seen a few posting on the
internet
> > about problems with large lists.
> >
> > Nick's advice in cases like this is to create two files and compare
them
> to
> > see what POI is doing differently. Build the first file using POI and
> make
> > it as simple as possible, adding only those elements that will cause
the
> > problem. Next, create exactly the same file using Excel. Thirdly,
unzip
> both
> > - I am assuming you are creating xlsx files rather than xls ones here
-
> > using something like WinRAR or PKUnzip and then dig around within the
> files
> > internals to see if you can spot where Excel's and POI's files differ.
> If
> > you are creating the older binary file format xls files then you can
use
> the
> > BiffViewer utility to take a look at both and see any differences POI
is
> > able to detect. At the very least this will enable you to point the
> > developers in the right direction if you need to raise this issue as a
> bug.
> >
> > Good luck.
> >
> > Yours
> >
> > Mark B
> > --
> > View this message in context: http://apache-poi.1045710.n5.nabble.
> > com/Large-list-in-data-validation-produces-error-when-sheet-opens-
> > tp3356946p3358273.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
> >
Re: Large list in data validation produces error when sheet opens
Posted by Lo...@log-net.com.
Thanks - good idea. I think I'll try that tonight.
-Lou
Mark Beardsley <ma...@tiscali.co.uk> wrote on 01/26/2011 12:13:37 PM:
>
> Oh well, just a thought because I had seen a few posting on the internet
> about problems with large lists.
>
> Nick's advice in cases like this is to create two files and compare them
to
> see what POI is doing differently. Build the first file using POI and
make
> it as simple as possible, adding only those elements that will cause the
> problem. Next, create exactly the same file using Excel. Thirdly, unzip
both
> - I am assuming you are creating xlsx files rather than xls ones here -
> using something like WinRAR or PKUnzip and then dig around within the
files
> internals to see if you can spot where Excel's and POI's files differ.
If
> you are creating the older binary file format xls files then you can use
the
> BiffViewer utility to take a look at both and see any differences POI is
> able to detect. At the very least this will enable you to point the
> developers in the right direction if you need to raise this issue as a
bug.
>
> Good luck.
>
> Yours
>
> Mark B
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.
> com/Large-list-in-data-validation-produces-error-when-sheet-opens-
> tp3356946p3358273.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
>
Re: Large list in data validation produces error when sheet opens
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Oh well, just a thought because I had seen a few posting on the internet
about problems with large lists.
Nick's advice in cases like this is to create two files and compare them to
see what POI is doing differently. Build the first file using POI and make
it as simple as possible, adding only those elements that will cause the
problem. Next, create exactly the same file using Excel. Thirdly, unzip both
- I am assuming you are creating xlsx files rather than xls ones here -
using something like WinRAR or PKUnzip and then dig around within the files
internals to see if you can spot where Excel's and POI's files differ. If
you are creating the older binary file format xls files then you can use the
BiffViewer utility to take a look at both and see any differences POI is
able to detect. At the very least this will enable you to point the
developers in the right direction if you need to raise this issue as a bug.
Good luck.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Large-list-in-data-validation-produces-error-when-sheet-opens-tp3356946p3358273.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
Re: Large list in data validation produces error when sheet opens
Posted by Lo...@log-net.com.
Good thought. I tried that and Excel has no problem with the list. I'll
dig around some more.
Thanks,
Lou
Mark Beardsley <ma...@tiscali.co.uk>
01/26/2011 01:28 AM
Please respond to
"POI Users List" <us...@poi.apache.org>
To
user@poi.apache.org
cc
Subject
Re: Large list in data validation produces error when sheet opens
Excel imposes limits for a few things, is it possible to create such large
vaidations directly using the application itself rather than POI? I Think
that is the first thing to try and then, if Excel does allow this, look at
what POI could be doing wrong.
Yours
Mark B
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Large-list-in-data-validation-produces-error-when-sheet-opens-tp3356946p3357451.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
Re: Large list in data validation produces error when sheet opens
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Excel imposes limits for a few things, is it possible to create such large
vaidations directly using the application itself rather than POI? I Think
that is the first thing to try and then, if Excel does allow this, look at
what POI could be doing wrong.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Large-list-in-data-validation-produces-error-when-sheet-opens-tp3356946p3357451.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