Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to search an Excel sheet for a specific date using Apache POI?

I've been using the example code given to search the sheet's contents:

Sheet sheet1 = wb.getSheetAt(0);
for (Row row : sheet1) {
 for (Cell cell : row) {
  CellReference cellRef = new CellReference(row.getRowNum(), cell.getCellNum());
  System.out.print(cellRef.formatAsString());
  System.out.print(" - ");

  switch(cell.getCellType()) {
      case Cell.CELL_TYPE_STRING:
        System.out.println(cell.getRichStringCellValue().getString());
        break;
      case Cell.CELL_TYPE_NUMERIC:
        if(DateUtil.isCellDateFormatted(cell)) {
          System.out.println(cell.getDateCellValue());
        } else {
          System.out.println(cell.getNumericCellValue());
        }
        break;
      case Cell.CELL_TYPE_BOOLEAN:
        System.out.println(cell.getBooleanCellValue());
        break;
      case Cell.CELL_TYPE_FORMULA:
        System.out.println(cell.getCellFormula());
        break;
      default:
        System.out.println();
  }
 }

}

But can't find a way to properly compare the dates extracted from the sheet with a given date. I've tried using a data validation and also formatting the date to a string and including further conditional statements but nothing has worked.

seems like a relatively simple problem and necessary to but havent been able to fix it yet?

any help would be greatly appreciated!

like image 331
Jsk Avatar asked Oct 25 '22 23:10

Jsk


1 Answers

Excel stores dates as floating point decimals which is - obviously - different to the way Java handles dates (long values).

Apache POI contains a helper class to do date conversion between Java and Excel: DateUtil. This should help (if I got your problem correct).

like image 107
Andreas Dolk Avatar answered Oct 31 '22 11:10

Andreas Dolk