Basit Khan

Basit Khan

  • 1.8k
  • 325
  • 99.2k

Data import from Excel to GridView

Feb 12 2018 11:31 PM
Hi,
 
with the below code im importing data from excel to gridview, Data is importing successfully, but if column have text and number data then it will take only either number or text data, if take number data then shows blank in text field data.
 
Example below excel file
 Item Code Item Description
 '000001 Item A
 2 Item B
 3 Item C
 
In this case First row 000001 shows blank in GridView 
 
Dim conn As OleDbConnection
Dim dtr As OleDbDataReader
Dim dta As OleDbDataAdapter
Dim cmd As OleDbCommand
Dim dts As DataSet
Dim excel As String
Dim OpenFileDialog As New OpenFileDialog
OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
OpenFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"
If (OpenFileDialog1.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
excel = OpenFileDialog1.InitialDirectory + OpenFileDialog1.FileName
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;")
dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)
dts = New DataSet
dta.Fill(dts, "[Sheet1$]")
DataGridView1.DataSource = dts
DataGridView1.DataMember = "[Sheet1$]"
conn.Close()
End If
 
Thanks
Basit. 
 

Answers (5)