Bug when recalculating a cell in a workbook

[luke_occ]

I am having problems getting a cell in a worbook to recaluclate properly.
I have attached the workbook and the following steps will reproduce the problem I am having:

1. Open the attached workbook in Excel (make sure not to save it at any point).
   
2. The worksheet “SPLS PI Data” has been saved with “1” selected in cell D17, this affects what data is displayed on that worksheet.
   As you can see, the cell K28 contains the value 100, this is the result of a formula that you can see if you uprotect the worksheet: =IF(SUM(C28,D28,E28)<>0,ROUND(SUM(C28,D28)/SUM(C28,D28,E28)*100,2),"")
3. Change the value in D17 to “4” using the drop down list, you see the values in the table below change. Now cell K28 contains no value.
4. Close the workbook (don’t save it).
5. Open an OleDbConnection to the workbook and use an OleDb command (in C# for example) to update K28 to be “4”.
6. Close the OleDbConnection.
7. Use Aspose to recalculate the formulas, i.e. RecalculateFormulas(DataSource).
8. Open the OleDbConnection again and select the value of cell K28.

The value should be DBNull.Value, but it is not. The value is still 100, as is was when D17=1 but the recalculation should have updated it to be blank as you saw when you updated D17 manually in step 3.

Another thing that confirms that this is a bug is that if you open the workbook in excel again, change D17 to be “3” (note that K28 is blank), save the workbook and then repeat steps 5-8 you will get the desired result of K28=DBNull.Value.

It seems that in this case, Aspose’s RecalculateFormulas() does not update the cell value if the recalculation causes it to go from a number to blank, instead it just stays at the original number.

I would appreciate your assistance in fixing this issue as our product is currently in tesing and must be released soon - this could cause a lot of problems!

Thank you,
–Luke

[Amjad_Sahi]

Hi Luke,

Thanks for considering Aspose.

Could you paste your code here which should utilize the template file to reproduce the issue.

Thank you.

[Amjad_Sahi]

Hi,

And by the way i opened your template file in MS Excel and manually select 3 from the dropdown on D17 cell in "SPLS PI Data" and save the excel file and then use only Aspose.Cells API to calculate the formulas and obtain the K28 result, it is fine as "" blank.

Following is my testing code:

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\Cell Update Problem.xls");
Worksheet worksheet = workbook.Worksheets["SPLS PI Data"];
workbook.CalculateFormula();
MessageBox.Show(worksheet.Cells["K28"].Value.ToString());//It will be blank string

Thank you.

[luke_occ]

Unfortunately the code that uses this template it pretty dispersed throughout our application and is not easy to paste here so before I try please could you try the following:

1. Open the template file in Excel.
2. Manually select 1 from the dropdown on the D17 cell in “SPLS PI Data”.
3. Save the excel file.
4. Use Aspose.Cells API to programatically change D17 in “SPLS PI Data” to 4.
5. Then use Aspose.Cells API to calculate the formulas and obtain the K28 result.

The result should be blank.

[Amjad_Sahi]

Hi,

Thanks for considering Aspose.

Yes it is blank.

Following is my code as per your instructions:

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\Cell Update Problem.xls");
Worksheet worksheet = workbook.Worksheets["SPLS PI Data"];
Cells cells = worksheet.Cells;
Cell cell = cells["D17"];
cell.PutValue(4);
workbook.CalculateFormula(false);
MessageBox.Show(cells["K28"].Value.ToString());

Note: If I do not insert 4 value to D17 cell, I got 100 from K28 cell, which is correct.

Could you try this code and tell us if it is fine there.

Thank you.

[luke_occ]

Hi Amjad,

Thanks for your help so far, and indeed your code does work, but in our code we need to access the data from the workbook in various places so try the following code (written in VB) and as before start with a workbook that has “1” selected before the code is run:

’ Define an Aspose workbook
Dim filename As String = "C:\Users\Luke\Desktop\Cell Update Problem.xls"
Dim workbook As Workbook = New Workbook()
workbook.Open(filename)
Dim worksheet As Worksheet = workbook.Worksheets(“SPLS PI Data”)
Dim cells As Cells = worksheet.Cells
Dim cell As Cell = cells(“D17”)
’ Insert the value, recalculate and report the value of K28
cell.PutValue(4)
workbook.CalculateFormula(False)
MessageBox.Show("1st read: " & cells(“K28”).Value.ToString())
’ Save the workbook’s values
workbook.Save(filename)

’ Define a new Aspose workbook and read the value of K28
Dim workbook2 As Workbook = New Workbook()
workbook2.Open(filename)
Dim worksheet2 As Worksheet = workbook2.Worksheets(“SPLS PI Data”)
Dim cells2 As Cells = worksheet2.Cells
MessageBox.Show("2nd read: " & cells2(“K28”).Value.ToString())

The 1st read will be blank, but the second will be 100 even though the workbook was saved and nothing has changed. Can you shed any light on this?

[Amjad_Sahi]

Hi,

Well, I don't find the problem you have mentioned. When I run your code I got blank for both returns / readings.

Could you try to add a line of code:

' Define an Aspose workbook
Dim filename As String = "C:\Users\Luke\Desktop\Cell Update Problem.xls"
Dim workbook As Workbook = New Workbook()
workbook.Open(filename)
Dim worksheet As Worksheet = workbook.Worksheets("SPLS PI Data")
Dim cells As Cells = worksheet.Cells
Dim cell As Cell = cells("D17")
' Insert the value, recalculate and report the value of K28
cell.PutValue(4)
workbook.CalculateFormula(False)
MessageBox.Show("1st read: " & cells("K28").Value.ToString())
' Save the workbook's values
workbook.Save(filename)

' Define a new Aspose workbook and read the value of K28
Dim workbook2 As Workbook = New Workbook()
workbook2.Open(filename)
Dim worksheet2 As Worksheet = workbook2.Worksheets("SPLS PI Data")
Dim cells2 As Cells = worksheet2.Cells 

workbook2.CalculateFormula(False)

MessageBox.Show("2nd read: " & cells2("K28").Value.ToString())

Thank you.

[luke_occ]

Hi Amjad,

I have tried your suggestion (adding in an additional CalculateFormula call) and it does work so thank you for the help!

–Luke