tags:

views:

514

answers:

3
Q: 

Datatable not returning values from Excel (values from formulas)

A project I am working on requires values from a C# application to update 6 values in an Excel sheet. This excel sheet then populates a named range of items and prices, driven by a number of VLOOKUP formulas and worksheets within the same Excel File.

The two columns in the range are named 'Item' & 'Price'.

'Item' is a column of items concatenated from formulas on a spreadsheet, and these appear fine in the DataTable. However, none of the values in the 'Price' column (all number values from VLOOKUP formulas) appear in the DataTable.

When loading the DataTable in C#, I can see that the two columns' types of string and double are fine. If I replace one of the formula derived values with a hard-coded value the DataTable is fine, so I guess the DataTable itself is working as expected.

If I open the Excel file after the values are passed into it, I can see that the input values went in fine and the formulas have calculated correctly.

So, the only thing I can think is causing the problem is that the formulas are not being recalculated after the values are passed into it.

Is there a way to open an excel file in C# and get it to recalculate all the formulas?

A: 

Which driver are you using to open the excel sheet? OLEDB or ODBC?

jdecuyper  2009-04-01 15:13:11
sorry, OLEDB for both the update query (adding the values to the sheet) and for the select query which populates the DataTable
lazygamer  2009-04-01 15:15:37
+1  A: 

Never mind,

Replaced the OLEDB update with an Excel Interop, opening the Excel file with the Interop allows the formulas to recalculate.

lazygamer  2009-04-02 19:38:17
A: 

GAH. Turns out some of the users of this app may not even have Excel installed - so the Excel Interop workaround is no good.

Does anyone know of a way of getting an Excel file to recalculate cell formulas without using the Excel Interop?

lazygamer  2009-04-06 10:53:36

related questions

Displaying Flash content in a C# WinForms application
How to get the value of built, encoded ViewState?
Unhandled Exception Handler in .NET 1.1
How do I connect to a database and loop over a recordset in C#?
How do I most elegantly express left join with aggregate SQL as LINQ query
Get a new object instance from a Type in C#
.NET Testing Framework Advice
Automatically update version number
What is the difference between an int and an Integer in Java/C#?
How to write to Web.Config in Medium Trust ?
WinForms ComboBox data binding gotcha
How do you sort a C# dictionary by value?
Adding Scripting functionality to .NET applications
Floating Point Number parsing: Is there a Catch All algorithm?
How do I print an HTML document from a web service?
Decoding T-SQL CAST in C#/VB.net
Anatomy of a "Memory Leak"
How do I get a distinct, ordered list of names from a DataTable using Linq
Reliable Timer in a Console Application
How do I fill a DataSet or a DataTable from a LINQ query resultset ?
What's the difference between Math.Floor() and Math.Truncate() in .NET?
How do I calculate relative time?
How do I calculate someone's age in C#?
Are there any conversion tools for porting Visual J# code to C#?
When setting a form's opacity should I use a decimal or double?