欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

解决Girdviewd导出Excel的相关问题 博客分类: ASP.NET excelaspc#gridview

程序员文章站 2024-03-21 22:27:34
...
前台代码如下不多说:
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" EnableEventValidation="false"%>
<!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>
    <style type="text/css">
        #form1
        {
            height: 645px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
        AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333"
        GridLines="None" Height="234px"
        onpageindexchanging="GridView1_PageIndexChanging" Width="782px">
        <PagerSettings FirstPageText="首页" LastPageText="尾页"
            Mode="NextPreviousFirstLast" NextPageText="下一页" PreviousPageText="上一页" />
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <Columns>
            <asp:BoundField DataField="id" HeaderText="序号">
                <ItemStyle HorizontalAlign="Center" />
            </asp:BoundField>
            <asp:BoundField DataField="StuId" HeaderText="学号">
                <ItemStyle HorizontalAlign="Center" />
            </asp:BoundField>
            <asp:BoundField DataField="StuPwd" HeaderText="密码">
                <ItemStyle HorizontalAlign="Center" />
            </asp:BoundField>
            <asp:BoundField DataField="StuName" HeaderText="姓名">
                <ItemStyle HorizontalAlign="Center" />
            </asp:BoundField>
            <asp:BoundField DataField="StuCardId" HeaderText="身份证号">
                <ItemStyle HorizontalAlign="Center" />
            </asp:BoundField>
            <asp:BoundField DataField="TeaName" HeaderText="任课教师">
                <ItemStyle HorizontalAlign="Center" />
            </asp:BoundField>
        </Columns>
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#999999" />
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    </asp:GridView>
    <br />
    <br />
    <asp:Button ID="Button1" runat="server" onclick="Button1_Click"
        Text="导出到Excel" />
    </form>
</body>
</html>


后台代码如下:注释很详细不再累赘

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;///添加数据库操作的命名空间
using System.IO;///添加输出操作的命名空间
public partial class _Default : System.Web.UI.Page
{
    /// <summary>
    /// 绑定Gridview 控件的数据
    /// </summary>
    /// <returns></returns>
    protected bool BindDB()
    {
        string sqlcon = ConfigurationManager.ConnectionStrings["DBString"].ConnectionString;
        SqlConnection con = new SqlConnection(sqlcon);
        con.Open();
        string strSelect = "select * from Student ";
        SqlDataAdapter sda = new SqlDataAdapter(strSelect,sqlcon);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        GridView1.DataSource = ds;
        try
        {
            GridView1.DataBind();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            con.Close();
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindDB();///数据库绑定
        }
    }
    /// <summary>
    /// Gridview 分页操作 没有分业的可以不添加此方法
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        BindDB();
    }
    /// <summary>
    /// Gridview数据导出到Excel的操作
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e)
    {
        #region
        ///在导出的时候,如果某个字段为长数字(如身份证号码511922198507151512)、以0开头的编号(如0914490000)
        ///之类的数据。如果不加处理在导出的Excel文件中将会被分别当作5.11922E+17和809111212来处理,这样与我们要达到的
        ///实际效果不一致。所以我们要加以处理,即给单元格数据规定格式
        ///即我们在导出的时候把类型改掉
        //for (int i=0; i < GridView1.Rows.Count; i++)
        //{
        //    GridView1.Rows[i].Cells[4].Text = "'" + GridView1.Rows[i].Cells[4].Text;
        //}
        #endregion


        ///另一种方式就是
        ///解决思路:在Excel中作一个包含有"012457890"的内容,设定单元格的显示方式,然后保存成Html的文件,
        ///在查看源代码. 发现在 CSS格式定义中有:td{mso-number-format:"\@";}.这样问题就容易解决了.我用的此种方式


        GridView1.AllowPaging = false;///清除分页,便于导出数据
        BindDB();  ///绑定Griedview数据                           
        Response.ClearContent();
        Response.Charset = "GB2312"; ///设定输出的字符集 
        string strStyle = "<style>td{mso-number-format:\"\\@\";}</style>"; ///设置输出格式


        ///解决导出到Excel2003乱码问题
        ///HttpUtility.UrlEncode("学生信息表.xls", System.Text.Encoding.UTF8).ToString())                                                                 
        Response.AddHeader("content-disposition", "attachment; filename=" +
                           HttpUtility.UrlEncode("学生信息表.xls", System.Text.Encoding.UTF8).ToString());     
        Response.ContentType = "application/excel"; ///设置导出文件的格式
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        sw.WriteLine(strStyle); ///读取格式
       
        ///GridView1.Columns[11].Visible = false; ///某一列Gridview数据不导出


        GridView1.RenderControl(htw);
        Response.Write(sw.ToString()); ///把HTML写回浏览器 
        Response.End();
        GridView1.AllowPaging = true;///恢复分业
        BindDB();///绑定Griedview数据
    }
    /// <summary>
    /// 此方法一定要重载,否则报错!
    /// </summary>
    /// <param name="control"></param>
    public override void VerifyRenderingInServerForm(Control control)
    {
    }
}

主要解决了Gridview导出到Excel
图不会传。。。。