在VB.NET中使用或不使用LINQ在DataTable中格式化数据[英] Format Data In DataTable With or Without LINQ in VB.NET

本文是小编为大家收集整理的关于在VB.NET中使用或不使用LINQ在DataTable中格式化数据的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我在尝试正确格式化一些数据时遇到了一些麻烦.

我不确定 LINQ 在这种情况下是否有用,但以下是:

预期输出

March 14
2:00 PM - 7:00 PM | Registration
5:30 PM - 7:00 PM | Meetup
7:30 PM - 9:00 PM | Dinner
March 15
2:00 PM - 7:00 PM | Registration
5:30 PM - 7:00 PM | Meetup

当前数据输出

March 14
2:00 PM - 7:00 PM | Registration
March 14
5:30 PM - 7:00 PM | Meetup
March 14
7:30 PM - 9:00 PM | Dinner
March 15
2:00 PM - 7:00 PM | Registration
March 15
6:00 PM - 7:00 PM | Meetup

代码结构

Imports System
Imports System.Globalization
Imports System.Data
Imports System.Data.DataSetExtensions

Public Module Module1

    Public Sub Main()
        Dim sessions = SampleTable()
        Dim ci = CultureInfo.CreateSpecificCulture("en-us")
        For Each session In sessions.AsEnumerable()

            Dim sessionID = session.Item("SessionID")
            Dim sessionDate = CDate(session.Item("SessionStartTime")).ToString("M", ci)
            Dim sessionStart = CDate(session.Item("SessionStartTime")).ToString("t", ci)
            Dim sessionEnd = CDate(session.Item("SessionEndTime")).ToString("t", ci)
            Dim sessionTitle = CStr(session.Item("SessionName"))

            Console.WriteLine("{0}", sessionDate)
            Console.WriteLine("{0} - {1} | {2}", sessionStart, sessionEnd, sessionTitle)
        Next
    End Sub

    Public Function SampleTable() As DataTable
        Dim table As New DataTable
        With table
            'Add Named Columns
            .Columns.Add("SessionID", GetType(Integer))
            .Columns.Add("SessionStartTime", GetType(Date))
            .Columns.Add("SessionEndTime", GetType(Date))
            .Columns.Add("SessionName", GetType(String))

            'Add 5 Rows of Data to test
            .Rows.Add(1, #03/14/2019 14:00:00#, #03/14/2019 19:00:00#, "Registration")
            .Rows.Add(1, #03/14/2019 17:30:00#, #03/14/2019 19:00:00#, "Meetup")
            .Rows.Add(1, #03/14/2019 19:30:00#, #03/14/2019 21:00:00#, "Dinner")
            .Rows.Add(2, #03/15/2019 14:00:00#, #03/15/2019 19:00:00#, "Registration")
            .Rows.Add(2, #03/15/2019 18:00:00#, #03/15/2019 19:00:00#, "Meetup")
        End With
        Return table
    End Function
End Module

我参考了其他 SO 问题,例如 THIS 但我不确定是否LINQ 将使预期的输出更容易获得.

欢迎提出任何建议.我正在尝试为每个循环添加一个嵌套的循环,然后在 sessionDate 相同时添加一个条件.

****** 更新 *******

我非常感谢您的反馈和有用的答案.我正在尝试对数据进行另一种格式更改.我必须为所有事件项添加一个特定的 SVG 图标.SVG 图标特定于 First、Last、Rest 事件.

我正在尝试使用提供的 LINQ 答案,但我还不精通使用它,但它看起来确实很容易阅读.就本示例而言,String 变量可用作 first/rest/last 的占位符.

我还尝试添加图标,但我没有使用 LINQ.

以下是更新代码和预期输出

预期输出

March 14
(Start SVG) 2:00 PM - 7:00 PM | Registration
(Body SVG)  5:30 PM - 7:00 PM | Meetup
(End SVG)   7:30 PM - 9:00 PM | Dinner
March 15
(Start SVG) 2:00 PM - 7:00 PM | Registration
(End SVG)   5:30 PM - 7:00 PM | Meetup

代码结构

Imports System
Imports System.Globalization
Imports System.Data
Imports System.Data.DataSetExtensions

Public Module Module1

  Public Sub Main()
    Dim ci = CultureInfo.CreateSpecificCulture("en-us")
    Dim sessionIcon = String.Empty
    'Dim Sessions = SampleTable().AsEnumerable()

    'For Each session In Sessions
    '  If Sessions.Rows.IndexOf(session) = 0 Then
    '    sessionIcon = "<svg>Start Icon</svg>"
    '  ElseIf Sessions.Rows.IndexOf(session) = (Sessions.Rows.Count - 1) Then
    '   sessionIcon = "<svg>End Icon</svg>"
    '  Else
    '    sessionIcon = "<svg>Rest Icon</svg>"
    '  End If
    'Next

    Dim sessions = SampleTable().AsEnumerable().[Select](Function(x) New With {
        Key.SessionDate = x.Field(Of Date)("SessionStartTime").ToString("M", ci),
        Key.SessionStartTime = x.Field(Of Date)("SessionStartTime").ToString("t", ci),
        Key.SessionEndTime = x.Field(Of Date)("SessionEndTime").ToString("t", ci),
        Key.SessionName = x.Field(Of String)("SessionName")
    })

    For Each g In sessions.GroupBy(Function(s) s.SessionDate)
      Console.WriteLine("{0}", g.Key)
      For Each r In g
        Console.WriteLine("{0} - {1} | {2}", r.SessionStartTime, r.SessionEndTime, r.SessionName)
      Next
    Next
End Sub

Public Function SampleTable() As DataTable
  Dim table As New DataTable
    With table
      'Add Named Columns
      .Columns.Add("SessionID", GetType(Integer))
      .Columns.Add("SessionStartTime", GetType(Date))
      .Columns.Add("SessionEndTime", GetType(Date))
      .Columns.Add("SessionName", GetType(String))

    'Add 5 Rows of Data to test
    .Rows.Add(1, #03/14/2019 14:00:00#, #03/14/2019 19:00:00#, "Registration")
      .Rows.Add(1, #03/14/2019 17:30:00#, #03/14/2019 19:00:00#, "Meetup")
      .Rows.Add(1, #03/14/2019 19:30:00#, #03/14/2019 21:00:00#, "Dinner")
      .Rows.Add(2, #03/15/2019 14:00:00#, #03/15/2019 19:00:00#, "Registration")
      .Rows.Add(2, #03/15/2019 18:00:00#, #03/15/2019 19:00:00#, "Meetup")
    End With
  Return table
End Function
End Module

推荐答案

使用LINQ的时候,像这样

Public Sub Main()

    Dim ci = CultureInfo.CreateSpecificCulture("en-us")

    Dim sessions = SampleTable().AsEnumerable().[Select](Function(x) New With {
        Key .SessionDate = x.Field(Of Date)("SessionStartTime").ToString("M", ci),
        Key .SessionStartTime = x.Field(Of Date)("SessionStartTime").ToString("t", ci),
        Key .SessionEndTime = x.Field(Of Date)("SessionEndTime").ToString("t", ci),
        Key .SessionName = x.Field(Of String)("SessionName")
    })

    For Each g In sessions.GroupBy(Function(s) s.SessionDate)
        Console.WriteLine("{0}", g.Key)
        For Each r In g
            Console.WriteLine("{0} - {1} | {2}", r.SessionStartTime, r.SessionEndTime, r.SessionName)
        Next
    Next

End Sub

加法

基本上你的思维方式没有错.添加该代码的位置略有不同.因为您要处理按日期分组的每一行必须在那个循环中完成.

Public Sub Main()

    Dim ci = CultureInfo.CreateSpecificCulture("en-us")

    Dim sessions = SampleTable().AsEnumerable().[Select](Function(x) New With {
    Key .SessionDate = x.Field(Of Date)("SessionStartTime").ToString("M", ci),
    Key .SessionStartTime = x.Field(Of Date)("SessionStartTime").ToString("t", ci),
    Key .SessionEndTime = x.Field(Of Date)("SessionEndTime").ToString("t", ci),
    Key .SessionName = x.Field(Of String)("SessionName")
})

    For Each g In sessions.GroupBy(Function(s) s.SessionDate)
        Console.WriteLine("{0}", g.Key)
        For Each r In g
            Dim sessionIcon As String
            If r.Equals(g.First) Then
                sessionIcon = "<svg>Start Icon</svg>"
            ElseIf r.Equals(g.Last) Then
                sessionIcon = "<svg>End Icon</svg>"
            Else
                sessionIcon = "<svg>Body Icon</svg>"
            End If
            Console.WriteLine("{3} {0} - {1} | {2}", r.SessionStartTime, r.SessionEndTime, r.SessionName, sessionIcon)
        Next
    Next

End Sub

本文地址:https://www.itbaoku.cn/post/1557042.html

问题描述

I'm having a little trouble trying to get some data to format properly.

I'm not sure if LINQ is helpful in this case but below is:

Expected output

March 14
2:00 PM - 7:00 PM | Registration
5:30 PM - 7:00 PM | Meetup
7:30 PM - 9:00 PM | Dinner
March 15
2:00 PM - 7:00 PM | Registration
5:30 PM - 7:00 PM | Meetup

Current data output

March 14
2:00 PM - 7:00 PM | Registration
March 14
5:30 PM - 7:00 PM | Meetup
March 14
7:30 PM - 9:00 PM | Dinner
March 15
2:00 PM - 7:00 PM | Registration
March 15
6:00 PM - 7:00 PM | Meetup

Code Structure

Imports System
Imports System.Globalization
Imports System.Data
Imports System.Data.DataSetExtensions

Public Module Module1

    Public Sub Main()
        Dim sessions = SampleTable()
        Dim ci = CultureInfo.CreateSpecificCulture("en-us")
        For Each session In sessions.AsEnumerable()

            Dim sessionID = session.Item("SessionID")
            Dim sessionDate = CDate(session.Item("SessionStartTime")).ToString("M", ci)
            Dim sessionStart = CDate(session.Item("SessionStartTime")).ToString("t", ci)
            Dim sessionEnd = CDate(session.Item("SessionEndTime")).ToString("t", ci)
            Dim sessionTitle = CStr(session.Item("SessionName"))

            Console.WriteLine("{0}", sessionDate)
            Console.WriteLine("{0} - {1} | {2}", sessionStart, sessionEnd, sessionTitle)
        Next
    End Sub

    Public Function SampleTable() As DataTable
        Dim table As New DataTable
        With table
            'Add Named Columns
            .Columns.Add("SessionID", GetType(Integer))
            .Columns.Add("SessionStartTime", GetType(Date))
            .Columns.Add("SessionEndTime", GetType(Date))
            .Columns.Add("SessionName", GetType(String))

            'Add 5 Rows of Data to test
            .Rows.Add(1, #03/14/2019 14:00:00#, #03/14/2019 19:00:00#, "Registration")
            .Rows.Add(1, #03/14/2019 17:30:00#, #03/14/2019 19:00:00#, "Meetup")
            .Rows.Add(1, #03/14/2019 19:30:00#, #03/14/2019 21:00:00#, "Dinner")
            .Rows.Add(2, #03/15/2019 14:00:00#, #03/15/2019 19:00:00#, "Registration")
            .Rows.Add(2, #03/15/2019 18:00:00#, #03/15/2019 19:00:00#, "Meetup")
        End With
        Return table
    End Function
End Module

I've referenced other SO questions such as THIS but I'm not sure if LINQ would make the expected output easier to obtain.

Any suggestions are welcomed. I'm trying to possibly add a nested for Each loop and then add a condition if the sessionDate is the same.

****** UPDATE *******

I really appreciate the feedback and helpful answers. I'm trying to do one more format change to the data. I have to add a specific SVG Icon to all the events items. The SVG icons are specific to the First, Last, Rest of events.

I'm trying to use the LINQ answer provided but I'm not well versed in using it yet but it does seem very easy to read. For the purposes of this example, A String variable can be used as place holders for the first / rest / last.

I've also added my attempt to try to add the icon but I was not using LINQ.

Below is the Updated Code and Expected Output

EXPECTED OUTPUT

March 14
(Start SVG) 2:00 PM - 7:00 PM | Registration
(Body SVG)  5:30 PM - 7:00 PM | Meetup
(End SVG)   7:30 PM - 9:00 PM | Dinner
March 15
(Start SVG) 2:00 PM - 7:00 PM | Registration
(End SVG)   5:30 PM - 7:00 PM | Meetup

Code Structure

Imports System
Imports System.Globalization
Imports System.Data
Imports System.Data.DataSetExtensions

Public Module Module1

  Public Sub Main()
    Dim ci = CultureInfo.CreateSpecificCulture("en-us")
    Dim sessionIcon = String.Empty
    'Dim Sessions = SampleTable().AsEnumerable()

    'For Each session In Sessions
    '  If Sessions.Rows.IndexOf(session) = 0 Then
    '    sessionIcon = "<svg>Start Icon</svg>"
    '  ElseIf Sessions.Rows.IndexOf(session) = (Sessions.Rows.Count - 1) Then
    '   sessionIcon = "<svg>End Icon</svg>"
    '  Else
    '    sessionIcon = "<svg>Rest Icon</svg>"
    '  End If
    'Next

    Dim sessions = SampleTable().AsEnumerable().[Select](Function(x) New With {
        Key.SessionDate = x.Field(Of Date)("SessionStartTime").ToString("M", ci),
        Key.SessionStartTime = x.Field(Of Date)("SessionStartTime").ToString("t", ci),
        Key.SessionEndTime = x.Field(Of Date)("SessionEndTime").ToString("t", ci),
        Key.SessionName = x.Field(Of String)("SessionName")
    })

    For Each g In sessions.GroupBy(Function(s) s.SessionDate)
      Console.WriteLine("{0}", g.Key)
      For Each r In g
        Console.WriteLine("{0} - {1} | {2}", r.SessionStartTime, r.SessionEndTime, r.SessionName)
      Next
    Next
End Sub

Public Function SampleTable() As DataTable
  Dim table As New DataTable
    With table
      'Add Named Columns
      .Columns.Add("SessionID", GetType(Integer))
      .Columns.Add("SessionStartTime", GetType(Date))
      .Columns.Add("SessionEndTime", GetType(Date))
      .Columns.Add("SessionName", GetType(String))

    'Add 5 Rows of Data to test
    .Rows.Add(1, #03/14/2019 14:00:00#, #03/14/2019 19:00:00#, "Registration")
      .Rows.Add(1, #03/14/2019 17:30:00#, #03/14/2019 19:00:00#, "Meetup")
      .Rows.Add(1, #03/14/2019 19:30:00#, #03/14/2019 21:00:00#, "Dinner")
      .Rows.Add(2, #03/15/2019 14:00:00#, #03/15/2019 19:00:00#, "Registration")
      .Rows.Add(2, #03/15/2019 18:00:00#, #03/15/2019 19:00:00#, "Meetup")
    End With
  Return table
End Function
End Module

推荐答案

When using LINQ, like this

Public Sub Main()

    Dim ci = CultureInfo.CreateSpecificCulture("en-us")

    Dim sessions = SampleTable().AsEnumerable().[Select](Function(x) New With {
        Key .SessionDate = x.Field(Of Date)("SessionStartTime").ToString("M", ci),
        Key .SessionStartTime = x.Field(Of Date)("SessionStartTime").ToString("t", ci),
        Key .SessionEndTime = x.Field(Of Date)("SessionEndTime").ToString("t", ci),
        Key .SessionName = x.Field(Of String)("SessionName")
    })

    For Each g In sessions.GroupBy(Function(s) s.SessionDate)
        Console.WriteLine("{0}", g.Key)
        For Each r In g
            Console.WriteLine("{0} - {1} | {2}", r.SessionStartTime, r.SessionEndTime, r.SessionName)
        Next
    Next

End Sub

Addition

Basically it is not wrong with your way of thinking. The position to add that code is slightly different. Because you want to process each row grouped by date It must be done in that loop.

Public Sub Main()

    Dim ci = CultureInfo.CreateSpecificCulture("en-us")

    Dim sessions = SampleTable().AsEnumerable().[Select](Function(x) New With {
    Key .SessionDate = x.Field(Of Date)("SessionStartTime").ToString("M", ci),
    Key .SessionStartTime = x.Field(Of Date)("SessionStartTime").ToString("t", ci),
    Key .SessionEndTime = x.Field(Of Date)("SessionEndTime").ToString("t", ci),
    Key .SessionName = x.Field(Of String)("SessionName")
})

    For Each g In sessions.GroupBy(Function(s) s.SessionDate)
        Console.WriteLine("{0}", g.Key)
        For Each r In g
            Dim sessionIcon As String
            If r.Equals(g.First) Then
                sessionIcon = "<svg>Start Icon</svg>"
            ElseIf r.Equals(g.Last) Then
                sessionIcon = "<svg>End Icon</svg>"
            Else
                sessionIcon = "<svg>Body Icon</svg>"
            End If
            Console.WriteLine("{3} {0} - {1} | {2}", r.SessionStartTime, r.SessionEndTime, r.SessionName, sessionIcon)
        Next
    Next

End Sub