CODEWITHSUNDEEP
javaapache-poixlsx
paramvir
paramvir

Reputation: 404

date constraint validation not working for xlsx using apache-POI in java

Hi I am trying to create a date constraint validation in xlsx file using apache-POI in java. Following is the code I am using.

XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(spreadsheet);
XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint)dvHelper.createDateConstraint(OperatorType.BETWEEN,
                    "25/10/2014", "30/10/2015", "dd/mm/yyyy");
CellRangeAddressList addressList = new CellRangeAddressList(0,0,0,0);
                XSSFDataValidation dataValidation = (XSSFDataValidation)dvHelper.createValidation(constraint, addressList);
dataValidation.setShowErrorBox(true);
dataValidation.setEmptyCellAllowed(false);
spreadsheet.addValidationData(dataValidation);

But it is not working. When I try to edit the value, except for that value it doesn't let me change the value. It's as if validation is for that particular value.

When I open the file, in the data validation section, validation is showing as follows:

enter image description here

And now if I remove the "=" sign from start date and end date then It works. Please help me here.

Upvotes: 0

Views: 2650

Answers (1)

Axel Richter
Axel Richter

Reputation: 61880

The String dateFormat is simply ignored in XSSFDataValidationHelper.createDateConstraint. So simply the strings "25/10/2014" and "30/10/2015" were putted into the formulas instead of Excel dates.

Do 

DataValidationConstraint dvConstraint = dvHelper.createDateConstraint(OperatorType.BETWEEN,
                    ""+DateUtil.getExcelDate(sdf.parse("25/10/2014")), ""+DateUtil.getExcelDate(sdf.parse("30/10/2015")), "");

instead.

There DateUtil is org.apache.poi.ss.usermodel.DateUtil and sdf is java.text.SimpleDateFormat: 

SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy")

Complete example:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.usermodel.DateUtil;

import java.text.SimpleDateFormat;

class DataValidationDateBetween {

 public static void main(String[] args) {
  try {

   Workbook workbook = new XSSFWorkbook();  
   Sheet sheet = workbook.createSheet("Sheet1");

   SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");

   DataValidationHelper dvHelper = sheet.getDataValidationHelper();
   DataValidationConstraint dvConstraint = dvHelper.createDateConstraint(OperatorType.BETWEEN,
                    ""+DateUtil.getExcelDate(sdf.parse("25/10/2014")), ""+DateUtil.getExcelDate(sdf.parse("30/10/2015")), "");
   CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);            
   DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);

   validation.setShowErrorBox(true);

   sheet.addValidationData(validation);

   workbook.write(new FileOutputStream("DataValidationDateBetween.xlsx"));
   workbook.close();

  } catch (Exception ex) {
   ex.printStackTrace();
  }
 }
}

Upvotes: 2

Related Questions