Click here to Skip to main content
13,258,055 members (48,357 online)
Click here to Skip to main content
Add your own
alternative version


92 bookmarked
Posted 28 Nov 2007

How to Read Microsoft Excel 2007 Using a Connection String in C#

, 28 Nov 2007
Rate this:
Please Sign up or sign in to vote.
This article explains how to connect to Microsoft Excel 2007 using a connection string and populate DataGridView
Screenshot - readExcel1.gif


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 open Microsoft Excel 2007 and enter a 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 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 Sheet1 of the Excel file.

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


// 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 show 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 an Excel file
OleDbCommand dbCommand = new OleDbCommand(strSQL,excelConnection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

// create data table
DataTable dTable = new DataTable();


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

// dispose used objects

Your suggestions and comments are most welcome.


  • 28th November, 2007: Initial post 


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior) Dhivehisoft
Maldives Maldives
No Biography provided

You may also be interested in...


Comments and Discussions

QuestionCan I read Opened Excel file continuosly? Pin
Member 458580414-Jan-08 10:59
memberMember 458580414-Jan-08 10:59 
GeneralRe: Can I read Opened Excel file continuosly? Pin
smramesh15-Jan-08 13:10
membersmramesh15-Jan-08 13:10 
QuestionMixed Field issue? Pin
EdwardQ28-Nov-07 7:30
memberEdwardQ28-Nov-07 7:30 
AnswerRe: Mixed Field issue? Pin
Sarafian29-Nov-07 2:52
memberSarafian29-Nov-07 2:52 
GeneralRe: Mixed Field issue? Pin
MKauffman29-Nov-07 7:34
memberMKauffman29-Nov-07 7:34 
GeneralRe: Mixed Field issue? Pin
Mihadh30-Nov-07 6:42
memberMihadh30-Nov-07 6:42 
GeneralRe: Mixed Field issue? Pin
Nair Machaan9-Dec-07 18:23
memberNair Machaan9-Dec-07 18:23 
AnswerRe: Mixed Field issue? Pin
stevechurcher30-Apr-08 14:04
memberstevechurcher30-Apr-08 14:04 
I know its a while since the last post but in case anyone comes across this and needs to know the answer, I have just had the same issue but solved it by adding IMEX=1 in to the connection string as follows:

myConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=" + strPath + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");

Note the quotes around the extended properties values as without those I got an error.

Also I cannot take the credit for this. I got the answer here: - it worked for me so thought I would post a solution.
GeneralRe: Mixed Field issue? Pin
Mihadh27-Nov-08 10:54
memberMihadh27-Nov-08 10:54 
QuestionRe: Mixed Field issue? Pin
abhi_chadha13-Jan-09 14:12
memberabhi_chadha13-Jan-09 14:12 
AnswerRe: Mixed Field issue? Pin
curro2k7-Mar-09 0:27
membercurro2k7-Mar-09 0:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171114.1 | Last Updated 28 Nov 2007
Article Copyright 2007 by Mihadh
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid