Getsuyodev, Programming is mainly used when constructing an application. Programming requires knowledge of application domains, algorithms, and programming language expertise. Programming can be developed in different logic based on developer knowledge.

Read and Import Excel File into DataSet



In the previous examples, we used Microsoft Excel 12.0 Object Library for reading or write to an 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.

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 screenshot after reading from Excel file in 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:\\getsuyodev.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());
            }
        }
   }
}

Comments are closed.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More