private static Ptg CreateDeletedRef(Ptg ptg)
{
if (ptg is RefPtg)
{
return(new RefErrorPtg());
}
if (ptg is Ref3DPtg)
{
Ref3DPtg rptg = (Ref3DPtg)ptg;
return(new DeletedRef3DPtg(rptg.ExternSheetIndex));
}
if (ptg is AreaPtg)
{
return(new AreaErrPtg());
}
if (ptg is Area3DPtg)
{
Area3DPtg area3DPtg = (Area3DPtg)ptg;
return(new DeletedArea3DPtg(area3DPtg.ExternSheetIndex));
}
if (ptg is Ref3DPxg)
{
Ref3DPxg pxg = (Ref3DPxg)ptg;
return(new Deleted3DPxg(pxg.ExternalWorkbookNumber, pxg.SheetName));
}
if (ptg is Area3DPxg)
{
Area3DPxg pxg = (Area3DPxg)ptg;
return(new Deleted3DPxg(pxg.ExternalWorkbookNumber, pxg.SheetName));
}
throw new ArgumentException("Unexpected ref ptg class (" + ptg.GetType().Name + ")");
}
public static HSSFSheet GetPtgSheet(HSSFSheet sheet, Ptg ptg)
{
if (ptg is RefPtg)
{
return(sheet);
}
if (ptg is Ref3DPtg)
{
Ref3DPtg ptg3 = (Ref3DPtg)ptg;
int idx = sheet.book.GetSheetIndexFromExternSheetIndex(ptg3.ExternSheetIndex);
if (idx == -1)
{
return(null);
}
return(sheet.Workbook.GetSheetAt(idx) as HSSFSheet);
}
if (ptg is Area2DPtgBase)
{
return(sheet);
}
if (ptg is Area3DPtg)
{
Area3DPtg aptg = (Area3DPtg)ptg;
int idx = sheet.book.GetSheetIndexFromExternSheetIndex(aptg.ExternSheetIndex);
if (idx == -1)
{
return(null);
}
return(sheet.Workbook.GetSheetAt(idx) as HSSFSheet);
}
return(null);
}
/**
* Call this on any ptg reference contained in a row of cells that was copied.
* If the ptg reference is relative, the references will be shifted by the distance
* that the rows were copied.
* In the future similar functions could be written due to column copying or
* individual cell copying. Just make sure to only call adjustPtgDueToRowCopy on
* formula cells that are copied (unless row shifting, where references outside
* of the shifted region need to be updated to reflect the shift, a copy is self-contained).
*
* @param ptg the ptg to shift
* @return deleted ref ptg, in-place modified ptg, or null
* If Ptg would be shifted off the first or last row of a sheet, return deleted ref
* If Ptg needs to be changed, modifies Ptg in-place
* If Ptg doesn't need to be changed, returns <code>null</code>
*/
private Ptg AdjustPtgDueToRowCopy(Ptg ptg)
{
if (ptg is RefPtg)
{
RefPtg rptg = (RefPtg)ptg;
return(RowCopyRefPtg(rptg));
}
if (ptg is Ref3DPtg)
{
Ref3DPtg rptg = (Ref3DPtg)ptg;
return(RowCopyRefPtg(rptg));
}
if (ptg is Ref3DPxg)
{
Ref3DPxg rpxg = (Ref3DPxg)ptg;
return(RowCopyRefPtg(rpxg));
}
if (ptg is Area2DPtgBase)
{
return(RowCopyAreaPtg((Area2DPtgBase)ptg));
}
if (ptg is Area3DPtg)
{
Area3DPtg aptg = (Area3DPtg)ptg;
return(RowCopyAreaPtg(aptg));
}
if (ptg is Area3DPxg)
{
Area3DPxg apxg = (Area3DPxg)ptg;
return(RowCopyAreaPtg(apxg));
}
return(null);
}
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);
}
}
public void TestToFormulaString()
{
Ref3DPtg target = new Ref3DPtg("A1", (short)0);
HSSFWorkbook wb = CreateWorkbookWithSheet("my sheet");
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(wb);
Assert.AreEqual("'my sheet'!A1", target.ToFormulaString(book));
wb.SetSheetName(0, "ProfitAndLoss");
Assert.AreEqual("ProfitAndLoss!A1", target.ToFormulaString(book));
wb.SetSheetName(0, "profit+loss");
Assert.AreEqual("'profit+loss'!A1", target.ToFormulaString(book));
}
// Fetch the workbook this refers to, and the name as defined with that
private ValueEval GetExternalNameXEval(ExternalName externName, string workbookName)
{
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");
}
// Need to Evaluate the reference in the context of the other book
OperationEvaluationContext refWorkbookContext = new OperationEvaluationContext(
refWorkbookEvaluator, refWorkbookEvaluator.Workbook, -1, -1, -1, _tracker);
Ptg ptg = evaluationName.NameDefinition[0];
if (ptg is Ref3DPtg)
{
Ref3DPtg ref3D = (Ref3DPtg)ptg;
return(refWorkbookContext.GetRef3DEval(ref3D));
}
else if (ptg is Ref3DPxg)
{
Ref3DPxg ref3D = (Ref3DPxg)ptg;
return(refWorkbookContext.GetRef3DEval(ref3D));
}
else if (ptg is Area3DPtg)
{
Area3DPtg area3D = (Area3DPtg)ptg;
return(refWorkbookContext.GetArea3DEval(area3D));
}
else if (ptg is Area3DPxg)
{
Area3DPxg area3D = (Area3DPxg)ptg;
return(refWorkbookContext.GetArea3DEval(area3D));
}
}
return(ErrorEval.REF_INVALID);
}
catch (WorkbookNotFoundException)
{
return(ErrorEval.REF_INVALID);
}
}
/**
* @return <c>true</c> if this Ptg needed to be changed
*/
private Ptg AdjustPtgDueToRowMove(Ptg ptg, int currentExternSheetIx)
{
if (ptg is RefPtg)
{
if (currentExternSheetIx != _externSheetIndex)
{
// local refs on other sheets are unaffected
return(null);
}
RefPtg rptg = (RefPtg)ptg;
return(RowMoveRefPtg(rptg));
}
if (ptg is Ref3DPtg)
{
Ref3DPtg rptg = (Ref3DPtg)ptg;
if (_externSheetIndex != rptg.ExternSheetIndex)
{
// only move 3D refs that refer to the sheet with cells being moved
// (currentExternSheetIx is irrelevant)
return(null);
}
return(RowMoveRefPtg(rptg));
}
if (ptg is Area2DPtgBase)
{
if (currentExternSheetIx != _externSheetIndex)
{
// local refs on other sheets are unaffected
return(ptg);
}
return(RowMoveAreaPtg((Area2DPtgBase)ptg));
}
if (ptg is Area3DPtg)
{
Area3DPtg aptg = (Area3DPtg)ptg;
if (_externSheetIndex != aptg.ExternSheetIndex)
{
// only move 3D refs that refer to the sheet with cells being moved
// (currentExternSheetIx is irrelevant)
return(null);
}
return(RowMoveAreaPtg(aptg));
}
return(null);
}
private Ptg AdjustPtgDueToSheetMove(Ptg ptg)
{
if (ptg is Ref3DPtg)
{
Ref3DPtg ref1 = (Ref3DPtg)ptg;
int oldSheetIndex = ref1.ExternSheetIndex;
// we have to handle a few cases here
// 1. sheet is outside moved sheets, no change necessary
if (oldSheetIndex < _srcSheetIndex &&
oldSheetIndex < _dstSheetIndex)
{
return(null);
}
if (oldSheetIndex > _srcSheetIndex &&
oldSheetIndex > _dstSheetIndex)
{
return(null);
}
// 2. ptg refers to the moved sheet
if (oldSheetIndex == _srcSheetIndex)
{
ref1.ExternSheetIndex = (_dstSheetIndex);
return(ref1);
}
// 3. new index is lower than old one => sheets get moved up
if (_dstSheetIndex < _srcSheetIndex)
{
ref1.ExternSheetIndex = (oldSheetIndex + 1);
return(ref1);
}
// 4. new index is higher than old one => sheets get moved down
if (_dstSheetIndex > _srcSheetIndex)
{
ref1.ExternSheetIndex = (oldSheetIndex - 1);
return(ref1);
}
}
return(null);
}
private Ptg AdjustPtgDueToShiftMove(Ptg ptg)
{
Ptg updatedPtg = null;
if (ptg is Ref3DPtg)
{
Ref3DPtg ref1 = (Ref3DPtg)ptg;
if (ref1.ExternSheetIndex == _srcSheetIndex)
{
ref1.ExternSheetIndex = (_dstSheetIndex);
updatedPtg = ref1;
}
else if (ref1.ExternSheetIndex == _dstSheetIndex)
{
ref1.ExternSheetIndex = (_srcSheetIndex);
updatedPtg = ref1;
}
}
return(updatedPtg);
}
public MockRefEval(Ref3DPtg ptg, ValueEval value)
: base(-1, -1, ptg.Row, ptg.Column)
{
_value = value;
}
/**
* returns an appropriate Eval impl instance for the Ptg. The Ptg must be
* one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg,
* StringPtg, BoolPtg <br/>special Note: OperationPtg subtypes cannot be
* passed here!
*/
private ValueEval GetEvalForPtg(Ptg ptg, OperationEvaluationContext ec)
{
// consider converting all these (ptg is XxxPtg) expressions To (ptg.GetType() == XxxPtg.class)
if (ptg is NamePtg)
{
// named ranges, macro functions
NamePtg namePtg = (NamePtg)ptg;
IEvaluationName nameRecord = _workbook.GetName(namePtg);
if (nameRecord.IsFunctionName)
{
return(new NameEval(nameRecord.NameText));
}
if (nameRecord.HasFormula)
{
return(EvaluateNameFormula(nameRecord.NameDefinition, ec));
}
throw new Exception("Don't now how To evalate name '" + nameRecord.NameText + "'");
}
if (ptg is NameXPtg)
{
return(ec.GetNameXEval(((NameXPtg)ptg)));
}
if (ptg is IntPtg)
{
return(new NumberEval(((IntPtg)ptg).Value));
}
if (ptg is NumberPtg)
{
return(new NumberEval(((NumberPtg)ptg).Value));
}
if (ptg is StringPtg)
{
return(new StringEval(((StringPtg)ptg).Value));
}
if (ptg is BoolPtg)
{
return(BoolEval.ValueOf(((BoolPtg)ptg).Value));
}
if (ptg is ErrPtg)
{
return(ErrorEval.ValueOf(((ErrPtg)ptg).ErrorCode));
}
if (ptg is MissingArgPtg)
{
return(MissingArgEval.instance);
}
if (ptg is AreaErrPtg || ptg is RefErrorPtg ||
ptg is DeletedArea3DPtg || ptg is DeletedRef3DPtg)
{
return(ErrorEval.REF_INVALID);
}
if (ptg is Ref3DPtg)
{
Ref3DPtg rptg = (Ref3DPtg)ptg;
return(ec.GetRef3DEval(rptg.Row, rptg.Column, rptg.ExternSheetIndex));
}
if (ptg is Area3DPtg)
{
Area3DPtg aptg = (Area3DPtg)ptg;
return(ec.GetArea3DEval(aptg.FirstRow, aptg.FirstColumn, aptg.LastRow, aptg.LastColumn, aptg.ExternSheetIndex));
}
if (ptg is RefPtg)
{
RefPtg rptg = (RefPtg)ptg;
return(ec.GetRefEval(rptg.Row, rptg.Column));
}
if (ptg is AreaPtg)
{
AreaPtg aptg = (AreaPtg)ptg;
return(ec.GetAreaEval(aptg.FirstRow, aptg.FirstColumn, aptg.LastRow, aptg.LastColumn));
}
if (ptg is UnknownPtg)
{
// POI uses UnknownPtg when the encoded Ptg array seems To be corrupted.
// This seems To occur in very rare cases (e.g. unused name formulas in bug 44774, attachment 21790)
// In any case, formulas are re-parsed before execution, so UnknownPtg should not Get here
throw new RuntimeException("UnknownPtg not allowed");
}
if (ptg is ExpPtg)
{
// ExpPtg is used for array formulas and shared formulas.
// it is currently unsupported, and may not even get implemented here
throw new RuntimeException("ExpPtg currently not supported");
}
throw new RuntimeException("Unexpected ptg class (" + ptg.GetType().Name + ")");
}
public ValueEval GetRef3DEval(Ref3DPtg rptg)
{
SheetRangeEvaluator sre = CreateExternSheetRefEvaluator(rptg.ExternSheetIndex);
return(new LazyRefEval(rptg.Row, rptg.Column, sre));
}
public LazyRefEval(Ref3DPtg ptg, SheetRefEvaluator sre)
: base(ptg.Row, ptg.Column)
{
_evaluator = sre;
}
public void TestFormulas()
{
FormulaRecord[] fRecs = mockListen.GetFormulaRecords();
// Check our formula records
Assert.AreEqual(6, fRecs.Length);
InternalWorkbook stubWB = listener.GetStubWorkbook();
Assert.IsNotNull(stubWB);
HSSFWorkbook stubHSSF = listener.GetStubHSSFWorkbook();
Assert.IsNotNull(stubHSSF);
// Check these stubs have the right stuff on them
Assert.AreEqual("Sheet1", stubWB.GetSheetName(0));
Assert.AreEqual("S2", stubWB.GetSheetName(1));
Assert.AreEqual("Sh3", stubWB.GetSheetName(2));
// Check we can Get the formula without breaking
for (int i = 0; i < fRecs.Length; i++)
{
HSSFFormulaParser.ToFormulaString(stubHSSF, fRecs[i].ParsedExpression);
}
// Peer into just one formula, and check that
// all the ptgs give back the right things
Ptg[] ptgs = fRecs[0].ParsedExpression;
Assert.AreEqual(1, ptgs.Length);
Assert.IsTrue(ptgs[0] is Ref3DPtg);
Ref3DPtg ptg = (Ref3DPtg)ptgs[0];
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(stubHSSF);
Assert.AreEqual("Sheet1!A1", ptg.ToFormulaString(book));
// Now check we Get the right formula back for
// a few sample ones
FormulaRecord fr;
// Sheet 1 A2 is on same sheet
fr = fRecs[0];
Assert.AreEqual(1, fr.Row);
Assert.AreEqual(0, fr.Column);
Assert.AreEqual("Sheet1!A1", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression));
// Sheet 1 A5 is to another sheet
fr = fRecs[3];
Assert.AreEqual(4, fr.Row);
Assert.AreEqual(0, fr.Column);
Assert.AreEqual("'S2'!A1", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression));
// Sheet 1 A7 is to another sheet, range
fr = fRecs[5];
Assert.AreEqual(6, fr.Row);
Assert.AreEqual(0, fr.Column);
Assert.AreEqual("SUM(Sh3!A1:A4)", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression));
// Now, load via Usermodel and re-check
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("3dFormulas.xls");
Assert.AreEqual("Sheet1!A1", wb.GetSheetAt(0).GetRow(1).GetCell(0).CellFormula);
Assert.AreEqual("SUM(Sh3!A1:A4)", wb.GetSheetAt(0).GetRow(6).GetCell(0).CellFormula);
}
