以文本方式查看主题 - Foxtable(狐表) (http://foxtable.net/bbs/index.asp) -- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2) ---- 从excel中导入数据自动匹配字段 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=135684) |
||||
-- 作者:蓝蚂蚁 -- 发布时间:2019/6/1 8:37:00 -- 从excel中导入数据自动匹配字段
表A结构: 登记日期 是否登记 审核 名称 规格型号 厂家 单位 数量
从Excel导入的表格其列名: 日期 编号 出库仓库 组件名称 规格 生产企业 单位 数量
需要匹配的字段是:登记日期-日期 名称-组件名称 规格型号-规格 厂家-生产企业 单位-单位 数量-数量 这个Excel其他的列不需要导入(红色字段都不用理会的)。 问题:能不能用代码指定上面对应的匹配 不用手工一个一个点匹配。 补充:我做一个按钮,在按钮代码里面把导入的相关设置好,直接点击按钮就能实现导入数据,不用打开图片显示那个窗口了,很方便。谢谢 [此贴子已经被作者于2019/6/1 9:01:16编辑过]
|
||||
-- 作者:有点蓝 -- 发布时间:2019/6/1 9:21:00 -- [此贴子已经被作者于2019/6/1 9:22:18编辑过]
|
||||
-- 作者:有点蓝 -- 发布时间:2019/6/1 9:29:00 -- 参考: Dim nms() As String = {"登记日期","名称"} Dim nms2() As String = {"日期","组件名称"} Dim dict As new Dictionary(of String,Integer) For m As Integer = 0 To nms.Length - 1 dict.add(nms2(m),nms(m)) Next Dim Book As New XLS.Book("c:\\test\\订单.xls") Dim Sheet As XLS.Sheet = Book.Sheets(0) Tables("订单").StopRedraw() \'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题 For n As Integer = 1 To Sheet.Rows.Count -1 Dim r As Row = Tables("订单").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 Next Tables("订单").ResumeRedraw() |
||||
-- 作者:蓝蚂蚁 -- 发布时间:2019/6/2 21:29:00 -- 从字符串“登记日期 ”到类型“Integer”的转换无效。 |
||||
-- 作者:蓝蚂蚁 -- 发布时间:2019/6/2 21:31:00 --
|
||||
-- 作者:有点甜 -- 发布时间:2019/6/3 8:49: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("表A").StopRedraw() \'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题 For n As Integer = 1 To Sheet.Rows.Count -1 Dim r As Row = Tables("表A").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 Next Tables("表A").ResumeRedraw() End If |
||||
-- 作者:蓝蚂蚁 -- 发布时间:2019/6/3 10:06:00 -- 此主题相关图片如下:image 1.jpg |
||||
-- 作者:有点甜 -- 发布时间:2019/6/3 10:17: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("表A").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("表A").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("表A").ResumeRedraw() End If |