Weird behavior when using SWITCH statement Array Formula and outputting as pdf

Aspose.Cells Product Family
1

Hello,

I’m using Aspose.Cells for .NET. I’m experiencing very strange behavior when using SWITCH statement in the Array Formula after upgrading to the latest version 23.2.0 (from version 22.1.0) several days ago.

In the following formulas, SOURCE_DATA is a named range. What I’m trying to do is attach a tag like (4) behind the string if the playname equals to ExpressShow, or (10) if there is no matching found.

Expected result:
BroadwayShow(10)
ExpressShow(4)
ImmersiveShow(10)

The following query only has the first row populated when I output as pdf:

=INDEX(OFFSET(SOURCE_DATA,1,0,ROWS(SOURCE_DATA)-1,COLUMNS(SOURCE_DATA)),MATCH(“playname”,INDEX(SOURCE_DATA,1,0),0))&SWITCH(INDEX(OFFSET(SOURCE_DATA,1,0,ROWS(SOURCE_DATA)-1,COLUMNS(SOURCE_DATA)),MATCH(“playname”,INDEX(SOURCE_DATA,1,0),0)),“ExpressShow”,"(4)","(10)")

Result:
BroadwayShow(10)

if I took off the default value, then all cells covered by the whole array formula could be populated, but the “(4)” is NOT attached to ExpressShow.

=INDEX(OFFSET(SOURCE_DATA,1,0,ROWS(SOURCE_DATA)-1,COLUMNS(SOURCE_DATA)),MATCH(“playname”,INDEX(SOURCE_DATA,1,0),0))&SWITCH(INDEX(OFFSET(SOURCE_DATA,1,0,ROWS(SOURCE_DATA)-1,COLUMNS(SOURCE_DATA)),MATCH(“playname”,INDEX(SOURCE_DATA,1,0),0)),“ExpressShow”,"(4)","")

Result
BroadwayShow
ExpressShow
ImmersiveShow

Everything is working as expected if I change from SWITCH to IF:

=INDEX(OFFSET(SOURCE_DATA,1,0,ROWS(SOURCE_DATA)-1,COLUMNS(SOURCE_DATA)),MATCH(“playname”,INDEX(SOURCE_DATA,1,0),0))&if(INDEX(OFFSET(SOURCE_DATA,1,0,ROWS(SOURCE_DATA)-1,COLUMNS(SOURCE_DATA)),MATCH(“playname”,INDEX(SOURCE_DATA,1,0),0))=“ExpressShow”,"(4)","")

I debugged into the code and found that after wb.RefreshDynamicArrayFormulas(), the calculatedresult only contains the show’s name like ExpressShow and doesn’t have <sup>(4)</sup> attached.

So I suspect the behavior of SWITCH statement is changed. Could you please advise if anything is changed?

Thank you,

2

@robinlou
Could you post a template file? We will check it soon.

3

Thank you Simon.
SampleTemplate.7z (13.5 KB)

Basically it looks like this in Excel:
image.png (8.6 KB)
And SOURCE_DATA is range H1:H4:
image.png (13.7 KB)

From the first screenshot we could see the (4) is attached before going through aspose processing. But the generated pdf looks like this:
image.png (22.9 KB)

4

@robinlou,

Thanks for the template Excel file and screenshots.

It looks like you are not using latest version/fix: Aspose.Cells for .NET v23.2 (Releases | NuGet). Please make sure to use latest version/fix. I did test your scenario/case with your template file using the following lines of code and it works fine. The output PDF is Ok.
e.g.
Sample code:

Workbook workbook = new Workbook("e:\\test2\\SampleTemplate.xlsx");
workbook.Save("e:\\test2\\out1.pdf");

Please find attached the output PDF file for your reference.
out1.pdf (16.6 KB)

If you still find the issue, kindly do provide output PDF by Aspose.Cells for .NET v23.2 which has incorrect contents. Also, share your expected PDF, you may render PDF in MS Excel manually. We will check your issue soon.

5

Thank you for the response Amjad. I’m using version 23.2.0.
image.png (10.2 KB)

The issue is happening if I add workbook.RefreshDynamicArrayFormulas(true) or workbook.CalculateFormula();

[HttpPost]
public IActionResult AsposeCellsSwitchTest(IFormFile templateFile)
{
using var templateStream = new MemoryStream();
templateFile.CopyTo(templateStream);
Workbook workbook = new Workbook(templateStream);
workbook.RefreshDynamicArrayFormulas(true);
workbook.CalculateFormula();

using var ms = new MemoryStream();
workbook.Save(ms, SaveFormat.Pdf);
return File(ms.ToArray(), "application/pdf", $"{Guid.NewGuid()}.pdf");

}

It works fine if I remove those two lines (import and export only):

[HttpPost]
public IActionResult AsposeCellsSwitchTest(IFormFile templateFile)
{
using var templateStream = new MemoryStream();
templateFile.CopyTo(templateStream);
Workbook workbook = new Workbook(templateStream);

using var ms = new MemoryStream();
workbook.Save(ms, SaveFormat.Pdf);
return File(ms.ToArray(), "application/pdf", $"{Guid.NewGuid()}.pdf");

}

In our use case, we must do calculation through RefreshDynamicArrayFormulas and CalculateFormula after upload and before return.

Here are output files for both cases:
good output (without calculation).pdf (22.4 KB)
bad output (with calculation).pdf (21.3 KB)

6

@robinlou
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-52752

You can obtain Paid Support services if you need support on a priority basis, along with the direct access to our Paid Support management team.

7

Thank you very much Simon. Hope it could be fixed in the next release.

8

@robinlou,

Sure, we will try to figure it out soon.
Once we have updates on it, we will let you know.

1 Like
9

@robinlou,

This is to inform you that your issue has been resolved. The fix will be included in our upcoming release (Aspose.Cells v23.3) that we plan to release in the first half of March 2023. You will be notified when the next version is released.

10

This is great. Thank you so much @amjad.sahi!

11

@robinlou,

You are welcome.

12

The issues you have found earlier (filed as CELLSNET-52752) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

13

Thank you very much for fixing this issue! I’ve updated the version and verified it’s working appropriately.

14

@robinlou
You are welcome!
If you have any other questions, feel free to contact us!