asp.net导出excel科学计数问题
方法一
Code
在asp.net 中 我一般都是将要导出的数据放到gridview网格里，首先对网格邦定数据时 字符串形式处理，然后再用普通的形式导出excel就把问题解决了。
我的代码非常简单：在邦定gridview控件时在rowdatabound事件中队数据格式化
protected void gError_RowDataBound(object sender, GridViewRowEventArgs e)
{
//1） 文本：vnd.ms-excel.numberformat:@
//2） 日期：vnd.ms-excel.numberformat:yyyy/mm/dd
//3） 数字：vnd.ms-excel.numberformat:#,##0.00
//4） 货币：vnd.ms-excel.numberformat:￥#,##0.00
//5） 百分比：vnd.ms-excel.numberformat: #0.00%
for (int i = 0; i < e.Row.Cells.Count; i++)
{
if (e.Row.RowType == DataControlRowType.DataRow)
e.Row.Cells[i ].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
}
}
然后执行到处操作就不会出现问题了
protected void btnOut_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
Response.ContentEncoding = System.Text.Encoding.UTF7;
//设置输出文件类型为excel文件。
Response.ContentType = "application/ms-excel";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.gError.RenderControl(oHtmlTextWriter);
Response.Output.Write(oStringWriter.ToString());
Response.Flush();
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
在winform程序开发时，处理的办法就是在导出的过程中，开始试了 处理excel对象的格式 mysheet.Cells.NumberFormat = "#";
后来没有成功。最后还是用了逐条纪录进行字符格式转化的方法，即添加“ ' ”.
我写得代码主要部分如下
执行数据导出#region 执行数据导出
try
{
//到导出excel
Excel.ApplicationClass my = new Excel.ApplicationClass();
if (my == null)
{
MessageBox.Show("无法创建excel对象，可能您的系统没有安装excel");
return;
}
my.Visible = false;
Excel.Workbook mybook = (Excel.Workbook)my.Workbooks.Add(1);
((Excel.Worksheet)mybook.Worksheets[1]).Name = "sheet1";
Excel.Worksheet mysheet = (Excel.Worksheet)mybook.Worksheets[1];
// mysheet.Cells.NumberFormat = "#";
//导出列名
for (int j = 0; j < this.dgvShow.Columns.Count; j++)
{
if (this.dgvShow.Columns[j].Visible == true)
{
mysheet.Cells[1, j + 1] = "'" + Convert.ToString(this.dgvShow.Columns[j].HeaderText);//加"'"防止科 学计数法
}
}
//导出数据
for (int i = 0; i < this.dgvShow.Rows.Count; i++)
{
for (int j = 0; j < this.dgvShow.Columns.Count; j++)
{
mysheet.Cells[i + 2, j + 1] = "'" + Convert.ToString(this.dgvShow.Rows[i].Cells[j].Value);
}
}
if (savefilename != "")
{
try
{
//mybook.Save();
mybook.SaveCopyAs(savefilename);
MessageBox.Show("excel文件导出成功！");
}
catch (Exception ex)
{
MessageBox.Show("导出文件出现错误，文件可能正被打开！\n" + ex.Message);
}
}
GC.Collect();
}
catch (Exception ex)
{
MessageBox.Show("数据导出时出现错误，一下是详细错误信息：\n" + ex.Message);
return;
}
#endregion
方法二
using System;
2using System.Collections;
3using System.ComponentModel;
4using System.Data;
5using System.Drawing;
6using System.Web;
7using System.Web.SessionState;
8using System.Web.UI;
9using System.Web.UI.WebControls;
10using System.Web.UI.HtmlControls;
11
12namespace eMeng.Exam
13{
14/**//// <summary>
15/// OutPutExcel 的摘要说明。
16/// </summary>
17public class OutPutExcel : System.Web.UI.Page
18{
19protected System.Web.UI.WebControls.Button Button1;
20protected System.Web.UI.WebControls.DataGrid DataGrid1;
21
22private void Page_Load(object sender, System.EventArgs e)
23{
24// 在此处放置用户代码以初始化页面
25DataGrid1.DataSource=CreateDataSource();
26DataGrid1.DataBind();
27}
28/**//// <summary>
29/// 创建数据源
30/// </summary>
31/// <returns>DataView</returns>
32ICollection CreateDataSource()
33{
34
35DataTable dt = new DataTable();
36DataRow dr;
37dt.Columns.Add(new DataColumn("身份证号码", typeof(string)));
38dt.Columns.Add(new DataColumn("图书单价",typeof(decimal)));
39dt.Columns.Add(new DataColumn("购买数量",typeof(Int32)));
40dt.Columns.Add(new DataColumn("总价格",typeof(decimal)));
41
42
43for (int i = 0; i < 30; i++)
44{
45dr = dt.NewRow();
46
47dr[0] = "123456789123456789";
48dr[1] = 100 * i /3.0;
49dr[2] = i + 5;
50dr[3] = (decimal)dr[1] * (Int32)dr[2];
51dt.Rows.Add(dr);
52}
53DataView dv = new DataView(dt);
54return dv;
55}
56/**//// <summary>
57/// 输出到Excel
58/// </summary>
59/// <param name="sender"></param>
60/// <param name="e"></param>
61private void Button1_Click(object sender, System.EventArgs e)
62{
63Response.Clear();
64Response.Buffer= true;
65Response.Charset="GB2312";
66Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls");
67Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
68Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
69this.EnableViewState = false;
70System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
71System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
72System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
73this.DataGrid1.RenderControl(oHtmlTextWriter);
74Response.Write(oStringWriter.ToString());
75Response.End();
76}
77
78Web 窗体设计器生成的代码#region Web 窗体设计器生成的代码
79override protected void OnInit(EventArgs e)
80{
81//
82// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
83//
84InitializeComponent();
85base.OnInit(e);
86}
87
88/**//// <summary>
89/// 设计器支持所需的方法 - 不要使用代码编辑器修改
90/// 此方法的内容。
91/// </summary>
92private void InitializeComponent()
93{
94this.Button1.Click += new System.EventHandler(this.Button1_Click);
95this.DataGrid1.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.DataGrid1_ItemDataBound);
96this.Load += new System.EventHandler(this.Page_Load);
97
98}
99#endregion
100
101private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
102{
103if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
104{
105e.Item.Cells[0].Attributes.Add("style","vnd.ms-excel.numberformat:@");
106e.Item.Cells[3].Attributes.Add("style","vnd.ms-excel.numberformat:￥#,###.00");
107}
108}
109}
110}
方法三(通过CSS)
<style type="text/css">
<!--
td {
background-color: #FFFFFF;
}
.txt
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:宋体;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:"\@";
text-align:general;
vertical-align:middle;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
-->
</style>
