DesignChart not available on copied sheet

Aspose.Cells Product Family
1

Hi Laurence,

I am copying an existing worksheet to a new worksheet and want to update the chart so that its formula(s) are the pointing at the new sheet. However, when I check the DesignChart object for the copied sheet it is empty although there is 1 designchart on the original sheet. The chart is shown on the sheet in Excel after save.

Any ideas?

Thanks,

David

2

Do you use Worksheet.Copy method to copy it? Which version of Aspose.Cells are you using? I will check and fix this issue soon.

3

Laurence,

I used AddCopy to copy it. The Aspose version is 4.0.1.7

David

4

Hi David,

Please try this attached version.

5

Hi Laurence,

The designchart now shows up on the copied sheet. However, the NSeries count is 0 for both the original and copy (which may be correct as I'm not sure what properties I should be updating) even though a series has been entered on the chart. Can you tell me how I update all the charts formula references (the data range and series values in Excel) so that after the copy the charts formulas point to the same sheet that it is on (this is what happens when a sheet is copied in Excel) rather than the original sheet?

Thanks,

David

6

You don't need to care the NSeries count in design chart. Just change it with code:

designCharts[0].NSeries.Add("B1:B3", true);

However, the number of new data series should be same as the number in your template file.

7

Hi Laurence,

The idea is that I'm copying a sheet which may or may not have a chart on it but if it does I need to set any references to the original to point to the new sheet. If I look at the Source Data for the chart in Excel the Data Range, Series (the name, values and category (xy) axis labels) are all using formulas which need to be updated.

As I don't know the number of series (each spreadsheet I am processing will be different) in the chart I was trying to use the count so that I could loop around updating them (or if it couldn't be updated as the examples I found on the forum implied reading them into an array and clearing the current ones then putting them back with an updated formula). However, even though there is 1 series on the chart the count is 0 so I can't work out what the current series is/are and therefore can't update them.

David

8

Laurence,

I've just noticed something else with the charts formulas. In my program I have 3 sheets to start with, one of which I copy multiple times in the workbook, and then I delete the first 3 sheets. The chart formulas for the first 3 copied sheets show #REF in them, with the 4th sheets chart referencing the 1st sheets data, the 5th sheets chart referencing the 2nd sheets data and so on.

David

9

For count of NSeries, I will check and fix it soon.

Could you post a sample file to show the problem of chart formula reference? Thank you very much.

10

Laurence,

I have attached a sample project that demonstrates the problem. When creating the project I noticed that if you don't delete any sheets the charts formulas are updated such that they point at the current sheet rather than the original one (which is what I wanted and was expecting it to do). If however you delete the some of the sheets, as I am doing in my project, the references then go out which was what I was getting and wasn't expecting.

David

11

Hi David,

Thanks for your project. Please try this attached version. It's fixed.

12

Laurence,

Thanks for the fix.

David

13

hi laurence,

good afternoon,

I have a user requirement and I need to change the source of my nseries. I tried to include

sample code you gave but i got an error message, invalid series data.Here is my sample code.

xls.Worksheets(shtindex).DesignCharts(0).NSeries.Add("B52:IV52", False)
xls.Worksheets(shtindex).DesignCharts(0).NSeries.Add("B54:IV54", False)
xls.Worksheets(shtindex).DesignCharts(0).NSeries.Add("B56:I56", False)
xls.Worksheets(shtindex).DesignCharts(0).NSeries.CategoryData = "B50:IV50"

hope to hear from you.

thanks,

Angelo


14

Hi,
I think you are using some older version of Aspose.Cells component. Kindly try the latest version of Aspose.Cells and change your code to.
E.g.,

xls.Worksheets(shtindex).Charts(0).NSeries.Add("B52:IV52", False)
xls.Worksheets(shtindex).Charts(0).NSeries.Add("B54:IV54", False)
xls.Worksheets(shtindex).Charts(0).NSeries.Add("B56:I56", False)
xls.Worksheets(shtindex).Charts(0).NSeries.CategoryData = "B50:IV50"

Thank you.

15

Hi,

Thanks for immedialte reply,I have not tried the hotfix yet but I already downloaded it.

I would like to ask question first before I install the hotfix. If the hotfix works do we need to buy the latest version even if we already have the old version?

Thank you.

Angelo

16

Hi,

Thanks for considering Aspose.

Well, I think since you are using some older version of the component and your subscription might have expired ( you may check the expiry date opening the license file into notepad ). If your license has expired and you want to use a version which is released after your expiry date, you have to update your suscription to use the later version(s).

Thank you.