Post
Ask Question

Connect to an Excel file as a data source via OBDC

I wrote this article to answer a question asked about how to connect to an excel file as a data source for a given application, I provide the solution via this ten steps walkthrough.
 
Walkthrough:
 
Let's begin by creating an Excel file and configuring an ODBC connection
  1. Open an Excel file and populate some of its cells then save it somewhere 
  2. Go to Start>Configuration panel>Administration tools> ODBC icon
  3. Open it  then select the tag data source system
     
     
    Figure 1
     
  4. Click add, then the following window Appears
     
     
    Figure 2
     
  5. Select the Driver do Microsoft Excel(*.xls)
  6. Then Apply and the bellow window appears
     
     
    Figure 3
     
  7. In the first field, define the Name or the alias going to be used, say we name it EXCEL for example, in the second field you can put a note according to this alias then click the button select a file, it helps you to browse to your given excel file then click ok, now the ODBC connection is ready to be used, this is the window after filling settings:
     
     
    Figure 4
     
  8. Now, as our connection is ready to be used let's create a new Windows application project and add a data grid view, a button, and a label  into the form that will appear like the bellow form:
     
     
    Figure 5
     
    Change the name of the button to btnconnect and change its text to "Connect to Excel data source", then change the label text to "".
     
  9. Add this code to the button click event handler.
    1. private void btnConnect_Click(object sender, EventArgs e) {  
    2.     OdbcConnection oConn = new OdbcConnection();  
    3.     oConn.ConnectionString = "Dsn=EXCEL";  
    4.     OdbcCommand oComm = new OdbcCommand();  
    5.     oComm.Connection = oConn;  
    6.     oComm.CommandText = "Select * From [Feuil1$A1:C3]";  
    7.     try {  
    8.         DataSet ds = new DataSet();  
    9.         OdbcDataAdapter oAdapter = new OdbcDataAdapter(oComm);  
    10.         oConn.Open();  
    11.         oAdapter.Fill(ds);  
    12.         dataGridView1.DataSource = ds;  
    13.         dataGridView1.DataMember = ds.Tables[0].TableName;  
    14.         label1.Text = "Connection established successfully!!!";  
    15.     } catch (IOException caught) { MessageBox.Show(caught.Message); } catch (OdbcException caught) { MessageBox.Show(caught.Message); } finally {  
    16.         oConn.Close();  
    17.     }  
    18. } 
    But don't forget to include the System.Data.Odbc namespace into your project.
     
  10. Now, run the application and observe.
     
     
    Figure 6
Good dotneting!!!


Similar Articles