CODEWITHSUNDEEP
c#excel
Moh Najdawi
Moh Najdawi

Reputation: 103

Get row from Excel file in C#

I am reading through an Excel file in C#. There are 3 sheets in the file:

  1. Summary
  2. Users
  3. Others

I am looping through the columns of Summary sheet.(code below)

There is a column: SummaryID in every sheet.

foreach (DataColumn dc in Summary.Columns)
 {
   foreach (DataRow dr in Summary.AsEnumerable())
   {
     //get column SummaryID for everyrow
     //And then get all rows in Users sheet that match  SummaryID
     //And then get all rows in Others sheet that match SummaryID
   }
  }

My question is: for everyrow in Summary Sheet (SummaryID), I want to get all matching rows that match the SummaryID in 'Users' and 'Others' sheets.

Note: The column SummaryID exists in all 3 sheets and is the first column in all sheets.

Upvotes: 0

Views: 2587

Answers (3)

Justin
Justin

Reputation: 4072

You can use OleDB to do this. Code is similiar 

 // create a connection to your excel file
    // the 8.0 denotes excel 2003
    // you will need to use the right number for your version of excel
    OleDbConnection con = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=InsertYourFile.xls"; Extended Properties=Excel 8.0" );

    // create a new blank datatableDataTable 
   dtSheets = new DataTable();

    // create a data adapter to select everything from the worksheet you want
    OleDbDataAdapter da = new OleDbDataAdapter( "select * from [YourWorksheetName$] WHERE BLAH, etc", con );


   // use the data adapter to fill the datatable
   da.Fill( dtSheets );

Upvotes: 0

idlehands23
idlehands23

Reputation: 326

I like using LinqToExcel They have a LinqToExcel.Row class that might help you and you will be using linq over foreach statements.

Upvotes: 2

John Bustos
John Bustos

Reputation: 19544

Have you considered treating your Excel sheet like a Database and querying out the data you want using OLEDB or a similar technology?

This would be a simple Join query at that point - Might be faster...

Upvotes: 0

Related Questions