Bug 32150

Summary: Error reading XL sheet when the cell is date data type
Product: POI Reporter: murugan <murugank>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED WONTFIX    
Severity: major CC: murugank
Priority: P3    
Version: 2.5-FINAL   
Target Milestone: ---   
Hardware: Other   
OS: All   

Description murugan 2004-11-10 09:57:51 UTC
The HSSFCell class couldn't find out if the cell data type is a date.There is 
no variable such as HSSFCell.CELL_TYPE_DATE. When there is a date in a cell, it 
returns numeric value. Also the method HSSFDateUtil.isCellDateFormatted() 
always return false if there is a date present in a cell.The method 
HSSFCell.getDateCellValue() works fine , but for any numeric value present in 
the cell, it gives me a java.util.date object (If the value of the cell is 1, 
then Mon Jan 01 00:00:00 IST 1900 is returned).What is the fix for this.
Comment 1 Avik Sengupta 2004-11-18 14:49:07 UTC
This is NOT a bug, but a consequence of the fact that Excel stores dates as
numeric values. See the FAQ for various ways to work around this. In general
questions should be asked on the mailing list, rather than bugzilla for quicker
response. Thanks. 
Comment 2 PG 2006-03-16 01:35:22 UTC
I use the approach suggested in the Jakarta POI FAQ, 
(See code snippet below) and the issue still occurs!.

   case HSSFCell.CELL_TYPE_NUMERIC:
                    double d = cell.getNumericCellValue();
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        cal.setTime(HSSFDateUtil.getJavaDate(d));
                        value =
(String.valueOf(cal.get(Calendar.YEAR))).substring(2);
                        value = cal.get(Calendar.MONTH) + 1 + "/" +
cal.get(Calendar.DAY_OF_MONTH) + "/" + value;
                    } else {
                        value = "" + cell.getNumericCellValue();
                    }
                    break;
Comment 3 PG 2006-03-16 01:37:20 UTC
Source Code
----------------------

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.util.Calendar;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelExporter {

    private String spreadsheet;

    private final String NEW_LINE = "\r\n";

    private final String DELIMITER = ",";

    private final String EXTENSION = ".csv";

    public static void main(String... args) throws Exception {
        new ExcelExporter("x.xls").execute();
        System.out.println("All done!");
    }

    public ExcelExporter(String spreadsheet) {
        this.spreadsheet = spreadsheet;
    }

    private void execute() throws Exception {
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(spreadsheet));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        String tsv = "";
        Calendar cal = Calendar.getInstance();
        for (int r = 0; r < sheet.getPhysicalNumberOfRows(); r++) {
            for (short c = 0; c < sheet.getRow(r).getPhysicalNumberOfCells(); c++) {
                HSSFCell cell = sheet.getRow(r).getCell(c);
                String value = null;
                if (cell == null)
                    continue;
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    // Excel stores dates as numbers therefore the only way to
                    // determine if a cell is actually stored as a date is to
                    // look at the formatting.
                    double d = cell.getNumericCellValue();
                    // test if a date!
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // format in form of M/D/YY
                        cal.setTime(HSSFDateUtil.getJavaDate(d));
                        value =
(String.valueOf(cal.get(Calendar.YEAR))).substring(2);
                        value = cal.get(Calendar.MONTH) + 1 + "/" +
cal.get(Calendar.DAY_OF_MONTH) + "/" + value;
                    } else {
                        value = "" + cell.getNumericCellValue();
                    }
                    break;

                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;

                default:
                }
                tsv += value + DELIMITER;

            }
            tsv += r == (sheet.getPhysicalNumberOfRows() - 1) ? "" : NEW_LINE; 
        }
        File f = new File("x" + EXTENSION);
        f.createNewFile();
        FileWriter fr = new FileWriter("x" + EXTENSION);
        fr.write(tsv);
        fr.flush();
        fr.close();

    }

}