We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

SUMIFS Excel function does not work in XLSB format

Aspose team,

When creating and saving an Aspose.Cells Workbook as .XSLB, all cells with the SUMIFS() formula show up as #NAME. If the same Workbook is saved as XLSX, the formula shows up properly.

Example formula: =SUMIFS(SUM_RANGE, FILTER_RANGE1, "Filter 1", FILTER_RANGE2, "Filter2")

Oddly enough, though: when you open the XLSB, edit the cell and hit enter, the formula calculates as expected.

I have attached a sample project that reproduces the issue. Also, this forum post seems to be related: https://forum.aspose.com/t/97548

Using Aspose.Cells .Net v8.6.1.0

Thank you.

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);
}

Hi,


This is to update you that we have fixed the issue logged earlier as CELLSNET-44030. We will shortly provide the fix here after ensuring the quality and incorporating other enhancements.

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.6.1.2 and let us know your feedback.

The issues you have found earlier (filed as CELLSNET-44030) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.