Excel数据导入到数据库

   <form id="form1" action="/SS/ScoreStandard/Leading?test=" method="post" enctype="multipart/form-data">
            <div class="con-main" style="height: 200px; padding-top:20px;">
                <div class="list">
                    <span class="add-lable w-p25 text-right valRequired">
                        选择文件：
                    </span>
                    <span class="add-show w-p65">
                        <input type="file" id="upfile" name="upfile" />

                    </span>
                </div>
            </div>   
        </form>

使用表单文件域（input type=”file”）时,

要让form能够传递文件的话，必须要在form标签中加入enctype=”multipart/form-data”

后台

   [HttpPost]
   public ActionResult Leading(string test)
        {
            string msg = "1";
            try
            {
             
                HttpFileCollectionBase files = Request.Files;
                HttpPostedFileBase filebase = null;
                string[] columns = new string[] {  "编码", "内容" };
                if (files.Count > 0)
                {
                    filebase = files[0];
                }
                if (filebase != null)
                {
                    DataSet ds = new DataSet();
                    string ext = Path.GetExtension(filebase.FileName);
                    if (ext == ".xls")
                        ds = RenderDataTableFromExcel(filebase.InputStream, 0);
                    else
                    {
                        ViewBag.Msg = "请上传正确的Excel文件！";
                        return View();
                    }
                    StringBuilder msgBuilder = new StringBuilder();
                    DataTable newTable = new DataTable();
                    newTable.Columns.Add("id", typeof(int));
                    newTable.Columns.Add("ItemCode");
                    newTable.Columns.Add("ItemContent");                  
               
                    var attObjList = new List<AttObj>();
                    foreach (DataTable table in ds.Tables)
                    {
                        if (table.Rows.Count > 0)
                        {
                            for (int i = 0; i < table.Columns.Count; i++)
                            {
                                if (i < columns.Length)
                                    table.Columns[i].ColumnName = columns[i];
                                else
                                    table.Columns.RemoveAt(i);
                            }
                            var depts = _db.QueryOver<GyDepartment>().List();
                            var works = _db.QueryOver<WorkTypeCoe>().List();
                            foreach (DataRow oldRow in table.Rows)
                            {
                                DataRow row = newTable.NewRow();
                                var code = oldRow["编码"].ToString().Trim();
                                if (code.Length > 0)
                                {
                                    var oneCode = code.Substring(0, 1).ToString();
                                    int num;
                                    if (!int.TryParse(oneCode, out num))//转化成功
                                    {
                                        code = code.Substring(1, code.Length - 1);
                                    }
                                    string[] strCode = code.Split('-');
                                    foreach (var item in strCode)
                                    {
                                        int i = 0;
                                        if (!int.TryParse(item, out i))
                                        {
                                            msgBuilder.Append("编码：" + oldRow["编码"].ToString() + " 格式不正确");
                                        }
                                    }                               
                                    var contents = oldRow["内容"].ToString().Trim();
                                    if (contents.Length == 0)
                                    {
                                        msgBuilder.Append("内容不能为空！");
                                    }
                                    else if (contents.Length >90)
                                    {
                                        msgBuilder.Append("内容最多为90汉字！");
                                    }
                                    var explain = oldRow["奖扣说明"].ToString().Trim();
                                    if (explain.Length==0)
                                    {
                                        msgBuilder.Append("奖扣说明不能为空！");
                                    }
                                    else if(explain.Length>20)
                                    {
                                        msgBuilder.Append("奖扣说明最多为10个汉字！");
                                    }
                                 
                                    attObjList.Add(new AttObj { ItemCode = code,  Content = contents+ explain });
                                   
                                    row["ItemCode"] = code;
                                    row["ItemContent"] = contents+ explain;                              
                                    newTable.Rows.Add(row);
                                }
                                else
                                {
                                    msgBuilder.Append("编码不能为空！<br/>");
                                }                          
                            }
                        }
                        else
                        {
                            msgBuilder.Append("当前表格无数据！<br/>");
                        }
                    }
                    msg = msgBuilder.ToString();
                    DataTable logTable = new DataTable();
                    logTable.Columns.Add("Id");
                    logTable.Columns.Add("LogTime");
                    logTable.Columns.Add("UserId");
                    logTable.Columns.Add("LogContent");
                    logTable.Columns.Add("BeforeContent");
                    logTable.Columns.Add("AfterContent");
                    if (string.IsNullOrEmpty(msg))
                    {
                        StringBuilder sqlStr = new StringBuilder();
                        foreach (var att in attObjList)
                        {
                            var list = _db.QueryOver<ScoreStandard>().Where(x => x.DeptCode == deptCode && x.ItemCode == att.ItemCode && x.WorkTypeId == int.Parse(workCoe)).List().FirstOrDefault();
                            if (list != null)
                            {
                                sqlStr.AppendFormat(@" insert into SS_ScoreStandardLog (Id,LogTime,UserId,LogContent,BeforeContent,AfterContent)
                                                  values(newiD(), '{0}', '{1}', '{2}', '{3}', '{4}')", DateTime.Now, SysHelper.LoginUserID, "导入覆盖", "编码:" + list.ItemCode + "评分细则（具体细则：" + list.ItemContent + ")",  "编码:" + att.ItemCode + "评分细则（具体细则：" + att.Content  + ")");
                                sqlStr.AppendFormat(" delete from SS_ScoreStandard where ItemCode='{0}' and WorkTypeId={1} and DeptCode='{2}' ", att.ItemCode, workCoe, deptCode);
                            }
                            else
                            {
                                DataRow rowLog = logTable.NewRow();
                                rowLog["Id"] = Guid.NewGuid().ToString();
                                rowLog["LogTime"] = DateTime.Now;
                                rowLog["UserId"] = SysHelper.LoginUserID;
                                rowLog["LogContent"] = "导入新增";
                                rowLog["BeforeContent"] = "";
                                rowLog["AfterContent"] = "编码:" + att.ItemCode + "评分细则（具体细则：" + att.Content  + ")";
                                logTable.Rows.Add(rowLog);
                            }
                        }
                        if (sqlStr.Length > 0)
                            _db.Session.CreateSQLQuery(sqlStr.ToString()).ExecuteUpdate();
                        using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
                        {
                            if (conn.State != ConnectionState.Open)
                                conn.Open();
                            using (SqlBulkCopy sqlCopy = new SqlBulkCopy(conn))
                            {
                                sqlCopy.DestinationTableName = "SS_ScoreStandard";
                                foreach (DataColumn col in newTable.Columns)
                                {
                                    sqlCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                                }
                                sqlCopy.WriteToServer(newTable);
                                sqlCopy.Close();
                            }
                            using (SqlBulkCopy sqlCopylog = new SqlBulkCopy(conn))
                            {
                                sqlCopylog.DestinationTableName = "SS_ScoreStandardLog";
                                foreach (DataColumn col in logTable.Columns)
                                {
                                    sqlCopylog.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                                }
                                sqlCopylog.WriteToServer(logTable);
                                sqlCopylog.Close();
                            }
                            if (conn.State != ConnectionState.Closed)
                                conn.Close();
                        }
                        ViewBag.OK = "1";
                    }
                }
                else
                {
                    msg = "未选择上传文件！";
                }
                ViewBag.Msg = msg;          
            }
            catch (Exception ex)
            {
                ViewBag.Msg = ex.Message;
            }
      
            return View();
        }

 public static DataSet RenderDataTableFromExcel(Stream ExcelFileStream, int HeaderRowIndex)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            DataSet ds = new DataSet();
            for (int x = 0; x < workbook.NumberOfSheets; x++)
            {
                HSSFSheet sheet = workbook.GetSheetAt(x) as HSSFSheet;
                DataTable table = RenderDataTableFromExcelSheet(sheet, HeaderRowIndex);
                if (table != null)
                    ds.Tables.Add(table);
                sheet = null;
            }
            ExcelFileStream.Close();
            workbook = null;
            return ds;
        }