Aspose.Cells 19.8 and 22.6 corrupt password-protected Excel (.xlsb, .xlsx and .xls) files that contain pivot tables

Aspose.Cells Product Family
1

Excel files that are processed using Aspose.Cells (versions 19.8 and 22.6) seem to be corrupted.

We have created a test application that only opens and saves the file to the new location using Aspose.Cells.
Original files can be opened without any issue.
Newly saved files exhibit strange behavior while opening.

Test project: TestProject.zip

The data sources for the test files must be extracted to C:\Test folder, PowerQuery doesn’t support relative links. Here is the folder that needs to be extracted under C:\Test.

Unzip the TestProject.zip to any location.
Start the application, \TestProject.zip\TestProject\bin\Debug\AsposeCellsTest.exe, select any of the test files located in \TestProject.zip\TestProject\TestFile folder.
The newly saved file will be saved in the same folder as the selected file under a different name: 01_originalfilename.

\TestProject.zip\TestProject\TestFile folder has files without Pivot table.
Those files were created by deleting the worksheet “Summary Dashboard“ from the original customer file.
This worksheet has a Pivot table for datasource.
After deleting it, the file can be opened normally using Excel.
If You try to open and save it using Aspose.Cells, using the test application, Aspose.Cells will through exception: “Object reference not set to an instance of an object“.

Observe the different behavior when opening original and newly saved files using Excel.

If the newly saved file is resaved in the Excel application, everything works normally.

Explained behavior suggests that this is an Aspose.Cells issue, saving the Excel file with the complex Data Model like Pivot table.

We use Aspose.Cells specifically for password-protected Excel files.

2

@vikram.venugopal,

Thanks for sharing sample files and project.

I tried your sample with template files using our latest version/fix: Aspose.Cells for .NET v22.6.1 and found it seems working with 4 out of 8 template files in the TestFile folder inside the project. Mostly Aspose.Cells shows problems/issue for XLSB file formats. See the screenshot on my end when I processed each and every file in the TestFile folder.
sc_shot1.png (35.2 KB)

Did you try with latest version of Aspose.Cells for .NET v22.6.1 and do you see the same display in the list box?

After your confirmation, we will log appropriate ticket for your issue, so it should be fixed precisely.

3

@vikram.venugopal,

After further evaluation, we found there is a relevant bug in the Aspose.Cells APIs. Mainly, it is regarding external connections which are corrupted in the generated file(s). We have logged a ticket with an id “CELLSNET-51506” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

4

Did you try with latest version of Aspose.Cells for .NET v22.6.1 and do you see the same display in the list box?

Yes we did and the behavior noticed is the same.
The Test project we sent, TestProject.zip contains Aspose folder, \TestProject.zip\TestProject\Aspose, with 2 subfolders:

  1. 19.8 - Aspose.Cells version 19.8, currently in the trunk
  2. 22.6 - Aspose.Cells latest version 22.6.1.

We have tested with both versions and the results are the same.

If you want to test it, you just need to change the reference path of the Aspose.Cells DLL from \TestProject\Aspose\19.8 to \TestProject\Aspose\22.6.

5

@vikram.venugopal,

Thanks for confirmation and providing further details.

We have logged it with your existing ticket into our database. Hopefully, your issue will be resolved soon.

Once we have any new information available, we will update you.

1 Like
6

@Amjad_Sahi Any updates?

7

@raghunandan.s,

We are sorry still there is no update on it. We will check if we could provide you latest updates or an ETA on it.

8

@raghunandan.s,

We hope, the issue will be fixed before August 25, 2022.

9

Thanks for the update, @Amjad_Sahi. I’ll look out for your comment on 25 August 2022.

10

@raghunandan.s,

Sure, we will keep you posted on it. Hopefully your issue will be fixed by/before the proposed date.

11

@raghunandan.s,

This is to inform you that your issue has been resolved. The fix will be included in our upcoming release (Aspose.Cells v22.9) scheduled for the second week of September 2022. You will also be notified when the next version is released.

12

Thanks for the update, @Amjad_Sahi!

13

@raghunandan.s,

You are welcome.

14

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

15

First of all, thanks for the update. We did test the latest Aspose.Cells 22.9 using the same test application we sent you’ll earlier (refer: Aspose.Cells 19.8 and 22.6 corrupt password-protected Excel (.xlsb, .xlsx and .xls) files that contain pivot tables) and here is what we found.
After opening and saving excel file using test application we have observed the following,

  1. The newly saved file has decreased in size.- Original file size: 2,023 KB.
  • Size of the file resaved using Aspose.Cells: 1,085 KB.
  1. After trying to open the newly saved file using Excel (Office 365) the following warning messages are displayed.
    image.png (11.3 KB)
  2. In the connections tab of the newly saved file, an additional connection is showing.
    image.png (17.0 KB)
  3. After resaving the file, its size was almost identical to the original file.
  • Original file size: 2,023 KB.
  • Size of the file resaved using Excel: 2,017 KB.
    I am attaching the test project rebuilt using Release configuration with the latest Aspose.Cells.
    ReleaseBuiltTestProject.zip (5.5 MB)
    I am attaching the test XLSB file used for testing, ProtectedTest.xlsb. The file is password protected and the password is “sasap“.
    ProtectedTest.zip (1.9 MB)

The queries used in this file point to our SharePoint, folder “https://linktek.sharepoint.com/Sasa P/Issues/LFA-5920/“, so You can refresh the queries to check if they work after resaving.
Although it looks like everything is ok with the newly saved file, after resaving it in Excel, there are some differences:

  1. The newly saved file doesn’t show values in the “Phased P&L Analysis“ sheet.
  2. Clicking on the properties of the connection “Connection“ in the newly saved file, we can observe that, on the “Used In” tab we do not see the same values as in the original file.
    The screenshots below are showing the difference.

The newly saved file.
image.jpg (380.9 KB)

The original file.
image.jpg (372.4 KB)

We have tested this on a not password-protected XLSB file, attached here.
UnProtectedTest.zip (1.0 MB)

Although there are no warning message boxes shown while opening the file in Excel, there is still a problem with values not shown and the number of references in connection properties “Used in“ tab, just like the ones shown above for password protected file.

After unpacking the XLSB file using 7Zip, more differences can be observed.

The contents of [xl] folder in the packed XLSB file (left is the original file, right is the file opened and saved using Aspose.Cells 22.9).
image.png (95.2 KB)

Besides the obvious difference in file sizes, the metadata.bin and volatileDependencies.bin are missing in the newly saved file.

The contents of the [xl]/[worksheets] folder in the packed XLSB file (left is the original file, right is the file opened and saved using Aspose.Cells 22.9).
image.png (70.0 KB)

All binaryIndex.bin files are removed and sheet4.bin is added to the newly saved file.

We also tested this on another XLSB file and after just opening and resaving the file using Aspose.Cells 22.9 both comments and notes are all replaced with the text from the first comment. Not only that but all the notes are transformed into comments.

Our final conclusion is that we do not think it is safe to use the latest Aspose.Cells, version 22.9, with XLSB files.

16

@vikram.venugopal,

Thanks for providing two XLSB files, application executable file, screenshots and complete details about the issues with other findings.

I did test your scenario/case using your sample application with both Excel files and found the issues you pointed out via some screenshots. I have logged a separate ticket with an id “CELLSNET-52086” for your issue. We will evaluate your issue in details and try to figure it out soon.

17

@vikram.venugopal,

This is to inform you that your issue (logged earlier as “CELLSNET-52086”) has been resolved now. The fix will be included in our upcoming release (Aspose.Cells v22.11) which is scheduled in the first half of November, 2022. You will also be notified when the next version is released.

Please note, the binaryIndex.bin file is the cached index of Excel, and does not need to be saved.

18

Thanks for the update @Amjad_Sahi.

19

@vikram.venugopal,

You are welcome.

20

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