Read Excel File With C# (OLEDB)

Automation of an Excel file allows us to doing various operations from C#. We can automate an Excel file from C# in two ways. Using Excel Object Model (using Microsoft.Office.Interop.Excel) in one way and another way is using Microsoft Jet Engine (OLEDB) to connect Excel from CSharp.

Using Microsoft Jet Engine (OLEDB) method to connect and read excel is explained in this article. You can also see article “Read Excel File With C# (Interop)” to read Excel Object Model (using Microsoft.Office.Interop.Excel) method to connect and read excel.

First we are creating an excel file to use in this article. Filename is “classroom.xlsx” (office 2010 file) and first worksheet named as “class_list”. We are filling the worksheet as follows;

In MS Visual Studio (used 2010), open a new Windows Form Application. By using textbox(txtFileDestination), button(btnBrowse), label(lblDataToShow), dataGridView(dataGridView1) and openFileDialog controls create a form as fallows;

Double click the browse button and start coding. First you msut to add “using System.Data.OleDb” to project. Codes are as fallows,

 using System;  
 using System.Collections.Generic;  
 using System.ComponentModel;  
 using System.Data;  
 using System.Drawing;  
 using System.Linq;  
 using System.Text;  
 using System.Windows.Forms;  
 //you have to add namespace for OLEDB  
 using System.Data.OleDb;  
 namespace excelReading  
   public partial class Form1 : Form  
     public Form1()  
     private void btnBrowse_Click(object sender, EventArgs e)  
       //by clicking browse button file dialog opens and when you selected excel file  
       // file destination is writen to textbox and also a string variable  
       txtFileDestination.Text = openFileDialog1.FileName;  
       string file_destination = openFileDialog1.FileName;  
       //opening connection to excel file selected from above  
       OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file_destination + "; Extended Properties=Excel 12.0");  
       //selecting all data inside the firstpage by using sql statement. For selecting specific part you can change  
       //sql statment like this : "select * from [class_list$A1:A3]"  
       string query = "select * from [class_list$] ";  
       OleDbDataAdapter data_adaptor = new OleDbDataAdapter(query, conn);  
       //closing connection  
       //Defining new datatable and fill datas. Then define the datatable as datagridview's datasource  
       DataTable dt = new DataTable();  
       dataGridView1.DataSource = dt;  
       //reading spesific cell and show in a label control. (Cells[rowNunmer,ColomnNumber])  
       //selecting first 3 colomns in 2nd row and writng in label control. Becouse its index number starts with 0  
       lblDataToShow.Text = dt.Rows[1][0].ToString() + " " + dt.Rows[1][1].ToString() + " " + dt.Rows[1][2].ToString();  

When application is run (F5);

  • click browse button,
  • select “classroom.xlsx” file which was created before,
  • you can see the selected cells written into label control and whole data in dataGridView.


Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir