将Excel导入Datagridview [英] Import Excel to Datagridview

问题描述

我正在使用这个代码来打开一个excel文件并保存在DataGridView中：

  string name =Items ; 
 string constr =Provider = Microsoft.Jet.OLEDB.4.0; Data Source =+ Dialog_Excel.FileName.ToString（）+;扩展属性= \Excel 8.0; HDR =是; \ ;; 
 OleDbConnection con = new OleDbConnection（constr）; 
 OleDbDataAdapter sda = new OleDbDataAdapter（Select * From [+ name +$]，con）; 
 DataTable data = new DataTable（）; 
 sda.Fill（data）; 
 grid_items.DataSource = data; 
  

如上所述：
这个YouTube链接

但是我收到ISAM错误。任何建议？

解决方案

由于您还没有回覆我上面的评论，我正在为两者发布解决方案。

您在扩展属性

$ b中缺少'
$ b

对于 Excel 2003 ，请尝试此（ 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+ 
;扩展属性='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，我早就停止了与Jet的工作。我现在使用ACE。

  private void button1_Click（object sender，EventArgs e）
 {
 String name =项目; 
 String constr =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ 
C：\\\Sample.xls+ 
;扩展属性='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; 
} 
  

对于 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+ 
;扩展属性='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; 
} 
  

I am 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 ISAM error. Any suggestion?

解决方案

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

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

这篇关于将Excel导入Datagridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！