Free Support Forum - aspose.com

Excel formulas are being modified when using absolute references

Hi,
I sent a couple of emails earlier, but haven’t heard back on this issue yet. So i am posting it here.
We are using aspose cells for .net.

In a simple example, i read an excel template file (which has a cell E1,with an absolute reference - something like $A$4). I get some data and insert it say in cells A1 to A10.
The resulting excel file generated has that absolute reference value for cell E1 changed to $A$11 in this case (basically it has changed the reference to the next available blank cell after data insertion, A11 in this case).

This is a bug, can you look into it and suggest a solution. One of our customers is waiting for a quick response, and i will appreciate the same from you.

thanks,
Jaideep.

Hi,


Please download and try our latest version/fix: Aspose.Cells for .NET v7.3.2.2

If you still find the issue, give us a sample console application, zip it and post it here to reproduce the issue on our end. Also attach your template Excel file(s) if you have any. We will check your issue soon.

By the way, you should also try to perform your steps (as per your code segments) in Ms Excel to see if MS Excel shows the same results or gives you different results. Generally, Aspose.Cells follows MS Excel and its standards.

Thank you.

Thanks for the response.
Yes, the issue still exists in the latest version.

I am attaching the sample template file for your reference.
Note the following - cell D1 has the formula (=$A$4*$C$4), this is an absolute reference. Now to reproduce this issue, simply use the aspose library to insert data into cells A1-A10,C1-C10,B1-B10.
The output excel file will show the formula for cell D1 as (=$A$11*$C$11) which is basically the next available cell after the data insertion. It will evaluate to nothing because those cells are not populated.

As regards to excel - it does not show this behavior, inserting a value in A4 and C4 will actually change the value of the cell D1 as desired.

Can you please look at this with high priority?

thanks,
Jaideep.

p.s. Please change the extension of the attached file to SimpleDemo1.xlt (it wont allow me to attach files with .xlt extension)

Hi,

Thanks for your sample file and giving us description.

If we insert a row in the second row in Ms-Excel, the formula of D1 will be changed too. Aspose.Cells works same as MS Excel.

We have also tested your issue with the following code and we were not able to replicate it.

The code inserts 3 in cell A4,B4,C4 and the value gets changed in D1 and the formula in D1 remains =$A$4*$C$4. Which seems to be correct?

If you still think, there is an error in this code, then please provide us your sample code replicating your issue.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\SimpleDemo1.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


worksheet.Cells[“A4”].PutValue(3);

worksheet.Cells[“B4”].PutValue(3);

worksheet.Cells[“C4”].PutValue(3);


//Saving the Excel file

workbook.Save(filePath + “.out.xlsx”);