Formula Recalculations

Aspose.Cells Product Family
1

Sir I am trying to evaluate Aspose.Cells for NET 4.8.2, in order to propose to a multi site client.

I wrote the following code

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Button1.Click

Dim lic As New License
lic.SetLicense(“f:\aspose.cells.lic”)

Dim wb As Workbook = New Workbook()
'Dim fstream As FileStream = New FileStream(“f:\test1.xls”, FileMode.Open)
'Opening the Excel file through the file stream
'wb.Open(fstream, FileFormatType.Excel2003, “abc”)
wb.Open(“f:\test1.xls”, FileFormatType.Excel2003)

wb.Worksheets(0).Cells(“C7”).PutValue(0)
wb.CalculateFormula()
MsgBox(wb.Worksheets(0).Cells(“B92”).Value.ToString())
wb.Worksheets(0).Cells(“C7”).PutValue(7)
wb.CalculateFormula()
MsgBox(wb.Worksheets(0).Cells(“B92”).Value.ToString())

End Sub

that attempts to open the atteched XLS, change some values and receive some return values back. However, even if I call recalculate, it does run the formulas and return something back, while using Excel to change the values, they change correctly. What am I doing wrong?

Thanks in advance
E. Gimissis
2

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Well, there is some differences between the formula and values in your code and template file. Like there is no formula on cell “B92” (in your template file the formula is at “B7”). Also, the value of “C7” is not used in the formula of “B7”, so that means if you even change the value of “C7”, it will not affect the formula result of “B7”. If you have some different requirements, please explain it in detail and we will check it soon. Also, I have made the changes to your code as per the above details and it works fine.

Dim wb As Workbook = New Workbook()

'Dim fstream As FileStream = New FileStream("f:\test1.xls", FileMode.Open)

'Opening the Excel file through the file stream

'wb.Open(fstream, FileFormatType.Excel2003, "abc")

wb.Open("d:\test1.xls", FileFormatType.Excel97To2003)

wb.Worksheets(0).Cells("C2").PutValue(0)

wb.CalculateFormula()

MsgBox(wb.Worksheets(0).Cells("B7").Value.ToString())

wb.Worksheets(0).Cells("C2").PutValue(7)

wb.CalculateFormula()

MsgBox(wb.Worksheets(0).Cells("B7").Value.ToString())

Please feel free to contact us in case you have any further query. We will be happy to help you out.

Thank You & Best Regards,

3

Sir,

thanks for your prompt and kind reply.
However, C2 that you are changing the value, is totally empty in the attached excel, and B7 accepts only input values. If you open the attached Excel using MS Excel, you will notice that if you put a value different than 0 in C7, the value of C92 will also change accordingly. That is the behavior I want to imitate. Is that possible?

Best Regards
E. Gimissis
4

Hi,

Please try the attached latest version/fix v4.9.1.3.

I have tested your scenario with it using your template file, it works fine.

Sample code:

Dim wb As New Workbook()

'Dim fstream As FileStream = New FileStream(“f:\test1.xls”, FileMode.Open)

'Opening the Excel file through the file stream

'wb.Open(fstream, FileFormatType.Excel2003, “abc”)

wb.Open(“e:\test\test1.xls”, FileFormatType.Excel97To2003)


MessageBox.Show(wb.Worksheets(0).Cells(“C7”).Value.ToString())//0
MessageBox.Show(wb.Worksheets(0).Cells(“C92”).Value.ToString())//-533


wb.Worksheets(0).Cells(“C7”).PutValue(3)

wb.CalculateFormula()

MessageBox.Show(wb.Worksheets(0).Cells(“C7”).Value.ToString()) //3
MessageBox.Show(wb.Worksheets(0).Cells(“C92”).Value.ToString()) //-685.

wb.Save(“e:\test\outtest1.xls”)


Thank you.