Bug 32150 - Error reading XL sheet when the cell is date data type
Summary: Error reading XL sheet when the cell is date data type
Status: RESOLVED WONTFIX
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.5-FINAL
Hardware: Other All
: P3 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2004-11-10 09:57 UTC by murugan
Modified: 2008-05-17 06:39 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
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();

    }

}