Index: src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java =================================================================== --- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java (revision 700063) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java (working copy) @@ -57,6 +57,7 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDialogsheet; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbookPr; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf; import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorkbookDocument; @@ -200,6 +201,16 @@ } /** + * Return the underlying XML bean + * + * @return the underlying CTWorkbookPr bean + */ + public CTWorkbookPr getWorkbookPr() { + CTWorkbookPr workbookPr = getWorkbook().getWorkbookPr() == null ? getWorkbook().addNewWorkbookPr() : getWorkbook().getWorkbookPr(); + return workbookPr; + } + + /** * Get the PackagePart corresponding to a given sheet. * * @param ctSheet The sheet Index: src/java/org/apache/poi/ss/usermodel/DateUtil.java =================================================================== --- src/java/org/apache/poi/ss/usermodel/DateUtil.java (revision 700063) +++ src/java/org/apache/poi/ss/usermodel/DateUtil.java (working copy) @@ -276,6 +276,7 @@ double d = cell.getNumericCellValue(); if ( DateUtil.isValidExcelDate(d) ) { CellStyle style = cell.getCellStyle(); + if(style==null) return false; int i = style.getDataFormat(); String f = style.getDataFormatString(); bDate = isADateFormat(i, f); Index: src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java =================================================================== --- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (revision 700063) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (working copy) @@ -17,9 +17,12 @@ package org.apache.poi.xssf.usermodel; +import java.text.DateFormat; +import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; +import org.apache.poi.hssf.record.formula.eval.ErrorEval; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; @@ -73,6 +76,7 @@ protected SharedStringSource getSharedStringSource() { return this.sharedStringSource; } + protected StylesSource getStylesSource() { return this.stylesSource; } @@ -256,14 +260,17 @@ } throw new NumberFormatException("You cannot get a string value from a non-string cell"); } - + + /** + * Sets this cell as the active cell for the worksheet + */ public void setAsActiveCell() { row.getSheet().setActiveCell(cell.getR()); } + public void setCellComment(Comment comment) { - String cellRef = - new CellReference(row.getRowNum(), getCellNum()).formatAsString(); + String cellRef = new CellReference(row.getRowNum(), getCellNum()).formatAsString(); row.getSheet().setCellComment(cellRef, (XSSFComment)comment); } @@ -352,6 +359,15 @@ } } + /** + * set the cells type (numeric, formula or string) + * @see #CELL_TYPE_NUMERIC + * @see #CELL_TYPE_STRING + * @see #CELL_TYPE_FORMULA + * @see #CELL_TYPE_BLANK + * @see #CELL_TYPE_BOOLEAN + * @see #CELL_TYPE_ERROR + */ public void setCellType(int cellType) { switch (cellType) { case CELL_TYPE_BOOLEAN: @@ -379,13 +395,38 @@ this.cell.setV(String.valueOf(value)); } + + /** + * set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as + * a date. + * + * @param value the date value to set this cell to. For formulas we'll set the + * precalculated value, for numerics we'll set its value. For other types we + * will change the cell to a numeric cell and set its value. + */ public void setCellValue(Date value) { - setCellValue(HSSFDateUtil.getExcelDate(value, false /*this.book.isUsing1904DateWindowing()*/)); // FIXME + boolean date1904 = this.row.getSheet().getWorkbook().getWorkbookPr().getDate1904(); + setCellValue(HSSFDateUtil.getExcelDate(value, date1904)); } + /** + * set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as + * a date. + * + * This will set the cell value based on the Calendar's timezone. As Excel + * does not support timezones this means that both 20:00+03:00 and + * 20:00-03:00 will be reported as the same value (20:00) even that there + * are 6 hours difference between the two times. This difference can be + * preserved by using setCellValue(value.getTime()) which will + * automatically shift the times to the default timezone. + * + * @param value the date value to set this cell to. For formulas we'll set the + * precalculated value, for numerics we'll set its value. For othertypes we + * will change the cell to a numeric cell and set its value. + */ public void setCellValue(Calendar value) { - // TODO Auto-generated method stub - + boolean date1904 = this.row.getSheet().getWorkbook().getWorkbookPr().getDate1904(); + setCellValue( HSSFDateUtil.getExcelDate(value, date1904 )); } public void setCellValue(String str) { @@ -414,8 +455,42 @@ this.cell.setV(value ? TRUE_AS_STRING : FALSE_AS_STRING); } + /** + * Returns a string representation of the cell + * + * This method returns a simple representation, + * anthing more complex should be in user code, with + * knowledge of the semantics of the sheet being processed. + * + * Formula cells return the formula string, + * rather than the formula result. + * Dates are displayed in dd-MMM-yyyy format + * Errors are displayed as #ERR<errIdx> + */ public String toString() { - return "[" + this.row.getRowNum() + "," + this.getCellNum() + "] " + this.cell.getV(); + // return "[" + this.row.getRowNum() + "," + this.getCellNum() + "] " + this.cell.getV(); + switch (getCellType()) { + case CELL_TYPE_BLANK: + return ""; + case CELL_TYPE_BOOLEAN: + return getBooleanCellValue() ? "TRUE" : "FALSE"; + case CELL_TYPE_ERROR: + return ErrorEval.getText(getErrorCellValue()); + case CELL_TYPE_FORMULA: + return getCellFormula(); + case CELL_TYPE_NUMERIC: + //TODO apply the dataformat for this cell + if (HSSFDateUtil.isCellDateFormatted(this)) { + DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy"); + return sdf.format(getDateCellValue()); + } else { + return getNumericCellValue() + ""; + } + case CELL_TYPE_STRING: + return getRichStringCellValue().toString(); + default: + return "Unknown Cell Type: " + getCellType(); + } } /** Index: src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java =================================================================== --- src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java (revision 700063) +++ src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java (working copy) @@ -23,17 +23,21 @@ import junit.framework.TestCase; +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFRichTextString; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.CreationHelper; +import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.XSSFTestDataSamples; import org.apache.poi.xssf.model.CommentsTable; +import org.apache.poi.xssf.model.SharedStringSource; import org.apache.poi.xssf.model.SharedStringsTable; -import org.apache.poi.xssf.model.SharedStringSource; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComments; @@ -108,6 +112,10 @@ } catch (NumberFormatException e) { // success } + + cell.setCellValue(cal); + assertEquals(before1904,cell.getDateCellValue()); + } public void testSetGetError() throws Exception { @@ -379,4 +387,82 @@ fail(); } catch(IllegalArgumentException e) {} } + + + public void testHSSFXSSFToString(){ + Workbook xwb = new XSSFWorkbook(); + Sheet xsheet = xwb.createSheet(); + XSSFCell xcell = (XSSFCell) xsheet.createRow(0).createCell((short)0); + + Workbook hwb=new HSSFWorkbook(); + Sheet hsheet=hwb.createSheet(); + HSSFCell hcell = (HSSFCell) hsheet.createRow(0).createCell((short)0); + + //BLANK + assertEquals(hcell.toString(),xcell.toString()); + System.out.println("BLANK==> xssf="+xcell.toString() + " - hssf="+hcell.toString()); + //BOOLEAN + xcell.setCellValue(true); + xcell.setCellType(Cell.CELL_TYPE_BOOLEAN); + hcell.setCellValue(true); + hcell.setCellType(Cell.CELL_TYPE_BOOLEAN); + System.out.println("BOOLEAN==> xssf="+xcell.toString() + " - hssf="+hcell.toString()); + assertEquals(hcell.toString(),xcell.toString()); + + //NUMERIC + + xcell.setCellValue(1234); + xcell.setCellType(Cell.CELL_TYPE_NUMERIC); + hcell.setCellValue(1234); + hcell.setCellType(Cell.CELL_TYPE_NUMERIC); + System.out.println("NUMERIC==> xssf="+xcell.toString() + " - hssf="+hcell.toString()); + assertEquals(hcell.toString(),xcell.toString()); + + //DATE ******************** + + Calendar cal = Calendar.getInstance(); + cal.set(1903, 1, 8); + xcell.setCellValue(cal.getTime()); + CellStyle xstyle=xwb.createCellStyle(); + DataFormat format = xwb.createDataFormat(); + xstyle.setDataFormat(format.getFormat("YYYY-MM-DD")); + xcell.setCellStyle(xstyle); + + hcell.setCellValue(cal.getTime()); + CellStyle hstyle=hwb.createCellStyle(); + DataFormat hformat = hwb.createDataFormat(); + hstyle.setDataFormat(hformat.getFormat("YYYY-MM-DD")); + hcell.setCellStyle(hstyle); + + System.out.println("DATE==> xssf="+xcell.toString() + " - hssf="+hcell.toString()); + assertEquals(hcell.toString(),xcell.toString()); + + + //STRING + xcell.setCellValue(new XSSFRichTextString("text string")); + xcell.setCellType(Cell.CELL_TYPE_STRING); + hcell.setCellValue(new HSSFRichTextString("text string")); + hcell.setCellType(Cell.CELL_TYPE_STRING); + System.out.println("STRING==> xssf="+xcell.toString() + " - hssf="+hcell.toString()); + assertEquals(hcell.toString(),xcell.toString()); + + //ERROR + xcell.setCellErrorValue(Cell.ERROR_VALUE); + xcell.setCellType(Cell.CELL_TYPE_ERROR); + + hcell.setCellErrorValue((byte)0); + hcell.setCellType(Cell.CELL_TYPE_ERROR); + + System.out.println("ERROR==> xssf="+xcell.toString() + " - hssf="+hcell.toString()); + assertEquals(hcell.toString(),xcell.toString()); + + //FORMULA + xcell.setCellFormula("A1+B2"); + hcell.setCellValue("A1+B2"); + System.out.println("FORMULA==> xssf="+xcell.toString() + " - hssf="+hcell.toString()); + assertEquals(hcell.toString(),xcell.toString()); + + } + + }