Imports System.IO

Public Class ExcelImport

    Public Sub FlowHeater_SetDataTable(ByRef dt As DataTable)

        If (dt.Rows.Count = 0) Then
            ' Testaufruf aus FlowHeater Designer oder nichts zu tun
            Exit Sub
        End If

        Dim excel As Object

        'Excel Object erzeugen
        excel = CreateObject("Excel.Application")
        'excel.Visible = True

        Dim filename As String
        filename = Path.Combine(System.Environment.CurrentDirectory, "Output.xls")

        ' Falls noch nicht vorhanden anlegen
        If File.Exists(filename) Then
            excel.Workbooks.Open(filename)
        Else
            excel.Workbooks.Add()
            excel.ActiveWorkbook.SaveAs(filename)
        End If

        Dim workbook As Object
        workbook = excel.ActiveWorkbook

        If workbook.Worksheets.Count() = 0 Then
            workbook.Worksheets.Add()
        End If

        Dim worksheet As Object
        worksheet = workbook.Worksheets(1)

        Dim nRow As Integer
        nRow = 1

        ' 1. freie Zeile suchen
        Do While True
            Dim range As Object
            range = worksheet.Cells(nRow, 1)

            Dim cellValue As String
            cellValue = String.Empty

            If range.Value2 <> Nothing Then
                cellValue = range.Value2.ToString()
            End If

            If cellValue.Length() = 0 Then
                Exit Do
            End If

            nRow = nRow + 1
        Loop


        Dim dr As DataRow
        For Each dr In dt.Rows

            Dim col As DataColumn
            Dim nCol As Integer
            nCol = 1

            For Each col In dt.Columns
                worksheet.Cells(nRow, nCol) = dr(col)
                nCol = nCol + 1
            Next col

            nRow = nRow + 1
        Next dr

        workbook.Save()
        excel.Quit()

    End Sub

End Class
