求救！C#做Excel导入功能，oledbConnect对象创建不了

Question

源码如下：/// <summary>
    /// 得到需要导入的数据
    /// </summary>
    /// <param name="filepath"></param>
    /// <returns></returns>
    private System.Data.DataTable GetImportData(string filepath)
    {
        try
        {
            //string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0";
            string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D://Books2.xls;Extended Properties=Excel 8.0";
            System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(connstr);
            string sql = "select * from [Sheet1$]";//选择第一个数据工作簿的数据
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(sql, con);
            System.Data.DataTable dt = new System.Data.DataTable();
            adapter.Fill(dt);
            con.Close();
            con.Dispose();
            return dt;
        }
        catch(Exception e)
        {
            return new System.Data.DataTable();
        }
    }这个代码我是写在aspx页面上的，不是写在.cs页面上的。调试时这一句new System.Data.OleDb.OleDbConnection(connstr);报异常，异常消息“ServerVersion”引发了“System.InvalidOperationException”类型的异常，
请问该怎么解决啊

Answer 1

给一个我写的例子：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;namespace ExcelToSQL
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }        private void Form1_Load(object sender, EventArgs e)
        {        }        private void btnOpen_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
           
            ofd.Filter = "*.xls|";
            ofd.CheckFileExists = true;
            ofd.CheckPathExists = true;
            ofd.ShowDialog();
            this.textBox1.Text = ofd.FileName.ToString();        }        private void btnInsert_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; " + "Data Source =" + this.textBox1.Text + ";Extended Properties=Excel 8.0";
            OleDbConnection myConn = new OleDbConnection(strCon);
            string sql = "Select * FROM [Sheet1$]";
            try
            {
                myConn.Open();
                OleDbDataAdapter oda = new OleDbDataAdapter(sql, myConn);
                
                oda.Fill(ds, "[Sheet1$]");
                myConn.Close();            }
            catch (Exception ex)
            {                MessageBox.Show(ex.Message);
            }
            string server = this.txtServer.Text.Trim();
            string db = this.txtDB.Text.Trim();
            string username = this.txtUserName.Text.Trim();
            string userpwd = this.txtUserPwd.Text.Trim();            //这里的连接用来将数据写入SQLDB
            string connectionString = @"server="+server+"; database="+db+"; uid="+username+";pwd="+userpwd+"";
            SqlConnection con = new SqlConnection(connectionString);
            string sqlGetAllDB = "select * from tb_Lot";
            SqlDataAdapter daAllDB = new SqlDataAdapter(sqlGetAllDB, con);
            DataSet dsAllDB = new DataSet();
            DataGrid mygrid = new DataGrid();
            mygrid.BeginInit();
            mygrid.Location = new System.Drawing.Point(10, 240);
            mygrid.Width = 1020;
            mygrid.Height = 300;
            this.Controls.Add(mygrid);
            mygrid.EndInit();
            mygrid.SetDataBinding(ds, "[Sheet1$]");            try
            {
                int num = ds.Tables[0].Rows.Count;
                for (int i = 0; i < num; i++)
                {
                    string ch1 = mygrid[i, 0].ToString();
                    string ch2 = mygrid[i, 1].ToString();
                    string ch3 = mygrid[i, 2].ToString();
                    string strii = "select * into from" + ds.Tables[0].TableName;
                    string strsql = "insert into tb_Lot values('" + ch1 + "','" + ch2 + "','" + ch3 + "')";
                    con.Open();
                    System.Data.DataTable dt = new System.Data.DataTable("tb_Lot");
                    SqlDataAdapter da = new SqlDataAdapter(strsql, con);
                    da.Fill(dt);
                    this.lblmessage.Text = "数据导入成功!";
                    this.groupBox2.Visible = true;                    
                    daAllDB.Fill(dsAllDB, "tb_Lot");
                    this.dataGridView1.DataSource = dsAllDB.Tables[0];
                    
                    con.Close();
                }
            }
            catch (Exception ex)
            {                MessageBox.Show("数据库连接失败！");
            }
        }
    }
}

Answer 2

using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';Data Source=" + "路径"))
            {
//....
}

Answer 3

主要是 这一句new System.Data.OleDb.OleDbConnection(connstr);报异常啊，不知道这一句为什么要报异常？是不是我的excel版本问题？我的是2003的版本

Answer 4

针对Excel97-2003的连接字符串：string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=”+filepath+";Extended Properties=Excel 8.0";
针对Excel2007的连接字符串：
string connstr=@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+filepath+
";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; 

Answer 5

操作Excel2003 (OLEDB)string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+"Data Source= " + filepath + ";" + "Extended Properties= Excel 8.0;";///数据库连接
OleDbConnection con = new OleDbConnection(sConnectionString);

Answer 6

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;
using System.IO;namespace WindowsFormsApplication3
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void Write(DataTable tb)
        {
            StringBuilder sb = new StringBuilder();
            foreach (DataRow dr in tb.Rows)
            {
                //时间类型的数据精确到毫秒级写入文本
                sb.Append(dr["姓名"].ToString() + ",");
                sb.Append(dr["传真号"].ToString() + System.Environment.NewLine);
            }
            byte[] s = System.Text.Encoding.UTF8.GetBytes(sb.ToString());            //表名， 打开方式：新建/已存在则覆盖，访问方式：文件的写访问
            //excel 写入 文件名字ib_data.txt
            FileStream fs = new FileStream("ib_data.txt", FileMode.Create, FileAccess.Write);
            fs.Write(s, 0, s.Length);
            fs.Close();
        }
        private void button1_Click(object sender, EventArgs e)
        {
             OpenFileDialog dia = new OpenFileDialog();
             if (dia.ShowDialog() == DialogResult.OK)
             {
                 //文件路径
                 string FilePath = dia.FileName;
                 DataSet dr = ImportFromExcel(FilePath);
                 this.dataGridView1.DataSource = dr.Tables[0];
                 Write(dr.Tables[0]);
         //   DataSet dt = qMYS;
             }        }
            #region Excel导入SQL数据库
        /// <summary>
        /// 获取Excel数据表列表
        /// </summary>
        /// <returns></returns>
        public static ArrayList GetExcelTables(string FilePath)
        {
            //将Excel架构存入数据里
            System.Data.DataTable dt = new System.Data.DataTable();
            ArrayList TablesList = new ArrayList();            if (File.Exists(FilePath))
            {
                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet." +
                     "OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + FilePath))
             
                {
                    try
                    {
                        conn.Open();
                        dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    }
                    catch (Exception exp)
                    {
                        MessageBox.Show(exp.Message);
                    }                    //获取数据表个数
                    int tablecount = dt.Rows.Count;
                    //for (int i = 0; i < tablecount; i = i + 2)
                    for (int i = 0; i < tablecount; i++)
                    {
                        string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
                        if (TablesList.IndexOf(tablename) < 0)
                        {
                            TablesList.Add(tablename);
                        }
                    }
                }
            }
            return TablesList;
        }        /// <summary>
        /// 导入Excel数据表至DataTable（第一行作为表头）
        /// </summary>
        /// <returns></returns>
        public static System.Data.DataSet FillDataSet(string FilePath)
        {
            if (!File.Exists(FilePath))
            {
                throw new Exception("Excel文件不存在！");
            }            ArrayList TableList = new ArrayList();
            TableList = GetExcelTables(FilePath);
            if (TableList.Count <= 0)
            {
                return null;
            }
            System.Data.DataTable table;
            System.Data.DataSet ds = new DataSet();
            //string fileName = "d:\\123.xls";
           // string excelStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";//execl 2003
            //string excelStr = "Provider= Microsoft.Ace.OleDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";//execl 2007以上（需要装个AccessDatabaseEngine引擎，网上找找）
            OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");
            try
            {
                if (dbcon.State == ConnectionState.Closed)
                {
                    dbcon.Open();
                }
                for (int i = 0; i < TableList.Count; i++)
                {
                    string dtname = TableList[i].ToString();
                    try
                    {
                        OleDbCommand cmd = new OleDbCommand("select * from [" + dtname + "$]", dbcon);
                        OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                        table = new DataTable(dtname);
                        adapter.Fill(table);
                        ds.Tables.Add(table);
                    }
                    catch (Exception exp)
                    {
                        MessageBox.Show(exp.Message);
                    }
                }
            }
            finally
            {
                if (dbcon.State == ConnectionState.Open)
                {
                    dbcon.Close();
                }
            }
            return ds;
        }        /// <summary>
        /// Excel导入数据库
        /// </summary>
        /// <returns></returns>
        public static DataSet ImportFromExcel(string FilePath)
        {
            return FillDataSet(FilePath);        }
        #endregion     
    }
}