/// <summary>        /// 查询Excel文件中的数据        /// </summary>        /// <param name="strPath">文件路径</param>        /// <param name="sheetName">工作表名</param>        /// <returns>DataTable</returns>        public DataTable getExcel(string strPath,string sheetName)        {            string mystring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = ‘" + strPath + "‘;Extended Properties=‘Excel 8.0;HDR=Yes;IMEX=1;‘";
　　　　　　　　//连接串, HDR=yes  表示excel第一行是标题行，反之表示第一行不是标题行            OleDbConnection cnnxls = new OleDbConnection(mystring);            cnnxls.Open();            OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [" + sheetName + "]", cnnxls);//查询工作表中的数据            DataSet ds = new DataSet();            myDa.Fill(ds, "[" + sheetName + "]");            cnnxls.Close();            DataTable dt = ds.Tables[0];            return dt;        }

        /// <summary>        /// 把Excel中的数据保存到sql数据库中        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        protected void btnSave_Click(object sender, EventArgs e)        {
            if (LaValue.Text.Length >0)            {                Helper.ExcelHelper eh = new Helper.ExcelHelper();//公共方法类对象，封装Excel操作的方法                DataTable dt = eh.getExcel(LaValue.Text, DropDownList1.Text.ToString());//得到Datatable                DataRow[] dr = dt.Select();//得到                int rowsnum = dt.Rows.Count;//表中数据行数                List<String> lstMsg = new List<string>();//保存失败信息                if (rowsnum == 0)                {                    Page.ClientScript.RegisterStartupScript(this.GetType(), "a", "alert(‘Excel表为空表,无数据!‘);", true);                }                else                {                    String error = "";                    for (int i = 0; i < dr.Length; i++)//循环插入数据                    {                        // excel列名不能变                        string C_OraName = dr[i]["CompanyName"].ToString();                        string C_Town = dr[i]["Town"].ToString();                        string C_Trding = dr[i]["Trading"].ToString();                        string C_PostCode = dr[i]["PostCode"].ToString();                        string C_Phone = dr[i]["Phone"].ToString();                        if (String.IsNullOrEmpty(C_OraName))                        {                            error += "公司名不能为空 ";                        }                        if (error.Equals(""))   //                        {                            Model.Model_Companey company_model = new Model.Model_Companey();                            BLL.BLL_Companey company_bll = new BLL.BLL_Companey();                            company_model.C_OraName = C_OraName;                            company_model.C_Town = C_Town;                            company_model.C_Trding = C_Trding;                            company_model.C_Postcode = C_PostCode;                            company_model.C_Phone = C_Phone;                            // 公司名之前未出现过                            if (Helper.SQLHelper.SqlContains(C_OraName, "Companey", "C_OraName") == false)                            {                                company_bll.Add(company_model);//添加数据                            }                            else                            {                                error = C_OraName + "已存在";                                lstMsg.Add("未导入成功，" + "原因:" + error + "。");//保存错误信息                                error = "";                            }                        }                        else                        {                        }                    }                    this.txtMessageList.Items.Add("导入完成。");                    if (null != lstMsg)                    {                        this.txtMessageList.Items.Add("共有" + lstMsg.Count() + "条记录未成功。<br /><br />");                        foreach (string s in lstMsg)                        {                            this.txtMessageList.Items.Add(s);                        }                    }                }            }         }

        /// <summary>        /// 把工作表名绑定到DropDownList控件中        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        protected void btnup_Click(object sender, EventArgs e)        {            if (FileUpload1.HasFile)            {                string ext = System.IO.Path.GetExtension(FileUpload1.FileName);//获取文件后缀                if (ext.ToLower() != ".xls" && ext.ToLower() != ".xlsx")                {                    Page.ClientScript.RegisterStartupScript(this.GetType(), "a", "alert(‘请选择一个Excel文件!‘);", true);                    return;                }                HttpFileCollection files = HttpContext.Current.Request.Files;//获取页面上所有的FileUpload控件                string tmp = files[0].FileName;                string savePath = "e:/test/" + DateTime.Now.ToString("yymmdd") + ".xls";//保存路径                LaValue.Text = savePath; //保存路径到一个不可见的Label控件中                            FileUpload1.SaveAs(savePath);//保存文件                DropdownListBind(savePath);//绑定DropDownList            }        } 

        /// <summary>        /// 绑定DropDownList        /// </summary>        /// <param name="savePath">路径</param>        protected void DropdownListBind(string savePath)        {            string mystring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = ‘" + savePath + "‘;Extended Properties=‘Excel 8.0;HDR=Yes;IMEX=1;‘";//连接Excel串            OleDbConnection cnnxls = new OleDbConnection(mystring);//创建连接            cnnxls.Open();            DataSet ds = new DataSet();            DataTable dt = cnnxls.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//获取工作表名            DropDownList1.DataSource = dt;            foreach (DataRow dr in dt.Rows)            {                DropDownList1.Items.Add((String)dr["TABLE_NAME"]);//向控件中添加工作表名            }            cnnxls.Close();        }