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

你的名字 2020-11-01 09:06 563阅读 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相应的列内容前加&nbsp;,例如

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