| 以文本方式查看主题 - 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=118850) |
| -- 作者：hrlong -- 发布时间：2018/5/12 9:59:00 -- 导入EXCEL怎么去掉重复的值 老师 ，您好 我采用下面的代码导入数据 但是，这个导入方式只能比对EXCEL里面和数据库里重复的数据，但是如果excel里面有重复，但是原数据库里面没有，这些重复的数据还是会继续导入 比如，我要查找的是 “系统单号”列是否有重复； 原来数据库里没有这个单号，但是要导入的excel中同样的系统单号有5行，那这5行都会导入，但是我只要1行导入 请问，该怎么改这个代码呢 =========================================================== Dim dt As DataTable = DataTables("订单简表") Dim Result As DialogResult Result = MessageBox.Show("确定要导入吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) If Result = DialogResult.Yes Then Dim dlg As New OpenFileDialog dlg.Filter = "Excel文件|*.xls;*.xlsx" dlg.MultiSelect = True \'允许选择多个文件 If dlg.ShowDialog =DialogResult.OK Then Dim Book As New XLS.Book(dlg.FileName) Dim Sheet As XLS.Sheet = Book.Sheets("sheet0") dt.ResumeRedraw() dt.StopRedraw() \'systemready = False Dim nms As New Dictionary(Of String, Integer) Dim dic As new Dictionary(Of DataRow, Integer) Dim ls As new List(Of Integer) For c As Integer = 0 To sheet.Cols.Count - 1 If dt.DataCols.Contains(sheet(0,c).Text.replace(" ", "")) Then nms.Add(sheet(0,c).Text.replace(" ", ""), c) End If Next For n As Integer = 1 To Sheet.Rows.Count -1 Dim sfzhm As String = sheet(n,nms("系统单号")).Text If sfzhm = "" Then Continue For Dim dr As DataRow = dt.Find("系统单号 = \'" & sfzhm & "\'") If dr Is Nothing Then ls.add(n) Else dic.Add(dr, n) End If Next For Each key As DataRow In dic.Keys For Each m As String In nms.keys If dt.DataCols(m).IsBoolean Then If Sheet(dic(key),nms(m)).Text = "" OrElse Sheet(dic(key),nms(m)).Text = "False" OrElse Sheet(dic(key),nms(m)).Value = 0 Then key(m) = False Else key(m) = True End If Else If dt.DataCols(m).Expression > "" Then \'表达式列 Else If dt.DataCols(m).IsNumeric Then key(m) = val(Sheet(dic(key),nms(m)).Value) Else If dt.DataCols(m).IsDate Then Dim d As Date If Date.TryParse(Sheet(dic(key),nms(m)).Value, d) key(m) = d End If Else key(m) = Sheet(dic(key),nms(m)).Value End If Next Next For Each l As Integer In ls Dim ndr As DataRow = dt.AddNew For Each m As String In nms.keys If dt.DataCols(m).IsBoolean Then If Sheet(l,nms(m)).Text = "" OrElse Sheet(l,nms(m)).Text = "False" OrElse Sheet(l,nms(m)).Value = 0 Then ndr (m) = False Else ndr (m) = True End If Else If dt.DataCols(m).Expression > "" Then \'表达式列 Else If dt.DataCols(m).IsNumeric Then ndr (m) = val(Sheet(l,nms(m)).Value) Else If dt.DataCols(m).IsDate Then Dim d As Date If Date.TryParse(Sheet(l,nms(m)).Value, d) ndr (m) = d End If Else ndr (m) = Sheet(l,nms(m)).Value End If Next Next dt.ResumeRedraw() \'systemready = True End If End If |
| -- 作者：有点蓝 -- 发布时间：2018/5/12 10:16:00 -- …… Dim ls_sfzhm As new List(Of String) For n As Integer = 1 To Sheet.Rows.Count -1 Dim sfzhm As String = sheet(n,nms("系统单号")).Text If sfzhm = "" Then Continue For Dim dr As DataRow = dt.Find("系统单号 = \'" & sfzhm & "\'") If dr Is Nothing Then If ls_sfzhm.Contains(sfzhm) = False Then ls.add(n) ls_sfzhm.Add(sfzhm) End If Else dic.Add(dr, n) End If Next …… |