namespace HH.WMS.Utils.NPOI.SS.Formula { using System; using HH.WMS.Utils.NPOI.SS.Formula.Eval; using HH.WMS.Utils.NPOI.SS.Formula.Functions; using HH.WMS.Utils.NPOI.SS.Util; using HH.WMS.Utils.NPOI.SS.Formula; using HH.WMS.Utils.NPOI.SS.Formula.PTG; using System.Globalization; /** * Contains all the contextual information required to Evaluate an operation * within a formula * * For POI internal use only * * @author Josh Micich */ public class OperationEvaluationContext { public static FreeRefFunction UDF = UserDefinedFunction.instance; private IEvaluationWorkbook _workbook; private int _sheetIndex; private int _rowIndex; private int _columnIndex; private EvaluationTracker _tracker; private WorkbookEvaluator _bookEvaluator; public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, IEvaluationWorkbook workbook, int sheetIndex, int srcRowNum, int srcColNum, EvaluationTracker tracker) { _bookEvaluator = bookEvaluator; _workbook = workbook; _sheetIndex = sheetIndex; _rowIndex = srcRowNum; _columnIndex = srcColNum; _tracker = tracker; } public IEvaluationWorkbook GetWorkbook() { return _workbook; } public int RowIndex { get { return _rowIndex; } } public int ColumnIndex { get { return _columnIndex; } } SheetRefEvaluator CreateExternSheetRefEvaluator(IExternSheetReferenceToken ptg) { return CreateExternSheetRefEvaluator(ptg.ExternSheetIndex); } SheetRefEvaluator CreateExternSheetRefEvaluator(int externSheetIndex) { ExternalSheet externalSheet = _workbook.GetExternalSheet(externSheetIndex); WorkbookEvaluator targetEvaluator; int otherSheetIndex; if (externalSheet == null) { // sheet is in same workbook otherSheetIndex = _workbook.ConvertFromExternSheetIndex(externSheetIndex); targetEvaluator = _bookEvaluator; } else { // look up sheet by name from external workbook String workbookName = externalSheet.GetWorkbookName(); try { targetEvaluator = _bookEvaluator.GetOtherWorkbookEvaluator(workbookName); } catch (WorkbookNotFoundException) { throw; } otherSheetIndex = targetEvaluator.GetSheetIndex(externalSheet.GetSheetName()); if (otherSheetIndex < 0) { throw new Exception("Invalid sheet name '" + externalSheet.GetSheetName() + "' in bool '" + workbookName + "'."); } } 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 ArgumentException("sheetName must not be null if workbookName is provided"); } try { targetEvaluator = _bookEvaluator.GetOtherWorkbookEvaluator(workbookName); } catch (WorkbookNotFoundException) { return null; } } int otherSheetIndex = sheetName == null ? _sheetIndex : targetEvaluator.GetSheetIndex(sheetName); if (otherSheetIndex < 0) { return null; } return new SheetRefEvaluator(targetEvaluator, _tracker, otherSheetIndex); } public SheetRefEvaluator GetRefEvaluatorForCurrentSheet() { return new SheetRefEvaluator(_bookEvaluator, _tracker, _sheetIndex); } /** * 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, bool isA1Style) { if (!isA1Style) { throw new Exception("R1C1 style not supported yet"); } SheetRefEvaluator sre = CreateExternSheetRefEvaluator(workbookName, sheetName); if (sre == null) { return ErrorEval.REF_INVALID; } // ugly typecast - TODO - make spReadsheet version more easily accessible SpreadsheetVersion ssVersion = ((IFormulaParsingWorkbook)_workbook).GetSpreadsheetVersion(); NameType part1refType = ClassifyCellReference(refStrPart1, ssVersion); switch (part1refType) { case NameType.BAD_CELL_OR_NAMED_RANGE: return ErrorEval.REF_INVALID; case NameType.NAMED_RANGE: IEvaluationName nm = ((IFormulaParsingWorkbook)_workbook).GetName(refStrPart1, _sheetIndex); if (!nm.IsRange) { throw new Exception("Specified name '" + refStrPart1 + "' is not a range as expected."); } return _bookEvaluator.EvaluateNameFormula(nm.NameDefinition, this); } if (refStrPart2 == null) { // no ':' switch (part1refType) { case NameType.COLUMN: case NameType.ROW: return ErrorEval.REF_INVALID; case NameType.CELL: CellReference cr = new CellReference(refStrPart1); return new LazyRefEval(cr.Row, cr.Col, sre); } throw new InvalidOperationException("Unexpected reference classification of '" + refStrPart1 + "'."); } NameType part2refType = ClassifyCellReference(refStrPart1, ssVersion); switch (part2refType) { case NameType.BAD_CELL_OR_NAMED_RANGE: return ErrorEval.REF_INVALID; case NameType.NAMED_RANGE: throw new Exception("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 NameType.COLUMN: firstRow = 0; lastRow = ssVersion.LastRowIndex; firstCol = ParseColRef(refStrPart1); lastCol = ParseColRef(refStrPart2); break; case NameType.ROW: firstCol = 0; lastCol = ssVersion.LastColumnIndex; firstRow = ParseRowRef(refStrPart1); lastRow = ParseRowRef(refStrPart2); break; case NameType.CELL: CellReference cr; cr = new CellReference(refStrPart1); firstRow = cr.Row; firstCol = cr.Col; cr = new CellReference(refStrPart2); lastRow = cr.Row; lastCol = cr.Col; break; default: throw new InvalidOperationException("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 ParseColRef(String refStrPart) { return Int32.Parse(refStrPart, CultureInfo.InvariantCulture) - 1; } private static NameType ClassifyCellReference(String str, SpreadsheetVersion ssVersion) { int len = str.Length; if (len < 1) { return NameType.BAD_CELL_OR_NAMED_RANGE; } return CellReference.ClassifyCellReference(str, ssVersion); } public FreeRefFunction FindUserDefinedFunction(String functionName) { return _bookEvaluator.FindUserDefinedFunction(functionName); } public ValueEval GetRefEval(int rowIndex, int columnIndex) { SheetRefEvaluator sre = GetRefEvaluatorForCurrentSheet(); return new LazyRefEval(rowIndex, columnIndex, sre); } public ValueEval GetRef3DEval(int rowIndex, int columnIndex, int extSheetIndex) { SheetRefEvaluator sre = CreateExternSheetRefEvaluator(extSheetIndex); return new LazyRefEval(rowIndex, columnIndex, sre); } public ValueEval GetAreaEval(int firstRowIndex, int firstColumnIndex, int lastRowIndex, int lastColumnIndex) { SheetRefEvaluator sre = GetRefEvaluatorForCurrentSheet(); return new LazyAreaEval(firstRowIndex, firstColumnIndex, lastRowIndex, lastColumnIndex, sre); } public ValueEval GetArea3DEval(int firstRowIndex, int firstColumnIndex, int lastRowIndex, int lastColumnIndex, int extSheetIndex) { SheetRefEvaluator sre = CreateExternSheetRefEvaluator(extSheetIndex); return new LazyAreaEval(firstRowIndex, firstColumnIndex, lastRowIndex, lastColumnIndex, sre); } public ValueEval GetNameXEval(NameXPtg nameXPtg) { ExternalSheet externSheet = _workbook.GetExternalSheet(nameXPtg.SheetRefIndex); if (externSheet == null) return new NameXEval(nameXPtg); String workbookName = externSheet.GetWorkbookName(); ExternalName externName = _workbook.GetExternalName( nameXPtg.SheetRefIndex, nameXPtg.NameIndex ); try { WorkbookEvaluator refWorkbookEvaluator = _bookEvaluator.GetOtherWorkbookEvaluator(workbookName); IEvaluationName evaluationName = refWorkbookEvaluator.GetName(externName.Name, externName.Ix - 1); if (evaluationName != null && evaluationName.HasFormula) { if (evaluationName.NameDefinition.Length > 1) { throw new Exception("Complex name formulas not supported yet"); } Ptg ptg = evaluationName.NameDefinition[0]; if (ptg is Ref3DPtg) { Ref3DPtg ref3D = (Ref3DPtg)ptg; int sheetIndex = refWorkbookEvaluator.GetSheetIndexByExternIndex(ref3D.ExternSheetIndex); String sheetName = refWorkbookEvaluator.GetSheetName(sheetIndex); SheetRefEvaluator sre = CreateExternSheetRefEvaluator(workbookName, sheetName); return new LazyRefEval(ref3D.Row, ref3D.Column, sre); } else if (ptg is Area3DPtg) { Area3DPtg area3D = (Area3DPtg)ptg; int sheetIndex = refWorkbookEvaluator.GetSheetIndexByExternIndex(area3D.ExternSheetIndex); String sheetName = refWorkbookEvaluator.GetSheetName(sheetIndex); SheetRefEvaluator sre = CreateExternSheetRefEvaluator(workbookName, sheetName); return new LazyAreaEval(area3D.FirstRow, area3D.FirstColumn, area3D.LastRow, area3D.LastColumn, sre); } } return ErrorEval.REF_INVALID; } catch (WorkbookNotFoundException) { return ErrorEval.REF_INVALID; } } } }