Workbook Chart with CopyOptions option referring to wrong sheet

Aspose.Cells Product Family
1

I have a requirement where need to copy worksheet with Chart to another Workbook. Once chart is copied that still refers to wrong sheet. I am using Aspose.Cells version 18.6.0.0.
In the sample project, As worksheet is copied to “Chart And Data1” so Chart Data Source should also refer to “Chart And Data1” sheet

Uploaded the image and Sample project. Please let me know in case of any more information you need:

image.png (80.6 KB)

Thanks,
Abhishek

SampleApplication.zip (3.9 MB)

2

@kumarabhishek06,

Thanks for the sample project, template files and screenshot.

After an initial test, I am able to observe the issue as you mentioned by using your sample project with your template file. I found that workbook chart with CopyOptions (ReferToDestinationSheet=true) option is still referring to wrong sheet as per your screenshot. I have logged a ticket with an id “CELLSNET-46402” for your issue. We will look into it soon.

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

3

@kumarabhishek06,

Please change your codes as:

        Workbook wbMain = new Workbook(dir + "wbMain.xlsx");
        Workbook wb = new Workbook(dir + "wb.xlsx");
        //Tried with all the properties of Aspose
        CopyOptions co = new CopyOptions() { CopyInvalidFormulasAsValues = true, CopyNames = true, ReferToDestinationSheet = true };

        wbMain.Worksheets["Ret - Data1"].Copy(wb.Worksheets["Ret - Data"], co);
        //As data is copied in "Chart And Data1" so Chart Data Source should also refer to "Chart And Data1" sheet
        co = new CopyOptions() { CopyInvalidFormulasAsValues = true, CopyNames = true, ReferToDestinationSheet = true };
        wbMain.Worksheets["Chart And Data1"].Copy(wb.Worksheets["Chart And Data"], co);
        wbMain.Save(dir + "dest.xlsx", SaveFormat.Xlsx);

When copying the worksheet with CopyOptions, the API will cache some data in CopyOptions, so please call same CopyOptions object for several times.

4

THanks for your response. Your solution work when we have Chart and Datasheet in same worksheet.
It breaks when Chart refers to different worksheet for data. Attached the modified solution where it breaks.

SampleApplication.zip (3.9 MB)

5

@kumarabhishek06,

Thanks for the updated project.

In that case (if data source and charts are in different sheets), you may comment out/remove the second initialization of CopyOptions as it is not needed:
e.g
Sample code:

 Workbook wbMain = new Workbook(new MemoryStream(Properties.Resources.wbMain));
            Workbook wb = new Workbook(new MemoryStream(Properties.Resources.wb));
            //Tried with all the propeties of Aspose
            CopyOptions co = new CopyOptions() { CopyInvalidFormulasAsValues = true, CopyNames = true, ReferToDestinationSheet=true };           

            wbMain.Worksheets["Ret - Data1"].Copy(wb.Worksheets["Ret - Data"], co);
            //As data is copied in "Chart And Data1" so Chart Data Source should also refer to "Chart And Data1" sheet
            //co = new CopyOptions() { CopyInvalidFormulasAsValues = true, CopyNames = true, ReferToDestinationSheet = true };
            wbMain.Worksheets["Chart And Data1"].Copy(wb.Worksheets["Chart And Data"], co);

           wbMain.Save("Output.xlsx", SaveFormat.Xlsx);

Hope, this helps a bit.

6

We have a requirement where both the things should work together. Need to merge Workbook with multiple sheets with Chart, some have Chart and Data in same sheet and other Chart and Data in separate sheet. Please let me know if there is a way to get both the things working.

7

@kumarabhishek06,

I guess you may evaluate if the data source for the chart is in other sheet or not, so you may decide whether to re-instantiate CopyOptions for it in the code.

Hope, this helps a bit.