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
Print | posted on Friday, August 25, 2006 2:23 PM