导出到Excel时将数字转换为文本 [英] Convert number to text when exporting to excel

问题描述

我正在网格视图中显示我的数据(用逗号分隔的数字)，并且发生在需要时.但是，当我将其导出到excel时，该值会在显示方面发生变化

I am displaying my data(comma separated numbers) in a grid view, and it happens as needed. However, when I export it to excel, then the value is changed in terms of display

例如，我的值为901155465、978785496、987458986 然后显示为901,155,465,978,785,496,987,458,986

e.g my value is 901155465, 978785496, 987458986 Then it appears as 901,155,465,978,785,496,987,458,986

这就是我将数据集传递到Excel中的方式.我知道我们也可以呈现HTML，但是我只需要传输数据.

This is how I pass the data set into an excel. I know we can render the HTML also, but I needed to transfer the data only.

GridView GridView1 = new GridView();
GridView1.DataSource = myDataSet;
GridView1.DataBind();
string style = @" .text { mso-number-format:\@; }  "; 
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=Report.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
esponse.ContentType = "application/vnd.ms-excel";
System.IO.StringWriter s_Write = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter h_write = new HtmlTextWriter(s_Write);
GridView1.ShowHeader = true;
GridView1.RenderControl(h_write);
Response.Write(style); 
Response.Write(s_Write.ToString());
Response.End();

似乎excel将数字视为一个数字，并在适当的位置添加逗号.

It seems excel is treating the number as one number and adding comma at appropriate places.

有什么解决方案可以显示网格视图中显示的数据.

Is there any solution to display data as shown in gridview.

预先感谢

推荐答案

尝试一下:

_worksheet.Cells[1, 1] = "=\"" + YOUR_VALUE + "\"";

这就是我使用Interop.Excel的方式

that's how I did using Interop.Excel

Excel将忽略(=)，因为它会启动一个公式，并且双引号将指示excel使用该值作为字符串.

Excel will ignore the (=) since it starts a formula and the double quotes will tell excel to use that value as a String.

这篇关于导出到Excel时将数字转换为文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！