asp.net中Gradview绑定数据后输出到Excel表格中

最新推荐文章于 2020-11-03 18:58:55 发布

fysuccess

最新推荐文章于 2020-11-03 18:58:55 发布

阅读量957

点赞数

分类专栏： C# 办公软件文章标签： asp.net gridview excel word

本文链接：https://blog.csdn.net/fysuccess/article/details/38227735

版权

C# 同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

办公软件

2 篇文章 0 订阅

订阅专栏

第一步：首先绑定数据源到Gradview中，

GradviewDemo.aspx页面

<asp:GridView ID="GridView1" runat="server" Width="100%" CellPadding="4" ForeColor="#333333"
                            AutoGenerateColumns="False" AllowPaging="True" PageSize="12" BorderColor="Silver"
                            BorderStyle="Solid" BorderWidth="1px" OnRowDataBound="GridView1_RowDataBound"
                            ShowFooter="True" EmptyDataText="没有数据记录！！" >
                            <Columns>
                                <asp:BoundField HeaderText="姓名" FooterText="姓名" DataField="name"  />
                                <asp:BoundField HeaderText="年龄" FooterText="年龄" DataField="age" />
			        <asp:BoundField HeaderText="地址" FooterText="地址" DataField="address"  />
                                <asp:BoundField HeaderText="手机" FooterText="手机" DataField="phone" />

                            </Columns>
                            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                            <EditRowStyle BackColor="#999999" />
                            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                            <PagerSettings Visible="False" />
                            <FooterStyle Font-Bold="True" />
                            <HeaderStyle Font-Bold="False" Font-Italic="False" />
 </asp:GridView>

 <asp:Button ID="BtnExcel" runat="server" OnClick="BtnExcel_Click" Text="导出到Excel表格" /> <br/>

<pre name="code" class="csharp"><asp:Button ID="BtnWord" runat="server" OnClick="BtnExcel_Click" Text="导出到Word" />

GradviewDemo.cs后台部分源码

 private void GridViewBind(string Sqlsort)
        {
	        dt = new DataTable();
		dt.Columns.Add("name");
		dt.Columns.Add("age");
		dt.Columns.Add("address");
		dt.Columns.Add("phone");

		DataRow dr = dt.NewRow();

		List<string> Li = new List<string>(); 

		dr["name"] = "张明";
		dr["age"] = 20;
		dr["address"] = "北京";
		dr["phone"] = 13666886688;

	 
		dt.Rows.Add(dr);

		gridview1.DataSource = dt;//绑定数据源
		gridview1.DataBind();
           
        }


      protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            e.Row.Attributes["onmouseover"] = "ItemOver(this)";

            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                e.Row.Cells[1].Attributes.Add("style", "vnd.ms-excel.numberformat:@;"); //数字格式
            }
        }


 //导出到Excel
        protected void BtnExcel_Click(object sender, EventArgs e)
        {

	      //  gridview1.BottomPagerRow.Visible = false;//导出到Excel表后，隐藏分页部分

		//gridview1.Columns[4].Visible = false;//隐藏“编辑”列
		//gridview1.Columns[5].Visible = false;//隐藏“删除”列

	 //   foreach (GridViewRow gv in this.gvIndex.Rows)
         //   {
          //      gv.Cells[0].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");//excel数字格式
          //      gv.Cells[1].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");

          //  }
		    

            Response.ClearContent();
            Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
            Response.ContentType = "application/excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            GridView1.RenderControl(htw);
            Response.Write(sw.ToString());
            Response.End();


        }
//导出到Word
        protected void BtnWord_Click(object sender, EventArgs e)
        {

            Response.Clear(); //清空无关信息
            Response.Buffer = true; //完成整个响应后再发送
            Response.Charset = "GB2312";//设置输出流的字符集-中文  
            Response.Write("<meta http-equiv=Content-Type content=text/html;charset=GB2312>");
            Response.AppendHeader("Content-Disposition", "attachment;filename=wordReport.doc");//追加头信息
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流的字符集
            Response.ContentType = "application/ms-word ";//输出流的MIME类型
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            GridView1.RenderControl(oHtmlTextWriter);//Add the GridView1 into the output Stream.
            Response.Write(oStringWriter.ToString());//Output the stream.
            Response.Flush();
            Response.End();

        }

第二步，原样输出到Excel表格中

导出到Excel表格或者word中的样式，只需要在绑定Gradview前设置好即可，导出的格式才会和Gradview的格式完全一样。

保持原样导出则在GridView相应的列内容前加 ,例如

<asp:TemplateField HeaderText="名字" >   
     <ItemTemplate>   
          <%#Eval("name")%> 
     </ItemTemplate>   
</asp:TemplateField>