import data from excel file

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • qfchen
    New Member
    • Oct 2006
    • 91

    import data from excel file

    I try to import data from an excel file, then save the data to a database, however, some cell always empty when read, whose cell combine with digit and alphabet, eg. "92FE0". below are the VB code

    Code:
    Private Sub OpenExcelFile_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenExcelFile.FileOk
            
            Name = OpenExcelFile.FileName
            Dim i, j, cnt As Integer
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    
            MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
                        "data source='" & Name & " '; " & "Extended Properties=Excel 8.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection)
    
            Dim DtSet As System.Data.DataSet
    
            Try
                MyCommand.TableMappings.Add("Table", "Attendence")
    
                DtSet = New System.Data.DataSet
    
                MyCommand.Fill(DtSet)
                cnt = DtSet.Tables(0).Rows.Count
    
                EmployeeDataGridView.DataSource = DtSet.Tables(0)
    
                MyConnection.Close()
    
                Dim record As System.Data.DataRow
                For i = 0 To cnt
                    record = Employee_DataSet.Employee.NewRow()
                    For j = 0 To 11
                        record.Item(j) = DtSet.Tables(0).Rows.Item(i).Item(j)
                    Next
                    Try
                        Employee_DataSet.Employee.Rows.InsertAt(record, i)
                        EmployeeTableAdapter.Update(Employee_DataSet.Employee)
                        EmployeeTableAdapter.Update(Employee_DataSet)
                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
    
                Next
    
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
    
    
        End Sub
Working...