[Bug 69277] New: Issue with Date Validation in HSSF (.xls) Files

[bugzilla]

https://bz.apache.org/bugzilla/show_bug.cgi?id=69277

            Bug ID: 69277
           Summary: Issue with Date Validation in HSSF (.xls) Files
           Product: POI
           Version: unspecified
          Hardware: PC
                OS: Mac OS X 10.1
            Status: NEW
          Severity: normal
          Priority: P2
         Component: HSSF
          Assignee: dev@poi.apache.org
          Reporter: nikknp.du...@gmail.com
  Target Milestone: ---

I have encountered an issue while attempting to apply date validation
constraints in HSSF (.xls) files using the Apache POI library. When creating a
date validation using the DataValidationHelper.createDateConstraint method, the
validation fails with an error: “Failed to parse date using specified format.”
This error occurs despite using valid date formats and properly converting
dates to Excel’s numeric format. While the same code functions correctly in
XSSF (.xlsx) files, it seems that HSSF does not correctly handle date formats
or the underlying numeric representation of dates when setting up data
validation. This issue affects the ability to create consistent date validation
across .xls and .xlsx files, which is critical for applications that need to
support both formats. I would appreciate it if this issue could be investigated
and addressed in future releases. Thank you for your attention to this matter.

Exception - 
Exception in thread "main" java.lang.RuntimeException: Failed to parse date
'45292.0' using specified format 'java.text.SimpleDateFormat@f67a0200'
        at
org.apache.poi.hssf.usermodel.DVConstraint.convertDate(DVConstraint.java:263)
        at
org.apache.poi.hssf.usermodel.DVConstraint.createDateConstraint(DVConstraint.java:192)
        at
org.apache.poi.hssf.usermodel.HSSFDataValidationHelper.createDateConstraint(HSSFDataValidationHelper.java:47)
        at coldfusion.excel.Driver.main(Driver.java:52)
Caused by: java.text.ParseException: Unparseable date: "45292.0"
        at java.base/java.text.DateFormat.parse(DateFormat.java:399)
        at
org.apache.poi.hssf.usermodel.DVConstraint.convertDate(DVConstraint.java:261)
        ... 3 more

Code to reproduce - 
public static void main(String[] args) throws ParseException {

        // Create a new workbook and sheet
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Hyperlink Example");

        // Create a row and a cell
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);

        // Create a hyperlink
        CreationHelper createHelper = workbook.getCreationHelper();
        Hyperlink hyperlink =
createHelper.createHyperlink(HyperlinkType.EMAIL);
        hyperlink.setLabel("mylabel");
        hyperlink.setAddress("http://www.example.com";);
        DataValidationHelper validationHelper =
sheet.getDataValidationHelper();

        // Set the date format you want
        String dateFormat = "yyyy-MM-dd";

        SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);

        // Define start and end dates for validation
        String startDate = "2024-01-01";
        String endDate = "2024-12-31";

        // Convert dates to Excel date values using DateUtil
        double startDateExcel = DateUtil.getExcelDate(sdf.parse(startDate));
        double endDateExcel = DateUtil.getExcelDate(sdf.parse(endDate));

        // Create the data validation constraint
        DataValidationConstraint constraint =
validationHelper.createDateConstraint(
                OperatorType.BETWEEN,
                String.valueOf(startDateExcel),
                String.valueOf(endDateExcel),
                dateFormat
        );
     // Define the cell range to apply the validation (e.g., A1:A10)
        CellRangeAddressList addressList = new CellRangeAddressList(0, 9, 0,
0);

        // Create the data validation object
        DataValidation validation =
validationHelper.createValidation(constraint, addressList);

        // Add the validation to the sheet
        sheet.addValidationData(validation);


        // Set the label and the hyperlink
        cell.setCellValue("Click here");
        cell.setHyperlink(hyperlink);


        // Get the cell value and hyperlink address
        String cellValue = cell.getStringCellValue();
        Hyperlink cellHyperlink = cell.getHyperlink();
        String hyperlinkAddress = cellHyperlink.getAddress();
        HyperlinkType hyperlinkType = cellHyperlink.getType();

        System.out.println("Cell Value: " + cellValue);
        System.out.println("Hyperlink Address: " + hyperlinkAddress);
        System.out.println("Hyperlink type: " + hyperlinkType.toString()); //
even though I gave type as Email in line 35, hyperlink type shows as URL here
        System.out.println("Hyperlink label: " + cellHyperlink.getLabel());

    }

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org