How to parse date from excel to java?

Question

Java

When trying to parse an excel file and write it to an object, it throws an exception stating that

Cannot get a numeric value from a text cell

in the date field.

Question: how to correctly parse the date?

excel:


code:

@Data
public class BaseModel {
    private Date dateTime;
    private int paymentId;
    private String paymentPurpose;
    private int sum;

}

@PostMapping("/import")
    public void mapReapExcelDatatoDB(@RequestParam("file") MultipartFile reapExcelDataFile) throws IOException {
        List<BaseModel> models = new ArrayList<>();
        XSSFWorkbook workbook = new XSSFWorkbook(reapExcelDataFile.getInputStream());
        XSSFSheet worksheet = workbook.getSheetAt(0);
        DataFormatter formatter = new DataFormatter();


        for(int i=1; i<worksheet.getPhysicalNumberOfRows() ;i++) {
            BaseModel baseModel = new BaseModel();

            XSSFRow row = worksheet.getRow(i);

            baseModel.setDateTime(row.getCell(0).getDateCellValue());
            baseModel.setPaymentId((int) row.getCell(1).getNumericCellValue());
            baseModel.setPaymentPurpose(row.getCell(2).getStringCellValue());
            baseModel.setSum((int) row.getCell(3).getNumericCellValue());
            models.add(baseModel);
        }
    }

Answer 1

There is such a piece from the processing of different types of excel fields

switch (sh.getRow(r).getCell(cell).getCellType()) {
      case HSSFCell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(sh.getRow(r).getCell(cell))) {
          Date aDate = sh.getRow(r).getCell(cell).getDateCellValue();
          SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
          res = sdf.format(aDate);
        } else {
          sh.getRow(r).getCell(cell).setCellType(Cell.CELL_TYPE_STRING);
          res = sh.getRow(r).getCell(cell).getStringCellValue();
        }
        break;