/*
* 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
* 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); } } }