Excel don't recognize formulas from closedXML / c#

Question

Info: The following code is in a foreach loop

Works:

indexSheet.Cell("G" + cellIndex).FormulaA1 = "=SUM(A1:A10)";

Only works, when i type this directly in excel:

indexSheet.Cell("G" + cellIndex).FormulaA1 = "=FIND(\"erw\";H5)"; // with Property
indexSheet.Cell("G" + cellIndex).SetFormulaA1("=FIND(\"erw\";H5)");  //with Function
indexSheet.Cell("G" + cellIndex).FormulaA1 = $"COUNTIF(H{cellIndex};\"*\"&$C$2&\"*\")*(MAX($G$4:G{cellIndex - 1})+1)";
indexSheet.Cell("B" + cellIndex).FormulaA1 = $"=IF(ROW()-{cellIndex}>MAX(G:G);\"\";HYPERLINK(CONCATENATE(\"#\";(INDEX(H:H;MATCH(ROW()-4;G:G;0)));\"!B{cellIndex}\");(INDEX(H:H;MATCH(ROW()-4;G:G;0)))))";

I've checked/tried:

Because in stackoverflow exists a similar case: If the language of my functions matching with my excel language (both are english)
I tried if simpler formulas would work like SUM() or FIND() --> (-SUM works, FIND doesn't work )
I typed the formulas directly in an excelsheet (formulas working here)
I tried it outside from the Loop with a hard-coded cell-informations and without the "cellindex"-variable
Checked out the closedXML-documentation
Whit equal signs at the beginning of the formulas and without it
I tried with closedXML Vers. 0.76.0 and 0.80.1 (latest stable)

Excel shows me this: Picture1

And this: Picture2

The Linked XML-logfile from Picture 2 contains informations about the deleted parts

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error035720_03.xml</logFileName><summary>Errors were detected in file 'C:\Users\XXX\Documents\Output\CodeBook.xlsx'</summary>
    <removedRecords>
        <removedRecord>Removed Records: Formula from /xl/worksheets/sheet.xml part</removedRecord>
        <removedRecord>Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)</removedRecord>
    </removedRecords>
</recoveryLog>

user1859022 user1859022 · Accepted Answer · 2016-10-17T15:09:22

you can't use semicolon (;) to separate arguments in closedxml. Use comma(,) like this:

indexSheet.Cell("G" + cellIndex).FormulaA1 = "=FIND(\"erw\",H5)";

that is the reason why SUM(range) works but FIND(find_text;within_text) does not...

Excel don't recognize formulas from closedXML / c#

1 Answers