导出到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屋！