asp.net - 如何在导出的Excel中以 'dd MMM yyyy'格式显示数据表的日期字段

Question

我能够在 GridView 中显示dd MMM yyyy(2015 年 10 月 29 日)中的日期字段值。导出到 Excel 后，日期字段值格式更改为:dd-MMM-yy ( 29-Oct-15)。 我的代码:

Protected Sub btnExportData_Click(ByVal sender As System.Object, ByVal e As EventArgs) Handles btnExportData.ServerClick
    Try
        Dim str As String
        str = HiddenFilterString.Value
        Dim dtExportData As New DataTable
        Dim dtTemp As New DataTable
        Dim dg As New DataGrid
        dtTemp = DirectCast(Session("dtSoHoManageSecretServerFoldersExportData"), DataTable)

        dtExportData = dtTemp.Copy()
        Dim dv As New DataView(dtExportData)
        dg.DataSource = dv
        dg.DataBind()

        Dim sFileName As String = "ServerFolders.xls"
        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.ClearHeaders()
        HttpContext.Current.Response.ClearContent()
        HttpContext.Current.Response.Buffer = True
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" & sFileName)
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
        Me.EnableViewState = False
        Dim objSW As New System.IO.StringWriter
        Dim objHTW As New HtmlTextWriter(objSW)
        dg.HeaderStyle.Font.Bold = True
        dg.RenderControl(objHTW)
        HttpContext.Current.Response.Write(objSW.ToString())
        HttpContext.Current.Response.Flush()
        HttpContext.Current.Response.End()

        dtExportData = Nothing
        dtTemp = Nothing
        dg = Nothing
        dv = Nothing
        objSW = Nothing
        objHTW = Nothing
    Catch ex As Exception
        ReportInternalError("btnExportData_Click", ex)
        Throw
    End Try
End Sub

是否可以在导出的 Excel 中保留相同的 GridView 格式？

Answer 1

想法:

-VBA:Columns("E:E").NumberFormat = "dd MMM yyyy"

-Excel 公式:=TEXT(TODAY(),"dd MMM yyyy") 使用 VBA:

列("E:E").复制
Columns("E:E").Paste特殊粘贴:=xlPasteValuesAndNumberFormats，操作:= _
xlNone、SkipBlanks:=False、Transpose:=False

-将日期单元格导出为文本，如果第一个字符是 ' 撇号，Excel 始终将单元格读取为文本。