导出到 excel 丢失日期格式 [英] Exporting to excel loses the date format

问题描述

我正在将 SP 的内容导出到 excel.其中一列将日期格式设为 08/2015，但在导出到 excel 时，格式更改为 2015 年 8 月.

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.

我在同一个谷歌上做了一个谷歌，发现包含下面的代码可以解决问题；

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(数据集到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!

谢谢！

推荐答案

我不太了解代码(不熟悉 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.

如果我对代码的理解是正确的:

If my understanding of the code is correct this:

response.Write(style);

需要在此之前.

dgrExport.RenderControl(htmlWrite);

也许是另一种解决方案

您找到的谷歌代码将单元格的格式设置为文本.很可能您希望 excel 将日期视为显示格式为 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屋！