DataGrid输出到Excel并进行格式化处理

来源：互联网发布：免费室内设计软件编辑：程序博客网时间：2024/05/23 02:01

在我们把DataGrid上的数据导入到Excel的时候，如果遇到比较长的数字字符串，比如身份证号码，就会在Excel里当成数字看待，并转换成科学计数法的格式，造成数据的丢失，下面这个方法就解决了这个问题，并示例如何进行其它的格式化。

查看例子
http://dotnet.aspx.cc/Exam/OutPutExcel.aspx

OutPutExcel.aspx

<%@ Page language="c#" Codebehind="OutPutExcel.aspx.cs"

AutoEventWireup="false" Inherits="eMeng.Exam.OutPutExcel" %>

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

<HTML>

<HEAD>

<title>OutPutExcel</title>

</HEAD>

<body>

<asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid>

<asp:Button id="Button1" runat="server" Text="输出到Excel"></asp:Button>

</form>

</body>

</HTML>

OutPutExcel.aspx.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;

namespace eMeng.Exam

{

/// <summary>

/// OutPutExcel 的摘要说明。

/// </summary>

public class OutPutExcel : System.Web.UI.Page

{

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

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

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

{

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

DataGrid1.DataSource = CreateDataSource();

DataGrid1.DataBind();

}

/// <summary>

/// 创建数据源

/// </summary>

/// <returns>DataView</returns>

ICollection CreateDataSource()

{

DataTable dt = new DataTable();

DataRow dr;

dt.Columns.Add(new DataColumn("身份证号码", typeof(string)));

dt.Columns.Add(new DataColumn("图书单价", typeof(decimal)));

dt.Columns.Add(new DataColumn("购买数量", typeof(Int32)));

dt.Columns.Add(new DataColumn("总价格", typeof(decimal)));

for (int i = 0; i < 30; i++)

{

dr = dt.NewRow();

dr[0] = "123456789123456789";

dr[1] = 100 * i / 3.0;

dr[2] = i + 5;

dr[3] = (decimal) dr[1] * (Int32) dr[2];

dt.Rows.Add(dr);

}

DataView dv = new DataView(dt);

return dv;

}

/// <summary>

/// 输出到Excel

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

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

{

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);

this.DataGrid1.RenderControl(oHtmlTextWriter);

Response.Write(oStringWriter.ToString());

Response.End();

}

Web 窗体设计器生成的代码

private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)

{

if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)

{

e.Item.Cells[0].Attributes.Add("style", "vnd.ms-excel.numberformat:@");

e.Item.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:￥#,###.00");

}