/// <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);
}
}
public void TestDiscontinousReference()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("44167.xls");
HSSFWorkbook wb = new HSSFWorkbook(is1);
NPOI.HSSF.Model.Workbook workbook = wb.Workbook;
HSSFEvaluationWorkbook eb = HSSFEvaluationWorkbook.Create(wb);
Assert.AreEqual(1, wb.NumberOfNames);
String sheetName = "Tabelle1";
String rawRefA = "$C$10:$C$14";
String rawRefB = "$C$16:$C$18";
String refA = sheetName + "!" + rawRefA;
String refB = sheetName + "!" + rawRefB;
String ref1 = refA + "," + refB;
// Check the low level record
NameRecord nr = workbook.GetNameRecord(0);
Assert.IsNotNull(nr);
Assert.AreEqual("test", nr.NameText);
Ptg[] def = nr.NameDefinition;
Assert.AreEqual(4, def.Length);
MemFuncPtg ptgA = (MemFuncPtg)def[0];
Area3DPtg ptgB = (Area3DPtg)def[1];
Area3DPtg ptgC = (Area3DPtg)def[2];
UnionPtg ptgD = (UnionPtg)def[3];
Assert.AreEqual("", ptgA.ToFormulaString());
Assert.AreEqual(refA, ptgB.ToFormulaString(eb));
Assert.AreEqual(refB, ptgC.ToFormulaString(eb));
Assert.AreEqual(",", ptgD.ToFormulaString());
Assert.AreEqual(ref1, NPOI.HSSF.Model.HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition));
// Check the high level definition
int idx = wb.GetNameIndex("test");
Assert.AreEqual(0, idx);
NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(idx);
// Should have 2 references
Assert.AreEqual(ref1, aNamedCell.RefersToFormula);
// Check the parsing of the reference into cells
Assert.IsFalse(AreaReference.IsContiguous(aNamedCell.RefersToFormula));
AreaReference[] arefs = AreaReference.GenerateContiguous(aNamedCell.RefersToFormula);
Assert.AreEqual(2, arefs.Length);
Assert.AreEqual(refA, arefs[0].FormatAsString());
Assert.AreEqual(refB, arefs[1].FormatAsString());
for (int i = 0; i < arefs.Length; i++)
{
AreaReference ar = arefs[i];
ConfirmResolveCellRef(wb, ar.FirstCell);
ConfirmResolveCellRef(wb, ar.LastCell);
}
}
private static ValueEval EvaluateFormula(Ptg[] ptgs)
{
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet().CreateRow(0).CreateCell(0);
IEvaluationWorkbook ewb = HSSFEvaluationWorkbook.Create(wb);
OperationEvaluationContext ec = new OperationEvaluationContext(null, ewb, 0, 0, 0, null);
return(new WorkbookEvaluator(null, null, null).EvaluateFormula(ec, ptgs));
}
private OperationEvaluationContext CreateContext()
{
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet();
HSSFEvaluationWorkbook workbook = HSSFEvaluationWorkbook.Create(wb);
WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(workbook, new IStabilityClassifier1(), null);
OperationEvaluationContext ctx = new OperationEvaluationContext(workbookEvaluator,
workbook, 0, 0, 0, null);
return(ctx);
}
private static IEvaluationWorkbook CreateEvaluationWorkbook(IWorkbook wb)
{
if (wb is HSSFWorkbook)
{
return(HSSFEvaluationWorkbook.Create((HSSFWorkbook)wb));
}
// TODO rearrange POI build to allow this
// if (wb is XSSFWorkbook) {
// return XSSFEvaluationWorkbook.Create((XSSFWorkbook) wb);
// }
throw new ArgumentException("Unexpected workbook type (" + wb.GetType().Name + ")");
}
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)
{
}
}
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));
}
public void TestToFormulaString()
{
Area3DPtg target = new Area3DPtg("A1:B1", (short)0);
string sheetName = "my sheet";
HSSFWorkbook wb = CreateWorkbookWithSheet(sheetName);
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(wb);
Assert.AreEqual("'my sheet'!A1:B1", target.ToFormulaString(book));
wb.SetSheetName(0, "Sheet1");
Assert.AreEqual("Sheet1!A1:B1", target.ToFormulaString(book));
wb.SetSheetName(0, "C64");
Assert.AreEqual("'C64'!A1:B1", target.ToFormulaString(book));
}
public void TestMacroFunction()
{
// testNames.xls contains a VB function called 'myFunc'
HSSFWorkbook w = HSSFTestDataSamples.OpenSampleWorkbook("testNames.xls");
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(w);
Ptg[] ptg = HSSFFormulaParser.Parse("myFunc()", w);
// the name Gets encoded as the first arg
NamePtg tname = (NamePtg)ptg[0];
Assert.AreEqual("myFunc", tname.ToFormulaString(book));
AbstractFunctionPtg tfunc = (AbstractFunctionPtg)ptg[1];
Assert.IsTrue(tfunc.IsExternalFunction);
}
private OperationEvaluationContext CreateContext()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue("123.43");
cell = row.CreateCell(1);
cell.SetCellValue("8");
cell = row.CreateCell(2);
cell.SetCellValue("-8");
HSSFEvaluationWorkbook workbook = HSSFEvaluationWorkbook.Create(wb);
WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(workbook, new IStabilityClassifier1(), null);
OperationEvaluationContext ctx = new OperationEvaluationContext(workbookEvaluator,
workbook, 0, 0, 0, null);
return(ctx);
}
public void TestConvertSharedFormulas()
{
IWorkbook wb = new HSSFWorkbook();
HSSFEvaluationWorkbook fpb = HSSFEvaluationWorkbook.Create(wb);
Ptg[] sharedFormula, ConvertedFormula;
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL97);
sharedFormula = FormulaParser.Parse("A2", fpb, FormulaType.Cell, -1, -1);
ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 0, 0);
ConfirmOperandClasses(sharedFormula, ConvertedFormula);
//conversion relative to [0,0] should return the original formula
Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "A2");
ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 1, 0);
ConfirmOperandClasses(sharedFormula, ConvertedFormula);
//one row down
Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "A3");
ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 1, 1);
ConfirmOperandClasses(sharedFormula, ConvertedFormula);
//one row down and one cell right
Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "B3");
sharedFormula = FormulaParser.Parse("SUM(A1:C1)", fpb, FormulaType.Cell, -1, -1);
ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 0, 0);
ConfirmOperandClasses(sharedFormula, ConvertedFormula);
Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "SUM(A1:C1)");
ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 1, 0);
ConfirmOperandClasses(sharedFormula, ConvertedFormula);
Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "SUM(A2:C2)");
ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 1, 1);
ConfirmOperandClasses(sharedFormula, ConvertedFormula);
Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "SUM(B2:D2)");
}
private static IEvaluationWorkbook CreateEvaluationWorkbook(IWorkbook wb)
{
if (wb is HSSFWorkbook)
{
return(HSSFEvaluationWorkbook.Create((HSSFWorkbook)wb));
}
else
{
try
{
// TODO: check if this is Java 9 compatible ...
Type evalWB = Type.GetType("NPOI.XSSF.UserModel.XSSFEvaluationWorkbook");
Type xssfWB = Type.GetType("NPOI.XSSF.UserMode.XSSFWorkbook");
MethodInfo createM = evalWB.GetMethod("create", new Type[] { xssfWB });
return((IEvaluationWorkbook)createM.Invoke(null, new object[] { wb }));
}
catch (Exception e)
{
throw new ArgumentException("Unexpected workbook type (" + wb.GetType().Name
+ ") - check for poi-ooxml and poi-ooxml schemas jar in the classpath", e);
}
}
}
/**
* Static method to convert an array of {@link Ptg}s in RPN order
* to a human readable string format in infix mode.
* @param book used for defined names and 3D references
* @param ptgs must not be <c>null</c>
* @return a human readable String
*/
public static String ToFormulaString(HSSFWorkbook book, Ptg[] ptgs)
{
return(FormulaRenderer.ToFormulaString(HSSFEvaluationWorkbook.Create(book), ptgs));
}
private static IFormulaParsingWorkbook CreateParsingWorkbook(HSSFWorkbook book)
{
return(HSSFEvaluationWorkbook.Create(book));
}
public void TestHSSFPassCase()
{
IFormulaParsingWorkbook workbook = HSSFEvaluationWorkbook.Create(new HSSFWorkbook());
FormulaParser.Parse("Sheet1!1:65536", workbook, FormulaType.Cell, 0);
}
public static WorkbookEvaluator CreateEvaluator(HSSFWorkbook wb, EvaluationListener listener)
{
return(new WorkbookEvaluator(HSSFEvaluationWorkbook.Create(wb), listener, null, null));
}
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 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);
}
