Querying Excel document with OleDb
 

Hi!

I have a puzzling situation here. I have an application that loads an
Excel document and subsequently queries the document for information
using OleDb. Code snippet below:

OleDbConnection conn = null;
DataSet dataSet = null;
OleDbCommand cmd = null;
OleDbDataAdapter adapter = null;
string sFileName = "test.xls";

try
{
// Get the data out of the spreadsheet into a dataset. Try opening the
Excel file with ADO
string sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
sConnect += sFileName + ";Extended Properties=Excel 8.0;";

conn = new OleDbConnection(sConnect);
conn.Open();

// Querying an insane amount of cells...
string sSQL = "Select * from [" + sWorksheetName + "$A1:IV65536]";
cmd = new OleDbCommand(sSQL, conn);
adapter = new OleDbDataAdapter();
adapter.SelectCommand = cmd;
dataSet = new DataSet();

adapter.Fill(dataSet, sWorksheetName);
}
finally
{
if(null != conn)
{
if(ConnectionState.Open == conn.State)
conn.Close();
conn.Dispose();
conn = null;
}
}

Everything works as I assume it should. There's something strange
though, that I can't quite figure out.

Let's say I have the document open in Excel at the time this code is
executed. If I make changes to the document in Excel (but DO NOT save
it!) the query in the code actually picks up the changes! If I then
quit Excel and don't save the changes, then the code picks up the
original values. This seems very strange, as I have never saved the
document to disk, and I don't see why connecting to it in this manner
would return me that data.

Any ideas? I'm using C# / .NET 1.1, and Excel 2003 SP2.