Additional @ in excel formula while downloading it.

[ghost]

I am using NPOI latest version in c#. I have used a formula like below, means when there is sheetname soon after the '=' sign then additional symbol '@' comes it self while exporting the excel.

e.g:
string strFormula = "IFERROR(IF(MATCH(1,(D{0}='sheet name'!D:D)*......so on

but in excel file this formula comes like:
"IFERROR(IF(MATCH(1,(D{0}=@'sheet name'!D:D)*......so on

means extra @ comes itself. so cell formula doesn't work.

Can someone please help on it,

[drmason789]

The @ symbol is the implicit intersection operator which was introduced in Office 365 sometime ago. It is part of the new "dynamic array" functionality. See also this link.

I believe in your case, NPOI has worked, but Excel 365 now renders these formulas differently. Let's say in Excel 2016 you had, in cell A4 a formula =$A$1:$D$1. This formula would be "implicitly intersected" with the referring cell, and so A4 would get the value from $A$1.
In Excel 365 with dynamic arrays, the same formula would create a dynamic array, with the values from A1:D1 in A4 to D4. In order to get the same behaviour as Excel 2016, you would need to change the formula to =@$A$1:$D$1.

Excel's object model Range interface has several members for supporting dynamic arrays, e.g. Formula2, Formula2R1C1, HasSpill, SpillParent, SpilingToRange, SavedAsArray. I think NPOI will probably need equivalents sooner or later.

[drmason789]

I've created issue #663 for this.

[ghost]

Thanks for your response..But I have one question , Is there any way to avoid @ symbol from formula while opening excel in office 365 .
Because formula is not evaluating values correctly when @ symbol is there, Is there any way to get rid of @ from c# code itself?

I am using formula like below:

                    ICell cellD5 = rowFeePayable.CreateCell(j);
                    cellD5.CellStyle = cellStyleFloat;            
                    cellD5.SetCellFormula(string.Format("myFormula", rowNumber)); j++;

formula is something like below:
IFERROR(IF(MATCH(1,(D{0}='@'Sheet Name'!D:D)*(B{0}='Sheet Name'!A:A)...........

and there is space in .

[drmason789]

Excel 365 that supports dynamic arrays will always display the @ for implicit intersections, AFAIK. However, if you want to get the "legacy style" formula out of a cell in Excel, use Range.Formula or Range.FormulaR1C1, otherwise use Range.Formula2 or Range.Formula2R1C1. If your formula has a mixture of implicit intersections and dynamic arrays, you'll still get an @ out of Range.Formula and Range.FormulaR1C1.

Regarding the calculation issue, are you able to attach a simple example workbook?

[ghost]

Thanks, but can you please give me code syntax example for the same. In C#, I am not able to see how to get range and apply formula on range using NPOI.

I can see only cell.setFormula.
Pls let me know how to use Range.Formula

[drmason789]

Range.Formula and the other Range.* refer to Excel Interop or VBA, not NPOI.

Set the formula in NPOI with ICell.SetCellFormula, but simply don't use dynamic array formulas. If you write the formula in Excel, use Alt-F11 to open the VB Editor, then Ctrl-G to go to the immediate window, then, assuming your formula is in C10, for example,

?Range("C10").Formula

This gets you the formula without the implicit intersection operator, if the formula actually has no dynamic array components. Assign this in NPOI with ICell.SetCellFormula. You can still get an @ out of Excel's Range.Formula with a mixed formula such as =SQRT(A1:A4)+ SQRT(@A1:A4).