I was recently exporting a Microsoft Excel SpreadSheet to a data base. I was using Apache POI to read the excel sheet. To my utter surprise, I was getting the date as some numeric value. I initially thought it was the Unix epoch, but it turned out wrong. I then applied my "dirty-fix" talent, rather than googling. 17-Jan-09 was 39830. 39830/365 = 109. So I gathered that this Microsoft Epoch started from 1st Jan 1900. I was not entirely wrong. But it actually starts from 31st December 1899 :). So, 31st December 1899 is the Day 1 in Microsoft Terminlogy. The Microsoft guys goofed up in the leap year thing, so they lost one day. I know this as I googled a bit today, and came across this. So, the Microsoft Epoch really should have been 1st Jan 1900. Better luck next time :).
I would love to share the code with you:
import java.io.IOException;
import java.io.InputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
*
* @author paawak
*/
abstract class SheetReader {
final HSSFWorkbook excelBook;
final int startFromRow;
SheetReader(InputStream excelSheetContents, int startFromRow)
throws IOException {
excelBook = new HSSFWorkbook(excelSheetContents);
this.startFromRow = startFromRow;
}
String getCellContent(HSSFRow row, int columnIndex) {
HSSFCell cell = row.getCell(columnIndex);
String content = null;
if (cell != null) {
int cellType = cell.getCellType();
switch (cellType) {
// also handles dates
case HSSFCell.CELL_TYPE_NUMERIC:
content = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
content = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
content = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
default:
throw new UnsupportedOperationException("HssfCellType "
+ cellType + " not yet supported");
}
}
return content;
}
Date getDate(String date) throws NumberFormatException {
Date givenDate = null;
if (date != null && !"".equals(date.trim())) {
// hack to have date in readble format
int dateInt = (int) Float.parseFloat(date);
Calendar cal = new GregorianCalendar(1899, 11, 30);
cal.add(Calendar.DATE, dateInt);
givenDate = cal.getTime();
}
return givenDate;
}
}
Note that I am getting a float for the date, which I am converting to String, for purely ease of handling.