专家坐堂-导入数据Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  导入数据  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=171395)

--  作者：xingshou
--  发布时间：2021/8/28 11:33:00
--  导入数据
老师您好，我想问一下导入数据，这是我的代码，我新增一条他就出很多的空值应该怎么修改呢
Dim dlg As New SaveFileDialog \'定义一个新的SaveFileDialog
dlg.Filter= "Excel文件|*.xls" \'设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then
Dim Book As New XLS.Book(dlg.FileName)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("t1").StopRedraw()
\'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致
Dim nms() As String = {"姓名","学号","专业","学校","实习单位"}
\'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题
For n As Integer = 1 To Sheet.Rows.Count -1
Dim bh As String = sheet(n,0).Text
If DataTables("t1").Find("学号 = \'" & bh & "\'") Is Nothing Then \'如果不存在同编号的订单
Dim r As Row = Tables("t1").AddNew()
For m As Integer = 0 To nms.Length - 1
r(nms(m)) = Sheet(n,m).Value
Next
End If
Next
Tables("t1").ResumeRedraw()
End If

--  作者：xingshou
--  发布时间：2021/8/28 11:34:00
--
Dim dlg As New SaveFileDialog \'定义一个新的SaveFileDialog
dlg.Filter= "Excel文件|*.xls" \'设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then
Dim Book As New XLS.Book(dlg.FileName)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("t1").StopRedraw()
\'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致
Dim nms() As String = {"姓名","学号","专业","学校","实习单位"}
\'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题
For n As Integer = 1 To Sheet.Rows.Count -1
Dim bh As String = sheet(n,0).Text
If DataTables("t1").Find("学号 = \'" & bh & "\'") Is Nothing Then \'如果不存在同编号的订单
Dim r As Row = Tables("t1").AddNew()
For m As Integer = 0 To nms.Length - 1
r(nms(m)) = Sheet(n,m).Value
Next
End If
Next
Tables("t1").ResumeRedraw()
End If
还有一个如果表的列顺序不一样应该怎么导入呢

--  作者：有点蓝
--  发布时间：2021/8/28 11:44:00
--
参考：http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&Id=135684

--  作者：xingshou
--  发布时间：2021/8/28 11:51:00
--
拿老师如果我foxtable是"姓名","学号","专业","学校","实习单位" excel是学号姓名专业学校实习单位怎么弄

--  作者：有点蓝
--  发布时间：2021/8/28 12:04:00
--
3楼链接里有，使用字典把对应关系存下来。如果看不懂，就上传实例

--  作者：xingshou
--  发布时间：2021/8/28 13:51:00
--

Dim dlg As New OpenFileDialog \'定义一个新的OpenFileDialog
dlg.Filter= "Excel文件|*.xls|Word文件|*.doc|Access文件|*.mdb" \'设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then \'如果用户单击了确定按钮
Dim nms() As String = {"学号","姓名","专业","学校","实习单位"}
Dim nms2() As String = {"学号","姓名","专业","学校","实习单位"}
Dim dict As new Dictionary(of String,String)
For m As Integer = 0 To nms.Length - 1
dict.add(nms2(m),nms(m))
Next
Dim Book As New XLS.Book(dlg.FileName)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("t1").StopRedraw()
\'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题
For n As Integer = 1 To Sheet.Rows.Count -1
If Sheet(n,0).text > "" Then
Dim r As Row = Tables("t1").AddNew()
For m1 As Integer = 0 To Sheet.Cols.Count - 1
If dict.ContainsKey(Sheet(0,m1).Value)
r(dict(Sheet(0,m1).Value)) = Sheet(n,m1).Value
End If
Next
End If
Next
Tables("t1").ResumeRedraw()
End If

--  作者：xingshou
--  发布时间：2021/8/28 13:51:00
--
老师我想问一下excel没有处理老师列的，怎么设置可以让他，导入的数据都添加处理人为username的

--  作者：有点蓝
--  发布时间：2021/8/28 13:55:00
--

For n As Integer = 1 To Sheet.Rows.Count -1
If Sheet(n,0).text > "" Then
Dim r As Row = Tables("t1").AddNew()
For m1 As Integer = 0 To Sheet.Cols.Count - 1
If dict.ContainsKey(Sheet(0,m1).Value)
r(dict(Sheet(0,m1).Value)) = Sheet(n,m1).Value
End If
Next
r("处理人") = user.name
End If
Next

[此贴子已经被作者于2021/8/28 13:55:14编辑过]