Formula reference works on same sheet ONLY

Aspose.Cells Product Family
1

Hello There,

We are using latest Aspose.Cells 4.8 and discovered a critical issue. Please see following code.

Aspose.Cells.License lic1 = new Aspose.Cells.License();
lic1.SetLicense("Aspose.Cells.lic");
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Worksheets.Add();
wb.Worksheets[1].Cells["A1"].PutValue("A1");
wb.Worksheets[1].Cells["A2"].PutValue("A2");
wb.Worksheets[1].Cells["A3"].PutValue("A3");
wb.Worksheets[0].Cells["A1"].PutValue("Test");
wb.Worksheets[0].Cells["A2"].Formula = "=Sheet1!A1";
wb.Worksheets[0].Cells["A3"].Formula = "=Sheet2!A3";
wb.Worksheets[1].Cells.InsertRows(0, 1);
wb.Worksheets[0].Cells.InsertRows(0, 1);
wb.Save("C:\\test.xls");
I expect 2 results:
  1. On "Sheet1", Cell "A3" has formula "=Sheet1!A2" with value "Test";
  2. On "Sheet1", Cell "A4" has formula "=Sheet2!A4" with value "A3".

The actual results failed on #2.

We consider this as a crital issue and please provide fix ASAP.

Regards,

William Ren

2

I’m having the same issue.

I have a number of different cells that contain currency values.

The cell formulas for sheet 2 are like so:
=+Sheet1!F6
=+Sheet1!F5

Using PutValue, I set the values for F6 and F5 in Sheet 1 to be "$125,000 and $110,000."

On Sheet2, the values should be:
$125,000
$110,000
but when I retrieve them programmatically they’re empty.

The most interesting thing is that the values are present in Sheet2 when I open the excel spreadsheet in excel.

3

I figured it out.

You have to call workbook.CalculateFormula(); after you programmatically set cell values at runtime, otherwise the fields won’t be updated.


4

It doesn’t work for my case. I believe we are talking about different issues.

5

Hi,

Thank you for considering Aspose.

Well, I checked your mentioned scenario with the attached latest version of Aspose.Cells for .NET and it works fine. Following is my sample code:

//Instantiate a new workbook

Workbook book = new Workbook();


Worksheet sheet1 = book.Worksheets[0];


sheet1.Cells["F5"].PutValue("$125,000");

sheet1.Cells["F6"].PutValue("$110,000");


Worksheet sheet2 = book.Worksheets[book.Worksheets.Add()];

sheet2.Cells["A1"].Formula = "=+Sheet1!F5";

sheet2.Cells["A2"].Formula = "=+Sheet1!F6";


book.CalculateFormula();


MessageBox.Show(sheet2.Cells["A1"].StringValue +" & "+sheet2.Cells["A2"].StringValue );


book.Save("C:\\book1.xls");

Please try the attached latest version of Aspose.Cells and let us know if it works fine for you. Also, for your information, if you want to use the numeric values as input and want to display them as currency you may refer the following code:

//Instantiate a new workbook

Workbook book = new Workbook();


Worksheet sheet1 = book.Worksheets[0];


sheet1.Cells["A1"].PutValue(125000);

sheet1.Cells["A2"].PutValue(110000);


Style stl = book.Styles[book.Styles.Add()];

stl.Number = 5;


sheet1.Cells["A1"].SetStyle(stl);

sheet1.Cells["A2"].SetStyle(stl);


Worksheet sheet2 = book.Worksheets[book.Worksheets.Add()];


sheet2.Cells["A1"].Formula = "=+Sheet1!A1";

sheet2.Cells["A2"].Formula = "=+Sheet1!A2";


stl.Number = 5;


sheet2.Cells["A1"].SetStyle(stl);

sheet2.Cells["A2"].SetStyle(stl);


book.CalculateFormula();


MessageBox.Show(sheet2.Cells["A1"].StringValue +" & "+sheet2.Cells["A2"].StringValue );


book.Save("C:\\book1.xls");

Also, you may check the following documentation link for difference style formats,

Thank You & Best Regards,

6

Hi William Ren,

Thanks for providing us the sample code with details (the first post in the thread).

We have found the issue and will fix it soon.

Thank you.

7

Hi William Ren,

After closely check your code, I think you can fix the issue using the overloaded method i.e…, public void InsertRows(int rowIndex, int rowNumber, bool updateReference). Put true for the last parameter. You must use this method if you want to update formula references in other worksheets in the workbook. For example, if you want to insert rows in the worksheet “Worksheet2” and update the formulas in the worksheet “Worksheet1”, you may use this method.


So, you may change your code a bit.


Aspose.Cells.License lic1 = new Aspose.Cells.License();

lic1.SetLicense("Aspose.Cells.lic");

Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();

wb.Worksheets.Add();

wb.Worksheets[1].Cells["A1"].PutValue("A1");

wb.Worksheets[1].Cells["A2"].PutValue("A2");

wb.Worksheets[1].Cells["A3"].PutValue("A3");

wb.Worksheets[0].Cells["A1"].PutValue("Test");

wb.Worksheets[0].Cells["A2"].Formula = "=Sheet1!A1";

wb.Worksheets[0].Cells["A3"].Formula = "=Sheet2!A3";

wb.Worksheets[1].Cells.InsertRows(0, 1, true);

wb.Worksheets[0].Cells.InsertRows(0, 1, true);

wb.Save("C:\\test.xls");


Thank you.
8

Thanks. The solution you provided works.