Writing and reading from a template Excel file using ADO.Net - results not recalculated

[RJN]

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

 

[tommaso.gastaldi]

HI RJN,

my guess.
Perhaps when we actually work with Excel, it recomputes the formulas
based on events such as "validate" or others. It is possible that just
uploading values via OleDB does not rise such events to recompute
formulas.

Perhaps, it is needed to invoke them programmatically.

(just a guess, let me know when you find out)

-tom

RJN ha scritto:

 

[RJN]

Hi Tom,

I think you're right. The calculations are not forced. But I don't want
to use excel object and force a recalculation. Microsoft doesn't
recommend using automation from ASP.Net. Is there any other way?

Thanks

rjn

 

[tommaso.gastaldi]

I don't know. I cannot think of any way to interact at that level with
Excel via OleDb.

At this point, if I were you, I would probably forget about Excel and
code manually the computations (Excel doesn't do anything
stratospheric). Which should be a more flexible and clean solution,
anyway.

but waite also for other people opinions ...

-tom

RJN ha scritto: