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: [email protected]
Reporter: [email protected]
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: [email protected]
For additional commands, e-mail: [email protected]
