导出到excel会丢失日期格式
我正在将 SP 的内容导出到 Excel。其中一列的日期格式为 08/2015,但导出到 Excel 时,格式会更改为 Aug-2015。
我对同样的内容进行了谷歌搜索,发现包含以下代码就可以解决问题;
string style = @"<style> .text { mso-number-format:\@; } </style> ";
导出到 Excel(数据集到 Excel)的工作原理如下;
/// <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();
}
我在哪里犯了错误?请指导!
谢谢!
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> ";
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!
Thanks!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
发布评论
评论(3)
往事风中埋2024-12-17 05:04:36
这是一些示例代码。
Response.AddHeader("content-disposition", "attachment; filename=Report.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
DataGrid g = new DataGrid();
DataTable d = new System.Data.DataTable();
d = (DataTable)Session["ReportData"];
g.DataSource = d;
g.DataBind();
foreach (DataGridItem i in g.Items)
{
foreach (TableCell tc in i.Cells)
tc.Attributes.Add("class", "text");
}
g.RenderControl(htmlWrite);
string style = @"<style> .text { mso-number-format:\@; } </style> ";
Response.Write(style);
Response.Write(stringWrite.ToString());
Response.End();
~没有更多了~
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
我不太了解代码的一部分(对 asp.net 不太熟悉),但我会说,如果您想在 Excel 工作表中强制使用文本,则需要先将目标区域定义为文本,然后再将数据放入其中。
如果我对代码的理解是正确的:
需要在此之前。
编辑:也许是替代解决方案
您找到的谷歌代码将单元格的格式设置为文本。您很可能希望 Excel 将日期视为显示格式为 MM/YYYY 的日期。
也许尝试将其替换为:
我
不确定 / 或 \ 是否是 ASP.net 中的转义字符,因此确切的 snytax 可能会有所不同。在 Excel 术语中,数字格式 @ 表示文本,mm/yyyy 表示具有您想要的显示格式的日期。
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:
Needs to be before this.
Edit: Perhaps an alternate solution
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.
maybe try replacing this:
with
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.