OLEDB的性能来读取Excel [英] Performance of OLEDB to read Excel

问题描述

下面的代码需要像2500毫秒上的i7- * 3.4 GHz的窗户-7的64位计算机与25000行5列读取Excel工作表。每个单元大约包括10个字符的字符串。它是正常的吗？我怎样才能读取速度更快

 秒表SW1 = Stopwatch.StartNew（）？; 
变种的connectionString =的String.Format（供应商= Microsoft.ACE.OLEDB.12.0;数据源= {0};+ 
扩展属性= Excel的12.0;文件名）; 
 
变种适配器=新OleDbDataAdapter的（SELECT * FROM [根$]的connectionString）; 
变种DS =新的DataSet（）; 
 adapter.Fill（DS，根）; 
 sw1.Stop（）; Console.WriteLine（采取的Excel根时间：{0}毫秒，sw1.Elapsed.TotalMilliseconds）; 
  

解决方案

我要介绍我的调查结果，因为答案行为始终是一致的。

我抄你的代码，并把一个按钮单击事件里面，只是改变了一下，以确保处置适配器和每一个测试连接的。

  // TEST.XLS包含26664行5列。平均10字符列，文件大小为2448kb 
 //操作系统Windows 7旗舰版64位。 CPU英特尔酷四核Q9550 2.83ghz 
 // 8GB的内存和磁盘C是256GB SSD CRUZER 
 
私人无效的button1_Click（对象发件人，EventArgs五）
 {
 
字符串文件名=C：\\tmp\\test.xls 
秒表SW1 = Stopwatch.StartNew（）; 
变种的connectionString =的String.Format（供应商= Microsoft.ACE.OLEDB.12.0;数据源= {0};+ 
扩展属性= Excel的12.0，文件名）; 
 
使用（VAR适配器=新OleDbDataAdapter的（SELECT * FROM [根$]的connectionString））
 {
变种DS =新的DataSet（）; 
 adapter.Fill（DS，根）; 
 sw1.Stop（）; 
 Console.WriteLine（采取的Excel根时间：{0}毫秒，sw1.Elapsed.TotalMilliseconds）; 
} 
} 
  

所以，这基本上是你的代码。此代码执行的500毫秒。但....
如果我把文件TEST.XLS打开在Excel 2010中，执行时间跳转到8000MS。

我也试过这个代码的变化，但最终的结果是一样的。

 私人无效的button1_Click（对象发件人，EventArgs的发送） 
 {
字符串文件名=C：\\tmp\\test.xls 
秒表SW1 = Stopwatch.StartNew（）; 
变种的connectionString =的String.Format（供应商= Microsoft.ACE.OLEDB.12.0;数据源= {0};+ 
扩展属性= Excel的12.0，文件名）; 
使用（OleDbConnection的CN =新的OleDbConnection（的connectionString））
 {
 cn.Open（）; 
使用（VAR适配器=新OleDbDataAdapter的（SELECT * FROM [根$]，CN））
 {
变种DS =新的DataSet（）; 
 adapter.Fill（DS，根）; 
 sw1.Stop（）; 
 Console.WriteLine（采取的Excel根时间：{0}毫秒，sw1.Elapsed.TotalMilliseconds）; 
} 
} 
} 
  

和，不，它不是OleDbConnection连接的开放式（），始终是adapter.Fill（）

Following code takes like 2500 milliseconds on an i7-*3.4 GHz windows-7 64-bit computer to read an excel sheet with 25000 lines and 5 columns. Each cell approximately include a string with 10 characters. Is it normal? How can I read it faster?

 Stopwatch sw1 = Stopwatch.StartNew();
 var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; " +
                                             "Extended Properties=Excel 12.0;", filename);

 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);

解决方案

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()

这篇关于OLEDB的性能来读取Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！