导入EXCEL

    /// <summary>
    /// 导入
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnImport_Click(object sender, EventArgs e)
    {
        try
        {
            if (this.FileUpload1.HasFile)
            {
                string filePath = Server.MapPath("../upload");
                if (!Directory.Exists(filePath))
                {
                    Directory.CreateDirectory(filePath);
                }
                string fileName = filePath + "/" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + "_" + FileUpload1.FileName;

                string type = fileName.Substring(fileName.LastIndexOf(".") + 1);
                if (type.ToLower() != "xls" && type.ToLower() != "xlsx")
                {
                    base.ShowMessage("上传的文件格式不正确！");
                    return;
                }

                FileUpload1.SaveAs(fileName);

                // RekTec.Crm.Common.Logging log = new RekTec.Crm.Common.Logging();

                // log.LogUI += new RekTec.Crm.Common.Logging.LogUIEvent(_log);
                //// RekTec.EAP.Common.PersisitBroker pb = new RekTec.EAP.Common.PersisitBroker("ITCC");
                // importRedListCommand import = new importRedListCommand(null, log, true, "TB_Redlist");
                // import.Transform(fileName);
                DataSet ds = new DataSet();

               
                //filename = "E:\\ITCC\\itccSource.root\\itccSource\\ITCC.Web\\upload/新建 Microsoft Office Excel 工作表.xls";
                //string strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
                string strConn = "provider=Microsoft.ACE.OleDb.12.0; Data Source ='" + fileName + "';Extended Properties='Excel 12.0;HDR=yes;IMEX=1';";
                //OleDbConnection conn = new OleDbConnection(strConn);

                //conn.Open();

                //string tableName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim();

                //OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + tableName + "]", conn);

                //odda.Fill(ds, table);


                // DataTable dtExcel = new DataTable();
                
                //return dtExcel;
              
                try
                {
                    using (OleDbConnection conn = new OleDbConnection(strConn))
                    {
                        conn.Open();
                        // 获取Excel的第一个SheetName
                        string sSheetName = "";
                        DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                        if (dtSheet.Rows.Count > 0)
                            sSheetName = dtSheet.Rows[0]["Table_Name"].ToString();
                        else
                            return;
                        // 获取Excel数据
                        string sSql = string.Format("select * from [{0}]", sSheetName);
                        OleDbDataAdapter adapter = new OleDbDataAdapter(sSql, conn);
                        adapter.Fill(ds);
                        conn.Close();
                    }

                    
                }
                catch(Exception ex)
                {
                    Response.Write("<script>alert('导入失败，Excel工作表标签名错误，标签名必须是Sheet1，请查证后再导入!')</script>");
                    return;
                }

                //DataRow dr = ds.Tables[0].Rows;            //定义一个DataRow数组
                int rowsnum = ds.Tables[0].Rows.Count;

                if (rowsnum == 0)
                {
                    Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
                    return;
                }
                else
                {
                    for (int i = 0; i < rowsnum; i++)
                    {
                        RedListCommand cmd = new RedListCommand();
                        if (cmd.CheckByPhone(ds.Tables[0].Rows[i]["电话号码"].ToString()))
                        {
                            _log.InfoMsg("电话号码为：" + ds.Tables[0].Rows[i]["电话号码"].ToString()+"的红名单已存在");
                            continue;
                        }
                        TB_RedList redlist = new TB_RedList();
                       RekTec.EAP.Common.PersisitBroker pb = new RekTec.EAP.Common.PersisitBroker("ITCC");
                        try
                        {
                            pb.OpenDb();
                          
                            redlist.EmployeeName = ds.Tables[0].Rows[i]["姓名"].ToString();
                            redlist.PhoneNo = ds.Tables[0].Rows[i]["电话号码"].ToString();                            
                            redlist.Department= ds.Tables[0].Rows[i]["部门"].ToString();
                            redlist.Description = ds.Tables[0].Rows[i]["描述"].ToString();
                            redlist.RedGroupID = Convert.ToInt32(ds.Tables[0].Rows[i]["组别"].ToString());
                            redlist.CreateBy = pb.GetCurrentUserName();
                            pb.Create(redlist);
                        }
                        catch (Exception ex)       //捕捉异常
                        {
                            Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
                            return;
                        }

                    }
                    Response.Write("<script>alert('Excle表导入成功!')</script>");
                    this.BindFormData();
                }
                //this.txtLog.Text = this.message.ToString();
            }
            else
            {
                base.ShowMessage("请选择要上传的文件！");
            }
        }
        catch (Exception ex)
        {
            base.ErrorException(ex);
        }

    }