Read and Import Excel File into DataSet

Through OLEDB, the programmers can interact with the files programmatically, ensuring that the data stored in the cells are well manipulated within the document. This alternative technique editor is not only flexible and versatile, but also creates the possibilities of several operations, which do not require the Excel Object for the completion.

OLEDB (Object Linking and Embedding Database)

Through the OLEDB, the developers/programmers can interact with Excel files programmatically, and easily, allowing for cells content manipulation in the document. This represents an alternative way to process excel files and do diverse tasks without relying on Excel Object. With the Excel Object excluded, the method becomes more efficient and flexible.

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()); } } } }





Related Topics
  1. How to create Excel file in C#
  2. How to open an Excel file in C#
  3. How to read an Excel file using C#
  4. How to programmatically Add New Worksheets
  1. How to delete worksheet from an excel file
  2. How to format an Excel file using C#
  3. How to insert a picture in excel from C# App
  4. How to insert a background picture in excel
  5. How to create Excel Chart from C#
  6. How to export excel chart from C#
  7. How to excel chart in C# picturebox
  8. C# data validation input box in excel file
  9. How to insert data to Excel file using OLEDB
  10. How to update data in Excel file using OLEDB
  11. How to export databse to excel file
  12. How to export DataGridView to excel file