在打开的XML Excel中日期列设置不正确

Question

我试图复制excel表中的数据，但它没有正确显示，它显示如#######，但我想要17-09-2016这样。请建议我写什么代码，以适当的格式导出excel。

代码:

        var rngTable2 = ws.Range("A:G");
        var rngHeaders2 = rngTable2.Range("F4:G4");
        rngHeaders2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.General;
        rngHeaders2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Bottom;

Date来自以下代码:

Label lblpkgdate = (Label)gvvessel.Rows[j].FindControl("lblpackagedate");
string myVal1 = lblpkgdate.Text; 
ws.Cell("F" + index5.ToString("dd/MM/yyyy")).Value = myVal1; 
index5++;

Answer 1

最终，您似乎正在尝试从标签获取日期，然后将此值放入列F内某处的单元格负载中。我猜你在for循环中也有这个，因为你在增加index5。所以像这样的代码应该可以工作:

//Make column F a date column. Alter to a specific range if the whole column shouldn't be of date type.
Range rg = ws.Range("F:F");
rg.EntireColumn.NumberFormat = "DD/MM/YYYY";
var lblpkgdate = (Label).gvvessel.Rows[j].FindControl("lblpackagedate");
//Convert lblpkgdate text to DateTime object assuming format of dd/MM/yyyy to ensure it is actually a date.
DateTime pkgDate = DateTime.ParseExact(lblpkgdate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture);
for(int i = 1, i < YourMaxRowValue, i++)
{
    ws.Cell("F" + i).Value = pkgDate;
}

注意 -我已将index5更改为' I'，因为如果您正在循环，这将减少误导。我也改变了myVal1 pkgDate，因为我认为这是更有意义的。

Answer 2

你可以使用NumberFormat

  Label lblpkgdate = (Label)gvvessel.Rows[j].FindControl("lblpackagedate"); 
     string myVal1 = lblpkgdate.Text;
     ws.Cell("F" + index5.ToString()).Style.NumberFormat.Format = "DD-MM-YYYY";
     ws.Cell("F" + index5.ToString()).Value = myVal1;
     index5++;