How to open Excel file in DataGridview

Kunal Vaishya
12y
30.9k
0
1

Blog

WindowsFormsApplication1.zip

Setp 1 : First Start visual Studion and Create new Project in C# Lnguages

Step 2 : Go Design Form and put a gridview and a button on Form ;

Step 3 : Use Namespace

using System.Data;
using System.Data.OleDb;

Setp 4 : Create Event og Button Write code in

private void Button1_Click(object sender, EventArgs e)
{
     OpenFileDialog op = new OpenFileDialog();
     op.Filter = "Excel 97 - 2003|*.xls|Excel 2007|*.xlsx";
     if (op.ShowDialog() == System.Windows.Forms.DialogResult.OK)
     {
         if (File.Exists(op.FileName))
         {
             string[] Arr = null;
             Arr = op.FileName.Split('.');
             if (Arr.Length > 0)
             {
                 if (Arr[Arr.Length - 1] == "xls")
                 sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                 op.FileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
             }
             else if (Arr[Arr.Length - 1] == "xlsx")
             {
                 sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + op.FileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES';";
             }
         }
             FillData();
         }
     }
}
public string sConnectionString;
private void FillData()
{
     if (sConnectionString.Length > 0)
     {
         OleDbConnection cn = new OleDbConnection(sConnectionString);
         {
             cn.Open();
             DataTable dt = new DataTable();
             OleDbDataAdapter Adpt = new OleDbDataAdapter("select * from [sheet1$]", cn);
             Adpt.Fill(dt);
             DataGridView1.DataSource = dt;
         }
         catch (Exception ex)
         {
         }
     }
}

Screen Shot