导出到Excel丢失的日期格式 [英] Exporting to excel loses the date format
问题描述
我出口SP的内容脱颖而出。其中一列带来的日期格式08/2015但导出到Excel时,格式得到改变至8月 - 2015年。
I am exporting the contents of SP to excel. One of the columns brings the date format as 08/2015 but when exporting to excel, the format gets changed to Aug-2015.
我做了同样的一个谷歌,发现包括以下code的伎俩;
I did a google on the same and found that including the below code does the trick;
string style = @"<style> .text { mso-number-format:\@; } </style> ";
创先争优(数据集到excel)出口下面的作品;
The exporting to excel (dataset to excel) works below;
/// <summary>
/// This method can be used for exporting data to excel from dataset
/// </summary>
/// <param name="dgrExport">System.Data.DataSet</param>
/// <param name="response">System.Web.Httpresponse</param>
public static void DataSetToExcel(System.Data.DataSet dtExport, System.Web.HttpResponse response, string strFileName)
{
string style = @"<style> .text { mso-number-format:\@; } </style> ";
//Clean up the response Object
response.Clear();
response.Charset = "";
//Set the respomse MIME type to excel
response.ContentType = "application/vnd.ms-excel";
//Opens the attachment in new window
response.AddHeader("Content-Disposition", "attachment; filename=" + strFileName.ToString() + ".xls;");
response.ContentEncoding = Encoding.Unicode;
response.BinaryWrite(Encoding.Unicode.GetPreamble());
//Create a string writer
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
//Create an htmltextwriter which uses the stringwriter
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
//Instantiate the datagrid
System.Web.UI.WebControls.GridView dgrExport = new System.Web.UI.WebControls.GridView();
//Set input datagrid to dataset table
dgrExport.DataSource = dtExport.Tables[0];
//bind the data with datagrid
dgrExport.DataBind();
//Make header text bold
dgrExport.HeaderStyle.Font.Bold = true;
//bind the modified datagrid
dgrExport.DataBind();
//Tell the datagrid to render itself to our htmltextwriter
dgrExport.RenderControl(htmlWrite);
response.Write(style);
//Output the HTML
response.Write(stringWrite.ToString());
response.End();
}
我在哪里犯了一个错误?请指导!
Where am i making a mistake? please guide!
谢谢!
推荐答案
我真的不明白code(asp.net中不流利)公平一点,但我会说,如果要强制文本Excel工作表你需要把你的数据在那里之前定义的目标区域为文本。
I don't really understand a fair bit of the code (not fluent in asp.net) but I will say that if you want to force text in an excel sheet you need to define the target area as text before putting your data in there.
如果我的code的理解是正确的:
If my understanding of the code is correct this:
response.Write(style);
必须是在此之前。
Needs to be before this.
dgrExport.RenderControl(htmlWrite);
编辑:也许一个替代的解决方案。
Perhaps an alternate solution
您已经找到了谷歌code位设置细胞作为文本的格式。在所有的情形产生你想要脱颖而出治疗的日期为具有MM / YYYY的显示格式的日期。
The bit of google code you have found sets the format of the cells as text. In all likelyhood you want excel to treat the date as a date which has a display format of MM/YYYY.
也许尝试更换这样的:
string style = @"<style> .text { mso-number-format:\@; } </style> "
与
string style = @"<style> .text { mso-number-format:\mm/yyyy; } </style> "
我不知道/或\\是ASP.net一个转义字符,以便准确snytax可能会有所不同。在Excel计算的数字格式@也就是说,文本和MM / YYYY将意味着与你想要的显示格式的日期。
I am not sure if / or \ is an escape character in ASP.net so the exact snytax might be different. In excel terms number format @ means text and mm/yyyy will mean a date with the display format that you want.
这篇关于导出到Excel丢失的日期格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!