This week's book giveaway is in the Reactive Progamming forum.
We're giving away four copies of Reactive Streams in Java: Concurrency with RxJava, Reactor, and Akka Streams and have Adam Davis on-line!
See this thread for details.
Win a copy of Reactive Streams in Java: Concurrency with RxJava, Reactor, and Akka Streams this week in the Reactive Progamming forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Ganesh Patekar

Apache POI Date Validation in Excel

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How to do date validation using createDateConstraint(comparisonOperator, expr1, expr2, dateFormat) ... i need to validate .xls file date cell , if date is other than MM/DD/YYYY format then shows an error ' Invalid Date Format'. any solutions ?


 
Rancher
Posts: 43011
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch.

I doubt POI can do that. It is a library to work with XLS/X files, not a spreadsheet application like Excel. The DataValidationConstraint classes in POI are not meant for actually validating data; that's something your code would have to implement. They facilitate creating, reading and writing constraints, not validating them.
 
krishna cirimavilla
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm using below code for checking text length in .xlsx file .... same as need to do for date validation (if user enters invalid date in excel cell then shows an error message as 'Invalid date format' like tat)

XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.TEXT_LENGTH, OperatorType.LESS_THAN, "100", null);
XSSFDataValidation xssfDataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
xssfDataValidation.setShowErrorBox(true);
xssfDataValidation.setShowPromptBox(true);
xssfDataValidation.createPromptBox("","Comments are mandatory for BY or SS Reason Codes");
xssfDataValidation.createErrorBox("ERROR", "Invalid Reason Code");
xssfDataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(xssfDataValidation);

I tried like tis but it is not working ..... DataValidationConstraint activationDateConstraint = validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "=VLOOKUP($D3,Offices!$B$2:$D$6,3,FALSE)", "=TODAY()", "dd/mm/yy");
 
Ulf Dittmer
Rancher
Posts: 43011
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, so you don't want to validate any data, you want to add validation constraints to an XLS/X file. Good, that's what POI is for. What do you have so far, and where are you stuck making progress?
 
krishna cirimavilla
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm confused, how to set the parameters in createDateConstraint(comparisonOperator, expr1, expr2, dateFormat) ... i need sample example ?
 
Ulf Dittmer
Rancher
Posts: 43011
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ah, I see what you mean - the javadocs are extremely thin :-) Sorry, no idea.
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
May be I'm late and the OP has found the solution, but here is how I do this kind of validation:


Take a look at org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType.BETWEEN, there are all possible values.
In fact, you put the operator (BETWEEN), initial date, end date and date format. Easy, right? I had some problems with the date format. In this case it must be "dd/MM/yyyy" and not "dd/mm/yyyy", in the second case it would return incorrect date, but this kind of things are up to the date format you'll use.

Hope this helps.
 
If tomatoes are a fruit, then ketchup must be a jam. Taste this tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!