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?

Satyriasis

What is the EXACT error message? – Protract

@JeremyThompson: Exception: Could not find installable ISAM – Satyriasis

Just as I expected, did you check this resource? or even here – Protract

@JeremyThompson: I just checked it but got no idea what is my connection string problem – Satyriasis

What kind of Excel file are you using? Excel 2003 or Excel 2007+ ? – Galegalea

Answer 1

I am posting a solution for both Excel 2003 and Excel 2007+.

You are missing ' in Extended Properties

For Excel 2003 try this

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

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

Galegalea

What does "Items" refer to here? – Fridell

"Items" is the name of the sheet. If you haven't renamed it, use "Sheet1". – Crumbly

What a great example mister, +1. Do you know if I can use ACE with VS 2010? I wonder where can I get it. Thanks and beautiful example. Cheers.! – Anemia

I found it, sorry my ignorance, was under the name of "Microsoft Office 12.0 Access Database Engine OLE DB Provider" from the Provider list in the visual studio Add Connection Wizard. Awesome! – Anemia

Thanks a lot for your help too ...I wonder if there is away to use something like sheet index instead of its name which is changeable ? – Montford

Answer 2

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!");
     }
  }

Duckweed

Answer 3

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 { }
}

Kacykaczer

Answer 4

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

Barozzi

Answer 5

In my case, I load the excel file with Microsoft.Office.Interop.Excel . I save in datatable to reuse in my project without reload the data from the datagridview again.

 using Excel = Microsoft.Office.Interop.Excel;
 Excel.Application excelApp = new Excel.Application();
 public void LoadDataGridView(DataGridView dataGridView, DataTable dataTable)
    {
        if (excelApp != null)
        {
            Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(parentPath + @"\test.xlsx", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1];

            Excel.Range excelRange = excelWorksheet.UsedRange;
            int rowCount = excelRange.Rows.Count;
            int colCount = excelRange.Columns.Count;
            DataRow row = null;
            for (int i = 1; i <= rowCount; i++)
            {
                if (i != 1)
                    row = dataTable.NewRow();
                for (int j = 1; j <= colCount; j++)
                {
                    if (i == 1)
                        dataTable.Columns.Add(excelRange.Cells[1, j].value);
                    else
                    {
                        if(!String.IsNullOrEmpty(excelRange.Cells[i, 1].value2.ToString()))
                        {
                            row[j - 1] = excelRange.Cells[i, j].value;
                        }
                    }
                }
                if (row != null && !String.IsNullOrEmpty(row[@"Id"].ToString()))
                    dataTable.Rows.Add(row);
            }
            dataGridView.DataSource = dataTable;
            excelWorkbook.Close();
            excelApp.Quit();
        }
    }

Housemaster