Excel gut aufgeräumt?

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

    Excel gut aufgeräumt?

    Hallo,

    ich schreibe Werte in eine bestehende Excel-Datei, dem Code von Microsoft traue ich nicht ganz, deswegen habe ich selbst das gebastelt, was mir zu dem Thema so einfällt.
    Da solche Sachen Excel lahmlegen können, macht mich das immer bissel nervös, daher wäre es toll wenn ihr da mal drüber gucken könntet. Der Test sah gut aus.

    Spoiler anzeigen

    VB.NET-Quellcode

    1. Private Sub ExportierenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExportierenToolStripMenuItem.Click
    2. If OpenFileDialog1.ShowDialog() = DialogResult.OK AndAlso OpenFileDialog1.FileName.EndsWith(".xlsx") Then
    3. Dim Excel As New ExcelHelper
    4. Try
    5. Excel.Export(OpenFileDialog1.FileName)
    6. Finally
    7. Excel.Dispose()
    8. End Try
    9. End If
    10. End Sub
    11. '
    12. Imports System.Runtime.InteropServices
    13. Imports Microsoft.Office.Interop
    15. Friend Class ExcelHelper
    16. Implements IDisposable
    17. Private disposedValue As Boolean
    18. Private _excel As Excel.Application
    19. Private _Books As Excel.Workbooks
    20. Private _Book As Excel.Workbook
    21. Private _Sheets As Excel.Sheets
    22. Private ReadOnly _WSheets As List(Of Excel.Worksheet)
    23. Private _Sheet As Excel.Worksheet
    24. Private ReadOnly _Ranges As List(Of Excel.Range)
    25. Private _Range As Excel.Range
    27. Friend Sub New()
    28. _excel = New Excel.Application()
    29. _WSheets = New List(Of Excel.Worksheet)
    30. _Ranges = New List(Of Excel.Range)
    31. End Sub
    33. Friend Sub Export(PathOfTemplate As String)
    34. _Books = _excel.Workbooks
    35. _Book = _Books.Open(PathOfTemplate)
    36. _Sheets = _Book.Worksheets
    37. For i = 1 To _Sheets.Count
    38. _WSheets.Add(DirectCast(_Sheets(i), Excel.Worksheet))
    39. Next
    40. ''Testblub
    41. _Ranges.Add(_WSheets(0).Range("A2:E6", Reflection.Missing.Value))
    42. Dim saRet(5, 5) As Double
    43. For iRow = 0 To 5
    44. For iCol = 0 To 5
    45. saRet(iRow, iCol) = iRow * iCol
    46. Next iCol
    47. Next iRow
    48. _Ranges(0).Value = saRet
    49. ''
    50. _Book.Close(SaveChanges:=True)
    51. _Books.Close()
    52. End Sub
    54. Protected Overridable Sub Dispose(ByVal disposing As Boolean)
    55. If Not disposedValue Then
    56. If disposing Then
    57. 'Managed Disposes here
    58. End If
    59. If _Range IsNot Nothing Then
    60. Marshal.FinalReleaseComObject(_Range)
    61. _Range = Nothing
    62. End If
    63. If _Ranges.Count > 0 Then
    64. For Each rng In _Ranges
    65. Marshal.FinalReleaseComObject(rng)
    66. Next
    67. _Ranges.Clear()
    68. End If
    69. If _Sheet IsNot Nothing Then
    70. Marshal.FinalReleaseComObject(_Sheet)
    71. _Sheet = Nothing
    72. End If
    73. If _WSheets.Count > 0 Then
    74. For Each sht In _WSheets
    75. Marshal.FinalReleaseComObject(sht)
    76. Next
    77. _WSheets.Clear()
    78. End If
    79. If _Sheets IsNot Nothing Then
    80. Marshal.FinalReleaseComObject(_Sheets)
    81. _Sheets = Nothing
    82. End If
    83. If _Book IsNot Nothing Then
    84. Marshal.FinalReleaseComObject(_Book)
    85. _Book = Nothing
    86. End If
    87. If _Books IsNot Nothing Then
    88. Marshal.FinalReleaseComObject(_Books)
    89. _Books = Nothing
    90. End If
    91. If _excel IsNot Nothing Then
    92. _excel.Quit()
    93. Marshal.FinalReleaseComObject(_excel)
    94. _excel = Nothing
    95. End If
    96. disposedValue = True
    97. End If
    98. End Sub
    100. Protected Overrides Sub Finalize()
    101. Dispose(disposing:=False)
    102. End Sub
    104. Friend Sub Dispose() Implements IDisposable.Dispose
    105. Dispose(disposing:=True)
    106. GC.SuppressFinalize(Me)
    107. End Sub
    108. End Class
    109. '


    Viele Grüße

Facebook
Telefonspende