0

I am working on a requirement where i need to validate xlsx file records and display validations to end user.I am using Apache POI and The problem is when a row contains a date cell like(01/31/2015) during parsing it is going inside numeric cell type switch case and giving some unexpected value(42005 like that).

I cant use getDateCellValue since I am converting xls to csv and doing different validations there. Hence irrespective of xls formula/format, I should get the value exactly entered by the user for my requirement not only for date but for all other types ( as of now I found issue with only with date strings). I have tried below code and please update me with any other solutions since I am not sure that below code will work fine for all scenarios.

if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){


     if(DateUtil.isCellDateFormatted(cell)){
           DataFormatter format= new DataFormatter();
           FormulaEvaluator fe = Workbook.getCreationHelper().createFormulaEvaluator();
           String str= format.formatCellValue(cell, fe);
           return str;
        }
    }
Kiran
  • 921
  • 1
  • 11
  • 23
  • 1
    Why not just use `DataFormatter` for all cases, and let it do all the work for you? – Gagravarr Feb 16 '16 at 23:27
  • If I use date format it will convert the actual cell data according to format specified. I want to have actual data(01/31/2015) which will be handled later in my parser module. First I need to read the xlsx file as it is with out any formatting which modifies the actual data – Kiran Feb 17 '16 at 01:13
  • 1
    Please note that Excel actually always uses a format for displaying stuff, e.g. it stores dates as double internally, not "exactly what the user entered" and displays the date via a format, using a default format if no special one is set. So the DataFormatter will usually be the closest what you get. – centic Feb 17 '16 at 06:58

1 Answers1

0

Kiran, i believe you can get the info regarding any cell format by using this cell.setCellType(Cell.CELL_TYPE_STRING); before reading the cell content.

This answer was answered already, but for numeric formats: How can I read numeric strings in Excel cells as string (not numbers) with Apache POI?

Community
  • 1
  • 1
Tutz
  • 11
  • 2
  • .I have tied that but its not working.even after setting cell type as string ,The method cell.getStingValue() returning "42005" for the value 01/31/2015 in the excel sheet cell – Kiran Feb 17 '16 at 01:10
  • Is it possible to modify the file Before running your code?, If this is possible, it would be very simple to create a macro to run this file before processing so you can have all data inside the excel file as string. – Tutz Feb 17 '16 at 20:48