By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,290 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,290 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

*** Sent via Developersdex http://www.developersdex.com ***
Apr 26 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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:
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 ***


Apr 26 '06 #2

P: n/a
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

*** Sent via Developersdex http://www.developersdex.com ***
Apr 27 '06 #3

P: n/a

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:
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

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


Apr 27 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.