Reading and writing excel in a web application

[Von Shean]

I have two problems using excel files.

let me explain the use cases....
1. I download an excel file from a server and export it to a sql database
problem: Some cells have errors as 'number stored as string'. So even
after using IMEX=1 i get dbNulls even if there is a value in the cell. I use
ole db provider.
2. My clients save an excel file with my web application. What is required
is i have to open the excel sheet, there is an input cell and an output
cell. So i set a value in the input cell...there are some formula
recalculations and i get a value from the output cell. I need to figure out
how can i do it.

Also i have heard office 2003 has some components that are lightweight so
that they can be run on web server.. can anyone please put a light on this
feature of office 2003 system.

Help is appreciated.

regards,

 

[onedaywhen]

Re: your point 1:

Check the registry settings for the local machine:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

TypeGuessRows: setting the value to 0 (zero) will force ADO to scan
all column values before choosing the appropriate data type (if you
have a value between 1 and 16, it may not be detecting any string
values in the first n rows).

ImportMixedTypes: should be set to value 'Text' i.e. import mixed-type
columns as text.

Using IMEX=1 in the connection string, as you have done, ensures the
registry settings are honored.