datatable导出excel

我们经常需要将datatable数据导出至excel中，以下是我们项目中所用到的方法：

1.

       //导出数据
        protected void Button1_Click(object sender, EventArgs e)
        {

　　　　　　//获取datatable数据
            var bll = new BLL_TeamworkAward();
            var pageModel = bll.TeamBuildingDetails(1, int.MaxValue, this.AdviserID, YearNum, MonthNum, AwardType);                     DataTable dt = pageModel.DataResult;
            if (dt != null && dt.Rows.Count > 0)
            {

　　　　　　//转换列名并导出
                HeplerTools.ImportOut.DataTable2Excel(GetSelectColumnsDT(dt, "rownum@序号,A8ID@编号,CountAdviserName@顾问姓名,AwardType@类型,JoinDate@加盟时间,ApprovalDate@审核通过时间,AdviserLevel@区域,ParentAdviserName@上级推荐人"));
            }
            else
            {
                HeplerTools.AlertMessage.ShowMessage(Page, "没有数据");
            }
        }

 1.1设置要导出的列格式内容等

  public DataTable ConvertTable(DataTable dt)
        {
            DataTable dt1 = new DataTable();
            DataColumn dc = new DataColumn("序号", Type.GetType("System.String"));
            dt1.Columns.Add(dc);
            dc = new DataColumn("顾问编号", Type.GetType("System.String"));
            dt1.Columns.Add(dc);
            dc = new DataColumn("姓名", Type.GetType("System.String"));
            dt1.Columns.Add(dc);
            dc = new DataColumn("合同起始日期", Type.GetType("System.String"));
            dt1.Columns.Add(dc);
            dc = new DataColumn("合同截止日期", Type.GetType("System.String"));
            dt1.Columns.Add(dc);
            dc = new DataColumn("推荐人", Type.GetType("System.String"));
            dt1.Columns.Add(dc);
            dc = new DataColumn("状态", Type.GetType("System.String"));
            dt1.Columns.Add(dc);
            dc = new DataColumn("区域", Type.GetType("System.String"));
            dt1.Columns.Add(dc);
            dc = new DataColumn("联系方式", Type.GetType("System.String"));
            dt1.Columns.Add(dc);
            int i = 0;
            for (i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt1.NewRow();
                dr[0] = i + 1;
                dr[1] = dt.Rows[i]["A8ID"].ToString();
                dr[2] = dt.Rows[i]["AdviserName"].ToString();
                dr[3] = dt.Rows[i]["JoinDate"].ToString();
                dr[4] = !string.IsNullOrEmpty(dt.Rows[i]["DeadLine"].ToString()) ? dt.Rows[i]["DeadLine"].ToString() : "";
                dr[5] = dt.Rows[i]["ParentAdviserName"].ToString();
                string state = dt.Rows[i]["AdviserState"].ToString();
                if (state == "0")
                    dr[6] = "待审核";
                else if (state == "1")
                    dr[6] = "正常";
                else if (state == "2")
                    dr[6] = "终止";
                else if(state=="3")
                    dr[6] = "冻结";
                dr[7] = CovertLevel(dt.Rows[i]["AdviserLevel"].ToString());
                dr[8] = dt.Rows[i]["MobileNo"].ToString();
                dt1.Rows.Add(dr);
            }
            return dt1;
        }

 

2、    /// <summary>
        /// 将列名转变
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        protected DataTable GetSelectColumnsDT(DataTable dt, string columns)
        {
            bool isColumn;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                isColumn = false;
                string[] arrNames = columns.ToUpper().Split(',');
                string[] arrName;
                for (int s = 0; s < arrNames.Length; s++)
                {
                    arrName = arrNames[s].Split('@');
                    if (String.Compare(arrName[0], dt.Columns[i].ColumnName.ToUpper(), false) == 0
                           || String.Compare(arrName[1], dt.Columns[i].ColumnName.ToUpper(), false) == 0)
                    {
                        dt.Columns[i].ColumnName = arrName[1];
                        isColumn = true;
                        break;
                    }
                }
                if (!isColumn)
                {
                    dt.Columns.RemoveAt(i);
                    i--;
                }
            }
            return dt;
        }

 

3.导出

         /// <summary>
        /// 从数据控件把数据导入至execl
        /// </summary>
        /// <param name="dtData">数据集合</param>
        /// <param name="FileName">文件名称</param>
        public static void DataTable2Excel(System.Data.DataTable dtData)
        {
            GridView dgExport = null;
            HttpContext curContext = HttpContext.Current;
           // StringWriter strWriter = null;
            StringWriter strWriter = null;
            HtmlTextWriter htmlWriter = null;
            if (dtData != null)
            {
                // set Encoding and formatting
                //System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)

                curContext.Response.Charset = "gb2312";

                curContext.Response.Buffer = true;
                curContext.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}.xls", Guid.NewGuid()));
               
                curContext.Response.ContentEncoding = Encoding.GetEncoding("gb2312");

                curContext.Response.ContentType = "application/ms-excel";//"application nd.ms-excel";  

                // export to Excel file

              
                strWriter = new StringWriter();
               
                htmlWriter = new HtmlTextWriter(strWriter);

                //
                dgExport = new GridView();
                dgExport.DataSource = dtData.DefaultView;
                dgExport.AllowPaging = false;
                dgExport.DataBind();

                // DownLoad to client side
                dgExport.RenderControl(htmlWriter);
                curContext.Response.Write("<html><head><title>导出</title><meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\"/></head><body>");
                curContext.Response.Write(strWriter.ToString());
                curContext.Response.Write("</body></html>");
                curContext.Response.Flush();
                curContext.Response.End();

                strWriter.Dispose();
                htmlWriter.Dispose();  
            }
        }