public void TestEvaluateInSheetExample2()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
sheet.CreateRow(1).CreateCell(0).SetCellValue(0.08d);
sheet.CreateRow(2).CreateCell(0).SetCellValue(-40000d);
sheet.CreateRow(3).CreateCell(0).SetCellValue(8000d);
sheet.CreateRow(4).CreateCell(0).SetCellValue(9200d);
sheet.CreateRow(5).CreateCell(0).SetCellValue(10000d);
sheet.CreateRow(6).CreateCell(0).SetCellValue(12000d);
sheet.CreateRow(7).CreateCell(0).SetCellValue(14500d);
ICell cell = row.CreateCell(8);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
// Enumeration
cell.CellFormula = ("NPV(A2, A4,A5,A6,A7,A8)+A3");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cell);
double res = cell.NumericCellValue;
Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);
// Range
cell.CellFormula = ("NPV(A2, A4:A8)+A3");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cell);
res = cell.NumericCellValue;
Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);
}
public void TestExistingWorkbook()
{
HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0);
HSSFCell lA1Cell = (HSSFCell)lSheet.GetRow(0).GetCell(0);
HSSFCell lB1Cell = (HSSFCell)lSheet.GetRow(1).GetCell(0);
HSSFCell lC1Cell = (HSSFCell)lSheet.GetRow(2).GetCell(0);
Assert.AreEqual(CellType.Formula, lA1Cell.CellType);
Assert.AreEqual(CellType.Formula, lB1Cell.CellType);
Assert.AreEqual(CellType.Formula, lC1Cell.CellType);
HSSFFormulaEvaluator lMainWorkbookEvaluator = new HSSFFormulaEvaluator(mainWorkbook);
HSSFFormulaEvaluator lSourceEvaluator = new HSSFFormulaEvaluator(sourceWorkbook);
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { MAIN_WORKBOOK_FILENAME, SOURCE_DUMMY_WORKBOOK_FILENAME },
new HSSFFormulaEvaluator[] { lMainWorkbookEvaluator, lSourceEvaluator });
Assert.AreEqual(CellType.Numeric, lMainWorkbookEvaluator.EvaluateFormulaCell(lA1Cell));
Assert.AreEqual(CellType.String, lMainWorkbookEvaluator.EvaluateFormulaCell(lB1Cell));
Assert.AreEqual(CellType.Boolean, lMainWorkbookEvaluator.EvaluateFormulaCell(lC1Cell));
Assert.AreEqual(20.0d, lA1Cell.NumericCellValue, 0.00001d);
Assert.AreEqual("Apache rocks!", lB1Cell.StringCellValue);
Assert.AreEqual(false, lC1Cell.BooleanCellValue);
}
public void TestEvaluate()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls");
HSSFWorkbook wb2 = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls");
CellReference cellRef = new CellReference(wb.GetName("QUANT").RefersToFormula);
ICell cell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
cell.SetCellValue(NEW_QUANT);
cell = wb2.GetSheet("CostSheet").GetRow(1).GetCell(1);
cell.SetCellValue(NEW_PART_COST);
HSSFFormulaEvaluator Evaluator = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator EvaluatorCost = new HSSFFormulaEvaluator(wb2);
String[] bookNames = { "XRefCalc.xls", "XRefCalcData.xls" };
HSSFFormulaEvaluator[] Evaluators = { Evaluator, EvaluatorCost, };
HSSFFormulaEvaluator.SetupEnvironment(bookNames, Evaluators);
cellRef = new CellReference(wb.GetName("UNITCOST").RefersToFormula);
ICell uccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
cellRef = new CellReference(wb.GetName("COST").RefersToFormula);
ICell ccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
cellRef = new CellReference(wb.GetName("TOTALCOST").RefersToFormula);
ICell tccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
Evaluator.EvaluateFormulaCell(uccell);
Evaluator.EvaluateFormulaCell(ccell);
Evaluator.EvaluateFormulaCell(tccell);
Assert.AreEqual(NEW_PART_COST, uccell.NumericCellValue);
Assert.AreEqual(NEW_PART_COST * NEW_QUANT, ccell.NumericCellValue);
Assert.AreEqual(NEW_PART_COST * NEW_QUANT * MARKUP_COST_2, tccell.NumericCellValue);
}
public void TestEvaluate_bug_45732()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.CellFormula = ("NPER(12,4500,100000,100000)");
cell.SetCellValue(15.0);
Assert.AreEqual("NPER(12,4500,100000,100000)", cell.CellFormula);
Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType);
Assert.AreEqual(15.0, cell.NumericCellValue, 0.0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
fe.EvaluateFormulaCell(cell);
Assert.AreEqual(CellType.ERROR, cell.CachedFormulaResultType);
Assert.AreEqual(HSSFErrorConstants.ERROR_NUM, cell.ErrorCellValue);
}
public void TestEvaluateInSheet()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(-4000d);
row.CreateCell(1).SetCellValue(1200d);
row.CreateCell(2).SetCellValue(1410d);
row.CreateCell(3).SetCellValue(1875d);
row.CreateCell(4).SetCellValue(1050d);
ICell cell = row.CreateCell(5);
cell.CellFormula = ("IRR(A1:E1)");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cell);
double res = cell.NumericCellValue;
Assert.AreEqual(0.143d, Math.Round(res * 1000d) / 1000d);
}
/**
* Loops over all cells in all sheets of the supplied
* workbook.
* For cells that contain formulas, their formulas are
* Evaluated, and the results are saved. These cells
* remain as formula cells.
* For cells that do not contain formulas, no Changes
* are made.
* This is a helpful wrapper around looping over all
* cells, and calling EvaluateFormulaCell on each one.
*/
public static void EvaluateAllFormulaCells(HSSFWorkbook wb)
{
for (int i = 0; i < wb.NumberOfSheets; i++)
{
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(i);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();)
{
HSSFRow r = (HSSFRow)rit.Current;
//evaluator.SetCurrentRow(r);
for (IEnumerator cit = r.GetCellEnumerator(); cit.MoveNext();)
{
NPOI.SS.UserModel.Cell c = (HSSFCell)cit.Current;
if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
{
evaluator.EvaluateFormulaCell(c);
}
}
}
}
}
public void TestDummyToSource()
{
bool changed = mainWorkbook.ChangeExternalReference("DOESNOTEXIST", SOURCE_WORKBOOK_FILENAME);
Assert.IsFalse(changed);
changed = mainWorkbook.ChangeExternalReference(SOURCE_DUMMY_WORKBOOK_FILENAME, SOURCE_WORKBOOK_FILENAME);
Assert.IsTrue(changed);
HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0);
HSSFCell lA1Cell = (HSSFCell)lSheet.GetRow(0).GetCell(0);
Assert.AreEqual(CellType.FORMULA, lA1Cell.CellType);
HSSFFormulaEvaluator lMainWorkbookEvaluator = new HSSFFormulaEvaluator(mainWorkbook);
HSSFFormulaEvaluator lSourceEvaluator = new HSSFFormulaEvaluator(sourceWorkbook);
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { MAIN_WORKBOOK_FILENAME, SOURCE_WORKBOOK_FILENAME },
new HSSFFormulaEvaluator[] { lMainWorkbookEvaluator, lSourceEvaluator });
Assert.AreEqual(CellType.NUMERIC, lMainWorkbookEvaluator.EvaluateFormulaCell(lA1Cell));
Assert.AreEqual(20.0d, lA1Cell.NumericCellValue, 0.00001d);
}
public void TestZipCodeFormulas()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet s = wb.CreateSheet();
s.CreateRow(0);
ICell c1 = s.GetRow(0).CreateCell(0);
ICell c2 = s.GetRow(0).CreateCell(1);
ICell c3 = s.GetRow(0).CreateCell(2);
// As number and string
c1.CellFormula = ("70164");
c2.CellFormula = ("\"70164\"");
c3.CellFormula = ("\"90210\"");
// Check the formulas
Assert.AreEqual("70164", c1.CellFormula);
Assert.AreEqual("\"70164\"", c2.CellFormula);
// And Check the values - blank
ConfirmCachedValue(0.0, c1);
ConfirmCachedValue(0.0, c2);
ConfirmCachedValue(0.0, c3);
// Try changing the cached value on one of the string
// formula cells, so we can see it updates properly
c3.SetCellValue(new HSSFRichTextString("Test"));
ConfirmCachedValue("Test", c3);
try
{
double a = c3.NumericCellValue;
throw new AssertionException("exception should have been thrown");
}
catch (InvalidOperationException e)
{
Assert.AreEqual("Cannot get a numeric value from a text formula cell", e.Message);
}
// Now Evaluate, they should all be changed
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
eval.EvaluateFormulaCell(c1);
eval.EvaluateFormulaCell(c2);
eval.EvaluateFormulaCell(c3);
// Check that the cells now contain
// the correct values
ConfirmCachedValue(70164.0, c1);
ConfirmCachedValue("70164", c2);
ConfirmCachedValue("90210", c3);
// Write and read
HSSFWorkbook nwb = WriteOutAndReadBack(wb);
HSSFSheet ns = (HSSFSheet)nwb.GetSheetAt(0);
ICell nc1 = ns.GetRow(0).GetCell(0);
ICell nc2 = ns.GetRow(0).GetCell(1);
ICell nc3 = ns.GetRow(0).GetCell(2);
// Re-Check
ConfirmCachedValue(70164.0, nc1);
ConfirmCachedValue("70164", nc2);
ConfirmCachedValue("90210", nc3);
CellValueRecordInterface[] cvrs = ns.Sheet.GetValueRecords();
for (int i = 0; i < cvrs.Length; i++)
{
CellValueRecordInterface cvr = cvrs[i];
if (cvr is FormulaRecordAggregate)
{
FormulaRecordAggregate fr = (FormulaRecordAggregate)cvr;
if (i == 0)
{
Assert.AreEqual(70164.0, fr.FormulaRecord.Value, 0.0001);
Assert.IsNull(fr.StringRecord);
}
else if (i == 1)
{
Assert.AreEqual(0.0, fr.FormulaRecord.Value, 0.0001);
Assert.IsNotNull(fr.StringRecord);
Assert.AreEqual("70164", fr.StringRecord.String);
}
else
{
Assert.AreEqual(0.0, fr.FormulaRecord.Value, 0.0001);
Assert.IsNotNull(fr.StringRecord);
Assert.AreEqual("90210", fr.StringRecord.String);
}
}
}
Assert.AreEqual(3, cvrs.Length);
}
public void TestXRefs()
{
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls");
IWorkbook wbData = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls");
ICell cell;
// VLookup on a name in another file
cell = wb.GetSheetAt(0).GetRow(1).GetCell(2);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);
// WARNING - this is wrong!
// The file name should be Showing, but bug #45970 is fixed
// we seem to loose it
Assert.AreEqual("VLOOKUP(PART,COSTS,2,FALSE)", cell.CellFormula);
// Simple reference to a name in another file
cell = wb.GetSheetAt(0).GetRow(1).GetCell(4);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);
// WARNING - this is wrong!
// The file name should be Showing, but bug #45970 is fixed
// we seem to loose it
Assert.AreEqual("Cost*Markup_Cost", cell.CellFormula);
// Evaluate the cells
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { "XRefCalc.xls", "XRefCalcData.xls" },
new HSSFFormulaEvaluator[] {
eval,
new HSSFFormulaEvaluator(wbData)
}
);
eval.EvaluateFormulaCell(
wb.GetSheetAt(0).GetRow(1).GetCell(2)
);
eval.EvaluateFormulaCell(
wb.GetSheetAt(0).GetRow(1).GetCell(4)
);
// Re-check VLOOKUP one
cell = wb.GetSheetAt(0).GetRow(1).GetCell(2);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);
// Re-check ref one
cell = wb.GetSheetAt(0).GetRow(1).GetCell(4);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);
// Add a formula that refers to one of the existing external workbooks
cell = wb.GetSheetAt(0).GetRow(1).CreateCell(40);
cell.CellFormula = (/*setter*/"Cost*[XRefCalcData.xls]MarkupSheet!$B$1");
// Check is was stored correctly
Assert.AreEqual("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.CellFormula);
// Check it Evaluates correctly
eval.EvaluateFormulaCell(cell);
Assert.AreEqual(24.60 * 1.8, cell.NumericCellValue);
// Try to add a formula for a new external workbook, won't be allowed to start
try
{
cell = wb.GetSheetAt(0).GetRow(1).CreateCell(42);
cell.CellFormula = (/*setter*/"[alt.xls]Sheet0!$A$1");
Assert.Fail("New workbook not linked, shouldn't be able to Add");
}
catch (Exception e) { }
// Link our new workbook
HSSFWorkbook alt = new HSSFWorkbook();
alt.CreateSheet().CreateRow(0).CreateCell(0).SetCellValue("In another workbook");
wb.LinkExternalWorkbook("alt.xls", alt);
// Now add a formula that refers to our new workbook
cell.CellFormula = (/*setter*/"[alt.xls]Sheet0!$A$1");
Assert.AreEqual("[alt.xls]Sheet0!$A$1", cell.CellFormula);
// Evaluate it, without a link to that workbook
try
{
eval.Evaluate(cell);
Assert.Fail("No cached value and no link to workbook, shouldn't Evaluate");
}
catch (Exception e) { }
// Add a link, check it does
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" },
new HSSFFormulaEvaluator[] {
eval,
new HSSFFormulaEvaluator(wbData),
new HSSFFormulaEvaluator(alt)
}
);
eval.EvaluateFormulaCell(cell);
Assert.AreEqual("In another workbook", cell.StringCellValue);
// Save and re-load
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook);
eval = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" },
new HSSFFormulaEvaluator[] {
eval,
new HSSFFormulaEvaluator(wbData),
new HSSFFormulaEvaluator(alt)
}
);
// Check the one referring to the previously existing workbook behaves
cell = wb.GetSheetAt(0).GetRow(1).GetCell(40);
Assert.AreEqual("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.CellFormula);
eval.EvaluateFormulaCell(cell);
Assert.AreEqual(24.60 * 1.8, cell.NumericCellValue);
// Now check the newly Added reference
cell = wb.GetSheetAt(0).GetRow(1).GetCell(42);
Assert.AreEqual("[alt.xls]Sheet0!$A$1", cell.CellFormula);
eval.EvaluateFormulaCell(cell);
Assert.AreEqual("In another workbook", cell.StringCellValue);
}
/**
* Loops over all cells in all sheets of the supplied
* workbook.
* For cells that contain formulas, their formulas are
* Evaluated, and the results are saved. These cells
* remain as formula cells.
* For cells that do not contain formulas, no Changes
* are made.
* This is a helpful wrapper around looping over all
* cells, and calling EvaluateFormulaCell on each one.
*/
public static void EvaluateAllFormulaCells(HSSFWorkbook wb)
{
for (int i = 0; i < wb.NumberOfSheets; i++)
{
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(i);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext(); )
{
HSSFRow r = (HSSFRow)rit.Current;
//evaluator.SetCurrentRow(r);
for (IEnumerator cit = r.GetCellEnumerator(); cit.MoveNext(); )
{
NPOI.SS.UserModel.Cell c = (HSSFCell)cit.Current;
if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
evaluator.EvaluateFormulaCell(c);
}
}
}
}
/**
* Sum across multiple workbooks
* eg =SUM($Sheet2.A1:$Sheet3.A1)
* DISABLED - We currently get the formula wrong, and mis-evaluate
*/
public void DISABLEDtest48703()
{
HSSFWorkbook wb = OpenSample("48703.xls");
Assert.AreEqual(3, wb.NumberOfSheets);
// Check reading the formula
ISheet sheet = wb.GetSheetAt(0);
IRow r = sheet.GetRow(0);
ICell c = r.GetCell(0);
Assert.AreEqual("SUM(Sheet2!A1:Sheet3!A1)", c.CellFormula);
Assert.AreEqual(4.0, c.NumericCellValue);
// Check the evaluated result
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
eval.EvaluateFormulaCell(c);
Assert.AreEqual(4.0, c.NumericCellValue);
}
/**
*
* Returns the Formatted value of a cell as a <tt>String</tt> regardless
* of the cell type. If the Excel FormatBase pattern cannot be Parsed then the
* cell value will be Formatted using a default FormatBase.
*
* When passed a null or blank cell, this method will return an empty
* String (""). Formula cells will be evaluated using the given
* {@link HSSFFormulaEvaluator} if the evaluator is non-null. If the
* evaluator is null, then the formula String will be returned. The caller
* is responsible for setting the currentRow on the evaluator
*
*
* @param cell The cell (can be null)
* @param evaluator The HSSFFormulaEvaluator (can be null)
* @return a string value of the cell
*/
public String FormatCellValue(HSSFCell cell, HSSFFormulaEvaluator evaluator)
{
if (cell == null)
{
return "";
}
int cellType = cell.CellType;
if (evaluator != null && cellType == HSSFCell.CELL_TYPE_FORMULA)
{
try
{
cellType = evaluator.EvaluateFormulaCell(cell);
}
catch (Exception e)
{
throw new Exception("Did you forGet to set the current" +
" row on the HSSFFormulaEvaluator?", e);
}
}
switch (cellType)
{
case HSSFCell.CELL_TYPE_BLANK:
return "";
case HSSFCell.CELL_TYPE_FORMULA:
// should only occur if evaluator is null
return cell.CellFormula;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
return GetFormattedDateString(cell);
}
return GetFormattedNumberString(cell);
case HSSFCell.CELL_TYPE_STRING:
return cell.RichStringCellValue.String;
case HSSFCell.CELL_TYPE_BOOLEAN:
return cell.BooleanCellValue?"TRUE":"FALSE";
case HSSFCell.CELL_TYPE_ERROR:
return NPOI.HSSF.Record.Formula.Eval.ErrorEval.GetText(cell.ErrorCellValue);
}
throw new Exception("Unexpected celltype (" + cellType + ")");
}
public void TestEvaluateInSheet()
{
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(-7500d);
row.CreateCell(1).SetCellValue(3000d);
row.CreateCell(2).SetCellValue(5000d);
row.CreateCell(3).SetCellValue(1200d);
row.CreateCell(4).SetCellValue(4000d);
row.CreateCell(5).SetCellValue(0.05d);
row.CreateCell(6).SetCellValue(0.08d);
ICell cell = row.CreateCell(7);
cell.CellFormula = (/*setter*/"MIRR(A1:E1, F1, G1)");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cell);
double res = cell.NumericCellValue;
Assert.AreEqual(0.18736225093, res, 0.00000001);
}
public void TestXRefs()
{
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls");
IWorkbook wbData = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls");
ICell cell;
// VLookup on a name in another file
cell = wb.GetSheetAt(0).GetRow(1).GetCell(2);
Assert.AreEqual(CellType.FORMULA, cell.CellType);
Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType);
Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);
// WARNING - this is wrong!
// The file name should be Showing, but bug #45970 is fixed
// we seem to loose it
Assert.AreEqual("VLOOKUP(PART,COSTS,2,FALSE)", cell.CellFormula);
// Simple reference to a name in another file
cell = wb.GetSheetAt(0).GetRow(1).GetCell(4);
Assert.AreEqual(CellType.FORMULA, cell.CellType);
Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType);
Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);
// WARNING - this is wrong!
// The file name should be Showing, but bug #45970 is fixed
// we seem to loose it
Assert.AreEqual("Cost*Markup_Cost", cell.CellFormula);
// Evaluate the cells
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { "XRefCalc.xls", "XRefCalcData.xls" },
new HSSFFormulaEvaluator[] {
eval,
new HSSFFormulaEvaluator(wbData)
}
);
eval.EvaluateFormulaCell(
wb.GetSheetAt(0).GetRow(1).GetCell(2)
);
eval.EvaluateFormulaCell(
wb.GetSheetAt(0).GetRow(1).GetCell(4)
);
// Re-check VLOOKUP one
cell = wb.GetSheetAt(0).GetRow(1).GetCell(2);
Assert.AreEqual(CellType.FORMULA, cell.CellType);
Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType);
Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);
// Re-check ref one
cell = wb.GetSheetAt(0).GetRow(1).GetCell(4);
Assert.AreEqual(CellType.FORMULA, cell.CellType);
Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType);
Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);
}
public void TestEvaluateAll()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s1 = wb.CreateSheet();
NPOI.SS.UserModel.ISheet s2 = wb.CreateSheet();
wb.SetSheetName(0, "S1");
wb.SetSheetName(1, "S2");
IRow s1r1 = s1.CreateRow(0);
IRow s1r2 = s1.CreateRow(1);
IRow s2r1 = s2.CreateRow(0);
ICell s1r1c1 = s1r1.CreateCell(0);
ICell s1r1c2 = s1r1.CreateCell(1);
ICell s1r1c3 = s1r1.CreateCell(2);
s1r1c1.SetCellValue(22.3);
s1r1c2.SetCellValue(33.4);
s1r1c3.CellFormula = ("SUM(A1:B1)");
ICell s1r2c1 = s1r2.CreateCell(0);
ICell s1r2c2 = s1r2.CreateCell(1);
ICell s1r2c3 = s1r2.CreateCell(2);
s1r2c1.SetCellValue(-1.2);
s1r2c2.SetCellValue(-3.4);
s1r2c3.CellFormula = ("SUM(A2:B2)");
ICell s2r1c1 = s2r1.CreateCell(0);
s2r1c1.CellFormula = ("S1!A1");
// Not Evaluated yet
Assert.AreEqual(0.0, s1r1c3.NumericCellValue, 0);
Assert.AreEqual(0.0, s1r2c3.NumericCellValue, 0);
Assert.AreEqual(0.0, s2r1c1.NumericCellValue, 0);
// Do a full Evaluate, as per our docs
// uses EvaluateFormulaCell()
for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
{
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext(); )
{
IRow r = (IRow)rit.Current;
for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext(); )
{
ICell c = (ICell)cit.Current;
if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
{
evaluator.EvaluateFormulaCell(c);
// For Testing - all should be numeric
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, evaluator.EvaluateFormulaCell(c));
}
}
}
}
// Check now as expected
Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
Assert.AreEqual("SUM(A1:B1)", wb.GetSheetAt(0).GetRow(0).GetCell(2).CellFormula);
Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);
Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
Assert.AreEqual("SUM(A2:B2)", wb.GetSheetAt(0).GetRow(1).GetCell(2).CellFormula);
Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);
Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
Assert.AreEqual("'S1'!A1", wb.GetSheetAt(1).GetRow(0).GetCell(0).CellFormula);
Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);
// Now do the alternate call, which zaps the formulas
// uses EvaluateInCell()
for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
{
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext(); )
{
IRow r = (IRow)rit.Current;
for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext(); )
{
ICell c = (ICell)cit.Current;
if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
{
evaluator.EvaluateInCell(c);
}
}
}
}
Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);
Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);
Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);
}
public void TestClassCast_bug44861()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44861.xls");
// Check direct
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
// And via calls
int numSheets = wb.NumberOfSheets;
for (int i = 0; i < numSheets; i++)
{
NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(i);
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
for (IEnumerator rows = s.GetRowEnumerator(); rows.MoveNext(); )
{
IRow r = (IRow)rows.Current;
for (IEnumerator cells = r.GetEnumerator(); cells.MoveNext(); )
{
ICell c = (ICell)cells.Current;
eval.EvaluateFormulaCell(c);
}
}
}
}
