I wish to present my findings as an answer because the behavior is always consistent.
I have copied your code and put inside a button click event, just changed a bit to be sure to dispose the adapter and the connection for every test made.
// test.xls contains 26664 rows by 5 columns. Average 10 char for column, file size is 2448kb
// OS Windows 7 Ultimate 64 bit. CPU Intel Core2 Quad Q9550 2.83ghz
// 8gb ram and disk C is an 256gb SSD cruzer
private void button1_Click(object sender, EventArgs e)
{
string filename = "c:\tmp\test.xls";
Stopwatch sw1 = Stopwatch.StartNew();
var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; " +
"Extended Properties=Excel 12.0", filename);
using(var adapter = new OleDbDataAdapter("SELECT * FROM [roots$]", connectionString))
{
var ds = new DataSet();
adapter.Fill(ds, "roots");
sw1.Stop();
Console.WriteLine("Time taken for excel roots: {0} ms", sw1.Elapsed.TotalMilliseconds);
}
}
So, this is basically is your code. This code executes in 500ms. BUT....
if I keep the file test.xls open in Excel 2010, the execute time jumps to 8000ms.
I have also tried this code variation, but the end results are the same
private void button1_Click(object sender, EventArgs e)
{
string filename = "c:\tmp\test.xls";
Stopwatch sw1 = Stopwatch.StartNew();
var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; " +
"Extended Properties=Excel 12.0", filename);
using(OleDbConnection cn = new OleDbConnection(connectionString))
{
cn.Open();
using(var adapter = new OleDbDataAdapter("SELECT * FROM [roots$]", cn))
{
var ds = new DataSet();
adapter.Fill(ds, "roots");
sw1.Stop();
Console.WriteLine("Time taken for excel roots: {0} ms", sw1.Elapsed.TotalMilliseconds);
}
}
}
and, no, it's not the Open() of the OleDbConnection, is always the adapter.Fill()
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…