DataGrid 完全攻略之二（把数据导出到Excel）

最新推荐文章于 2019-08-22 15:36:00 发布

转载最新推荐文章于 2019-08-22 15:36:00 发布 · 217 阅读

文章标签：

#ui #c# #javascript #ViewUI

博客展示了使用C#代码实现Excel文件导出的功能。通过设置响应头、编码、内容类型等，将数据以Excel文件形式输出。代码中涉及DataGrid和GridView的渲染控制，同时提到编码设置不当可能导致文件乱码问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前台代码：html

@ Page language="c#" Codebehind="ExportExcel.aspx.cs" AutoEventWireup="false" Inherits="MsDataGrid.ExportExcel" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >

<HTML>

<HEAD>

<title>DataGrid使用举例</title>

</HEAD>

<asp:DataGrid id="dgShow" style="Z-INDEX: 100; LEFT: 26px; POSITION: absolute; TOP: 89px" runat="server" Width="842px" Height="172px" BorderColor="Tan" BorderWidth="1px" BackColor="LightGoldenrodYellow" CellPadding="2" GridLines="None" ForeColor="Black" PageSize="1" AutoGenerateColumns="False" ShowFooter="True" AllowPaging="True">

<asp:BoundColumn DataField="StudentID" ReadOnly="True" HeaderText="学生ID"></asp:BoundColumn>

<asp:BoundColumn DataField="StudentName" HeaderText="学生姓名"></asp:BoundColumn>

<asp:BoundColumn DataField="StudentPass" HeaderText="密码"></asp:BoundColumn>

<asp:BoundColumn DataField="Sex" HeaderText="性别"></asp:BoundColumn>

<asp:BoundColumn DataField="Birthday" HeaderText="生日" DataFormatString="{0:yyyy-M-d}"></asp:BoundColumn>

<asp:BoundColumn DataField="Email" HeaderText="邮件地址"></asp:BoundColumn>

<asp:BoundColumn DataField="Score" HeaderText="分数"></asp:BoundColumn>

<asp:ButtonColumn Text="选择" CommandName="Select"></asp:ButtonColumn>

</Columns>

</asp:DataGrid>

<asp:Button id="btnMIME" style="Z-INDEX: 102; LEFT: 230px; POSITION: absolute; TOP: 44px" runat="server" Text="导出"></asp:Button></FONT>

</form>

</body>

</HTML>

后台代码：cs

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

namespace MsDataGrid

{

/**//// <summary>

/// WebForm1 的摘要说明。

/// </summary>

public class ExportExcel : System.Web.UI.Page

{

protected System.Web.UI.WebControls.Button btnMIME;

protected System.Web.UI.WebControls.DataGrid dgShow;

private void Page_Load(object sender, System.EventArgs e)

{

// 在此处放置用户代码以初始化页面

if(!IsPostBack)

BindData();

}

private void BindData()

{

string strCon = System.Configuration.ConfigurationSettings.AppSettings["DSN"];

SqlConnection con = new SqlConnection(strCon);

SqlDataAdapter da = new SqlDataAdapter("Select * from tbStudentinfo",con);

DataSet ds = new DataSet();

da.Fill(ds,"studentinfo");

dgShow.DataSource = ds.Tables["studentinfo"].DefaultView;

dgShow.DataBind();

}

Web Form Designer generated code#region Web Form Designer generated code

override protected void OnInit(EventArgs e)

{

// CODEGEN：该调用是 ASP.NET Web 窗体设计器所必需的。

InitializeComponent();

base.OnInit(e);

}

/**//// <summary>

/// 设计器支持所需的方法 - 不要使用代码编辑器修改

/// 此方法的内容。

/// </summary>

private void InitializeComponent()

{

this.btnMIME.Click += new System.EventHandler(this.btnMIME_Click);

this.Load += new System.EventHandler(this.Page_Load);

}

#endregion

private void btnMIME_Click(object sender, System.EventArgs e)

{

Response.ContentType = "application/vnd.ms-excel";

Response.Charset = "";

this.EnableViewState = false;

System.IO.StringWriter sw = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);

int nCur = dgShow.CurrentPageIndex;

int nSize = dgShow.PageSize;

dgShow.AllowPaging = false;

BindData();

dgShow.Columns[7].Visible =false;

dgShow.RenderControl(hw);

dgShow.Columns[7].Visible =true;

//以下恢复分页

dgShow.AllowPaging = true;

dgShow.CurrentPageIndex = nCur;

dgShow.PageSize = nSize;

BindData();

Response.Write(sw.ToString());

Response.End();

}

方法二：
if(DataGrid1.Items.Count==0)
    {
     Response.Write("<script>alert('对不起,你没有查询到任何记录,不能导出数据')</script>");
    }
    else
    {

     Response.Clear();
     Response.Buffer= true;
     Response.Charset="GB2312";
     Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls");
     Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
     //设置输出流为简体中文
     Response.ContentType = "application/ms-excel";
     //设置输出文件类型为excel文件。
     this.EnableViewState = false;
     System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
     System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
     System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
     DataGrid1.RenderControl(oHtmlTextWriter);
     Response.Write(oStringWriter.ToString());
     Response.End();

}
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312";
        Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
        // 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码！！！
        Response.ContentEncoding = System.Text.Encoding.UTF7;
        Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        this.GridView1.RenderControl(oHtmlTextWriter);
        Response.Output.Write(oStringWriter.ToString());
        Response.Flush();
        Response.End();