Geeks With Blogs

News




What I do:

Identity Mine

MVVM Light

GalaSoft


What I am:

Microsoft Most Valuable Professional, Client Application Development

Microsoft Certified Technology Specialist, Windows Presentation Foundation

WPF disciples


Social:


View my profile on LinkedIn

XING
Creative Commons License
Diary of a Code Trotter by Laurent Bugnion is licensed under a Creative Commons Attribution 3.0 Unported License

All source code on this blog is licensed under the MIT license.

Copyright (c) 2006 - 2011 GalaSoft Laurent Bugnion

Laurent Bugnion (GalaSoft) Diary of a Code Trotter
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.
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
  + "Data Source=" + strPathExcel + ";"
  + "Extended Properties=Excel 8.0;";

// Create connection object by using the preceding connection string.
OleDbConnection cnConnection = new OleDbConnection( strConnection );

// Open connection with the database.
cnConnection.Open();

try
{
  // Get a reader for the ImageCollections.
  // For example's sake, this imports values from the
  // worksheet named "MyData". Note the trailing '$'
  OleDbCommand cmdSelectSections
    = new OleDbCommand( "SELECT * FROM [MyData$]", cnConnection );

  OleDbDataReader drdSections =
    cmdSelectSections.ExecuteReader();

  // Import excel rows into XML files
  while ( drdSections.Read() )
  {
    // The columns can be accessed by index, or by using
    // the Title string, which is the content of the
    // corresponding column on the first row.

    // For example:
    string strPath = (string) drdSections[ "Path" ];

    // Imported values should be tested against DbNull.Value
    // and also casted to the corresponding type. For example
    // numbers are imported as double, even if they are integers.

    // More code...
  }
}
catch ( Exception )
{
  throw;
}
finally
{
  // Make sure that connection is properly closed.
  cnConnection.Close();
}
Connecting to an Excel file using ADO.NET 2.0
Posted on Friday, August 25, 2006 2:23 PM Technical stuff , .NET , Website , ASP.NET 2.0 | Back to top


Comments on this post: Excel integration in (ASP).NET using OleDb

# re: Excel integration in (ASP).NET using OleDb
Requesting Gravatar...
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


Left by Wiebe Tijsma on Dec 15, 2006 1:22 PM

# re: Excel integration in (ASP).NET using OleDb
Requesting Gravatar...
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.
Left by Wiebe Tijsma on Dec 15, 2006 1:50 PM

# re: Excel integration in (ASP).NET using OleDb
Requesting Gravatar...
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
Left by Laurent on Dec 15, 2006 2:32 PM

# re: Excel integration in (ASP).NET using OleDb
Requesting Gravatar...
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.
Left by Laksh on Mar 20, 2007 9:50 PM

# re: Excel integration in (ASP).NET using OleDb
Requesting Gravatar...
Wiebe, you're a lifesaver! IMEX=1 just eliminated my headache.
Left by Scott Mather on Apr 04, 2007 12:06 AM

# re: Excel integration in (ASP).NET using OleDb
Requesting Gravatar...
Wiebe, about exceptions...
Actually it's just ridiculous to catch an exception then throw it again.
And didn't mention the performance yet...
Left by eXeCuToR on May 31, 2007 6:42 AM

# re: Excel integration in (ASP).NET using OleDb
Requesting Gravatar...
@ 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
Left by Laurent on May 31, 2007 7:05 AM

# re: Excel integration in (ASP).NET using OleDb
Requesting Gravatar...
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
Left by Manu on Jun 07, 2007 4:39 AM

Comments have been closed on this topic.
Copyright © Laurent Bugnion | Powered by: GeeksWithBlogs.net