ClosedXML now resolves formulas!
Yes it finally happened. If you call cell.Value and it has a formula the library will try to evaluate the formula and give you the result.
For example:
var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Sheet1");
ws.Cell("A1").SetValue(1).CellBelow().SetValue(1);
ws.Cell("B1").SetValue(1).CellBelow().SetValue(1);
ws.Cell("C1").FormulaA1 = "\"The total value is: \" & SUM(A1:B2)";
var r = ws.Cell("C1").Value;
Assert.AreEqual("The total value is: 4", r.ToString());
You can even resolve your own formulas without using cells. For Example:
If you're not referencing a worksheet you can use:
var sum = XLWorkbook.EvaluateExpr("SUM(1,2,3)");
// sum = 6
// SUM(Sheet1!A1:B2) will fail because it doesn't know which workbook to use
If you're not referencing a range without a worksheet you can use:
var sum = workbook.Evaluate("SUM(Sheet1!A1:B2)");
// SUM(A1:B2) will fail because it doesn't know which sheet to use
If you have the worksheet you can evaluate at your heart's content:
var sum = worksheet.Evaluate("SUM(A1:B2)");
This is a list of the included formulas:
All math and trigonometric formulas.
All text formulas except BAHTTEXT, JIS, and PHONETIC.
All logical formulas.
All date and time formulas.
Many statistical formulas.
Very important:
- Not all formulas are included and you'll probably get a nasty error if the formula isn't supported or if there's an error in the formula. Please test your formulas before going to production.
- I'm adding new formulas all the time but if your formula isn't included please let me know via the Issue Tracker. I'll do my best to include the formula asap.
As always many fixes and performance improvementsv0.68.1
- Added all date and time functions