0

I am reading an excel file and am trying to find out which of the cell values are date values. Now, I tried doing it with getCellType of Apache POI SS User Model but the problem is my excel is returning String value even for the cells which contains Date value.

enter image description here

Is there a way to get around with this issue?

Below is my code for reference.

if (wb != null) {
            sh = wb.getSheetAt(0);
            CellStyle cellStyle = wb.createCellStyle();
            CreationHelper createHelper = wb.getCreationHelper();
            cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("DD-MM-YYYY HH:MM"));
            System.out.println("Printing columns header.......");
            for (int r = sh.getFirstRowNum()+1; r <= 10; r++) { //this for loop is iterating through all the rows of excel
                Row currentRow = sh.getRow(r); //get r'th row
                for (int c=currentRow.getFirstCellNum(); c<currentRow.getLastCellNum(); c++) { //this for loop is iterating through columns 4 to 7 of r'th row
                    Cell currentCell = currentRow.getCell(c); //get the c'th column
                    if (currentCell != null) {
                        CellType currentCellType = currentCell.getCellType();
                        if (currentCellType != null) {
                            System.out.print(currentCell.getCellType() + ":");
                            printCellValue(currentCellType, currentCell);
                        }
                    }

//                  if (DateUtil.isCellDateFormatted(currentCell)) {
//                      System.out.println("Current Cell value: " + currentCell.getDateCellValue());
//                  }
                }
                System.out.println();
            }
        }
MitSM
  • 21
  • 11
  • Please share your code snippet what you have tried so far? – miiiii Dec 12 '19 at 09:09
  • Have added my code in the qquestion. – MitSM Dec 12 '19 at 09:11
  • 1
    One thing I would like to tell you is.. if it is an excel file then the dates you see in original file column MAY(definitely will) be formatted when it is sent as file. then in that case you MAY NOT expect the DateFormatter work like formatting the string in normal code. – miiiii Dec 12 '19 at 09:15
  • Try the same code with csv file rather.. if the date is in **DD-MM-YYYY HH:MM** this format your code shd work (I'm still going thru your code.) – miiiii Dec 12 '19 at 09:16
  • @miiiii - My challenge is not to have a proper format to the date but to recognize which cells contain dates if the getCellType method is returning String and not Numeric as is required for the date values. – MitSM Dec 12 '19 at 09:22
  • I think, you didn't get my point. I said, if you use an excel sheet, which stored a date column, then it is more likely to happen that it masks the actual DATE with some integer number inside the actual file. In excel file storage, it stores your data (rows & columns) in one part and the formatting/meta-info in other part (bith in the same file). so when you read via apache poi, then it just reads from data part, which will give you numbers (not the DATE). – miiiii Dec 12 '19 at 09:30
  • Ok. So any ideas how can we read the formatting info for the excel file so that we can get the required info? – MitSM Dec 12 '19 at 09:53
  • If you know it is a date, what's wrong with [Cell.getDateCellValue()](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Cell.html#getDateCellValue--)? – Gagravarr Dec 12 '19 at 11:22
  • @Gagravarr - I need to skim through the whole sheet and get only those values which are date. So using Cell.getDateCellValue() for non-date values will through an exception. – MitSM Dec 12 '19 at 12:10
  • Then [check if it is a date first?](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/DateUtil.html#isCellDateFormatted-org.apache.poi.ss.usermodel.Cell-) – Gagravarr Dec 12 '19 at 13:16
  • isCellDateFormatted will only work if the cellType is Numeric. In my case it is being returned as String. – MitSM Dec 12 '19 at 13:25
  • @MitSM can you share your excel sheet (probably github link.. with only few rows.. with date column you have) and removing all private/secret information. ? – miiiii Dec 12 '19 at 14:11
  • @miiiii - Git hub Link: https://github.com/msm86/mHub – MitSM Dec 12 '19 at 14:37
  • @MitSM Got 404 for github link :P – miiiii Dec 12 '19 at 14:40
  • Try this: https://github.com/msm86/mHubP.git – MitSM Dec 12 '19 at 14:57

0 Answers0