用excel 导入导出数据

 /// <summary>
        /// .xls导入数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnDaoRu_Click(object sender, EventArgs e)
        {
            // Flag("DelPurview&0", "EnterLibPos");

             if (string.IsNullOrEmpty(FileUpload1.FileName)) 
            {
                JScriptUtil.AlertAndRedirect("请选择导入文件!", "index.aspx");
            }

             string sFileName = FileUpload1.FileName; //文件的名字

            if (sFileName.Substring(sFileName.Length - 4, 4).ToLower()!=".xls") 
            {
                JScriptUtil.AlertAndRedirect("格式不支持!", "index.aspx");
                return;
            }

            //文件保存的路径
            string sFilePath = HttpContext.Current.Server.MapPath("~/") + "excel\" + sFileName;

            FileUpload1.PostedFile.SaveAs(sFilePath);
            if (!File.Exists(sFilePath))
            {
                JScriptUtil.AlertAndRedirect("文件上传不成功!", "index.aspx");
            }

            string sErrorMsg = string.Empty; //错误信息

            //从Excel获取到的DataSet数据
            DataSet dsBookExcel = ExcelUtil.GetDataSetByPath(sFilePath, ref sErrorMsg);

            //此时出错了
            if (sErrorMsg.Length > 0) 
            {
                JScriptUtil.AlertAndRedirect(sErrorMsg, "index.aspx");
            }

            foreach (DataTable dtItem in dsBookExcel.Tables)
            {
               
                if (!dtItem.Columns.Contains("第三空间ID") || dtItem.Rows.Count == 0)
                {   
                    continue;
                }
                foreach (DataRow drItem in dtItem.Rows)
                {
                    InsertData(drItem);
                }
            }
            bind_data();
            JScriptUtil.AlertAndRedirect("导入完成", "index.aspx");
        
        }

/// <summary>         /// 通过路径获取DataSet         /// </summary>         /// <param name="strFileName"> 文件路径 </param>         /// <param name="strErrorMsg"> 错误信息 </param>         public static DataSet GetDataSetByPath(string strFileName, ref string strErrorMsg)         {             OleDbConnection OleConn = null;             DataSet dsResult = new DataSet();  // DataSet结果集             try             {                 string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + ";Extended Properties='Excel 12.0;HDR=False;IMEX=1'";                 OleConn = new OleDbConnection(strConn);                 OleConn.Open();

                //Excel中表的数据                 DataTable dtSheet = OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,                     new object[] { null, null, null, "TABLE" });//获取工作簿

                string sql = string.Empty; // 查询语句

                foreach (DataRow item in dtSheet.Rows)                 {                     if (item[2].ToString().Contains("FilterDatabase")) continue; // 过滤掉重复的数据                     sql = string.Format("SELECT * FROM  [{0}]", item[2].ToString());                     OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);                     DataTable dtItem = new DataTable();                     OleDaExcel.Fill(dtItem);                     dtItem.TableName = item[2].ToString();                     dsResult.Tables.Add(dtItem);                     OleDaExcel.Dispose();                     dtItem.Dispose();                 }

            }             catch (Exception err)             {                 strErrorMsg = string.Format("数据绑定Excel失败!失败原因：{0}", err.Message);                 return null;             }             finally             {                 OleConn.Close();             }             return dsResult;         }

   /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnDaochu_Click(object sender, EventArgs e)
        {
            string sql = "select a.*,b.Name,b.Address,b.Tel,b.OpenTime,b.AreaId from dbo.bz_EnterpriseLibaryPos as a left join dbo.bz_EnterpriseLibrary as b on a.EnterpriseLibaryId=b.EnterpriseLibraryId ";
          
            //得到需要导入Excel的DataTable
            DataTable dt = Common.DBUtility.SqlHelper.ExecuteDataTable(CommandType.Text, sql, null); ;
            CreateExcel(dt, "zuobiao.xls", this.Page);
          
        }

        /// <summary>
        /// 导出xls
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="fileName">文件名（坐标.xls）</param>
        /// <param name="page"></param>
        public static void CreateExcel(DataTable dt, string fileName, Page page)
        {
            HttpResponse resp;
            resp = page.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            //  HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=Excel.xls");
            string colHeaders = "", ls_item = "";

            ////定义表对象与行对象，同时用DataSet对其值进行初始化
            //DataTable dt = ds.Tables[0];
            DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
            int i = 0;
            int cl = dt.Columns.Count;

            //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
            for (i = 0; i < cl-1; i++)
            {
                if (i == (cl - 2))//最后一列，加n,不是AreaId列
                {
                   // colHeaders += dt.Columns[i].Caption.ToString() + "
";
                    colHeaders += "开放时间" + "
";
                }
                else
                {
                   // colHeaders += dt.Columns[i].Caption.ToString() + "	";
                    switch (dt.Columns[i].Caption.ToString())
                    {
                        case "EnterpriseLibaryPosId": colHeaders += "序号" + "	"; break;
                        case "EnterpriseLibaryId": colHeaders += "第三空间ID" + "	";break ;
                        case "Position": colHeaders += "坐标" + "	"; break;
                        case "Name": colHeaders += "名称" + "	"; break;
                        case "Address": colHeaders += "地址" + "	"; break;
                        case "Tel": colHeaders += "电话" + "	"; break;

                        default: return;
                    }
                }

            }
            resp.Write(colHeaders);
            //向HTTP输出流中写入取得的数据信息

            //逐行处理数据 
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据   
                for (i = 0; i < cl-1; i++)
                {
                    if (i == (cl - 2))//最后一列，加n
                    {
                        ls_item += row[i].ToString() + "
";
                    }
                    else
                    {
                        if (i == 4)
                        {
                            string addr = AreaBLL.GetArea(Convert.ToInt32(row[7])) + row[i].ToString();
                            ls_item += addr + "	";
                        }
                        else
                        {
                            ls_item += row[i].ToString() + "	";
                        }
                    }

                }
                resp.Write(ls_item);
                ls_item = "";
            }
            dt.Clear();
            resp.End();
        }