使用ASP.NET的GridView导出/儲存資料至Excel,Word,Txt檔,Powerpoint-CSDN博客

ps.註解的部分屬於儲存檔案的程式碼

excel.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="excel.aspx.cs" Inherits="excel" %>
02
03 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
04 <html xmlns=" http://www.w3.org/1999/xhtml">
05 <head id="Head1" runat="server">
06     <title>未命名頁面</title>
07 </head>
08 <body>
09     <form id="form1" runat="server">
10         <div>
             <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
                 <Columns>
                     <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
                         SortExpression="id" />
                     <asp:BoundField DataField="gender" HeaderText="gender" SortExpression="gender" />
                 </Columns>
             </asp:GridView>
             <asp:Button ID="ExportExcel" runat="server" Text="匯出Excel檔/儲存Excel檔" OnClick="ExportExcel_Click"
                 Width="200px" /><br />
             <asp:Button ID="ExportWord" runat="server" Text="匯出Word檔/儲存Word檔" OnClick="ExportWord_Click"
                 Width="200px" /><br />
             <asp:Button ID="ExportText" runat="server" Text="匯出txt檔/儲存txt檔" Width="200px" OnClick="ExportText_Click" />
             <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>"
                 SelectCommand="SELECT * FROM [user]"></asp:SqlDataSource>
         </div>
     </form>
</body>
</html>
excel.aspx.cs

using ...System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.IO;

public partial class excel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

     }
     protected void ExportExcel_Click(object sender, EventArgs e)
{
         //匯出excel檔
         Response.Clear();
         Response.AddHeader("content-disposition", "attachment;filename=test.xls");//excel檔名
         Response.ContentType = "application/vnd.ms-excel";
         Response.Charset = "";
         System.IO.StringWriter sw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
        DataGrid dg = new DataGrid();
        dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);
       dg.DataBind();
        dg.RenderControl(htw);
        Response.Write(sw.ToString());
         Response.End();

        //儲存excel檔
         //System.IO.StringWriter sw = new System.IO.StringWriter();
       //System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
        //DataGrid dg = new DataGrid();
        //dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);
         //dg.DataBind();
        //dg.RenderControl(htw);
       //System.IO.File.WriteAllText(@"c:/test.xls", sw.ToString());
     }
     protected void ExportWord_Click(object sender, EventArgs e)
{
        //匯出word檔
       Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=test.doc");//word檔名
        Response.ContentType = "application/vnd.ms-word";
         Response.Charset = "";
         System.IO.StringWriter sw = new System.IO.StringWriter();
         System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
         DataGrid dg = new DataGrid();
         dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);
        dg.DataBind();
         dg.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();

         //儲存word檔
         //System.IO.StringWriter sw = new System.IO.StringWriter();
        //System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
         //DataGrid dg = new DataGrid();
        //dg.DataSource = this.SqlDataSource1.Select(DataSourceSelectArguments.Empty);
         //dg.DataBind();
         //dg.RenderControl(htw);
         //System.IO.File.WriteAllText(@"c:/test.doc", sw.ToString());
     }
     protected void ExportText_Click(object sender, EventArgs e)
{
         //匯出txt檔
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=test.txt");//txt檔名
        Response.ContentType = "application/vnd.ms-word";
         Response.Charset = "";
      System.Text.StringBuilder sb = new System.Text.StringBuilder();
        DataTable dt = new DataTable();
         dt = ((DataView)this.SqlDataSource1.Select(DataSourceSelectArguments.Empty)).ToTable();
         //欄位名
         for (int i = 0; i < dt.Columns.Count; i++)
         {
            sb.Append(dt.Columns[i].ColumnName);
             sb.Append(",");
         }
         sb.Append(Environment.NewLine);
        //資料
         for (int i = 0; i < dt.Rows.Count; i++)
         {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                 sb.Append(dt.Rows[i][j].ToString());
                 sb.Append(",");
             }
             sb.Append(Environment.NewLine);
         }
         Response.Write(sb.ToString());
         Response.End();

         //儲存txt檔
         //System.Text.StringBuilder sb = new System.Text.StringBuilder();
         //DataTable dt = new DataTable();
         //dt = ((DataView)this.SqlDataSource1.Select(DataSourceSelectArguments.Empty)).ToTable();
         欄位名
         //for (int i = 0; i < dt.Columns.Count; i++)
         //...{
         // sb.Append(dt.Columns[i].ColumnName);
         // sb.Append(",");
         //}
         //sb.Append(Environment.NewLine);
        資料
         //for (int i = 0; i < dt.Rows.Count; i++)
         //...{
         // for (int j = 0; j < dt.Columns.Count; j++)
         // ...{
         // sb.Append(dt.Rows[i][j].ToString());
         // sb.Append(",");
         // }
         // sb.Append(Environment.NewLine);
         //}
         //System.IO.File.WriteAllText(@"c:/test.txt", sb.ToString());
     }

    protected void Button3_Click(object sender, EventArgs e)
      {
          //Export("application/ms-excel", "报表.xls");//输出Excel
         Export("application/ms-powerpnt", "报表.ppt"); //输出Powerpoint
      }
}