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:
- Open the attached workbook in Excel (make sure not to save it at any point).
- 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),"")
- 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.
- Close the workbook (don’t save it).
- Open an OleDbConnection to the workbook and use an OleDb command (in C# for example) to update K28 to be “4”.
- Close the OleDbConnection.
- Use Aspose to recalculate the formulas, i.e. RecalculateFormulas(DataSource).
- 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!