Getting exception while loading excel using Aspose.Cells.Workbook(fstream, loadOptions)

Aspose.Cells Product Family
2

@nraj,

Thanks for the sample files and details.

First of all I would like to comment on 2) and 3). Please note, Aspose.Cells follows and mimics MS Excel standards and specifications when loading/opening the Excel files via the APIs. I found when I tried to open the files from “File format and extension” folder in MS Excel (manually), I found MS Excel could not open the files being corrupted. When loading/reading the files from this folder, Aspose.Cells will also throw exception “xml file is corrupted” and rightly so. Similarly, when I tried to open the files from “Microsoft graphs and gallery files” folder in MS Excel (manually), I found MS Excel could not open the files being Microsoft chart gallery files. When loading/reading the files from this folder, Aspose.Cells throws an exception “This Excel files contains Microsoft Graph Charts” and rightly so. So, for 2) and 3), there is no issue on Aspose.Cells’ end.

Now coming to 1) and 4), you are right, I got the same results as per your findings. Here is the detail for both.

1). I was able to reproduce the issue as you mentioned when loading the file from folder “Issue with content” using the following sample code. I checked/tested and MS Excel can open the file fine finally with a few prompts/error messages.

FileStream fstream = new FileStream("g:\\test2\\test_files\\Issue with content\\NERV001_00014363.xls", FileMode.Open);// File is corrupted - Not Ok as MS Excel can open the file fine

var loadOptions = new LoadOptions(LoadFormat.Auto);
//Load stream from file store.
var workbook = new Aspose.Cells.Workbook(fstream, loadOptions);//exception.

I have logged a ticket with an id “CELLSNET-55254” for the issue. We will look into your issue soon.

PS. I also tested with other files in “Issue with content” folder and Aspose.Cells is rightly giving the exception “File is corrupted” as when opening the other files (except the above mentioned in the code segment), I could not open other files into MS Excel manually. So, the ticket “CELLSNET-55254” is logged for single file only.

4). I was able to reproduce the issue as you mentioned when loading the files from “Protected view files” folder using the following sample code. I checked/tested and MS Excel can open the files fine but in Protected View.

//FileStream fstream = new FileStream("g:\\test2\\test_files\\Protected view files\\NERV001_00002406.xls", FileMode.Open); //Index was outside the bounds of the array. Not Ok, MS Excel can open the file fine in Protected view
//FileStream fstream = new FileStream("g:\\test2\\test_files\\Protected view files\\NERV001_00020032.xls", FileMode.Open); //Index was outside the bounds of the array. Not Ok, MS Excel can open the file fine in Protected view
FileStream fstream = new FileStream("g:\\test2\\test_files\\Protected view files\\NERV001_00033652.xls", FileMode.Open); //Index was outside the bounds of the array. Not Ok, MS Excel can open the file fine in Protected view

var loadOptions = new LoadOptions(LoadFormat.Auto);
//Load stream from file store.
var workbook = new Aspose.Cells.Workbook(fstream, loadOptions);//exception.

I have logged another ticket with an id “CELLSNET-55255” for this issue. We will look into the details of the issue.

Once we have an update on any of the issues, we will let you know.

3

@nraj
1,File format and extension
We will throw an Exception “The file format and extension do not match.” in the next version.

2,Protected view files
There are some corrupted files. We will ignore those error data in the next version.

4

@nraj
The file is corrupted, so a corrupted file exception is thrown.
If you want to ignore it, please try the following codes:

class CppWarningCallback : IWarningCallback
    {
        public void Warning(WarningInfo warningInfo)
        {
            switch (warningInfo.WarningType)
            {
              
                case WarningType.IO:
                case WarningType.InvalidData:
                case WarningType.Limitation:
                    return;
                default:
                    return;
            }
        }
    } 
 LoadOptions loadOptions = new LoadOptions();
            loadOptions.WarningCallback = new CppWarningCallback();
            FileStream fstream =File.OpenRead("C:\\Users\\Administrator\\Downloads\\test_Files\\Issue with content\\NERV001_00014363.xls");
              Workbook wb = new Workbook(fstream, loadOptions);
            fstream.Close();

@amjad.sahi @John.He

5

@nraj,

  • To update you on your issue (ticket id: “CELLSNET-55254”), we won’t fix it as the file “NERV001_00014363.xls” is corrupted. Please see and try the suggested code by @simon.zhao in the post to ignore or bypass the error.

  • Regarding the second issue (ticket id: “CELLSNET-55255”), we have fixed it. The fix/enhancement will be included in the next release (Aspose.Cells v24.3) scheduled for release this week or next week in March. You will be notified when the next version is published.

6

I am appreciative of your prompt response. I appreciate the changes and eagerly await your upcoming release.

7

@nraj
You are welcome. Once the version v24.3 is released, we will notify you immediately.

8

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

9

I appreciate the update. Aspose has been upgraded to the recommended version. These are the things I’ve noticed.
I attempted to use the below attached sheet, but it failed with the message “Index was out of range. Must be non-negative and less than the size of the collection. (Parameter ‘startIndex’)”.

NERV001_00016832.zip (888.2 KB)

Additionally, when I attempted to save the previously shared sheet NERV001_00033652.xls using workbook.Save(“pro.xls”, SaveFormat.Auto) and tried viewing it, the spreadsheet automatically closes when you get to Sheet 1.

10

@nraj
We can reproduce the issue by testing it on the latest version v24.4 using the following code. Discovered ArgumentOutOfRangeException when loading XLS files via stream.
Additionally, when we open the newly provided sample file, a protection view prompt message appears. Please refer to the attachment. result.png (106.1 KB)

FileStream fstream = new FileStream(filePath + "NERV001_00016832.xls", FileMode.Open);

var loadOptions = new LoadOptions(LoadFormat.Auto);
//Load stream from file store.
var workbook = new Aspose.Cells.Workbook(fstream, loadOptions);//exception.

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-55686

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.

11

@nraj,

We are pleased to inform you that your issue (logged as “CELLSNET-55686”) has been resolved. The fix/enhancement will be included in an upcoming release (Aspose.Cells v24.5) that we plan to release this week. You will be notified when the next version is released.

12

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

13

Loading workbook is working fine but copying the workbook data to other workbook and saving it is failing with error “Index was out of range. Must be non-negative and less than the size of the collection. (Parameter ‘index’)” for this protected view files. Please find attached the console application to test the same.

Index.zip (1.9 MB)

14

@nraj
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-55813

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.

15

@nraj
1). If you want to copy all worksheets to another workbook, please simply call Workbook.Copy().
2). If you want to copy worksheets one by one, please add the worksheets first, then copy them as follows:

 foreach (Worksheet ws in workBook.Worksheets)
 {
     string validSheetName = CellsHelper.CreateSafeSheetName(ws.Name);
     Converted.Worksheets.Add(validSheetName);
 }
     foreach (Worksheet ws in workBook.Worksheets)
     {
         sourceCells = ws.Cells;
         string validSheetName = CellsHelper.CreateSafeSheetName(ws.Name);
         targetCells = Converted.Worksheets[validSheetName].Cells;
         Converted.Worksheets[validSheetName].TabColor = ws.TabColor;
         //Unhide/Unfreeze/UnMerge/Transfer Images, Headers
         PrepareWorksheet(ws, Converted.Worksheets[validSheetName]);
         TransferCells(ws, Converted, sourceCells, targetCells);
     }

If you copy a worksheet to another workbook and there are no referenced worksheets by this worksheet, Excel will add an external link to the source workbook. This issue is caused by creating external links, and we will check it soon.

16

@nraj
Please check the document about copying worksheet:

17

I didn’t understand what you were saying. Could you please clarify? Are you proposing that I apply the code you mentioned above to address the problem, or are you indicating that you will fix something after checking it?

To be clear, we are largely copying data and making a few modifications rather than just copying straight from one workbook to another.

Additionally, we have been using the code I gave for a very long time, and it just broke protected view spreadsheet—it didn’t affect any other spreadsheet. What do you think about this?

18

@nraj
Thank you for your feedback.

As @simon.zhao replied earlier, We have located that this issue is caused by the creation of external links. We will further study and solve this issue. Once there is an update, we will notify you immediately.

19

@nraj

  1. If you copy a part of a worksheet to another workbook and there are some formulas referring to another worksheet, you have to change those formulas to refer to the original workbook. For example, the formula “=Sheet2!A1” in worksheet “Sheet1” of book1.xlsx, if you copy it to book2.xlsx, the formula should still refer to worksheet “Sheet1” of book1.xlsx, so the formula will be [book1.xlsx]sheet2!A1 in book2.xlsx.
  2. After closely looking into this issue, we found that the issue is caused by copying a chart from the worksheet “Exchange Rates”, where the source is a defined name range. If you only copy the chart, we cannot know whether the defined name should be copied or has been copied. We can only refer to this name in the original file. As the defined name and chart belong to the same worksheet, we will try to refer to the new worksheet to fix it.
20

@nraj,

We are pleased to inform you that your issue (CELLSNET-55813 - ArgumentOutOfRangeException when saving file after copying the workbook data to other workbook) has been resolved. The fix will be included in an upcoming release (Aspose.Cells v24.6) that we plan to release in the first half of June 2024. You will be notified when the next version is released.

21

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