C#中导出Excel总结

方法一：将页面中的所有元素都导出到excel中

Response.Clear();
Response.Buffer = true;
Response.AppendHeader("Content-Disposition", "attachment;filename="+ (DateTime.Now.ToString("yyyyMMddHHmmss")+Server.UrlEncode("客户回访统计表")) + ".xls");
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/vnd.ms-excel";

方法二：将页面中指定部分的数据导出到excel中

Response.Clear();
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        pn_1.MyRender(htw);//pn_1是一个asp:Panel控件
        Response.ContentType = "application/ms-excel";
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode("打分统计明细") + ".xls");
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        Response.Write(sw.ToString());
        Response.End();

方法三：将从数据库查询出来的数据直接导出指定格式的excel

        DataTable dt = ProReturnVisitBLL.GetProReturnVisitDataTable();
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        Response.Clear();
        Response.Buffer = true;
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + (DateTime.Now.ToString("yyyyMMddHHmmss") + Server.UrlEncode("客户回访数据")) + ".xls");
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 
        this.EnableViewState = false;
        DataGrid dg = new DataGrid();
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            switch (dt.Columns[i].ColumnName)
            {
                case "PRO_NO":
                    dt.Columns[i].ColumnName = "项目协议号";
                    break;
                case "PRO_NAME":
                    dt.Columns[i].ColumnName = "项目名称";
                    break;
                case "INCOME_TYPE":
                    dt.Columns[i].ColumnName = "签单类型";
                    break;
                case "EmpName":
                    dt.Columns[i].ColumnName = "项目经理";
                    break;
                case "ReturnVisitUserName":
                    dt.Columns[i].ColumnName = "回访助理";
                    break;
                case "ReturnVisitTime1":
                    dt.Columns[i].ColumnName = "第三稿回访回访时间";
                    break;
                case "Customer1":
                    dt.Columns[i].ColumnName = "第三稿回访回访客户";
                    break;
                case "Phone1":
                    dt.Columns[i].ColumnName = "第三稿回访联系方式";
                    break;
                case "questionone1":
                    dt.Columns[i].ColumnName = "第三稿回访问题一";
                    break;
                case "questionTwo1":
                    dt.Columns[i].ColumnName = "第三稿回访问题二";
                    break;
                case "questionThree1":
                    dt.Columns[i].ColumnName = "第三稿回访问题三";
                    break;
                case "Explain1":
                    dt.Columns[i].ColumnName = "第三稿回访问题说明（当分数低于4时）";
                    break;
                case "ReturnVisitTime2":
                    dt.Columns[i].ColumnName = "完工回访回访时间";
                    break;
                case "Customer2":
                    dt.Columns[i].ColumnName = "完工回访回访客户";
                    break;
                case "Phone2":
                    dt.Columns[i].ColumnName = "完工回访联系方式";
                    break;
                case "questionone2":
                    dt.Columns[i].ColumnName = "完工回访问题一";
                    break;
                case "questionTwo2":
                    dt.Columns[i].ColumnName = "完工回访问题二";
                    break;
                case "questionThree2":
                    dt.Columns[i].ColumnName = "完工回访问题三";
                    break;
                case "Explain2":
                    dt.Columns[i].ColumnName = "完工回访问题说明（当分数低于4时）";
                    break;
                case "ReturnVisitTime3":
                    dt.Columns[i].ColumnName = "追加回访回访时间";
                    break;
                case "Customer3":
                    dt.Columns[i].ColumnName = "追加回访回访客户";
                    break;
                case "Phone3":
                    dt.Columns[i].ColumnName = "追加回访联系方式";
                    break;
                case "questionone3":
                    dt.Columns[i].ColumnName = "追加回访问题一";
                    break;
                case "questionTwo3":
                    dt.Columns[i].ColumnName = "追加回访问题二";
                    break;
                case "questionThree3":
                    dt.Columns[i].ColumnName = "追加回访问题三";
                    break;
                case "Explain3":
                    dt.Columns[i].ColumnName = "追加回访问题说明（当分数低于4时）";
                    break;
                case "questionone4":
                    dt.Columns[i].ColumnName = "问题一最终得分";
                    break;
                case "questionTwo4":
                    dt.Columns[i].ColumnName = "问题二最终得分";
                    break;
                case "questionThree4":
                    dt.Columns[i].ColumnName = "问题三最终得分";
                    break;
                default:
                    dt.Columns.RemoveAt(i);
                    i--;
                    break;
            }
        }
        //dg.CssClass = "desktable";
        dg.HeaderStyle.BackColor = Color.FromArgb(150,150,150);
        //dg.HeaderStyle.CssClass = "header";
        dg.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        dg.PagerStyle.Position = PagerPosition.Bottom;
        //dg.FooterStyle.CssClass = "header";
        dg.DataSource = dt;
        dg.DataBind();
        dg.RenderControl(htw);
        //DataSet ds = new DataSet();
        //ds.Tables.Add(dt);
        Response.Write(sw.ToString());
        Response.End();    

 

注意：如果页面中用了asp:UpdatePanel，那么查看源文件时，Repeater、DataGrid，等数据控件，里面是没有结构元素的。