poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Pari Gandhi <pari.gan...@essential.com.au>
Subject Problem with reading date values
Date Thu, 16 Mar 2006 04:12:30 GMT
Hi,

I am having problems reading a Date Value from an excel file. I use the
approach suggested in the Jakarta POI FAQ, 
(See code snippet below) and it doesn't work (I get 38635.0 as opposed to
10/10/2005).

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;
......

I have attached my source code and my test excel file (x.xls). Please take a
look and advise what I am doing wrong.

----------------------------------------------------------------------------
--------------------------------------------
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;                
//                System.out.println(cell.getCellStyle().getDataFormat())
                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();

    }

}
----------------------------------------------------------------------------
--------------------------------------------

Regards,

Pari Gandhi, 
Senior Java Developer,
Essential Computer Systems,
Level 3 , 122 Pirie St,
Adelaide, SA 5000,
ph: 08 82361600
http://www.essential.com.au
pari.gandhi@essential.com.au

This email is from Essential Computer Systems Pty Ltd (ACN 008 126 707) 
The information contained in this email (including any attached files) may
be privileged and confidential intended for the use of the person/persons
named above.  If you are not the intended recipient, you are advised that
any use, distribution or copying of the information is strictly prohibited.
If you have received this transmission in error, please notify Essential
Computer Systems immediately on +61 8 8223 1144 or reply by email to the
sender.
Essential Computer Systems does not represent or warrant that this email or
any attached files are free from computer viruses or other defects. Any
attached files are provided, and may only be used, on the basis that the
user assumes all responsibility for any loss, damage or consequence
resulting directly or indirectly from use of the attached files.




Mime
View raw message