How to quickly read an Excel spreadsheet in C #

Question

I am using Microsoft.Office.Interop.Excel to read a spreadsheet open in memory.

gXlWs = (Microsoft.Office.Interop.Excel.Worksheet)gXlApp.ActiveWorkbook.ActiveSheet;
int NumCols = 7;
string[] Fields = new string[NumCols];
string input = null;
int NumRow = 2;
while (Convert.ToString(((Microsoft.Office.Interop.Excel.Range)gXlWs.Cells[NumRow, 1]).Value2) != null)
{
    for (int c = 1; c <= NumCols; c++)
    {
        Fields[c-1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)gXlWs.Cells[NumRow, c]).Value2);
    }
    NumRow++;

    //Do my other processing
}

I have 180,000 lines, and it comes out very slowly. I am not sure that "Convert" is effective. Anyway, could I do it faster?

Moon

Answer 1

Hi, I found a much faster way.

It is best to read all the data in one go using get_range. This loads the data into memory, and I can skip it like a regular array.

Microsoft.Office.Interop.Excel.Range range = gXlWs.get_Range("A1", "F188000");
object[,] values = (object[,])range.Value2;
int NumRow=1;
while (NumRow < values.GetLength(0))
{
    for (int c = 1; c <= NumCols; c++)
    {
        Fields[c - 1] = Convert.ToString(values[NumRow, c]);
    }
    NumRow++;
}

Answer 2

There are several options: they all include some additional library:

• OpenXML 2.0 ( MS) / .xlsx, ,

• () , excel ( Winforms/WPF/ASP.NET...), SpreadsheetGear, Aspose.Cells ..

Answer 3

, "Convert" . , ?

? , Convert.ToString() - , . , 180 000 excel...

, , , .

Value2 ?

Answer 4

, ""...

, .

, :

(Microsoft.Office.Interop.Excel.Range)gXlWs

.

:

gXlWs.Cells[NumRow, 1].Value != null

, , .

Answer 5

Use the method OleDB. It is the fastest as it should;

string con =
  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;" + 
  @"Extended Properties='Excel 8.0;HDR=Yes;'";    
using(OleDbConnection connection = new OleDbConnection(con))
{
    connection.Open();
    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection); 
    using(OleDbDataReader dr = command.ExecuteReader())
    {
         while(dr.Read())
         {
             var row1Col0 = dr[0];
             Console.WriteLine(row1Col0);
         }
    }
}