Copy bar charts without referencing source worksheet

Hi there,


I am currently working on Aspose.Cells 8.9.0 for .NET
I am trying to copy some pre-made bar charts from one worksheet to another, along with the data source range.

I know copying charts in excel would make the new chart reference to the old data source from source worksheet,
but I would like the chart references to the new data source in the destination worksheet, just like the way back in Aspose.Cells 7.5 .

For example, after copying rows from “Format” to “Output1” (using ws.Cells.CopyRows() ),
including bar charts along with the data source,
I would like to change the data source of Charts[0] in “Output1” to point to “Output1” instead of “Format”.

Would you mind helping me out?
Thank you

Hi Jonathan,


Thank you for contacting Aspose support.

I have evaluated the presented scenario while using the latest version of Aspose.Cells for .NET 8.9.0.4, and I am able to notice the said problem, that is; copied chart still references the source worksheet in its data range. Moreover, if we repeat the test against 7.5.0.x, the resultant chart references the newly added worksheet in its data range. Based on these observations, I have logged an investigative ticket CELLSNET-44626 for product team’s review. You have to spare us little time to properly analyze the case and revert back with updates in this regard.

C#

var book = new Workbook(dir + “book1.xlsx”);
var source = book.Worksheets[0];
var destination = book.Worksheets[book.Worksheets.Add()];
destination.Cells.CopyRows(source.Cells, 0, 0, source.Cells.MaxDisplayRange.RowCount);
book.Save(dir + CellsHelper.GetVersion()+ “.xlsx”);

Hi again,


This is to update you that I have performed a few more tests while using Excel application only. I have noticed that Excel behaves exactly similar to the latest revision of the Aspose.Cells APIs, that is; if we manually copy a range of cells or rows from source worksheet and paste them onto a new Worksheet, the chart on the destination worksheet still points to the source worksheet in its data range. According to these results, the behaviour of Aspose.Cells for .NET 8.9.0.x seems to be correct.

Anyway, as we have already logged an investigation in this regard, we will look further into the scenario and revert back as soon as we have completed the preliminary analysis.

Hi there,


Thank you for the very quick response.

I understand that following excel might be a good practice as most of the company would treat excel as a standard.
However, I would like to to choose which data range the copied chart will refer to.
Would that be possible?
If not, which field of the chart should I change manually during copy?

The reason behind this is because I am implementing a program
where users can define a format within specific rows, (might include charts)
1. then the program would add data to the user specified cells,
2. then copy those few rows to the output worksheet (destination.Cells.MaxRow).
and redo step 1-2 until being told not to.

That’s why I would like to point to the new data source when I copy the chart,
or else I will not be able to keep track of the where the newly added data is

many thanks,
Jonathan

Hi Jonathan,


Thank you for your understanding.

As discussed in my previous response, the current revision of Aspose.Cells is behaving exactly as Excel application therefore we are not considering the said behaviour as a bug on the part of Aspose.Cells APIs. However, your requirement makes sense therefore I have requested the concerned member of the product team to review the case again and suggest a workaround for the said situation. We will write back as soon as we get updates from the product team in this regard.

Hi BaBar,


Thank you very much for helping me.

I know the schedule of the Aspose team must be fully packed yet you decided to ask for a solution, thank you.

I really really look forward to hearing from you soon.

thank you
Jonathan

Hi Jonathan,


This is to update you that the product team has decided to provide an overloaded version of Cells.CopyRows method which could accept an instance of CopyOptions while allowing you to choose which worksheet (Source or Destination) should be referenced in the copied chart. More details as well as code snippets will be available as soon as we have completed the development phase and the release is available for public use after quality assurance.

Hi BaBar,


Thank you very much :slight_smile:
I am so glad that there will be a overload function!!!

Please take your time and I will be here waiting… grab chair

:stuck_out_tongue:

many thanks,
Jonathan

Hi Jonathan,


Please note that the product team has marked the ticket aforementioned ticket as resolved. We will soon share the next revision of the API here for your testing.

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback.

Dear BaBar,


thanks, I will try it out now.

Hi again,


Please try the following piece of code against the latest revision of Aspose.Cells for .NET 8.9.0.5 (shared in our previous post).

C#

var book = new Workbook(dir + “source.xlsx”);
var source = book.Worksheets[0];
var destination = book.Worksheets[book.Worksheets.Add()];
CopyOptions options = new CopyOptions();
options.ReferToDestinationSheet = true;
destination.Cells.CopyRows(source.Cells, 0, 0, source.Cells.MaxDisplayRange.RowCount, options);
book.Save(dir + “dest.xlsx”);

Dear BaBar,


Thank you,

I have tested out the function and it seems to be running smoothly. :slight_smile:
I really appreciate the quick response and update from Aspose support.
regards,
Jonathan

P.S. Is the DLL I received a tmp or release version?

Hi,

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.

The dll, we provided is a temp dll. It is called minor version. The major version i.e 8.9.1 will be released in August somewhere between 15-25th. However, you can use minor version just like major version without any issue in your production use.

The issues you have found earlier (filed as CELLSNET-44626) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.