OLE DB/JET and Excel - Reading Data, Error Handling

[mcljava]

I recently started a C# prototype using the OLE DB access method to read from a hefty Excel spreadsheet that we use at the office. The nice thing about OLE DB is you can read Excel worksheets pretty easily, and in block read style. H O W E V E R, when was the last time you saw a clean error free, perfectly formatted spreadsheet? That's where I am stuck. It seems the OLE DB driver/JET engine will happily retrieve the specified query range but it also supplies a null for any cell it doesn't like and considers to have an error. Is there a way to override this behavior and tell OLEDB through the DataSet or ExtendedProperties to retrive all data "as is" thus ignoring any cell errors? FYI, here is how I am reading a worksheet in one read. In my case I know there are roughly 330-370 rows per worksheet, so I specify a range of 400 rows: // open the JET connection OleDbConnection con = new OleDbConnection( connectionOpts ); con.Open(); // build the query string query = string.Format( "SELECT * FROM [{0}$[1]]", tableName, queryRange ); // fill the dataset OleDbDataAdapter oleAdapter = new OleDbDataAdapter(); oleAdapter.SelectCommand = new OleDbCommand( query, con ); DataSet ds = new DataSet(); int rowsProcessed = oleAdapter.Fill( ds, "data" ); // free up resources oleAdapter.Dispose(); oleAdapter.Close(); etc. Mike Luster CTI/IVR/Telephony SME