0

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.

Jacco
  • 3,251
  • 1
  • 19
  • 29
Moh Najdawi
  • 103
  • 1
  • 7
  • what is your question.. you have made a statement not a question.. you should be asking how to `get matching row based on SummaryID` is this a correct assumption..? can you show how you are connecting to the Excel Spread sheet..? do you have existing code where you declare Excel Application Instance? – MethodMan Feb 13 '13 at 18:52

3 Answers3

2

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

idlehands23
  • 326
  • 2
  • 10
0

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...

John Bustos
  • 19,036
  • 17
  • 89
  • 151
  • I am sure there should be a method of simply getting an entire row. 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. – Moh Najdawi Feb 13 '13 at 18:33
  • ... I'm not sure what you're getting at, but I still believe the fastest way would be by treating the Excel file as a DB - If you want the data from each sheet separate, you simply select distinct `SummaryID` from Summary sheet then Select * from each of the other 2 sheets where `SummaryID` is in that list. You're done! – John Bustos Feb 13 '13 at 18:46
0

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 );
Justin
  • 4,002
  • 2
  • 19
  • 21