in C#, when I use this string for excel formula
string formula = “=IF(ISNA(MATCH("A", {"A", "B"}, 0)), FALSE, TRUE)”;
I get “=IF(ISNA(MATCH("A”, {“A” "B"}, 0)), false, true)" instead of “=IF(ISNA(MATCH("A”, {“A” ,“B”}, 0)), false, true)", where the comma is replaced with a backlash but the formula still works. Why this issue? Is it a bug from this specific Aspose Cells version?
The issue you’re experiencing with the formula string in Aspose.Cells, where commas are replaced with backslashes, is related to how Excel interprets array formulas. In the newer versions of Excel, when you input an array formula, it may automatically adjust the syntax, which can lead to unexpected characters appearing in the formula string.
To resolve this, instead of using the Cell.Formula property to set your formula, you should use the SetArrayFormula method. This method explicitly tells Excel that you are setting an array formula, which should prevent the unwanted modification of the formula string.
Here’s how you can set your formula correctly:
string formula = "=IF(ISNA(MATCH(\"A\", {\"A\", \"B\"}, 0)), FALSE, TRUE)";
firstSheet.Cells[0, 1].SetArrayFormula(formula, 1, 1);
By using SetArrayFormula, you ensure that the formula is treated as an array formula, which should maintain the correct syntax without replacing commas with backslashes.
If you continue to experience issues, it may be worth checking for updates or patches for Aspose.Cells, as this behavior could be specific to the version you are using.
For more detailed information on handling array formulas, you can refer to the official documentation or community discussions regarding this topic.
Sources:
[1]: Formula is incorrectly modified with @ symbol when using TEXTJOIN & IF with named ranges or sheet references
First, thanks for the reply! Unfortunately, after testing, the issue is still present, is there any chance you know other possible causes?
I tested your scenario/case using the following sample code with latest version/fix: Aspose.Cells
for .NET v24.10 (please try it if you are not already using it), it works fine and I got expected results. The output Excel file (attached is also fine tuned.
e.g.,
Sample code:
Workbook workbook = new Workbook();
Worksheet firstSheet = workbook.Worksheets[0];
string formula = "=IF(ISNA(MATCH(\"A\", {\"A\", \"B\"}, 0)), FALSE, TRUE)";
firstSheet.Cells[0, 1].Formula = formula;
firstSheet.Cells[0, 2].SetArrayFormula(formula, 1, 1);
string formulaString = firstSheet.Cells[0, 1].Formula;
string formulaString1 = firstSheet.Cells[0, 2].Formula;
Console.WriteLine(formulaString);
Console.WriteLine(formulaString1);
workbook.Save("e:\\test2\\out1.xlsx");
console output:
=IF(ISNA(MATCH(“A”,{“A”,“B”},0)),FALSE,TRUE)
=IF(ISNA(MATCH(“A”,{“A”,“B”},0)),FALSE,TRUE)
Please find attached the output Excel file for your reference.
out1.zip (6.2 KB)
If you still find the issue with Aspose.Cells for .NET v24.10, kindly provide your sample (runnable) code and sample Excel files(if any) to reproduce the issue on our end. We will check your issue soon.