How to create a date validation in a cell?

[king200143]

NPOI Version

2.7.1

File Type

• [x] XLSX

Upload the Excel File

test.xlsx

Please attach your original Excel File to help us reproduce the issue

Reproduce Steps

`IRow row = sheet.GetRow(rowIdx) ?? sheet.CreateRow(rowIdx); ICell cell = row.CreateCell(colIdx);

ICellStyle dateStyle = sheet.Workbook.CreateCellStyle(); short dateFormat = sheet.Workbook.CreateDataFormat().GetFormat("yyyy-MM-dd"); dateStyle.DataFormat = dateFormat; cell.CellStyle = dateStyle;

IDataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet)sheet); //IDataValidationHelper validationHelper = new XSSFDataValidationHelper((XSSFSheet)sheet); IDataValidationConstraint dateConstraint = validationHelper.CreateDateConstraint( OperatorType.BETWEEN, "1900-1-1", "9999-12-31", "yyyy-MM-dd"); CellRangeAddressList addressList = new CellRangeAddressList(rowIdx, rowIdx, colIdx, colIdx); IDataValidation dataValidation = validationHelper.CreateValidation(dateConstraint, addressList); dataValidation.ShowErrorBox = true; dataValidation.ErrorStyle = 0; dataValidation.CreateErrorBox("Input Error","Only date format (yyyy-MM-dd) can be entered!");

sheet.AddValidationData(dataValidation);` In xls, I can get a correct result, but in xlsx, a '=' will be automatically added before the date rule, the date rang is at the first line and third column in the xlsx file

Issue Description

[Bykiev]

Hi, for data validation in Excel 2007 format, dates should be Excel date, not the strings. So, you should use DateUtil.GetExcelDate method.

[tonyqus]

Please check this post