diff options
9 files changed, 1081 insertions, 622 deletions
diff --git a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java index 15e7f1b822..b848cd4530 100644 --- a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java +++ b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java @@ -70,26 +70,6 @@ public interface Cell { 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 @@ public interface Cell { 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 @@ public interface Cell { /** * 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 @@ public interface Cell { 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 @@ public interface Cell { * * @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); } diff --git a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/FormulaError.java b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/FormulaError.java new file mode 100755 index 0000000000..d765e0f01a --- /dev/null +++ b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/FormulaError.java @@ -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;
+ }
+}
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java index 6388258698..87ba105e6c 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java @@ -23,18 +23,8 @@ import java.util.Calendar; 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.CTCellFormula; 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 @@ public final class XSSFCell implements Cell { 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 @@ public final class XSSFCell implements Cell { 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); + } + + /** + * 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 = 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. + * <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 void setCellValue(Calendar value) { + boolean date1904 = getSheet().getWorkbook().isDate1904(); + setCellValue( DateUtil.getExcelDate(value, date1904 )); } /** * 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() { - 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; + int cellType = getCellType(); + if(cellType != CELL_TYPE_ERROR) throw typeMismatch(CELL_TYPE_ERROR, cellType, false); + + return cell.getV(); } /** - * Returns the error type, in the same way that - * HSSFCell does. See {@link Cell} for details + * 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() { - 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; - } + String code = getErrorCellString(); + if(code == null) return 0; - 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. - - // 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 (byte)FormulaError.forString(code).getCode(); } - 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"); - } - /** - * 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); - } - - 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() ); + /** + * 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()); } + /** + * 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 @@ public final class XSSFCell implements Cell { 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 final class XSSFCell implements Cell { */ 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 @@ public final class XSSFCell implements Cell { 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 @@ public final class XSSFCell implements Cell { /** * 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; + } } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java index 68f5784256..c477caa1cd 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java @@ -866,6 +866,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * @param fmt the index of a data format */ public void setDataFormat(short fmt) { + cellXf.setApplyNumberFormat(true); cellXf.setNumFmtId((long)fmt); } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java index d797a7dfe5..610c1f3c6d 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java @@ -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 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow; /** * 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 class XSSFRow implements Row, Comparable { 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 @@ public class XSSFRow implements Row, Comparable { * 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 class XSSFRow implements Row, Comparable { 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 @@ public class XSSFRow implements Row, Comparable { /** * 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 @@ public class XSSFRow implements Row, Comparable { * } * </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; + return (short)(cells.size() == 0 ? -1 : (cells.lastKey() + 1)); } /** - * 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; - } - - /** - * 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 @@ public class XSSFRow implements Row, Comparable { } /** + * 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 @@ public class XSSFRow implements Row, Comparable { 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(); + } } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java index 6795adef80..8ee5cd6f5c 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java @@ -71,7 +71,7 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { 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 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { } 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 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { 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 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { * @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 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { * @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 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { 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 class XSSFSheet extends POIXMLDocumentPart implements Sheet { } 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 class XSSFSheet extends POIXMLDocumentPart implements Sheet { } public Iterator<Row> rowIterator() { - return rows.iterator(); + return rows.values().iterator(); } /** @@ -1444,6 +1410,10 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { 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 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { 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")); diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java index 877cf34ec2..87454dba47 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java @@ -25,13 +25,7 @@ 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.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 final class TestXSSFCell extends TestCase { 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 final class TestXSSFCell extends TestCase { 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 @@ public final class TestXSSFCell extends TestCase { 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 @@ public final class TestXSSFCell extends TestCase { } + /** + * 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 @@ public final class TestXSSFCell extends TestCase { 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 @@ public final class TestXSSFCell extends TestCase { 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 final class TestXSSFCell extends TestCase { 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 @@ public final class TestXSSFCell extends TestCase { 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 @@ public final class TestXSSFCell extends TestCase { 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 @@ public final class TestXSSFCell extends TestCase { 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); diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java index 160f5609bc..668e83a0e2 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java @@ -42,12 +42,12 @@ public final class TestXSSFFormulaEvaluation extends TestCase { 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 @@ public final class TestXSSFFormulaEvaluation extends TestCase { 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 diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java index 4aa3c8d9c9..1e1ce24a69 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java @@ -793,9 +793,7 @@ public class TestXSSFSheet extends TestCase { //one level sheet.groupRow(9,10); assertEquals(2,sheet.rows.size()); - CTRow[]rowArray=sheet.getWorksheet().getSheetData().getRowArray(); - assertEquals(2,rowArray.length); - CTRow ctrow=rowArray[0]; + CTRow ctrow = sheet.getRow(8).getCTRow(); assertNotNull(ctrow); assertEquals(9,ctrow.getR()); @@ -804,10 +802,8 @@ public class TestXSSFSheet extends TestCase { //two level sheet.groupRow(10,13); - rowArray=sheet.getWorksheet().getSheetData().getRowArray(); - assertEquals(5,rowArray.length); assertEquals(5,sheet.rows.size()); - ctrow=rowArray[1]; + ctrow = sheet.getRow(9).getCTRow(); assertNotNull(ctrow); assertEquals(10,ctrow.getR()); assertEquals(2, ctrow.getOutlineLevel()); @@ -815,14 +811,11 @@ public class TestXSSFSheet extends TestCase { sheet.ungroupRow(8, 10); - rowArray=sheet.getWorksheet().getSheetData().getRowArray(); - assertEquals(4,rowArray.length); + assertEquals(4,sheet.rows.size()); assertEquals(1,sheet.getSheetTypeSheetFormatPr().getOutlineLevelRow()); sheet.ungroupRow(10,10); - rowArray=sheet.getWorksheet().getSheetData().getRowArray(); - assertEquals(3,rowArray.length); - assertEquals(3,sheet.rows.size()); + assertEquals(3,sheet.rows.size()); assertEquals(1,sheet.getSheetTypeSheetFormatPr().getOutlineLevelRow()); } |