From e97e241ac2f0e0d5a1d8c4d0fcfa2521da73818b Mon Sep 17 00:00:00 2001 From: Javen O'Neal Date: Fri, 10 Jun 2016 02:51:45 +0000 Subject: [PATCH] whitespace (tabs to spaces) git-svn-id: https://svn.apache.org/repos/asf/poi/branches/xssf_structured_references@1747625 13f79535-47bb-0310-9956-ffa450edef68 --- .../usermodel/HSSFEvaluationWorkbook.java | 2 +- .../ss/formula/FormulaParsingWorkbook.java | 76 +- .../formula/OperationEvaluationContext.java | 446 +++---- .../poi/ss/formula/WorkbookEvaluator.java | 1068 ++++++++--------- .../poi/ss/formula/functions/Indirect.java | 362 +++--- .../apache/poi/xssf/usermodel/XSSFCell.java | 8 +- .../poi/xssf/usermodel/XSSFWorkbook.java | 180 +-- .../hssf/record/TestSharedFormulaRecord.java | 332 ++--- 8 files changed, 1237 insertions(+), 1237 deletions(-) diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java index 037d55d9f0..81eef90050 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java @@ -6,7 +6,7 @@ (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 + 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, diff --git a/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java b/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java index 0fe93eec7f..7ddcc944c5 100644 --- a/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java +++ b/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java @@ -32,51 +32,51 @@ import org.apache.poi.ss.util.CellReference; * @author Josh Micich */ public interface FormulaParsingWorkbook { - /** - * named range name matching is case insensitive - */ - EvaluationName getName(String name, int sheetIndex); - - /** - * Return the underlying workbook - */ - Name createName(); + /** + * named range name matching is case insensitive + */ + EvaluationName getName(String name, int sheetIndex); + + /** + * Return the underlying workbook + */ + Name createName(); - /** - * XSSF Only - gets a table that exists in the worksheet - */ - Table getTable(String name); - - /** - * Return an external name (named range, function, user-defined function) Ptg - */ - Ptg getNameXPtg(String name, SheetIdentifier sheet); - - /** - * Produce the appropriate Ptg for a 3d cell reference - */ - Ptg get3DReferencePtg(CellReference cell, SheetIdentifier sheet); + /** + * XSSF Only - gets a table that exists in the worksheet + */ + Table getTable(String name); + + /** + * Return an external name (named range, function, user-defined function) Ptg + */ + Ptg getNameXPtg(String name, SheetIdentifier sheet); + + /** + * Produce the appropriate Ptg for a 3d cell reference + */ + Ptg get3DReferencePtg(CellReference cell, SheetIdentifier sheet); /** * Produce the appropriate Ptg for a 3d area reference */ Ptg get3DReferencePtg(AreaReference area, SheetIdentifier sheet); - /** - * gets the externSheet index for a sheet from this workbook - */ - int getExternalSheetIndex(String sheetName); - /** - * gets the externSheet index for a sheet from an external workbook - * @param workbookName e.g. "Budget.xls" - * @param sheetName a name of a sheet in that workbook - */ - int getExternalSheetIndex(String workbookName, String sheetName); + /** + * gets the externSheet index for a sheet from this workbook + */ + int getExternalSheetIndex(String sheetName); + /** + * gets the externSheet index for a sheet from an external workbook + * @param workbookName e.g. "Budget.xls" + * @param sheetName a name of a sheet in that workbook + */ + int getExternalSheetIndex(String workbookName, String sheetName); - /** - * Returns an enum holding spreadhseet properties specific to an Excel version ( - * max column and row numbers, max arguments to a function, etc.) - */ - SpreadsheetVersion getSpreadsheetVersion(); + /** + * Returns an enum holding spreadhseet properties specific to an Excel version ( + * max column and row numbers, max arguments to a function, etc.) + */ + SpreadsheetVersion getSpreadsheetVersion(); } diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java index 277fbfc159..d7345f7cbe 100644 --- a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java +++ b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java @@ -46,199 +46,199 @@ import org.apache.poi.ss.util.CellReference.NameType; * For POI internal use only */ public final class OperationEvaluationContext { - public static final FreeRefFunction UDF = UserDefinedFunction.instance; - private final EvaluationWorkbook _workbook; - private final int _sheetIndex; - private final int _rowIndex; - private final int _columnIndex; - private final EvaluationTracker _tracker; - private final WorkbookEvaluator _bookEvaluator; + public static final FreeRefFunction UDF = UserDefinedFunction.instance; + private final EvaluationWorkbook _workbook; + private final int _sheetIndex; + private final int _rowIndex; + private final int _columnIndex; + private final EvaluationTracker _tracker; + private final WorkbookEvaluator _bookEvaluator; - public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, EvaluationWorkbook workbook, int sheetIndex, int srcRowNum, - int srcColNum, EvaluationTracker tracker) { - _bookEvaluator = bookEvaluator; - _workbook = workbook; - _sheetIndex = sheetIndex; - _rowIndex = srcRowNum; - _columnIndex = srcColNum; - _tracker = tracker; - } + public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, EvaluationWorkbook workbook, int sheetIndex, int srcRowNum, + int srcColNum, EvaluationTracker tracker) { + _bookEvaluator = bookEvaluator; + _workbook = workbook; + _sheetIndex = sheetIndex; + _rowIndex = srcRowNum; + _columnIndex = srcColNum; + _tracker = tracker; + } - public EvaluationWorkbook getWorkbook() { - return _workbook; - } + public EvaluationWorkbook getWorkbook() { + return _workbook; + } - public int getRowIndex() { - return _rowIndex; - } + public int getRowIndex() { + return _rowIndex; + } - public int getColumnIndex() { - return _columnIndex; - } + public int getColumnIndex() { + return _columnIndex; + } - SheetRangeEvaluator createExternSheetRefEvaluator(ExternSheetReferenceToken ptg) { - return createExternSheetRefEvaluator(ptg.getExternSheetIndex()); - } - SheetRangeEvaluator createExternSheetRefEvaluator(String firstSheetName, String lastSheetName, int externalWorkbookNumber) { + SheetRangeEvaluator createExternSheetRefEvaluator(ExternSheetReferenceToken ptg) { + return createExternSheetRefEvaluator(ptg.getExternSheetIndex()); + } + SheetRangeEvaluator createExternSheetRefEvaluator(String firstSheetName, String lastSheetName, int externalWorkbookNumber) { ExternalSheet externalSheet = _workbook.getExternalSheet(firstSheetName, lastSheetName, externalWorkbookNumber); return createExternSheetRefEvaluator(externalSheet); } - SheetRangeEvaluator createExternSheetRefEvaluator(int externSheetIndex) { - ExternalSheet externalSheet = _workbook.getExternalSheet(externSheetIndex); + SheetRangeEvaluator createExternSheetRefEvaluator(int externSheetIndex) { + ExternalSheet externalSheet = _workbook.getExternalSheet(externSheetIndex); return createExternSheetRefEvaluator(externalSheet); - } - SheetRangeEvaluator createExternSheetRefEvaluator(ExternalSheet externalSheet) { - WorkbookEvaluator targetEvaluator; - int otherFirstSheetIndex; - int otherLastSheetIndex = -1; - if (externalSheet == null || externalSheet.getWorkbookName() == null) { - // sheet is in same workbook - targetEvaluator = _bookEvaluator; - if(externalSheet == null) { - otherFirstSheetIndex = 0; - } else { - otherFirstSheetIndex = _workbook.getSheetIndex(externalSheet.getSheetName()); - } - - if (externalSheet instanceof ExternalSheetRange) { - String lastSheetName = ((ExternalSheetRange)externalSheet).getLastSheetName(); - otherLastSheetIndex = _workbook.getSheetIndex(lastSheetName); - } - } else { - // look up sheet by name from external workbook - String workbookName = externalSheet.getWorkbookName(); - try { - targetEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName); - } catch (WorkbookNotFoundException e) { - throw new RuntimeException(e.getMessage(), e); - } - - otherFirstSheetIndex = targetEvaluator.getSheetIndex(externalSheet.getSheetName()); + } + SheetRangeEvaluator createExternSheetRefEvaluator(ExternalSheet externalSheet) { + WorkbookEvaluator targetEvaluator; + int otherFirstSheetIndex; + int otherLastSheetIndex = -1; + if (externalSheet == null || externalSheet.getWorkbookName() == null) { + // sheet is in same workbook + targetEvaluator = _bookEvaluator; + if(externalSheet == null) { + otherFirstSheetIndex = 0; + } else { + otherFirstSheetIndex = _workbook.getSheetIndex(externalSheet.getSheetName()); + } + + if (externalSheet instanceof ExternalSheetRange) { + String lastSheetName = ((ExternalSheetRange)externalSheet).getLastSheetName(); + otherLastSheetIndex = _workbook.getSheetIndex(lastSheetName); + } + } else { + // look up sheet by name from external workbook + String workbookName = externalSheet.getWorkbookName(); + try { + targetEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName); + } catch (WorkbookNotFoundException e) { + throw new RuntimeException(e.getMessage(), e); + } + + otherFirstSheetIndex = targetEvaluator.getSheetIndex(externalSheet.getSheetName()); if (externalSheet instanceof ExternalSheetRange) { String lastSheetName = ((ExternalSheetRange)externalSheet).getLastSheetName(); otherLastSheetIndex = targetEvaluator.getSheetIndex(lastSheetName); } - - if (otherFirstSheetIndex < 0) { - throw new RuntimeException("Invalid sheet name '" + externalSheet.getSheetName() - + "' in bool '" + workbookName + "'."); - } - } - - if (otherLastSheetIndex == -1) { - // Reference to just one sheet - otherLastSheetIndex = otherFirstSheetIndex; - } - - SheetRefEvaluator[] evals = new SheetRefEvaluator[otherLastSheetIndex-otherFirstSheetIndex+1]; - for (int i=0; inull if either workbook or sheet is not found - */ - private SheetRefEvaluator createExternSheetRefEvaluator(String workbookName, String sheetName) { - WorkbookEvaluator targetEvaluator; - if (workbookName == null) { - targetEvaluator = _bookEvaluator; - } else { - if (sheetName == null) { - throw new IllegalArgumentException("sheetName must not be null if workbookName is provided"); - } - try { - targetEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName); - } catch (WorkbookNotFoundException e) { - return null; - } - } - int otherSheetIndex = sheetName == null ? _sheetIndex : targetEvaluator.getSheetIndex(sheetName); - if (otherSheetIndex < 0) { - return null; - } - return new SheetRefEvaluator(targetEvaluator, _tracker, otherSheetIndex); - } + /** + * @return null if either workbook or sheet is not found + */ + private SheetRefEvaluator createExternSheetRefEvaluator(String workbookName, String sheetName) { + WorkbookEvaluator targetEvaluator; + if (workbookName == null) { + targetEvaluator = _bookEvaluator; + } else { + if (sheetName == null) { + throw new IllegalArgumentException("sheetName must not be null if workbookName is provided"); + } + try { + targetEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName); + } catch (WorkbookNotFoundException e) { + return null; + } + } + int otherSheetIndex = sheetName == null ? _sheetIndex : targetEvaluator.getSheetIndex(sheetName); + if (otherSheetIndex < 0) { + return null; + } + return new SheetRefEvaluator(targetEvaluator, _tracker, otherSheetIndex); + } - public SheetRangeEvaluator getRefEvaluatorForCurrentSheet() { - SheetRefEvaluator sre = new SheetRefEvaluator(_bookEvaluator, _tracker, _sheetIndex); - return new SheetRangeEvaluator(_sheetIndex, sre); - } + public SheetRangeEvaluator getRefEvaluatorForCurrentSheet() { + SheetRefEvaluator sre = new SheetRefEvaluator(_bookEvaluator, _tracker, _sheetIndex); + return new SheetRangeEvaluator(_sheetIndex, sre); + } - /** - * Resolves a cell or area reference dynamically. - * @param workbookName the name of the workbook containing the reference. If null - * the current workbook is assumed. Note - to evaluate formulas which use multiple workbooks, - * a {@link CollaboratingWorkbooksEnvironment} must be set up. - * @param sheetName the name of the sheet containing the reference. May be null - * (when workbookName is also null) in which case the current workbook and sheet is - * assumed. - * @param refStrPart1 the single cell reference or first part of the area reference. Must not - * be null. - * @param refStrPart2 the second part of the area reference. For single cell references this - * parameter must be null - * @param isA1Style specifies the format for refStrPart1 and refStrPart2. - * Pass true for 'A1' style and false for 'R1C1' style. - * TODO - currently POI only supports 'A1' reference style - * @return a {@link RefEval} or {@link AreaEval} - */ - public ValueEval getDynamicReference(String workbookName, String sheetName, String refStrPart1, - String refStrPart2, boolean isA1Style) { - if (!isA1Style) { - throw new RuntimeException("R1C1 style not supported yet"); - } - SheetRefEvaluator se = createExternSheetRefEvaluator(workbookName, sheetName); - if (se == null) { - return ErrorEval.REF_INVALID; - } - SheetRangeEvaluator sre = new SheetRangeEvaluator(_sheetIndex, se); - - // ugly typecast - TODO - make spreadsheet version more easily accessible - SpreadsheetVersion ssVersion = ((FormulaParsingWorkbook)_workbook).getSpreadsheetVersion(); + /** + * Resolves a cell or area reference dynamically. + * @param workbookName the name of the workbook containing the reference. If null + * the current workbook is assumed. Note - to evaluate formulas which use multiple workbooks, + * a {@link CollaboratingWorkbooksEnvironment} must be set up. + * @param sheetName the name of the sheet containing the reference. May be null + * (when workbookName is also null) in which case the current workbook and sheet is + * assumed. + * @param refStrPart1 the single cell reference or first part of the area reference. Must not + * be null. + * @param refStrPart2 the second part of the area reference. For single cell references this + * parameter must be null + * @param isA1Style specifies the format for refStrPart1 and refStrPart2. + * Pass true for 'A1' style and false for 'R1C1' style. + * TODO - currently POI only supports 'A1' reference style + * @return a {@link RefEval} or {@link AreaEval} + */ + public ValueEval getDynamicReference(String workbookName, String sheetName, String refStrPart1, + String refStrPart2, boolean isA1Style) { + if (!isA1Style) { + throw new RuntimeException("R1C1 style not supported yet"); + } + SheetRefEvaluator se = createExternSheetRefEvaluator(workbookName, sheetName); + if (se == null) { + return ErrorEval.REF_INVALID; + } + SheetRangeEvaluator sre = new SheetRangeEvaluator(_sheetIndex, se); + + // ugly typecast - TODO - make spreadsheet version more easily accessible + SpreadsheetVersion ssVersion = ((FormulaParsingWorkbook)_workbook).getSpreadsheetVersion(); - NameType part1refType = classifyCellReference(refStrPart1, ssVersion); - switch (part1refType) { - case BAD_CELL_OR_NAMED_RANGE: - return ErrorEval.REF_INVALID; - case NAMED_RANGE: + NameType part1refType = classifyCellReference(refStrPart1, ssVersion); + switch (part1refType) { + case BAD_CELL_OR_NAMED_RANGE: + return ErrorEval.REF_INVALID; + case NAMED_RANGE: EvaluationName nm = ((FormulaParsingWorkbook)_workbook).getName(refStrPart1, _sheetIndex); if(!nm.isRange()){ throw new RuntimeException("Specified name '" + refStrPart1 + "' is not a range as expected."); } return _bookEvaluator.evaluateNameFormula(nm.getNameDefinition(), this); - } - if (refStrPart2 == null) { - // no ':' - switch (part1refType) { - case COLUMN: - case ROW: - return ErrorEval.REF_INVALID; - case CELL: - CellReference cr = new CellReference(refStrPart1); - return new LazyRefEval(cr.getRow(), cr.getCol(), sre); - } - throw new IllegalStateException("Unexpected reference classification of '" + refStrPart1 + "'."); - } - NameType part2refType = classifyCellReference(refStrPart1, ssVersion); - switch (part2refType) { - case BAD_CELL_OR_NAMED_RANGE: - return ErrorEval.REF_INVALID; - case NAMED_RANGE: - throw new RuntimeException("Cannot evaluate '" + refStrPart1 - + "'. Indirect evaluation of defined names not supported yet"); - } + } + if (refStrPart2 == null) { + // no ':' + switch (part1refType) { + case COLUMN: + case ROW: + return ErrorEval.REF_INVALID; + case CELL: + CellReference cr = new CellReference(refStrPart1); + return new LazyRefEval(cr.getRow(), cr.getCol(), sre); + } + throw new IllegalStateException("Unexpected reference classification of '" + refStrPart1 + "'."); + } + NameType part2refType = classifyCellReference(refStrPart1, ssVersion); + switch (part2refType) { + case BAD_CELL_OR_NAMED_RANGE: + return ErrorEval.REF_INVALID; + case NAMED_RANGE: + throw new RuntimeException("Cannot evaluate '" + refStrPart1 + + "'. Indirect evaluation of defined names not supported yet"); + } - if (part2refType != part1refType) { - // LHS and RHS of ':' must be compatible - return ErrorEval.REF_INVALID; - } - int firstRow, firstCol, lastRow, lastCol; - switch (part1refType) { - case COLUMN: + if (part2refType != part1refType) { + // LHS and RHS of ':' must be compatible + return ErrorEval.REF_INVALID; + } + int firstRow, firstCol, lastRow, lastCol; + switch (part1refType) { + case COLUMN: firstRow =0; if (part2refType.equals(NameType.COLUMN)) { @@ -252,7 +252,7 @@ public final class OperationEvaluationContext { lastCol = parseColRef(refStrPart2); } break; - case ROW: + case ROW: // support of cell range in the form of integer:integer firstCol = 0; if (part2refType.equals(NameType.ROW)) @@ -265,61 +265,61 @@ public final class OperationEvaluationContext { firstRow = parseRowRef(refStrPart1); lastRow = parseRowRef(refStrPart2); } - break; - case CELL: - CellReference cr; - cr = new CellReference(refStrPart1); - firstRow = cr.getRow(); - firstCol = cr.getCol(); - cr = new CellReference(refStrPart2); - lastRow = cr.getRow(); - lastCol = cr.getCol(); - break; - default: - throw new IllegalStateException("Unexpected reference classification of '" + refStrPart1 + "'."); - } - return new LazyAreaEval(firstRow, firstCol, lastRow, lastCol, sre); - } + break; + case CELL: + CellReference cr; + cr = new CellReference(refStrPart1); + firstRow = cr.getRow(); + firstCol = cr.getCol(); + cr = new CellReference(refStrPart2); + lastRow = cr.getRow(); + lastCol = cr.getCol(); + break; + default: + throw new IllegalStateException("Unexpected reference classification of '" + refStrPart1 + "'."); + } + return new LazyAreaEval(firstRow, firstCol, lastRow, lastCol, sre); + } - private static int parseRowRef(String refStrPart) { - return CellReference.convertColStringToIndex(refStrPart); - } + private static int parseRowRef(String refStrPart) { + return CellReference.convertColStringToIndex(refStrPart); + } - private static int parseColRef(String refStrPart) { - return Integer.parseInt(refStrPart) - 1; - } + private static int parseColRef(String refStrPart) { + return Integer.parseInt(refStrPart) - 1; + } - private static NameType classifyCellReference(String str, SpreadsheetVersion ssVersion) { - int len = str.length(); - if (len < 1) { - return CellReference.NameType.BAD_CELL_OR_NAMED_RANGE; - } - return CellReference.classifyCellReference(str, ssVersion); - } + private static NameType classifyCellReference(String str, SpreadsheetVersion ssVersion) { + int len = str.length(); + if (len < 1) { + return CellReference.NameType.BAD_CELL_OR_NAMED_RANGE; + } + return CellReference.classifyCellReference(str, ssVersion); + } - public FreeRefFunction findUserDefinedFunction(String functionName) { - return _bookEvaluator.findUserDefinedFunction(functionName); - } + public FreeRefFunction findUserDefinedFunction(String functionName) { + return _bookEvaluator.findUserDefinedFunction(functionName); + } - public ValueEval getRefEval(int rowIndex, int columnIndex) { - SheetRangeEvaluator sre = getRefEvaluatorForCurrentSheet(); - return new LazyRefEval(rowIndex, columnIndex, sre); - } - public ValueEval getRef3DEval(Ref3DPtg rptg) { - SheetRangeEvaluator sre = createExternSheetRefEvaluator(rptg.getExternSheetIndex()); - return new LazyRefEval(rptg.getRow(), rptg.getColumn(), sre); - } + public ValueEval getRefEval(int rowIndex, int columnIndex) { + SheetRangeEvaluator sre = getRefEvaluatorForCurrentSheet(); + return new LazyRefEval(rowIndex, columnIndex, sre); + } + public ValueEval getRef3DEval(Ref3DPtg rptg) { + SheetRangeEvaluator sre = createExternSheetRefEvaluator(rptg.getExternSheetIndex()); + return new LazyRefEval(rptg.getRow(), rptg.getColumn(), sre); + } public ValueEval getRef3DEval(Ref3DPxg rptg) { SheetRangeEvaluator sre = createExternSheetRefEvaluator( rptg.getSheetName(), rptg.getLastSheetName(), rptg.getExternalWorkbookNumber()); return new LazyRefEval(rptg.getRow(), rptg.getColumn(), sre); } - public ValueEval getAreaEval(int firstRowIndex, int firstColumnIndex, - int lastRowIndex, int lastColumnIndex) { - SheetRangeEvaluator sre = getRefEvaluatorForCurrentSheet(); - return new LazyAreaEval(firstRowIndex, firstColumnIndex, lastRowIndex, lastColumnIndex, sre); - } + public ValueEval getAreaEval(int firstRowIndex, int firstColumnIndex, + int lastRowIndex, int lastColumnIndex) { + SheetRangeEvaluator sre = getRefEvaluatorForCurrentSheet(); + return new LazyAreaEval(firstRowIndex, firstColumnIndex, lastRowIndex, lastColumnIndex, sre); + } public ValueEval getArea3DEval(Area3DPtg aptg) { SheetRangeEvaluator sre = createExternSheetRefEvaluator(aptg.getExternSheetIndex()); return new LazyAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(), @@ -348,8 +348,8 @@ public final class OperationEvaluationContext { ); return getExternalNameXEval(externName, workbookName); } - public ValueEval getNameXEval(NameXPxg nameXPxg) { - ExternalSheet externSheet = _workbook.getExternalSheet(nameXPxg.getSheetName(), null, nameXPxg.getExternalWorkbookNumber()); + public ValueEval getNameXEval(NameXPxg nameXPxg) { + ExternalSheet externSheet = _workbook.getExternalSheet(nameXPxg.getSheetName(), null, nameXPxg.getExternalWorkbookNumber()); if(externSheet == null || externSheet.getWorkbookName() == null) { // External reference to our own workbook's name return getLocalNameXEval(nameXPxg); @@ -363,8 +363,8 @@ public final class OperationEvaluationContext { nameXPxg.getExternalWorkbookNumber() ); return getExternalNameXEval(externName, workbookName); - } - + } + private ValueEval getLocalNameXEval(NameXPxg nameXPxg) { // Look up the sheet, if present int sIdx = -1; @@ -383,7 +383,7 @@ public final class OperationEvaluationContext { return new FunctionNameEval(name); } } - private ValueEval getLocalNameXEval(NameXPtg nameXPtg) { + private ValueEval getLocalNameXEval(NameXPtg nameXPtg) { String name = _workbook.resolveNameXText(nameXPtg); // Try to parse it as a name @@ -406,11 +406,11 @@ public final class OperationEvaluationContext { // Must be an external function return new FunctionNameEval(name); } - } - public int getSheetIndex() { - return _sheetIndex; - } - + } + public int getSheetIndex() { + return _sheetIndex; + } + private ValueEval getExternalNameXEval(ExternalName externName, String workbookName) { try { // Fetch the workbook this refers to, and the name as defined with that diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java index 84099bf2bb..1ef08dd6d5 100644 --- a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java +++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java @@ -93,39 +93,39 @@ import org.apache.poi.util.POILogger; * @author Thies Wellpott (debug output enhancements) */ public final class WorkbookEvaluator { - - private static final POILogger LOG = POILogFactory.getLogger(WorkbookEvaluator.class); + + private static final POILogger LOG = POILogFactory.getLogger(WorkbookEvaluator.class); private final EvaluationWorkbook _workbook; - private EvaluationCache _cache; - /** part of cache entry key (useful when evaluating multiple workbooks) */ - private int _workbookIx; + private EvaluationCache _cache; + /** part of cache entry key (useful when evaluating multiple workbooks) */ + private int _workbookIx; - private final IEvaluationListener _evaluationListener; - private final Map _sheetIndexesBySheet; - private final Map _sheetIndexesByName; - private CollaboratingWorkbooksEnvironment _collaboratingWorkbookEnvironment; - private final IStabilityClassifier _stabilityClassifier; - private final AggregatingUDFFinder _udfFinder; + private final IEvaluationListener _evaluationListener; + private final Map _sheetIndexesBySheet; + private final Map _sheetIndexesByName; + private CollaboratingWorkbooksEnvironment _collaboratingWorkbookEnvironment; + private final IStabilityClassifier _stabilityClassifier; + private final AggregatingUDFFinder _udfFinder; private boolean _ignoreMissingWorkbooks = false; - /** - * @param udfFinder pass null for default (AnalysisToolPak only) - */ - public WorkbookEvaluator(EvaluationWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) { - this (workbook, null, stabilityClassifier, udfFinder); - } - /* package */ WorkbookEvaluator(EvaluationWorkbook workbook, IEvaluationListener evaluationListener, - IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) { - _workbook = workbook; - _evaluationListener = evaluationListener; - _cache = new EvaluationCache(evaluationListener); - _sheetIndexesBySheet = new IdentityHashMap(); - _sheetIndexesByName = new IdentityHashMap(); - _collaboratingWorkbookEnvironment = CollaboratingWorkbooksEnvironment.EMPTY; - _workbookIx = 0; - _stabilityClassifier = stabilityClassifier; + /** + * @param udfFinder pass null for default (AnalysisToolPak only) + */ + public WorkbookEvaluator(EvaluationWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) { + this (workbook, null, stabilityClassifier, udfFinder); + } + /* package */ WorkbookEvaluator(EvaluationWorkbook workbook, IEvaluationListener evaluationListener, + IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) { + _workbook = workbook; + _evaluationListener = evaluationListener; + _cache = new EvaluationCache(evaluationListener); + _sheetIndexesBySheet = new IdentityHashMap(); + _sheetIndexesByName = new IdentityHashMap(); + _collaboratingWorkbookEnvironment = CollaboratingWorkbooksEnvironment.EMPTY; + _workbookIx = 0; + _stabilityClassifier = stabilityClassifier; AggregatingUDFFinder defaultToolkit = // workbook can be null in unit tests workbook == null ? null : (AggregatingUDFFinder)workbook.getUDFFinder(); @@ -133,501 +133,501 @@ public final class WorkbookEvaluator { defaultToolkit.add(udfFinder); } _udfFinder = defaultToolkit; - } - - /** - * also for debug use. Used in toString methods - */ - /* package */ String getSheetName(int sheetIndex) { - return _workbook.getSheetName(sheetIndex); - } - - /* package */ EvaluationSheet getSheet(int sheetIndex) { - return _workbook.getSheet(sheetIndex); - } - - /* package */ EvaluationWorkbook getWorkbook() { - return _workbook; - } - - /* package */ EvaluationName getName(String name, int sheetIndex) { - EvaluationName evalName = _workbook.getName(name, sheetIndex); - return evalName; - } - - private static boolean isDebugLogEnabled() { - return LOG.check(POILogger.DEBUG); - } - private static boolean isInfoLogEnabled() { - return LOG.check(POILogger.INFO); - } - private static void logDebug(String s) { - if (isDebugLogEnabled()) { - LOG.log(POILogger.DEBUG, s); - } - } - private static void logInfo(String s) { - if (isInfoLogEnabled()) { - LOG.log(POILogger.INFO, s); - } - } - /* package */ void attachToEnvironment(CollaboratingWorkbooksEnvironment collaboratingWorkbooksEnvironment, EvaluationCache cache, int workbookIx) { - _collaboratingWorkbookEnvironment = collaboratingWorkbooksEnvironment; - _cache = cache; - _workbookIx = workbookIx; - } - /* package */ CollaboratingWorkbooksEnvironment getEnvironment() { - return _collaboratingWorkbookEnvironment; - } - - /** - * Discards the current workbook environment and attaches to the default 'empty' environment. - * Also resets evaluation cache. - */ - /* package */ void detachFromEnvironment() { - _collaboratingWorkbookEnvironment = CollaboratingWorkbooksEnvironment.EMPTY; - _cache = new EvaluationCache(_evaluationListener); - _workbookIx = 0; - } - /** - * @return the evaluator for another workbook which is part of the same {@link CollaboratingWorkbooksEnvironment} - */ - /* package */ WorkbookEvaluator getOtherWorkbookEvaluator(String workbookName) throws WorkbookNotFoundException { - return _collaboratingWorkbookEnvironment.getWorkbookEvaluator(workbookName); - } - - /* package */ IEvaluationListener getEvaluationListener() { - return _evaluationListener; - } - - /** - * Should be called whenever there are changes to input cells in the evaluated workbook. - * Failure to call this method after changing cell values will cause incorrect behaviour - * of the evaluate~ methods of this class - */ - public void clearAllCachedResultValues() { - _cache.clear(); - _sheetIndexesBySheet.clear(); - } - - /** - * Should be called to tell the cell value cache that the specified (value or formula) cell - * has changed. - */ - public void notifyUpdateCell(EvaluationCell cell) { - int sheetIndex = getSheetIndex(cell.getSheet()); - _cache.notifyUpdateCell(_workbookIx, sheetIndex, cell); - } - /** - * Should be called to tell the cell value cache that the specified cell has just been - * deleted. - */ - public void notifyDeleteCell(EvaluationCell cell) { - int sheetIndex = getSheetIndex(cell.getSheet()); - _cache.notifyDeleteCell(_workbookIx, sheetIndex, cell); - } - - private int getSheetIndex(EvaluationSheet sheet) { - Integer result = _sheetIndexesBySheet.get(sheet); - if (result == null) { - int sheetIndex = _workbook.getSheetIndex(sheet); - if (sheetIndex < 0) { - throw new RuntimeException("Specified sheet from a different book"); - } - result = Integer.valueOf(sheetIndex); - _sheetIndexesBySheet.put(sheet, result); - } - return result.intValue(); - } - - public ValueEval evaluate(EvaluationCell srcCell) { - int sheetIndex = getSheetIndex(srcCell.getSheet()); - return evaluateAny(srcCell, sheetIndex, srcCell.getRowIndex(), srcCell.getColumnIndex(), new EvaluationTracker(_cache)); - } - - /** - * Case-insensitive. - * @return -1 if sheet with specified name does not exist - */ - /* package */ int getSheetIndex(String sheetName) { - Integer result = _sheetIndexesByName.get(sheetName); - if (result == null) { - int sheetIndex = _workbook.getSheetIndex(sheetName); - if (sheetIndex < 0) { - return -1; - } - result = Integer.valueOf(sheetIndex); - _sheetIndexesByName.put(sheetName, result); - } - return result.intValue(); - } - - /* package */ int getSheetIndexByExternIndex(int externSheetIndex) { - return _workbook.convertFromExternSheetIndex(externSheetIndex); - } - - - /** - * @return never null, never {@link BlankEval} - */ - private ValueEval evaluateAny(EvaluationCell srcCell, int sheetIndex, - int rowIndex, int columnIndex, EvaluationTracker tracker) { - - // avoid tracking dependencies to cells that have constant definition - boolean shouldCellDependencyBeRecorded = _stabilityClassifier == null ? true - : !_stabilityClassifier.isCellFinal(sheetIndex, rowIndex, columnIndex); - if (srcCell == null || srcCell.getCellType() != Cell.CELL_TYPE_FORMULA) { - ValueEval result = getValueFromNonFormulaCell(srcCell); - if (shouldCellDependencyBeRecorded) { - tracker.acceptPlainValueDependency(_workbookIx, sheetIndex, rowIndex, columnIndex, result); - } - return result; - } - - FormulaCellCacheEntry cce = _cache.getOrCreateFormulaCellEntry(srcCell); - if (shouldCellDependencyBeRecorded || cce.isInputSensitive()) { - tracker.acceptFormulaDependency(cce); - } - IEvaluationListener evalListener = _evaluationListener; - ValueEval result; - if (cce.getValue() == null) { - if (!tracker.startEvaluate(cce)) { - return ErrorEval.CIRCULAR_REF_ERROR; - } - OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex, columnIndex, tracker); - - try { - - Ptg[] ptgs = _workbook.getFormulaTokens(srcCell); -// System.out.println("====="); -// XSSFCell c = ((XSSFEvaluationCell)srcCell).getXSSFCell(); -// System.out.println("Formula is "+ c); -// System.out.println("The cell is " + c.getSheet().getSheetName()+"!"+c.getReference()); -// System.out.println("Evaluation tokens : "); // TODO Dlivshen remove -// for (Ptg ptg : ptgs) { // TODO Dlivshen remove -// System.out.println(ptg); // TODO Dlivshen remove -// } // TODO Dlivshen remove -// System.out.println("======"); // TODO Dlivshen remove - if (evalListener == null) { - result = evaluateFormula(ec, ptgs); - } else { - evalListener.onStartEvaluate(srcCell, cce); - result = evaluateFormula(ec, ptgs); - evalListener.onEndEvaluate(cce, result); - } - - tracker.updateCacheResult(result); - } - catch (NotImplementedException e) { - throw addExceptionInfo(e, sheetIndex, rowIndex, columnIndex); - } catch (RuntimeException re) { - if (re.getCause() instanceof WorkbookNotFoundException && _ignoreMissingWorkbooks) { - logInfo(re.getCause().getMessage() + " - Continuing with cached value!"); - switch(srcCell.getCachedFormulaResultType()) { - case Cell.CELL_TYPE_NUMERIC: - result = new NumberEval(srcCell.getNumericCellValue()); - break; - case Cell.CELL_TYPE_STRING: - result = new StringEval(srcCell.getStringCellValue()); - break; - case Cell.CELL_TYPE_BLANK: - result = BlankEval.instance; - break; - case Cell.CELL_TYPE_BOOLEAN: - result = BoolEval.valueOf(srcCell.getBooleanCellValue()); - break; - case Cell.CELL_TYPE_ERROR: - result = ErrorEval.valueOf(srcCell.getErrorCellValue()); - break; - case Cell.CELL_TYPE_FORMULA: - default: - throw new RuntimeException("Unexpected cell type '" + srcCell.getCellType()+"' found!"); - } - } else { - throw re; - } - } finally { - tracker.endEvaluate(cce); - } - } else { - if(evalListener != null) { - evalListener.onCacheHit(sheetIndex, rowIndex, columnIndex, cce.getValue()); - } - return cce.getValue(); - } - if (isDebugLogEnabled()) { - String sheetName = getSheetName(sheetIndex); - CellReference cr = new CellReference(rowIndex, columnIndex); - logDebug("Evaluated " + sheetName + "!" + cr.formatAsString() + " to " + result.toString()); - } - // Usually (result === cce.getValue()) - // But sometimes: (result==ErrorEval.CIRCULAR_REF_ERROR, cce.getValue()==null) - // When circular references are detected, the cache entry is only updated for - // the top evaluation frame - return result; - } - - /** - * Adds the current cell reference to the exception for easier debugging. - * Would be nice to get the formula text as well, but that seems to require - * too much digging around and casting to get the FormulaRenderingWorkbook. - */ - private NotImplementedException addExceptionInfo(NotImplementedException inner, int sheetIndex, int rowIndex, int columnIndex) { - - try { - String sheetName = _workbook.getSheetName(sheetIndex); - CellReference cr = new CellReference(sheetName, rowIndex, columnIndex, false, false); - String msg = "Error evaluating cell " + cr.formatAsString(); - return new NotImplementedException(msg, inner); - } catch (Exception e) { - // avoid bombing out during exception handling - LOG.log(POILogger.ERROR, "Can't add exception info", e); - return inner; // preserve original exception - } - } - /** - * Gets the value from a non-formula cell. - * @param cell may be null - * @return {@link BlankEval} if cell is null or blank, never null - */ - /* package */ static ValueEval getValueFromNonFormulaCell(EvaluationCell cell) { - if (cell == null) { - return BlankEval.instance; - } - int cellType = cell.getCellType(); - switch (cellType) { - case Cell.CELL_TYPE_NUMERIC: - return new NumberEval(cell.getNumericCellValue()); - case Cell.CELL_TYPE_STRING: - return new StringEval(cell.getStringCellValue()); - case Cell.CELL_TYPE_BOOLEAN: - return BoolEval.valueOf(cell.getBooleanCellValue()); - case Cell.CELL_TYPE_BLANK: - return BlankEval.instance; - case Cell.CELL_TYPE_ERROR: - return ErrorEval.valueOf(cell.getErrorCellValue()); - } - throw new RuntimeException("Unexpected cell type (" + cellType + ")"); - } + } + + /** + * also for debug use. Used in toString methods + */ + /* package */ String getSheetName(int sheetIndex) { + return _workbook.getSheetName(sheetIndex); + } + + /* package */ EvaluationSheet getSheet(int sheetIndex) { + return _workbook.getSheet(sheetIndex); + } + + /* package */ EvaluationWorkbook getWorkbook() { + return _workbook; + } + + /* package */ EvaluationName getName(String name, int sheetIndex) { + EvaluationName evalName = _workbook.getName(name, sheetIndex); + return evalName; + } + + private static boolean isDebugLogEnabled() { + return LOG.check(POILogger.DEBUG); + } + private static boolean isInfoLogEnabled() { + return LOG.check(POILogger.INFO); + } + private static void logDebug(String s) { + if (isDebugLogEnabled()) { + LOG.log(POILogger.DEBUG, s); + } + } + private static void logInfo(String s) { + if (isInfoLogEnabled()) { + LOG.log(POILogger.INFO, s); + } + } + /* package */ void attachToEnvironment(CollaboratingWorkbooksEnvironment collaboratingWorkbooksEnvironment, EvaluationCache cache, int workbookIx) { + _collaboratingWorkbookEnvironment = collaboratingWorkbooksEnvironment; + _cache = cache; + _workbookIx = workbookIx; + } + /* package */ CollaboratingWorkbooksEnvironment getEnvironment() { + return _collaboratingWorkbookEnvironment; + } + + /** + * Discards the current workbook environment and attaches to the default 'empty' environment. + * Also resets evaluation cache. + */ + /* package */ void detachFromEnvironment() { + _collaboratingWorkbookEnvironment = CollaboratingWorkbooksEnvironment.EMPTY; + _cache = new EvaluationCache(_evaluationListener); + _workbookIx = 0; + } + /** + * @return the evaluator for another workbook which is part of the same {@link CollaboratingWorkbooksEnvironment} + */ + /* package */ WorkbookEvaluator getOtherWorkbookEvaluator(String workbookName) throws WorkbookNotFoundException { + return _collaboratingWorkbookEnvironment.getWorkbookEvaluator(workbookName); + } + + /* package */ IEvaluationListener getEvaluationListener() { + return _evaluationListener; + } + + /** + * Should be called whenever there are changes to input cells in the evaluated workbook. + * Failure to call this method after changing cell values will cause incorrect behaviour + * of the evaluate~ methods of this class + */ + public void clearAllCachedResultValues() { + _cache.clear(); + _sheetIndexesBySheet.clear(); + } + + /** + * Should be called to tell the cell value cache that the specified (value or formula) cell + * has changed. + */ + public void notifyUpdateCell(EvaluationCell cell) { + int sheetIndex = getSheetIndex(cell.getSheet()); + _cache.notifyUpdateCell(_workbookIx, sheetIndex, cell); + } + /** + * Should be called to tell the cell value cache that the specified cell has just been + * deleted. + */ + public void notifyDeleteCell(EvaluationCell cell) { + int sheetIndex = getSheetIndex(cell.getSheet()); + _cache.notifyDeleteCell(_workbookIx, sheetIndex, cell); + } + + private int getSheetIndex(EvaluationSheet sheet) { + Integer result = _sheetIndexesBySheet.get(sheet); + if (result == null) { + int sheetIndex = _workbook.getSheetIndex(sheet); + if (sheetIndex < 0) { + throw new RuntimeException("Specified sheet from a different book"); + } + result = Integer.valueOf(sheetIndex); + _sheetIndexesBySheet.put(sheet, result); + } + return result.intValue(); + } + + public ValueEval evaluate(EvaluationCell srcCell) { + int sheetIndex = getSheetIndex(srcCell.getSheet()); + return evaluateAny(srcCell, sheetIndex, srcCell.getRowIndex(), srcCell.getColumnIndex(), new EvaluationTracker(_cache)); + } + + /** + * Case-insensitive. + * @return -1 if sheet with specified name does not exist + */ + /* package */ int getSheetIndex(String sheetName) { + Integer result = _sheetIndexesByName.get(sheetName); + if (result == null) { + int sheetIndex = _workbook.getSheetIndex(sheetName); + if (sheetIndex < 0) { + return -1; + } + result = Integer.valueOf(sheetIndex); + _sheetIndexesByName.put(sheetName, result); + } + return result.intValue(); + } + + /* package */ int getSheetIndexByExternIndex(int externSheetIndex) { + return _workbook.convertFromExternSheetIndex(externSheetIndex); + } + + + /** + * @return never null, never {@link BlankEval} + */ + private ValueEval evaluateAny(EvaluationCell srcCell, int sheetIndex, + int rowIndex, int columnIndex, EvaluationTracker tracker) { + + // avoid tracking dependencies to cells that have constant definition + boolean shouldCellDependencyBeRecorded = _stabilityClassifier == null ? true + : !_stabilityClassifier.isCellFinal(sheetIndex, rowIndex, columnIndex); + if (srcCell == null || srcCell.getCellType() != Cell.CELL_TYPE_FORMULA) { + ValueEval result = getValueFromNonFormulaCell(srcCell); + if (shouldCellDependencyBeRecorded) { + tracker.acceptPlainValueDependency(_workbookIx, sheetIndex, rowIndex, columnIndex, result); + } + return result; + } + + FormulaCellCacheEntry cce = _cache.getOrCreateFormulaCellEntry(srcCell); + if (shouldCellDependencyBeRecorded || cce.isInputSensitive()) { + tracker.acceptFormulaDependency(cce); + } + IEvaluationListener evalListener = _evaluationListener; + ValueEval result; + if (cce.getValue() == null) { + if (!tracker.startEvaluate(cce)) { + return ErrorEval.CIRCULAR_REF_ERROR; + } + OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex, columnIndex, tracker); + + try { + + Ptg[] ptgs = _workbook.getFormulaTokens(srcCell); +// System.out.println("====="); +// XSSFCell c = ((XSSFEvaluationCell)srcCell).getXSSFCell(); +// System.out.println("Formula is "+ c); +// System.out.println("The cell is " + c.getSheet().getSheetName()+"!"+c.getReference()); +// System.out.println("Evaluation tokens : "); // TODO Dlivshen remove +// for (Ptg ptg : ptgs) { // TODO Dlivshen remove +// System.out.println(ptg); // TODO Dlivshen remove +// } // TODO Dlivshen remove +// System.out.println("======"); // TODO Dlivshen remove + if (evalListener == null) { + result = evaluateFormula(ec, ptgs); + } else { + evalListener.onStartEvaluate(srcCell, cce); + result = evaluateFormula(ec, ptgs); + evalListener.onEndEvaluate(cce, result); + } + + tracker.updateCacheResult(result); + } + catch (NotImplementedException e) { + throw addExceptionInfo(e, sheetIndex, rowIndex, columnIndex); + } catch (RuntimeException re) { + if (re.getCause() instanceof WorkbookNotFoundException && _ignoreMissingWorkbooks) { + logInfo(re.getCause().getMessage() + " - Continuing with cached value!"); + switch(srcCell.getCachedFormulaResultType()) { + case Cell.CELL_TYPE_NUMERIC: + result = new NumberEval(srcCell.getNumericCellValue()); + break; + case Cell.CELL_TYPE_STRING: + result = new StringEval(srcCell.getStringCellValue()); + break; + case Cell.CELL_TYPE_BLANK: + result = BlankEval.instance; + break; + case Cell.CELL_TYPE_BOOLEAN: + result = BoolEval.valueOf(srcCell.getBooleanCellValue()); + break; + case Cell.CELL_TYPE_ERROR: + result = ErrorEval.valueOf(srcCell.getErrorCellValue()); + break; + case Cell.CELL_TYPE_FORMULA: + default: + throw new RuntimeException("Unexpected cell type '" + srcCell.getCellType()+"' found!"); + } + } else { + throw re; + } + } finally { + tracker.endEvaluate(cce); + } + } else { + if(evalListener != null) { + evalListener.onCacheHit(sheetIndex, rowIndex, columnIndex, cce.getValue()); + } + return cce.getValue(); + } + if (isDebugLogEnabled()) { + String sheetName = getSheetName(sheetIndex); + CellReference cr = new CellReference(rowIndex, columnIndex); + logDebug("Evaluated " + sheetName + "!" + cr.formatAsString() + " to " + result.toString()); + } + // Usually (result === cce.getValue()) + // But sometimes: (result==ErrorEval.CIRCULAR_REF_ERROR, cce.getValue()==null) + // When circular references are detected, the cache entry is only updated for + // the top evaluation frame + return result; + } + + /** + * Adds the current cell reference to the exception for easier debugging. + * Would be nice to get the formula text as well, but that seems to require + * too much digging around and casting to get the FormulaRenderingWorkbook. + */ + private NotImplementedException addExceptionInfo(NotImplementedException inner, int sheetIndex, int rowIndex, int columnIndex) { + + try { + String sheetName = _workbook.getSheetName(sheetIndex); + CellReference cr = new CellReference(sheetName, rowIndex, columnIndex, false, false); + String msg = "Error evaluating cell " + cr.formatAsString(); + return new NotImplementedException(msg, inner); + } catch (Exception e) { + // avoid bombing out during exception handling + LOG.log(POILogger.ERROR, "Can't add exception info", e); + return inner; // preserve original exception + } + } + /** + * Gets the value from a non-formula cell. + * @param cell may be null + * @return {@link BlankEval} if cell is null or blank, never null + */ + /* package */ static ValueEval getValueFromNonFormulaCell(EvaluationCell cell) { + if (cell == null) { + return BlankEval.instance; + } + int cellType = cell.getCellType(); + switch (cellType) { + case Cell.CELL_TYPE_NUMERIC: + return new NumberEval(cell.getNumericCellValue()); + case Cell.CELL_TYPE_STRING: + return new StringEval(cell.getStringCellValue()); + case Cell.CELL_TYPE_BOOLEAN: + return BoolEval.valueOf(cell.getBooleanCellValue()); + case Cell.CELL_TYPE_BLANK: + return BlankEval.instance; + case Cell.CELL_TYPE_ERROR: + return ErrorEval.valueOf(cell.getErrorCellValue()); + } + throw new RuntimeException("Unexpected cell type (" + cellType + ")"); + } /** * whether print detailed messages about the next formula evaluation */ - private boolean dbgEvaluationOutputForNextEval = false; - - // special logger for formula evaluation output (because of possibly very large output) - private final POILogger EVAL_LOG = POILogFactory.getLogger("POI.FormulaEval"); - // current indent level for evalution; negative value for no output - private int dbgEvaluationOutputIndent = -1; - - // visibility raised for testing - /* package */ ValueEval evaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs) { - - String dbgIndentStr = ""; // always init. to non-null just for defensive avoiding NPE - if (dbgEvaluationOutputForNextEval) { - // first evaluation call when ouput is desired, so iit. this evaluator instance - dbgEvaluationOutputIndent = 1; - dbgEvaluationOutputForNextEval = false; - } - if (dbgEvaluationOutputIndent > 0) { - // init. indent string to needed spaces (create as substring vom very long space-only string; - // limit indendation for deep recursions) - dbgIndentStr = " "; - dbgIndentStr = dbgIndentStr.substring(0, Math.min(dbgIndentStr.length(), dbgEvaluationOutputIndent*2)); - EVAL_LOG.log(POILogger.WARN, dbgIndentStr - + "- evaluateFormula('" + ec.getRefEvaluatorForCurrentSheet().getSheetNameRange() - + "'/" + new CellReference(ec.getRowIndex(), ec.getColumnIndex()).formatAsString() - + "): " + Arrays.toString(ptgs).replaceAll("\\Qorg.apache.poi.ss.formula.ptg.\\E", "")); - dbgEvaluationOutputIndent++; - } - - Stack stack = new Stack(); - for (int i = 0, iSize = ptgs.length; i < iSize; i++) { - - // since we don't know how to handle these yet :( - Ptg ptg = ptgs[i]; - if (dbgEvaluationOutputIndent > 0) { - EVAL_LOG.log(POILogger.INFO, dbgIndentStr + " * ptg " + i + ": " + ptg); - } - if (ptg instanceof AttrPtg) { - AttrPtg attrPtg = (AttrPtg) ptg; - if (attrPtg.isSum()) { - // Excel prefers to encode 'SUM()' as a tAttr token, but this evaluator - // expects the equivalent function token - ptg = FuncVarPtg.SUM; - } - if (attrPtg.isOptimizedChoose()) { - ValueEval arg0 = stack.pop(); - int[] jumpTable = attrPtg.getJumpTable(); - int dist; - int nChoices = jumpTable.length; - try { - int switchIndex = Choose.evaluateFirstArg(arg0, ec.getRowIndex(), ec.getColumnIndex()); - if (switchIndex<1 || switchIndex > nChoices) { - stack.push(ErrorEval.VALUE_INVALID); - dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE) - } else { - dist = jumpTable[switchIndex-1]; - } - } catch (EvaluationException e) { - stack.push(e.getErrorEval()); - dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE) - } - // Encoded dist for tAttrChoose includes size of jump table, but - // countTokensToBeSkipped() does not (it counts whole tokens). - dist -= nChoices*2+2; // subtract jump table size - i+= countTokensToBeSkipped(ptgs, i, dist); - continue; - } - if (attrPtg.isOptimizedIf()) { - ValueEval arg0 = stack.pop(); - boolean evaluatedPredicate; - try { - evaluatedPredicate = IfFunc.evaluateFirstArg(arg0, ec.getRowIndex(), ec.getColumnIndex()); - } catch (EvaluationException e) { - stack.push(e.getErrorEval()); - int dist = attrPtg.getData(); - i+= countTokensToBeSkipped(ptgs, i, dist); - attrPtg = (AttrPtg) ptgs[i]; - dist = attrPtg.getData()+1; - i+= countTokensToBeSkipped(ptgs, i, dist); - continue; - } - if (evaluatedPredicate) { - // nothing to skip - true param follows - } else { - int dist = attrPtg.getData(); - i+= countTokensToBeSkipped(ptgs, i, dist); - Ptg nextPtg = ptgs[i+1]; - if (ptgs[i] instanceof AttrPtg && nextPtg instanceof FuncVarPtg && - // in order to verify that there is no third param, we need to check - // if we really have the IF next or some other FuncVarPtg as third param, e.g. ROW()/COLUMN()! - ((FuncVarPtg)nextPtg).getFunctionIndex() == FunctionMetadataRegistry.FUNCTION_INDEX_IF) { - // this is an if statement without a false param (as opposed to MissingArgPtg as the false param) - i++; - stack.push(BoolEval.FALSE); - } - } - continue; - } - if (attrPtg.isSkip()) { - int dist = attrPtg.getData()+1; - i+= countTokensToBeSkipped(ptgs, i, dist); - if (stack.peek() == MissingArgEval.instance) { - stack.pop(); - stack.push(BlankEval.instance); - } - continue; - } - } - if (ptg instanceof ControlPtg) { - // skip Parentheses, Attr, etc - continue; - } - if (ptg instanceof MemFuncPtg || ptg instanceof MemAreaPtg) { - // can ignore, rest of tokens for this expression are in OK RPN order - continue; - } - if (ptg instanceof MemErrPtg) { - continue; - } - - ValueEval opResult; - if (ptg instanceof OperationPtg) { - OperationPtg optg = (OperationPtg) ptg; - - if (optg instanceof UnionPtg) { continue; } - - - int numops = optg.getNumberOfOperands(); - ValueEval[] ops = new ValueEval[numops]; - - // storing the ops in reverse order since they are popping - for (int j = numops - 1; j >= 0; j--) { - ValueEval p = stack.pop(); - ops[j] = p; - } -// logDebug("invoke " + operation + " (nAgs=" + numops + ")"); - opResult = OperationEvaluatorFactory.evaluate(optg, ops, ec); - } else { - opResult = getEvalForPtg(ptg, ec); - } - if (opResult == null) { - throw new RuntimeException("Evaluation result must not be null"); - } -// logDebug("push " + opResult); - stack.push(opResult); - if (dbgEvaluationOutputIndent > 0) { - EVAL_LOG.log(POILogger.INFO, dbgIndentStr + " = " + opResult); - } - } - - ValueEval value = stack.pop(); - if (!stack.isEmpty()) { - throw new IllegalStateException("evaluation stack not empty"); - } - ValueEval result = dereferenceResult(value, ec.getRowIndex(), ec.getColumnIndex()); - if (dbgEvaluationOutputIndent > 0) { - EVAL_LOG.log(POILogger.INFO, dbgIndentStr + "finshed eval of " - + new CellReference(ec.getRowIndex(), ec.getColumnIndex()).formatAsString() - + ": " + result); - dbgEvaluationOutputIndent--; - if (dbgEvaluationOutputIndent == 1) { - // this evaluation is done, reset indent to stop logging - dbgEvaluationOutputIndent = -1; - } - } // if - return result; - - } - - /** - * Calculates the number of tokens that the evaluator should skip upon reaching a tAttrSkip. - * - * @return the number of tokens (starting from startIndex+1) that need to be skipped - * to achieve the specified distInBytes skip distance. - */ - private static int countTokensToBeSkipped(Ptg[] ptgs, int startIndex, int distInBytes) { - int remBytes = distInBytes; - int index = startIndex; - while (remBytes != 0) { - index++; - remBytes -= ptgs[index].getSize(); - if (remBytes < 0) { - throw new RuntimeException("Bad skip distance (wrong token size calculation)."); - } - if (index >= ptgs.length) { - throw new RuntimeException("Skip distance too far (ran out of formula tokens)."); - } - } - return index-startIndex; - } - - /** - * Dereferences a single value from any AreaEval or RefEval evaluation - * result. If the supplied evaluationResult is just a plain value, it is - * returned as-is. - * - * @return a {@link NumberEval}, {@link StringEval}, {@link BoolEval}, or - * {@link ErrorEval}. Never null. {@link BlankEval} is - * converted to {@link NumberEval#ZERO} - */ - public static ValueEval dereferenceResult(ValueEval evaluationResult, int srcRowNum, int srcColNum) { - ValueEval value; - try { - value = OperandResolver.getSingleValue(evaluationResult, srcRowNum, srcColNum); - } catch (EvaluationException e) { - return e.getErrorEval(); - } - if (value == BlankEval.instance) { - // Note Excel behaviour here. A blank final final value is converted to zero. - return NumberEval.ZERO; - // Formulas _never_ evaluate to blank. If a formula appears to have evaluated to - // blank, the actual value is empty string. This can be verified with ISBLANK(). - } - return value; - } + private boolean dbgEvaluationOutputForNextEval = false; + + // special logger for formula evaluation output (because of possibly very large output) + private final POILogger EVAL_LOG = POILogFactory.getLogger("POI.FormulaEval"); + // current indent level for evalution; negative value for no output + private int dbgEvaluationOutputIndent = -1; + + // visibility raised for testing + /* package */ ValueEval evaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs) { + + String dbgIndentStr = ""; // always init. to non-null just for defensive avoiding NPE + if (dbgEvaluationOutputForNextEval) { + // first evaluation call when ouput is desired, so iit. this evaluator instance + dbgEvaluationOutputIndent = 1; + dbgEvaluationOutputForNextEval = false; + } + if (dbgEvaluationOutputIndent > 0) { + // init. indent string to needed spaces (create as substring vom very long space-only string; + // limit indendation for deep recursions) + dbgIndentStr = " "; + dbgIndentStr = dbgIndentStr.substring(0, Math.min(dbgIndentStr.length(), dbgEvaluationOutputIndent*2)); + EVAL_LOG.log(POILogger.WARN, dbgIndentStr + + "- evaluateFormula('" + ec.getRefEvaluatorForCurrentSheet().getSheetNameRange() + + "'/" + new CellReference(ec.getRowIndex(), ec.getColumnIndex()).formatAsString() + + "): " + Arrays.toString(ptgs).replaceAll("\\Qorg.apache.poi.ss.formula.ptg.\\E", "")); + dbgEvaluationOutputIndent++; + } + + Stack stack = new Stack(); + for (int i = 0, iSize = ptgs.length; i < iSize; i++) { + + // since we don't know how to handle these yet :( + Ptg ptg = ptgs[i]; + if (dbgEvaluationOutputIndent > 0) { + EVAL_LOG.log(POILogger.INFO, dbgIndentStr + " * ptg " + i + ": " + ptg); + } + if (ptg instanceof AttrPtg) { + AttrPtg attrPtg = (AttrPtg) ptg; + if (attrPtg.isSum()) { + // Excel prefers to encode 'SUM()' as a tAttr token, but this evaluator + // expects the equivalent function token + ptg = FuncVarPtg.SUM; + } + if (attrPtg.isOptimizedChoose()) { + ValueEval arg0 = stack.pop(); + int[] jumpTable = attrPtg.getJumpTable(); + int dist; + int nChoices = jumpTable.length; + try { + int switchIndex = Choose.evaluateFirstArg(arg0, ec.getRowIndex(), ec.getColumnIndex()); + if (switchIndex<1 || switchIndex > nChoices) { + stack.push(ErrorEval.VALUE_INVALID); + dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE) + } else { + dist = jumpTable[switchIndex-1]; + } + } catch (EvaluationException e) { + stack.push(e.getErrorEval()); + dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE) + } + // Encoded dist for tAttrChoose includes size of jump table, but + // countTokensToBeSkipped() does not (it counts whole tokens). + dist -= nChoices*2+2; // subtract jump table size + i+= countTokensToBeSkipped(ptgs, i, dist); + continue; + } + if (attrPtg.isOptimizedIf()) { + ValueEval arg0 = stack.pop(); + boolean evaluatedPredicate; + try { + evaluatedPredicate = IfFunc.evaluateFirstArg(arg0, ec.getRowIndex(), ec.getColumnIndex()); + } catch (EvaluationException e) { + stack.push(e.getErrorEval()); + int dist = attrPtg.getData(); + i+= countTokensToBeSkipped(ptgs, i, dist); + attrPtg = (AttrPtg) ptgs[i]; + dist = attrPtg.getData()+1; + i+= countTokensToBeSkipped(ptgs, i, dist); + continue; + } + if (evaluatedPredicate) { + // nothing to skip - true param follows + } else { + int dist = attrPtg.getData(); + i+= countTokensToBeSkipped(ptgs, i, dist); + Ptg nextPtg = ptgs[i+1]; + if (ptgs[i] instanceof AttrPtg && nextPtg instanceof FuncVarPtg && + // in order to verify that there is no third param, we need to check + // if we really have the IF next or some other FuncVarPtg as third param, e.g. ROW()/COLUMN()! + ((FuncVarPtg)nextPtg).getFunctionIndex() == FunctionMetadataRegistry.FUNCTION_INDEX_IF) { + // this is an if statement without a false param (as opposed to MissingArgPtg as the false param) + i++; + stack.push(BoolEval.FALSE); + } + } + continue; + } + if (attrPtg.isSkip()) { + int dist = attrPtg.getData()+1; + i+= countTokensToBeSkipped(ptgs, i, dist); + if (stack.peek() == MissingArgEval.instance) { + stack.pop(); + stack.push(BlankEval.instance); + } + continue; + } + } + if (ptg instanceof ControlPtg) { + // skip Parentheses, Attr, etc + continue; + } + if (ptg instanceof MemFuncPtg || ptg instanceof MemAreaPtg) { + // can ignore, rest of tokens for this expression are in OK RPN order + continue; + } + if (ptg instanceof MemErrPtg) { + continue; + } + + ValueEval opResult; + if (ptg instanceof OperationPtg) { + OperationPtg optg = (OperationPtg) ptg; + + if (optg instanceof UnionPtg) { continue; } + + + int numops = optg.getNumberOfOperands(); + ValueEval[] ops = new ValueEval[numops]; + + // storing the ops in reverse order since they are popping + for (int j = numops - 1; j >= 0; j--) { + ValueEval p = stack.pop(); + ops[j] = p; + } +// logDebug("invoke " + operation + " (nAgs=" + numops + ")"); + opResult = OperationEvaluatorFactory.evaluate(optg, ops, ec); + } else { + opResult = getEvalForPtg(ptg, ec); + } + if (opResult == null) { + throw new RuntimeException("Evaluation result must not be null"); + } +// logDebug("push " + opResult); + stack.push(opResult); + if (dbgEvaluationOutputIndent > 0) { + EVAL_LOG.log(POILogger.INFO, dbgIndentStr + " = " + opResult); + } + } + + ValueEval value = stack.pop(); + if (!stack.isEmpty()) { + throw new IllegalStateException("evaluation stack not empty"); + } + ValueEval result = dereferenceResult(value, ec.getRowIndex(), ec.getColumnIndex()); + if (dbgEvaluationOutputIndent > 0) { + EVAL_LOG.log(POILogger.INFO, dbgIndentStr + "finshed eval of " + + new CellReference(ec.getRowIndex(), ec.getColumnIndex()).formatAsString() + + ": " + result); + dbgEvaluationOutputIndent--; + if (dbgEvaluationOutputIndent == 1) { + // this evaluation is done, reset indent to stop logging + dbgEvaluationOutputIndent = -1; + } + } // if + return result; + + } + + /** + * Calculates the number of tokens that the evaluator should skip upon reaching a tAttrSkip. + * + * @return the number of tokens (starting from startIndex+1) that need to be skipped + * to achieve the specified distInBytes skip distance. + */ + private static int countTokensToBeSkipped(Ptg[] ptgs, int startIndex, int distInBytes) { + int remBytes = distInBytes; + int index = startIndex; + while (remBytes != 0) { + index++; + remBytes -= ptgs[index].getSize(); + if (remBytes < 0) { + throw new RuntimeException("Bad skip distance (wrong token size calculation)."); + } + if (index >= ptgs.length) { + throw new RuntimeException("Skip distance too far (ran out of formula tokens)."); + } + } + return index-startIndex; + } + + /** + * Dereferences a single value from any AreaEval or RefEval evaluation + * result. If the supplied evaluationResult is just a plain value, it is + * returned as-is. + * + * @return a {@link NumberEval}, {@link StringEval}, {@link BoolEval}, or + * {@link ErrorEval}. Never null. {@link BlankEval} is + * converted to {@link NumberEval#ZERO} + */ + public static ValueEval dereferenceResult(ValueEval evaluationResult, int srcRowNum, int srcColNum) { + ValueEval value; + try { + value = OperandResolver.getSingleValue(evaluationResult, srcRowNum, srcColNum); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + if (value == BlankEval.instance) { + // Note Excel behaviour here. A blank final final value is converted to zero. + return NumberEval.ZERO; + // Formulas _never_ evaluate to blank. If a formula appears to have evaluated to + // blank, the actual value is empty string. This can be verified with ISBLANK(). + } + return value; + } /** @@ -719,7 +719,7 @@ public final class WorkbookEvaluator { } return eval; } - + private ValueEval getEvalForNameRecord(EvaluationName nameRecord, OperationEvaluationContext ec) { if (nameRecord.isFunctionName()) { return new FunctionNameEval(nameRecord.getNameText()); @@ -734,25 +734,25 @@ public final class WorkbookEvaluator { /** * YK: Used by OperationEvaluationContext to resolve indirect names. */ - /*package*/ ValueEval evaluateNameFormula(Ptg[] ptgs, OperationEvaluationContext ec) { + /*package*/ ValueEval evaluateNameFormula(Ptg[] ptgs, OperationEvaluationContext ec) { if (ptgs.length == 1) { return getEvalForPtg(ptgs[0], ec); } - return evaluateFormula(ec, ptgs); - } - - /** - * Used by the lazy ref evals whenever they need to get the value of a contained cell. - */ - /* package */ ValueEval evaluateReference(EvaluationSheet sheet, int sheetIndex, int rowIndex, - int columnIndex, EvaluationTracker tracker) { - - EvaluationCell cell = sheet.getCell(rowIndex, columnIndex); - return evaluateAny(cell, sheetIndex, rowIndex, columnIndex, tracker); - } - public FreeRefFunction findUserDefinedFunction(String functionName) { - return _udfFinder.findFunction(functionName); - } + return evaluateFormula(ec, ptgs); + } + + /** + * Used by the lazy ref evals whenever they need to get the value of a contained cell. + */ + /* package */ ValueEval evaluateReference(EvaluationSheet sheet, int sheetIndex, int rowIndex, + int columnIndex, EvaluationTracker tracker) { + + EvaluationCell cell = sheet.getCell(rowIndex, columnIndex); + return evaluateAny(cell, sheetIndex, rowIndex, columnIndex, tracker); + } + public FreeRefFunction findUserDefinedFunction(String functionName) { + return _udfFinder.findFunction(functionName); + } /** * Whether to ignore missing references to external workbooks and diff --git a/src/java/org/apache/poi/ss/formula/functions/Indirect.java b/src/java/org/apache/poi/ss/formula/functions/Indirect.java index 7366c798cc..a5be765b68 100644 --- a/src/java/org/apache/poi/ss/formula/functions/Indirect.java +++ b/src/java/org/apache/poi/ss/formula/functions/Indirect.java @@ -49,78 +49,78 @@ public final class Indirect implements FreeRefFunction { public static final FreeRefFunction instance = new Indirect(); - private Indirect() { - // enforce singleton - } + private Indirect() { + // enforce singleton + } - public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { - if (args.length < 1) { - return ErrorEval.VALUE_INVALID; - } + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + if (args.length < 1) { + return ErrorEval.VALUE_INVALID; + } - boolean isA1style; - String text; - try { - ValueEval ve = OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec - .getColumnIndex()); - text = OperandResolver.coerceValueToString(ve); - switch (args.length) { - case 1: - isA1style = true; - break; - case 2: - isA1style = evaluateBooleanArg(args[1], ec); - break; - default: - return ErrorEval.VALUE_INVALID; - } - } catch (EvaluationException e) { - return e.getErrorEval(); - } + boolean isA1style; + String text; + try { + ValueEval ve = OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec + .getColumnIndex()); + text = OperandResolver.coerceValueToString(ve); + switch (args.length) { + case 1: + isA1style = true; + break; + case 2: + isA1style = evaluateBooleanArg(args[1], ec); + break; + default: + return ErrorEval.VALUE_INVALID; + } + } catch (EvaluationException e) { + return e.getErrorEval(); + } - return evaluateIndirect(ec, text, isA1style); - } + return evaluateIndirect(ec, text, isA1style); + } - private static boolean evaluateBooleanArg(ValueEval arg, OperationEvaluationContext ec) - throws EvaluationException { - ValueEval ve = OperandResolver.getSingleValue(arg, ec.getRowIndex(), ec.getColumnIndex()); + private static boolean evaluateBooleanArg(ValueEval arg, OperationEvaluationContext ec) + throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(arg, ec.getRowIndex(), ec.getColumnIndex()); - if (ve == BlankEval.instance || ve == MissingArgEval.instance) { - return false; - } - // numeric quantities follow standard boolean conversion rules - // for strings, only "TRUE" and "FALSE" (case insensitive) are valid - return OperandResolver.coerceValueToBoolean(ve, false).booleanValue(); - } + if (ve == BlankEval.instance || ve == MissingArgEval.instance) { + return false; + } + // numeric quantities follow standard boolean conversion rules + // for strings, only "TRUE" and "FALSE" (case insensitive) are valid + return OperandResolver.coerceValueToBoolean(ve, false).booleanValue(); + } - private static ValueEval evaluateIndirect(final OperationEvaluationContext ec, String text, - boolean isA1style) { - - ec.getRowIndex(); - ec.getColumnIndex(); + private static ValueEval evaluateIndirect(final OperationEvaluationContext ec, String text, + boolean isA1style) { + + ec.getRowIndex(); + ec.getColumnIndex(); - // Search backwards for '!' because sheet names can contain '!' - int plingPos = text.lastIndexOf('!'); + // Search backwards for '!' because sheet names can contain '!' + int plingPos = text.lastIndexOf('!'); - String workbookName; - String sheetName; - String refText; // whitespace around this gets trimmed OK - if (plingPos < 0) { - workbookName = null; - sheetName = null; - refText = text; - } else { - String[] parts = parseWorkbookAndSheetName(text.subSequence(0, plingPos)); - if (parts == null) { - return ErrorEval.REF_INVALID; - } - workbookName = parts[0]; - sheetName = parts[1]; - refText = text.substring(plingPos + 1); - } + String workbookName; + String sheetName; + String refText; // whitespace around this gets trimmed OK + if (plingPos < 0) { + workbookName = null; + sheetName = null; + refText = text; + } else { + String[] parts = parseWorkbookAndSheetName(text.subSequence(0, plingPos)); + if (parts == null) { + return ErrorEval.REF_INVALID; + } + workbookName = parts[0]; + sheetName = parts[1]; + refText = text.substring(plingPos + 1); + } - String refStrPart1; - String refStrPart2; + String refStrPart1; + String refStrPart2; if (Table.isStructuredReference.matcher(refText).matches()) { // The argument is structured reference Area3DPxg areaPtg = null; try{ @@ -130,128 +130,128 @@ public final class Indirect implements FreeRefFunction { } return ec.getArea3DEval(areaPtg); } else { // The argumnet is regular reference - int colonPos = refText.indexOf(':'); - if (colonPos < 0) { - refStrPart1 = refText.trim(); - refStrPart2 = null; - } else { - refStrPart1 = refText.substring(0, colonPos).trim(); - refStrPart2 = refText.substring(colonPos + 1).trim(); - } - return ec.getDynamicReference(workbookName, sheetName, refStrPart1, refStrPart2, isA1style); + int colonPos = refText.indexOf(':'); + if (colonPos < 0) { + refStrPart1 = refText.trim(); + refStrPart2 = null; + } else { + refStrPart1 = refText.substring(0, colonPos).trim(); + refStrPart2 = refText.substring(colonPos + 1).trim(); + } + return ec.getDynamicReference(workbookName, sheetName, refStrPart1, refStrPart2, isA1style); } - } + } - /** - * @return array of length 2: {workbookName, sheetName,}. Second element will always be - * present. First element may be null if sheetName is unqualified. - * Returns null if text cannot be parsed. - */ - private static String[] parseWorkbookAndSheetName(CharSequence text) { - int lastIx = text.length() - 1; - if (lastIx < 0) { - return null; - } - if (canTrim(text)) { - return null; - } - char firstChar = text.charAt(0); - if (Character.isWhitespace(firstChar)) { - return null; - } - if (firstChar == '\'') { - // workbookName or sheetName needs quoting - // quotes go around both - if (text.charAt(lastIx) != '\'') { - return null; - } - firstChar = text.charAt(1); - if (Character.isWhitespace(firstChar)) { - return null; - } - String wbName; - int sheetStartPos; - if (firstChar == '[') { - int rbPos = text.toString().lastIndexOf(']'); - if (rbPos < 0) { - return null; - } - wbName = unescapeString(text.subSequence(2, rbPos)); - if (wbName == null || canTrim(wbName)) { - return null; - } - sheetStartPos = rbPos + 1; - } else { - wbName = null; - sheetStartPos = 1; - } + /** + * @return array of length 2: {workbookName, sheetName,}. Second element will always be + * present. First element may be null if sheetName is unqualified. + * Returns null if text cannot be parsed. + */ + private static String[] parseWorkbookAndSheetName(CharSequence text) { + int lastIx = text.length() - 1; + if (lastIx < 0) { + return null; + } + if (canTrim(text)) { + return null; + } + char firstChar = text.charAt(0); + if (Character.isWhitespace(firstChar)) { + return null; + } + if (firstChar == '\'') { + // workbookName or sheetName needs quoting + // quotes go around both + if (text.charAt(lastIx) != '\'') { + return null; + } + firstChar = text.charAt(1); + if (Character.isWhitespace(firstChar)) { + return null; + } + String wbName; + int sheetStartPos; + if (firstChar == '[') { + int rbPos = text.toString().lastIndexOf(']'); + if (rbPos < 0) { + return null; + } + wbName = unescapeString(text.subSequence(2, rbPos)); + if (wbName == null || canTrim(wbName)) { + return null; + } + sheetStartPos = rbPos + 1; + } else { + wbName = null; + sheetStartPos = 1; + } - // else - just sheet name - String sheetName = unescapeString(text.subSequence(sheetStartPos, lastIx)); - if (sheetName == null) { // note - when quoted, sheetName can - // start/end with whitespace - return null; - } - return new String[] { wbName, sheetName, }; - } + // else - just sheet name + String sheetName = unescapeString(text.subSequence(sheetStartPos, lastIx)); + if (sheetName == null) { // note - when quoted, sheetName can + // start/end with whitespace + return null; + } + return new String[] { wbName, sheetName, }; + } - if (firstChar == '[') { - int rbPos = text.toString().lastIndexOf(']'); - if (rbPos < 0) { - return null; - } - CharSequence wbName = text.subSequence(1, rbPos); - if (canTrim(wbName)) { - return null; - } - CharSequence sheetName = text.subSequence(rbPos + 1, text.length()); - if (canTrim(sheetName)) { - return null; - } - return new String[] { wbName.toString(), sheetName.toString(), }; - } - // else - just sheet name - return new String[] { null, text.toString(), }; - } + if (firstChar == '[') { + int rbPos = text.toString().lastIndexOf(']'); + if (rbPos < 0) { + return null; + } + CharSequence wbName = text.subSequence(1, rbPos); + if (canTrim(wbName)) { + return null; + } + CharSequence sheetName = text.subSequence(rbPos + 1, text.length()); + if (canTrim(sheetName)) { + return null; + } + return new String[] { wbName.toString(), sheetName.toString(), }; + } + // else - just sheet name + return new String[] { null, text.toString(), }; + } - /** - * @return null if there is a syntax error in any escape sequence - * (the typical syntax error is a single quote character not followed by another). - */ - private static String unescapeString(CharSequence text) { - int len = text.length(); - StringBuilder sb = new StringBuilder(len); - int i = 0; - while (i < len) { - char ch = text.charAt(i); - if (ch == '\'') { - // every quote must be followed by another - i++; - if (i >= len) { - return null; - } - ch = text.charAt(i); - if (ch != '\'') { - return null; - } - } - sb.append(ch); - i++; - } - return sb.toString(); - } + /** + * @return null if there is a syntax error in any escape sequence + * (the typical syntax error is a single quote character not followed by another). + */ + private static String unescapeString(CharSequence text) { + int len = text.length(); + StringBuilder sb = new StringBuilder(len); + int i = 0; + while (i < len) { + char ch = text.charAt(i); + if (ch == '\'') { + // every quote must be followed by another + i++; + if (i >= len) { + return null; + } + ch = text.charAt(i); + if (ch != '\'') { + return null; + } + } + sb.append(ch); + i++; + } + return sb.toString(); + } - private static boolean canTrim(CharSequence text) { - int lastIx = text.length() - 1; - if (lastIx < 0) { - return false; - } - if (Character.isWhitespace(text.charAt(0))) { - return true; - } - if (Character.isWhitespace(text.charAt(lastIx))) { - return true; - } - return false; - } + private static boolean canTrim(CharSequence text) { + int lastIx = text.length() - 1; + if (lastIx < 0) { + return false; + } + if (Character.isWhitespace(text.charAt(0))) { + return true; + } + if (Character.isWhitespace(text.charAt(lastIx))) { + return true; + } + return false; + } } 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 f542e0f172..923ca57c58 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java @@ -406,9 +406,9 @@ public final class XSSFCell implements Cell { if (cachedValueType != expectedTypeCode) { throw typeMismatch(expectedTypeCode, cachedValueType, true); } - } + } - /** + /** * Set a string value for the cell. * * @param str value to set the cell to. For formulas we'll set the formula @@ -925,8 +925,8 @@ public final class XSSFCell implements Cell { throw new IllegalArgumentException("Illegal cell type: " + cellType); } if (cellType != CELL_TYPE_FORMULA && _cell.isSetF()) { - _cell.unsetF(); - } + _cell.unsetF(); + } } /** diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java index 74974fe7d2..742720f788 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -1762,7 +1762,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { * Get the document's embedded files. */ @Override - public List getAllEmbedds() throws OpenXML4JException { + public List getAllEmbedds() throws OpenXML4JException { List embedds = new LinkedList(); for(XSSFSheet sheet : sheets){ @@ -1929,7 +1929,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { */ @Internal public MapInfo getMapInfo(){ - return mapInfo; + return mapInfo; } /** @@ -1946,92 +1946,92 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { throw new RuntimeException("Not Implemented - see bug #57184"); } - /** - * Specifies a boolean value that indicates whether structure of workbook is locked.
- * A value true indicates the structure of the workbook is locked. Worksheets in the workbook can't be moved, - * deleted, hidden, unhidden, or renamed, and new worksheets can't be inserted.
- * A value of false indicates the structure of the workbook is not locked.
- * - * @return true if structure of workbook is locked - */ - public boolean isStructureLocked() { - return workbookProtectionPresent() && workbook.getWorkbookProtection().getLockStructure(); - } - - /** - * Specifies a boolean value that indicates whether the windows that comprise the workbook are locked.
- * A value of true indicates the workbook windows are locked. Windows are the same size and position each time the - * workbook is opened.
- * A value of false indicates the workbook windows are not locked. - * - * @return true if windows that comprise the workbook are locked - */ - public boolean isWindowsLocked() { - return workbookProtectionPresent() && workbook.getWorkbookProtection().getLockWindows(); - } - - /** - * Specifies a boolean value that indicates whether the workbook is locked for revisions. - * - * @return true if the workbook is locked for revisions. - */ - public boolean isRevisionLocked() { - return workbookProtectionPresent() && workbook.getWorkbookProtection().getLockRevision(); - } - - /** - * Locks the structure of workbook. - */ - public void lockStructure() { - safeGetWorkbookProtection().setLockStructure(true); - } - - /** - * Unlocks the structure of workbook. - */ - public void unLockStructure() { - safeGetWorkbookProtection().setLockStructure(false); - } - - /** - * Locks the windows that comprise the workbook. - */ - public void lockWindows() { - safeGetWorkbookProtection().setLockWindows(true); - } - - /** - * Unlocks the windows that comprise the workbook. - */ - public void unLockWindows() { - safeGetWorkbookProtection().setLockWindows(false); - } - - /** - * Locks the workbook for revisions. - */ - public void lockRevision() { - safeGetWorkbookProtection().setLockRevision(true); - } - - /** - * Unlocks the workbook for revisions. - */ - public void unLockRevision() { - safeGetWorkbookProtection().setLockRevision(false); - } - - /** - * Sets the workbook password. - * - * @param password if null, the password will be removed - * @param hashAlgo if null, the password will be set as XOR password (Excel 2010 and earlier) - * otherwise the given algorithm is used for calculating the hash password (Excel 2013) - */ - public void setWorkbookPassword(String password, HashAlgorithm hashAlgo) { + /** + * Specifies a boolean value that indicates whether structure of workbook is locked.
+ * A value true indicates the structure of the workbook is locked. Worksheets in the workbook can't be moved, + * deleted, hidden, unhidden, or renamed, and new worksheets can't be inserted.
+ * A value of false indicates the structure of the workbook is not locked.
+ * + * @return true if structure of workbook is locked + */ + public boolean isStructureLocked() { + return workbookProtectionPresent() && workbook.getWorkbookProtection().getLockStructure(); + } + + /** + * Specifies a boolean value that indicates whether the windows that comprise the workbook are locked.
+ * A value of true indicates the workbook windows are locked. Windows are the same size and position each time the + * workbook is opened.
+ * A value of false indicates the workbook windows are not locked. + * + * @return true if windows that comprise the workbook are locked + */ + public boolean isWindowsLocked() { + return workbookProtectionPresent() && workbook.getWorkbookProtection().getLockWindows(); + } + + /** + * Specifies a boolean value that indicates whether the workbook is locked for revisions. + * + * @return true if the workbook is locked for revisions. + */ + public boolean isRevisionLocked() { + return workbookProtectionPresent() && workbook.getWorkbookProtection().getLockRevision(); + } + + /** + * Locks the structure of workbook. + */ + public void lockStructure() { + safeGetWorkbookProtection().setLockStructure(true); + } + + /** + * Unlocks the structure of workbook. + */ + public void unLockStructure() { + safeGetWorkbookProtection().setLockStructure(false); + } + + /** + * Locks the windows that comprise the workbook. + */ + public void lockWindows() { + safeGetWorkbookProtection().setLockWindows(true); + } + + /** + * Unlocks the windows that comprise the workbook. + */ + public void unLockWindows() { + safeGetWorkbookProtection().setLockWindows(false); + } + + /** + * Locks the workbook for revisions. + */ + public void lockRevision() { + safeGetWorkbookProtection().setLockRevision(true); + } + + /** + * Unlocks the workbook for revisions. + */ + public void unLockRevision() { + safeGetWorkbookProtection().setLockRevision(false); + } + + /** + * Sets the workbook password. + * + * @param password if null, the password will be removed + * @param hashAlgo if null, the password will be set as XOR password (Excel 2010 and earlier) + * otherwise the given algorithm is used for calculating the hash password (Excel 2013) + */ + public void setWorkbookPassword(String password, HashAlgorithm hashAlgo) { if (password == null && !workbookProtectionPresent()) return; setPassword(safeGetWorkbookProtection(), password, hashAlgo, "workbook"); - } + } /** * Validate the password against the stored hash, the hashing method will be determined @@ -2074,9 +2074,9 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { } } - private boolean workbookProtectionPresent() { - return workbook.isSetWorkbookProtection(); - } + private boolean workbookProtectionPresent() { + return workbook.isSetWorkbookProtection(); + } private CTWorkbookProtection safeGetWorkbookProtection() { if (!workbookProtectionPresent()){ @@ -2084,7 +2084,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { } return workbook.getWorkbookProtection(); } - + /** * * Returns the locator of user-defined functions. diff --git a/src/testcases/org/apache/poi/hssf/record/TestSharedFormulaRecord.java b/src/testcases/org/apache/poi/hssf/record/TestSharedFormulaRecord.java index 7b81c7cff7..cf49432ded 100644 --- a/src/testcases/org/apache/poi/hssf/record/TestSharedFormulaRecord.java +++ b/src/testcases/org/apache/poi/hssf/record/TestSharedFormulaRecord.java @@ -38,67 +38,67 @@ import org.apache.poi.util.LittleEndianInput; */ public final class TestSharedFormulaRecord extends TestCase { - /** - * A sample spreadsheet known to have one sheet with 4 shared formula ranges - */ - private static final String SHARED_FORMULA_TEST_XLS = "SharedFormulaTest.xls"; - /** - * Binary data for an encoded formula. Taken from attachment 22062 (bugzilla 45123/45421). - * The shared formula is in Sheet1!C6:C21, with text "SUMPRODUCT(--(End_Acct=$C6),--(End_Bal))" - * This data is found at offset 0x1A4A (within the shared formula record). - * The critical thing about this formula is that it contains shared formula tokens (tRefN*, - * tAreaN*) with operand class 'array'. - */ - private static final byte[] SHARED_FORMULA_WITH_REF_ARRAYS_DATA = { - 0x1A, 0x00, - 0x63, 0x02, 0x00, 0x00, 0x00, - 0x6C, 0x00, 0x00, 0x02, (byte)0x80, // tRefNA - 0x0B, - 0x15, - 0x13, - 0x13, - 0x63, 0x03, 0x00, 0x00, 0x00, - 0x15, - 0x13, - 0x13, - 0x42, 0x02, (byte)0xE4, 0x00, - }; - - /** - * The method SharedFormulaRecord.convertSharedFormulas() converts formulas from - * 'shared formula' to 'single cell formula' format. It is important that token operand - * classes are preserved during this transformation, because Excel may not tolerate the - * incorrect encoding. The formula here is one such example (Excel displays #VALUE!). - */ - public void testConvertSharedFormulasOperandClasses_bug45123() { - - LittleEndianInput in = TestcaseRecordInputStream.createLittleEndian(SHARED_FORMULA_WITH_REF_ARRAYS_DATA); - int encodedLen = in.readUShort(); - Ptg[] sharedFormula = Ptg.readTokens(encodedLen, in); + /** + * A sample spreadsheet known to have one sheet with 4 shared formula ranges + */ + private static final String SHARED_FORMULA_TEST_XLS = "SharedFormulaTest.xls"; + /** + * Binary data for an encoded formula. Taken from attachment 22062 (bugzilla 45123/45421). + * The shared formula is in Sheet1!C6:C21, with text "SUMPRODUCT(--(End_Acct=$C6),--(End_Bal))" + * This data is found at offset 0x1A4A (within the shared formula record). + * The critical thing about this formula is that it contains shared formula tokens (tRefN*, + * tAreaN*) with operand class 'array'. + */ + private static final byte[] SHARED_FORMULA_WITH_REF_ARRAYS_DATA = { + 0x1A, 0x00, + 0x63, 0x02, 0x00, 0x00, 0x00, + 0x6C, 0x00, 0x00, 0x02, (byte)0x80, // tRefNA + 0x0B, + 0x15, + 0x13, + 0x13, + 0x63, 0x03, 0x00, 0x00, 0x00, + 0x15, + 0x13, + 0x13, + 0x42, 0x02, (byte)0xE4, 0x00, + }; + + /** + * The method SharedFormulaRecord.convertSharedFormulas() converts formulas from + * 'shared formula' to 'single cell formula' format. It is important that token operand + * classes are preserved during this transformation, because Excel may not tolerate the + * incorrect encoding. The formula here is one such example (Excel displays #VALUE!). + */ + public void testConvertSharedFormulasOperandClasses_bug45123() { + + LittleEndianInput in = TestcaseRecordInputStream.createLittleEndian(SHARED_FORMULA_WITH_REF_ARRAYS_DATA); + int encodedLen = in.readUShort(); + Ptg[] sharedFormula = Ptg.readTokens(encodedLen, in); SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL97); - Ptg[] convertedFormula = sf.convertSharedFormulas(sharedFormula, 100, 200); - - RefPtg refPtg = (RefPtg) convertedFormula[1]; - assertEquals("$C101", refPtg.toFormulaString()); - if (refPtg.getPtgClass() == Ptg.CLASS_REF) { - throw new AssertionFailedError("Identified bug 45123"); - } - - confirmOperandClasses(sharedFormula, convertedFormula); - } - - private static void confirmOperandClasses(Ptg[] originalPtgs, Ptg[] convertedPtgs) { - assertEquals(originalPtgs.length, convertedPtgs.length); - for (int i = 0; i < convertedPtgs.length; i++) { - Ptg originalPtg = originalPtgs[i]; - Ptg convertedPtg = convertedPtgs[i]; - if (originalPtg.getPtgClass() != convertedPtg.getPtgClass()) { - throw new ComparisonFailure("Different operand class for token[" + i + "]", - String.valueOf(originalPtg.getPtgClass()), String.valueOf(convertedPtg.getPtgClass())); - } - } - } + Ptg[] convertedFormula = sf.convertSharedFormulas(sharedFormula, 100, 200); + + RefPtg refPtg = (RefPtg) convertedFormula[1]; + assertEquals("$C101", refPtg.toFormulaString()); + if (refPtg.getPtgClass() == Ptg.CLASS_REF) { + throw new AssertionFailedError("Identified bug 45123"); + } + + confirmOperandClasses(sharedFormula, convertedFormula); + } + + private static void confirmOperandClasses(Ptg[] originalPtgs, Ptg[] convertedPtgs) { + assertEquals(originalPtgs.length, convertedPtgs.length); + for (int i = 0; i < convertedPtgs.length; i++) { + Ptg originalPtg = originalPtgs[i]; + Ptg convertedPtg = convertedPtgs[i]; + if (originalPtg.getPtgClass() != convertedPtg.getPtgClass()) { + throw new ComparisonFailure("Different operand class for token[" + i + "]", + String.valueOf(originalPtg.getPtgClass()), String.valueOf(convertedPtg.getPtgClass())); + } + } + } public void testConvertSharedFormulas() { HSSFWorkbook wb = new HSSFWorkbook(); @@ -138,111 +138,111 @@ public final class TestSharedFormulaRecord extends TestCase { } /** - * Make sure that POI preserves {@link SharedFormulaRecord}s - */ - public void testPreserveOnReserialize() { - HSSFWorkbook wb; - HSSFSheet sheet; - HSSFCell cellB32769; - HSSFCell cellC32769; - - // Reading directly from XLS file - wb = HSSFTestDataSamples.openSampleWorkbook(SHARED_FORMULA_TEST_XLS); - sheet = wb.getSheetAt(0); - cellB32769 = sheet.getRow(32768).getCell(1); - cellC32769 = sheet.getRow(32768).getCell(2); - // check reading of formulas which are shared (two cells from a 1R x 8C range) - assertEquals("B32770*2", cellB32769.getCellFormula()); - assertEquals("C32770*2", cellC32769.getCellFormula()); - confirmCellEvaluation(wb, cellB32769, 4); - confirmCellEvaluation(wb, cellC32769, 6); - // Confirm this example really does have SharedFormulas. - // there are 3 others besides the one at A32769:H32769 - assertEquals(4, countSharedFormulas(sheet)); - - - // Re-serialize and check again - wb = HSSFTestDataSamples.writeOutAndReadBack(wb); - sheet = wb.getSheetAt(0); - cellB32769 = sheet.getRow(32768).getCell(1); - cellC32769 = sheet.getRow(32768).getCell(2); - assertEquals("B32770*2", cellB32769.getCellFormula()); - confirmCellEvaluation(wb, cellB32769, 4); - assertEquals(4, countSharedFormulas(sheet)); - } - - public void testUnshareFormulaDueToChangeFormula() { - HSSFWorkbook wb; - HSSFSheet sheet; - HSSFCell cellB32769; - HSSFCell cellC32769; - - wb = HSSFTestDataSamples.openSampleWorkbook(SHARED_FORMULA_TEST_XLS); - sheet = wb.getSheetAt(0); - cellB32769 = sheet.getRow(32768).getCell(1); - cellC32769 = sheet.getRow(32768).getCell(2); - - // Updating cell formula, causing it to become unshared - cellB32769.setCellFormula("1+1"); - confirmCellEvaluation(wb, cellB32769, 2); - // currently (Oct 2008) POI handles this by exploding the whole shared formula group - assertEquals(3, countSharedFormulas(sheet)); // one less now - // check that nearby cell of the same group still has the same formula - assertEquals("C32770*2", cellC32769.getCellFormula()); - confirmCellEvaluation(wb, cellC32769, 6); - } - public void testUnshareFormulaDueToDelete() { - HSSFWorkbook wb; - HSSFSheet sheet; - HSSFCell cell; - final int ROW_IX = 2; - - // changing shared formula cell to blank - wb = HSSFTestDataSamples.openSampleWorkbook(SHARED_FORMULA_TEST_XLS); - sheet = wb.getSheetAt(0); - - assertEquals("A$1*2", sheet.getRow(ROW_IX).getCell(1).getCellFormula()); - cell = sheet.getRow(ROW_IX).getCell(1); - cell.setCellType(HSSFCell.CELL_TYPE_BLANK); - assertEquals(3, countSharedFormulas(sheet)); - - wb = HSSFTestDataSamples.writeOutAndReadBack(wb); - sheet = wb.getSheetAt(0); - assertEquals("A$1*2", sheet.getRow(ROW_IX+1).getCell(1).getCellFormula()); - - // deleting shared formula cell - wb = HSSFTestDataSamples.openSampleWorkbook(SHARED_FORMULA_TEST_XLS); - sheet = wb.getSheetAt(0); - - assertEquals("A$1*2", sheet.getRow(ROW_IX).getCell(1).getCellFormula()); - cell = sheet.getRow(ROW_IX).getCell(1); - sheet.getRow(ROW_IX).removeCell(cell); - assertEquals(3, countSharedFormulas(sheet)); - - wb = HSSFTestDataSamples.writeOutAndReadBack(wb); - sheet = wb.getSheetAt(0); - assertEquals("A$1*2", sheet.getRow(ROW_IX+1).getCell(1).getCellFormula()); - } - - private static void confirmCellEvaluation(HSSFWorkbook wb, HSSFCell cell, double expectedValue) { - HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); - CellValue cv = fe.evaluate(cell); - assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType()); - assertEquals(expectedValue, cv.getNumberValue(), 0.0); - } - - /** - * @return the number of {@link SharedFormulaRecord}s encoded for the specified sheet - */ - private static int countSharedFormulas(HSSFSheet sheet) { - Record[] records = RecordInspector.getRecords(sheet, 0); - int count = 0; - for (int i = 0; i < records.length; i++) { - Record rec = records[i]; - if(rec instanceof SharedFormulaRecord) { - count++; - } - } - return count; - } + * Make sure that POI preserves {@link SharedFormulaRecord}s + */ + public void testPreserveOnReserialize() { + HSSFWorkbook wb; + HSSFSheet sheet; + HSSFCell cellB32769; + HSSFCell cellC32769; + + // Reading directly from XLS file + wb = HSSFTestDataSamples.openSampleWorkbook(SHARED_FORMULA_TEST_XLS); + sheet = wb.getSheetAt(0); + cellB32769 = sheet.getRow(32768).getCell(1); + cellC32769 = sheet.getRow(32768).getCell(2); + // check reading of formulas which are shared (two cells from a 1R x 8C range) + assertEquals("B32770*2", cellB32769.getCellFormula()); + assertEquals("C32770*2", cellC32769.getCellFormula()); + confirmCellEvaluation(wb, cellB32769, 4); + confirmCellEvaluation(wb, cellC32769, 6); + // Confirm this example really does have SharedFormulas. + // there are 3 others besides the one at A32769:H32769 + assertEquals(4, countSharedFormulas(sheet)); + + + // Re-serialize and check again + wb = HSSFTestDataSamples.writeOutAndReadBack(wb); + sheet = wb.getSheetAt(0); + cellB32769 = sheet.getRow(32768).getCell(1); + cellC32769 = sheet.getRow(32768).getCell(2); + assertEquals("B32770*2", cellB32769.getCellFormula()); + confirmCellEvaluation(wb, cellB32769, 4); + assertEquals(4, countSharedFormulas(sheet)); + } + + public void testUnshareFormulaDueToChangeFormula() { + HSSFWorkbook wb; + HSSFSheet sheet; + HSSFCell cellB32769; + HSSFCell cellC32769; + + wb = HSSFTestDataSamples.openSampleWorkbook(SHARED_FORMULA_TEST_XLS); + sheet = wb.getSheetAt(0); + cellB32769 = sheet.getRow(32768).getCell(1); + cellC32769 = sheet.getRow(32768).getCell(2); + + // Updating cell formula, causing it to become unshared + cellB32769.setCellFormula("1+1"); + confirmCellEvaluation(wb, cellB32769, 2); + // currently (Oct 2008) POI handles this by exploding the whole shared formula group + assertEquals(3, countSharedFormulas(sheet)); // one less now + // check that nearby cell of the same group still has the same formula + assertEquals("C32770*2", cellC32769.getCellFormula()); + confirmCellEvaluation(wb, cellC32769, 6); + } + public void testUnshareFormulaDueToDelete() { + HSSFWorkbook wb; + HSSFSheet sheet; + HSSFCell cell; + final int ROW_IX = 2; + + // changing shared formula cell to blank + wb = HSSFTestDataSamples.openSampleWorkbook(SHARED_FORMULA_TEST_XLS); + sheet = wb.getSheetAt(0); + + assertEquals("A$1*2", sheet.getRow(ROW_IX).getCell(1).getCellFormula()); + cell = sheet.getRow(ROW_IX).getCell(1); + cell.setCellType(HSSFCell.CELL_TYPE_BLANK); + assertEquals(3, countSharedFormulas(sheet)); + + wb = HSSFTestDataSamples.writeOutAndReadBack(wb); + sheet = wb.getSheetAt(0); + assertEquals("A$1*2", sheet.getRow(ROW_IX+1).getCell(1).getCellFormula()); + + // deleting shared formula cell + wb = HSSFTestDataSamples.openSampleWorkbook(SHARED_FORMULA_TEST_XLS); + sheet = wb.getSheetAt(0); + + assertEquals("A$1*2", sheet.getRow(ROW_IX).getCell(1).getCellFormula()); + cell = sheet.getRow(ROW_IX).getCell(1); + sheet.getRow(ROW_IX).removeCell(cell); + assertEquals(3, countSharedFormulas(sheet)); + + wb = HSSFTestDataSamples.writeOutAndReadBack(wb); + sheet = wb.getSheetAt(0); + assertEquals("A$1*2", sheet.getRow(ROW_IX+1).getCell(1).getCellFormula()); + } + + private static void confirmCellEvaluation(HSSFWorkbook wb, HSSFCell cell, double expectedValue) { + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + CellValue cv = fe.evaluate(cell); + assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType()); + assertEquals(expectedValue, cv.getNumberValue(), 0.0); + } + + /** + * @return the number of {@link SharedFormulaRecord}s encoded for the specified sheet + */ + private static int countSharedFormulas(HSSFSheet sheet) { + Record[] records = RecordInspector.getRecords(sheet, 0); + int count = 0; + for (int i = 0; i < records.length; i++) { + Record rec = records[i]; + if(rec instanceof SharedFormulaRecord) { + count++; + } + } + return count; + } } -- 2.39.5