Import Excel to Datagridview

Question

I m using this code to open an excel file and save it in a DataGridView   string name    Items   string constr    Provider   Microsoft Jet OLEDB 4 0  Data Source     Dialog Excel FileName ToString        Extented Properties    Excel 8 0  HDR Yes       OleDbConnection con   new OleDbConnection constr   OleDbDataAdapter sda   new OleDbDataAdapter  Select   From      name         con   DataTable data   new DataTable    sda Fill data   grid items DataSource   data    As explained on  This youtube link  But I get an ISAM error  Any suggestion

User · Accepted Answer

Since you have not replied to my comment above, I am posting a solution for both.

You are missing ' in Extended Properties

For Excel 2003 try this (TRIED AND TESTED)

    private void button1_Click(object sender, EventArgs e)
    {
        String name = "Items";
        String constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                        "C:\\Sample.xls" + 
                        ";Extended Properties='Excel 8.0;HDR=YES;';";

        OleDbConnection con = new OleDbConnection(constr);
        OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con);
        con.Open();

        OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
        DataTable data = new DataTable();
        sda.Fill(data);
        grid_items.DataSource = data;
    }

BTW, I stopped working with Jet longtime ago. I use ACE now.

    private void button1_Click(object sender, EventArgs e)
    {
        String name = "Items";
        String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        "C:\\Sample.xls" + 
                        ";Extended Properties='Excel 8.0;HDR=YES;';";

        OleDbConnection con = new OleDbConnection(constr);
        OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con);
        con.Open();

        OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
        DataTable data = new DataTable();
        sda.Fill(data);
        grid_items.DataSource = data;
    }

enter image description here

For Excel 2007+

    private void button1_Click(object sender, EventArgs e)
    {
        String name = "Items";
        String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        "C:\\Sample.xlsx" + 
                        ";Extended Properties='Excel 12.0 XML;HDR=YES;';";

        OleDbConnection con = new OleDbConnection(constr);
        OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con);
        con.Open();

        OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
        DataTable data = new DataTable();
        sda.Fill(data);
        grid_items.DataSource = data;
    }

User · Answer

I used the following code  it s working   using System Data OleDb  using System IO  using System Text RegularExpressions   private void btopen Click object sender  EventArgs e       try           OpenFileDialog openFileDialog1   new OpenFileDialog       create openfileDialog Object      openFileDialog1 Filter    XML Files    xml    xls    xlsx    xlsm    xlsb     xml    xls    xlsx    xlsm    xlsb    open file format define Excel Files  xls    xls  Excel Files  xlsx    xlsx        openFileDialog1 FilterIndex   3        openFileDialog1 Multiselect   false           not allow multiline selection at the file selection level      openFileDialog1 Title    Open Text File-R13       define the name of openfileDialog      openFileDialog1 InitialDirectory     Desktop     define the initial directory       if  openFileDialog1 ShowDialog      DialogResult OK           executing when file open               string pathName   openFileDialog1 FileName         fileName   System IO Path GetFileNameWithoutExtension openFileDialog1 FileName          DataTable tbContainer   new DataTable           string strConn   string Empty         string sheetName   fileName          FileInfo file   new FileInfo pathName          if   file Exists    throw new Exception  Error  file doesn t exists              string extension   file Extension         switch  extension                     case   xls                      strConn    Provider Microsoft Jet OLEDB 4 0 Data Source     pathName     Extended Properties  Excel 8 0 HDR Yes IMEX 1                        break            case   xlsx                      strConn    Provider Microsoft ACE OLEDB 12 0 Data Source     pathName     Extended Properties  Excel 12 0 HDR Yes IMEX 1                        break            default                     strConn    Provider Microsoft Jet OLEDB 4 0 Data Source     pathName     Extended Properties  Excel 8 0 HDR Yes IMEX 1                        break                      OleDbConnection cnnxls   new OleDbConnection strConn            OleDbDataAdapter oda   new OleDbDataAdapter string Format  select   from   0      sheetName   cnnxls            oda Fill tbContainer             dtGrid DataSource   tbContainer                        catch  Exception                 MessageBox Show  Error

User · Answer

try this following snippet  its working fine   private void button1 Click object sender  EventArgs e         try                     OpenFileDialog openfile1   new OpenFileDialog                 if  openfile1 ShowDialog      System Windows Forms DialogResult OK                                    this textBox1 Text   openfile1 FileName                                                   string pathconn    Provider   Microsoft jet OLEDB 4 0  Data source     textBox1 Text     Extended Properties   Excel 8 0 HDR  yes                          OleDbConnection conn   new OleDbConnection pathconn                      OleDbDataAdapter MyDataAdapter   new OleDbDataAdapter  Select   from      textBox2 Text         conn                      DataTable dt   new DataTable                       MyDataAdapter Fill dt                      dataGridView1 DataSource   dt                               catch

User · Answer

try the following program  using System  using System Data  using System Windows Forms  using System Data SqlClient   namespace WindowsFormsApplication1   public partial class Form1   Form       public Form1                 InitializeComponent               private void button1 Click object sender  EventArgs e                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    Net-informations com            DtSet   new System Data DataSet            MyCommand Fill DtSet           dataGridView1 DataSource   DtSet Tables 0           MyConnection Close

[c#] Import Excel to Datagridview

Examples related to c#

Examples related to excel

Examples related to datagridview

Examples related to oledb