C# DataGridView Excel - Import Data

June 28, 2017 Oclemy C# Excel, C# DataGridView 1 minute, 9 seconds

We want to see how to import Data from an Excel file to a DataGridView.Thats the aim.

Its easy because Excel and dataGridVeiw both are tabular,hence we can simply map their columns and rows.In fact its even easier as we can use OleDB class to set the Excel as the data source of our DataGridView.

Source Code

Below is the only class we need.  

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CS_DGView_Excel
{
  public partial class Form1 : Form
  {
    String conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\Excel\players.xls';Extended Properties=Excel 8.0;";
    OleDbConnection con;
    OleDbDataAdapter adapter;

    public Form1()
    {
      InitializeComponent();
    }

    private void loadBtn_Click(object sender, EventArgs e)
    {
      load();
    }

    private void clearBtn_Click(object sender, EventArgs e)
    {
      dataGridView1.DataSource = new DataTable();
    }

    private void load()
    {
      con = new OleDbConnection(conString);

      String sql = "SELECT * FROM [Sheet0$]";

      //ADAPTER
      adapter = new OleDbDataAdapter(sql,con);

      DataSet ds=new DataSet();

      adapter.Fill(ds);

      dataGridView1.DataSource = ds.Tables[0];

      //CLOSE CON
      con.Close();
    }
  }
}

  Conclusion

We have seen how to set the Excel as our data source for our datagridview.We have used OleDBdataAdapter passing in our query statement as well the OleDBConnection object.Of course you shall need to specify the correct path for the Excel file in your computer.

Cheers.

Comments