Carlos Borau Carlos Borau - 4 months ago 46
Vb.net Question

Formula created with ClosedXML not recognized in Excel

So far I've been exporting data to Excel using the Microsoft.Office.Interop library. Now I need instead to do it using ClosedXML. Everything works fine except for the formulas. Each formula itself is exported properly, however it doesn't "work" until I doubleclick its content and press enter. I attach a screenshot to clarify this.

enter image description here

Note: the range (P2:P3) in the image is black until I click it and becomes blue. This somehow makes Excel recognize the content.

Previously I was using:

Imports Microsoft.Office.Interop
worksheet.Cells(i, j).FormulaLocal = "=SUMA(" & col_letter & "2:" & col_letter & rowcount & ")"


Now I'm using:

Imports ClosedXML.Excel
worksheet.Cell(i, j).SetFormulaA1("SUMA(" & col_letter & "2:" & col_letter & rowcount & ")")


It is so simple that I'm completely stuck.
Any help would be greatly appreciated!

Answer

Although I can't find any documentation to confirm this, I suspect that ClosedXML only works with the English formula names, so use SUM instead of SUMA:

worksheet.Cell(i, j).SetFormulaA1("SUM(" & col_letter & "2:" & col_letter & rowcount  & ")")