AddCopy and chart data source in .NET

Hello,

I have a template worksheet in my excel file. It contains a data table and a chart. The chart is based on the data table. When I copy the template worksheet (Worksheet 1) in Excel, I get a new Worksheet (Number 2). The new chart in Worksheet 2 references the new data table in Worksheet 2.

When I do an AddCopy in Aspose.Excel, the new chart in worksheet 2 references the original data table in worksheet 1. The reason for this is the data source of the chart, which includes the name of the sheet (e.g. “MyTemplateSheet!$A$1:$C$7”). Excel obviously changes that reference, when you copy a sheet but Aspose.Excel does not.

Is there a solution for this problem? Is there a way to adjust the data source of the chart using the Apsose.Excel API?

Thanks for your help.

One Addition: When I try to use then expression

sheet.Charts.Count

it returns 0, though there is a chart inside the worksheet (created with Excel XP). The expression

sheet.Charts(0)

also returns an error.

Dear Christopher,

Currently, AddCopy method of Aspose.Excel acts as the following steps in Excel:

1. Insert a new worksheet
2. Select all of the source worksheet
3. Paste in the new worksheet

So, the data source of chart is not changed.

The chart of designer file cannot be manipulated by Aspose.Excel api. The Chart and Charts objects are only used to create charts by api.

To meet your requirement, I suggest that you can use Chart/Charts objects to create charts and manipulate them by api.

I will add a Copy method to Charts object to facilitate this action.

Thus, you can do the following:

1.Use AddCopy to copy a worksheet(not include the charts)
2.Use Chart/Charts object to create source charts in the source worksheet
3.Use Charts.Copy method to copy charts to the destination worksheet

Can this solution meet your needs? If yes, I will add the Copy method to Charts object in the next hotfix within one week.

Hello Laurence,

Thank you very much for your reply.

The problem is the complexity of the chart. It is provided by our customer and he is allowed to change it. To create the Chart by API would be very inflexible und time consuming.

I have written a small Excel macro that duplicates the template sheet several times (100 or more). In my program I use a fresh template sheet for each report. So far, this is a good solution for my problem and I think, I do not need the Copy-Method for Charts.

Regards,
Christoph

Hi! We are also facing the same challenge in our solution.

Is there no better way of fixing this?

Could you add an option to the addcopy method, which specifes if it should change the references?

Could this funtionality be written outside, if so, can you please supply sample code?

We are very interested in purchasing your component, if this issue can can be resolved.

Kindest regards,
Richard Funke

Dear Richard,

Thanks for your consideration.


Could you tell me your timeline? So I can put this work in my future plan.



I was hoping you already had a fix for this today. If not, we’ll unfortunately have to go for excel activex.

Richard

Dear Richard,

I am fixing this problem. I think it can be solved today. But other enhancements are added to the new hotfix, so I have to do some more test and I plan to release the new hotfix tomorrow.

Could you be patient for another day? Thanks.

Hi,

Please download hotfix 1.6.2. This problem is fixed.

This is great news, looking forward to this fix. The sooner you can supply it to us, the better!

Remember we are on a really tight schedule, so we need it fast or else we’ll have to go for activex. You still have a fair chance if you deliver today!

If you need more time, let me know (send email!) I will have to discuss it with the project leader.

Regards,
Richard Funke

You are too fast, I didn’t have time to finish writing my response :slight_smile: Ignore the previous message!

We are now interested in licensing this component. What edition will we have to purchase to take advantage of this copy functionality and also using the worksheet.Cells[4, 1].PutValue(x);

on multiple sheets.

Regards,
Richard Funke

Dear Richard,

Thanks for your consideration.

Please choose Professional for Edition option at Pricing.