Hi

I've a template excel file which has all the calculations defined. There
are certain input values to be entered which gives a lot of output to
the user. I don't want to expose the excel sheet to the user as
calculations become visible. I'm writing a web interface which takes the
input values from user, updates the excel sheet and returns the
recalculated output from the excel file. I'm using ADO.Net to update and
read the excel file.

The update works fine, but when I read the output, I still get the old
values that are in the template file and not the recalculated values. It
appears that even though the input values are updated, the values are
not recalculated unless we open the excel file. Say the input in the
template is 2000 and the calculated output is 10000, now I update the
input to 4000 and the actual recalculated output is 20000, but I still
get the result as 10000.

The following is my code. I first create a copy of the template file and
work on the copy instead of the origical template.

Dim strTemplateFile As String = "E:\temp\temp.xls"
Dim strDestFileName As String = "E:\temp\temp1.xls"
File.Copy(strTemplateFile, strDestFileName)

Dim objConn As OleDb.OleDbConnection
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=""" + strDestFileName + """;Extended Properties=""Excel
8.0;HDR=No;"""
objConn = New OleDb.OleDbConnection(strConn)
objConn.Open()

Dim objCommand As New OleDbCommand
objCommand.Connection = objConn
'set the input values
objCommand.CommandText = "Update [Sheet1$E5:E5] Set F1=4000"
objCommand.ExecuteNonQuery()
objConn.Close()

Dim objAdapter As New OleDbDataAdapter("select * from [Sheet1$]",
strConn)
Dim oTable As New DataTable
objAdapter.Fill(oTable)
Response.Write(oTable.Rows(2).Item(11))

Regards

Rjn



*** Sent via Developersdex http://www.developersdex.com ***