从datagridview中的excel错误导入 [英] import from excel error in datagridview

问题描述

我在datagridview中导入excel文件，下面是我的代码

'ofdImport.Filter =Excel Files（* .xls）| * .xls
 ofdImport.FileName =
如果ofdImport.ShowDialog（Me）= Windows.Forms.DialogResult.Cancel则
 
退出Sub 
 
结束如果
 txtPath.Text = ofdImport.FileName 
如果txtPath.Text<> 然后
 chkItemList.Visible = True 
 dgvImportData.Visible = True 
 pnlDataMsg.Visible = False 
 dgvImportData.Columns.Clear（）
 chkItemList.Items.Clear （）
尝试
 
 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然后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。表（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（））
 
下一页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（）
'CheckListBox1.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）
结束尝试
 
 
结束如果
 

excel文件包含电话号码例如9874532146/8456663225在一个colms中我也有98455566966在同一个colmns

这里的问题是我的代码没有读取电话号码没有/它的空白

in grid

请帮助

解决方案

我认为你应该在数据检索后将手机号码DataColumn转换为String to dset.Tables（ 0）



您可以这样写的示例代码：



Dim Col1 As New DataColumn（NewPhoneNoCol ，GetType（String））



dset.Tables（0）.Column.Add（Col1）



对于J为整数= 0到dset.Tables（0）.Rows.Count-1

dset.Tables（0）.Rows（J）.Item（NewPhoneNoCol）= dset.Tables（0）.Rows（J）.Item（OldPhoneNoCol）

下一页



dset.Tables（0）.Columns.Remove（dset.Tables（0）.Columns（OldPhoneNoCol））

dset.Tables（0）.Columns（NewPhoneNoCol）。ColumnName =OldPhoneNoCol

替换连接字符串

  Dim  conExcel  As  新 OleDbConnection（  Provider = Microsoft.Jet.OLEDB.4.0;数据源='& txtPath.Text&  ';扩展属性=Excel 8.0 ; HDR = YES; IMEX = 1 ）

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

解决方案

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"

replaced connection string

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

这篇关于从datagridview中的excel错误导入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！