如何把excel文件导入到数据库中

我现在做一个教师工资管理系统，但是要求以后更新的是用excel导入数据库。这个应该怎么弄，求解。~

12 个解决方案

#1

http://blog.sina.com.cn/s/blog_66588a630100tj6w.html

#2

mssql里面自带导入导出工具，可以选择数据源，来自excel。要在程序里面做的话，就先把数据读出，再写入sql数据库里面吧

#3

http://topic.csdn.net/u/20120512/22/c199427b-f55f-4a5c-8f8e-4b90e9a15b83.html
这里有刚发的

#4

楼上的能说下具体应该加一些什么控件吗。谢了

#5

楼主楼阁邮箱吧，我把我做的这部分发给你

#6

除非操作excel用到excel.dll。不然不用其它控件。

#7

算了直接说吧，我忘了放哪了，加一个button或一个工具栏toolStripButton用来获取excel的路径，当然要用到openFileDialog来选择文件，选完这之后加一个button或一个工具栏toolStripButton用来将刚刚选择的excel文件导入

#8

string strConn = string.Empty;
                 if (filePath.Substring(filePath.LastIndexOf('.')).Equals(".xls"))
                 {
                     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                 }
                 else
                 {
                     strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                 }
                 OleDbConnection OleConn = new OleDbConnection(strConn);
                 OleConn.Open();
                 string sql = "SELECT * FROM [" + tableName + "]";//[Sheet1$]";//可更改Sheet名称，比如sheet2，等等

                 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                 DataSet ds = new DataSet();
                 OleDaExcel.Fill(ds, tableName);
                 OleConn.Close();
                 return ds;

网上给你找的操作excel的代码，得到数据集你应该知道怎么操作了吧，实在不行写个循环一条一条往数据库写。
这个还真不知道有什么控件可以直接用的，.Net提供的控件是不错，但是不是万能的，很多代码还是要自己写的。要不然估计什么架构师，技术总监都没用了，也没有什么资深工程师，中级工程师了，大家伙一起上阵，狂拖控件一切搞定。

#9

引用 2 楼  的回复:

mssql里面自带导入导出工具，可以选择数据源，来自excel。要在程序里面做的话，就先把数据读出，再写入sql数据库里面吧

这个办法最简单，但是不可能每次都让你直接操作数据库的，何况楼主应该是做一个系统，以后交给别人使用，肯定要用程序导入导出了。

#10

楼上说的对啊，是要就是用户不会用数据库，然后直接可以用execl导出数据。

#11

我现在就是用FileUpload还有Button，然后Button代码
  
  protected void btnUpload_Click(object sender, EventArgs e)
    {
         string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString.ToString();  
        OracleConnection con = new OracleConnection(strConnection);  
        con.Open();    
        //连接数据库，并打开数据库   
        if (Convert.ToInt32(lblmes.Text) > 0)  
  
       {                     
        foreach (GridViewRow row in this.gridview1.Rows)  
       {  
  
        string zsbh = row.Cells[0].Text.ToString();  
        string xm = row.Cells[1].Text.ToString();  
       //DateTime kssj =DateTime.Parse(row.Cells[2].Text);   
      string sfcx = row.Cells[2].Text.ToString();  
       //string zslx = row.Cells[4].Text.ToString();   
       string strSQL = "insert into tb_classic_joke(num,笑话一则,item) values('" + zsbh + "','" + xm + "','" + sfcx + "')";  
       //数据库中必须存在该表zs               
        OracleCommand com = new OracleCommand(strSQL, con);  
       com.ExecuteNonQuery();  
  
       this.Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('导入成功！');</script>");  
       }  
 
       }                    
      if (lblmes.Text != "" || Convert.ToInt32(lblmes.Text) != 0)  
        {  
       this.Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('数据不能为空！');</script>");  
       }  
        con.Close();  

           }  
    }

显示错误 11 找不到类型或命名空间名称“OracleCommand”(是否缺少 using 指令或程序集引用?)

#12

这个功能我做过的。用了一个组件。Net.SourceForge.Koogra.dll

  //////////////////////////////内存流上传数据///////////////////////////////////
        if (FileUpload1.HasFile)
        {
            #region 验证文件格式
            //此处需要判断文件格式
            string str = FileUpload1.PostedFile.ContentType;
            //Response.Write("文件类型：" + str);
            string filename = "";
            FileExtension[] fe = { FileExtension.EXCEL };
            
           if (IsAllowedExtension(FileUpload1, fe) && (str == "application/vnd.ms-excel"))
            {
                string fileExt = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                errpt.Text = "文件类型为：" + str + "<br>" + "验证通过！";
                //filename = "/Images/" + DateTime.Now.ToString("yyyyMMddHHmmss") + fileExt;   
                //FileUpload1.PostedFile.SaveAs(Server.MapPath(filename));   
            }
            else
            {
                errpt.Text = "文件类型为：" + str + "<br>" + "验证未通过！";
                return;
            }
            #endregion

            #region 数据导入
            DataTable dt = null;

            try
            {
                using (System.IO.MemoryStream stream = new System.IO.MemoryStream(FileUpload1.FileBytes))
                {
                    dt = ExcelUtils.TranslateToTable(stream, "sheet1");
                    errpt.Text ="数据条数为："+ (dt.Rows.Count-1).ToString();

                    devicesBLL bll = new devicesBLL();
                    if (dt.Rows.Count!=0)
                {
                        if (bll.ins_Batch(dt))
                        {
                            Label1.Text = "批量插入成功！";
                           
                        }
                        else
                        {
                            Label1.Text = "批量插入失败！请检查原数据格式";
                            return;
                        }
                     

                }
                  

                }
               
             
            }
            catch (Exception ex)
            {
                errpt.Text = "处理数据文件错误";
                errpt.Text = ex.Message;

            }
            #endregion

        }
        else
        {
            errpt.Text = "请选择数据文件";
        }
前台的。
/////////////////////////////////////////////////////////////////////////
 ////////////////////////读取最大值////////////////////////////
        bool tag = false;
        mydbconn.Open();
        mytran = mydbconn.BeginTransaction();

       
        OracleCommand cmdReadMaxXH = new OracleCommand("select id from main_devices_sheet2 where id=(select nvl(max(to_number(id)),0) from main_devices_sheet2) for update", mydbconn); //max()在对number类型和为空时存在Bug
        cmdReadMaxXH.Transaction = mytran;
        try
        {
            Decimal id = Convert.ToDecimal(cmdReadMaxXH.ExecuteScalar()) + 1;
            Decimal num = id;
            dt.Columns.Add("id");
            //dt.Columns["id"].DataType = System.Type.GetType("System.Decimal");


            for (int i = 1; i < dt.Rows.Count; i++) /////////////////???????????????????????????字段
            {
                dt.Rows[i]["id"] = (num++).ToString();
            }


        }
        catch (Exception ex)
        {
            errpt = ex.Message + "ins_Batch.SQL_READ_MAX_ID2";
            mytran.Rollback();
            mydbconn.Close();
            tag = false;
            return tag;
        }



        ////////////////////////批量导入////////////////////////////
        OracleDataAdapter OracleAdapter = new OracleDataAdapter();
        string insertSql = "insert  into main_devices_sheet2 (id,dev_name,dev_type,dev_category,dev_subcategory,dev_brand,dev_model,dev_receivecom,dev_receivecom2,dev_configuration,dev_use,dev_subuse,dev_receiveinst,dev_position,dev_source,dev_state,dev_onlinetime,dev_useman,dev_maintenstart,dev_maintenend,dev_maintencom,dev_machinenumber,dev_snnumber,dev_maintenman,dev_maintentel,dev_apply,dev_plan,dev_contract,dev_project,dev_originalvalue,dev_fixedassetsnum,dev_assetsvalue,dev_summary) values (:id,:dev_name,:dev_type,:dev_category,:dev_subcategory,:dev_brand,:dev_model,:dev_receivecom,:dev_receivecom2,:dev_configuration,:dev_use,:dev_subuse,:dev_receiveinst,:dev_position,:dev_source,:dev_state,:dev_onlinetime,:dev_useman,:dev_maintenstart,:dev_maintenend,:dev_maintencom,:dev_machinenumber,:dev_snnumber,:dev_maintenman,:dev_maintentel,:dev_apply,:dev_plan,:dev_contract,:dev_project,:dev_originalvalue,:dev_fixedassetsnum,:dev_assetsvalue,:dev_summary)";
        OracleAdapter.InsertCommand = new OracleCommand();
        OracleAdapter.InsertCommand.CommandType = CommandType.Text;
        OracleAdapter.InsertCommand.CommandText = insertSql;
        OracleParameter[] insertParms = {
                    new OracleParameter(":id", OracleType.VarChar,2000, "id"),   
                    new OracleParameter(":dev_name", OracleType.VarChar, 2000, "dev_name"),   
                    new OracleParameter(":dev_type", OracleType.VarChar, 2000, "dev_type"),
                    new OracleParameter(":dev_category", OracleType.VarChar,2000, "dev_category"),  
                    new OracleParameter(":dev_subcategory", OracleType.VarChar,2000, "dev_subcategory"), 
                    new OracleParameter(":dev_brand", OracleType.VarChar,2000, "dev_brand"),
                    new OracleParameter(":dev_model", OracleType.VarChar,2000, "dev_model"),
                    new OracleParameter(":dev_receivecom", OracleType.VarChar,2000, "dev_receivecom"),
                    new OracleParameter(":dev_receivecom2", OracleType.VarChar,2000, "dev_receivecom2"),
                    new OracleParameter(":dev_configuration", OracleType.VarChar,2000, "dev_configuration"),
                    new OracleParameter(":dev_use", OracleType.VarChar,2000, "dev_use"),
                    new OracleParameter(":dev_subuse", OracleType.VarChar,2000, "dev_subuse"),
                    new OracleParameter(":dev_receiveinst", OracleType.VarChar,2000, "dev_receiveinst"),
                    new OracleParameter(":dev_position", OracleType.VarChar,2000, "dev_position"),
                    new OracleParameter(":dev_source", OracleType.VarChar,2000, "dev_source"),
                    new OracleParameter(":dev_state", OracleType.VarChar,2000, "dev_state"),
                    new OracleParameter(":dev_onlinetime", OracleType.VarChar,2000, "dev_onlinetime"),
                    new OracleParameter(":dev_useman", OracleType.VarChar,2000, "dev_useman"),
                    new OracleParameter(":dev_maintenstart", OracleType.VarChar,2000, "dev_maintenstart"),
                    new OracleParameter(":dev_maintenend", OracleType.VarChar,2000, "dev_maintenend"),
                    new OracleParameter(":dev_maintencom", OracleType.VarChar,2000, "dev_maintencom"),
                    new OracleParameter(":dev_machinenumber", OracleType.VarChar,2000, "dev_machinenumber"),
                    new OracleParameter(":dev_snnumber", OracleType.VarChar,2000, "dev_snnumber"),
                    new OracleParameter(":dev_maintenman", OracleType.VarChar,2000, "dev_maintenman"),
                    new OracleParameter(":dev_maintentel", OracleType.VarChar,2000, "dev_maintentel"),
                    new OracleParameter(":dev_apply", OracleType.VarChar,2000, "dev_apply"),
                    new OracleParameter(":dev_plan", OracleType.VarChar,2000, "dev_plan"),
                    new OracleParameter(":dev_contract", OracleType.VarChar,2000, "dev_contract"),
                    new OracleParameter(":dev_project", OracleType.VarChar,2000, "dev_project"),
                    new OracleParameter(":dev_originalvalue", OracleType.VarChar,2000, "dev_originalvalue"),
                    new OracleParameter(":dev_fixedassetsnum", OracleType.VarChar,2000, "dev_fixedassetsnum"),
                    new OracleParameter(":dev_assetsvalue", OracleType.VarChar,2000, "dev_assetsvalue"),
                    new OracleParameter(":dev_summary", OracleType.VarChar,2000, "dev_summary")
                    };
        OracleAdapter.InsertCommand.Parameters.AddRange(insertParms);
        OracleAdapter.InsertCommand.Connection = mydbconn;
        OracleAdapter.InsertCommand.Transaction = mytran;

        try
        {
            dt.Rows[0].Delete();


            int r = OracleAdapter.Update(dt);
            dt.AcceptChanges();

            mytran.Commit();
            tag = true;
            return tag;

        }
        catch (Exception ex)
        {

            errpt = ex.Message + "ins_Batch.insertSql";
            mytran.Rollback();
            mydbconn.Close();
            tag = false;
            return tag;
        }