/* * Licensed to the Apache Software Foundation (ASF) Under one or more * contributor license agreements. See the NOTICE file distributed with * this work for Additional information regarding copyright ownership. * The ASF licenses this file to You Under the Apache License, Version 2.0 * (the "License"); you may not use this file except in compliance with * the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed Under the License is distributed on an "AS Is" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations Under the License. */ namespace HH.WMS.Utils.NPOI.HSSF.UserModel { using System; using System.Collections; using HH.WMS.Utils.NPOI.SS.Formula; using HH.WMS.Utils.NPOI.SS.Formula.Eval; using HH.WMS.Utils.NPOI.SS.Formula.PTG; using HH.WMS.Utils.NPOI.SS.Formula.Udf; using HH.WMS.Utils.NPOI.SS.UserModel; /** * @author Amol S. Deshmukh < amolweb at ya hoo dot com > * */ public class HSSFFormulaEvaluator : IFormulaEvaluator { private WorkbookEvaluator _bookEvaluator; // params to lookup the right constructor using reflection private static Type[] VALUE_CONTRUCTOR_CLASS_ARRAY = new Type[] { typeof(Ptg) }; private static Type[] AREA3D_CONSTRUCTOR_CLASS_ARRAY = new Type[] { typeof(Ptg), typeof(ValueEval[]) }; private static Type[] REFERENCE_CONSTRUCTOR_CLASS_ARRAY = new Type[] { typeof(Ptg), typeof(ValueEval) }; private static Type[] REF3D_CONSTRUCTOR_CLASS_ARRAY = new Type[] { typeof(Ptg), typeof(ValueEval) }; // Maps for mapping *Eval to *Ptg private static Hashtable VALUE_EVALS_MAP = new Hashtable(); /* * Following is the mapping between the Ptg tokens returned * by the FormulaParser and the *Eval classes that are used * by the FormulaEvaluator */ static HSSFFormulaEvaluator() { VALUE_EVALS_MAP[typeof(BoolPtg)] = typeof(BoolEval); VALUE_EVALS_MAP[typeof(IntPtg)] = typeof(NumberEval); VALUE_EVALS_MAP[typeof(NumberPtg)] = typeof(NumberEval); VALUE_EVALS_MAP[typeof(StringPtg)] = typeof(StringEval); } protected IRow row; protected ISheet sheet; protected IWorkbook workbook; [Obsolete] public HSSFFormulaEvaluator(ISheet sheet, IWorkbook workbook) : this(workbook) { this.sheet = sheet; this.workbook = workbook; } public HSSFFormulaEvaluator(IWorkbook workbook) : this(workbook, null) { this.workbook = workbook; } /** * @param stabilityClassifier used to optimise caching performance. Pass null * for the (conservative) assumption that any cell may have its definition changed after * evaluation begins. */ public HSSFFormulaEvaluator(IWorkbook workbook, IStabilityClassifier stabilityClassifier) : this(workbook, stabilityClassifier, null) { } /** * @param udfFinder pass null for default (AnalysisToolPak only) */ public HSSFFormulaEvaluator(IWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) { _bookEvaluator = new WorkbookEvaluator(HSSFEvaluationWorkbook.Create(workbook), stabilityClassifier, udfFinder); } /** * @param stabilityClassifier used to optimise caching performance. Pass null * for the (conservative) assumption that any cell may have its definition changed after * evaluation begins. * @param udfFinder pass null for default (AnalysisToolPak only) */ public static HSSFFormulaEvaluator Create(IWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) { return new HSSFFormulaEvaluator(workbook, stabilityClassifier, udfFinder); } private static void SetCellType(ICell cell, CellValue cv) { CellType cellType = cv.CellType; switch (cellType) { case CellType.BOOLEAN: case CellType.ERROR: case CellType.NUMERIC: case CellType.STRING: cell.SetCellType(cellType); return; case CellType.BLANK: // never happens - blanks eventually get translated to zero break; case CellType.FORMULA: // this will never happen, we have already evaluated the formula break; } throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")"); } private static void SetCellValue(ICell cell, CellValue cv) { CellType cellType = cv.CellType; switch (cellType) { case CellType.BOOLEAN: cell.SetCellValue(cv.BooleanValue); break; case CellType.ERROR: cell.SetCellErrorValue((byte)cv.ErrorValue); break; case CellType.NUMERIC: cell.SetCellValue(cv.NumberValue); break; case CellType.STRING: cell.SetCellValue(new HSSFRichTextString(cv.StringValue)); break; //case CellType.BLANK: //// never happens - blanks eventually get translated to zero //case CellType.FORMULA: //// this will never happen, we have already evaluated the formula default: throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")"); } } /** * Coordinates several formula evaluators together so that formulas that involve external * references can be evaluated. * @param workbookNames the simple file names used to identify the workbooks in formulas * with external links (for example "MyData.xls" as used in a formula "[MyData.xls]Sheet1!A1") * @param evaluators all evaluators for the full set of workbooks required by the formulas. */ public static void SetupEnvironment(String[] workbookNames, HSSFFormulaEvaluator[] evaluators) { WorkbookEvaluator[] wbEvals = new WorkbookEvaluator[evaluators.Length]; for (int i = 0; i < wbEvals.Length; i++) { wbEvals[i] = evaluators[i]._bookEvaluator; } CollaboratingWorkbooksEnvironment.Setup(workbookNames, wbEvals); } /** * If cell Contains a formula, the formula is Evaluated and returned, * else the CellValue simply copies the appropriate cell value from * the cell and also its cell type. This method should be preferred over * EvaluateInCell() when the call should not modify the contents of the * original cell. * @param cell */ /** * If cell contains a formula, the formula is evaluated and returned, * else the CellValue simply copies the appropriate cell value from * the cell and also its cell type. This method should be preferred over * evaluateInCell() when the call should not modify the contents of the * original cell. * * @param cell may be null signifying that the cell is not present (or blank) * @return null if the supplied cell is null or blank */ public CellValue Evaluate(ICell cell) { if (cell == null) { return null; } switch (cell.CellType) { case CellType.BOOLEAN: return CellValue.ValueOf(cell.BooleanCellValue); case CellType.ERROR: return CellValue.GetError(cell.ErrorCellValue); case CellType.FORMULA: return EvaluateFormulaCellValue(cell); case CellType.NUMERIC: return new CellValue(cell.NumericCellValue); case CellType.STRING: return new CellValue(cell.RichStringCellValue.String); case CellType.BLANK: return null; } throw new InvalidOperationException("Bad cell type (" + cell.CellType + ")"); } /** * Should be called whenever there are major changes (e.g. moving sheets) to input cells * in the evaluated workbook. If performance is not critical, a single call to this method * may be used instead of many specific calls to the notify~ methods. * * Failure to call this method after changing cell values will cause incorrect behaviour * of the evaluate~ methods of this class */ public void ClearAllCachedResultValues() { _bookEvaluator.ClearAllCachedResultValues(); } /** * Should be called to tell the cell value cache that the specified (value or formula) cell * has changed. * Failure to call this method after changing cell values will cause incorrect behaviour * of the evaluate~ methods of this class */ public void NotifyUpdateCell(ICell cell) { _bookEvaluator.NotifyUpdateCell(new HSSFEvaluationCell(cell)); } /** * Should be called to tell the cell value cache that the specified cell has just been * deleted. * Failure to call this method after changing cell values will cause incorrect behaviour * of the evaluate~ methods of this class */ public void NotifyDeleteCell(ICell cell) { _bookEvaluator.NotifyDeleteCell(new HSSFEvaluationCell(cell)); } /** * Should be called to tell the cell value cache that the specified (value or formula) cell * has changed. * Failure to call this method after changing cell values will cause incorrect behaviour * of the evaluate~ methods of this class */ public void NotifySetFormula(ICell cell) { _bookEvaluator.NotifyUpdateCell(new HSSFEvaluationCell(cell)); } /** * If cell Contains formula, it Evaluates the formula, * and saves the result of the formula. The cell * remains as a formula cell. * Else if cell does not contain formula, this method leaves * the cell UnChanged. * Note that the type of the formula result is returned, * so you know what kind of value is also stored with * the formula. *
         * int EvaluatedCellType = evaluator.EvaluateFormulaCell(cell);
         * 
* Be aware that your cell will hold both the formula, * and the result. If you want the cell Replaced with * the result of the formula, use {@link #EvaluateInCell(HSSFCell)} * @param cell The cell to Evaluate * @return The type of the formula result (the cell's type remains as CellType.FORMULA however) */ public CellType EvaluateFormulaCell(ICell cell) { if (cell == null || cell.CellType != CellType.FORMULA) { return CellType.Unknown; } CellValue cv = EvaluateFormulaCellValue(cell); // cell remains a formula cell, but the cached value is changed SetCellValue(cell, cv); return cv.CellType; } /** * Returns a CellValue wrapper around the supplied ValueEval instance. * @param eval */ private CellValue EvaluateFormulaCellValue(ICell cell) { ValueEval eval = _bookEvaluator.Evaluate(new HSSFEvaluationCell((HSSFCell)cell)); if (eval is NumberEval) { NumberEval ne = (NumberEval)eval; return new CellValue(ne.NumberValue); } if (eval is BoolEval) { BoolEval be = (BoolEval)eval; return CellValue.ValueOf(be.BooleanValue); } if (eval is StringEval) { StringEval ne = (StringEval)eval; return new CellValue(ne.StringValue); } if (eval is ErrorEval) { return CellValue.GetError(((ErrorEval)eval).ErrorCode); } throw new InvalidOperationException("Unexpected eval class (" + eval.GetType().Name + ")"); } /** * If cell Contains formula, it Evaluates the formula, and * puts the formula result back into the cell, in place * of the old formula. * Else if cell does not contain formula, this method leaves * the cell UnChanged. * Note that the same instance of Cell is returned to * allow chained calls like: *
         * int EvaluatedCellType = evaluator.EvaluateInCell(cell).CellType;
         * 
* Be aware that your cell value will be Changed to hold the * result of the formula. If you simply want the formula * value computed for you, use {@link #EvaluateFormulaCell(HSSFCell)} * @param cell */ public ICell EvaluateInCell(ICell cell) { if (cell == null) { return null; } if (cell.CellType == CellType.FORMULA) { CellValue cv = EvaluateFormulaCellValue(cell); SetCellValue(cell, cv); SetCellType(cell, cv); // cell will no longer be a formula cell } return cell; } /** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * Evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no Changes * are made. * This is a helpful wrapper around looping over all * cells, and calling EvaluateFormulaCell on each one. */ public static void EvaluateAllFormulaCells(HSSFWorkbook wb) { EvaluateAllFormulaCells(wb, new HSSFFormulaEvaluator(wb)); } /** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no changes * are made. * This is a helpful wrapper around looping over all * cells, and calling evaluateFormulaCell on each one. */ public static void EvaluateAllFormulaCells(IWorkbook wb) { IFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); EvaluateAllFormulaCells(wb, evaluator); } private static void EvaluateAllFormulaCells(IWorkbook wb, IFormulaEvaluator evaluator) { for (int i = 0; i < wb.NumberOfSheets; i++) { ISheet sheet = wb.GetSheetAt(i); for (IEnumerator it = sheet.GetRowEnumerator(); it.MoveNext(); ) { IRow r = (IRow)it.Current; foreach (ICell c in r.Cells) { if (c.CellType == CellType.FORMULA) { evaluator.EvaluateFormulaCell(c); } } } } } public void EvaluateAll() { HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook, this); } } }