protected void Page_Load(object sender, EventArgs e)
        {
            
        }

        protected void btn_import_Click(object sender, EventArgs e)
        {
            bool b = Upload(fuExcel);  // 上传excel文件 
            if (!b)
            {
                return;
            }
            string name = fuExcel.FileName;
            string filepath = Server.MapPath("~/upload/") + name;
            DataSet ds = ExcelDataSource(filepath, ExcelSheetName(filepath)[0].ToString());
            //导入的EXCEL表的列名和GV中绑定列名不相同，不能正确绑定，需要处理才好
            DataTable modiTable = ds.Tables[0];
            //modiTable.Columns[0].ColumnName = "tid";
            modiTable.Columns[0].ColumnName = "pID";
            modiTable.Columns[1].ColumnName = "pName";
            modiTable.Columns[2].ColumnName = "ppwd";
            modiTable.Columns[3].ColumnName = "psex";
            modiTable.Columns[4].ColumnName = "pbirth";
            modiTable.Columns[5].ColumnName = "pwork";
            modiTable.Columns[6].ColumnName = "pdepartment";
            modiTable.Columns[7].ColumnName = "pworktime";
            modiTable.Columns[8].ColumnName = "pblood";
            modiTable.Columns[9].ColumnName = "phealth";
            modiTable.Columns[10].ColumnName = "pmarriage";
            modiTable.Columns[11].ColumnName = "premark";

            GridView1.DataSource = modiTable;
            GridView1.DataBind();

        }

        //上传文件方法  
        private bool Upload(FileUpload myFileUpload)
        {
            bool flag = false;
            //是否允许上载  
            bool fileAllow = false;
            //设定允许上载的扩展文件名类型  
            string[] allowExtensions = { ".xls" };

            //取得网站根目录路径  
            string path = HttpContext.Current.Request.MapPath("~/upload/");
            if (myFileUpload.HasFile)
            {
                string fileExtension = System.IO.Path.GetExtension(myFileUpload.FileName).ToLower();
                for (int i = 0; i < allowExtensions.Length; i++)
                {
                    if (fileExtension == allowExtensions[i])
                    {
                        fileAllow = true;
                    }
                }

                if (fileAllow)
                {
                    try
                    {
                        //存储文件到文件夹 
                        myFileUpload.SaveAs(path + myFileUpload.FileName);
                        //lblMes.Text = "文件导入成功";
                        flag = true;
                    }
                    catch (Exception ex)
                    {
                       // lblMes.Text += ex.Message;
                        flag = false;
                    }
                }
                else
                {
                   // lblMes.Text = "不允许上载：" + myFileUpload.PostedFile.FileName + "，只能上传xls的文件，请检查！";
                    flag = false;
                }
            }
            else
            {
                //lblMes.Text = "请选择要导入的excel文件!";
                flag = false;
            }
            return flag;
        }

        //该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径， sheetname为excel文件中的表名
        public DataSet ExcelDataSource(string filepath, string sheetname)
           {
               string strConn;
               strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
               OleDbConnection conn = new OleDbConnection(strConn);
               OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
               DataSet ds = new DataSet();
               oada.Fill(ds);
               conn.Close();
               return ds;
           }

           //获得Excel中的所有sheetname。
        public ArrayList ExcelSheetName(string filepath)
           {
               ArrayList al = new ArrayList();
               string strConn;
               strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
               OleDbConnection conn = new OleDbConnection(strConn);
               conn.Open();
               DataTable sheetNames = conn.GetOleDbSchemaTable
               (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
               conn.Close();
               foreach (DataRow dr in sheetNames.Rows)
               {
                   al.Add(dr[2]);
               }
               return al;
           }

        protected void btn_uploadDB_Click(object sender, EventArgs e)
        {
            //提交到数据库tab_acc中去
            SqlConnection con = BaseDate.returnSqlCon();
            SqlTransaction trans = null;
            SqlCommand cmd = new SqlCommand();
            if (GridView1.Rows.Count <= 0)
                return;
            try
            {
                con.Open();
                trans = con.BeginTransaction();
                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    int gpID = int.Parse(((Label)GridView1.Rows[i].FindControl("Label1")).Text.ToString().Trim());
                    string gpName = GridView1.Rows[i].Cells[1].Text.ToString().Trim();
                    String gpwd =((Label)GridView1.Rows[i].FindControl("Label2")).Text.ToString().Trim();
                    string gpsex = GridView1.Rows[i].Cells[3].Text.ToString().Trim();
                    DateTime gpbirth = DateTime.Parse(GridView1.Rows[i].Cells[4].Text.ToString().Trim());
                    String gpwork = GridView1.Rows[i].Cells[5].Text.ToString().Trim();
                    String gpdepartment = GridView1.Rows[i].Cells[6].Text.ToString().Trim();
                    DateTime gpworktime = DateTime.Parse(GridView1.Rows[i].Cells[7].Text.ToString().Trim());
                    String gpblood = GridView1.Rows[i].Cells[8].Text.ToString().Trim();
                    String gphealth = GridView1.Rows[i].Cells[9].Text.ToString().Trim();
                    String gpmarriage = GridView1.Rows[i].Cells[10].Text.ToString().Trim();
                    String gpremark = GridView1.Rows[i].Cells[11].Text.ToString().Trim();
                   
                    Employee emp = new Employee();
                    emp.AccId = BaseDate.getMaxId("tab_acc", "acc_id");
                    emp.AccName = gpName;
                    emp.AccLoginName = gpName;
                    emp.AccPassWord = gpwd;
                    emp.AccSex = gpsex;
                    emp.Birthday = gpbirth;
                    emp.Work = gpwork;
                    department dep = new department();
                    dep.DepartmentName = gpdepartment;
                    if ("开发".Equals(gpdepartment)) {
                        dep.DepartmentId = "1";
                    }
                    else if ("市场".Equals(gpdepartment))
                    {
                        dep.DepartmentId = "2";
                    }
                    else if ("运维".Equals(gpdepartment))
                    {
                        dep.DepartmentId = "3";
                    }
                    emp.Department = dep;
                    emp.EmpTime = gpworktime;
                    emp.Blood = gpblood;
                    emp.Marriage = gpmarriage;
                    emp.Remark = gpremark;
                    emp.Role = "0";
                    Record rec = new Record();
                    rec.RecordId = BaseDate.getMaxId("tab_healthyRecord", "rec_id");
                    rec.Result = gphealth;
                    rec.Emp = emp;
                    if (EmpAction.addOne(emp) > 0) {
                        HealthyRecordAction.addOne(rec);
                    }
                }
                trans.Commit();
                btn_uploadDB.Enabled = false;
                Response.Write("<script  language = 'javascript'> alert('恭喜您：数据提交成功!') </script>");
            }
            catch (Exception exc)
            {
                trans.Rollback();
                Response.Write("<script  language = 'javascript'> alert('对不起：数据提交出错，原因是：" + exc.Message + "!') </script>");
            }
            finally
            {
                con.Close();

            }
        }