XSSF Data Validation in POI 3.7

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

XSSF Data Validation in POI 3.7

GeckoZoo
Has anyone been able to successfully get an XSSF Data Validation example working using POI 3.7 (beta 2) in Java?   I'm trying to create a simple drop down list of names in cell A1...

I have tried something like this:

final XSSFWorkbook wb = new XSSFWorkbook();
final XSSFSheet sheet = wb.createSheet("Project Data");
final Row r = sheet.createRow(0);
final Cell cell = r.createCell(0);

final String[] excelListValues = new String[] { "Robin", "Chris", "Jason", "Rajat", "Greg" };
final XSSFDataValidationHelper h = new XSSFDataValidationHelper(sheet);
final CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
final DataValidationConstraint dvConstraint = h.createExplicitListConstraint(excelListValues);
final DataValidation dataValidation = h.createValidation(dvConstraint, addressList);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);

and wrote out the file, but when I open it in Excel, there is no data validation.  If I literally change the "XSSF"s to "HSSF"s above, it works fine when reading it back to Excel...e.g. the list shows up.

Any help/examples would be appreciated.

Thanks,

Jason



This email may contain confidential or privileged material.  Use or disclosure of it by anyone other than the recipient is unauthorized.  If you are not an intended recipient, please delete this email.

Reply | Threaded
Open this post in threaded view
|

Re: XSSF Data Validation in POI 3.7

Louis.Masters
Jason:

I just got this working two days ago.  Here is my code (with my business
stuff removed):

                List[] allowedValues = sd.getAllowedValues();

                DataValidationConstraint constraint = null;
                DataValidation dataValidation = null;
                DataValidationHelper validationHelper = null;

                if(wbType==WB_TYPE_XSSF) {
                        validationHelper = new
XSSFDataValidationHelper((XSSFSheet)sheet);
                } else {
                        validationHelper = new
org.apache.poi.hssf.usermodel.HSSFDataValidationHelper((org.apache.poi.hssf.usermodel.HSSFSheet)sheet);
                }
 
                if(allowedValues!=null&&allowedValues.length>0) {
                        for (int i = 0; i < allowedValues.length; i++) {
                                if(allowedValues[i]==null)continue;
 org.apache.poi.ss.util.CellRangeAddressList addressList = new
org.apache.poi.ss.util.CellRangeAddressList(1, dummyRowMax, i, i);
                                constraint =
validationHelper.createExplicitListConstraint((String[])allowedValues[i].toArray());
                                dataValidation =
validationHelper.createValidation(constraint, addressList);
                               
dataValidation.setSuppressDropDownArrow(true);
                                dataValidation.createPromptBox("Valid
Values", "The following values are valid for this cell:" +
allowedValues[i]);
                                dataValidation.setShowPromptBox(true);
 dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                                dataValidation.createErrorBox("Validation
Error", "The following values are valid for this cell:" +
allowedValues[i]);
                                sheet.addValidationData(dataValidation);
                        }

                }

The allowedValues list array is a business object so my developers can add
validations as they see fit.  Basically, it is just an array of lists for
every column on the sheet.  Notice the setSuppressDropDownArrow(true) -
this is where it took me a bit - the examples have it as false.
Also, I am still trying to get the error box to appear - it may not be an
issue - I just added it yesterday, but have not had a chance to look into
it.

Let me know if you have any problems with it.  I have only tested it with
XSSF.

-Lou

"Goris, Jason" <[hidden email]> wrote on 09/21/2010 04:05:07
PM:

> Has anyone been able to successfully get an XSSF Data Validation
> example working using POI 3.7 (beta 2) in Java?   I'm trying to
> create a simple drop down list of names in cell A1...
>
> I have tried something like this:
>
> final XSSFWorkbook wb = new XSSFWorkbook();
> final XSSFSheet sheet = wb.createSheet("Project Data");
> final Row r = sheet.createRow(0);
> final Cell cell = r.createCell(0);
>
> final String[] excelListValues = new String[] { "Robin", "Chris",
> "Jason", "Rajat", "Greg" };
> final XSSFDataValidationHelper h = new XSSFDataValidationHelper(sheet);
> final CellRangeAddressList addressList = new CellRangeAddressList(0,0,
0, 0);

> final DataValidationConstraint dvConstraint = h.
> createExplicitListConstraint(excelListValues);
> final DataValidation dataValidation = h.
> createValidation(dvConstraint, addressList);
> dataValidation.setSuppressDropDownArrow(false);
> sheet.addValidationData(dataValidation);
>
> and wrote out the file, but when I open it in Excel, there is no
> data validation.  If I literally change the "XSSF"s to "HSSF"s
> above, it works fine when reading it back to Excel...e.g. the list shows
up.

>
> Any help/examples would be appreciated.
>
> Thanks,
>
> Jason
>
>
>
> This email may contain confidential or privileged material.  Use or
> disclosure of it by anyone other than the recipient is unauthorized.
> If you are not an intended recipient, please delete this email.
>
Reply | Threaded
Open this post in threaded view
|

RE: XSSF Data Validation in POI 3.7

GeckoZoo
Yeah, it appears that the setSuppressDropDownArrow() method is backwards from what I would have expected (and what worked for HSSF).  Thank you for your help!

Jason

-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
Sent: Wednesday, September 22, 2010 7:19 AM
To: POI Users List
Cc: '[hidden email]'
Subject: Re: XSSF Data Validation in POI 3.7

Jason:

I just got this working two days ago.  Here is my code (with my business
stuff removed):

                List[] allowedValues = sd.getAllowedValues();

                DataValidationConstraint constraint = null;
                DataValidation dataValidation = null;
                DataValidationHelper validationHelper = null;

                if(wbType==WB_TYPE_XSSF) {
                        validationHelper = new
XSSFDataValidationHelper((XSSFSheet)sheet);
                } else {
                        validationHelper = new
org.apache.poi.hssf.usermodel.HSSFDataValidationHelper((org.apache.poi.hssf.usermodel.HSSFSheet)sheet);
                }
 
                if(allowedValues!=null&&allowedValues.length>0) {
                        for (int i = 0; i < allowedValues.length; i++) {
                                if(allowedValues[i]==null)continue;
 org.apache.poi.ss.util.CellRangeAddressList addressList = new
org.apache.poi.ss.util.CellRangeAddressList(1, dummyRowMax, i, i);
                                constraint =
validationHelper.createExplicitListConstraint((String[])allowedValues[i].toArray());
                                dataValidation =
validationHelper.createValidation(constraint, addressList);
                               
dataValidation.setSuppressDropDownArrow(true);
                                dataValidation.createPromptBox("Valid
Values", "The following values are valid for this cell:" +
allowedValues[i]);
                                dataValidation.setShowPromptBox(true);
 dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                                dataValidation.createErrorBox("Validation
Error", "The following values are valid for this cell:" +
allowedValues[i]);
                                sheet.addValidationData(dataValidation);
                        }

                }

The allowedValues list array is a business object so my developers can add
validations as they see fit.  Basically, it is just an array of lists for
every column on the sheet.  Notice the setSuppressDropDownArrow(true) -
this is where it took me a bit - the examples have it as false.
Also, I am still trying to get the error box to appear - it may not be an
issue - I just added it yesterday, but have not had a chance to look into
it.

Let me know if you have any problems with it.  I have only tested it with
XSSF.

-Lou

"Goris, Jason" <[hidden email]> wrote on 09/21/2010 04:05:07
PM:

> Has anyone been able to successfully get an XSSF Data Validation
> example working using POI 3.7 (beta 2) in Java?   I'm trying to
> create a simple drop down list of names in cell A1...
>
> I have tried something like this:
>
> final XSSFWorkbook wb = new XSSFWorkbook();
> final XSSFSheet sheet = wb.createSheet("Project Data");
> final Row r = sheet.createRow(0);
> final Cell cell = r.createCell(0);
>
> final String[] excelListValues = new String[] { "Robin", "Chris",
> "Jason", "Rajat", "Greg" };
> final XSSFDataValidationHelper h = new XSSFDataValidationHelper(sheet);
> final CellRangeAddressList addressList = new CellRangeAddressList(0,0,
0, 0);

> final DataValidationConstraint dvConstraint = h.
> createExplicitListConstraint(excelListValues);
> final DataValidation dataValidation = h.
> createValidation(dvConstraint, addressList);
> dataValidation.setSuppressDropDownArrow(false);
> sheet.addValidationData(dataValidation);
>
> and wrote out the file, but when I open it in Excel, there is no
> data validation.  If I literally change the "XSSF"s to "HSSF"s
> above, it works fine when reading it back to Excel...e.g. the list shows
up.

>
> Any help/examples would be appreciated.
>
> Thanks,
>
> Jason
>
>
>
> This email may contain confidential or privileged material.  Use or
> disclosure of it by anyone other than the recipient is unauthorized.
> If you are not an intended recipient, please delete this email.
>

This email may contain confidential or privileged material.  Use or disclosure of it by anyone other than the recipient is unauthorized.  If you are not an intended recipient, please delete this email.



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

RE: XSSF Data Validation in POI 3.7

Nick Burch-11
On Wed, 22 Sep 2010, Goris, Jason wrote:
> Yeah, it appears that the setSuppressDropDownArrow() method is backwards
> from what I would have expected (and what worked for HSSF).  Thank you
> for your help!

If there is something that is inconsistent between HSSF and XSSF, please
create a new bug in bugzilla, and upload either some code to generate the
two files that differ, or even better a failing unit test!

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

RE: XSSF Data Validation in POI 3.7

Louis.Masters
Nick:

I did not check it against HSSF - I don't think that is the issue.  The
issue is that the documentation on
http://poi.apache.org/spreadsheet/quick-guide.html#Validation states:

Drop Down Lists:
This code will do the same but offer the user a drop down list to select a
value from.
  HSSFWorkbook workbook = new HSSFWorkbook();
  HSSFSheet sheet = workbook.createSheet("Data Validation");
  CellRangeAddressList addressList = new CellRangeAddressList(
      0, 0, 0, 0);
  DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
      new String[]{"10", "20", "30"});
  HSSFDataValidation dataValidation = new HSSFDataValidation
      (addressList, dvConstraint);
  datavalidation.setSuppressDropDownArrow(false);


Notice the "false" in the suppress statement - in 3.7 b2, it needs to be
"true" for the drop down to appear.  Maybe a simple documentation issue?




Nick Burch <[hidden email]> wrote on 09/22/2010 11:30:44 AM:

> On Wed, 22 Sep 2010, Goris, Jason wrote:
> > Yeah, it appears that the setSuppressDropDownArrow() method is
backwards
> > from what I would have expected (and what worked for HSSF).  Thank you

> > for your help!
>
> If there is something that is inconsistent between HSSF and XSSF, please

> create a new bug in bugzilla, and upload either some code to generate
the
> two files that differ, or even better a failing unit test!
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
Reply | Threaded
Open this post in threaded view
|

RE: XSSF Data Validation in POI 3.7

Louis.Masters
In reply to this post by GeckoZoo
I just noticed one more thing that needs to be added to get the error
message box to appear:

dataValidation.setShowErrorBox(true);


So, in HSSF, the drop down works as expected (and opposite from XSSF)?

-Lou


"Goris, Jason" <[hidden email]> wrote on 09/22/2010 11:24:07
AM:

> Yeah, it appears that the setSuppressDropDownArrow() method is
> backwards from what I would have expected (and what worked for
> HSSF).  Thank you for your help!
>
> Jason
>
> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> Sent: Wednesday, September 22, 2010 7:19 AM
> To: POI Users List
> Cc: '[hidden email]'
> Subject: Re: XSSF Data Validation in POI 3.7
>
> Jason:
>
> I just got this working two days ago.  Here is my code (with my business

> stuff removed):
>
>                 List[] allowedValues = sd.getAllowedValues();
>
>                 DataValidationConstraint constraint = null;
>                 DataValidation dataValidation = null;
>                 DataValidationHelper validationHelper = null;
>
>                 if(wbType==WB_TYPE_XSSF) {
>                         validationHelper = new
> XSSFDataValidationHelper((XSSFSheet)sheet);
>                 } else {
>                         validationHelper = new
> org.apache.poi.hssf.usermodel.HSSFDataValidationHelper((org.apache.
> poi.hssf.usermodel.HSSFSheet)sheet);
>                 }
>
>                 if(allowedValues!=null&&allowedValues.length>0) {
>                         for (int i = 0; i < allowedValues.length; i++) {

>                                 if(allowedValues[i]==null)continue;
>  org.apache.poi.ss.util.CellRangeAddressList addressList = new
> org.apache.poi.ss.util.CellRangeAddressList(1, dummyRowMax, i, i);
>                                 constraint =
> validationHelper.createExplicitListConstraint((String[])
> allowedValues[i].toArray());
>                                 dataValidation =
> validationHelper.createValidation(constraint, addressList);
>
> dataValidation.setSuppressDropDownArrow(true);
>                                 dataValidation.createPromptBox("Valid
> Values", "The following values are valid for this cell:" +
> allowedValues[i]);
>                                 dataValidation.setShowPromptBox(true);
>  dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
> dataValidation.createErrorBox("Validation
> Error", "The following values are valid for this cell:" +
> allowedValues[i]);
>                                 sheet.addValidationData(dataValidation);
>                         }
>
>                 }
>
> The allowedValues list array is a business object so my developers can
add
> validations as they see fit.  Basically, it is just an array of lists
for
> every column on the sheet.  Notice the setSuppressDropDownArrow(true) -
> this is where it took me a bit - the examples have it as false.
> Also, I am still trying to get the error box to appear - it may not be
an
> issue - I just added it yesterday, but have not had a chance to look
into
> it.
>
> Let me know if you have any problems with it.  I have only tested it
with
> XSSF.
>
> -Lou
>
> "Goris, Jason" <[hidden email]> wrote on 09/21/2010
04:05:07

> PM:
>
> > Has anyone been able to successfully get an XSSF Data Validation
> > example working using POI 3.7 (beta 2) in Java?   I'm trying to
> > create a simple drop down list of names in cell A1...
> >
> > I have tried something like this:
> >
> > final XSSFWorkbook wb = new XSSFWorkbook();
> > final XSSFSheet sheet = wb.createSheet("Project Data");
> > final Row r = sheet.createRow(0);
> > final Cell cell = r.createCell(0);
> >
> > final String[] excelListValues = new String[] { "Robin", "Chris",
> > "Jason", "Rajat", "Greg" };
> > final XSSFDataValidationHelper h = new
XSSFDataValidationHelper(sheet);
> > final CellRangeAddressList addressList = new CellRangeAddressList(0,0,

> 0, 0);
> > final DataValidationConstraint dvConstraint = h.
> > createExplicitListConstraint(excelListValues);
> > final DataValidation dataValidation = h.
> > createValidation(dvConstraint, addressList);
> > dataValidation.setSuppressDropDownArrow(false);
> > sheet.addValidationData(dataValidation);
> >
> > and wrote out the file, but when I open it in Excel, there is no
> > data validation.  If I literally change the "XSSF"s to "HSSF"s
> > above, it works fine when reading it back to Excel...e.g. the list
shows

> up.
> >
> > Any help/examples would be appreciated.
> >
> > Thanks,
> >
> > Jason
> >
> >
> >
> > This email may contain confidential or privileged material.  Use or
> > disclosure of it by anyone other than the recipient is unauthorized.
> > If you are not an intended recipient, please delete this email.
> >
>
> This email may contain confidential or privileged material.  Use or
> disclosure of it by anyone other than the recipient is unauthorized.
> If you are not an intended recipient, please delete this email.
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>