public void ParserShouldCallLexer()
{
var lexer = MockRepository.GenerateStub <ILexer>();
lexer.Stub(x => x.Tokenize("ABC")).Return(Enumerable.Empty <Token>());
_parser.Configure(x => x.SetLexer(lexer));
_parser.Parse("ABC");
lexer.AssertWasCalled(x => x.Tokenize("ABC"));
}
public void ParserShouldCallLexer()
{
var lexer = A.Fake <ILexer>();
A.CallTo(() => lexer.Tokenize("ABC")).Returns(Enumerable.Empty <Token>());
_parser.Configure(x => x.SetLexer(lexer));
_parser.Parse("ABC");
A.CallTo(() => lexer.Tokenize("ABC")).MustHaveHappened();
}
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)));
}
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 UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f == null)
{
return;
}
string formula = f.Value;
if (formula == null || formula.Length <= 0)
{
return;
}
int sheetIndex = this._wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, (IFormulaParsingWorkbook)this._fpwb, FormulaType.CELL, sheetIndex);
string formulaString = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (formula.Equals(formulaString))
{
return;
}
f.Value = formulaString;
}
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();
}
}
/**
* Evaluate a formula outside a cell value, e.g. conditional format rules or data validation expressions
*
* @param formula to evaluate
* @param ref defines the optional sheet and row/column base for the formula, if it is relative
* @return value
*/
public ValueEval Evaluate(String formula, CellReference reference)
{
String sheetName = reference == null ? null : reference.SheetName;
int sheetIndex;
if (sheetName == null)
{
sheetIndex = -1; // workbook scope only
}
else
{
sheetIndex = Workbook.GetSheetIndex(sheetName);
}
int rowIndex = reference == null ? -1 : reference.Row;
int colIndex = reference == null ? -1 : reference.Col;
OperationEvaluationContext ec = new OperationEvaluationContext(
this,
Workbook,
sheetIndex,
rowIndex,
colIndex,
new EvaluationTracker(_cache)
);
Ptg[] ptgs = FormulaParser.Parse(formula, (IFormulaParsingWorkbook)Workbook, FormulaType.Cell, sheetIndex, rowIndex);
return(EvaluateNameFormula(ptgs, ec));
}
public Ptg[] GetFormulaTokens(IEvaluationCell EvalCell)
{
XSSFCell xssfCell = ((XSSFEvaluationCell)EvalCell).GetXSSFCell();
XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create((IWorkbook)this._uBook);
return(FormulaParser.Parse(xssfCell.CellFormula, (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.CELL, this._uBook.GetSheetIndex(xssfCell.Sheet)));
}
// copied from org.apache.poi.hssf.model.TestFormulaParser
public void TestMacroFunction()
{
// testNames.xlsm contains a VB function called 'myFunc'
String testFile = "testNames.xlsm";
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook(testFile);
try
{
XSSFEvaluationWorkbook workbook = XSSFEvaluationWorkbook.Create(wb);
//Expected ptg stack: [NamePtg(myFunc), StringPtg(arg), (additional operands would go here...), FunctionPtg(myFunc)]
Ptg[] ptg = FormulaParser.Parse("myFunc(\"arg\")", workbook, FormulaType.Cell, -1);
Assert.AreEqual(3, ptg.Length);
// the name gets encoded as the first operand on the stack
NameXPxg tname = (NameXPxg)ptg[0];
Assert.AreEqual("myFunc", tname.ToFormulaString());
// the function's arguments are pushed onto the stack from left-to-right as OperandPtgs
StringPtg arg = (StringPtg)ptg[1];
Assert.AreEqual("arg", arg.Value);
// The external FunctionPtg is the last Ptg added to the stack
// During formula evaluation, this Ptg pops off the the appropriate number of
// arguments (getNumberOfOperands()) and pushes the result on the stack
AbstractFunctionPtg tfunc = (AbstractFunctionPtg)ptg[2];
Assert.IsTrue(tfunc.IsExternalFunction);
// confirm formula parsing is case-insensitive
FormulaParser.Parse("mYfUnC(\"arg\")", workbook, FormulaType.Cell, -1);
// confirm formula parsing doesn't care about argument count or type
// this should only throw an error when evaluating the formula.
FormulaParser.Parse("myFunc()", workbook, FormulaType.Cell, -1);
FormulaParser.Parse("myFunc(\"arg\", 0, TRUE)", workbook, FormulaType.Cell, -1);
// A completely unknown formula name (not saved in workbook) should still be parseable and renderable
// but will throw an NotImplementedFunctionException or return a #NAME? error value if evaluated.
FormulaParser.Parse("yourFunc(\"arg\")", workbook, FormulaType.Cell, -1);
// Make sure workbook can be written and read
XSSFTestDataSamples.WriteOutAndReadBack(wb).Close();
// Manually check to make sure file isn't corrupted
// TODO: develop a process for occasionally manually reviewing workbooks
// to verify workbooks are not corrupted
/*
* FileInfo fileIn = XSSFTestDataSamples.GetSampleFile(testFile);
* FileInfo reSavedFile = new FileInfo(fileIn.FullName.Replace(".xlsm", "-saved.xlsm"));
* FileStream fos = new FileStream(reSavedFile.FullName, FileMode.Create, FileAccess.ReadWrite);
* wb.Write(fos);
* fos.Close();
*/
}
finally
{
wb.Close();
}
}
/// <summary>
/// Creates a non shared formula from the shared formula counterpart
/// </summary>
/// <param name="si">Shared Group Index</param>
/// <param name="fpb"></param>
/// <returns>non shared formula created for the given shared formula and this cell</returns>
private String ConvertSharedFormula(int si, XSSFEvaluationWorkbook fpb)
{
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);
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);
Ptg[] ptgs = FormulaParser.Parse(sharedFormula, fpb, FormulaType.Cell, sheetIndex, RowIndex);
Ptg[] fmla = sf.ConvertSharedFormulas(ptgs,
RowIndex - ref1.FirstRow, ColumnIndex - ref1.FirstColumn);
return(FormulaRenderer.ToFormulaString(fpb, fmla));
}
/// <summary>
/// Shift a formula using the supplied FormulaShifter
/// </summary>
/// <param name="row">the row of the cell this formula belongs to. Used to get a reference to the parent workbook.</param>
/// <param name="formula">the formula to shift</param>
/// <param name="formulaShifter">the FormulaShifter object that operates on the parsed formula tokens</param>
/// <returns>the shifted formula if the formula was changed, null if the formula wasn't modified</returns>
public static String ShiftFormula(IRow row, String formula, FormulaShifter formulaShifter)
{
ISheet sheet = row.Sheet;
IWorkbook wb = sheet.Workbook;
int sheetIndex = wb.GetSheetIndex(sheet);
int rowIndex = row.RowNum;
HSSFEvaluationWorkbook fpb = HSSFEvaluationWorkbook.Create((HSSFWorkbook)wb);
try
{
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex, rowIndex);
String shiftedFmla;
if (formulaShifter.AdjustFormula(ptgs, sheetIndex))
{
shiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
}
else
{
shiftedFmla = formula;
}
return(shiftedFmla);
}
catch (FormulaParseException fpe)
{
// Log, but don't change, rather than breaking
log.Log(POILogger.ERROR, "Error shifting formula on row " + row.RowNum.ToString(), fpe);
return(formula);
}
}
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();
}
}
public void TestRead()
{
String hex =
"21 02 25 00 01 00 01 00 01 01 00 00 00 00 00 00 " +
"17 00 65 00 00 01 00 02 C0 02 C0 65 00 00 01 00 " +
"03 C0 03 C0 04 62 01 07 00";
byte[] data = HexRead.ReadFromString(hex);
RecordInputStream in1 = TestcaseRecordInputStream.Create(data);
ArrayRecord r1 = new ArrayRecord(in1);
CellRangeAddress8Bit range = r1.Range;
Assert.AreEqual(1, range.FirstColumn);
Assert.AreEqual(1, range.LastColumn);
Assert.AreEqual(1, range.FirstRow);
Assert.AreEqual(1, range.LastRow);
Ptg[] ptg = r1.FormulaTokens;
Assert.AreEqual(FormulaRenderer.ToFormulaString(null, ptg), "MAX(C1:C2-D1:D2)");
//construct a new ArrayRecord with the same contents as r1
Ptg[] fmlaPtg = FormulaParser.Parse("MAX(C1:C2-D1:D2)", null, FormulaType.Array, 0);
ArrayRecord r2 = new ArrayRecord(Formula.Create(fmlaPtg), new CellRangeAddress8Bit(1, 1, 1, 1));
byte[] ser = r2.Serialize();
//serialize and check that the data is the same as in r1
Assert.AreEqual(HexDump.ToHex(data), HexDump.ToHex(ser));
}
public override Ptg[] GetFormulaTokens(IEvaluationCell evalCell)
{
XSSFCell cell = ((XSSFEvaluationCell)evalCell).GetXSSFCell();
int sheetIndex = _uBook.GetSheetIndex(cell.Sheet);
int rowIndex = cell.RowIndex;
return(FormulaParser.Parse(cell.GetCellFormula(this), this, FormulaType.Cell, sheetIndex, rowIndex));
}
public void ShouldHandleCircularReference2()
{
var expectedAddres = "A1:A2";
var provider = MockRepository.GenerateStub <ExcelDataProvider>();
provider
.Stub(x => x.GetRangeValues(expectedAddres))
.Return(new ExcelCell[] { CreateItem(1, 0), new ExcelCell(null, "SUM(A1:A2)", 0, 1) });
var parser = new FormulaParser(provider);
var result = parser.Parse(string.Format("sum({0})", expectedAddres));
}
public void ShouldCallProviderInSumFunctionAndCalculateResult()
{
var expectedAddres = "A1:A2";
var provider = MockRepository.GenerateStub <ExcelDataProvider>();
provider
.Stub(x => x.GetRangeValues(expectedAddres))
.Return(new ExcelCell[] { CreateItem(1, 0), CreateItem(2, 1) });
var parser = new FormulaParser(provider);
var result = parser.Parse(string.Format("sum({0})", expectedAddres));
Assert.AreEqual(3d, result);
}
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)
{
}
}
/** 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 ShouldExecuteFormulaInRange()
{
var expectedAddres = "A1:A2";
var provider = MockRepository.GenerateStub <ExcelDataProvider>();
provider
.Stub(x => x.GetRangeValues(expectedAddres))
.Return(new ExcelCell[] { CreateItem(1, 0), new ExcelCell(null, "SUM(1,2)", 0, 1) });
var parser = new FormulaParser(provider);
var result = parser.Parse(string.Format("sum({0})", expectedAddres));
Assert.AreEqual(4d, result);
}
/**
* Parse formula in the named range and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param name the name to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateName(IName name, IFormulaRenderingWorkbook frwb)
{
String formula = name.RefersToFormula;
if (formula != null)
{
int sheetIndex = name.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.NamedRange, sheetIndex);
String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (!formula.Equals(updatedFormula))
{
name.RefersToFormula = (updatedFormula);
}
}
}
private static string CopyFormula(string formula, ISheet sheet, int targetIndex)
{
var workbookWrapper = XSSFEvaluationWorkbook.Create((XSSFWorkbook)sheet.Workbook);
var ptgs = FormulaParser.Parse(formula, workbookWrapper, FormulaType.Cell, sheet.Workbook.GetSheetIndex(sheet));
foreach (var ptg in ptgs)
{
if (ptg is RefPtgBase refs && refs.IsRowRelative)
{
refs.Row = targetIndex;
}
}
return(FormulaRenderer.ToFormulaString(workbookWrapper, ptgs));
}
private static string ShiftFormula(XSSFRow row, string formula, FormulaShifter Shifter)
{
ISheet sheet = row.Sheet;
IWorkbook workbook = sheet.Workbook;
int sheetIndex = workbook.GetSheetIndex(sheet);
XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(workbook);
Ptg[] ptgs = FormulaParser.Parse(formula, (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.CELL, sheetIndex);
string str = (string)null;
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
str = FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs);
}
return(str);
}
/**
* Shift a formula using the supplied FormulaShifter
*
* @param row the row of the cell this formula belongs to. Used to get a reference to the parent workbook.
* @param formula the formula to shift
* @param Shifter the FormulaShifter object that operates on the Parsed formula tokens
* @return the Shifted formula if the formula was Changed,
* <code>null</code> if the formula wasn't modified
*/
private static String ShiftFormula(XSSFRow row, String formula, FormulaShifter Shifter)
{
ISheet sheet = row.Sheet;
IWorkbook wb = sheet.Workbook;
int sheetIndex = wb.GetSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex);
String ShiftedFmla = null;
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
}
return(ShiftedFmla);
}
public void UpdateNamedRanges(FormulaShifter shifter)
{
IWorkbook workbook = this.sheet.Workbook;
XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(workbook);
for (int nameIndex = 0; nameIndex < workbook.NumberOfNames; ++nameIndex)
{
IName nameAt = workbook.GetNameAt(nameIndex);
string refersToFormula = nameAt.RefersToFormula;
int sheetIndex = nameAt.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(refersToFormula, (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.NAMEDRANGE, sheetIndex);
if (shifter.AdjustFormula(ptgs, sheetIndex))
{
string formulaString = FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs);
nameAt.RefersToFormula = formulaString;
}
}
}
/**
* Updated named ranges
*/
public override void UpdateNamedRanges(FormulaShifter shifter)
{
IWorkbook wb = sheet.Workbook;
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
foreach (IName name in wb.GetAllNames())
{
String formula = name.RefersToFormula;
int sheetIndex = name.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.NamedRange, sheetIndex, -1);
if (shifter.AdjustFormula(ptgs, sheetIndex))
{
String shiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
name.RefersToFormula = shiftedFmla;
}
}
}
/**
* Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param cell the cell to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f != null)
{
String formula = f.Value;
if (formula != null && formula.Length > 0)
{
int sheetIndex = _wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex);
String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (!formula.Equals(updatedFormula))
{
f.Value = (updatedFormula);
}
}
}
}
/**
* Parse formula in the named range and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param name the name to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateName(IName name, String oldName, String newName)
{
String formula = name.RefersToFormula;
if (formula != null)
{
int sheetIndex = name.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.NamedRange, sheetIndex, -1);
foreach (Ptg ptg in ptgs)
{
UpdatePtg(ptg, oldName, newName);
}
String updatedFormula = FormulaRenderer.ToFormulaString(_fpwb, ptgs);
if (!formula.Equals(updatedFormula))
{
name.RefersToFormula = (updatedFormula);
}
}
}
/**
* Updated named ranges
*/
public void UpdateNamedRanges(FormulaShifter shifter)
{
IWorkbook wb = sheet.Workbook;
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
for (int i = 0; i < wb.NumberOfNames; i++)
{
IName name = wb.GetNameAt(i);
String formula = name.RefersToFormula;
int sheetIndex = name.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.NamedRange, sheetIndex);
if (shifter.AdjustFormula(ptgs, sheetIndex))
{
String shiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
name.RefersToFormula = shiftedFmla;
}
}
}
private void UpdateName(IName name, IFormulaRenderingWorkbook frwb)
{
string refersToFormula = name.RefersToFormula;
if (refersToFormula == null)
{
return;
}
int sheetIndex = name.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(refersToFormula, (IFormulaParsingWorkbook)this._fpwb, FormulaType.NAMEDRANGE, sheetIndex);
string formulaString = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (refersToFormula.Equals(formulaString))
{
return;
}
name.RefersToFormula = formulaString;
}
public static Ptg[] Parse(String formula, FormulaParsingWorkbook workbook, int formulaType)
{
FormulaParser fp = new FormulaParser(formula, workbook);
fp.Parse();
return fp.GetRPNPtg(formulaType);
}
/**
* 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, HSSFWorkbook book)
{
FormulaParser fp = new FormulaParser(formula, book);
fp.Parse();
return fp.GetRPNPtg();
}