Aug 25 2006

Excel integration in (ASP).NET using OleDb

.NET, ASP.NET 2.0, Technical stuff, Website
See comments
Most of my private programming time lately has been invested in my PhotoAlbum's new version, in which I implement bug corrections and prio 1 improvements over the version I used during the last holidays in Asia. I will publish that version soon together with a list of improvements.
One important feature (or so I thought) is the ability to import the content of an Excel file into the XML files I use in the PhotoAlbum system. I use an Excel file as main repository for all my pictures' information, not a database. The main reason is that I want to be able to edit the pictures info when I am on the road, using my Palm LifeDrive together with DocumentsToGo. So the idea was: I will input the pictures info only once in the Excel file, post the file through FTP to the web server, and then import the new pics' information into the PhotoAlbum.
Well, the import works great (I use ADO.NET to connect to the Excel file, use a few SQL statements to populate my objects and then save all to XML). Tested on the production server, it works too, very happy. However, when I tried to enter data into the real Excel file on my PDA today, I got the dreaded error 6150 "Cannot save in native Excel format"... This error happens from time to time, and I have no idea why. Asking Dataviz support didn't help (though they did their best to try and answer me), so... I think that I need a more solid, though less comfortable format...
Bottom line is, the "Import from Excel" functionality will be very useful to me when I am home (or near a PC). However I cannot rely on it when I am on the PDA only, so I need to use another format, probably CSV. The advantage of CSV is that I can easily produce it on the PDA, and can import it into Excel easily too. The other alternative is to write a XML to Excel converter for the PhotoAlbum files. This way, I'll enter the information in XML when on the road, and when I am home I just import the whole thing into my main Excel file. That would also eliminate the need for a 3rd format (XML, Excel, CSV). I need to think about it.
There are not so many examples online about how to connect to an Excel file using ADO.NET 2.0, so I post a (simplified) example here.
Previous entry | Next blog entry

Responses to “Excel integration in (ASP).NET using OleDb”

  1. Wiebe Tijsma Says:
    Dec 15, 2006 1:22 PM

    Hi 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


  2. Wiebe Tijsma Says:
    Dec 15, 2006 1:50 PM

    Ah 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.

  3. Laurent Says:
    Dec 15, 2006 2:32 PM

    About 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

  4. Laksh Says:
    Mar 20, 2007 9:50 PM

    thanks..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.

  5. Scott Mather Says:
    Apr 04, 2007 12:06 AM

    Wiebe, you're a lifesaver! IMEX=1 just eliminated my headache.

  6. eXeCuToR Says:
    May 31, 2007 6:42 AM

    Wiebe, about exceptions...
    Actually it's just ridiculous to catch an exception then throw it again.
    And didn't mention the performance yet...

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

  8. Manu Says:
    Jun 07, 2007 4:39 AM

    Hi,

    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

Comments for Excel integration in (ASP).NET using OleDb