Formula issues - references lost

Aspose.Cells Product Family
1

Hello,

I have the following scenario: the template contains a sheet with formula. The sheet is being copied with different name, then removed. The reference to formula which is the data source for pie chart gets corrupt. Please see sample code below along with spreadsheet attached.

    private static void Main()
    {
        var license = new License();
        license.SetLicense("Aspose.Cells.lic");

        using var workbook = new Workbook("In.xlsx");
        workbook.Worksheets.Add("Interface");
        workbook.Worksheets["Interface"].MoveTo(workbook.Worksheets["EntryType"].Index);
        workbook.Worksheets["Interface"].Copy(workbook.Worksheets["EntryType"]);
        workbook.Worksheets.RemoveAt("EntryType");

        workbook.Save("Out.xlsx", SaveFormat.Xlsx);
    }

Looking forward to your feedback,

Regards,

SergeyIn.zip (68.8 KB)

2

@sgasparyan,

Thanks for the sample code and template file.

I noticed the issue/behavior in the output file when using your sample code with your template file. I then performed the task manually in MS Excel (for copying sheets, I selected all the cells and then right click to “Copy” and in the new sheet, I pasted in it). I then removed the sheet “EntryType” in the workbook. I noticed the chart’s data source is corrupt as well. Could you perform the task in MS Excel manually and let us know if you find something different. If yes, kindly do provide details and output file, we will check it soon.

3

Hi Amjad,

That is fair enough, it is clear that I am not doing some operations to preserve the references in copied sheet. Could you please let me know what steps are missing in my code in order to achieve full correlation.

Regards,

Sergey

4

@sgasparyan,

I am not sure how could you accomplish the task in MS Excel manually. Could you perform the task in MS Excel manually to achieve it and let us know the details, steps involved and your expected output file for our reference, we will check it soon.

5

Hi there,

I have found way to fix it using the excel - you need to go to “Select data source” of the pie chart, then to “Edit series” and manually change the formula reference (set the correct sheet name). Please advise how this can be achieved programmatically.

Looking forward to your reply,

Regards,

Sergey

6

@sgasparyan,

Please try the following code segment with latest version/fix if it suites your needs:
e.g
Sample code:

var workbook = new Workbook("e:\\test2\\In.xlsx");
        workbook.Worksheets.Add("Interface");
        workbook.Worksheets["Interface"].MoveTo(workbook.Worksheets["EntryType"].Index);
        CopyOptions options = new CopyOptions() { ReferToDestinationSheet = false, CopyInvalidFormulasAsValues = true};
        workbook.Worksheets["Interface"].Copy(workbook.Worksheets["EntryType"], options);
        workbook.Worksheets.RemoveAt("EntryType");

        workbook.Save("e:\\test2\\out1.xlsx", SaveFormat.Xlsx);

If it does not suite your needs, provide your expected file (you may manually update the file in MS Excel and save it), we will check it on how to do it via Aspose.Cells.