Relative references to data sheet are converted to absolute references in chart

[Jeff_B]

Using Aspose.Cells 6.0.0.0 I have 2 new issues:

1. Relative cell references are converted to absolute references
2. Cell references in a chart sheet are not being updated after inserting rows in a separate data sheet (apparently caused by item #1)

I found that inserting rows in my data sheet after building chart sheets with absolute references to the data was causing the charts to show wrong data. I changed the charts to use relative references which allows references to be updated after inserting rows using Excel. But the Aspose generated charts still show absolute references even though my code is now using relative references.

Using this code:

Dim _i As Int32 = chart.NSeries.Add("‘The Elements’!B3:B6", True)

Results in a chart with the “Series Values” in Excel:

=‘The Elements’!$B$3:$B$6

The row inserts may or may not happen dynamically in our production code depending on if a logo is available to insert into a new top row of each data sheet. So the obvious solution of “just do the row insert first” is not an option. I updated the source code I provided here <a href="https://forum.aspose.com/t/126338 to show this issue.

Please advise if this conversion of relative to absolute cell references is expected, required or controllable.

Thanks,

Jeff

[Amjad_Sahi]

Hi Jeff,

Regarding your first point, it might be the MS Excel’s behavior. We need to investigate your both issues though. I have logged a ticket with an id: CELLSNET-28783. We will look into it soon.

Thank you.

[shakeel.faiz]

Hi,

Thanks for your question. It works as Ms-Excel.

[Amjad_Sahi]

Hi,

We have analyzed your case. We come to know it is the limitation of MS Excel and Aspose.Cells works as MS Excel. For confirmation, you may try the steps in MS Excel (manually) based on your code snippet.

thank you.