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
      2. 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
      4. Private ReadOnly _Order As Integer
      5. Public Sub New(ByVal Optional OrderNumber As Integer = 0)
      6. _Order = OrderNumber
      7. End Sub
      8. Public ReadOnly Property Order As Integer
      9. Get
      10. Return _Order
      11. End Get
      12. End Property
      13. End Class



      ElementName Attribut

      VB.NET-Quellcode

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


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