我正在使用c#使用gridview和boundfields编程生成excel文件。现在,它不断将某些值转换为日期,而不是将其作为纯文本。
我希望他们留下来,因为他们从DB返回,而没有Excel的转换。我已经探索了几个选择,但似乎无法解决此转换。
这直接进入文件。
与此问题的边界是" ReportNumber"。谢谢大家提供的任何帮助。
代码段:
[HttpGet]
public ActionResult AFDOJClosed2Excel(byte[] Data, string Reporting_Year)
{
try
{
DataTable dt = GetAFDOJClosed(Reporting_Year);//your datatable
DataView dv = dt.DefaultView;
dv.Sort = "Case_Number ASC";
DataTable sortedDT = dv.ToTable();
dt = sortedDT;
GridView excel = new GridView();
excel.DataSource = dt;
excel.AutoGenerateColumns = false;
//Explicitly Create bound fields
BoundField CaseNumber = new BoundField();
CaseNumber.HeaderText = "Case Number";
CaseNumber.DataField = "Case_Number";
excel.Columns.Add(CaseNumber);
BoundField Docket = new BoundField();
Docket.HeaderText = "Docket";
Docket.DataField = "Docket";
excel.Columns.Add(Docket);
BoundField AgencyName = new BoundField();
AgencyName.HeaderText = "Agency Name";
AgencyName.DataField = "Agency_Name";
excel.Columns.Add(AgencyName);
BoundField ReportNumber = new BoundField();
ReportNumber.HeaderText = "Report Number";
ReportNumber.DataField = "Report_Number";
excel.Columns.Add(ReportNumber);
BoundField ClosedDate = new BoundField();
ClosedDate.HeaderText = "Closed Date";
ClosedDate.DataField = "Closed_Date";
ClosedDate.DataFormatString = "{0:d}";
excel.Columns.Add(ClosedDate);
BoundField TotalDisbursed = new BoundField();
TotalDisbursed.HeaderText = "Total Disbursed";
TotalDisbursed.DataField = "Total_Disbursed";
TotalDisbursed.DataFormatString = "{0:C}";
excel.Columns.Add(TotalDisbursed);
excel.DataBind();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=AFDOJClosed" + Reporting_Year + ".xls");
Response.ContentType = "application/ms-excel";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
excel.RenderControl(htw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
return null;
}
catch (Exception ex)
{
ViewBag.ErrorMessage = ex.Message.ToString();
return View("Error");
}
}
excel如果没有特定的数据/格式元元(例如,格式为文本,编号,日期/时间等),则根据单元格的内容应用自己的格式规则)。
您的代码采用GridView
,并有效地将网格数据直接写入响应流。就生成的文件而言,日期位于"常规"类型的单元格中,Excel将应用默认格式规则。
为了控制与单元格关联的数据类型,您需要更明确地使用与Excel数据交互的库,而不是依靠上面代码的转换机制。Nuget中相当直接的库是我们使用的epplus。
要将特定日期格式应用于单元格,您可以使用以下内容;
ws.Cells["A3"].Style.Numberformat.Format = "yyyy-mm-dd";