在ASP.NET中将dataset数据保存到excel,GridView导出到excel

最新推荐文章于 2024-04-22 16:32:10 发布

highplayer

最新推荐文章于 2024-04-22 16:32:10 发布

阅读量3.5k

点赞数

文章标签： excel dataset asp.net asp server string

本文链接：https://blog.csdn.net/highplayer/article/details/5662169

版权

1 把dataset数据保存到excel

protected void Page_Load(object sender, EventArgs e)
{

        //建立连接
        SqlConnection con = new SqlConnection();
        con.ConnectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=True";

        //创建一个SqlCommand对你
        SqlCommand cmd = new SqlCommand("select * from Customers", con);
        con.Open();
        SqlDataAdapter ad = new SqlDataAdapter(cmd);

        DataSet set = new DataSet();
        ad.Fill(set);
        string fileName = "myexecl";
        CreateExcel(set, fileName);

con.Close();
}

   public void CreateExcel(DataSet ds, string FileName)
    {
        HttpResponse resp;
        resp = Page.Response;
        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
        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; i++)
        {
            if (i == (cl - 1))//最后一列，加n
            {
                colHeaders += dt.Columns[i].Caption.ToString() + "/n";
            }
            else
            {
                colHeaders += dt.Columns[i].Caption.ToString() + "/t";
            }
        }
        resp.Write(colHeaders);
        //向HTTP输出流中写入取得的数据信息
        //逐行处理数据　
        foreach (DataRow row in myRow)
        {
            //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据　　
            for (i = 0; i < cl; i++)
            {
                if (i == (cl - 1))//最后一列，加n
                {
                    ls_item += row[i].ToString() + "/n";
                }
                else
                {
                    ls_item += row[i].ToString() + "/t";
                }
            }
            resp.Write(ls_item);
            ls_item = "";
        }
        resp.End();
    }

2 GridView导出到excel

前台代码：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="TrandformExecl.aspx.cs" Inherits="TrandformExecl" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>无标题页</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    </div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        DataKeyNames="CustomerID" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True"
                SortExpression="CustomerID" />
            <asp:BoundField DataField="CompanyName" HeaderText="CompanyName"
                SortExpression="CompanyName" />
            <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
            <asp:BoundField DataField="Address" HeaderText="Address"
                SortExpression="Address" />
            <asp:BoundField DataField="Country" HeaderText="Country"
                SortExpression="Country" />
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString3 %>"
        SelectCommand="SELECT [CustomerID], [CompanyName], [City], [Address], [Country] FROM [Customers]">
    </asp:SqlDataSource>
    </form>
</body>
</html>
后台代码：

protected void Page_Load(object sender, EventArgs e)
    {
         ExportExcel(this.GridView1);

}

public void ExportExcel(GridView exportTargetGridView)
{
HttpContext.Current.Response.ClearContent();

HttpContext.Current.Response.Charset = "GB2312";

HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF7;

HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");

HttpContext.Current.Response.ContentType = "application/excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

        exportTargetGridView.RenderControl(htw);

        HttpContext.Current.Response.Write(sw.ToString());

HttpContext.Current.Response.End();
}

public override void VerifyRenderingInServerForm(Control control)//注意重写这个函数
{

}

highplayer

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
在ASP.NET中将dataset数据保存到excel,GridView导出到excel

<br />1 把dataset数据保存到excel<br /> protected void Page_Load(object sender, EventArgs e)<br /> {<br /> //建立连接<br /> SqlConnection con = new SqlConnection();<br /> con.ConnectionString = "Data Source=.;Initial Catalog=Northwind;Integra
复制链接

扫一扫