读大Excel文档 [英] Read a big Excel document

问题描述

我想知道什么是阅读的细胞在Excel的最快方法。
我有一个包含50000行Excel文件和我想知道如何读它快。
我只需要读的第一列和OLEDB连接它需要我像15秒。
是有一个更快的方法？

I want to know what is the fastest way to read cells in Excel. I have an Excel file that contains 50000 rows and I wanna know how to read it fast. I just need to read the first column and with oledb connection it takes me like 15 seconds. Is there a faster way?

感谢

推荐答案

下面是依赖于使用的Microsoft.Office.Interop.Excel的方法

Here is a method that relies on using Microsoft.Office.Interop.Excel.

请注意：我使用的Excel文件曾与拥有50000项数据只有一列。

Please Note: The Excel file I used had only one column with data with 50,000 entries.

1）打开与Excel文件，保存为CSV，并关闭Excel。

1) Open the file with Excel, save it as csv, and close Excel.

2）使用StreamReader的快速读取数据。

2) Use StreamReader to quickly read the data.

3）各执回车换行的数据，并将其添加到字符串列表。

3) Split the data on carriage return line feed and add it to a string list.

4）删除我创建的CSV文件。

4) Delete the csv file I created.

我用System.Diagnostics.StopWatch时间执行，并花了1.5568秒的功能运行

I used System.Diagnostics.StopWatch to time the execution and it took 1.5568 seconds for the function to run.

public static List<string> ExcelReader( string fileLocation )
{                       
    Microsoft.Office.Interop.Excel.Application excel = new Application();
    Microsoft.Office.Interop.Excel.Workbook workBook =
        excel.Workbooks.Open(fileLocation);
    workBook.SaveAs(
        fileLocation + ".csv",
        Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows
    );
    workBook.Close(true);
    excel.Quit();
    List<string> valueList = null;
    using (StreamReader sr = new StreamReader(fileLocation + ".csv")) {
        string content = sr.ReadToEnd();
        valueList = new List<string>(
            content.Split(
                new string[] {"\r\n"},
                StringSplitOptions.RemoveEmptyEntries
            )
        );
    }
    new FileInfo(fileLocation + ".csv").Delete();
    return valueList;
}

资源：

Resources:

http://www.codeproject.com/Articles/5123/Opening-and -Navigating-Excel的使用-C

的如何用C＃各执回车字符串？

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