excel 数据导入程序(C#)

        /// <summary>
        /// 获取 Excel 文件中指定索引的工作表名称
        /// </summary>
        /// <param name="fileName">Excel 的文件名</param>
        /// <param name="sheetIndex">要获取的索引</param>
        /// <returns></returns>
        private String GetExcelSheetNameByIndex(String fileName, int sheetIndex)
        {
            string connectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", fileName);
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                return schemaTable.Rows.Count > sheetIndex
                    ? schemaTable.Rows[sheetIndex]["TABLE_NAME"].ToString().Trim()
                    : String.Empty;
            }
        }

        /// <summary>
        /// 获取导入的Excel内容，放在DataSet中
        /// </summary>
        public DataSet GetExcelContent(string filepath)
        {
            string sheetName = this.GetExcelSheetNameByIndex(filepath, 0);
            string strCon = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + filepath + "; Extended Properties=\"Excel 8.0; HDR=No; IMEX=1;\"";
            System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
            string strCom = "SELECT * FROM " + "[" + sheetName + "]";
            myConn.Open();
            System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
            DataSet myDataSet = new DataSet();
            myCommand.Fill(myDataSet, "[" + sheetName + "]");
            myConn.Close();
            myConn.Dispose();
            return myDataSet;
        } 

 

        /// <summary>
        /// 导入事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSave_Click(object sender, EventArgs e)
        {
            if (DropDownType.SelectedValue == "-1")
            {
                Response.Write("<script language='javascript'>alert('防汛责任人类型必须要选择.');</script>");
                return;
            }
            if (FileUpload_Excel.FileName.ToString().Trim() == "")
            {
                Response.Write("<script language='javascript'>alert('请选择导入的Excel文件.');</script>");
                return;
            }
            else
            {
                string FileType = "[[[[.xls[[[.xlsx[[[[";
                if (!FileType.Contains((FileUpload_Excel.FileName.ToString().Trim()).Substring((FileUpload_Excel.FileName).Length - 3, 3).ToLower()))
                {
                    Response.Write("<script language='javascript'>alert('请选择正确的Excel文件.');</script>");
                    return;
                }
            }
            DataSet ds = new DataSet();
            //string strMd5 = "";
            //string pathcout = "";
            try
            {
               
                //判断是否能获取到上传Excel文件，在IE7,8或者一些浏览器上要设置一个选项才能正常的获取上传文件的绝对路径
                //if (FileUpload_Excel.PostedFile.FileName.ToString().Trim() == FileUpload_Excel.FileName)
                //{
                //    Response.Write("<script language='javascript'>alert('获取文件路径失败，请设置浏览器：工具-Internet选项-安全-自定义选项-其他选项-将文件上传到服务器时包含本地目录路径，设置成启用状态！');</script>");
                //    return;
                //}
                //获取文件名称
                //hlj 修改处
               // string time = DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss_");
               // string tmpPath = FileUpload_Excel.FileName.ToString().Trim().Split('.')[0];
                //用MD5的方式加密处理文件名称
                //strMd5 = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(tmpPath, "MD5");
                //pathcout = time + tmpPath;//把字符串组合
                string savePath = Server.MapPath("防汛责任人Temp.xls");//获取保存到服务器上的临时excel文件的绝对路径
                FileUpload_Excel.SaveAs(savePath);//SaveAs 将上传的文件内容保存在服务器上
                ds = GetExcelContent(savePath);
               
                //获取FileUpload_Excel.PostedFile.FileName.ToString().Trim()只有文件名的话把工具～Internet选项～安全～Internet～自定义级别～其它～将文件上载到服务器时包含本地目录路径：设为启用
            }
            catch (Exception ex)
            {
                string javascript = "<script language='javascript'>alert('" + ex.ToString() + "') </script>";
                this.ClientScript.RegisterStartupScript(this.GetType(), "upYAFile", javascript);
                return;
            }
            string code = DropDownType.SelectedValue;

            //获取责任人数据存放的表名
            string _TableName = Strongsoft_TZFXWEB.Manage.XTManager.FXZRR_NEW.FXZRR_BLL.GetTableNameByZRRCode(code);

            if (_TableName != "")
            {
                //Inport(_TableName, ds, pathcout);
                Inport(_TableName, ds, FileUpload_Excel.FileName.ToString().Trim().Split('.')[0]);
            }
        }

 

        /// <summary>
        /// 导入数据前进行数据预处理
        /// </summary>
        /// <param name="_TableName">根据责任人类型传过来的对应表名</param>
        /// <param name="ds">用户所上传的Excel表格导入到dataset</param>
        /// <param name="fileName">导入数据的文件，作为未能导入的数据返回文件的名称头_未导入数据</param>
        private void Inport(string _TableName, DataSet ds, string fileName)
        {
            #region  数据
            DataTable InvalidDataTable = new DataTable();
            Model.InportExcelData InportData = new Strongsoft_TZFXWEB.Manage.InportToFXZRR.Model.InportExcelData();
            DataTable dt = ds.Tables[0];

            #region ---------------列对应---------------------
            //初始化默认的列对应
            Model_FXZRR_CN Model_CN = new Model_FXZRR_CN();//初始化模型内列号都为0
            int DWCount = 0;//用于统计单位名称对应列是否有重复
            int IsHasTel = 0;//标示是否含有手机列
            //根据Excel数据修正列对应
            for (int c = 0; c < dt.Columns.Count; c++)
            {
                //单位名包括的名字
                string DpNameStr = "单位,企事业名称,提防名称,,堤防名称,水库名称,小流域名称,海塘名称,地质灾害隐患点名称,学校名称,水闸名称,船号,避灾点名称,公路危险区名称,居,自然村,转移对象,水电站名称";
                if (DpNameStr.Contains(dt.Rows[0][c].ToString().Trim()) && dt.Rows[0][c].ToString().Trim() != "村" && dt.Rows[0][c].ToString().Trim() != "转移对象简况" && dt.Rows[0][c].ToString().Trim() != "")//自然村作为单位和【村】字段有冲突
                {
                    Model_CN.DpNameCN = c;

                    DWCount++;
                }
                else if (dt.Rows[0][c].ToString().Trim() == "学校类别")
                {
                    Model_CN.SchoolTypeCN = c;
                }
                else if (dt.Rows[0][c].ToString().Trim() == "是否配STK卡")
                {
                    Model_CN.IsSTKCN = c;
                }
                else if (dt.Rows[0][c].ToString().Trim() == "职工人数")
                {
                    Model_CN.EmployeesCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "县市区" || dt.Rows[0][c].ToString().Trim() == "所属县")
                {
                    Model_CN.CountyCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim().Contains("街道") || dt.Rows[0][c].ToString().Trim().Contains("乡镇"))
                {
                    Model_CN.TownCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "责任人类别" || dt.Rows[0][c].ToString().Trim() == "防汛责任人类别" || dt.Rows[0][c].ToString().Trim() == "防汛职务岗位" || dt.Rows[0][c].ToString().Trim() == "防汛岗位职务" || dt.Rows[0][c].ToString().Trim() == "防汛岗位")
                {
                    Model_CN.FxzwCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "村" || dt.Rows[0][c].ToString().Trim() == "自然村")
                {
                    Model_CN.VillageCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "威胁户数")
                {
                    Model_CN.ThrDoorCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "威胁人数")
                {
                    Model_CN.ThrPeCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "姓名" || dt.Rows[0][c].ToString().Trim() == "管理单位责任人")
                {
                    Model_CN.NameCN = c;
                }
                else if (dt.Rows[0][c].ToString().Trim() == "职务")
                {
                    Model_CN.PositionCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "电话" || dt.Rows[0][c].ToString().Trim() == "固定电话")
                {
                    Model_CN.HomeTelCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "手机")
                {
                    Model_CN.TelCN = c;
                    IsHasTel = 1;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "备注")
                {
                    Model_CN.RemarkCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "GPS定位系统情况")
                {
                    Model_CN.GpsCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "船只型号规模")
                {
                    Model_CN.ShipTypeCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "船号")
                {
                    Model_CN.ShipidCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "所属公司" || dt.Rows[0][c].ToString().Trim() == "工作单位")
                {
                    Model_CN.CompanyCN = c;
                }
                else if (dt.Rows[0][c].ToString().Trim() == "转移对象简况")
                {
                    Model_CN.MoveCommentCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "库型")
                {
                    Model_CN.ReservoirTypeCN = c;

                }
                else if (dt.Rows[0][c].ToString().Trim() == "是否保密")
                {
                    Model_CN.IsPrivacyCN = c;
                }
                else if (dt.Rows[0][c].ToString().Trim() == "年龄")
                {
                    Model_CN.IsAgeCN = c;
                }
            }
            if (DWCount < 1)
            {
                this.ClientScript.RegisterStartupScript(this.GetType(), "msg", "<script language='javascript'>alert('未找到单位列，无法导入！') </script>");
                return;
            }
            else if (DWCount > 1)
            {
                this.ClientScript.RegisterStartupScript(this.GetType(), "msg", "<script language='javascript'>alert('表中含有多个单位列，无法导入！') </script>");
                return;
            }
            if (IsHasTel != 1)
            {
                this.ClientScript.RegisterStartupScript(this.GetType(), "msg", "<script language='javascript'>alert('未找到责任人手机列，无法导入！') </script>");
                return;
            }
            //验证是否找到县市区列，若没有无法进行编号导入。不允许导入。找到后不进行验证，县市区名无法识别的直接导为台州市一级
            if (Model_CN.CountyCN == -1)
            {
                this.ClientScript.RegisterStartupScript(this.GetType(), "msg", "<script language='javascript'>alert('未找到责任人所属县市区列，无法导入！') </script>");
                return;
            }
            #endregion
            for (int c = 0; c < dt.Columns.Count; c++)//遍历导入表的表头，赋值给无效数据表。
            {
                InvalidDataTable.Columns.Add(dt.Columns[c].ToString());

            }
            DataRow Headdr = InvalidDataTable.NewRow();
            try
            {
                Headdr.ItemArray = dt.Rows[0].ItemArray;
            }
            catch (Exception)
            {
                this.ClientScript.RegisterStartupScript(this.GetType(), "msg", "<script language='javascript'>alert('表中含有无法识别的列，无法导入！') </script>");
                return;
            }
            Headdr = dt.Rows[0];
            InvalidDataTable.ImportRow(Headdr);//添加表头行
            dt.Rows.Remove(dt.Rows[0]);//完成列对应后移除表头行
            dt.DefaultView.Sort = "F" + (Model_CN.DpNameCN + 1) + " ASC";//进行按单位排序，方便以后导入数据提高效率
            dt = dt.DefaultView.ToTable();
            string CheckCountyStr = ",椒江区,黄岩区,路桥区,玉环县,三门县,天台县,仙居县,温岭市,临海市,台州市,椒江,黄岩,路桥,玉环,三门,天台,仙居,温岭,临海,台州,";
            List<Model_FXZRR> List_Data = new List<Model_FXZRR>();//存放数据
            string CityName = GlobalPars.GetCountyNameByUnitID().Trim();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                int IsReturn = 0;
                //单位必须的不能不存在这列也不能为空
                if (string.IsNullOrEmpty(dt.Rows[i][Model_CN.DpNameCN].ToString().Trim()))
                {
                    IsReturn = 1;
                    dt.Rows[i][Model_CN.DpNameCN] += "(*)";
                }
                if (dt.Rows[i][Model_CN.TelCN].ToString().Trim().ToLower().Contains("e"))
                {
                    this.ClientScript.RegisterStartupScript(this.GetType(), "msg", "<script language='javascript'>alert('手机列不全都是文本列，无法识别！') </script>");
                    return;
                }
                Model_FXZRR ModelFXZRR = new Model_FXZRR();
                if (dt.Rows[i][Model_CN.CountyCN].ToString().Trim() != "")//县市区有填写就验证县市区是否合法
                {
                    if (!CheckCountyStr.Contains("," + dt.Rows[i][Model_CN.CountyCN].ToString().Trim() + ","))//验证县市区的合法性
                    {
                        dt.Rows[i][Model_CN.CountyCN] = dt.Rows[i][Model_CN.CountyCN] + "(*)";
                        IsReturn = 1;
                    }
                    else//验证县市区合法后
                    {
                        if (dt.Rows[i][Model_CN.CountyCN].ToString().Trim().Length == 2)//如果是县市区简写（2个字）补充成全称（3个字）
                        {
                            if (dt.Rows[i][Model_CN.CountyCN].ToString().Trim() == "椒江") dt.Rows[i][Model_CN.CountyCN] = "椒江区";
                            else if (dt.Rows[i][Model_CN.CountyCN].ToString().Trim() == "黄岩") dt.Rows[i][Model_CN.CountyCN] = "黄岩区";
                            else if (dt.Rows[i][Model_CN.CountyCN].ToString().Trim() == "路桥") dt.Rows[i][Model_CN.CountyCN] = "路桥区";
                            else if (dt.Rows[i][Model_CN.CountyCN].ToString().Trim() == "玉环") dt.Rows[i][Model_CN.CountyCN] = "玉环县";
                            else if (dt.Rows[i][Model_CN.CountyCN].ToString().Trim() == "三门") dt.Rows[i][Model_CN.CountyCN] = "三门县";
                            else if (dt.Rows[i][Model_CN.CountyCN].ToString().Trim() == "天台") dt.Rows[i][Model_CN.CountyCN] = "天台县";
                            else if (dt.Rows[i][Model_CN.CountyCN].ToString().Trim() == "仙居") dt.Rows[i][Model_CN.CountyCN] = "仙居县";
                            else if (dt.Rows[i][Model_CN.CountyCN].ToString().Trim() == "温岭") dt.Rows[i][Model_CN.CountyCN] = "温岭市";
                            else if (dt.Rows[i][Model_CN.CountyCN].ToString().Trim() == "临海") dt.Rows[i][Model_CN.CountyCN] = "临海市";
                            else if (dt.Rows[i][Model_CN.CountyCN].ToString().Trim() == "台州") dt.Rows[i][Model_CN.CountyCN] = "台州市";
                        }
                    }
                }
                else//如果为空默认为台州市一级
                {
                    dt.Rows[i][Model_CN.CountyCN] = "台州市";
                }

                if (CityName != "台州市" && dt.Rows[i][Model_CN.CountyCN].ToString().Trim() != CityName)//判断如果是县市区用户，导入的数据不是本县市的就不让导入
                {
                    dt.Rows[i][Model_CN.CountyCN] = dt.Rows[i][Model_CN.CountyCN] + "(*)";
                    IsReturn = 1;
                }
                if (Model_CN.TownCN != -1)
                    ModelFXZRR.Town = dt.Rows[i][Model_CN.TownCN].ToString().Trim();
                if (ModelFXZRR.Town.Length > 10)
                {
                    dt.Rows[i][Model_CN.TownCN] = dt.Rows[i][Model_CN.TownCN] + "(*)";
                    IsReturn = 1;
                }

                if (Model_CN.CountyCN != -1)
                    ModelFXZRR.County = dt.Rows[i][Model_CN.CountyCN].ToString().Trim();
                if (ModelFXZRR.County.Length > 5)
                {
                    dt.Rows[i][Model_CN.CountyCN] = dt.Rows[i][Model_CN.CountyCN] + "(*)";
                    IsReturn = 1;
                }

                if (Model_CN.DpNameCN != -1)
                    ModelFXZRR.DpName = dt.Rows[i][Model_CN.DpNameCN].ToString().Trim();
                if (Model_CN.DpNameCN != -1 && ModelFXZRR.DpName.Length > 20)
                {
                    dt.Rows[i][Model_CN.DpNameCN] = dt.Rows[i][Model_CN.DpNameCN] + "(*)";
                    IsReturn = 1;
                }

                if (Model_CN.FxzwCN != -1)
                {
                    ModelFXZRR.Fxzw = dt.Rows[i][Model_CN.FxzwCN].ToString().Trim();
                    if ((Model_CN.FxzwCN != -1 && ModelFXZRR.Fxzw.Length > 20) || ModelFXZRR.Fxzw.Trim() == "")
                    {
                        dt.Rows[i][Model_CN.FxzwCN] = dt.Rows[i][Model_CN.FxzwCN] + "(*)";
                        IsReturn = 1;
                    }
                }

                ModelFXZRR.Tel = dt.Rows[i][Model_CN.TelCN].ToString().Trim();
                ModelFXZRR.HomeTel = dt.Rows[i][Model_CN.HomeTelCN].ToString().Trim();

                if (Model_CN.NameCN != -1)
                    ModelFXZRR.Name = dt.Rows[i][Model_CN.NameCN].ToString().Trim();
                if (Model_CN.NameCN != -1 && ModelFXZRR.Name.Length > 5)
                {
                    dt.Rows[i][Model_CN.NameCN] = dt.Rows[i][Model_CN.NameCN] + "(*)";
                    IsReturn = 1;
                }

                if (Model_CN.PositionCN != -1)
                    ModelFXZRR.Position = dt.Rows[i][Model_CN.PositionCN].ToString().Trim();
                if (Model_CN.PositionCN != -1 && ModelFXZRR.Position.Length > 20)
                {
                    dt.Rows[i][Model_CN.PositionCN] = dt.Rows[i][Model_CN.PositionCN] + "(*)";
                    IsReturn = 1;
                }

                if (Model_CN.RemarkCN != -1)
                    ModelFXZRR.Remark = dt.Rows[i][Model_CN.RemarkCN].ToString().Trim();
                if (Model_CN.RemarkCN != -1 && ModelFXZRR.Remark.Length > 150)
                {
                    dt.Rows[i][Model_CN.RemarkCN] = dt.Rows[i][Model_CN.RemarkCN] + "(*)";
                    IsReturn = 1;
                }

                if (Model_CN.ReservoirTypeCN != -1)
                    ModelFXZRR.ReservoirType = dt.Rows[i][Model_CN.ReservoirTypeCN].ToString().Trim();
                if (Model_CN.ReservoirTypeCN != -1 && ModelFXZRR.ReservoirType.Length > 5)
                {
                    dt.Rows[i][Model_CN.ReservoirTypeCN] = dt.Rows[i][Model_CN.ReservoirTypeCN] + "(*)";
                    IsReturn = 1;
                }

                if (Model_CN.ShipidCN != -1)
                    ModelFXZRR.Shipid = dt.Rows[i][Model_CN.ShipidCN].ToString().Trim();
                if (Model_CN.ShipidCN != -1 && ModelFXZRR.Shipid.Length > 25)
                {
                    dt.Rows[i][Model_CN.ShipidCN] = dt.Rows[i][Model_CN.ShipidCN] + "(*)";
                    IsReturn = 1;
                }

                if (Model_CN.EmployeesCN != -1)
                    ModelFXZRR.Employees = dt.Rows[i][Model_CN.EmployeesCN].ToString().Trim();
                try
                {
                    if (Model_CN.EmployeesCN != -1)
                    {
                        //tyh20110818 修改如果没有填数据，默认为0
                        string num = ModelFXZRR.Employees;
                        if (num.Trim() == "")
                        {
                            ModelFXZRR.Employees = "0";
                        }
                        int.Parse(ModelFXZRR.Employees.Trim());
                    }
                }
                catch
                {
                    dt.Rows[i][Model_CN.EmployeesCN] = dt.Rows[i][Model_CN.EmployeesCN] + "(*)";
                    IsReturn = 1;
                }

                if (Model_CN.GpsCN != -1)
                    ModelFXZRR.Gps = dt.Rows[i][Model_CN.GpsCN].ToString().Trim();
                if (Model_CN.GpsCN != -1 && ModelFXZRR.Gps.Length > 25)
                {
                    dt.Rows[i][Model_CN.GpsCN] = dt.Rows[i][Model_CN.GpsCN] + "(*)";
                    IsReturn = 1;
                }

                if (Model_CN.MoveCommentCN != -1)
                    ModelFXZRR.MoveComment = dt.Rows[i][Model_CN.MoveCommentCN].ToString().Trim();
                if (Model_CN.MoveCommentCN != -1 && ModelFXZRR.MoveComment.Length > 150)
                {
                    dt.Rows[i][Model_CN.MoveCommentCN] = dt.Rows[i][Model_CN.MoveCommentCN] + "(*)";
                    IsReturn = 1;
                }
                //ModelFXZRR.RyId = dt.Rows[i][Model_CN.RyIdCN].ToString().Trim();

                if (Model_CN.ShipTypeCN != -1)
                    ModelFXZRR.ShipType = dt.Rows[i][Model_CN.ShipTypeCN].ToString().Trim();
                if (Model_CN.ShipTypeCN != -1 && ModelFXZRR.ShipType.Length > 25)
                {
                    dt.Rows[i][Model_CN.ShipTypeCN] = dt.Rows[i][Model_CN.ShipTypeCN] + "(*)";
                    IsReturn = 1;
                }

                if (Model_CN.CompanyCN != -1)
                    ModelFXZRR.Company = dt.Rows[i][Model_CN.CompanyCN].ToString().Trim();
                if (Model_CN.CompanyCN != -1 && ModelFXZRR.Company.Length > 10)
                {
                    dt.Rows[i][Model_CN.CompanyCN] = dt.Rows[i][Model_CN.CompanyCN] + "(*)";
                    IsReturn = 1;
                }

                if (Model_CN.SchoolTypeCN != -1)
                    ModelFXZRR.SchoolType = dt.Rows[i][Model_CN.SchoolTypeCN].ToString().Trim();
                if (Model_CN.SchoolTypeCN != -1 && ModelFXZRR.SchoolType.Length > 25)
                {
                    dt.Rows[i][Model_CN.SchoolTypeCN] = dt.Rows[i][Model_CN.SchoolTypeCN] + "(*)";
                    IsReturn = 1;
                }

                if (Model_CN.ThrDoorCN != -1)
                {
                    ModelFXZRR.ThrDoor = dt.Rows[i][Model_CN.ThrDoorCN].ToString().Trim();
                    try
                    {
                        if (Model_CN.ThrDoorCN != -1)
                        {
                            if (ModelFXZRR.ThrDoor.Trim() == "")
                            {
                                ModelFXZRR.ThrDoor = "0";
                            }
                        }
                        int.Parse(ModelFXZRR.ThrDoor.Trim());
                    }
                    catch
                    {
                        dt.Rows[i][Model_CN.ThrDoorCN] = dt.Rows[i][Model_CN.ThrDoorCN] + "(*)";
                        IsReturn = 1;
                    }
                }

                if (Model_CN.ThrPeCN != -1)
                {
                    ModelFXZRR.ThrPe = dt.Rows[i][Model_CN.ThrPeCN].ToString().Trim();
                    try
                    {
                        if (Model_CN.ThrPeCN != -1)
                        {
                            if (ModelFXZRR.ThrPe.Trim() == "")
                            {
                                ModelFXZRR.ThrPe = "0";
                            }
                        }
                        int.Parse(ModelFXZRR.ThrPe.Trim());
                    }
                    catch
                    {
                        dt.Rows[i][Model_CN.ThrPeCN] = dt.Rows[i][Model_CN.ThrPeCN] + "(*)";
                        IsReturn = 1;
                    }
                }
                if (Model_CN.VillageCN != -1)
                    ModelFXZRR.Village = dt.Rows[i][Model_CN.VillageCN].ToString().Trim();
                if (Model_CN.VillageCN != -1 && ModelFXZRR.Village.Length > 10)
                {
                    dt.Rows[i][Model_CN.VillageCN] = dt.Rows[i][Model_CN.VillageCN] + "(*)";
                    IsReturn = 1;
                }

                //验证是否进行手机电话的保密
                if (Model_CN.IsPrivacyCN != -1)
                    ModelFXZRR.IsPrivacy = dt.Rows[i][Model_CN.IsPrivacyCN].ToString().Trim() == "" ? "否" : dt.Rows[i][Model_CN.IsPrivacyCN].ToString().Trim();
                if (Model_CN.IsPrivacyCN != -1 && ModelFXZRR.IsPrivacy != "是" && ModelFXZRR.IsPrivacy != "否")//是否保密列，只有一个字（是或否），为空的时候也为否
                {
                    dt.Rows[i][Model_CN.IsPrivacyCN] = dt.Rows[i][Model_CN.IsPrivacyCN] + "(*)";
                    IsReturn = 1;
                }

                //验证是否配有STK卡
                if (Model_CN.IsSTKCN != -1)
                    ModelFXZRR.IsSTK = dt.Rows[i][Model_CN.IsSTKCN].ToString().Trim() == "" ? "否" : dt.Rows[i][Model_CN.IsSTKCN].ToString().Trim();
                if (Model_CN.IsSTKCN != -1 && ModelFXZRR.IsSTK != "是" && ModelFXZRR.IsSTK != "否")//是否保密列，只有一个字（是或否），为空的时候也为否
                {
                    dt.Rows[i][Model_CN.IsSTKCN] = dt.Rows[i][Model_CN.IsSTKCN] + "(*)";
                    IsReturn = 1;
                }

                //年龄
                if (Model_CN.IsAgeCN != -1)
                {
                    try
                    {
                        if (dt.Rows[i][Model_CN.IsAgeCN].ToString().Trim() == "")
                        {
                            ModelFXZRR.Age = 0;
                        }
                        ModelFXZRR.Age = Convert.ToInt16(dt.Rows[i][Model_CN.IsAgeCN].ToString().Trim());
                    }
                    catch
                    {
                        dt.Rows[i][Model_CN.IsAgeCN] = dt.Rows[i][Model_CN.IsAgeCN] + "(*)";
                        IsReturn = 1;
                    }
                }

                ModelFXZRR.Shipid = ModelFXZRR.DpName;//船只避风责任人的船号就是单位名
                //ModelFXZRR.ZwId = dt.Rows[i][Model_CN.ZwIdCN].ToString().Trim();
                ModelFXZRR.Type = GetType(_TableName);//根据原有数据库获取的（类型编号，好像没有用）

 

                if (dt.Rows[i][Model_CN.HomeTelCN].ToString().Trim().ToLower().Contains("e"))
                {
                    this.ClientScript.RegisterStartupScript(this.GetType(), "msg", "<script language='javascript'>alert('电话列不全都是文本列，无法识别！') </script>");
                    return;
                }
                Regex regex = new Regex("(18\\d{9}$)|(13\\d{9}$)|(15\\d{9}$)");
                //有可能固定电话代替手机
                string pattern = "(\\d{3}-\\d{8}|\\d{4}-\\d{7}|\\d{8}$)";
                Regex regex2 = new Regex(pattern);
                //判断手机号码为空的时候，用电话号码代替手机号码
                if (dt.Rows[i][Model_CN.TelCN].ToString().Trim() == "" && dt.Rows[i][Model_CN.HomeTelCN].ToString().Trim() != "" && regex2.IsMatch(ModelFXZRR.HomeTel))
                {
                    ModelFXZRR.Tel = ModelFXZRR.HomeTel;

                }
                //判断手机是否合法

                if (!(regex.IsMatch(ModelFXZRR.Tel) || regex2.IsMatch(ModelFXZRR.Tel)))
                {
                    dt.Rows[i][Model_CN.TelCN] = dt.Rows[i][Model_CN.TelCN] + "(*)";
                    IsReturn = 1;
                }

                if (!regex2.IsMatch(ModelFXZRR.HomeTel) && ModelFXZRR.HomeTel.Trim() != "")
                {
                    dt.Rows[i][Model_CN.HomeTelCN] = dt.Rows[i][Model_CN.HomeTelCN] + "(*)";
                    IsReturn = 1;
                }
                ModelFXZRR.ZrrId = ModelFXZRR.Tel;
                #region  if (IsReturn == 1)
                if (IsReturn == 1)
                {
                    bool IsEmpty = false;
                    //for (int cc = 0; cc < dt.Columns.Count; cc++)
                    //{
                    //    if (dt.Rows[i][cc].ToString().Trim() != "")
                    //    {
                    //        IsEmpty = false;
                    //        break;
                    //    }
                    //}
                    if (dt.Rows[i][Model_CN.NameCN].ToString().Replace('(', ' ').Replace('*', ' ').Replace(')', ' ').Trim() == "" && dt.Rows[i][Model_CN.TelCN].ToString().Trim().Replace('(', ' ').Replace('*', ' ').Replace(')', ' ').Trim() == "" && dt.Rows[i][Model_CN.HomeTelCN].ToString().Replace('(', ' ').Replace('*', ' ').Replace(')', ' ').Trim() == "")
                    {
                        IsEmpty = true;
                    }
                    if (!IsEmpty)//如果不是空行放入无效数据表中，以便做数据返回
                    {
                        DataRow dr = InvalidDataTable.NewRow();
                        try
                        {
                            dr.ItemArray = dt.Rows[i].ItemArray;
                        }
                        catch (Exception)
                        {
                            this.ClientScript.RegisterStartupScript(this.GetType(), "msg", "<script language='javascript'>alert('表中含有无法识别的列，无法导入！') </script>");
                            return;
                        }
                        dr = dt.Rows[i];
                        InvalidDataTable.ImportRow(dr);
                    }
                    dt.Rows.Remove(dt.Rows[i]);
                    i--;
                    continue;

                }
                #endregion
                else
                {
                    List_Data.Add(ModelFXZRR);
                }
            }
            //2011-04-26钟崇安添加导入责任人的英文编号，以便获取该责任人的类型用于写日志
            InportData.InsertData(List_Data, _TableName, DropDownType.SelectedValue);
            #endregion

            #region 2011-07-22 hlj修改
            //try
            //{
               // string newPath = HttpContext.Current.Server.MapPath("Excel/" + fileName + "_未导入数据.xls");
               // try
               // {
                    //System.IO.FileInfo file = new System.IO.FileInfo(newPath);
                   // if (!file.Exists)
                   // {
                        //将导入失败的数据重新导出都Excel中保存到服务器上
                       // ExcelHelper aa = new ExcelHelper(HttpContext.Current.Server.MapPath("Excel/" + fileName + "_未导入数据.xls"));
                       // DataTable[] idt = new DataTable[1];
                       // idt[0] = InvalidDataTable;
                        //if (idt[0].Rows.Count > 1)
                        //{
                        //    aa.DataTableToExcel(idt);
                        //}
                   // }
                   // else
                   // {
                        //DirectoryInfo parentDir = new DirectoryInfo(HttpContext.Current.Server.MapPath("Excel/"));
                        //System.IO.FileInfo[] childFiles = parentDir.GetFiles();
                        //foreach (FileInfo childFile in childFiles)
                        //{
                        //    string co = childFile.Name.ToString();
                        //}
                        //将导入失败的数据重新导出都Excel中保存到服务器上
                        //ExcelHelper bb = new ExcelHelper(HttpContext.Current.Server.MapPath("Excel/" + fileName + "_未导入数据.xls"));
                       // DataTable[] ddt = new DataTable[1];
                      //  ddt[0] = InvalidDataTable;
                      //  if (ddt[0].Rows.Count > 1)
                       // {
                       //     bb.DataTableToExcel(ddt);
                       // }
                  //  }
                //}
                //catch { }
            //}
            //catch { }
            #endregion

            try
            {
                //将导入失败的数据重新导出都Excel中保存到服务器上
                ExcelHelper aa = new ExcelHelper(HttpContext.Current.Server.MapPath("Excel/" + fileName + "_未导入数据.xls"));
                DataTable[] idt = new DataTable[1];
                idt[0] = InvalidDataTable;
                if (idt[0].Rows.Count > 1)
                {
                    aa.DataTableToExcel(idt);
                }
            }
            catch
            {

            }
            if (List_Data.Count == 0)
            {
                this.ClientScript.RegisterStartupScript(this.GetType(), "msg", "<script language='javascript'>alert('无法导入任何数据，请检查Excel表！') </script>");
                if (InvalidDataTable.Rows.Count > 1)
                {
                    HyperLink_Excel.NavigateUrl = "Excel/" + fileName + "_未导入数据.xls";
                    HyperLink_Excel.Visible = true;
                }
                else
                {
                    HyperLink_Excel.Visible = false;
                }
                return;
            }
            else if (List_Data.Count > 0 && InvalidDataTable.Rows.Count > 1)
            {
                this.ClientScript.RegisterStartupScript(this.GetType(), "msg", "<script language='javascript'>alert('导入成功，但是有部分数据未导入,可点击下载未导入的数据！') </script>");
                HyperLink_Excel.NavigateUrl = "Excel/" + fileName + "_未导入数据.xls";
                HyperLink_Excel.Visible = true;
            }
            else if (List_Data.Count > 0 && InvalidDataTable.Rows.Count == 1)//第一行是表头行
            {
                this.ClientScript.RegisterStartupScript(this.GetType(), "msg", "<script language='javascript'>alert('导入成功！！！') </script>");
                HyperLink_Excel.Visible = false;
            }
        }