How to run over cells on Excel in C#?

Question

How i can run over cells in Excel (not .csv) and read the cell values? I need to run all the cells. Thanks in advance.

Answer 1

Here is a sample:

Excel.Application oXL;
    Excel._Workbook oWB;
    Excel._Worksheet oSheet;
    Excel.Range oRng;


     //Start Excel and get Application object.
     oXL = new Excel.Application();
     oXL.Visible = true;

     //Get a new workbook.
     oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
     oSheet = (Excel._Worksheet)oWB.ActiveSheet;

     //Add table headers going cell by cell.
     oSheet.Cells[1, 1] = "First Name";

Answer 2

I think the best way to do this is to treat excel as an ADO.NET datasource. Here is an example: http://www.knowdotnet.com/articles/exceldatasource.html.

This approach let's you easily query data in excel sheets and what is more you don't even have to install Microsoft Excel.

Answer 3

Here's an approach using ADO.NET:

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myfile.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
string provider = "System.Data.OleDb";
DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
connection.Open();
DbCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM [Sheet1$]"
DbDataReader reader = command.ExecuteReader();
while(reader.read()) {
    // process row
}

Look at ConnectionString.com for details on the connection string setup (for example, Excel 2007 is slightly different). When you set the commandText property on command you can set up more sophisticated queries (SELECT * from [Sheet1$] where someColumn <> someValue and someOtherColumn > someOtherValue).

This method has a nice advantage in that if you're already familiar with ADO.NET you can use the knowledge you've acquired there. Further, Excel does not need to be installed on the machine.