C# excel导入导入数据库

[heibaoliangjiecan]

我要把My.xls这个excel文件导入数据库或者dataSet，我该如何用代码实现？

小弟是个新学C#的，请求各位不吝赐教，能给我一些资料，实例代码，谢谢！

[xzf_fancy]

用控件啊

[pwxcomer]

http://topic.csdn.net/u/20100927/16/cbc9bcdc-9f03-42e3-9c95-036aaa72c629.html

[Just4life]

使用C#提供的操作Excel的类

[jeje]

/// <summary>
    /// 以Excel为数据源获取数据集
    /// </summary>
    /// <returns></returns>
    private DataSet createDataSource()
    {
        string strCon;
        strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/Files/Employee.xls") + ";Extended Properties=Excel 8.0;";
        OleDbConnection con = new OleDbConnection(strCon);
        OleDbDataAdapter da = new OleDbDataAdapter("select * from [Employee$]", con);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

[keeya0416]

        /// <summary>
        /// 读取Excel文档
        /// </summary>
        /// <param name="xPath">文件名称</param>
        /// <param name="sheetIndex">sheet序号</param>
        /// <returns>返回一个数据集</returns>
        public static DataSet readExcel(string xPath, string sheetIndex)
        {
            Microsoft.Office.Interop.Excel.Application ExcelRS = new ApplicationClass();
            Workbook RSbook = ExcelRS.Workbooks.Open(xPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            Worksheet RSsheet = (Worksheet)RSbook.Sheets.get_Item(sheetIndex);
            string sheetName = RSsheet.Name;
            RSbook.Close();
            RSsheet = null;
            RSbook = null;
            ExcelRS = null;
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + xPath + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                conn.Open();
                string strExcel = string.Format("select * from [{0}$]", sheetName);
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
                DataSet ds = new DataSet();
                myCommand.Fill(ds);
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

[porschev]

网上有很多示例。。

自己看看。。。但想找完全和你需求一样的估计难

[曾经的我们是那么的年轻]

+1
[Quote=引用 6 楼 porschev 的回复:]
网上有很多示例。。

自己看看。。。但想找完全和你需求一样的估计难
[/Quote]

[langyinghong]

 /// <summary>
    /// 
    /// </summary>
    /// <param name="path"></param>
    /// <param name="TableName"></param>
    /// <param name="tableName2">如果这个有就以他为表名，没有的话就以TableName</param>
    private DataTable ImportExcel(string Path, string TableName, string tableName2)
    {
        DataTable dt = new DataTable();
        try
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter mycommand = null;
            if (tableName2.Length > 0 && !tableName2.Equals(string.Empty))
                TableName = tableName2;
            strExcel = "select * from [" + TableName + "$]";
            mycommand = new OleDbDataAdapter(strExcel, conn);

            mycommand.Fill(dt);
            conn.Close();

        }
        catch (Exception ex)
        {
            Common.MessageBox.Show(this.Page, "导入失败!原因:" + ex.Message + "");
        }
        return dt;
    }
 protected void Button1_Click(object sender, EventArgs e)
    {
        string path = "";
        if (FileUpload2.HasFile)
        {
            try
            {

                DataTable dt = new DataTable();
                int len = this.FileUpload2.FileName.ToString().Length;
                path = this.FileUpload2.FileName.ToString().Trim();
                path = Server.MapPath("~/Upload/" + path);
                this.FileUpload2.SaveAs(path);
                dt = ImportExcel(path, this.FileUpload2.FileName.ToString().Substring(0, len - 4), this.tbxExcelName.Text);
                if (Session["dt"] != null)
                    Session.Remove("dt");
                Session.Add("dt", dt);
                List<Model.student_user> list = new List<Model.student_user>();
                string msg = string.Empty;
                int num = 0;
                int i = 0;
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {｝
                 ｝
            }
            catch (Exception ex)
            {
                Common.MessageBox.Show(this.Page, "导入失败!原因:" + ex.Message + "");
            }
        }
        else
        {
            Common.MessageBox.Show(this.Page, "选择Excel路径");
            return;
        }
        deleteFile(path);
    }

[w281314342]

SQL 数据库有一个导EXCEL文件的功能，将EXCEL导入到库中，并命名为表，然后将这个表里面的数据查询出来插入到你要导入的表中就OK了。

[Journey_ZZ]

//读取Excel数据

            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + fileName + ";Extended Properties='Excel "
                + (fileName.EndsWith("xls", StringComparison.CurrentCultureIgnoreCase) ? "8" : "12") + ".0;HDR=Yes'";
            DataSet ds = new DataSet();
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等 
                DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

                //包含excel中表名的字符串数组
                for (int k = 0; k < dtSheetName.Rows.Count; k++)
                {
                    string strTableName = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                    string str = "select * from [" + strTableName + "]";
                    OleDbDataAdapter da = new OleDbDataAdapter(str, conn);
                    da.Fill(ds, strTableName.Replace("$", "").ToLower());
                    da.Dispose();
                }
                conn.Close();
                conn.Dispose();
            }
            return ds;