-- 作者：cnsjroom
-- 发布时间：2022/2/24 1:45:00
-- 当前代码实现根据选择的文本文件，读取标题列创建对应的表，赋值对应的表字段值，也可实现对应的文件目录下批量导入数据，代码略显繁琐，老师们是否可以帮忙精简一下代码呢？
当前代码实现根据选择的文本文件，读取标题列创建对应的表，赋值对应的表字段值，也可实现对应的文件目录下批量导入数据，代码略显繁琐，老师们是否可以帮忙精简一下代码呢？
Dim dlg As new OpenFileDialog
dlg.Filter = "文本文件|*.txt"
Dim t2 As WinForm.TextBox = e.Form.Controls("TextBox2")
Dim t1 As WinForm.TextBox = e.Form.Controls("TextBox1")
Dim ck As WinForm.CheckBox = e.Form.Controls("是否批量导入")
If dlg.ShowDialog = DialogResult.OK Then
If ck.Checked=True Then \'选择批量导入
\'先从其中一个文本里面读取字段列,然后判断表是否存在数据库中,如果不存在,就创建,反之就不创建表
Dim Multi As String
Dim strs As String = FileSys.ReadAllText(dlg.FileName, Encoding.Default)
t2.text=dlg.FileName
Dim Values11() As String = strs.Replace(vbcr,"").split(vblf)
For i1 As Integer = 0 To 0
Dim rs As String = Values11(0)
For i As Integer = 0 To 0
Dim cs() As String = rs.Split(vbtab)
output.Show(cs(0))
Multi = cs(0)
Next
Next
Dim ab As String="否"
Dim lst1 As List(Of String)
lst1 = Connections(" 委").GetTableNames
For Each nm1 As String In lst1
output.show(nm1)
If t1.text=nm1 Then
ab="是"
End If
Next
Dim Values() As String
Values = Multi.split("|")
If ab="是" Then
Else
Dim Builder As New ADOXBuilder(" 委") \'要指定数据源名称
Dim tbl As ADOXTable
Builder.Open()
tbl = Builder.NewTable(t1.text) \'创建表
With tbl
For Index1 As Integer = 0 To Values.Length - 1
.AddColumn(Values(Index1) ,ADOXType.String, 255)
Next
End With
Builder.AddTable(tbl, True, True) \'增加表
Builder.Close()
Dim Builder1 As New ADOXBuilder(" 委") \'要指定数据源名称
Builder1.Open() \'打开ADOXBuilder
With Builder1.Tables(t1.text)
.AddColumn("日期" ,ADOXType.DateTime) \'增加日期型列
End With
Builder1.Close() \'关闭ADOXBuilder
End If
\'遍历选择的文件所在根目录,然后遍历目录下的所有文件,然后开始赋值
For Each File As String In FileSys.GetFiles(FileSys.GetParentPath(dlg.FileName))
Dim strs1 As String = FileSys.ReadAllText(File, Encoding.Default)
t2.text=FileSys.GetParentPath(dlg.FileName)
Dim Values111() As String = strs1.Replace(vbcr,"").split(vblf)
Dim cmd As New SQ LCo mmand
cmd.Conne ction Name = " 委"
Dim dt As DataTable
Dim bbb As String ="SEL ECT * From {" & t1.text & "}"" "
cmd.Comm andText = bbb
dt = cmd.ExecuteReader()
Dim tb1 As WinForm.Table = e.Form.Controls("Table1")
tb1.Table.DataSource = dt
tb1.Table.StopRedraw
For i As Integer = 1 To Values111.length - 1
Dim cs() As String = Values111(i).split("|")
Dim r As Row = tb1.Table.AddNew
For i1 As Integer = 0 To math.min(cs.Length-1, tb1.Table.cols.count - 1)
Dim bb As String=cs(i1).trim()
If bb > "" Then
If Values(i1).Contains("时间") OrElse Values(i1).Contains("日期") Then
Dim d As Date = cdate(bb)
r("日期")= Format(d, "yyyy-MM-dd")
r(i1) = cs(i1)
Else
r(i1) = cs(i1)
End If
End If
Next
Next
tb1.Table.ResumeRedraw
Next
Else
Dim Multi As String
Dim strs As String = FileSys.ReadAllText(dlg.FileName, Encoding.Default)
t2.text=dlg.FileName
Dim Values11() As String = strs.Replace(vbcr,"").split(vblf)
For i1 As Integer = 0 To 0
Dim rs As String = Values11(0)
For i As Integer = 0 To 0
Dim cs() As String = rs.Split(vbtab)
output.Show(cs(0))
Multi = cs(0)
Next
Next
Dim ab As String="否"
Dim lst As List(Of String)
lst = Connections(" 委").GetTableNames
For Each nm As String In lst
output.show(nm)
If t1.text=nm Then
ab="是"
End If
Next
Dim Values() As String
Values = Multi.split("|")
If ab="是" Then
Else
Dim Builder As New ADOXBuilder(" 委") \'要指定数据源名称
Dim tbl As ADOXTable
Builder.Open()
tbl = Builder.NewTable(t1.text) \'创建表
With tbl
For Index1 As Integer = 0 To Values.Length - 1
.AddColumn(Values(Index1) ,ADOXType.String, 255)
Next
End With
Builder.AddTable(tbl, True, True) \'增加表
Builder.Close()
Dim Builder1 As New ADOXBuilder(" 委") \'要指定数据源名称
Builder1.Open() \'打开ADOXBuilder
With Builder1.Tables(t1.text)
.AddColumn("日期" ,ADOXType.DateTime) \'增加日期型列
End With
Builder1.Close() \'关闭ADOXBuilder
End If
Dim cmd As New SQ LC o mmand
cmd.Conn ection Name = " 委"
Dim dt As DataTable
Dim bbb As String ="SEL E CT * From {" & t1.text & "}"" "
cmd.Comma ndText = bbb
dt = cmd.ExecuteReader()
Dim tb1 As WinForm.Table = e.Form.Controls("Table1")
tb1.Table.DataSource = dt
tb1.Table.StopRedraw
For i As Integer = 1 To Values11.length - 1
Dim cs() As String = Values11(i).split("|")
Dim r As Row = tb1.Table.AddNew
For i1 As Integer = 0 To math.min(cs.Length-1, tb1.Table.cols.count - 1)
Dim bb As String=cs(i1).trim()
If bb > "" Then
If Values(i1).Contains("时间") OrElse Values(i1).Contains("日期") Then
Dim d As Date = cdate(bb)
r("日期")= Format(d, "yyyy-MM-dd")
r(i1) = cs(i1)
Else
r(i1) = cs(i1)
End If
End If
Next
Next
tb1.Table.ResumeRedraw
End If
End If
|