Read and Import Excel File into DataSet

In the previous examples we used Microsoft Excel 12.0 Object Library for read or write to and Excel file . In C# without using Excel Object we can insert , edit , delete , select etc. in cell content of an Excel file using OLEDB .

Read Excel Sheet Data into DataTable

Here we are using OleDbConnection , OleDbDataAdapter , DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations . For read the content from an Excel file using |BLBS^ |ADO.NET|BLBE^ | , We can use the SELECT command like in SQL Operations.

sample Select sql

sql = "select * from [Sheet1$]"

Here is the sample Excel file .

Open the connection using OLEDB Provider

(provider=Microsoft.Jet.OLEDB.4.0;Data Source='Your Filename';Extended Properties=Excel 8.0;)

Specify which data you want to read

select * from [Sheet1$]

Excel to Dataset

Here is the screen short after reading from Excel file in C# .

Full Source C#

using System; using System.Drawing; using System.Windows.Forms; using Excel = Microsoft.Office.Interop.Excel; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { try { System.Data.OleDb.OleDbConnection MyConnection ; System.Data.DataSet DtSet ; System.Data.OleDb.OleDbDataAdapter MyCommand ; MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); MyCommand.TableMappings.Add("Table", "TestTable"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dataGridView1.DataSource = DtSet.Tables[0]; MyConnection.Close(); } catch (Exception ex) { MessageBox.Show (ex.ToString()); } } } }

Next : How to insert data to Excel file using OLEDB

Download Source Code

Print Source Code

Related Topics