Public Class ExcelTools

  ''' <summary>
  ''' Convert a Excel File to a Dataset
  ''' </summary>
  ''' <param name="aFilename">Filename of the Excel file</param>
  ''' <param name="HasHeaders">Are there column-headers in the excel-file?</param>
  ''' <returns>DataSet</returns>
  Public Shared Function GetExcelDataSet(ByVal aFilename As String, ByVal HasHeaders As Boolean) As DataSet

    Dim ds As New DataSet()
    Dim con As New OleDb.OleDbConnection("Data Source=" & aFilename & ";Provider=Microsoft.Jet.OLEDB.4.0;")

    If HasHeaders Then
      con.ConnectionString += "Extended Properties=""Excel 8.0;HDR=Yes"""
    Else
      con.ConnectionString += "Extended Properties=""Excel 8.0;HDR=No"""
    End If

    Try
      con.Open()
    Catch ex As Exception
      Return Nothing
    End Try

    Dim sheets As DataTable = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

    For Each sheet As DataRow In sheets.Rows

      Dim strTableName As String = sheet("Table_Name").ToString()
      Dim strSQL As String = "SELECT * FROM [" & strTableName & "]"
      Dim da As New OleDb.OleDbDataAdapter(strSQL, con)

      Try
        da.Fill(ds, strTableName)
      Catch ex As Exception
      End Try

    Next

    con.Close()
    Return ds

  End Function

  ''' <summary>
  ''' Export a DataTable into an Excel Datasheet
  ''' </summary>
  ''' <param name="aDataTable"></param>
  ''' <param name="aOutputFilename"></param>
  ''' <returns></returns>
  ''' <remarks></remarks>
  Public Shared Function DatatableToExcel(ByVal aDataTable As DataTable, ByVal aOutputFilename As String) As Boolean

    Dim app As New Excel.ApplicationClass
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet

    wb = app.Workbooks.Add()
    ws = wb.ActiveSheet()

    Dim dc As DataColumn
    Dim dr As DataRow
    Dim colIndex As Integer
    Dim rowIndex As Integer

    ' Columns erstellen
    For Each dc In aDataTable.Columns
      colIndex += 1
      app.Cells(1, colIndex) = dc.ColumnName
    Next

    ' Rows hinzufügen
    For Each dr In aDataTable.Rows

      rowIndex += 1
      colIndex = 0

      Try
        For Each dc In aDataTable.Columns
          colIndex += 1
          app.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
        Next
      Catch ex As Exception
      End Try


    Next

    ws.Columns.AutoFit()

    wb.SaveAs(aOutputFilename)
	
    wb.Close()
    app.Quit()
	
	wp = nothing
	System.Runtime.InteropServices.Marshal.ReleaseComObject(wp)
	app = nothing
	System.Runtime.InteropServices.Marshal.ReleaseComObject(app)

	GC.Collect()
	GC.WaitForPendingFinalizers()

    Return True

  End Function

End Class