阅读一个大的Excel文档 [英] Read a big Excel document

问题描述

我想知道在Excel中读取单元格的最快方式是什么。
我有一个包含50000行的Excel文件，我想知道如何快速阅读它。
我只需要阅读第一列，并使用oledb连接，这需要我15秒。
有更快的方法吗？

谢谢

解决方案

这里是一个依赖于使用Microsoft.Office.Interop.Excel的方法。

请注意：我使用的Excel文件只有一列包含50,000个条目的数据。 1）用Excel打开文件，保存为csv并关闭Excel。

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

3）拆分回车线上的数据，并将其添加到字符串列表。

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

我使用System.Diagnostics.StopWatch来执行时间，该函数运行了1.5568秒。

  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（）; 
列表< string> valueList = null; 
 using（StreamReader sr = new StreamReader（fileLocation +.csv））{
 string content = sr.ReadToEnd（）; 
 valueList = new List< string>（
 content.Split（
 new string [] {\r\\\
}，
 StringSplitOptions.RemoveEmptyEntries 
 ）
）; 
} 
 new FileInfo（fileLocation +.csv）。Delete（）; 
 return valueList; 
} 
  

资源：

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

如何用C＃拆分回车符上的字符串？

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?

Thanks

解决方案

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

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

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

2) Use StreamReader to quickly read the data.

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

4) Delete the csv file I created.

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-with-C

How to split strings on carriage return with C#?

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