Hi,
Thanks for your posting and using Aspose.Cells.
We have tested this issue with the following sample code which is same as yours with the latest version: Aspose.Cells for .NET 8.6.1 and found that formula is not calculated for xlsb files. It works fine for xlsx file though.
We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.
This issue has been logged as
- CELLSNET-44030 - SUMIFS Excel function does not work in XLSB format
I have also attached the output xlsx and xlsb files generated by your code for a reference.
C#static void Main()
{
GenerateExcel(SaveFormat.Xlsb, false);
GenerateExcel(SaveFormat.Xlsb, true);
GenerateExcel(SaveFormat.Xlsx, false);
GenerateExcel(SaveFormat.Xlsx, true);
Console.WriteLine(“END OF LINE.”);
}
public static void GenerateExcel(SaveFormat format, bool calcBeforeSave)
{
Console.WriteLine(“Generating: {0}, Calculated: {1}”, format, calcBeforeSave);
Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];
for(int row = 0; row < 100; row++)
{
ws.Cells[row, 0].Value = string.Format(“FOO_{0}”, row % 4);
ws.Cells[row, 1].Value = string.Format(“BAR_{0}”, row == 0 ? 0 : row / 50);
ws.Cells[row, 2].Value = row;
}
Range range1 = ws.Cells.CreateRange(0, 0, 100, 1);
range1.Name = “FILTER_RANGE1”;
ws.Cells.AddRange(range1);
Range range2 = ws.Cells.CreateRange(0, 1, 100, 1);
range2.Name = “FILTER_RANGE2”;
ws.Cells.AddRange(range2);
Range range3 = ws.Cells.CreateRange(0, 2, 100, 1);
range3.Name = “SUM_RANGE1”;
ws.Cells.AddRange(range3);
ws.Cells[0, 4].Formula = “SUMIFS(SUM_RANGE1, FILTER_RANGE1, "FOO_1", FILTER_RANGE2, "BAR_0")”;
string fileName = string.Format(“{0}{1}.{2}”, Guid.NewGuid(), calcBeforeSave ? “-CalcBeforeSave” : “”, format.ToString());
if (calcBeforeSave)
wb.CalculateFormula();
wb.Save(fileName, format);
}