private void SetFormula(string formula, FormulaType formulaType)
{
IWorkbook workbook1 = this._row.Sheet.Workbook;
if (formula == null)
{
((XSSFWorkbook)workbook1).OnDeleteFormula(this);
if (!this._cell.IsSetF())
{
return;
}
this._cell.unsetF();
}
else
{
IFormulaParsingWorkbook workbook2 = (IFormulaParsingWorkbook)XSSFEvaluationWorkbook.Create(workbook1);
FormulaParser.Parse(formula, workbook2, formulaType, workbook1.GetSheetIndex(this.Sheet));
this._cell.f = new CT_CellFormula()
{
Value = formula
};
if (!this._cell.IsSetV())
{
return;
}
this._cell.unsetV();
}
}
private void SetFormula(String formula, FormulaType formulaType)
{
XSSFWorkbook wb = (XSSFWorkbook)_row.Sheet.Workbook;
if (formula == null)
{
RemoveFormula();
return;
}
if (wb.CellFormulaValidation)
{
IFormulaParsingWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
//validate through the FormulaParser
FormulaParser.Parse(formula, fpb, formulaType, wb.GetSheetIndex(this.Sheet), RowIndex);
}
CT_CellFormula f = new CT_CellFormula();
f.Value = formula;
_cell.f = (f);
if (_cell.IsSetV())
{
_cell.unsetV();
}
}
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), -1);
CT_CellFormula f = new CT_CellFormula();
f.Value = formula;
_cell.f = (f);
if (_cell.IsSetV())
{
_cell.unsetV();
}
}
private static string ToFormulaString(Ptg ptg, IFormulaParsingWorkbook wb)
{
if (ptg is WorkbookDependentFormula)
{
return(((WorkbookDependentFormula)ptg).ToFormulaString((IFormulaRenderingWorkbook)wb));
}
return(ptg.ToFormulaString());
}
/** confirm formula has invalid syntax and parsing the formula results in FormulaParseException
* @param formula
* @param wb
*/
private static void parseExpectedException(String formula, IFormulaParsingWorkbook wb)
{
try
{
FormulaParser.Parse(formula, wb, FormulaType.Cell, -1);
Assert.Fail("Expected FormulaParseException: " + formula);
}
catch (FormulaParseException e)
{
// expected during successful test
Assert.IsNotNull(e.Message);
}
}
public void TestXSSFFailCase()
{
IFormulaParsingWorkbook workbook = XSSFEvaluationWorkbook.Create(new XSSFWorkbook());
try
{
FormulaParser.Parse("Sheet1!1:1048577", workbook, FormulaType.Cell, 0); // one more than max rows.
Assert.Fail("Expected exception");
}
catch (FormulaParseException)
{
}
}
public void TestHSSFFailsForOver65536()
{
IFormulaParsingWorkbook workbook = HSSFEvaluationWorkbook.Create(new HSSFWorkbook());
try
{
FormulaParser.Parse("Sheet1!1:65537", workbook, FormulaType.Cell, 0);
Assert.Fail("Expected exception");
}
catch (FormulaParseException)
{
}
}
/**
* Parse a formula into a array of tokens
*
* @param formula the formula to parse
* @param workbook the parent workbook
* @param formulaType the type of the formula, see {@link FormulaType}
* @param sheetIndex the 0-based index of the sheet this formula belongs to.
* The sheet index is required to resolve sheet-level names. <code>-1</code> means that
* the scope of the name will be ignored and the parser will match names only by name
*
* @return array of parsed tokens
* @throws FormulaParseException if the formula is unparsable
*/
public static Ptg[] Parse(String formula, IFormulaParsingWorkbook workbook, FormulaType formulaType, int sheetIndex)
{
FormulaParser fp = new FormulaParser(formula, workbook, sheetIndex);
fp.Parse();
return fp.GetRPNPtg(formulaType);
}
public static Ptg[] Parse(String formula, IFormulaParsingWorkbook book)
{
return Parse(formula, book, FormulaType.CELL);
}
/**
* Create the formula Parser, with the string that is To be
* Parsed against the supplied workbook.
* A later call the Parse() method To return ptg list in
* rpn order, then call the GetRPNPtg() To retrive the
* Parse results.
* This class is recommended only for single threaded use.
*
* If you only have a usermodel.HSSFWorkbook, and not a
* model.Workbook, then use the convenience method on
* usermodel.HSSFFormulaEvaluator
*/
public FormulaParser(String formula, IFormulaParsingWorkbook book, int sheetIndex)
{
formulaString = formula;
pointer = 0;
this._book = book;
_ssVersion = book == null ? SpreadsheetVersion.EXCEL97 : book.GetSpreadsheetVersion();
formulaLength = formulaString.Length;
_sheetIndex = sheetIndex;
}
private static String ToFormulaString(Ptg ptg, IFormulaParsingWorkbook wb)
{
if (ptg is WorkbookDependentFormula)
{
return ((WorkbookDependentFormula)ptg).ToFormulaString((IFormulaRenderingWorkbook)wb);
}
return ptg.ToFormulaString();
}
private static Ptg[] Parse(IFormulaParsingWorkbook fpb, String fmla)
{
return FormulaParser.Parse(fmla, fpb, FormulaType.Cell, -1);
}
public void TestXSSFWorksForOver65536()
{
IFormulaParsingWorkbook workbook = XSSFEvaluationWorkbook.Create(new XSSFWorkbook());
FormulaParser.Parse("Sheet1!1:65537", workbook, FormulaType.Cell, 0);
}
public void TestHSSFPassCase()
{
IFormulaParsingWorkbook workbook = HSSFEvaluationWorkbook.Create(new HSSFWorkbook());
FormulaParser.Parse("Sheet1!1:65536", workbook, FormulaType.Cell, 0);
}
public static Ptg[] Parse(String formula, IFormulaParsingWorkbook workbook, FormulaType formulaType)
{
return Parse(formula, workbook, formulaType, -1);
}
public Name(IName name, int index, IFormulaParsingWorkbook fpBook)
{
_nameRecord = (XSSFName)name;
_index = index;
_fpBook = fpBook;
}
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);
}
}
public Name(IName name, int index, IFormulaParsingWorkbook fpBook)
{
_nameRecord = (XSSFName)name;
_index = index;
_fpBook = fpBook;
}
private static Ptg[] Parse(IFormulaParsingWorkbook fpb, String fmla)
{
return(FormulaParser.Parse(fmla, fpb, FormulaType.Cell, -1));
}
