CSV Im-/Export

    • VB.NET
    • .NET (FX) 4.5–4.8

      CSV Im-/Export

      Hey Leute,

      ich habe gerade auf einen Post zum Thema CSV-Import geantwortet und gedacht, dass meine Antwort eventuell auch was für den Sourcecode-Austausch ist.

      Der folgende Code soll die Übertragung von Daten aus einer CSV-Datei in ein klassenbasiertes Datenmodel erleichtern. Zunächst schreiben wir uns eine Basisklasse. Die kann nichts, muss aber existieren.

      Basisklasse Entity

      VB.NET-Quellcode

      1. Public Class Base
      3. End Class



      Anschließend schreiben wir uns zwei Attribute. Unter Umständen kann man hier auch auf bereits vorhandene Attribute zurückgreifen. Zur Vervollständigung nehme ich sie dennoch mit auf.

      Order Attribut

      VB.NET-Quellcode

      1. <AttributeUsage(AttributeTargets.Property, Inherited:=False, AllowMultiple:=False)>
      2. Public NotInheritable Class OrderAttribute
      3. Inherits Attribute
      5. Private ReadOnly _Order As Integer
      7. Public Sub New(ByVal Optional OrderNumber As Integer = 0)
      8. _Order = OrderNumber
      9. End Sub
      11. Public ReadOnly Property Order As Integer
      12. Get
      13. Return _Order
      14. End Get
      15. End Property
      17. End Class



      ElementName Attribut

      VB.NET-Quellcode

      1. <AttributeUsage(AttributeTargets.Property, Inherited:=False, AllowMultiple:=False)>
      2. Public NotInheritable Class ElementNameAttribute
      3. Inherits Attribute
      5. Private ReadOnly _ElementName As String
      7. Public Sub New(ByVal Optional Name As String = "")
      8. _ElementName = Name
      9. End Sub
      11. Public ReadOnly Property ElementName As String
      12. Get
      13. Return _ElementName
      14. End Get
      15. End Property
      17. End Class



      Jetzt kommt der eigentliche Part, der die Logik enthält: Serialize ist die Exportmethode, Deserialize die Import-Methode

      Logic

      VB.NET-Quellcode

      1. Public Class Serializer(Of T As New)
      3. Public Shared Function Serialize(FileName As String, Data As T, HeaderVisible As Boolean, Optional Separator As Char = ";"c) As String
      4. Dim HeadlineWritten As Boolean = False
      5. Dim ExportText As String = String.Empty
      6. If Data.GetType().IsGenericType AndAlso TypeOf Data Is IEnumerable Then
      7. For Each elem In TryCast(Data, IEnumerable)
      8. Dim Properties = (From prop In elem.GetType().GetProperties()
      9. Where Attribute.IsDefined(prop, GetType(Entity.OrderAttribute))
      10. Order By TryCast(prop.GetCustomAttributes(GetType(Entity.OrderAttribute), False).FirstOrDefault(), Entity.OrderAttribute).Order
      11. Select prop).ToList()
      13. If HeaderVisible AndAlso Not HeadlineWritten Then
      14. HeadlineWritten = True
      15. add(ExportText, String.Join(Separator, Properties.Select(Function(n) TryCast(n.GetCustomAttributes(GetType(Entity.ElementNameAttribute), False).FirstOrDefault(), Entity.ElementNameAttribute).ElementName).ToList()))
      16. End If
      17. add(ExportText, String.Join(Separator, Properties.Select(Function(n) n.GetValue(elem)).ToList()))
      18. Next
      19. Else
      20. Dim Properties = (From prop In Data.GetType().GetProperties()
      21. Where Attribute.IsDefined(prop, GetType(Entity.OrderAttribute))
      22. Order By TryCast(prop.GetCustomAttributes(GetType(Entity.OrderAttribute), False).FirstOrDefault(), Entity.OrderAttribute).Order
      23. Select prop).ToList()
      25. If HeaderVisible AndAlso Not HeadlineWritten Then
      26. HeadlineWritten = True
      27. add(ExportText, String.Join(Separator, Properties.Select(Function(n) TryCast(n, Reflection.PropertyInfo).GetCustomAttributes(GetType(Entity.ElementNameAttribute), False).FirstOrDefault()).ToList()))
      28. End If
      29. add(ExportText, String.Join(Separator, Properties.Select(Function(n) n.GetValue(n)).ToList()))
      30. End If
      32. Dim ret As String = ExportText
      34. Using x As New StreamWriter(FileName, False, Text.Encoding.UTF8)
      35. x.WriteLine(ret)
      36. End Using
      38. Return ret
      39. End Function
      41. Public Shared Function Deserialize(FileName As String, SkipFirstLine As Boolean, Optional Separator As Char = ";"c) As List(Of T)
      42. Dim ret As String
      43. Dim FirstLineSkipped As Boolean = False
      44. Using x As New StreamReader(FileName, Text.Encoding.UTF8)
      45. ret = x.ReadToEnd()
      46. End Using
      48. Dim Properties As List(Of Reflection.PropertyInfo) = (From prop In GetType(T).GetProperties()
      49. Where Attribute.IsDefined(prop, GetType(Entity.OrderAttribute))
      50. Order By TryCast(prop.GetCustomAttributes(GetType(Entity.OrderAttribute), False).FirstOrDefault(), Entity.OrderAttribute).Order
      51. Select prop).ToList()
      53. Dim Liste As New List(Of T)
      54. Dim elem As New T
      55. Dim Lines As List(Of String) = ret.Split(Chr(10), Chr(13)).ToList()
      57. For Each Line In Lines
      58. If Not String.IsNullOrWhiteSpace(Line) Then
      59. If SkipFirstLine AndAlso Not FirstLineSkipped Then
      60. FirstLineSkipped = True
      61. Continue For
      62. End If
      63. Dim Values As List(Of String) = Line.Split(Separator).ToList()
      64. Dim Counter As Integer = 0
      66. For Each Proper In Properties
      67. Select Case Proper.PropertyType
      68. Case GetType(String)
      69. GetType(T).GetProperty(Proper.Name).SetValue(elem, Values(Counter).ToString)
      70. Case GetType(Char)
      71. GetType(T).GetProperty(Proper.Name).SetValue(elem, CChar(Values(Counter)))
      72. Case GetType(Short)
      73. GetType(T).GetProperty(Proper.Name).SetValue(elem, CShort(Values(Counter)))
      74. Case GetType(Integer)
      75. GetType(T).GetProperty(Proper.Name).SetValue(elem, CInt(Values(Counter)))
      76. Case GetType(Long)
      77. GetType(T).GetProperty(Proper.Name).SetValue(elem, CLng(Values(Counter)))
      78. Case GetType(Single)
      79. GetType(T).GetProperty(Proper.Name).SetValue(elem, CSng(Values(Counter)))
      80. Case GetType(Double)
      81. GetType(T).GetProperty(Proper.Name).SetValue(elem, CDbl(Values(Counter)))
      82. Case GetType(Decimal)
      83. GetType(T).GetProperty(Proper.Name).SetValue(elem, CDec(Values(Counter)))
      84. Case GetType(Boolean)
      85. GetType(T).GetProperty(Proper.Name).SetValue(elem, CBool(Values(Counter)))
      86. Case GetType(Date)
      87. GetType(T).GetProperty(Proper.Name).SetValue(elem, CDate(Values(Counter)))
      88. Case GetType(Nullable(Of Short))
      89. GetType(T).GetProperty(Proper.Name).SetValue(elem, If(Values(Counter) Is Nothing, CType(Nothing, Nullable(Of Short)), CShort(Values(Counter))))
      90. Case GetType(Nullable(Of Integer))
      91. GetType(T).GetProperty(Proper.Name).SetValue(elem, If(Values(Counter) Is Nothing, CType(Nothing, Nullable(Of Integer)), CInt(Values(Counter))))
      92. Case GetType(Nullable(Of Integer))
      93. GetType(T).GetProperty(Proper.Name).SetValue(elem, If(Values(Counter) Is Nothing, CType(Nothing, Nullable(Of Long)), CLng(Values(Counter))))
      94. Case GetType(Nullable(Of Single))
      95. GetType(T).GetProperty(Proper.Name).SetValue(elem, If(Values(Counter) Is Nothing, CType(Nothing, Nullable(Of Single)), CSng(Values(Counter))))
      96. Case GetType(Nullable(Of Double))
      97. GetType(T).GetProperty(Proper.Name).SetValue(elem, If(Values(Counter) Is Nothing, CType(Nothing, Nullable(Of Double)), CDbl(Values(Counter))))
      98. Case GetType(Nullable(Of Decimal))
      99. GetType(T).GetProperty(Proper.Name).SetValue(elem, If(Values(Counter) Is Nothing, CType(Nothing, Nullable(Of Decimal)), CDec(Values(Counter))))
      100. Case GetType(Nullable(Of Boolean))
      101. GetType(T).GetProperty(Proper.Name).SetValue(elem, If(Values(Counter) Is Nothing, CType(Nothing, Nullable(Of Boolean)), CBool(Values(Counter))))
      102. Case GetType(Nullable(Of Date))
      103. GetType(T).GetProperty(Proper.Name).SetValue(elem, If(Values(Counter) Is Nothing, CType(Nothing, Nullable(Of Date)), CDate(Values(Counter))))
      104. End Select
      106. Counter += 1
      107. Next
      109. Liste.Add(elem)
      110. elem = New T
      111. End If
      112. Next
      114. Return Liste
      115. End Function
      117. End Class



      Und anschließend legt ihr euch für jeden Importtyp eine Klasse an, die vom Basisdatenmodel erbt. Die Reihenfolge der Spalten in der CSV steuert ihr über das OrderAttribut, Spaltenüberschriften über das ElementName-Attribut.
      Ein DataModel würde dann beispielsweise so aussehen:

      ExampleClass

      VB.NET-Quellcode

      1. Public Class ExampleClass
      2. Inherits Base
      4. <Order(1), ElementName("Nr")>
      5. Public Property ID As Integer
      7. <Order(2), ElementName("Text")>
      8. Public Property Text As String
      10. <Order(3), ElementName("Name")>
      11. Public Property Name As String
      13. End Class



      Export zu CSV:
      Export CSV

      VB.NET-Quellcode

      1. Serializer(Of List(Of ExampleClass)).Serialize("C:\Temp\Testfile.csv",
      2. New List(Of ExampleClass) From
      3. {
      4. New ExampleClass With {.ID = 1, .Name = "Testname1", .Text = "TestText1"},
      5. New ExampleClass With {.ID = 2, .Name = "Testname2", .Text = "TestText2"},
      6. New ExampleClass With {.ID = 3, .Name = "Testname3", .Text = "TestText3"}
      7. }, False)



      Import CSV

      VB.NET-Quellcode

      1. Dim ExampleList As List(Of ExampleClass) = Serializer(Of ExampleClass).Deserialize("C:\Temp\Testfile.csv", False)

      Bilder

      • Ergebnis in Excel.PNG

        2,26 kB, 249×78, 413 mal angesehen

      Ein Computer wird das tun, was du programmierst - nicht das, was du willst.