/// <summary>
/// Creates a non shared formula from the shared formula counterpart
/// </summary>
/// <param name="si">Shared Group Index</param>
/// <returns>non shared formula created for the given shared formula and this cell</returns>
private string ConvertSharedFormula(int si)
{
XSSFSheet sheet = (XSSFSheet)Sheet;
CT_CellFormula f = sheet.GetSharedFormula(si);
if (f == null)
{
throw new InvalidOperationException(
"Master cell of a shared formula with sid=" + si + " was not found");
}
String sharedFormula = f.Value;
//Range of cells which the shared formula applies to
String sharedFormulaRange = f.@ref;
CellRangeAddress ref1 = CellRangeAddress.ValueOf(sharedFormulaRange);
int sheetIndex = sheet.Workbook.GetSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(sheet.Workbook);
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);
Ptg[] ptgs = FormulaParser.Parse(sharedFormula, fpb, FormulaType.Cell, sheetIndex);
Ptg[] fmla = sf.ConvertSharedFormulas(ptgs,
RowIndex - ref1.FirstRow, ColumnIndex - ref1.FirstColumn);
return(FormulaRenderer.ToFormulaString(fpb, fmla));
}
private void SetFormula(string formula, FormulaType formulaType)
{
IWorkbook wb = _row.Sheet.Workbook;
if (formula == null)
{
((XSSFWorkbook)wb).OnDeleteFormula(this);
if (_cell.IsSetF())
{
_cell.unsetF();
}
return;
}
IFormulaParsingWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
//validate through the FormulaParser
FormulaParser.Parse(formula, fpb, formulaType, wb.GetSheetIndex(this.Sheet));
CT_CellFormula f = new CT_CellFormula();
f.Value = formula;
_cell.f = (f);
if (_cell.IsSetV())
{
_cell.unsetV();
}
}
public Ptg[] GetFormulaTokens(IEvaluationCell EvalCell)
{
XSSFCell cell = ((XSSFEvaluationCell)EvalCell).GetXSSFCell();
XSSFEvaluationWorkbook frBook = XSSFEvaluationWorkbook.Create(_uBook);
return(FormulaParser.Parse(cell.CellFormula, frBook, FormulaType.Cell, _uBook.GetSheetIndex(cell.Sheet)));
}
public void FormulaReferencesOtherWorkbook()
{
// Use a test file with the external linked table in place
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("ref-56737.xlsx");
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
Ptg[] ptgs;
// Reference to a single cell in a different workbook
ptgs = Parse(fpb, "[1]Uses!$A$1");
Assert.AreEqual(1, ptgs.Length);
Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType());
Assert.AreEqual(1, ((Ref3DPxg)ptgs[0]).ExternalWorkbookNumber);
Assert.AreEqual("Uses", ((Ref3DPxg)ptgs[0]).SheetName);
Assert.AreEqual("$A$1", ((Ref3DPxg)ptgs[0]).Format2DRefAsString());
Assert.AreEqual("[1]Uses!$A$1", ((Ref3DPxg)ptgs[0]).ToFormulaString());
// Reference to a sheet-scoped named range in a different workbook
ptgs = Parse(fpb, "[1]Defines!NR_To_A1");
Assert.AreEqual(1, ptgs.Length);
Assert.AreEqual(typeof(NameXPxg), ptgs[0].GetType());
Assert.AreEqual(1, ((NameXPxg)ptgs[0]).ExternalWorkbookNumber);
Assert.AreEqual("Defines", ((NameXPxg)ptgs[0]).SheetName);
Assert.AreEqual("NR_To_A1", ((NameXPxg)ptgs[0]).NameName);
Assert.AreEqual("[1]Defines!NR_To_A1", ((NameXPxg)ptgs[0]).ToFormulaString());
// Reference to a global named range in a different workbook
ptgs = Parse(fpb, "[1]!NR_Global_B2");
Assert.AreEqual(1, ptgs.Length);
Assert.AreEqual(typeof(NameXPxg), ptgs[0].GetType());
Assert.AreEqual(1, ((NameXPxg)ptgs[0]).ExternalWorkbookNumber);
Assert.AreEqual(null, ((NameXPxg)ptgs[0]).SheetName);
Assert.AreEqual("NR_Global_B2", ((NameXPxg)ptgs[0]).NameName);
Assert.AreEqual("[1]!NR_Global_B2", ((NameXPxg)ptgs[0]).ToFormulaString());
}
public void TestBuiltInFormulas()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
Ptg[] ptgs;
ptgs = Parse(fpb, "LOG10");
Assert.AreEqual(1, ptgs.Length);
Assert.IsTrue((ptgs[0] is RefPtg));
ptgs = Parse(fpb, "LOG10(100)");
Assert.AreEqual(2, ptgs.Length);
Assert.IsTrue(ptgs[0] is IntPtg);
Assert.IsTrue(ptgs[1] is FuncPtg);
}
public void FormaulReferncesSameWorkbook()
{
// Use a test file with "other workbook" style references
// to itself
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56737.xlsx");
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
Ptg[] ptgs;
// Reference to a named range in our own workbook, as if it
// were defined in a different workbook
ptgs = Parse(fpb, "[0]!NR_Global_B2");
Assert.AreEqual(1, ptgs.Length);
Assert.AreEqual(typeof(NameXPxg), ptgs[0].GetType());
Assert.AreEqual(0, ((NameXPxg)ptgs[0]).ExternalWorkbookNumber);
Assert.AreEqual(null, ((NameXPxg)ptgs[0]).SheetName);
Assert.AreEqual("NR_Global_B2", ((NameXPxg)ptgs[0]).NameName);
Assert.AreEqual("[0]!NR_Global_B2", ((NameXPxg)ptgs[0]).ToFormulaString());
}
public void FormulaReferencesOtherSheets()
{
// Use a test file with the named ranges in place
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56737.xlsx");
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
Ptg[] ptgs;
// Reference to a single cell in a different sheet
ptgs = Parse(fpb, "Uses!A1");
Assert.AreEqual(1, ptgs.Length);
Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType());
Assert.AreEqual(-1, ((Ref3DPxg)ptgs[0]).ExternalWorkbookNumber);
Assert.AreEqual("A1", ((Ref3DPxg)ptgs[0]).Format2DRefAsString());
Assert.AreEqual("Uses!A1", ((Ref3DPxg)ptgs[0]).ToFormulaString());
// Reference to a single cell in a different sheet, which needs quoting
ptgs = Parse(fpb, "'Testing 47100'!A1");
Assert.AreEqual(1, ptgs.Length);
Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType());
Assert.AreEqual(-1, ((Ref3DPxg)ptgs[0]).ExternalWorkbookNumber);
Assert.AreEqual("Testing 47100", ((Ref3DPxg)ptgs[0]).SheetName);
Assert.AreEqual("A1", ((Ref3DPxg)ptgs[0]).Format2DRefAsString());
Assert.AreEqual("'Testing 47100'!A1", ((Ref3DPxg)ptgs[0]).ToFormulaString());
// Reference to a sheet scoped named range from another sheet
ptgs = Parse(fpb, "Defines!NR_To_A1");
Assert.AreEqual(1, ptgs.Length);
Assert.AreEqual(typeof(NameXPxg), ptgs[0].GetType());
Assert.AreEqual(-1, ((NameXPxg)ptgs[0]).ExternalWorkbookNumber);
Assert.AreEqual("Defines", ((NameXPxg)ptgs[0]).SheetName);
Assert.AreEqual("NR_To_A1", ((NameXPxg)ptgs[0]).NameName);
Assert.AreEqual("Defines!NR_To_A1", ((NameXPxg)ptgs[0]).ToFormulaString());
// Reference to a workbook scoped named range
ptgs = Parse(fpb, "NR_Global_B2");
Assert.AreEqual(1, ptgs.Length);
Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
Assert.AreEqual("NR_Global_B2", ((NamePtg)ptgs[0]).ToFormulaString(fpb));
}
public void BasicParse()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
Ptg[] ptgs;
ptgs = Parse(fpb, "ABC10");
Assert.AreEqual(1, ptgs.Length);
Assert.IsTrue(ptgs[0] is RefPtg);
ptgs = Parse(fpb, "A500000");
Assert.AreEqual(1, ptgs.Length);
Assert.IsTrue(ptgs[0] is RefPtg);
ptgs = Parse(fpb, "ABC500000");
Assert.AreEqual(1, ptgs.Length);
Assert.IsTrue(ptgs[0] is RefPtg);
//highest allowed rows and column (XFD and 0x100000)
ptgs = Parse(fpb, "XFD1048576");
Assert.AreEqual(1, ptgs.Length);
Assert.IsTrue(ptgs[0] is RefPtg);
//column greater than XFD
try
{
ptgs = Parse(fpb, "XFE10");
Assert.Fail("expected exception");
}
catch (FormulaParseException e)
{
Assert.AreEqual("Specified named range 'XFE10' does not exist in the current workbook.", e.Message);
}
//row greater than 0x100000
try
{
ptgs = Parse(fpb, "XFD1048577");
Assert.Fail("expected exception");
}
catch (FormulaParseException e)
{
Assert.AreEqual("Specified named range 'XFD1048577' does not exist in the current workbook.", e.Message);
}
// Formula referencing one cell
ptgs = Parse(fpb, "ISEVEN(A1)");
Assert.AreEqual(3, ptgs.Length);
Assert.AreEqual(typeof(NameXPxg), ptgs[0].GetType());
Assert.AreEqual(typeof(RefPtg), ptgs[1].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ptgs[2].GetType());
Assert.AreEqual("ISEVEN", ptgs[0].ToFormulaString());
Assert.AreEqual("A1", ptgs[1].ToFormulaString());
Assert.AreEqual("#external#", ptgs[2].ToFormulaString());
// Formula referencing an area
ptgs = Parse(fpb, "SUM(A1:B3)");
Assert.AreEqual(2, ptgs.Length);
Assert.AreEqual(typeof(AreaPtg), ptgs[0].GetType());
Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
Assert.AreEqual("A1:B3", ptgs[0].ToFormulaString());
Assert.AreEqual("SUM", ptgs[1].ToFormulaString());
// Formula referencing one cell in a different sheet
ptgs = Parse(fpb, "SUM(Sheet1!A1)");
Assert.AreEqual(2, ptgs.Length);
Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType());
Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
Assert.AreEqual("Sheet1!A1", ptgs[0].ToFormulaString());
Assert.AreEqual("SUM", ptgs[1].ToFormulaString());
// Formula referencing an area in a different sheet
ptgs = Parse(fpb, "SUM(Sheet1!A1:B3)");
Assert.AreEqual(2, ptgs.Length);
Assert.AreEqual(typeof(Area3DPxg), ptgs[0].GetType());
Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
Assert.AreEqual("Sheet1!A1:B3", ptgs[0].ToFormulaString());
Assert.AreEqual("SUM", ptgs[1].ToFormulaString());
}
public void MultiSheetReferencesHSSFandXSSF()
{
IWorkbook[] wbs = new IWorkbook[] {
HSSFTestDataSamples.OpenSampleWorkbook("55906-MultiSheetRefs.xls"),
XSSFTestDataSamples.OpenSampleWorkbook("55906-MultiSheetRefs.xlsx")
};
foreach (IWorkbook wb in wbs)
{
ISheet s1 = wb.GetSheetAt(0);
Ptg[] ptgs;
// Check the contents
ICell sumF = s1.GetRow(2).GetCell(0);
Assert.IsNotNull(sumF);
Assert.AreEqual("SUM(Sheet1:Sheet3!A1)", sumF.CellFormula);
ICell avgF = s1.GetRow(2).GetCell(1);
Assert.IsNotNull(avgF);
Assert.AreEqual("AVERAGE(Sheet1:Sheet3!A1)", avgF.CellFormula);
ICell countAF = s1.GetRow(2).GetCell(2);
Assert.IsNotNull(countAF);
Assert.AreEqual("COUNTA(Sheet1:Sheet3!C1)", countAF.CellFormula);
ICell maxF = s1.GetRow(4).GetCell(1);
Assert.IsNotNull(maxF);
Assert.AreEqual("MAX(Sheet1:Sheet3!A$1)", maxF.CellFormula);
ICell sumFA = s1.GetRow(2).GetCell(7);
Assert.IsNotNull(sumFA);
Assert.AreEqual("SUM(Sheet1:Sheet3!A1:B2)", sumFA.CellFormula);
ICell avgFA = s1.GetRow(2).GetCell(8);
Assert.IsNotNull(avgFA);
Assert.AreEqual("AVERAGE(Sheet1:Sheet3!A1:B2)", avgFA.CellFormula);
ICell maxFA = s1.GetRow(4).GetCell(8);
Assert.IsNotNull(maxFA);
Assert.AreEqual("MAX(Sheet1:Sheet3!A$1:B$2)", maxFA.CellFormula);
ICell countFA = s1.GetRow(5).GetCell(8);
Assert.IsNotNull(countFA);
Assert.AreEqual("COUNT(Sheet1:Sheet3!$A$1:$B$2)", countFA.CellFormula);
// Create a formula Parser
IFormulaParsingWorkbook fpb = null;
if (wb is HSSFWorkbook)
{
fpb = HSSFEvaluationWorkbook.Create((HSSFWorkbook)wb);
}
else
{
fpb = XSSFEvaluationWorkbook.Create((XSSFWorkbook)wb);
}
// Check things parse as expected:
// SUM to one cell over 3 workbooks, relative reference
ptgs = Parse(fpb, "SUM(Sheet1:Sheet3!A1)");
Assert.AreEqual(2, ptgs.Length);
if (wb is HSSFWorkbook)
{
Assert.AreEqual(typeof(Ref3DPtg), ptgs[0].GetType());
}
else
{
Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType());
}
Assert.AreEqual("Sheet1:Sheet3!A1", ToFormulaString(ptgs[0], fpb));
Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
Assert.AreEqual("SUM", ToFormulaString(ptgs[1], fpb));
// MAX to one cell over 3 workbooks, absolute row reference
ptgs = Parse(fpb, "MAX(Sheet1:Sheet3!A$1)");
Assert.AreEqual(2, ptgs.Length);
if (wb is HSSFWorkbook)
{
Assert.AreEqual(typeof(Ref3DPtg), ptgs[0].GetType());
}
else
{
Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType());
}
Assert.AreEqual("Sheet1:Sheet3!A$1", ToFormulaString(ptgs[0], fpb));
Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());
Assert.AreEqual("MAX", ToFormulaString(ptgs[1], fpb));
// MIN to one cell over 3 workbooks, absolute reference
ptgs = Parse(fpb, "MIN(Sheet1:Sheet3!$A$1)");
Assert.AreEqual(2, ptgs.Length);
if (wb is HSSFWorkbook)
{
Assert.AreEqual(typeof(Ref3DPtg), ptgs[0].GetType());
}
else
{
Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType());
}
Assert.AreEqual("Sheet1:Sheet3!$A$1", ToFormulaString(ptgs[0], fpb));
Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());
Assert.AreEqual("MIN", ToFormulaString(ptgs[1], fpb));
// SUM to a range of cells over 3 workbooks
ptgs = Parse(fpb, "SUM(Sheet1:Sheet3!A1:B2)");
Assert.AreEqual(2, ptgs.Length);
if (wb is HSSFWorkbook)
{
Assert.AreEqual(typeof(Area3DPtg), ptgs[0].GetType());
}
else
{
Assert.AreEqual(typeof(Area3DPxg), ptgs[0].GetType());
}
Assert.AreEqual(ToFormulaString(ptgs[0], fpb), "Sheet1:Sheet3!A1:B2");
Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
Assert.AreEqual("SUM", ToFormulaString(ptgs[1], fpb));
// MIN to a range of cells over 3 workbooks, absolute reference
ptgs = Parse(fpb, "MIN(Sheet1:Sheet3!$A$1:$B$2)");
Assert.AreEqual(2, ptgs.Length);
if (wb is HSSFWorkbook)
{
Assert.AreEqual(typeof(Area3DPtg), ptgs[0].GetType());
}
else
{
Assert.AreEqual(typeof(Area3DPxg), ptgs[0].GetType());
}
Assert.AreEqual(ToFormulaString(ptgs[0], fpb), "Sheet1:Sheet3!$A$1:$B$2");
Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());
Assert.AreEqual("MIN", ToFormulaString(ptgs[1], fpb));
// Check we can round-trip - try to Set a new one to a new single cell
ICell newF = s1.GetRow(0).CreateCell(10, CellType.Formula);
newF.CellFormula = (/*setter*/ "SUM(Sheet2:Sheet3!A1)");
Assert.AreEqual("SUM(Sheet2:Sheet3!A1)", newF.CellFormula);
// Check we can round-trip - try to Set a new one to a cell range
newF = s1.GetRow(0).CreateCell(11, CellType.Formula);
newF.CellFormula = (/*setter*/ "MIN(Sheet1:Sheet2!A1:B2)");
Assert.AreEqual("MIN(Sheet1:Sheet2!A1:B2)", newF.CellFormula);
}
}
private XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)
{
_bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(workbook), stabilityClassifier, udfFinder);
_book = workbook;
}
/**
* @param stabilityClassifier used to optimise caching performance. Pass <code>null</code>
* for the (conservative) assumption that any cell may have its defInition Changed After
* Evaluation begins.
* @deprecated (Sep 2009) (reduce overloading) use {@link #Create(XSSFWorkbook, Npoi.Core.ss.formula.IStabilityClassifier, Npoi.Core.ss.formula.udf.UDFFinder)}
*/
public XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier)
{
_bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(workbook), stabilityClassifier, null);
_book = workbook;
}
