You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by dpront <ge...@xmarter.com> on 2011/07/11 10:14:04 UTC
Read drop down list data
Hi,
I have searched the forum regarding this topic and can't seem to find
something that can help me out. The question is, is Apache POI capable of
reading the values of a cell that has a drop down list?
What I wanted to achieve is to read the excel file and get the datas on
the drop down list and turn it into a java object (Iterate in a certain row
and return the values of drop down list per cell).
I haven't written any code since I dont know where to start. Can someone
point me into the right direction or what method should I needed to look
into?
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Read-drop-down-list-data-tp4574232p4574232.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: Read drop down list data
Posted by dpront <ge...@xmarter.com>.
Hi,
I am working on a XSSF format, thanks for the enlightenment though. I
think I can now start coding.
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Read-drop-down-list-data-tp4574232p4574531.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: Read drop down list data
Posted by dpront <ge...@xmarter.com>.
Hello,
Thank you so much for that link. I did get the values that I wanted (for
the latter part, haven't tried the first part of your suggestion) but I get
errors on some of the formula that I am passing on the Formula Cell
Reference.
My code as of now is:
http://privatepaste.com/3c22f04489
And the output is:
http://privatepaste.com/7884dfbced
There's two errors on the last link because I tried other values on it.
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Read-drop-down-list-data-tp4574232p4581337.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: Read drop down list data
Posted by Alex Panayotopoulos <Al...@vianet.co.uk>.
Hi,
It looks like some sort of dependent list;
http://www.contextures.com/xlDataVal02.html
As far as I'm aware there's no "ready rolled" POI solution to this; it
looks like you'll have to parse the formula yourself, query the values
of the cells referenced ("AU10" and "AW10" in your example -- the
CellReference class may help), and then retrieve the resultant named
range. The "busy developers' guide" tells you how to do the last bit:
http://poi.apache.org/spreadsheet/quick-guide.html#NamedRanges
(Or perhaps you can work out some hackery involving the
"FormulaEvaluator" class? Might be more trouble than it's worth,
though.)
Thanks,
Alex
-----Original Message-----
From: dpront [mailto:gerard.agda@xmarter.com]
Sent: 12 July 2011 14:12
To: user@poi.apache.org
Subject: RE: Read drop down list data
Hi,
I have followed your guidance and there's a little problem. I think
that
I'm already getting the values of the DataValidation class but the
problem
is, it consists of formulas, is it possible for POI to translate those
formulas into datas or java objects?
This is my sample code:
http://privatepaste.com/4499ddb477
and this is the result:
http://privatepaste.com/10b548872e
Thanks.
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Read-drop-down-list-data-tp45742
32p4578814.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
RE: Read drop down list data
Posted by dpront <ge...@xmarter.com>.
Hi,
I have followed your guidance and there's a little problem. I think that
I'm already getting the values of the DataValidation class but the problem
is, it consists of formulas, is it possible for POI to translate those
formulas into datas or java objects?
This is my sample code:
http://privatepaste.com/4499ddb477
and this is the result:
http://privatepaste.com/10b548872e
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Read-drop-down-list-data-tp4574232p4578814.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: Read drop down list data
Posted by Alex Panayotopoulos <Al...@vianet.co.uk>.
Hi there,
Interesting question. Are you working with XML or legacy format
spreadsheets?
If the former, I notice that there's a method in XSSFSheet called
getDataValidations(); this yields a list of XSSFDataValidation objects
which you can iterate through. XSSFDataValidation has a method
getRegions(), which yields a CellRangeAddressList which may contain the
cell address you are interested in. It also has a method
getValidationConstraint(), yielding a DataValidationConstraint object.
If it's a list of explicit values, then you can call
getExplicitListValues() on the DataValidationConstraint object.
This is all rather messy, but it does appear doable. But if it's an
HSSFSheet you are working with, I'm afraid I don't know how (or even if)
there is any way of finding what you are looking for.
Thanks,
Alex
-----Original Message-----
From: dpront [mailto:gerard.agda@xmarter.com]
Sent: 11 July 2011 09:14
To: user@poi.apache.org
Subject: Read drop down list data
Hi,
I have searched the forum regarding this topic and can't seem to find
something that can help me out. The question is, is Apache POI capable
of
reading the values of a cell that has a drop down list?
What I wanted to achieve is to read the excel file and get the datas
on
the drop down list and turn it into a java object (Iterate in a certain
row
and return the values of drop down list per cell).
I haven't written any code since I dont know where to start. Can
someone
point me into the right direction or what method should I needed to look
into?
Thanks.
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Read-drop-down-list-data-tp45742
32p4574232.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