阅读一个大的 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 文件只有一列包含 50,000 个条目的数据.

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[] {"
"},
                StringSplitOptions.RemoveEmptyEntries
            )
        );
    }
    new FileInfo(fileLocation + ".csv").Delete();
    return valueList;
}

资源:

http://www.codeproject.com/Articles/5123/Opening-and-Navigating-Excel-with-C

如何使用 C# 在回车时拆分字符串?

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