For i As Integer = 0 To columns.GetUpperBound(0) Step 1

For i As Integer = 0 To columns.GetUpperBound(4) Step 1

vb@rchiv iGoogle Gadget gibts hier:
http://www.google.de/ig/directory?q=vbarchiv&hl=de&root=%2Fig&dpos=top&url=gadgets.hemken.org/gadgets/vbarchiv/vbarchivnews.xml

Beitrag wurde zuletzt am 18.03.10 um 16:04:31 editiert.

Imports System.IO
Imports System.Data.OleDb
Function xls2Datatable(ByVal Dateiname As String, ByVal Blattname As String) As _
  DataTable
  If File.Exists(Dateiname) = False Then Return Nothing
 
  Dim FileInfo As FileInfo = New FileInfo(Dateiname)
  Dim DT As New DataTable
 
  Dim oConn As OleDbConnection = New OleDbConnection
  If Dateiname.Contains(".xlsx") Then
    oConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
      "Data Source=" & Dateiname & ";" & _
      "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  Else
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & Dateiname & ";" & _
      "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
  End If
  oConn.Open()
 
  Dim cmd As OleDbCommand
  cmd = New OleDbCommand("SELECT * FROM [" & Blattname & "$]", oConn)
 
  Dim reader As OleDbDataReader = cmd.ExecuteReader( _
    CommandBehavior.KeyInfo Or CommandBehavior.CloseConnection)
  Dim schema As DataTable = reader.GetSchemaTable()
  Dim columns(schema.Rows.Count - 1) As DataColumn
  Dim column As DataColumn
 
  ' Build the schema for the table that will contain the data.
  For i As Integer = 0 To columns.GetUpperBound(0) Step 1
    column = New DataColumn
    With column
      .AllowDBNull = CBool(schema.Rows(i)("AllowDBNull"))
      .AutoIncrement = CBool(schema.Rows(i)("IsAutoIncrement"))
      .ColumnName = CStr(schema.Rows(i)("ColumnName"))
      .DataType = CType(schema.Rows(i)("DataType"), Type)
 
      If .DataType Is GetType(String) Then
        .MaxLength = CInt(schema.Rows(i)("ColumnSize"))
      End If
 
      .ReadOnly = CBool(schema.Rows(i)("IsReadOnly"))
      .Unique = CBool(schema.Rows(i)("IsUnique"))
    End With
    columns(i) = column
  Next i
 
  Dim data As New DataTable
  Dim row As DataRow
 
  data.Columns.AddRange(columns)
 
  ' Get the data itself.
  While reader.Read()
    row = data.NewRow()
 
    For i As Integer = 0 To columns.GetUpperBound(0)
      row(i) = reader(i)
    Next i
 
    data.Rows.Add(row)
  End While
 
  reader.Close()
  oConn.Close()
 
  Return data
End Function