CODEWITHSUNDEEP
c#excelclosedxml
Fuad Hashimov
Fuad Hashimov

Reputation: 41

Calculate formula in closedxml

I used a formula in closedxml:

var result = worksheet.Cell(3, 8);
result.FormulaA1 = "=IF(ISNA(VLOOKUP(G3,OrganizationUnitsById,2,FALSE))," 
                 + "" 
                 + ",VLOOKUP(G3,OrganizationUnitsById,2,FALSE))";

When I used this formula before, it worked perfectly. But then I changed something, then this formula didn't calculate automatically. I saved file from an application, this formula works but didn't calculate automatically.

What is wrong?

Upvotes: 1

Views: 9890

Answers (1)

rene
rene

Reputation: 42494

My experiments have revealed that your formula throws the following exception:

An unhandled exception of type 'System.NotSupportedException' occurred in ClosedXML.dll

Here is the testrig:

var wb = new ClosedXML.Excel.XLWorkbook();        
var worksheet = wb.AddWorksheet("test");

worksheet.Cell("G3").Value = "fu";

worksheet.Cell("A1").Value = "fu";
worksheet.Cell("B1").Value = "bar";
worksheet.Cell("C1").Value = "baz";
worksheet.Cell("A2").Value = "fu2";
worksheet.Cell("B2").Value = "bar2";
worksheet.Cell("C2").Value = "baz2";

worksheet.Range("A1:C2").AddToNamed("OrganizationUnitsById");

var result = worksheet.Cell(3, 8);
result.FormulaA1 = "=IF(ISNA(VLOOKUP(G3,OrganizationUnitsById,2,FALSE)),"
                 + ""
                 + ",VLOOKUP(G3,OrganizationUnitsById,2,FALSE))";
wb.CalculateMode = ClosedXML.Excel.XLCalculateMode.Auto;
Console.WriteLine(result.Value); // throws exception

The reason it throws is caused by the use of the ISNA function in your formula. It isn't yet implemented. Removing ISNA from your formula resolves the calculation issues but that will lead to different behavior of that formula.

You have to find an alternative for the ISNA function. At the date of writing, pick one of these: ISBLANK, ISEVEN, ISLOGICAL, ISNUMBER, ISODD, ISTEXT, N or shape the data in your lookup table so you don't need the check for `ISNA.

As my example is tested with 0.88 you could fetch the beta release ClosedXML 0.89.0-beta1 because ISNA is implemented in PR441.

Upvotes: 5

Related Questions