Summary: | Error reading XL sheet when the cell is date data type | ||
---|---|---|---|
Product: | POI | Reporter: | murugan <murugank> |
Component: | HSSF | Assignee: | 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
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. 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; 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(); } } |