Error when copying sheet from one book to another


#1

Hello,
I’m using the latest Aspose.Cells for .NET. I am trying to merge several sheets together into a single workbook. It seems to work in general, but I have a problem with some specific reports that contain charts. When I have two books, I can merge a sheet from the second into the first with no issues. When I try to add a third, the copy gives an error. If I add Thread.Sleep(1000) before the copy command, it works. Is there a better way to do the copy, or something I can check within the book instead of doing sleep?

Here is the part of the code that does the copy:
using (var book = new Workbook(reportStream))
{
var sourceSheet = book.Worksheets.First();
var newSheet = mergedBook.Worksheets.Add(reportName);
Thread.Sleep(1000);
newSheet.Copy(sourceSheet);
}

And here is the error:
System.IndexOutOfRangeException : Index was outside the bounds of the array.
at Aspose.Cells.CopyOptions.\u0002(Int32 \u0002, Int32 \u0003, Int32 \u0005)
at Aspose.Cells.CopyOptions.\u0002(\u0005​ \u0002, Int32 \u0003)
at Aspose.Cells.CopyOptions.\u0005(Int32 \u0002, Int32 \u0003)
at \u000e .\u0005(Byte[] \u0002, Int32 \u0003, CopyOptions \u0005)
at \u000e .\u0003()
at \u000e .\u0002(Byte[] \u0002, Int32 \u0003, Int32 \u0005, Int32 \u0008, CopyOptions \u0006)
at \u0002 .\u0002(\u0002 \u0002, Int32 \u0003, CopyOptions \u0005)
at Aspose.Cells.Charts.Series.\u0002(Series \u0002, CopyOptions \u0003)
at Aspose.Cells.Charts.SeriesCollection.\u0002(SeriesCollection \u0002, CopyOptions \u0003)
at Aspose.Cells.Charts.Chart.\u0002(Chart \u0002, CopyOptions \u0003)
at Aspose.Cells.Drawing.ShapeCollection.\u0002(Shape \u0002, GroupShape \u0003, CopyOptions \u0005)
at Aspose.Cells.Drawing.ShapeCollection.\u0002(ShapeCollection \u0002, CopyOptions \u0003)
at Aspose.Cells.Worksheet.\u0002(Worksheet \u0002, CopyOptions \u0003)
at Aspose.Cells.Worksheet.Copy(Worksheet sourceSheet, CopyOptions copyOptions)


#2

@MGraney,

Thanks for the error trace and details.

Could you provide us your template Excel file(s) and preferably a sample console application (runnable) (you may zip the project excluding Aspose.Cells.Dll assembly prior attaching it here) and post us here to reproduce the issue on our end, we will check it soon.


#3

I’m attaching a zip of a program with sample files. Thanks!

ConsoleApp1.zip (63.9 KB)


#4

@MGraney

Thanks for using Aspose APIs.

Please download and try the most recent version (i.e. Aspose.Cells for .NET 17.12.6). It works fine and it should resolve your issue. Please also see the output Excel file generated with the recent version. As you can see, the output Excel file is quite right.

Download Link:
Output Excel File.zip (40.3 KB)


#5

Thanks. I downloaded version 17.12.6, and it did not solve the problem. It works occasionally, but usually I still get the same error. I believe the console app that I sent had the Thread.Sleep call which makes it work; if you take that line out you may see the issue.


#6

@MGraney

Thanks for using Aspose APIs.

We were able to observe the exception after commenting Thread.Sleep as shown in the following screenshot. We have logged this issue in our database for product team investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-45850 - Error when copying sheet from one book to another

Screenshot of Error:


#7

@MGraney

Thanks for using Aspose APIs.

This is to inform you that we have fixed your issue CELLSNET-45850 now. We will soon provide the fix after performing QA and including other enhancements and fixes.


#8

@MGraney

Thanks for using Aspose APIs.

Please download and try the following fix and let us know your feedback.


#9

Hi,

I get a defect workbook when copiing one sheet to another workbook:

private static void Test23_CopyWorksheet()
{
const string srcFilePath = @“C:\temp\DataTest\AsposeTest” + nameof(Test23_CopyWorksheet) + @"\SrcBook.xlsm";
const string tgtFileBasePath = @“C:\temp\DataTest\AsposeTest” + nameof(Test23_CopyWorksheet) + @"\TgtBookBase.xlsm";
const string tgtFilePath = @“C:\temp\DataTest\AsposeTest” + nameof(Test23_CopyWorksheet) + @"\TgtBook.xlsm";
const string sheetName = “Mold1”;

Workbook wbSource = new Workbook(srcFilePath);
Workbook wbTargetBase = new Workbook(tgtFileBasePath);

Worksheet sheetTgt = wbTargetBase.Worksheets.Add(sheetName);
sheetTgt.Copy(wbSource.Worksheets[sheetName]);
wbTargetBase.Save(tgtFilePath);

}

Test23_CopyWorksheet.zip (462.4 KB)

Did not work since at least 17.10.0, but it worked well with a few version before.
grafik.png (4.4 KB)

Additionally after click on yes for repair, Excel says the workbook has some “bindings” (Sry, i don´t know what it is called in english, i´m only having the german version)

grafik.png (17.4 KB)

Why is this “binding” created? I only copied the worksheet. This might be the cause for the defect workbook.


#10

@sabrinaluedtke,

Thanks for the sample files and sample code.

After an initial test, I observed the issue as you mentioned by using your sample code with your template files. I found that the output XLSM is corrupt file when copying a sheet b/w workbooks. I have logged a separate ticket with an id “CELLSNET-45857” for your issue. We will look into it soon.

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


#11

It’s working great so far. Thanks!


#12

@MGraney

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved with the latest fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.


#13

The issues you have found earlier (filed as CELLSNET-45850) have been fixed in this Aspose.Cells for .NET 18.1 update.

Please also check the following article:


#14

@MGraney

Thanks for using Aspose APIs.

This is to inform you that we have fixed your issue CELLSNET-45857 now. We will soon provide the fix after performing QA and including other enhancements and fixes.


#15

@MGraney

Please download and try the following fix and let us know your feedback.


#16

@shakeel.faiz

Thanks for the fix. Works fine.


#17

@sabrinaluedtke,

Good to know that your issue is sorted out by the new fix/version. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.


#18

The issues you have found earlier (filed as CELLSNET-45857 ) have been fixed in this Aspose.Cells for .NET 18.2 update.

Please also check the following article: