SUMIF(..) in xps not working

Aspose.Cells Product Family
1

Hi,


Attached a sample project. Cell E10 has a formula SUMIF(Sheet2!$Q:$Q;$A10;Sheet2!D:D) it works fine in Excel but if i export it to XPS the cell has a different value (See the attached screenshot).

Hope someone can help me with this problem.

Thanks in advance,

Willem-Jan
2

Hi Willem-Jan,

Thanks for your posting and using Aspose.Cells for .NET.

We have tested your issue with the latest version: Aspose.Cells
for .NET v7.7.1.4 and it is working fine.

Please download and use the latest version from the above link and it should fix your issue.

We have attached the output xps for your reference.

3

Hi,


thanks for the fast reply, i just tried it with the latest version 7.7.1.4 but the problem still occurs (See the screenshot). Maybe i’m forgetting something?

Best regards,

Willem-Jan


4

Hi Willem-Jan,

Thanks for your posting and using Aspose.Cells.

I have rechecked this issue but unable to replicate it. Probably you are still referencing the older version. I checked it with the following code. I have attached a screenshot that shows my settings of Aspose.Cells reference. Here the Copy Local attribute is True. Please check it at your end, may be it is causing this issue.

You can also check which version of Aspose.Cells assembly is loaded at your end using the following line.

Console.WriteLine(CellsHelper.GetVersion());


C#


Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(“prämien.xlsx”);


workbook.CalculateFormula(true);


Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions();

options.SaveFormat = Aspose.Cells.SaveFormat.XPS;

options.PrintingPage = Aspose.Cells.PrintingPageType.Default;

options.OnlyArea = false;

options.OnePagePerSheet = true;


Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(workbook.Worksheets[0], options);

if (sr.PageCount > 0)

{

for (int i = 0; i < sr.PageCount; i++)

{


MemoryStream msOld = new MemoryStream();

sr.ToImage(i, msOld);

byte[] bAr = msOld.ToArray();

msOld.Close();


MemoryStream ms = new MemoryStream(bAr);

using (FileStream file = new FileStream(“Test” + Guid.NewGuid().ToString() + “.xps”, FileMode.Create, System.IO.FileAccess.Write))

{

byte[] bytes = new byte[ms.Length];

ms.Read(bytes, 0, (int)ms.Length);

file.Write(bytes, 0, bytes.Length);

ms.Close();

}

}

}



workbook.Save(“Test” + Guid.NewGuid().ToString() + “.xlsx”);
Screenshot:
5

Hi,


I believe you that its working but i can’t get it to work, i’m using this solution for some other Aspose bugs too never had problems, but to be sure i made a new solution and added System.Diagnostics.Debug.WriteLine(Aspose.Cells.CellsHelper.GetVersion());
the output is: 7.7.1.4 (See the screenshot).

I added the new solution with the 7.7.1.4 dll as attachement.

Best regards,

Willem-Jan



6

Hi Willem-Jan,

Thanks for your feedback and using Aspose.Cells.

We are not able to replicate the issue with your project and everything is working fine. Could you please let us know your System settings like OS, .NET Framework etc.

Please also let us know your Control Panel Date Time and Regional Settings.

Please also convert your source xlsx file into pdf using the following code and attach the output pdf for our reference.

C#


Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(“F:\Shak-Data-RW\Downloads\AsposeFormulaTest\prämien.xlsx”);

workbook.CalculateFormula(true);


workbook.Save(“output.pdf”);

7
Hi,

CurrentCulture = de-DE
CurrentUICulture = en-US

I changed the currentCulture in en-US and then it worked, but if i change both to de-DE it isn't working. Attached both pdf's.

Windows: Windows Server 2008 R8 Enterprise, Windows Server 2012 Standard
Time Zone: (UTC+01:00)
Framework: .NET 4.5

Best regards,

Willem-Jan
8

Hi Willem-Jan,

Thank you for your continues help and support.

We are able to observe the said problem on our end while using the latest build of Aspose.Cells for .NET 7.7.1.4. It seems that changing the thread’s CurrentCulture to “de-DE” incorrectly calculate the SUMIF formula before rendering the spreadsheet to PDF and XPS formats. A ticket (CELLSNET-42354) has been logged in our bug tracking system to probe further into this matter. Please spare us little time to properly analyze the problem cause, and to provide a fix at earliest (if applicable). In the meanwhile, we will keep you posted with updates in this regard.

Attached is the sample project used to replicate the problem on our end.

9

Hi,

Thanks for using Aspose.Cells.

We have fixed this issue.

Please download and try the latest version: Aspose.Cells
for .NET v7.7.1.7 and let us know your feedback.

10

Hi,


Thanks it works! Keep up the good work!

Best regards,

Willem-Jan
11

Hi Willem-Jan,

Thanks for your feedback and using Aspose.Cells.

It
is good to know that your issue is resolved with the latest version.
If you encounter any other issue, please feel free to post on our
forums, we will be glad to help you further.