GridView和DataSet导出成excel

在b/s中简单的导出excel

1.把GridView数据导出excel（单纯的导出gridview的数据）

protected void Button3_Click(object sender, EventArgs e)
    {
        string style = @"<style> .text { } </script> ";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-7");//编码
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile1.xls");//excel名称
        Response.ContentType = "application/excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        GridView1.RenderControl(htw);
        Response.Write(style);
        Response.Write(sw.ToString());
        Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
    }

2.把DataSet导出（可以将dataset转成datatable，但不知道为什么添加自定义行的时候就不行了）

 public void ExportResult(DataSet ds, string excelName)//输入数据源和excel名称
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Charset = "";
        HttpContext.Current.Response.ContentType = "application/vnd.ms-xls";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-7");
        StringWriter stringWrite = new StringWriter();
        HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        DataGrid dg = new DataGrid();
        dg.DataSource = ds;
        dg.DataBind();
        dg.RenderControl(htmlWrite);
        HttpContext.Current.Response.AddHeader
            ("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(excelName));
        HttpContext.Current.Response.Write(stringWrite.ToString());
        HttpContext.Current.Response.End();
    }

3.将dataset转成datatable（可以添加自定义行和列）

public void CreateExcel(DataSet ds)
    {
       
        HttpResponse resp;
        resp = HttpContext.Current.Response;
        resp.ContentEncoding = System.Text.Encoding.GetEncoding("utf-7");//此处用UTF-7防止导出乱码,GB2312或UTF-8遇到-或+导出有可能乱码
        resp.AppendHeader("Content-Disposition", "attachment;filename=result.xls");//xls名称
        resp.ContentType = "application/ms-excel";
        DataTable tb = ds.Tables[0];
        string data = "";
        data += "<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">";
        //写出列名
        data += "<tr><td colspan=" + tb.Columns.Count + " style=\"text-align:center; font-size:20px;vnd.ms-excel.numberformat:@\">表标题</td></tr>";//表标题
        data += "<tr style=\"font-weight: bold; white-space: nowrap;\">";
        foreach (DataColumn column in tb.Columns)
        {
            data += "<td>" + column.ColumnName + "</td>";
        }
        data += "</tr>";
        //写出数据
        foreach (DataRow row in tb.Rows)
        {
            data += "<tr>";
            foreach (DataColumn column in tb.Columns)
                data += "<td style=\"vnd.ms-excel.numberformat:@\">" + row[column].ToString() + "</td>";
            data += "</tr>";
            resp.Write(data);
            data = "";
        }
        //自定义行
        data += "<tr style=\"font-weight: bold; white-space: nowrap;\">";//字体样式
        data += "<td>id合计:</td>";
        int sum = 0;
            for (int i = 0; i < tb.Columns.Count - 1; i++)
            {
                data += "<td>aaaaaa</td>";
            }
            data += "</tr>";
        resp.Write(data);
        resp.End();
    }