Introduction:
Many peoples ask in several forums about how to connect to an Excel file and make use of it as a front end application to store and retrieve data instead of access, SQL server or any other data bases formats. I personaly perfer to use a data base instead but nevertheless, 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:
1.Open an Excel file and populate some of its cells then save it somewhere or if you already have an Excel file ready to be used then make sure that is populated with data
2.Go to Start>Configuration panel>Administration tools> ODBC ressources
3. Open it then select the user data source tab
4. Click add then a window appears, this last one contains the drivers list
5. Select the Microsoft Excel ODBC driver
6. A windows appears and you are invited to enter the data source name, the discription, there is a combo box that enables you to select the witch Excel virsion are you using and finaly, there is a button labeled "Select a file" witch enables you to browse to the targeted Excel file
Enter the data source name as you perfer, this will be used as a data source name later in the code, you can also add a discription of your data source inthe discription text zone
8.The connection is now 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
9. Add the System.Data.Odbc namespace to the project
10. Add this code to the button click event handler
private void btnConnect_Click(object sender, EventArgs e)
{
OdbcConnection oConn = new OdbcConnection();
oConn.ConnectionString = "Dsn=EXCEL";
OdbcCommand oComm = new OdbcCommand();
oComm.Connection = oConn;
oComm.CommandText = "Select * From [Feuil1$A1:C3]";
try
{
DataSet ds = new DataSet();
OdbcDataAdapter oAdapter = new OdbcDataAdapter(oComm);
oConn.Open();
oAdapter.Fill(ds);
dataGridView1.DataSource = ds;
dataGridView1.DataMember = ds.Tables[0].TableName;
label1.Text = "Connection established successfully!!!";
}
catch (IOException caught) { MessageBox.Show(caught.Message); }
catch (OdbcException caught) { MessageBox.Show(caught.Message); }
finally
{
oConn.Close();
}
}
11. Finally, fire up the program, click the button and observe
GodDotneting!!!
|
No responses found. Be the first to respond and make money from revenue sharing program.
|