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.