You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by jamshid <ja...@gmail.com> on 2012/01/17 16:22:47 UTC
Problem creating an excel cell with Drop Down Lists
Hello,
I'm having problem with creating dropdown cells with many array items (this
is country list), It is actually working with approximately 100-150
countries but not with all countries. When there are more countries, the
comma deliminated countries string will be in a cell. I'm using the latest
version of POI 3.8 beta 5 and still having problem. Here is the code which
is the same with given here:
http://poi.apache.org/spreadsheet/quick-guide.html#Validation Data
Validations
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("Data Validation");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DVConstraint dvConstraint =
DVConstraint.createExplicitListConstraint(countries);
DataValidation dataValidation = new HSSFDataValidation (addressList,
dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);
How can I solve this problem ?
Thanks in advance!!!
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-excel-cell-with-Drop-Down-Lists-tp5151878p5151878.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: Problem creating an excel cell with Drop Down Lists
Posted by jamshid <ja...@gmail.com>.
Thank you Mr Yegor. I understood it, Excel itself does not allow
entering validation range string more than 255 characters, this was
not POI limitation. And now Named Ranges is working properly for me.
http://poi.apache.org/spreadsheet/quick-guide.html#NamedRanges
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-excel-cell-with-Drop-Down-Lists-tp5151878p5155356.html
Sent from the POI - User mailing list archive at Nabble.com.
Re: Problem creating an excel cell with Drop Down Lists
Posted by Yegor Kozlov <ye...@dinom.ru>.
POI does not limit the length of the entered constraints, all values
are written as a comma-separated list.
You can help us to research the problem.
Create two simple files: one created programmatically with POI and the
other manually in Excel . Then unzip the files and compare sheet data
( /xl/worksheets/sheet1.xml relative to the package root). You should
see the data validation entries and figure out why POI does it wrong.
It looked at the code and at first glance everything is good on the
POI side: there are no hardcoded limits, etc. Comparison with a valid
.xlsx file should shed a light on what's wrong.
Yegor
On Wed, Jan 18, 2012 at 5:00 PM, jamshid <ja...@gmail.com> wrote:
> After that I have tried with XSSFWorkBook with this code:
>
> XSSFWorkbook wb = new XSSFWorkbook();
> XSSFSheet sheet = wb.createSheet("new sheet");
> DataValidationHelper validationHelper = new XSSFDataValidationHelper(sheet);
> DataValidationConstraint constraint =
> validationHelper.createExplicitListConstraint(getCountries());
> CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
> DataValidation dataValidation =
> validationHelper.createValidation(constraint, addressList);
> dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
> dataValidation.setSuppressDropDownArrow(true);
> sheet.addValidationData(dataValidation);
> FileOutputStream fileOut = new FileOutputStream("c:\\test.xlsx");
>
> Unfortunately, no success with such result which is comma delimenated long
> string in one cell:
>
> [image: enter image description here]
>
> But manually in excel, I can create dropdown cells with long this country
> list.
> Is there any way generate dropdown with long strings, or API does not
> support?
>
> On Wed, Jan 18, 2012 at 3:25 AM, jamshid [via Apache POI] <
> ml-node+s1045710n5153136h94@n5.nabble.com> wrote:
>
>> Hi, thank you for quick reply
>> Sorry I confused giving explanation because I've done with XSSF in that
>> time I got comma deliminated String but when I do it with HSSF I'm getting
>> *String literals in formulas can't be bigger than 255 characters ASCII*
>> for this code:
>>
>> try {
>> HSSFWorkbook wb = new HSSFWorkbook();
>> HSSFSheet sheet = wb.createSheet("new sheet");
>> HSSFRow row = sheet.createRow((short) 0);
>> //CellRangeAddressList from org.apache.poi.ss.util package
>> CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0,
>> 0);
>> DVConstraint dvConstraint =
>> DVConstraint.createExplicitListConstraint(getCountries());
>> DataValidation dataValidation = new HSSFDataValidation(addressList,
>> dvConstraint);
>> dataValidation.setSuppressDropDownArrow(false);
>> sheet.addValidationData(dataValidation);
>> FileOutputStream fileOut = new FileOutputStream("c:\\test.xls");
>> wb.write(fileOut);
>> fileOut.close();
>> } catch (IOException e) {
>> e.printStackTrace();
>> }
>>
>>
>> http://stackoverflow.com/questions/8655185/limitation-while-generating-excel-drop-down-list-with-apache-poi
>>
>>
>>
>> ------------------------------
>> If you reply to this email, your message will be added to the discussion
>> below:
>>
>> http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-excel-cell-with-Drop-Down-Lists-tp5151878p5153136.html
>> To unsubscribe from Problem creating an excel cell with Drop Down Lists, click
>> here<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5151878&code=amFtc2hpZC5hc2F0aWxsYXlldkBnbWFpbC5jb218NTE1MTg3OHwxODU3MDg5MTI2>
>> .
>> NAML<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.InstantMailNamespace&breadcrumbs=instant+emails%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-excel-cell-with-Drop-Down-Lists-tp5151878p5154747.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: Problem creating an excel cell with Drop Down Lists
Posted by jamshid <ja...@gmail.com>.
After that I have tried with XSSFWorkBook with this code:
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("new sheet");
DataValidationHelper validationHelper = new XSSFDataValidationHelper(sheet);
DataValidationConstraint constraint =
validationHelper.createExplicitListConstraint(getCountries());
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DataValidation dataValidation =
validationHelper.createValidation(constraint, addressList);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(dataValidation);
FileOutputStream fileOut = new FileOutputStream("c:\\test.xlsx");
Unfortunately, no success with such result which is comma delimenated long
string in one cell:
[image: enter image description here]
But manually in excel, I can create dropdown cells with long this country
list.
Is there any way generate dropdown with long strings, or API does not
support?
On Wed, Jan 18, 2012 at 3:25 AM, jamshid [via Apache POI] <
ml-node+s1045710n5153136h94@n5.nabble.com> wrote:
> Hi, thank you for quick reply
> Sorry I confused giving explanation because I've done with XSSF in that
> time I got comma deliminated String but when I do it with HSSF I'm getting
> *String literals in formulas can't be bigger than 255 characters ASCII*
> for this code:
>
> try {
> HSSFWorkbook wb = new HSSFWorkbook();
> HSSFSheet sheet = wb.createSheet("new sheet");
> HSSFRow row = sheet.createRow((short) 0);
> //CellRangeAddressList from org.apache.poi.ss.util package
> CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0,
> 0);
> DVConstraint dvConstraint =
> DVConstraint.createExplicitListConstraint(getCountries());
> DataValidation dataValidation = new HSSFDataValidation(addressList,
> dvConstraint);
> dataValidation.setSuppressDropDownArrow(false);
> sheet.addValidationData(dataValidation);
> FileOutputStream fileOut = new FileOutputStream("c:\\test.xls");
> wb.write(fileOut);
> fileOut.close();
> } catch (IOException e) {
> e.printStackTrace();
> }
>
>
> http://stackoverflow.com/questions/8655185/limitation-while-generating-excel-drop-down-list-with-apache-poi
>
>
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-excel-cell-with-Drop-Down-Lists-tp5151878p5153136.html
> To unsubscribe from Problem creating an excel cell with Drop Down Lists, click
> here<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5151878&code=amFtc2hpZC5hc2F0aWxsYXlldkBnbWFpbC5jb218NTE1MTg3OHwxODU3MDg5MTI2>
> .
> NAML<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.InstantMailNamespace&breadcrumbs=instant+emails%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-excel-cell-with-Drop-Down-Lists-tp5151878p5154747.html
Sent from the POI - User mailing list archive at Nabble.com.
Re: Problem creating an excel cell with Drop Down Lists
Posted by jamshid <ja...@gmail.com>.
Hi, thank you for quick reply
Sorry I confused giving explanation because I've done with XSSF in that time
I got comma deliminated String but when I do it with HSSF I'm getting
*String literals in formulas can't be bigger than 255 characters ASCII*
for this code:
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFRow row = sheet.createRow((short) 0);
//CellRangeAddressList from org.apache.poi.ss.util package
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DVConstraint dvConstraint =
DVConstraint.createExplicitListConstraint(getCountries());
DataValidation dataValidation = new HSSFDataValidation(addressList,
dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);
FileOutputStream fileOut = new FileOutputStream("c:\\test.xls");
wb.write(fileOut);
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
http://stackoverflow.com/questions/8655185/limitation-while-generating-excel-drop-down-list-with-apache-poi
http://stackoverflow.com/questions/8655185/limitation-while-generating-excel-drop-down-list-with-apache-poi
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-excel-cell-with-Drop-Down-Lists-tp5151878p5153136.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: Problem creating an excel cell with Drop Down Lists
Posted by Lo...@log-net.com.
I ran into an issue when loading my validations with many items. This may
help:
http://apache-poi.1045710.n5.nabble
.com/Large-list-in-data-validation-produces-error-when-sheet-opens-tp3356946p3358273.html
-Lou
jamshid <ja...@gmail.com> wrote on 01/17/2012 10:22:47 AM:
> jamshid <ja...@gmail.com>
> 01/17/2012 10:24 AM
>
> Please respond to
> "POI Users List" <us...@poi.apache.org>
>
> To
>
> user@poi.apache.org
>
> cc
>
> Subject
>
> Problem creating an excel cell with Drop Down Lists
>
> Hello,
> I'm having problem with creating dropdown cells with many array items
(this
> is country list), It is actually working with approximately 100-150
> countries but not with all countries. When there are more countries, the
> comma deliminated countries string will be in a cell. I'm using the
latest
> version of POI 3.8 beta 5 and still having problem. Here is the code
which
> is the same with given here:
> http://poi.apache.org/spreadsheet/quick-guide.html#Validation Data
> Validations
>
> Workbook workbook = new HSSFWorkbook();
> Sheet sheet = workbook.createSheet("Data Validation");
> CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0,
0);
> DVConstraint dvConstraint =
> DVConstraint.createExplicitListConstraint(countries);
> DataValidation dataValidation = new HSSFDataValidation (addressList,
> dvConstraint);
> dataValidation.setSuppressDropDownArrow(false);
> sheet.addValidationData(dataValidation);
>
> How can I solve this problem ?
> Thanks in advance!!!
>
> --
> View this message in context: http://apache-poi.
> 1045710.n5.nabble.com/Problem-creating-an-excel-cell-with-Drop-Down-
> Lists-tp5151878p5151878.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: Problem creating an excel cell with Drop Down Lists
Posted by Yegor Kozlov <ye...@dinom.ru>.
Please post the full source code so that we can reproduce the problem
on our side.
Yegor
On Tue, Jan 17, 2012 at 7:22 PM, jamshid <ja...@gmail.com> wrote:
> Hello,
> I'm having problem with creating dropdown cells with many array items (this
> is country list), It is actually working with approximately 100-150
> countries but not with all countries. When there are more countries, the
> comma deliminated countries string will be in a cell. I'm using the latest
> version of POI 3.8 beta 5 and still having problem. Here is the code which
> is the same with given here:
> http://poi.apache.org/spreadsheet/quick-guide.html#Validation Data
> Validations
>
> Workbook workbook = new HSSFWorkbook();
> Sheet sheet = workbook.createSheet("Data Validation");
> CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
> DVConstraint dvConstraint =
> DVConstraint.createExplicitListConstraint(countries);
> DataValidation dataValidation = new HSSFDataValidation (addressList,
> dvConstraint);
> dataValidation.setSuppressDropDownArrow(false);
> sheet.addValidationData(dataValidation);
>
> How can I solve this problem ?
> Thanks in advance!!!
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-excel-cell-with-Drop-Down-Lists-tp5151878p5151878.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
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org