You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2008/10/23 20:57:29 UTC
svn commit: r707445 [1/2] - in /poi/branches/ooxml/src/ooxml:
interfaces-jdk15/org/apache/poi/ss/usermodel/
java/org/apache/poi/xssf/usermodel/ testcases/org/apache/poi/xssf/usermodel/
Author: yegor
Date: Thu Oct 23 11:57:28 2008
New Revision: 707445
URL: http://svn.apache.org/viewvc?rev=707445&view=rev
Log:
1. important algorithmic improvements of XSSFRow and XSSFSheet, removed LinkedList in favor of TreeMap, that allowed O(Log(N)) performance instead of O(N) when adding new rows and cells2. Revised cell value accessors in XSSFCell. Now both HSSF and XSSF handle various cell types equally. The same exceptions are thrown in case of type mismatch, same behaviour when setting nulls, etc.
3. Moved FormulaError codes out of the Cell interface into enum. Interface isn't a proper place for it
4. Finally finished javadoc on XSSFRow and XSSFCell
Added:
poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/FormulaError.java (with props)
Modified:
poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java
poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java
Modified: poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java?rev=707445&r1=707444&r2=707445&view=diff
==============================================================================
--- poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java (original)
+++ poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java Thu Oct 23 11:57:28 2008
@@ -70,26 +70,6 @@
public final static int CELL_TYPE_ERROR = 5;
- public final static class CELL_ERROR_TYPE {
- private final byte type;
- private final String repr;
- private CELL_ERROR_TYPE(int type, String repr) {
- this.type = (byte)type;
- this.repr = repr;
- }
-
- public byte getType() { return type; }
- public String getStringRepr() { return repr; }
- }
- public static final CELL_ERROR_TYPE ERROR_NULL = new CELL_ERROR_TYPE(0, "#NULL!");
- public static final CELL_ERROR_TYPE ERROR_DIV0 = new CELL_ERROR_TYPE(7, "#DIV/0!");
- public static final CELL_ERROR_TYPE ERROR_VALUE = new CELL_ERROR_TYPE(15, "#VALUE!");
- public static final CELL_ERROR_TYPE ERROR_REF = new CELL_ERROR_TYPE(23, "#REF!");
- public static final CELL_ERROR_TYPE ERROR_NAME = new CELL_ERROR_TYPE(29, "#NAME?");
- public static final CELL_ERROR_TYPE ERROR_NUM = new CELL_ERROR_TYPE(36, "#NUM!");
- public static final CELL_ERROR_TYPE ERROR_NA = new CELL_ERROR_TYPE(42, "#N/A");
-
-
/**
* set the cell's number within the row (0 based)
* @param num short the cell number
@@ -239,12 +219,12 @@
byte getErrorCellValue();
/**
- * set the style for the cell. The style should be an HSSFCellStyle created/retreived from
- * the HSSFWorkbook.
+ * set the style for the cell. The style should be an CellStyle created/retreived from
+ * the Workbook.
*
* @param style reference contained in the workbook
- * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createCellStyle()
- * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#getCellStyleAt(short)
+ * @see Workbook#createCellStyle()
+ * @see Workbook#getCellStyleAt(short)
*/
void setCellStyle(CellStyle style);
@@ -252,7 +232,7 @@
/**
* get the style for the cell. This is a reference to a cell style contained in the workbook
* object.
- * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#getCellStyleAt(short)
+ * @see Workbook#getCellStyleAt(short)
*/
CellStyle getCellStyle();
@@ -263,20 +243,6 @@
void setAsActiveCell();
/**
- * 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>
- */
- String toString();
-
- /**
* Assign a comment to this cell
*
* @param comment comment associated with this cell
@@ -295,12 +261,12 @@
*
* @return hyperlink associated with this cell or null if not found
*/
- public Hyperlink getHyperlink();
+ Hyperlink getHyperlink();
/**
* Assign a hypelrink to this cell
*
* @param link hypelrink associated with this cell
*/
- public void setHyperlink(Hyperlink link);
+ void setHyperlink(Hyperlink link);
}
Added: poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/FormulaError.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/FormulaError.java?rev=707445&view=auto
==============================================================================
--- poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/FormulaError.java (added)
+++ poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/FormulaError.java Thu Oct 23 11:57:28 2008
@@ -0,0 +1,140 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.usermodel;
+
+import java.util.Map;
+import java.util.HashMap;
+
+/**
+ * Enumerates error values in SpreadsheetML formula calculations.
+ *
+ * @author Yegor Kozlov
+ */
+public enum FormulaError {
+ /**
+ * Intended to indicate when two areas are required to intersect, but do not.
+ * <p>Example:
+ * In the case of SUM(B1 C1), the space between B1 and C1 is treated as the binary
+ * intersection operator, when a comma was intended. end example]
+ * </p>
+ */
+ NULL(0x00, "#NULL!"),
+
+ /**
+ * Intended to indicate when any number, including zero, is divided by zero.
+ * Note: However, any error code divided by zero results in that error code.
+ */
+ DIV0(0x07, "#DIV/0!"),
+
+ /**
+ * Intended to indicate when an incompatible type argument is passed to a function, or
+ * an incompatible type operand is used with an operator.
+ * <p>Example:
+ * In the case of a function argument, text was expected, but a number was provided
+ * </p>
+ */
+ VALUE(0x0F, "#VALUE!"),
+
+ /**
+ * Intended to indicate when a cell reference is invalid.
+ * <p>Example:
+ * If a formula contains a reference to a cell, and then the row or column containing that cell is deleted,
+ * a #REF! error results. If a worksheet does not support 20,001 columns,
+ * OFFSET(A1,0,20000) will result in a #REF! error.
+ * </p>
+ */
+ REF(0x1D, "#REF!"),
+
+ /**
+ * Intended to indicate when what looks like a name is used, but no such name has been defined.
+ * <p>Example:
+ * XYZ/3, where XYZ is not a defined name. Total is & A10,
+ * where neither Total nor is is a defined name. Presumably, "Total is " & A10
+ * was intended. SUM(A1C10), where the range A1:C10 was intended.
+ * </p>
+ */
+ NAME(0x1D, "#NAME?"),
+
+ /**
+ * Intended to indicate when an argument to a function has a compatible type, but has a
+ * value that is outside the domain over which that function is defined. (This is known as
+ * a domain error.)
+ * <p>Example:
+ * Certain calls to ASIN, ATANH, FACT, and SQRT might result in domain errors.
+ * </p>
+ * Intended to indicate that the result of a function cannot be represented in a value of
+ * the specified type, typically due to extreme magnitude. (This is known as a range
+ * error.)
+ * <p>Example: FACT(1000) might result in a range error. </p>
+ */
+ NUM(0x24, "#NUM!"),
+
+ /**
+ * Intended to indicate when a designated value is not available.
+ * <p>Example:
+ * Some functions, such as SUMX2MY2, perform a series of operations on corresponding
+ * elements in two arrays. If those arrays do not have the same number of elements, then
+ * for some elements in the longer array, there are no corresponding elements in the
+ * shorter one; that is, one or more values in the shorter array are not available.
+ * </p>
+ * This error value can be produced by calling the function NA
+ */
+ NA(0x2A, "#N/A");
+
+ private byte type;
+ private String repr;
+
+ private FormulaError(int type, String repr) {
+ this.type = (byte) type;
+ this.repr = repr;
+ }
+
+ /**
+ * @return numeric code of the error
+ */
+ public int getCode() {
+ return type;
+ }
+
+ /**
+ * @return string representation of the error
+ */
+ public String getString() {
+ return repr;
+ }
+
+ private static Map<String, FormulaError> smap = new HashMap<String, FormulaError>();
+ private static Map<Integer, FormulaError> imap = new HashMap<Integer, FormulaError>();
+ static{
+ for (FormulaError error : values()) {
+ imap.put(error.getCode(), error);
+ smap.put(error.getString(), error);
+ }
+ }
+
+ public static FormulaError forInt(int type){
+ FormulaError err = imap.get(type);
+ if(err == null) throw new IllegalArgumentException("Unknown error type: " + type);
+ return err;
+ }
+
+ public static FormulaError forString(String code){
+ FormulaError err = smap.get(code);
+ if(err == null) throw new IllegalArgumentException("Unknown error code: " + code);
+ return err;
+ }
+}
Propchange: poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/FormulaError.java
------------------------------------------------------------------------------
svn:executable = *
Modified: poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java?rev=707445&r1=707444&r2=707445&view=diff
==============================================================================
--- poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (original)
+++ poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java Thu Oct 23 11:57:28 2008
@@ -23,18 +23,8 @@
import java.util.Date;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
-import org.apache.poi.ss.usermodel.DateUtil;
-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.Hyperlink;
-import org.apache.poi.ss.usermodel.RichTextString;
-import org.apache.poi.ss.usermodel.Sheet;
-import org.apache.poi.ss.usermodel.StylesSource;
+import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
-import org.apache.poi.util.POILogFactory;
-import org.apache.poi.util.POILogger;
-import org.apache.poi.xssf.model.SharedStringSource;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
@@ -42,21 +32,63 @@
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType;
/**
- *
+ * High level representation of a cell in a row of a spreadsheet.
+ * <p>
+ * Cells can be numeric, formula-based or string-based (text). The cell type
+ * specifies this. String cells cannot conatin numbers and numeric cells cannot
+ * contain strings (at least according to our model). Client apps should do the
+ * conversions themselves. Formula cells have the formula string, as well as
+ * the formula result, which can be numeric or string.
+ * </p>
+ * <p>
+ * Cells should have their number (0 based) before being added to a row. Only
+ * cells that have values should be added.
+ * </p>
*/
public final class XSSFCell implements Cell {
+ /**
+ * The maximum number of columns in SpreadsheetML
+ */
+ private static final int MAX_COLUMN_NUMBER = 16384;
+
private static final String FALSE_AS_STRING = "0";
private static final String TRUE_AS_STRING = "1";
+
+ /**
+ * the xml bean containing information about the cell's location, value,
+ * data type, formatting, and formula
+ */
private final CTCell cell;
+
+ /**
+ * the XSSFRow this cell belongs to
+ */
private final XSSFRow row;
+
+ /**
+ * 0-based column index
+ */
private int cellNum;
+
+ /**
+ * Table of strings shared across this workbook.
+ * If two cells contain the same string, then the cell value is the same index into SharedStringsTable
+ */
private SharedStringsTable sharedStringSource;
- private StylesTable stylesSource;
- private POILogger logger = POILogFactory.getLogger(XSSFCell.class);
+ /**
+ * Table of cell styles shared across all cells in a workbook.
+ */
+ private StylesTable stylesSource;
- public XSSFCell(XSSFRow row, CTCell cell) {
+ /**
+ * Construct a XSSFCell.
+ *
+ * @param row the xml bean containing information about the cell.
+ * @param row the parent row.
+ */
+ protected XSSFCell(XSSFRow row, CTCell cell) {
this.cell = cell;
this.row = row;
if (cell.getR() != null) {
@@ -66,54 +98,284 @@
this.stylesSource = row.getSheet().getWorkbook().getStylesSource();
}
+ /**
+ * @return table of strings shared across this workbook
+ */
protected SharedStringsTable getSharedStringSource() {
- return this.sharedStringSource;
+ return sharedStringSource;
}
- protected StylesSource getStylesSource() {
- return this.stylesSource;
+ /**
+ * @return table of cell styles shared across this workbook
+ */
+ protected StylesTable getStylesSource() {
+ return stylesSource;
}
- public Sheet getSheet() {
- return this.row.getSheet();
+ /**
+ * Returns the sheet this cell belongs to
+ *
+ * @return the sheet this cell belongs to
+ */
+ public XSSFSheet getSheet() {
+ return getRow().getSheet();
}
+ /**
+ * Returns the row this cell belongs to
+ *
+ * @return the row this cell belongs to
+ */
+ public XSSFRow getRow() {
+ return row;
+ }
+
+ /**
+ * Get the value of the cell as a boolean.
+ * <p>
+ * For strings, numbers, and errors, we throw an exception. For blank cells we return a false.
+ * </p>
+ * @return the value of the cell as a boolean
+ * @throws IllegalStateException if the cell type returned by {@link #getCellType()}
+ * is not CELL_TYPE_BOOLEAN, CELL_TYPE_BLANK or CELL_TYPE_FORMULA
+ */
public boolean getBooleanCellValue() {
- if (STCellType.B != cell.getT()) {
- throw new NumberFormatException("You cannot get a boolean value from a non-boolean cell");
+ int cellType = getCellType();
+ switch(cellType) {
+ case CELL_TYPE_BLANK:
+ return false;
+ case CELL_TYPE_BOOLEAN:
+ return cell.isSetV() && TRUE_AS_STRING.equals(cell.getV());
+ case CELL_TYPE_FORMULA:
+ //YK: should throw an exception if requesting boolean value from a non-boolean formula
+ return cell.isSetV() && TRUE_AS_STRING.equals(cell.getV());
+ default:
+ throw typeMismatch(CELL_TYPE_BOOLEAN, cellType, false);
}
- if (cell.isSetV()) {
- return (TRUE_AS_STRING.equals(this.cell.getV()));
+ }
+
+ /**
+ * Set a boolean value for the cell
+ *
+ * @param value the boolean value to set this cell to. For formulas we'll set the
+ * precalculated value, for booleans we'll set its value. For other types we
+ * will change the cell to a boolean cell and set its value.
+ */
+ public void setCellValue(boolean value) {
+ cell.setT(STCellType.B);
+ cell.setV(value ? TRUE_AS_STRING : FALSE_AS_STRING);
+ }
+
+ /**
+ * Get the value of the cell as a number.
+ * <p>
+ * For strings we throw an exception. For blank cells we return a 0.
+ * For formulas or error cells we return the precalculated value;
+ * </p>
+ * @return the value of the cell as a number
+ * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is CELL_TYPE_STRING
+ * @exception NumberFormatException if the cell value isn't a parsable <code>double</code>.
+ */
+ public double getNumericCellValue() {
+ int cellType = getCellType();
+ switch(cellType) {
+ case CELL_TYPE_BLANK:
+ return 0.0;
+ case CELL_TYPE_ERROR:
+ case CELL_TYPE_FORMULA:
+ case CELL_TYPE_NUMERIC:
+ return cell.isSetV() ? Double.parseDouble(cell.getV()) : 0.0;
+ default:
+ throw typeMismatch(CELL_TYPE_NUMERIC, cellType, false);
}
+ }
+
- return false;
+ /**
+ * Set a numeric value for the cell
+ *
+ * @param value the numeric 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(double value) {
+ int cellType = getCellType();
+ switch (cellType) {
+ case CELL_TYPE_ERROR:
+ case CELL_TYPE_FORMULA:
+ cell.setV(String.valueOf(value));
+ break;
+ default:
+ cell.setT(STCellType.N);
+ cell.setV(String.valueOf(value));
+ break;
+ }
}
- public Comment getCellComment() {
- return row.getSheet().getCellComment(row.getRowNum(), getColumnIndex());
+ /**
+ * Get the value of the cell as a string
+ * <p>
+ * For numeric cells we throw an exception. For blank cells we return an empty string.
+ * For formulaCells that are not string Formulas, we return empty String.
+ * </p>
+ * @return the value of the cell as a string
+ */
+ public String getStringCellValue() {
+ XSSFRichTextString str = getRichStringCellValue();
+ return str == null ? null : str.getString();
}
- public String getCellFormula() {
- if(this.cell.getF() == null) {
- throw new NumberFormatException("You cannot get a formula from a non-formula cell");
+ /**
+ * Get the value of the cell as a XSSFRichTextString
+ * <p>
+ * For numeric cells we throw an exception. For blank cells we return an empty string.
+ * For formula cells we return the pre-calculated value.
+ * </p>
+ * @return the value of the cell as a XSSFRichTextString
+ */
+ public XSSFRichTextString getRichStringCellValue() {
+ int cellType = getCellType();
+ XSSFRichTextString rt;
+ switch (cellType) {
+ case CELL_TYPE_BLANK:
+ rt = new XSSFRichTextString("");
+ break;
+ case CELL_TYPE_STRING:
+ if (!cell.isSetV()) rt = new XSSFRichTextString("");
+ else {
+ if (cell.getT() == STCellType.INLINE_STR) {
+ return new XSSFRichTextString(cell.getV());
+ } else {
+ int idx = Integer.parseInt(cell.getV());
+ rt = new XSSFRichTextString(sharedStringSource.getEntryAt(idx));
+ }
+ }
+ break;
+ case CELL_TYPE_FORMULA:
+ rt = new XSSFRichTextString(cell.isSetV() ? cell.getV() : "");
+ break;
+ default:
+ throw typeMismatch(CELL_TYPE_STRING, cellType, false);
}
+ rt.setStylesTableReference(stylesSource);
+ return rt;
+ }
+
+ /**
+ * Set a string value for the cell.
+ *
+ * @param str value to set the cell to. For formulas we'll set the formula
+ * cached string result, for String cells we'll set its value. For other types we will
+ * change the cell to a string cell and set its value.
+ * If value is null then we will change the cell to a Blank cell.
+ */
+ public void setCellValue(String str) {
+ setCellValue(str == null ? null : new XSSFRichTextString(str));
+ }
+
+ /**
+ * Set a string value for the cell.
+ *
+ * @param str value to set the cell to. For formulas we'll set the 'pre-evaluated result string,
+ * for String cells we'll set its value. For other types we will
+ * change the cell to a string cell and set its value.
+ * If value is null then we will change the cell to a Blank cell.
+ */
+ public void setCellValue(RichTextString str) {
+ if(str == null){
+ setBlank();
+ return;
+ }
+ int cellType = getCellType();
+ switch(cellType){
+ case Cell.CELL_TYPE_FORMULA:
+ cell.setV(str.getString());
+ break;
+ default:
+ if(cell.getT() == STCellType.INLINE_STR) {
+ //set the 'pre-evaluated result
+ cell.setV(str.getString());
+ } else {
+ cell.setT(STCellType.S);
+ XSSFRichTextString rt = (XSSFRichTextString)str;
+ rt.setStylesTableReference(stylesSource);
+ int sRef = sharedStringSource.addEntry(rt.getCTRst());
+ cell.setV(Integer.toString(sRef));
+ }
+ break;
+ }
+ }
+
+ /**
+ * Return a formula for the cell, for example, <code>SUM(C4:E4)</code>
+ *
+ * @return a formula for the cell
+ * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is not CELL_TYPE_FORMULA
+ */
+ public String getCellFormula() {
+ int cellType = getCellType();
+ if(cellType != CELL_TYPE_FORMULA) throw typeMismatch(CELL_TYPE_FORMULA, cellType, false);
+
return this.cell.getF().getStringValue();
}
/**
+ * Sets formula for this cell.
+ * <p>
+ * Note, this method only sets the formula string and does not calculate the formula value.
+ * To set the precalculated value use {@link #setCellValue(double)} or {@link #setCellValue(String)}
+ * </p>
+ *
+ * @param formula the formula to set, e.g. <code>SUM(C4:E4)</code>.
+ * If the argument is <code>null</code> then the current formula is removed.
+ */
+ public void setCellFormula(String formula) {
+ if (formula == null && cell.isSetF()) {
+ cell.unsetF();
+ return;
+ }
+
+ CTCellFormula f = CTCellFormula.Factory.newInstance();
+ f.setStringValue(formula);
+ cell.setF(f);
+ if(cell.isSetV()) cell.unsetV();
+ }
+
+ /**
* @deprecated use {@link #getColumnIndex()}
*/
public short getCellNum() {
return (short)getColumnIndex();
}
+
+ /**
+ * Returns column index of this cell
+ *
+ * @return zero-based column index of a column in a sheet.
+ */
public int getColumnIndex() {
return this.cellNum;
}
- public int getRowIndex() {
+
+ /**
+ * Returns row index of a row in the sheet that contains this cell
+ *
+ * @return zero-based row index of a row in the sheet that contains this cell
+ */
+ public int getRowIndex() {
return row.getRowNum();
}
/**
+ * Returns an A1 style reference to the location of this cell
+ *
+ * @return A1 style reference to the location of this cell
+ */
+ public String getReference() {
+ return cell.getR();
+ }
+
+ /**
* Return the cell's style.
*
* @return the cell's style. Always not-null. Default cell style has zero index and can be obtained as
@@ -124,217 +386,236 @@
return stylesSource.getStyleAt(idx);
}
+ /**
+ * Set the style for the cell. The style should be an XSSFCellStyle created/retreived from
+ * the XSSFWorkbook.
+ *
+ * @param style reference contained in the workbook.
+ * If the value is null then the style information is removed causing the cell to used the default workbook style.
+ */
+ public void setCellStyle(CellStyle style) {
+ if(style == null) {
+ cell.unsetS();
+ } else {
+ XSSFCellStyle xStyle = (XSSFCellStyle)style;
+ xStyle.verifyBelongsToStylesSource(stylesSource);
+
+ long idx = stylesSource.putStyle(xStyle);
+ cell.setS(idx);
+ }
+ }
+
+ /**
+ * Return the cell type.
+ *
+ * @return the cell type
+ * @see Cell#CELL_TYPE_BLANK
+ * @see Cell#CELL_TYPE_NUMERIC
+ * @see Cell#CELL_TYPE_STRING
+ * @see Cell#CELL_TYPE_FORMULA
+ * @see Cell#CELL_TYPE_BOOLEAN
+ * @see Cell#CELL_TYPE_ERROR
+ */
public int getCellType() {
- // Detecting formulas is quite pesky,
- // as they don't get their type set
- if(this.cell.getF() != null) {
+
+ if (cell.getF() != null) {
return CELL_TYPE_FORMULA;
}
switch (this.cell.getT().intValue()) {
- case STCellType.INT_B:
- return CELL_TYPE_BOOLEAN;
- case STCellType.INT_N:
- if(!cell.isSetV()) {
- // ooxml does have a separate cell type of 'blank'. A blank cell gets encoded as
- // (either not present or) a numeric cell with no value set.
- // The formula evaluator (and perhaps other clients of this interface) needs to
- // distinguish blank values which sometimes get translated into zero and sometimes
- // empty string, depending on context
- return CELL_TYPE_BLANK;
- }
- return CELL_TYPE_NUMERIC;
- case STCellType.INT_E:
- return CELL_TYPE_ERROR;
- case STCellType.INT_S: // String is in shared strings
- case STCellType.INT_INLINE_STR: // String is inline in cell
- return CELL_TYPE_STRING;
- case STCellType.INT_STR:
- return CELL_TYPE_FORMULA;
- default:
- throw new IllegalStateException("Illegal cell type: " + this.cell.getT());
+ case STCellType.INT_B:
+ return CELL_TYPE_BOOLEAN;
+ case STCellType.INT_N:
+ if (!cell.isSetV()) {
+ // ooxml does have a separate cell type of 'blank'. A blank cell gets encoded as
+ // (either not present or) a numeric cell with no value set.
+ // The formula evaluator (and perhaps other clients of this interface) needs to
+ // distinguish blank values which sometimes get translated into zero and sometimes
+ // empty string, depending on context
+ return CELL_TYPE_BLANK;
+ }
+ return CELL_TYPE_NUMERIC;
+ case STCellType.INT_E:
+ return CELL_TYPE_ERROR;
+ case STCellType.INT_S: // String is in shared strings
+ case STCellType.INT_INLINE_STR: // String is inline in cell
+ return CELL_TYPE_STRING;
+ case STCellType.INT_STR:
+ return CELL_TYPE_FORMULA;
+ default:
+ throw new IllegalStateException("Illegal cell type: " + this.cell.getT());
}
}
+ /**
+ * Get the value of the cell as a date.
+ * <p>
+ * For strings we throw an exception. For blank cells we return a null.
+ * </p>
+ * @return the value of the cell as a date
+ * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is CELL_TYPE_STRING
+ * @exception NumberFormatException if the cell value isn't a parsable <code>double</code>.
+ */
public Date getDateCellValue() {
- if (STCellType.N == this.cell.getT() || STCellType.STR == this.cell.getT()) {
- double value = this.getNumericCellValue();
- if (row.getSheet().getWorkbook().isDate1904()) {
- return DateUtil.getJavaDate(value,true);
- }
- else {
- return DateUtil.getJavaDate(value,false);
- }
+ int cellType = getCellType();
+ if (cellType == CELL_TYPE_BLANK) {
+ return null;
}
- throw new NumberFormatException("You cannot get a date value from a cell of type " + this.cell.getT());
+
+ double value = getNumericCellValue();
+ boolean date1904 = getSheet().getWorkbook().isDate1904();
+ return DateUtil.getJavaDate(value, date1904);
}
/**
- * Returns the error message, such as #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 String getErrorCellString() {
- if (STCellType.E != cell.getT()) {
- throw new NumberFormatException("You cannot get a error value from a non-error cell");
- }
- if (this.cell.isSetV()) {
- return this.cell.getV();
- }
- return null;
+ public void setCellValue(Date value) {
+ boolean date1904 = getSheet().getWorkbook().isDate1904();
+ setCellValue(DateUtil.getExcelDate(value, date1904));
}
+
/**
- * Returns the error type, in the same way that
- * HSSFCell does. See {@link Cell} for details
+ * Set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
+ * a date.
+ * <p>
+ * 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 <code>setCellValue(value.getTime())</code> which will
+ * automatically shift the times to the default timezone.
+ * </p>
+ *
+ * @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 byte getErrorCellValue() {
- if (STCellType.E != cell.getT()) {
- throw new NumberFormatException("You cannot get a error value from a non-error cell");
- }
- if (this.cell.isSetV()) {
- String errS = this.cell.getV();
- if(errS.equals(Cell.ERROR_NULL.getStringRepr())) {
- return Cell.ERROR_NULL.getType();
- }
- if(errS.equals(Cell.ERROR_DIV0.getStringRepr())) {
- return Cell.ERROR_DIV0.getType();
- }
- if(errS.equals(Cell.ERROR_VALUE.getStringRepr())) {
- return Cell.ERROR_VALUE.getType();
- }
- if(errS.equals(Cell.ERROR_REF.getStringRepr())) {
- return Cell.ERROR_REF.getType();
- }
- if(errS.equals(Cell.ERROR_NAME.getStringRepr())) {
- return Cell.ERROR_NAME.getType();
- }
- if(errS.equals(Cell.ERROR_NUM.getStringRepr())) {
- return Cell.ERROR_NUM.getType();
- }
- return Cell.ERROR_NA.getType();
- }
- return 0;
+ public void setCellValue(Calendar value) {
+ boolean date1904 = getSheet().getWorkbook().isDate1904();
+ setCellValue( DateUtil.getExcelDate(value, date1904 ));
}
- public double getNumericCellValue() {
- if (STCellType.N != cell.getT() && STCellType.STR != cell.getT()) {
- throw new NumberFormatException("You cannot get a numeric value from a non-numeric cell");
- }
- if (this.cell.isSetV()) {
- return Double.parseDouble(this.cell.getV());
- }
- // else - cell is blank.
+ /**
+ * Returns the error message, such as #VALUE!
+ *
+ * @return the error message such as #VALUE!
+ * @throws IllegalStateException if the cell type returned by {@link #getCellType()} isn't CELL_TYPE_ERROR
+ * @see FormulaError
+ */
+ public String getErrorCellString() {
+ int cellType = getCellType();
+ if(cellType != CELL_TYPE_ERROR) throw typeMismatch(CELL_TYPE_ERROR, cellType, false);
- // TODO - behaviour in the case of blank cells.
- // Revise spec, choose best alternative below, and comment why.
- if (true) {
- // returning NaN from a blank cell seems wrong
- // there are a few junits which assert this behaviour, though.
- return Double.NaN;
- }
- if (true) {
- // zero is probably a more reasonable value.
- return 0.0;
- } else {
- // or perhaps disallow reading value from blank cell.
- throw new RuntimeException("You cannot get a numeric value from a blank cell");
- }
- // Note - it would be nice if the behaviour is consistent with getRichStringCellValue
- // (i.e. whether to return empty string or throw exception).
+ return cell.getV();
}
+ /**
+ * Get the value of the cell as an error code.
+ * <p>
+ * For strings, numbers, and booleans, we throw an exception.
+ * For blank cells we return a 0.
+ * </p>
+ *
+ * @return the value of the cell as an error code
+ * @throws IllegalStateException if the cell type returned by {@link #getCellType()} isn't CELL_TYPE_ERROR
+ * @see FormulaError
+ */
+ public byte getErrorCellValue() {
+ String code = getErrorCellString();
+ if(code == null) return 0;
- public XSSFRichTextString getRichStringCellValue() {
- if(this.cell.getT() == STCellType.INLINE_STR) {
- if(this.cell.isSetV()) {
- return new XSSFRichTextString(this.cell.getV());
- } else {
- return new XSSFRichTextString("");
- }
- }
- if(this.cell.getT() == STCellType.S) {
- XSSFRichTextString rt;
- if(this.cell.isSetV()) {
- int sRef = Integer.parseInt(this.cell.getV());
- rt = new XSSFRichTextString(sharedStringSource.getEntryAt(sRef));
- } else {
- rt = new XSSFRichTextString("");
- }
- rt.setStylesTableReference(stylesSource);
- return rt;
- }
- throw new NumberFormatException("You cannot get a string value from a non-string cell");
+ return (byte)FormulaError.forString(code).getCode();
}
-
+
/**
- * Sets this cell as the active cell for the worksheet
+ * Set a error value for the cell
+ *
+ * @param errorCode the error value to set this cell to. For formulas we'll set the
+ * precalculated value , for errors we'll set
+ * its value. For other types we will change the cell to an error
+ * cell and set its value.
+ * @see FormulaError
*/
- public void setAsActiveCell() {
- row.getSheet().setActiveCell(cell.getR());
+ public void setCellErrorValue(byte errorCode) {
+ FormulaError error = FormulaError.forInt(errorCode);
+ setCellErrorValue(error);
}
-
- public void setCellComment(Comment comment) {
- String cellRef = new CellReference(row.getRowNum(), getColumnIndex()).formatAsString();
- row.getSheet().setCellComment(cellRef, (XSSFComment)comment);
+ /**
+ * Set a error value for the cell
+ *
+ * @param error the error value to set this cell to. For formulas we'll set the
+ * precalculated value , for errors we'll set
+ * its value. For other types we will change the cell to an error
+ * cell and set its value.
+ */
+ public void setCellErrorValue(FormulaError error) {
+ cell.setT(STCellType.E);
+ cell.setV(error.getString());
}
- public void setCellErrorValue(byte value) {
- if(value == Cell.ERROR_DIV0.getType()) {
- setCellErrorValue(Cell.ERROR_DIV0);
- } else if(value == Cell.ERROR_NA.getType()) {
- setCellErrorValue(Cell.ERROR_NA);
- } else if(value == Cell.ERROR_NAME.getType()) {
- setCellErrorValue(Cell.ERROR_NAME);
- } else if(value == Cell.ERROR_NULL.getType()) {
- setCellErrorValue(Cell.ERROR_NULL);
- } else if(value == Cell.ERROR_NUM.getType()) {
- setCellErrorValue(Cell.ERROR_NUM);
- } else if(value == Cell.ERROR_REF.getType()) {
- setCellErrorValue(Cell.ERROR_REF);
- } else if(value == Cell.ERROR_VALUE.getType()) {
- setCellErrorValue(Cell.ERROR_VALUE);
- } else {
- logger.log(POILogger.WARN, "Unknown error type " + value + " specified, treating as #N/A");
- setCellErrorValue(Cell.ERROR_NA);
- }
- }
- public void setCellErrorValue(CELL_ERROR_TYPE errorType) {
- if ((this.cell.getT() != STCellType.E) && (this.cell.getT() != STCellType.STR))
- {
- this.cell.setT(STCellType.E);
- }
- this.cell.setV( errorType.getStringRepr() );
+ /**
+ * Sets this cell as the active cell for the worksheet.
+ */
+ public void setAsActiveCell() {
+ getSheet().setActiveCell(cell.getR());
}
-
- public void setCellFormula(String formula) {
- CTCellFormula f = CTCellFormula.Factory.newInstance();
- f.setStringValue(formula);
- this.cell.setF(f);
- // XXX: is this correct? Should we recompute the value when the formula changes?
- if (this.cell.isSetV()) {
- this.cell.unsetV();
- }
-
+ /**
+ * Blanks this cell. Blank cells have no formula or value but may have styling.
+ * This method erases all the data previously associated with this cell.
+ */
+ private void setBlank(){
+ CTCell blank = CTCell.Factory.newInstance();
+ blank.setR(cell.getR());
+ cell.set(blank);
}
+ /**
+ * Sets column index of this cell
+ *
+ * @param num column index of this cell
+ */
public void setCellNum(int num) {
setCellNum((short)num);
}
+
+ /**
+ * Sets column index of this cell
+ *
+ * @param num column index of this cell
+ */
public void setCellNum(short num) {
checkBounds(num);
- this.cellNum = num;
- this.cell.setR(formatPosition());
+ cellNum = num;
+ cell.setR(formatPosition());
}
+ /**
+ * Converts A1 style reference into 0-based column index
+ *
+ * @param r an A1 style reference to the location of this cell
+ * @return 0-based column index
+ */
protected static short parseCellNum(String r) {
r = r.split("\\d+")[0];
if (r.length() == 1) {
return (short) (r.charAt(0) - 'A');
} else {
return (short) (r.charAt(1) - 'A' + 26 * (r.charAt(0) - '@'));
-
}
}
+ /**
+ * Builds an A1 style reference from internal represenetation
+ *
+ * @return an A1 style reference to the location of this cell
+ */
protected String formatPosition() {
int col = this.getColumnIndex();
String result = Character.valueOf((char) (col % 26 + 'A')).toString();
@@ -346,23 +627,10 @@
return result;
}
- public void setCellStyle(CellStyle style) {
- if(style == null) {
- this.cell.setS(0);
- } else {
- XSSFCellStyle xStyle = (XSSFCellStyle)style;
- xStyle.verifyBelongsToStylesSource(
- row.getSheet().getWorkbook().getStylesSource()
- );
-
- this.cell.setS(
- row.getSheet().getWorkbook().getStylesSource().putStyle(xStyle)
- );
- }
- }
-
/**
- * set the cells type (numeric, formula or string)
+ * Set the cells type (numeric, formula or string)
+ *
+ * @throws IllegalArgumentException if the specified cell type is invalid
* @see #CELL_TYPE_NUMERIC
* @see #CELL_TYPE_STRING
* @see #CELL_TYPE_FORMULA
@@ -372,105 +640,43 @@
*/
public void setCellType(int cellType) {
switch (cellType) {
- case CELL_TYPE_BOOLEAN:
- this.cell.setT(STCellType.B);
- break;
- case CELL_TYPE_NUMERIC:
- this.cell.setT(STCellType.N);
- break;
- case CELL_TYPE_ERROR:
- this.cell.setT(STCellType.E);
- break;
- case CELL_TYPE_STRING:
- this.cell.setT(STCellType.S);
- break;
- default:
- throw new IllegalArgumentException("Illegal type: " + cellType);
- }
- }
-
- public void setCellValue(double value) {
- if ((this.cell.getT() != STCellType.N) && (this.cell.getT() != STCellType.STR))
- {
- this.cell.setT(STCellType.N);
- }
- 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) {
- boolean date1904 = this.row.getSheet().getWorkbook().isDate1904();
- setCellValue(DateUtil.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 <code>setCellValue(value.getTime())</code> 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) {
- boolean date1904 = this.row.getSheet().getWorkbook().isDate1904();
- setCellValue( DateUtil.getExcelDate(value, date1904 ));
- }
-
- public void setCellValue(String str) {
- this.setCellValue(new XSSFRichTextString(str));
- }
-
- public void setCellValue(RichTextString value) {
- if(this.cell.getT() == STCellType.INLINE_STR) {
- this.cell.setV(value.getString());
- return;
- }
- if(this.cell.getT() != STCellType.S) {
- this.cell.setT(STCellType.S);
- }
- XSSFRichTextString rt = (XSSFRichTextString)value;
- rt.setStylesTableReference(stylesSource);
- int sRef = sharedStringSource.addEntry(rt.getCTRst());
- this.cell.setV(Integer.toString(sRef));
- }
-
- public void setCellValue(boolean value) {
- if ((this.cell.getT() != STCellType.B) && (this.cell.getT() != STCellType.STR))
- {
- this.cell.setT(STCellType.B);
+ case CELL_TYPE_BLANK:
+ setBlank();
+ break;
+ case CELL_TYPE_BOOLEAN:
+ cell.setT(STCellType.B);
+ break;
+ case CELL_TYPE_NUMERIC:
+ cell.setT(STCellType.N);
+ break;
+ case CELL_TYPE_ERROR:
+ cell.setT(STCellType.E);
+ break;
+ case CELL_TYPE_STRING:
+ cell.setT(STCellType.S);
+ break;
+ case CELL_TYPE_FORMULA:
+ if(!cell.isSetF()){
+ CTCellFormula f = CTCellFormula.Factory.newInstance();
+ f.setStringValue("0");
+ cell.setF(f);
+ cell.unsetT();
+ }
+ break;
+ default:
+ throw new IllegalArgumentException("Illegal cell type: " + cellType);
}
- 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.
+ * <p>
+ * 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>
+ * </p>
*/
public String toString() {
- // return "[" + this.row.getRowNum() + "," + this.getCellNum() + "] " + this.cell.getV();
switch (getCellType()) {
case CELL_TYPE_BLANK:
return "";
@@ -481,7 +687,6 @@
case CELL_TYPE_FORMULA:
return getCellFormula();
case CELL_TYPE_NUMERIC:
- //TODO apply the dataformat for this cell
if (DateUtil.isCellDateFormatted(this)) {
DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
return sdf.format(getDateCellValue());
@@ -497,33 +702,108 @@
/**
* Returns the raw, underlying ooxml value for the cell
+ * <p>
+ * If the cell contains a string, then this value is an index into
+ * the shared string table, pointing to the actual string value. Otherwise,
+ * the value of the cell is expressed directly in this element. Cells containing formulas express
+ * the last calculated result of the formula in this element.
+ * </p>
+ *
+ * @return the raw cell value as contained in the underlying CTCell bean,
+ * <code>null</code> for blank cells.
*/
public String getRawValue() {
- return this.cell.getV();
+ return cell.getV();
+ }
+
+ /**
+ * Used to help format error messages
+ */
+ private static String getCellTypeName(int cellTypeCode) {
+ switch (cellTypeCode) {
+ case CELL_TYPE_BLANK: return "blank";
+ case CELL_TYPE_STRING: return "text";
+ case CELL_TYPE_BOOLEAN: return "boolean";
+ case CELL_TYPE_ERROR: return "error";
+ case CELL_TYPE_NUMERIC: return "numeric";
+ case CELL_TYPE_FORMULA: return "formula";
+ }
+ return "#unknown cell type (" + cellTypeCode + ")#";
+ }
+
+ /**
+ * Used to help format error messages
+ */
+ private static RuntimeException typeMismatch(int expectedTypeCode, int actualTypeCode, boolean isFormulaCell) {
+ String msg = "Cannot get a "
+ + getCellTypeName(expectedTypeCode) + " value from a "
+ + getCellTypeName(actualTypeCode) + " " + (isFormulaCell ? "formula " : "") + "cell";
+ return new IllegalStateException(msg);
}
/**
* @throws RuntimeException if the bounds are exceeded.
*/
- private void checkBounds(int cellNum) {
- if (cellNum > 255) {
- throw new RuntimeException("You cannot have more than 255 columns " +
- "in a given row (IV). Because Excel can't handle it");
+ private static void checkBounds(int cellNum) {
+ if (cellNum > MAX_COLUMN_NUMBER) {
+ throw new RuntimeException("You cannot have more than "+MAX_COLUMN_NUMBER+" columns " +
+ "in a given row because Excel can't handle it");
} else if (cellNum < 0) {
throw new RuntimeException("You cannot reference columns with an index of less then 0.");
}
}
- public Hyperlink getHyperlink() {
- return row.getSheet().getHyperlink(row.getRowNum(), cellNum);
+ /**
+ * Returns cell comment associated with this cell
+ *
+ * @return the cell comment associated with this cell or <code>null</code>
+ */
+ public XSSFComment getCellComment() {
+ return getSheet().getCellComment(row.getRowNum(), getColumnIndex());
+ }
+
+ /**
+ * Assign a comment to this cell. If the supplied comment is null,
+ * the comment for this cell will be removed.
+ *
+ * @param comment comment associated with this cell
+ */
+ public void setCellComment(Comment comment) {
+ String cellRef = new CellReference(row.getRowNum(), getColumnIndex()).formatAsString();
+ getSheet().setCellComment(cellRef, (XSSFComment)comment);
+ }
+
+ /**
+ * Returns hyperlink associated with this cell
+ *
+ * @return hyperlink associated with this cell or <code>null</code> if not found
+ */
+ public XSSFHyperlink getHyperlink() {
+ return getSheet().getHyperlink(row.getRowNum(), cellNum);
}
- public void setHyperlink(Hyperlink hyperlink) {
+
+ /**
+ * Assign a hypelrink to this cell
+ *
+ * @param hyperlink the hypelrink to associate with this cell
+ */
+ public void setHyperlink(Hyperlink hyperlink) {
XSSFHyperlink link = (XSSFHyperlink)hyperlink;
// Assign to us
link.setCellReference( new CellReference(row.getRowNum(), cellNum).formatAsString() );
// Add to the lists
- row.getSheet().setCellHyperlink(link);
+ getSheet().setCellHyperlink(link);
}
+
+ /**
+ * Returns the xml bean containing information about the cell's location (reference), value,
+ * data type, formatting, and formula
+ *
+ * @return the xml bean containing information about this cell
+ */
+ public CTCell getCTCell(){
+ return cell;
+ }
}
Modified: poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java?rev=707445&r1=707444&r2=707445&view=diff
==============================================================================
--- poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java (original)
+++ poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java Thu Oct 23 11:57:28 2008
@@ -866,6 +866,7 @@
* @param fmt the index of a data format
*/
public void setDataFormat(short fmt) {
+ cellXf.setApplyNumberFormat(true);
cellXf.setNumFmtId((long)fmt);
}
Modified: poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java?rev=707445&r1=707444&r2=707445&view=diff
==============================================================================
--- poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java (original)
+++ poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java Thu Oct 23 11:57:28 2008
@@ -17,11 +17,8 @@
package org.apache.poi.xssf.usermodel;
-import java.util.Iterator;
-import java.util.LinkedList;
-import java.util.List;
+import java.util.*;
-import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
@@ -30,28 +27,38 @@
/**
* High level representation of a row of a spreadsheet.
*/
-public class XSSFRow implements Row, Comparable {
+public class XSSFRow implements Row, Comparable<XSSFRow> {
- private CTRow row;
-
- private List<Cell> cells;
-
- private XSSFSheet sheet;
+ /**
+ * the xml bean containing all cell definitions for this row
+ */
+ private final CTRow row;
+
+ /**
+ * Cells of this row keyed by their column indexes.
+ * The TreeMap ensures that the cells are ordered by columnIndex in the ascending order.
+ */
+ private final TreeMap<Integer, Cell> cells;
/**
- * Create a new XSSFRow.
- *
- * @param row The underlying XMLBeans row.
- * @param sheet The parent sheet.
+ * the parent sheet
*/
- public XSSFRow(CTRow row, XSSFSheet sheet) {
+ private final XSSFSheet sheet;
+
+ /**
+ * Construct a XSSFRow.
+ *
+ * @param row the xml bean containing all cell definitions for this row.
+ * @param sheet the parent sheet.
+ */
+ protected XSSFRow(CTRow row, XSSFSheet sheet) {
this.row = row;
this.sheet = sheet;
- this.cells = new LinkedList<Cell>();
+ this.cells = new TreeMap<Integer, Cell>();
for (CTCell c : row.getCArray()) {
- this.cells.add(new XSSFCell(this, c));
+ XSSFCell cell = new XSSFCell(this, c);
+ this.cells.put(cell.getColumnIndex(), cell);
}
-
}
/**
@@ -62,25 +69,39 @@
public XSSFSheet getSheet() {
return this.sheet;
}
-
+
/**
- * @return Cell iterator of the physically defined cells. Note element 4 may
- * actually be row cell depending on how many are defined!
+ * Cell iterator over the physically defined cells:
+ * <blockquote><pre>
+ * for (Iterator<Cell> it = row.cellIterator(); it.hasNext(); ) {
+ * Cell cell = it.next();
+ * ...
+ * }
+ * </pre></blockquote>
+ *
+ * @return an iterator over cells in this row.
*/
public Iterator<Cell> cellIterator() {
- return cells.iterator();
+ return cells.values().iterator();
}
/**
- * Alias for {@link #cellIterator()} to allow
- * foreach loops
+ * Alias for {@link #cellIterator()} to allow foreach loops:
+ * <blockquote><pre>
+ * for(Cell cell : row){
+ * ...
+ * }
+ * </pre></blockquote>
+ *
+ * @return an iterator over cells in this row.
*/
public Iterator<Cell> iterator() {
return cellIterator();
}
/**
- * Compares two <code>XSSFRow</code> objects.
+ * Compares two <code>XSSFRow</code> objects. Two rows are equal if they belong to the same worksheet and
+ * their row indexes are equal.
*
* @param row the <code>XSSFRow</code> to be compared.
* @return the value <code>0</code> if the row number of this <code>XSSFRow</code> is
@@ -89,84 +110,57 @@
* than the row number of the argument <code>XSSFRow</code>; and a value greater
* than <code>0</code> if the row number of this this <code>XSSFRow</code> is numerically
* greater than the row number of the argument <code>XSSFRow</code>.
+ * @throws IllegalArgumentException if the argument row belongs to a different worksheet
*/
- public int compareTo(Object row) {
+ public int compareTo(XSSFRow row) {
int thisVal = this.getRowNum();
- int anotherVal = ((XSSFRow)row).getRowNum();
+ if(row.getSheet() != getSheet()) throw new IllegalArgumentException("The compared rows must belong to the same XSSFSheet");
+
+ int anotherVal = row.getRowNum();
return (thisVal < anotherVal ? -1 : (thisVal == anotherVal ? 0 : 1));
}
/**
* Use this to create new cells within the row and return it.
* <p>
- * The cell that is returned is a CELL_TYPE_BLANK. The type can be changed
+ * The cell that is returned is a {@link Cell#CELL_TYPE_BLANK}. The type can be changed
* either through calling <code>setCellValue</code> or <code>setCellType</code>.
- *
- * @param column - the column number this cell represents
+ * </p>
+ * @param columnIndex - the column number this cell represents
* @return Cell a high level representation of the created cell.
+ * @throws IllegalArgumentException if columnIndex < 0
*/
- public XSSFCell createCell(int column) {
- return createCell(column, Cell.CELL_TYPE_BLANK);
- }
-
- /**
- * Add a new empty cell to this row.
- *
- * @param column Cell column number.
- * @param index Position where to insert cell.
- * @param type cell type, one of Cell.CELL_TYPE_*
- * @return The new cell.
- */
- protected XSSFCell addCell(int column, int index, int type) {
- CTCell ctcell = row.insertNewC(index);
- XSSFCell xcell = new XSSFCell(this, ctcell);
- xcell.setCellNum(column);
- if (type != Cell.CELL_TYPE_BLANK) {
- xcell.setCellType(type);
- }
- return xcell;
+ public XSSFCell createCell(int columnIndex) {
+ return createCell(columnIndex, Cell.CELL_TYPE_BLANK);
}
/**
* Use this to create new cells within the row and return it.
*
- * @param column - the column number this cell represents
+ * @param columnIndex - the column number this cell represents
* @param type - the cell's data type
- *
* @return XSSFCell a high level representation of the created cell.
+ * @throws IllegalArgumentException if columnIndex < 0 or if the specified cell type is invalid
+ * @see Cell#CELL_TYPE_BLANK
+ * @see Cell#CELL_TYPE_BOOLEAN
+ * @see Cell#CELL_TYPE_ERROR
+ * @see Cell#CELL_TYPE_FORMULA
+ * @see Cell#CELL_TYPE_NUMERIC
+ * @see Cell#CELL_TYPE_STRING
*/
- public XSSFCell createCell(int column, int type) {
- int index = 0;
- for (Cell c : this.cells) {
- if (c.getColumnIndex() == column) {
- // Replace c with new Cell
- XSSFCell xcell = addCell(column, index, type);
- cells.set(index, xcell);
- return xcell;
- }
- if (c.getColumnIndex() > column) {
- XSSFCell xcell = addCell(column, index, type);
- cells.add(index, xcell);
- return xcell;
- }
- ++index;
+ public XSSFCell createCell(int columnIndex, int type) {
+ if(columnIndex < 0) throw new IllegalArgumentException("columnIndex must be >= 0, was " + columnIndex);
+
+ CTCell ctcell = CTCell.Factory.newInstance();
+ XSSFCell xcell = new XSSFCell(this, ctcell);
+ xcell.setCellNum(columnIndex);
+ if (type != Cell.CELL_TYPE_BLANK) {
+ xcell.setCellType(type);
}
- XSSFCell xcell = addCell(column, index, type);
- cells.add(xcell);
+ cells.put(columnIndex, xcell);
return xcell;
}
- private XSSFCell retrieveCell(int cellnum) {
- Iterator<Cell> it = cellIterator();
- for ( ; it.hasNext() ; ) {
- Cell cell = it.next();
- if (cell.getColumnIndex() == cellnum) {
- return (XSSFCell)cell;
- }
- }
- return null;
- }
-
/**
* Returns the cell at the given (0 based) index,
* with the {@link MissingCellPolicy} from the parent Workbook.
@@ -176,28 +170,33 @@
public XSSFCell getCell(int cellnum) {
return getCell(cellnum, sheet.getWorkbook().getMissingCellPolicy());
}
-
+
/**
- * Returns the cell at the given (0 based) index,
- * with the specified {@link MissingCellPolicy}
+ * Returns the cell at the given (0 based) index, with the specified {@link MissingCellPolicy}
*
* @return the cell at the given (0 based) index
+ * @throws IllegalArgumentException if cellnum < 0 or the specified MissingCellPolicy is invalid
+ * @see Row#RETURN_NULL_AND_BLANK
+ * @see Row#RETURN_BLANK_AS_NULL
+ * @see Row#CREATE_NULL_AS_BLANK
*/
public XSSFCell getCell(int cellnum, MissingCellPolicy policy) {
- XSSFCell cell = retrieveCell(cellnum);
+ if(cellnum < 0) throw new IllegalArgumentException("Cell index must be >= 0");
+
+ XSSFCell cell = (XSSFCell)cells.get(cellnum);
if(policy == RETURN_NULL_AND_BLANK) {
return cell;
}
if(policy == RETURN_BLANK_AS_NULL) {
if(cell == null) return cell;
- if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
+ if(cell.getCellType() == Cell.CELL_TYPE_BLANK) {
return null;
}
return cell;
}
if(policy == CREATE_NULL_AS_BLANK) {
if(cell == null) {
- return createCell((short)cellnum, HSSFCell.CELL_TYPE_BLANK);
+ return createCell((short)cellnum, Cell.CELL_TYPE_BLANK);
}
return cell;
}
@@ -207,41 +206,11 @@
/**
* Get the number of the first cell contained in this row.
*
- * @return short representing the first logical cell in the row, or -1 if the row does not contain any cells.
+ * @return short representing the first logical cell in the row,
+ * or -1 if the row does not contain any cells.
*/
public short getFirstCellNum() {
- for (Iterator<Cell> it = cellIterator() ; it.hasNext() ; ) {
- Cell cell = it.next();
- if (cell != null) {
- return (short)cell.getColumnIndex();
- }
- }
- return -1;
- }
-
- /**
- * Get the row's height measured in twips (1/20th of a point). If the height is not set, the default worksheet value is returned,
- * See {@link org.apache.poi.xssf.usermodel.XSSFSheet#getDefaultRowHeightInPoints()}
- *
- * @return row height measured in twips (1/20th of a point)
- */
- public short getHeight() {
- return (short)(getHeightInPoints()*20);
- }
-
- /**
- * Returns row height measured in point size. If the height is not set, the default worksheet value is returned,
- * See {@link org.apache.poi.xssf.usermodel.XSSFSheet#getDefaultRowHeightInPoints()}
- *
- * @return row height measured in point size
- * @see org.apache.poi.xssf.usermodel.XSSFSheet#getDefaultRowHeightInPoints()
- */
- public float getHeightInPoints() {
- if (this.row.isSetHt()) {
- return (float) this.row.getHt();
- } else {
- return sheet.getDefaultRowHeightInPoints();
- }
+ return (short)(cells.size() == 0 ? -1 : cells.firstKey());
}
/**
@@ -260,70 +229,36 @@
* }
* </pre>
*
- * @return short representing the last logical cell in the row <b>PLUS ONE</b>, or -1 if the
- * row does not contain any cells.
+ * @return short representing the last logical cell in the row <b>PLUS ONE</b>,
+ * or -1 if the row does not contain any cells.
*/
public short getLastCellNum() {
- short lastCellNum = -1;
- for (Iterator<Cell> it = cellIterator() ; it.hasNext() ; ) {
- Cell cell = it.next();
- if (cell != null) {
- lastCellNum = (short)(cell.getColumnIndex() + 1);
- }
- }
- return lastCellNum;
- }
-
- /**
- * Gets the number of defined cells (NOT number of cells in the actual row!).
- * That is to say if only columns 0,4,5 have values then there would be 3.
- *
- * @return int representing the number of defined cells in the row.
- */
- public int getPhysicalNumberOfCells() {
- int count = 0;
- for (Iterator<Cell> it = cellIterator() ; it.hasNext() ; ) {
- if (it.next() != null) {
- count++;
- }
- }
- return count;
+ return (short)(cells.size() == 0 ? -1 : (cells.lastKey() + 1));
}
/**
- * Get row number this row represents
- *
- * @return the row number (0 based)
- */
- public int getRowNum() {
- return (int) (row.getR() - 1);
- }
-
- /**
- * Get whether or not to display this row with 0 height
+ * Get the row's height measured in twips (1/20th of a point). If the height is not set, the default worksheet value is returned,
+ * See {@link org.apache.poi.xssf.usermodel.XSSFSheet#getDefaultRowHeightInPoints()}
*
- * @return - height is zero or not.
+ * @return row height measured in twips (1/20th of a point)
*/
- public boolean getZeroHeight() {
- return this.row.getHidden();
+ public short getHeight() {
+ return (short)(getHeightInPoints()*20);
}
/**
- * Remove the Cell from this row.
+ * Returns row height measured in point size. If the height is not set, the default worksheet value is returned,
+ * See {@link org.apache.poi.xssf.usermodel.XSSFSheet#getDefaultRowHeightInPoints()}
*
- * @param cell to remove
+ * @return row height measured in point size
+ * @see org.apache.poi.xssf.usermodel.XSSFSheet#getDefaultRowHeightInPoints()
*/
- public void removeCell(Cell cell) {
- int counter = 0;
- for (Iterator<Cell> it = cellIterator(); it.hasNext(); ) {
- Cell c = it.next();
- if (c.getColumnIndex() == cell.getColumnIndex()) {
- it.remove();
- row.removeC(counter);
- continue;
- }
- counter++;
- }
+ public float getHeightInPoints() {
+ if (this.row.isSetHt()) {
+ return (float) this.row.getHt();
+ } else {
+ return sheet.getDefaultRowHeightInPoints();
+ }
}
/**
@@ -351,13 +286,43 @@
}
/**
+ * Gets the number of defined cells (NOT number of cells in the actual row!).
+ * That is to say if only columns 0,4,5 have values then there would be 3.
+ *
+ * @return int representing the number of defined cells in the row.
+ */
+ public int getPhysicalNumberOfCells() {
+ return cells.size();
+ }
+
+ /**
+ * Get row number this row represents
+ *
+ * @return the row number (0 based)
+ */
+ public int getRowNum() {
+ return (int) (row.getR() - 1);
+ }
+
+ /**
* Set the row number of this row.
*
* @param rowNum the row number (0-based)
+ * @throws IllegalArgumentException if rowNum < 0
*/
public void setRowNum(int rowNum) {
+ if(rowNum < 0) throw new IllegalArgumentException("Row number must be >= 0");
+
this.row.setR(rowNum + 1);
+ }
+ /**
+ * Get whether or not to display this row with 0 height
+ *
+ * @return - height is zero or not.
+ */
+ public boolean getZeroHeight() {
+ return this.row.getHidden();
}
/**
@@ -369,14 +334,48 @@
this.row.setHidden(height);
}
-
+
+ /**
+ * Remove the Cell from this row.
+ *
+ * @param cell the cell to remove
+ */
+ public void removeCell(Cell cell) {
+ cells.remove(cell.getColumnIndex());
+ }
+
/**
- * Returns the underlying CTRow xml bean representing this row
+ * Returns the underlying CTRow xml bean containing all cell definitions in this row
*
- * @return the underlying CTRow bean
+ * @return the underlying CTRow xml bean
*/
public CTRow getCTRow(){
- return this.row;
+ return row;
}
+ /**
+ * Fired when the document is written to an output stream.
+ * <p>
+ * Attaches CTCell beans to the underlying CTRow bean
+ * </p>
+ * @see org.apache.poi.xssf.usermodel.XSSFSheet#commit()
+ */
+ protected void onDocumentWrite(){
+ ArrayList<CTCell> cArray = new ArrayList<CTCell>(cells.size());
+ //create array of CTCell objects.
+ //TreeMap's value iterator ensures that the cells are ordered by columnIndex in the ascending order
+ for (Cell cell : cells.values()) {
+ XSSFCell c = (XSSFCell)cell;
+ cArray.add(c.getCTCell());
+ }
+ row.setCArray(cArray.toArray(new CTCell[cArray.size()]));
+ }
+
+ /**
+ * @return formatted xml representation of this row
+ */
+ @Override
+ public String toString(){
+ return row.toString();
+ }
}
Modified: poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java?rev=707445&r1=707444&r2=707445&view=diff
==============================================================================
--- poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (original)
+++ poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java Thu Oct 23 11:57:28 2008
@@ -71,7 +71,7 @@
protected CTSheet sheet;
protected CTWorksheet worksheet;
- protected List<Row> rows;
+ protected TreeMap<Integer, Row> rows;
protected List<XSSFHyperlink> hyperlinks;
protected ColumnHelper columnHelper;
private CommentsSource sheetComments;
@@ -156,9 +156,10 @@
}
protected void initRows(CTWorksheet worksheet) {
- this.rows = new LinkedList<Row>();
+ this.rows = new TreeMap<Integer, Row>();
for (CTRow row : worksheet.getSheetData().getRowArray()) {
- this.rows.add(new XSSFRow(row, this));
+ XSSFRow r = new XSSFRow(row, this);
+ this.rows.put(r.getRowNum(), r);
}
}
@@ -307,13 +308,6 @@
return (XSSFComment)sheetComments.addComment();
}
- protected XSSFRow addRow(int index, int rownum) {
- CTRow row = this.worksheet.getSheetData().insertNewRow(index);
- XSSFRow xrow = new XSSFRow(row, this);
- xrow.setRowNum(rownum);
- return xrow;
- }
-
/**
* Create a new row within the sheet and return the high level representation
*
@@ -322,24 +316,11 @@
* @see #removeRow(org.apache.poi.ss.usermodel.Row)
*/
public XSSFRow createRow(int rownum) {
- int index = 0;
- for (Row r : this.rows) {
- if (r.getRowNum() == rownum) {
- // Replace r with new row
- XSSFRow xrow = addRow(index, rownum);
- rows.set(index, xrow);
- return xrow;
- }
- if (r.getRowNum() > rownum) {
- XSSFRow xrow = addRow(index, rownum);
- rows.add(index, xrow);
- return xrow;
- }
- ++index;
- }
- XSSFRow xrow = addRow(index, rownum);
- rows.add(xrow);
- return xrow;
+ CTRow ctRow = CTRow.Factory.newInstance();
+ XSSFRow r = new XSSFRow(ctRow, this);
+ r.setRowNum(rownum);
+ rows.put(r.getRowNum(), r);
+ return r;
}
/**
@@ -737,14 +718,7 @@
* @return <code>XSSFRow</code> representing the rownumber or <code>null</code> if its not defined on the sheet
*/
public XSSFRow getRow(int rownum) {
- //TODO current implemenation is expensive, it should take O(1), not O(N)
- for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {
- Row row = it.next();
- if (row.getRowNum() == rownum) {
- return (XSSFRow)row;
- }
- }
- return null;
+ return (XSSFRow)rows.get(rownum);
}
/**
@@ -926,7 +900,7 @@
private short getMaxOutlineLevelRows(){
short outlineLevel=0;
- for(Row r:rows){
+ for(Row r : rows.values()){
XSSFRow xrow=(XSSFRow)r;
outlineLevel=xrow.getCTRow().getOutlineLevel()>outlineLevel? xrow.getCTRow().getOutlineLevel(): outlineLevel;
}
@@ -1067,16 +1041,8 @@
}
public void removeRow(Row row) {
- int counter = 0;
- int rowNum=row.getRowNum();
- for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {
- Row r = it.next();
- if (r.getRowNum() == rowNum) {
- it.remove();
- worksheet.getSheetData().removeRow(counter);
- }
- counter++;
- }
+
+ rows.remove(row.getRowNum());
}
/**
@@ -1093,7 +1059,7 @@
}
public Iterator<Row> rowIterator() {
- return rows.iterator();
+ return rows.values().iterator();
}
/**
@@ -1444,6 +1410,10 @@
row.setRowNum(row.getRowNum() + n);
}
}
+ //rebuild the rows map
+ TreeMap<Integer, Row> map = new TreeMap<Integer, Row>();
+ for(Row r : this) map.put(r.getRowNum(), r);
+ rows = map;
}
/**
@@ -1680,6 +1650,15 @@
worksheet.getHyperlinks().setHyperlinkArray(ctHls);
}
+ CTSheetData sheetData = worksheet.getSheetData();
+ ArrayList<CTRow> rArray = new ArrayList<CTRow>(rows.size());
+ for(Row row : rows.values()){
+ XSSFRow r = (XSSFRow)row;
+ r.onDocumentWrite();
+ rArray.add(r.getCTRow());
+ }
+ sheetData.setRowArray(rArray.toArray(new CTRow[rArray.size()]));
+
XmlOptions xmlOptions = new XmlOptions(DEFAULT_XML_OPTIONS);
xmlOptions.setSaveSyntheticDocumentElement(new QName(CTWorksheet.type.getName().getNamespaceURI(), "worksheet"));
Modified: poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java?rev=707445&r1=707444&r2=707445&view=diff
==============================================================================
--- poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java (original)
+++ poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java Thu Oct 23 11:57:28 2008
@@ -25,13 +25,7 @@
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.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.ss.usermodel.*;
import org.apache.poi.xssf.XSSFTestDataSamples;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
@@ -51,18 +45,39 @@
public void testSetGetBoolean() throws Exception {
XSSFRow row = createParentObjects();
XSSFCell cell = row.createCell(0);
+ //for blank cells getBooleanCellValue returns false
+ assertFalse(cell.getBooleanCellValue());
+
cell.setCellValue(true);
assertEquals(Cell.CELL_TYPE_BOOLEAN, cell.getCellType());
assertTrue(cell.getBooleanCellValue());
cell.setCellValue(false);
assertFalse(cell.getBooleanCellValue());
+
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
try {
cell.getBooleanCellValue();
fail("Exception expected");
- } catch (NumberFormatException e) {
+ } catch (IllegalStateException e) {
// success
+ assertEquals("Cannot get a boolean value from a numeric cell", e.getMessage());
}
+
+ cell.setCellValue("1");
+ assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType());
+ try {
+ cell.getBooleanCellValue();
+ fail("Exception expected");
+ } catch (IllegalStateException e) {
+ // success
+ assertEquals("Cannot get a boolean value from a text cell", e.getMessage());
+ }
+
+ //reverted to a blank cell
+ cell.setCellType(Cell.CELL_TYPE_BLANK);
+ assertFalse(cell.getBooleanCellValue());
+
+
}
/**
@@ -71,15 +86,54 @@
public void testSetGetNumeric() throws Exception {
XSSFRow row = createParentObjects();
XSSFCell cell = row.createCell(0);
- cell.setCellValue(10d);
+ assertEquals(0.0, cell.getNumericCellValue());
+
+ cell.setCellValue(10.0);
assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType());
- assertEquals(10d, cell.getNumericCellValue());
+ assertEquals(10.0, cell.getNumericCellValue());
cell.setCellValue(-23.76);
assertEquals(-23.76, cell.getNumericCellValue());
- }
+
+ cell.setCellValue("string");
+ try {
+ cell.getNumericCellValue();
+ fail("Exception expected");
+ } catch (IllegalStateException e) {
+ // success
+ assertEquals("Cannot get a numeric value from a text cell", e.getMessage());
+ }
+
+ cell.setCellValue(true);
+ try {
+ cell.getNumericCellValue();
+ fail("Exception expected");
+ } catch (IllegalStateException e) {
+ // success
+ assertEquals("Cannot get a numeric value from a boolean cell", e.getMessage());
+ }
+
+ //reverted to a blank cell
+ cell.setCellType(Cell.CELL_TYPE_BLANK);
+ assertEquals(0.0, cell.getNumericCellValue());
+
+ //setting numeric value for a formula cell does not change the cell type
+ XSSFCell fcell = row.createCell(1);
+ fcell.setCellFormula("SUM(C4:E4)");
+ assertEquals(Cell.CELL_TYPE_FORMULA, fcell.getCellType());
+ fcell.setCellValue(36.6);
+ assertEquals(Cell.CELL_TYPE_FORMULA, fcell.getCellType());
+ assertEquals(36.6, fcell.getNumericCellValue());
+
+ //the said above is true for error cells
+ fcell.setCellType(Cell.CELL_TYPE_ERROR);
+ assertEquals(36.6, fcell.getNumericCellValue());
+ fcell.setCellValue(16.6);
+ assertEquals(Cell.CELL_TYPE_FORMULA, fcell.getCellType());
+ assertEquals(16.6, fcell.getNumericCellValue());
+ }
/**
- * Test setting and getting numeric values.
+ * Test setting and getting date values.
*/
public void testSetGetDate() throws Exception {
XSSFRow row = createParentObjects();
@@ -100,8 +154,9 @@
try {
cell.getDateCellValue();
fail("Exception expected");
- } catch (NumberFormatException e) {
+ } catch (IllegalStateException e) {
// success
+ assertEquals("Cannot get a numeric value from a boolean cell", e.getMessage());
}
cell.setCellValue(cal);
@@ -109,6 +164,34 @@
}
+ /**
+ * Test setting and getting date values.
+ */
+ public void testSetGetType() throws Exception {
+ XSSFRow row = createParentObjects();
+ XSSFCell cell = row.createCell(0);
+ cell.setCellType(Cell.CELL_TYPE_BLANK);
+ assertEquals(Cell.CELL_TYPE_BLANK, cell.getCellType());
+ cell.setCellType(Cell.CELL_TYPE_STRING);
+ assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType());
+ cell.setCellType(Cell.CELL_TYPE_FORMULA);
+ assertEquals(Cell.CELL_TYPE_FORMULA, cell.getCellType());
+ cell.setCellFormula(null);
+
+ //number cell w/o value is treated as a Blank cell
+ cell.setCellType(Cell.CELL_TYPE_NUMERIC);
+ assertFalse(cell.getCTCell().isSetV());
+ assertEquals(Cell.CELL_TYPE_BLANK, cell.getCellType());
+
+ //normal number cells have set values
+ cell.setCellType(Cell.CELL_TYPE_NUMERIC);
+ cell.getCTCell().setV("0");
+ assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType());
+
+ cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
+ assertEquals(Cell.CELL_TYPE_BOOLEAN, cell.getCellType());
+ }
+
public void testSetGetError() throws Exception {
XSSFRow row = createParentObjects();
XSSFCell cell = row.createCell(0);
@@ -116,14 +199,15 @@
cell.setCellErrorValue((byte)0);
assertEquals(Cell.CELL_TYPE_ERROR, cell.getCellType());
assertEquals((byte)0, cell.getErrorCellValue());
-
+
+ //YK setting numeric value of a error cell does not change the cell type
cell.setCellValue(2.2);
- assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType());
+ assertEquals(Cell.CELL_TYPE_ERROR, cell.getCellType());
- cell.setCellErrorValue(Cell.ERROR_NAME);
+ cell.setCellErrorValue(FormulaError.NAME);
assertEquals(Cell.CELL_TYPE_ERROR, cell.getCellType());
- assertEquals(Cell.ERROR_NAME.getType(), cell.getErrorCellValue());
- assertEquals(Cell.ERROR_NAME.getStringRepr(), cell.getErrorCellString());
+ assertEquals(FormulaError.NAME.getCode(), cell.getErrorCellValue());
+ assertEquals(FormulaError.NAME.getString(), cell.getErrorCellString());
}
public void testSetGetFormula() throws Exception {
@@ -135,7 +219,13 @@
assertEquals(Cell.CELL_TYPE_FORMULA, cell.getCellType());
assertEquals(formula, cell.getCellFormula());
- assertTrue( Double.isNaN( cell.getNumericCellValue() ));
+ assertEquals(0.0, cell.getNumericCellValue());
+
+ cell.setCellValue(44.5); //set precalculated value
+ assertEquals(Cell.CELL_TYPE_FORMULA, cell.getCellType());
+ assertEquals(44.5, cell.getNumericCellValue());
+
+ cell.setCellValue(""); //set precalculated value
}
public void testSetGetStringInline() throws Exception {
@@ -166,14 +256,27 @@
public void testSetGetStringShared() {
XSSFRow row = createParentObjects();
XSSFCell cell = row.createCell(0);
+ //we return empty string for blank cells
+ assertEquals("", cell.getStringCellValue());
- cell.setCellValue(new XSSFRichTextString(""));
+ cell.setCellValue(new XSSFRichTextString("test"));
assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType());
- assertEquals("", cell.getRichStringCellValue().getString());
+ assertEquals("test", cell.getRichStringCellValue().getString());
cell.setCellValue(new XSSFRichTextString("Foo"));
assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType());
assertEquals("Foo", cell.getRichStringCellValue().getString());
+
+ cell.setCellValue((String)null);
+ assertEquals(Cell.CELL_TYPE_BLANK, cell.getCellType());
+
+ XSSFCell fcell = row.createCell(1);
+ fcell.setCellFormula("SUM(C4:E4)");
+ assertEquals(Cell.CELL_TYPE_FORMULA, fcell.getCellType());
+ fcell.setCellValue("36.6");
+ assertEquals(Cell.CELL_TYPE_FORMULA, fcell.getCellType());
+ assertEquals("36.6", fcell.getStringCellValue());
+
}
/**
@@ -185,7 +288,7 @@
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
assertFalse(cell.getBooleanCellValue());
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
- assertTrue(Double.isNaN( cell.getNumericCellValue() ));
+ assertEquals(0.0, cell.getNumericCellValue() );
assertNull(cell.getDateCellValue());
cell.setCellType(Cell.CELL_TYPE_ERROR);
assertEquals(0, cell.getErrorCellValue());
@@ -246,17 +349,15 @@
XSSFComment comment = sheet.createComment();
comment.setAuthor(TEST_C10_AUTHOR);
- CTWorksheet ctWorksheet = sheet.getWorksheet();
-
// Create C10 cell
- Row row = sheet.createRow(9);
- Cell cell = row.createCell(2);
+ XSSFRow row = sheet.createRow(9);
+ XSSFCell cell = row.createCell(2);
row.createCell(3);
// Set a comment for C10 cell
cell.setCellComment(comment);
- CTCell ctCell = ctWorksheet.getSheetData().getRowArray(0).getCArray(0);
+ CTCell ctCell = cell.getCTCell();
assertNotNull(ctCell);
assertEquals("C10", ctCell.getR());
assertEquals(TEST_C10_AUTHOR, comment.getAuthor());
@@ -407,7 +508,7 @@
assertEquals(hcell.toString(),xcell.toString());
//ERROR
- xcell.setCellErrorValue(Cell.ERROR_VALUE);
+ xcell.setCellErrorValue(FormulaError.VALUE);
xcell.setCellType(Cell.CELL_TYPE_ERROR);
hcell.setCellErrorValue((byte)0);
Modified: poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java?rev=707445&r1=707444&r2=707445&view=diff
==============================================================================
--- poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java (original)
+++ poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java Thu Oct 23 11:57:28 2008
@@ -42,12 +42,12 @@
Cell c1 = r.createCell(0);
c1.setCellFormula("1+5");
- assertTrue( Double.isNaN(c1.getNumericCellValue()) );
+ assertEquals(0.0, c1.getNumericCellValue() );
Cell c2 = r.createCell(1);
c2.setCellFormula("10/2");
- assertTrue( Double.isNaN(c2.getNumericCellValue()) );
-
+ assertEquals(0.0, c2.getNumericCellValue() );
+
FormulaEvaluator fe = new XSSFFormulaEvaluator(wb);
fe.evaluateFormulaCell(c1);
@@ -70,19 +70,19 @@
Cell c1 = r.createCell(0);
c1.setCellFormula("SUM(A1:B1)");
- assertTrue( Double.isNaN(c1.getNumericCellValue()) );
-
+ assertEquals(0.0, c1.getNumericCellValue() );
+
Cell c2 = r.createCell(1);
c2.setCellFormula("SUM(A1:E1)");
- assertTrue( Double.isNaN(c2.getNumericCellValue()) );
-
+ assertEquals(0.0, c2.getNumericCellValue() );
+
Cell c3 = r.createCell(2);
c3.setCellFormula("COUNT(A1:A1)");
- assertTrue( Double.isNaN(c3.getNumericCellValue()) );
+ assertEquals(0.0, c3.getNumericCellValue() );
Cell c4 = r.createCell(3);
c4.setCellFormula("COUNTA(A1:E1)");
- assertTrue( Double.isNaN(c4.getNumericCellValue()) );
+ assertEquals(0.0, c4.getNumericCellValue() );
// Evaluate and test
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org