Bug: Chart gets wrong datasource using excel.Worksheets.RemoveAt(0);

Aspose.Cells Product Family
1

Hi Laurence,

here’s my cutted code.

excel.Open(@“c:\template.xls”);
designersheet = excel.Worksheets[“Formatierungen”];
int sheetIndex = excel.Worksheets.Add();
sheet = excel.Worksheets[sheetIndex];
Chart chart = sheet.Charts[chartIndex];
chart.NSeries.Add(startCell + “:” + endCell, false);
excel.Save(@“c:\test.xls”, FileFormatType.Default);

excel.Open(@“c:\test.xls”);
designersheet = excel.Worksheets[“Formatierungen”];
int sheetIndex = excel.Worksheets.Add();
sheet = excel.Worksheets[sheetIndex];
Chart chart = sheet.Charts[chartIndex];
chart.NSeries.Add(startCell + “:” + endCell, false);
excel.Worksheets.RemoveAt(0);
excel.Save(@“c:\test.xls”, FileFormatType.Default);

What I do:
1. Open template (designersheet). This file contains 1 sheet (“Formatierungen”)
2. Add first new sheet and write chart to it.
3. Save file.
4. Open saved file
5. Add second sheet (now 3 sheets are in my aspose.excel object) and write chart to it
6. Delete sheet(0) (designersheet)
7. Save

The problem:
When deleting sheet 0 (designersheet), the chart of first added sheet gets datasource of second added sheet!!! Strangely only the sheetname will exchanged.

Example:
Right click in Excel on chart -> DataSource:
=‘newSheet1’!$A$24
now delete sheet(0) and the datasource is following
=‘newSheet2’!$A$24

If I don’t delete sheet(0) all is OK!!
If I don’t save the file temporary, all is OK!
But when add sheet with chart, save, load, add sheet and delete sheet(0), datasource gets exchanged.

For fast support I would be grateful!

-Stefan

2

Hi Stefan,

I will check this issue right now.

Why do you need to save the file temporarily?

3

Hi Laurence,

temporary saving is only for better explaining!

In my project users can create excelfiles and save them.
Some days after creating them, they want to add some sheets more -> they have to open the existing file and attach some sheets and save again.
All existing sheets can’t be new written because of values in database are changing often and so the old values will get lost. Therefore I have to attach…

Regards, Stefan

4

I have a similar problem.

I have a template .XLS with several worksheets. On a particular worksheet, are 3 charts. I have recently changed my program such that this particular page gets copied to another page ( and populated) , and the original page gets deleted. On the copied page, however, the cells used to populate the graph, point to same index as where the original page was.

Is there a way to get the data on the new, copied page to look at the new, copied pages’s cells?

Please let me know if I am not being too clear.

Thanks
Pam

5

Hi Stefan and Pam,

Please download the latest hotfix and have a try.

6

Hi Laurence,

seems to be working!

Many thanks!

7

Ca you tell me what exactly the fix you made was? It doesn’t seem to do what I thought the fix would be, and I am still getting the graph’s data source of the copied pages pointing to cells on the pages having the index of where the deleted(original) page was.

I downloaded the fix and just replaced the old DLL with the new DLL. Did I need to do something else?

Thanks.

Pam

8

Hi Pam,

Previous fix solves Stefan’s problem. After making a deeper invetigation, I find it doesn’t work in your case. Now I fix it. Please re-download the latest hotfix and have a try.

  • Aspose.Cells for .NET (Download | NuGet)


    To make sure you use the new dll, I suggest you to rebuild your project and check if the new dll is referenced in your project.
9

I doublechecked and see that I am using the correct DLL. However, it doesn’t solve the problem. The copied page does not reference the cells on its own page for the graph, as I had hoped.

This is an urgent problem for me, as I have to have 900 reports done by the end of Tuesday (and the thought of editting these manually is scary!!!). Any help you could offer would be greatly appreciated. I will be working on this over the weekend. Please let me know if you have further questions.


Pam

10

Hi Pam,

The latest hotfix is 2.1.0.2. I fixed your problem in this hotfix yesterday. So please re-download it and try it in your machine.

If the problem still exists, please email your designer file and sample code to me. Or you can have a Live chat with me at http://www.aspose.com/Corporate/Teams/Nanjing/Contactus.html

11

Hi Pam,

After investigating your designer file, I found what caused your problem. I fixed it in the latest hotfix. Please re-download it.

12

Thanks so much. It works like a champ!

13

Well, I have another small problem.

As you saw my code, I think you get the idea of what I am doing. Over the weekend, I made a modification to include a few more dynamically created pages to the workbook, also based on template pages in the designer file. So when I run the program, I am creating 2 pages with the graphs, and several other dynamic pages after those are created. Somehow, the graph on the first page has its Source data referencing of the last sheet that was created dynamically. I know this doesn’t make sense. I would like to send you the created file so that you know what I am talking about. Please let me know if this is possible.

Pam

14

Yes. Please send me your created file. Do you still use the previous template?

15

The designer file has changed, but I only added static sheets with some graphics, so now the file is huge! I will only send the end product, but if you want the designer file, I can send that as well.

Thanks
Pam

16

Your designer file will help me to figure out how to solve the problem. Please send it to me.

Thank you.