Read and Import Excel File into DataSet

In the earlier examples, we utilized the Microsoft Excel 12.0 Object Library to read from and write to Excel files. However, in C#, it is also possible to perform operations such as inserting, editing, deleting, and selecting cell content in an Excel file without employing the Excel Object. This can be accomplished using the OLEDB (Object Linking and Embedding Database) approach.

OLEDB (Object Linking and Embedding Database)

By using OLEDB, developers can interact with Excel files programmatically, enabling seamless manipulation of cell data within the file. This alternative method provides flexibility and versatility in working with Excel files, presenting an efficient approach to perform various operations without directly relying on the Excel Object.

Read Excel Sheet Data into DataTable

In order to perform operations such as inserting, editing, deleting, and selecting cell content in an Excel file without relying on the Excel Object, we utilize components such as OleDbConnection, OleDbDataAdapter, and DataSet within the ADO.NET framework. Importing the System.Data namespace into the project is essential for utilizing these components effectively. When reading content from an Excel file using ADO.NET, we can employ the SELECT command, similar to SQL operations, to retrieve the desired data from the Excel file. This approach offers a seamless integration with Excel files, allowing for efficient data manipulation and retrieval without direct dependency on the Excel Object model.

sample Select sql

sql = "select * from [Sheet1$]"

Here is the sample Excel file .

csharp-excel-oledb-select

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

csharp-excel-oledb-read 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()); } } } }