Responses to “Excel integration in (ASP).NET using OleDb”
-
Wiebe Tijsma Says:
Dec 15, 2006 1:22 PMHi Laurent,
Just some notes, it might be useful to include "Extended Properties=Excel 8.0;IMEX=1" when connecting to excel, in case of mixed-mode columns (string and numeric for example) (in the first 8 rows) this will convert the values to the string type instead of outputting NULL values.
Just a comment on the C#:
throw ex;
is a bad practice, as it throws a new exception, losing the current stack trace. use:
throw;
instead to preserve it :)
Wiebe Tijsma
-
Wiebe Tijsma Says:
Dec 15, 2006 1:50 PMAh additionally this requires you to make some changes to the registry:
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.
ImportMixedTypes: should be set to value 'Text' i.e. import mixed-type
columns as text:
Using IMEX=1 in the connection string ensures the
registry setting is applied. -
Laurent Says:
Dec 15, 2006 2:32 PMAbout the "throw ex;": That's correct. I even blogged about it here:
http://geekswithblogs.net/lbugnion/archive/2006/09/29/92708.aspx
About the mixed columns: In my use cases, it was never needed. Having mixed types in a columns is not a good idea in my opinion. Having to change the registry to use it is not applicable in many cases, for example when you run an ASP.NET application (my case), and have only limited access to the server's settings. I prefer to stick to the one column = one type paradigm.
Thanks,
Laurent -
Laksh Says:
Mar 20, 2007 9:50 PMthanks..this post really helped me a lot. But i dont agree with Laurent that "Having mixed types in a columns is not a good idea". Becuase if data is comming from client then we dont have control over how data is comming to us. Second thing, most of the time IDs are alphanumeric characters like DL, Insuarnce ID, Group ID etc etc I dont understand why cant OLE db just read the values and let the user decide what to do with invalid values.
-
Scott Mather Says:
Apr 04, 2007 12:06 AMWiebe, you're a lifesaver! IMEX=1 just eliminated my headache.
-
eXeCuToR Says:
May 31, 2007 6:42 AMWiebe, about exceptions...
Actually it's just ridiculous to catch an exception then throw it again.
And didn't mention the performance yet... -
Laurent Says:
May 31, 2007 7:05 AM@ eXeCuToR:
No, no, it makes sense sometimes to catch an exception and rethrow it: Imagine that your class opens a DB connection. Imagine that something happens during the treatment, and an exception is thrown. You need to close the DB connection! So you catch the exception, close the connection (typically in the "finally" clause), then you pass the exception to the upper level, for example to display a message to the user.
That said, see this:
http://geekswithblogs.net/lbugnion/archive/2006/09/29/92708.aspx
HTH,
Laurent -
Manu Says:
Jun 07, 2007 4:39 AMHi,
I am using the same method to connect to excel. It works perfect on my machine but doesnt work on the server. Now excel is not installed on the server nor is the OLEDB data provider.
Which of these is a necessary prerequisite for the application to work on the server?
If you let me know the answer I would sincerely thankful to you as it would save me a lot of time and effort.
Thanks in advance.
Manu