Net-informations.com
SiteMap  | About    

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 ADO.NET , We can use the SELECT command like in SQL Operations.

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



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






net-informations.com (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.