从每列中搜索并删除“00:00”值

debugcn 发表于 Dev

李忠

我试图让我的代码通过每个 DateTime 列并搜索包含“00:00”作为小时和分钟的单元格。如果该值存在，则删除 00:00 并仅保留日期。对于不包含该值的单元格，将单元格保持原样。例如，如果“3/22/2017 00:00”，则将其格式化为“3/22/2017”。否则，如果是“3/22/2017 09:16”，请不要管它。先感谢您！

dataGrid.DataSource = dataSet.Tables(0)
dataGrid.DataBind()
ws.Cells(1, 1).LoadFromDataTable(dataGrid.DataSource, True)
Dim data = dataGrid.DataSource
Dim columnCount = data.Columns.Count
For i = 0 To columnCount - 1
    If data.Columns(i).DataType Is GetType(DateTime) Then
        If Not data.Columns(i).ToString.Contains("00:00") Then
            ws.Column(i + 1).Style.Numberformat.Format = "mm/dd/yyyy hh:mm"
        Else
            ws.Column(i + 1).Style.Numberformat.Format = "mm/dd/yyyy"
        End If
    End If
Next

给你

这可以通过以下方式实现：

为所有列设置默认格式DateTime
仅覆盖满足条件的值的默认格式。

这是一个处理多DateTime列的简单辅助方法：

Sub SetDateTimeStyles(ByRef data As DataTable, ByRef ws As ExcelWorksheet)
    ' Track DateTime columns to override default column style
    Dim dateTimeColumns = New List(Of Integer)()

    ' Set column format
    Dim columnCount = data.Columns.Count
    For i = 0 To columnCount - 1
        If data.Columns(i).DataType Is GetType(DateTime) Then
            Dim epPlusColumn = i + 1
            ws.Column(epPlusColumn).Style.Numberformat.Format = "mm/dd/yyyy hh:mm"
            dateTimeColumns.Add(epPlusColumn)
        End If
    Next

    ' Header row exists; set to 1 if no header row
    Dim rowOffset = 2
    Dim rowCount = data.Rows.Count

    ' Only set cell format when hour and minute are **both** zero
    For i = 0 To rowCount - 1
        For Each dateTimeColumn In dateTimeColumns
            Dim value As DateTime = data(i)(dateTimeColumn - 1)
            If value.Hour = 0 AndAlso value.Minute = 0 AndAlso value.Second = 0 Then
                ws.Cells(i + rowOffset, dateTimeColumn) _
                    .Style.Numberformat.Format = "mm/dd/yyyy"
            End If
        Next
    Next
End Sub

并在创建 Excel 文件时调用它：

dataGrid.DataSource = dataSet.Tables(0)
dataGrid.DataBind()
ws.Cells(1, 1).LoadFromDataTable(dataGrid.DataSource, True)
Dim data As DataTable = dataGrid.DataSource
SetDateTimeStyles(data, ws)

结果：