Click here to Skip to main content
15,909,199 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i am importing excel file in datagridview below is my code
'ofdImport.Filter = "Excel Files (*.xls)|*.xls"
       ofdImport.FileName = ""
       If ofdImport.ShowDialog(Me) = Windows.Forms.DialogResult.Cancel Then

           Exit Sub

       End If
       txtPath.Text = ofdImport.FileName
       If txtPath.Text <> "" Then
           chkItemList.Visible = True
           dgvImportData.Visible = True
           pnlDataMsg.Visible = False
           dgvImportData.Columns.Clear()
           chkItemList.Items.Clear()
           Try

               Dim conExcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & txtPath.Text & " '; Extended Properties=Excel 8.0;")
               Dim excelSelect As New OleDbCommand
               Dim excelAdp As New OleDbDataAdapter
               Dim excelSchemaDt As DataTable
               Dim dset As New DataSet

               If conExcel.State Then conExcel.Close()
               conExcel.Open()
               excelSelect.Connection = conExcel
               excelSchemaDt = conExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
               Dim sheetName As String = excelSchemaDt.Rows(0)("TABLE_NAME").ToString()

               excelSelect.CommandText = "select * from [" & sheetName & "] "
               excelAdp.SelectCommand = excelSelect
               excelAdp.TableMappings.Add("Table", "TestTable")
               excelAdp.Fill(dset)
               dgvImportData.DataSource = dset.Tables(0)
               Dim i As Integer
               Dim q(dgvImportData.Columns.Count()) As String
               For i = 0 To (dgvImportData.Columns.Count - 1)
                   q(i) = (dgvImportData.Columns(i).HeaderText.ToString())

               Next i
               conExcel.Close()

               i = 0
               Dim a As String = ""
               'Dim q(frmImportWizard.gridImport.Columns.Count()) As String
               For i = 0 To dgvImportData.Columns.Count - 1
                   a = dgvImportData.Columns(i).HeaderText.ToString()
                   'CheckedListBox1.Items.Add(a)
                   chkItemList.Items.Add(a)
               Next i
               chkAll.Visible = True
               chkAll.Checked = False
               lblColumnData.Visible = True
           Catch ex As Exception
               'MsgBox(ex.Message, MsgBoxStyle.Information)
           End Try


       End If


excel file contains phone nos for eg "9874532146/8456663225" in one colms and also i have "98455566966" in same colmns
the problem here is my code is not reading the phone numbers without "/" its going blank
in grid
please help
Posted

I think you should convert phone nos DataColumn to String after data retrieves To dset.Tables(0)

Sample code you may write like this:

Dim Col1 As New DataColumn("NewPhoneNoCol",GetType(String))

dset.Tables(0).Column.Add(Col1)

For J as integer =0 to dset.Tables(0).Rows.Count-1
dset.Tables(0).Rows(J).Item("NewPhoneNoCol") = dset.Tables(0).Rows(J).Item("OldPhoneNoCol")
Next

dset.Tables(0).Columns.Remove(dset.Tables(0).Columns("OldPhoneNoCol"))
dset.Tables(0).Columns("NewPhoneNoCol").ColumnName = "OldPhoneNoCol"
 
Share this answer
 
Comments
Omkaara 13-Jun-13 2:16am    
data colomn is used only for header text of data gridview
replaced connection string

VB
Dim conExcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & txtPath.Text & "';Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""")
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900