C# excel导入导入数据库

2011-04-19 08:41:13 987 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

heibaoliangjiecan 墨衍会员 · AI 创作全网分发关注 + 加入社区

C#导入excel到数据库这几天又碰到了要导excel数据到数据库中，之前在网上也是找，东拼西凑也是把功能弄出来了，今天写个博客记录下。首先页面的代码 @using (Html.BeginForm("Browse", "OutGygx", FormMethod.Post, new { enctype = "multipart/form-data", id = "form_Upload" })) { ... 阅读详情

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

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

...全文

银河麒麟服务器（ky10 server）arm、x86安装qemu虚拟机接下来就可以新建服务器了。阅读详情

heibaoliangjiecan

C# 111,128 成员 642,536 内容

+ 关注 + 加入社区

切换为时间正序

Journey_ZZ 2011-04-19

打赏
举报



//读取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;

w281314342 2011-04-19

打赏
举报

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

langyinghong 2011-04-19

打赏
举报

 /// <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);

    }

曾经的我们是那么的年轻 2011-04-19

打赏
举报

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

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

porschev 2011-04-19

打赏
举报

网上有很多示例。。

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

keeya0416 2011-04-19

打赏
举报





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

            }

        }

jeje 2011-04-19

打赏
举报

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

    }

Just4life 2011-04-19

打赏
举报

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

pwxcomer 2011-04-19

打赏
举报

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

xzf_fancy 2011-04-19

打赏
举报

用控件啊

C# excel导入导入数据库

相关推荐