Asp.net页面用file控件导入excel 到sqlserver 数据库

做web开发的都知道，经常要用到excel 导入，网上有很多例子，但是都不太完整，正好前几天用到，然后就整理出一个相对完整的例子，导入前的数据验证+导入

需求是选择excel 之后自动导入，不需要再单击一个按钮，因此页面上需要两个控件，file 和 button  选择文件之后触发按钮然后提交请求：

页面代码：

<!--页面代码片段-->
<div class="li-cont">
                        <p>
                            <span>第三步：确认填写无误后，请重新上传模板，系统会自动计算帐号开通率。</span></p>
                        <div class="file-box">
                            <a href="javascript:;" class="up-btn">请点击上传表格</a>
                            <input type="file" name="fileField" runat="server" onchange="javascript:__doPostBack('lbUploadPhoto','')"
                                class="file-input" id="fileField" size="18" />
                            <asp:LinkButton ID="lbUploadPhoto" runat="server" OnClick="lbUploadPhoto_Click"></asp:LinkButton>
                        </div>
 </div>

　　后台代码:

//自动上传事件
        protected void lbUploadPhoto_Click(object sender, EventArgs e)
        {
            fileUpload();
        }

        /// <summary>
        /// 数据导入
        /// </summary>
        public void fileUpload()
        {
            bool Result_Import = false;
            bool Result = false;
            Guid RanageID = Guid.NewGuid();
            string IsXls = System.IO.Path.GetExtension(fileField.PostedFile.FileName).ToString().ToLower();
            if (fileField.PostedFile.FileName == "" || fileField.PostedFile.ContentLength == 0)
            {
                Response.Write("<script lanage=javascript>alert('请选择文件');</script>");
            }
            else if (IsXls != ".xls" && IsXls != ".xlsx")
            {
                Response.Write("<script lanage=javascript>alert('文件类型不合法');</script>");
            }
            else
            {
                string NewFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + new Random().Next(99, 9999) + IsXls;//新文件名
                string UploadPath = Server.MapPath("/DownExcel/Excel/");
                if (!Directory.Exists(UploadPath))
                {
                    Directory.CreateDirectory(UploadPath);
                }
                string NewPath = UploadPath + NewFileName;//服务器保存路径
                fileField.PostedFile.SaveAs(NewPath);
                DataTable dt = ExcelSqlConnection(NewPath, NewFileName);
                DataRow[] Dr = dt.Select();
                int RowsNum = dt.Rows.Count;
                if (RowsNum.Equals(0))
                {
                    Response.Write("<script lanage=javascript>alert('该考核表为空表，请重新上传');</script>");
                }
                else
                {
                    var checkDataRow = true;
                    var countryCode = UInfos.CountryCode;
                    dt.Columns.Add("状态");
                    foreach (DataRow dr in dt.Rows)
                    {
                        var SchoolName = dr.GetString("学校名称", "").Trim();//获取学校名称
                        var StudentNum = dr.GetString("学生数量", "0").Trim();//学生数量
                        var SchoolID = 0;//学校数量

                        if (SchoolName == "")
                        {
                            dr["状态"] = "<font style=\"color:red;\">学校名称不能为空</font>";
                            checkDataRow = false;
                            continue;
                        }
                        else if (StudentNum == "0")
                        {
                            dr["状态"] = "<font style=\"color:red;\">学生数量必须大于0</font>";
                            checkDataRow = false;
                            continue;
                        }
                        else //学校名称和学生填报数量都合法的情况看，检测学校是否存在、数量是否大于等于学校学生数量
                        {
                            if (DALAreaInfo.GetSchoolIdBySchoolName(SchoolName, countryCode) == 0)
                            {
                                dr["状态"] = "<font style=\"color:red;\">学校不存在</font>";
                                checkDataRow = false;
                                continue;
                            }
                            else
                            {
                                SchoolID = DALAreaInfo.GetSchoolIdBySchoolName(SchoolName, countryCode);//学校id
                                int StuNum = DALAreaInfo.GetSchoolStudentNum(SchoolID, SchoolYear.CurrentSchoolYear.Year, SchoolYear.CurrentSchoolYear.Semester);//当前学校学生人数
                                if (Convert.ToInt32(StudentNum) < StuNum)
                                {
                                    dr["状态"] = "<font style=\"color:red;\">填报人数必须大于或等于当前学校人数</font>";
                                    checkDataRow = false;
                                    continue;
                                }
                            }

                        }

                    }
                    if (!checkDataRow)//数据不合法，返回页面提示
                    {
                        ImportResult
                    }
                    else //开启导入模式
                    {
                        ImportStudentNum(dt);
                    }
                }
            }

        }

        /// <summary>
        /// 返回数据监测结果
        /// 导入之前检查数据是否合法，如果有不合法的，整体返回
        /// </summary>
        /// <param name="dt"></param>
        public void ImportResult(DataTable dt)
        {
            HttpResponse response = Page.Response;
            StringBuilder sb = new StringBuilder();
            dt.Columns.Remove("学校id");
            dt.Columns.Remove("区县id");
            sb.Append("<table border='1' style=' width:90%'>");
            int colCount = dt.Columns.Count;
            sb.Append("<tr><td style='text-align:center'>学校名称</td><td style='text-align:center'>填报学生数量</td><td style='text-align:center'>反馈信息</td></tr>");
            foreach (DataRow row in dt.Rows)
            {
                sb.Append("<tr>");
                for (int i = 0; i < colCount; i++)
                {
                    sb.Append("<td style='text-align:center'>");
                    if (i == colCount - 1)
                    {
                        sb.Append(row[i].ToString() + "</td></tr>");
                        //excelContent += row[i].ToString() + "\n";
                    }
                    else
                    {
                        sb.Append(row[i].ToString() + "</td>");
                        //excelContent += row[i].ToString() + "\t";
                    }
                }
            }
            //octet-stream html流，vnd.ms-excel-->Excel格式
            response.Write(@"<html><head></head>");
            response.Write("<body>");
            response.Write("<div style=' margin-left:10%'><strong>区县填报学生数量-结果反馈</strong><a target='_blank' href='#'><strong> 返回重新导入</strong></a><br /><div>");
            response.Write(sb.ToString());
            response.Write("</body>");
            response.Write(@"</html>");
            response.Flush();
            response.End();
        }

        /// <summary>
        /// 开始执行导入
        /// </summary>
        public void ImportStudentNum(DataTable dt)
        {
            dt.Columns.Remove("状态");
            dt.Columns.Add("SchoolID");
            dt.Columns["学校名称"].ColumnName = "SchoolName";
            dt.Columns["学生数量"].ColumnName = "StudentNum";
            List<CSEESchool> list = BLLAreaInfo.SchoolListGet(UInfos.CountryCode);//获取学校列表
            var query = (from snew in dt.AsEnumerable()
                         join newone in list on Convert.ToString(snew["SchoolName"]) equals newone.SchoolName
                         into special_Data
                         from special in special_Data.DefaultIfEmpty()
                         select new
                         {
                             SchoolName = Convert.ToString(snew["SchoolName"]),
                             StudentNum = Convert.ToInt32(snew["StudentNum"]),
                             SchoolID = special != null ? special.ID : -1
                         }).ToList();
            DataTable result = new DataTable();
            if (query.Count > 0)
            {
                PropertyInfo[] propertys = query[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    result.Columns.Add(pi.Name, pi.PropertyType);
                }
                for (int i = 0; i < query.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(query[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            try
            {
               //制定与方法，将数据导入到数据，这里我是讲数据集转为xml 批量导入数据库，需要的可以联系我
                string res = DALMySituation.CountryFillStudentNum(result, UInfos.CountryCode);
                if (res == "1")
                {
                    Response.Write("<script lanage=javascript>alert('学生人数填报成功');</script>");
                }
                else
                {
                    Response.Write("<script lanage=javascript>alert('学生人数填报失败');</script>");
                }
            }
            catch (Exception ex)
            {
                Response.Write("<script lanage=javascript>alert('" + ex.ToString() + "');</script>");
            }
        }

        #region 连接Excel  读取Excel数据   并返回DataSet数据集合
        /// <summary>
        /// 连接Excel  读取Excel数据   并返回DataSet数据集合
        /// </summary>
        /// <param name="filepath">Excel服务器路径</param>
        /// <param name="tableName">Excel表名称</param>
        /// <returns></returns>
        public static System.Data.DataTable ExcelSqlConnection(string filepath, string tableName)
        {
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            OleDbConnection ExcelConn = new OleDbConnection(strCon);
            try
            {
                string strCom = string.Format("SELECT * FROM [Sheet0$]");
                ExcelConn.Open();
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
                DataSet ds = new DataSet();
                myCommand.Fill(ds, "[" + tableName + "$]");
                ExcelConn.Close();
                return ds.Tables[0];
            }
            catch
            {
                ExcelConn.Close();
                return null;
            }
        }
        #endregion

　　项目中的源码，已经证实可用，如果有不同看法，欢迎指正！