public String ResolveNameXText(int refIndex, int definedNameIndex, InternalWorkbook workbook)
{
int extBookIndex = _externSheetRecord.GetExtbookIndexFromRefIndex(refIndex);
int firstTabIndex = _externSheetRecord.GetFirstSheetIndexFromRefIndex(refIndex);
if (firstTabIndex == -1)
{
// The referenced sheet could not be found
throw new RuntimeException("Referenced sheet could not be found");
}
// Does it exist via the external book block?
ExternalBookBlock externalBook = _externalBookBlocks[extBookIndex];
if (externalBook._externalNameRecords.Length > definedNameIndex)
{
return(_externalBookBlocks[extBookIndex].GetNameText(definedNameIndex));
}
else if (firstTabIndex == -2)
{
// Workbook scoped name, not actually external after all
NameRecord nr = GetNameRecord(definedNameIndex);
int sheetNumber = nr.SheetNumber;
StringBuilder text = new StringBuilder();
if (sheetNumber > 0)
{
String sheetName = workbook.GetSheetName(sheetNumber - 1);
SheetNameFormatter.AppendFormat(text, sheetName);
text.Append("!");
}
text.Append(nr.NameText);
return(text.ToString());
}
else
{
throw new IndexOutOfRangeException(
"Ext Book Index relative but beyond the supported length, was " +
extBookIndex + " but maximum is " + _externalBookBlocks.Length
);
}
}
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);
}