Click here to Skip to main content
15,867,453 members
Articles / Desktop Programming / Windows Forms

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

Rate me:
Please Sign up or sign in to vote.
4.52/5 (17 votes)
28 Nov 2007CPOL1 min read 277.4K   11.1K   94   33
This article explains how to connect to Microsoft Excel 2007 using a connection string and populate DataGridView
Screenshot - readExcel1.gif

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

Code

C#
// 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();

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.

History

  • 28th November, 2007: Initial post 

License

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


Written By
Software Developer (Senior) Dhivehisoft
Maldives Maldives
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionThis could be interesting here: Pin
dietmar paul schoder29-Jul-14 10:35
professionaldietmar paul schoder29-Jul-14 10:35 
GeneralMy vote of 1 Pin
gs_virdi9-Oct-13 23:24
gs_virdi9-Oct-13 23:24 
Question... Pin
aditya_prestige22-Dec-12 8:56
aditya_prestige22-Dec-12 8:56 
BugGetting Error : Pin
mansooralikhan5-May-12 21:39
mansooralikhan5-May-12 21:39 
GeneralRe: Getting Error : Pin
taith14-Nov-12 5:02
taith14-Nov-12 5:02 
QuestionHow to Import Excel data to Ms access Database Pin
PrabakaranM8-Mar-12 2:34
PrabakaranM8-Mar-12 2:34 
Questioneeception arised after this.... Pin
tripti_kumbhat18-Feb-12 6:24
tripti_kumbhat18-Feb-12 6:24 
GeneralMy vote of 5 Pin
Alisettar30-Dec-11 22:01
Alisettar30-Dec-11 22:01 
QuestionThank You Pin
freshonlineMax21-Oct-11 20:28
freshonlineMax21-Oct-11 20:28 
QuestionDo i need Access installed on my system for this? Pin
RuchiJ22-Sep-10 0:03
RuchiJ22-Sep-10 0:03 
QuestionRetieve the data from excel 2007 when the worksheet is open [modified] Pin
_simi16-Aug-10 23:57
_simi16-Aug-10 23:57 
GeneralMy vote of 5 Pin
didi212013-Aug-10 6:31
didi212013-Aug-10 6:31 
GeneralAlien Code Pin
Wisam E. Mohammed25-Jun-10 6:49
Wisam E. Mohammed25-Jun-10 6:49 
QuestionHow to support different versions of Excel Pin
metazone27-Jan-09 15:02
metazone27-Jan-09 15:02 
AnswerRe: How to support different versions of Excel Pin
CikaPero17-Jul-11 22:23
CikaPero17-Jul-11 22:23 
GeneralFound a bug in vista and VS 2008 Pin
newbieprogrammerguy24-Sep-08 16:05
newbieprogrammerguy24-Sep-08 16:05 
GeneralRe: Found a bug in vista and VS 2008 Pin
ashish diwakar22-Sep-09 19:00
ashish diwakar22-Sep-09 19:00 
GeneralExcel 2007 - Reading more than 256 columns from a sheet using OLEDB Pin
Fad12320-May-08 3:51
Fad12320-May-08 3:51 
GeneralRe: Excel 2007 - Reading more than 256 columns from a sheet using OLEDB Pin
nishit.amin@successcraft.com16-Jun-09 4:04
nishit.amin@successcraft.com16-Jun-09 4:04 
GeneralRe: Excel 2007 - Reading more than 256 columns from a sheet using OLEDB Pin
jvandertol122-Jun-09 9:57
jvandertol122-Jun-09 9:57 
GeneralRe: Excel 2007 - Reading more than 256 columns from a sheet using OLEDB Pin
stranger_From_Tepa6-Nov-09 12:24
stranger_From_Tepa6-Nov-09 12:24 
GeneralRe: Excel 2007 - Reading more than 256 columns from a sheet using OLEDB Pin
stranger_From_Tepa10-Nov-09 13:10
stranger_From_Tepa10-Nov-09 13:10 
QuestionCan I read Opened Excel file continuosly? Pin
smramesh14-Jan-08 9:59
smramesh14-Jan-08 9:59 
GeneralRe: Can I read Opened Excel file continuosly? Pin
smramesh15-Jan-08 12:10
smramesh15-Jan-08 12:10 
QuestionMixed Field issue? Pin
EdwardQ28-Nov-07 6:30
EdwardQ28-Nov-07 6:30 

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.