Loop through excel file in c#

Question

I have a problem with looping through an excel file. I want to be able to create an automatic code that will handle multiple excel files

There is a fix header in each file, so the "real" datas begin at line 15.

I'm trying to use "usedRange" but I don't really understand the doc.

Actually, I have this :

var excel = new Excel.Application();
var wkb = OpenBook(excel, _myExcelFile, true, false, false);
var sheet = wkb.Sheets["B.C"] as Excel.Worksheet;
var usedRange = sheet.UsedRange;
var i = 0;
foreach (Excel.Range row in sheet.UsedRange.Rows)
{
    i++;
    // I get data like this (for column 2 for example) : 
    // Convert.ToString(row.Cells[i, 2].Value);
}

Problem is that my excel file have over 3000+ rows, but the loop returns only 1800+, I can't figure why.

I think that there is a problem with the "UsedRange" function but don't know why.

How can I loop ALL rows in my file?

You should look into reading the whole sheet into a 2d array and then iterating that. Reading an Excel sheet cell by cell is very slow. Have a look at the accepted answer here: http://stackoverflow.com/questions/7613898/how-to-read-an-excel-spreadsheet-in-c-sharp-quickly — Darren Young, Aug 03 '15 at 16:21
I will share several answers with you, You can choose which one is suite for your business problem Refer following URLs [How to read an Excel spreadsheet in c# quickly](http://stackoverflow.com/questions/7613898/how-to-read-an-excel-spreadsheet-in-c-sharp-quickly) [Faster MS Excel Reading using Office Interop Assemblies](http://www.codeproject.com/Articles/9992/Faster-MS-Excel-Reading-using-Office-Interop-Assem) — Gehan Fernando, Aug 03 '15 at 16:47

score 0 · Answer 1 · answered Aug 03 '15 at 17:01

Another option is to turn your excel into a datatable - following is untested (I don't have excel on this PC)

System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataTable DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataTable();
MyCommand.Fill(DtSet);
MyConnection.Close();

You can then do linq on it with things like

var x = DTset.AsEnumberable().Where( r => r["My Field"].ToString() == "Pick me");

or just use it like a normal datatable

Loop through excel file in c#

1 Answers1