ASP.NET Excel导出

导出方式1：

 protected void GridToExcel_Click(object sender, ImageClickEventArgs e)
 {

      string strHTML = GetString1(table);//得到字符串

       string FileName = "个人得分状况" + DateTime.Now.ToString();
      CreateExcel(FileName, strHTML.ToString(), Response);

}

 //拼HTML串

 private string GetString1(DataTable dt)
    {
        DateTime fromDate = PickerFrom.SelectedDate;
        DateTime toDate = PickerTo.SelectedDate;
        StringBuilder str = new StringBuilder();
        str.Append("<center><table style=\"width: 100%;\"><tr><td colspan=\"" + 8 +

        "\" style=\"text-align: center; font-size: 16pt; height:80px;\"><b>个人得分状况</b></td></tr>");

       str.Append("<td colspan=\"" + 8 + "\" style=\"text-align: right; font-size: 11pt;\">日期：" + fromDate.ToString("yyyy年MM月dd日")

        + "-" + toDate.ToString("yyyy年MM月dd日")    + "</td></tr></table>");

        str.Append("<table border=\"1\" cellpadding=\"0\" cellspacing=\"0\" style=\"text-align: center; border-color: Black;width: 100%;\">");
        str.Append("<tr><td style=\"font-size: 9pt;  font-weight: bold; text-align: center;\">项目部</td>");
        str.Append("<td style=\"font-size: 9pt; font-weight: bold; text-align: center;\">班组</td>");
        str.Append("<td style=\"font-size: 9pt; font-weight: bold; text-align: center;\">姓名</td>");
        str.Append("<td style=\"font-size: 9pt; font-weight: bold; text-align: center;\">日期</td>");
        str.Append("<td style=\"font-size: 9pt; font-weight: bold; text-align: center;\">路段</td>");
        str.Append("<td style=\"font-size: 9pt; font-weight: bold; text-align: center;\">工作项目</td>");
        str.Append("<td style=\"font-size: 9pt; font-weight: bold; text-align: center;\">得分</td>");
        str.Append("<td style=\"font-size:9pt; font-weight: bold; text-align: center;\">金额</td></tr>");
        foreach (DataRow row in dt.Rows)
        {
            str.Append("<tr><td style=\"font-size: 9pt;text-align: center;\">" + row["FDepartmentName"] + "</td>");
            str.Append("<td style=\"font-size: 9pt;text-align: center;\">" + row["DepartmentName"] + "</td>");
            str.Append("<td style=\"font-size: 9pt;  text-align: center;\">" + row["WorkerName"] + "</td>");
            str.Append("<td style=\"font-size: 9pt;text-align: center;\">" + row["WorkDate"] + "</td>");
            str.Append("<td style=\"font-size: 9pt;  text-align: center;\">" + row["Location"] + "</td>");
            str.Append("<td style=\"font-size: 9pt;text-align: center;\">" + row["ConservationName"] + "</td>");
            str.Append("<td style=\"font-size: 9pt;  text-align: center;\">" + row["Score"] + "</td>");
            str.Append("<td style=\"font-size: 9pt; text-align: center;\">" + row["SMoney"] + "</td></tr>");
        }
        str.Append("</table></center>");
        return str.ToString();
    }

   //导出方法

  public static void CreateExcel(string FileName, string html, HttpResponse Response)
    {
        StringBuilder sbPrint = new StringBuilder();
        sbPrint.Append(html);
        if (sbPrint != null)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "GB2312";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" +

            HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)                                                 

             + ".xls");//HttpUtility.UrlEncode(FileName, System.Text.Encoding.GetEncoding("GB2312")) + ".xls");
            // 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码！！！
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
            Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

            Label lbExport = new Label();
            lbExport.Text = sbPrint.ToString();
            lbExport.RenderControl(oHtmlTextWriter);

            Response.Output.Write(oStringWriter.ToString());
            Response.Flush();
            Response.End();
        }
    }

============================================================================

导出方式2：

 protected void GridToExcel_Click(object sender, ImageClickEventArgs e)
    {
            DataTable endTable = new DataTable();
            endTable.Columns.Add("项目部", typeof(System.String));
            endTable.Columns.Add("班组", typeof(System.String));
            endTable.Columns.Add("姓名", typeof(System.String));
            endTable.Columns.Add("日期", typeof(System.DateTime));
            endTable.Columns.Add("路段", typeof(System.String));
            endTable.Columns.Add("工作项目", typeof(System.String));
            endTable.Columns.Add("得分", typeof(System.String));
            endTable.Columns.Add("金额", typeof(System.String));
            foreach (DataRow row in table.Rows)
            {
                DataRow endrow = endTable.NewRow();
                endrow["项目部"] = row["FDepartmentName"].ToString();
                endrow["班组"] = row["DepartmentName"];
                endrow["姓名"] = row["WorkerName"].ToString();
                endrow["日期"] = row["WorkDate"].ToString();
                endrow["路段"] = row["Location"].ToString();
                endrow["工作项目"] = row["ConservationName"].ToString();
                endrow["得分"] = row["Score"].ToString();
                endrow["金额"] = row["SMoney"].ToString();
                endTable.Rows.Add(endrow);
            }
           Export(this.Page, endTable);

}

//导出方法

  public static void Export(System.Web.UI.Page page, System.Data.DataTable tab)
    {
        string FileName = DateTime.Now.ToString("yyyyMMdd-hhmmss") + ".xls";
        System.Web.HttpResponse httpResponse = page.Response;
        System.Web.UI.WebControls.DataGrid dataGrid = new System.Web.UI.WebControls.DataGrid();
        dataGrid.DataSource = tab.DefaultView;
        dataGrid.AllowPaging = false;
        dataGrid.HeaderStyle.BackColor = System.Drawing.Color.White;
        dataGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        dataGrid.HeaderStyle.Font.Bold = true;
        dataGrid.DataBind();
        httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)); //filename="*.xls";
        httpResponse.ContentEncoding = System.Text.Encoding.UTF8;//.GetEncoding("GB2312");
        httpResponse.ContentType = "application/ms-excel";
        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        dataGrid.RenderControl(hw);           

        httpResponse.Write(tw.ToString());
        httpResponse.End();

    }