How to read MS Excel 2007 using a connection string in C#

• Download ReadExcel07_demo.zip - 12.1 KB
• Download ReadExcel07_src.zip - 0.8 KB

Introduction

This article explains how to connect to Microsoft Excel 2007 using ADO.NET Connection string and populate a DataGridView on a form.

Using the code

First create a C# Windows application using Visual Studio. Add the following controls to the main form from the tool box.

- DataViewGrid control (name as dgvExcelList)

- BindingSource control (name as dataBindingSrc)

- Button control (name as btnPopulate)

Now create open MS Excel 2007 and enter few records. Save the file and close Excel.

I have already created a sample Excel 2007 file which is available in the project folder of the demo project. If you want you can use it by copying to the C:\. The name of the Excel file is Members.xlsx

Copy the following codes into the btnPopulate_Click event and run the application. you will find the dgvExcelList is filled with all the entries in the Sheet1 of the Excel file.

Note: If you want to to use your own Excel file or saved the Members.xlsx file in a different location other than C:\ change the file and path name from the connection string.

Code:

// You can change C:\Members.xlsx to any valid path 

// where the file is located.


string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
    FONT-SIZE: 10pt; COLOR: #a31515; FONT-FAMILY: 'Courier New'">Data Source=C:\Members.xlsx;Extended
    FONT-SIZE: 10pt; COLOR: #a31515; FONT-FAMILY: 'Courier New'">Properties=""Excel 12.0;HDR=YES;"""; 
// if you don't want to sho the header row (first row) in the grid

// use 'HDR=NO' in the string


string strSQL = "SELECT * FROM [Sheet1$]";
OleDbConnection excelConnection = new OleDbConnection(connectionString);
excelConnection.Open(); // this will open excel file

OleDbCommand dbCommand = new OleDbCommand(strSQL,excelConnection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

// create data table

DataTable dTable = new DataTable();

dataAdapter.Fill(dTable);

// bind the datasource

dataBingingSrc.DataSource = dTable;
// assign the dataBindingSrc to the DataGridView

dgvExcelList.DataSource = dataBingingSrc;


// dispose used objects

dTable.Dispose()
dataAdapter.Dispose();
dbCommand.Dispose();
excelConnection.Close();
excelConnection.Dispose();

Your suggestions and comments are most welcome