2

I am using Apache POI to read several Excel files and convert them to an Oracle DB. My code looks roughly like this:

wb = new HSSFWorkbook( new FileInputStream( FORECAST_HEADER_EXCEL_FILE ) );
Sheet sheet = wb.getSheetAt( 0 );
int rows = sheet.getPhysicalNumberOfRows();

// For each row...
for ( int r = 0; r < rows; r++ ) {
    Row row = sheet.getRow( r );
    ForecastLine forecastLine = new ForecastLine();

    cell = row.getCell( EXCEL_COLUMN_BUCKET_START );
    forecastLine.setBucketStart( cell == null 
        ? null 
        : cell.getDateCellValue() );

}

With two of the sheets (separate files) my dates come out fine, but with one the year is wrong (1900, as if there were no year). The columns seems formatted the same in Excel. I've tried different Excel formats on the dates to see if POI would parse them correctly, but no. I don't know if there's a way to affect the POI date parser. Any ideas?

ksnortum
  • 2,809
  • 4
  • 27
  • 36
  • possible Duplicate of [Reading date values from excel cell using POI HSSF API](http://stackoverflow.com/questions/861877/reading-date-values-from-excel-cell-using-poi-hssf-api) – Peter Elliott May 01 '13 at 20:49
  • What numeric value does Excel show for the problem cells? (Format the date cell as a number to see). And what format string is applied to the problem cells? – Gagravarr May 02 '13 at 08:53
  • @Gagravarr: when I use .getNumericCellValue() it returns values like 9.0 and 22.0. The range is about 0.0 <= x < 30.0. – ksnortum May 02 '13 at 16:16
  • Those are all dates in 1900 by the normal rules. What format string is applied to them to make them show something different? – Gagravarr May 02 '13 at 16:53
  • @PeterElliott: DataUtil.isCellDateFormatted() returns false for the Excel cell I'm trying to extract the date from. How do I a) change the spreadsheet so that it returns a date format (the cell format is already Date) or b) tell POI that the cell really is a date? – ksnortum May 02 '13 at 17:14
  • What is the *format string* that is applied to make those 1900s dates show up with recent years? We need to know exactly what formatting is applied to make it have a different year to normal. (The file format spec says that dates are stored from 1900 or 1904, so a small number should never be 2010) – Gagravarr May 02 '13 at 19:40
  • The dates are recent years (like 2010) in the Excel spreadsheet but when I apply the code above, the date that I have in `forecastLine.getBucketEndDate()` is 1900 (without a year). Two other spreadsheets work fine with the same Excel format. – ksnortum May 03 '13 at 15:58

0 Answers0