How to read an Excel spreadsheet in c# quickly

Question

I am using Microsoft.Office.Interop.Excel to read a spreadsheet that is 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 rows and this turns out be very slow. I am not sure the "Convert" is efficient. Is there anyway I could do this faster?

Moon

Answer 1

Hi I found a very much faster way.

It is better to read the entire data in one go using "get_range". This loads the data into memory and I can loop through that like a normal 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 - all involve some additional library:

• OpenXML 2.0 (free library from MS) can be used to read/modify the content of an .xlsx so you can do with it what you want

• some (commercial) 3rd-party libraries come with grid controls allowing you to do much more with excel files in your application (be it Winforms/WPF/ASP.NET...) like SpreadsheetGear, Aspose.Cells etc.