-- 作者:刘林
-- 发布时间:2017/3/5 10:44:00
-- 导入校验
Dim xxmc As String = user.group Dim dlg As New OpenFileDialog dlg.Filter = "Excel|*.xls;*.xlsx;*.csv" If dlg.ShowDialog = DialogResult.OK Then Dim Book As New XLS.Book(dlg.filename) Dim Sheet As XLS.Sheet = Book.Sheets(0) Dim bj1 As Integer Dim xm1 As Integer Dim bsm1 As Integer Dim sfzjh1 As Integer Dim csrq As Integer = -1 Dim p As WinForm.ProgressBar p = e.Form.Controls("ProgressBar1") p.Maximum = sheet.Rows.Count-1 \'设置最大值 p.Minimum = 0 \'设置最小值 p.Value = 0 \'设置当前值 Dim s1 As new List(of String) For m As Integer = 0 To sheet.Cols.count -1 If Tables("学生信息").Cols.Contains(sheet(0,m).value) = False Then s1.add(sheet(0,m).value) End If Next If s1.count >0 Dim yw As String For yw1 As Integer =0 To s1.count-1 yw = yw & s1(yw1) & "列 " Next messagebox.show("请注意,你的样表表头存在" & s1.count &" 处错误,分别是:" & yw & " 请返回修改后再重新导入","提示" ) Return End If For i As Integer = 0 To sheet.Cols.count -1 If sheet(0,i).value = "班级" bj1 =i End If If sheet(0,i).value = "学生姓名" xm1 = i End If If sheet(0,i).value = "身份证件号" sfzjh1 = i End If If sheet(0,i).value ="出生日期" csrq = i End If Next For n1 As Integer = 1 To Sheet.Rows.Count -1 If sheet(n1,bj1).value.Substring(0,2) <> "初中" Or sheet(n1,bj1).value.Substring(0,2) <> "小学" messagebox.show("请注意,你的班级列中 第" & n1 & "行 有不规范内容,正确格式应为初中20XX级X班或小学20XX级X班","错误提示",MessageBoxButtons.OK) Return End If Next \' Tables("学生信息").StopRedraw() For n As Integer = 1 To Sheet.Rows.Count -1 Dim bj As String = sheet(n,bj1).value.replace(" ","").replace(chr(9),"") Dim xm As String = sheet(n,xm1).value.replace(" ","").replace(chr(9),"") Dim sfzjh As String = sheet(n,sfzjh1).value.replace(" ","").replace(chr(9),"") Dim dr As DataRow = DataTables("学生信息").sqlFind("班级 = \'" & bj & "\'and 学校名称 = \'" & xxmc & "\' And 学生姓名 = \'" & xm & "\' and 身份证件号 = \'" & sfzjh & "\'") If dr Is Nothing Then \'如果不存在同考号的行 dr = DataTables("学生信息").AddNew() End If For m As Integer = 0 To sheet.Cols.count -1 If sheet(0,m).value IsNot Nothing AndAlso Tables("学生信息").Cols.Contains(sheet(0,m).value) Then If m = csrq dr(sheet(0,m).value) = Nothing Else dr(sheet(0,m).value) = Sheet(n,m).Value.replace(" ","").replace(chr(9),"") dr("状态") = "在读:原籍在读" If dr("学校名称") = "" dr("学校名称") = xxmc End If End If End If Next p.Value = n Next \' Tables("学生信息").ResumeRedraw() For Each r As Row In Tables("学生信息").Rows For Each c As Col In Tables("学生信息").Cols If r.IsNull(c.Name) = False AndAlso c.IsString Then r(c.Name) = r(c.Name).replace(" ","") r(c.Name) = r(c.Name).replace(chr(9),"") End If Next Next DataTables("学生信息").DataCols("身份证件号").RaiseDataColChanged() DataTables("学生信息").DataCols("班级").RaiseDataColChanged() DataTables("学生信息").save() Dim cmd As New SQLCommand Dim dt As DataTable cmd.c区业务主管") cmd.CommandText = "SELEC T DISTINCT 学校名称, 年级, 班级 From {学生信息} " Else cmd.CommandText = "SELEC T DISTINCT 学校名称, 年级, 班级 From {学生信息} where 学校名称 = \'" & xxmc & "\'" End If dt = cmd.ExecuteReader() Dim trv As WinForm.TreeView = Forms("学生信息").Controls("TreeView1") trv.BuildTree(dt,"学校名称|年级|班级") End If
老师,请教一下上面蓝色部分是想先校验EXCl 表中有不有与table不一致的字段,红色是想校验下主要列班级的写法。
1、现在执行messagebox.show返回都是第1行有错,试验是第一行格式对了的。请问怎么改?如何检验更好
2、如果导入过程中发现错误,如何用代码终止进程
3、如果终止导入后,如何撤销添加的的部分记录,现在的情况是发现有错,不能终止,导完了后又只有删除记录
|
-- 作者:刘林
-- 发布时间:2017/3/5 13:24:00
--
For n1 As Integer = 1 To Sheet.Rows.Count -1 If sheet(n1,bj1).value.Substring(0,2) <> "初中" Or sheet(n1,bj1).value.Substring(0,2) <> "小学" messagebox.show("请注意,你的班级列中 第" & n1 & "行 有不规范内容,正确格式应为初中20XX级X班或小学20XX级X班","错误提示",MessageBoxButtons.OK) Return End If Next
1、这段检验班级记录值不规范我认为可以这样写,但第一行格式是对的时候也要反回,我是想检查该列哪行开始有问题。
2、如果在导入过程中如何用代码来中止导入
|
-- 作者:刘林
-- 发布时间:2017/3/5 14:33:00
--
For n1 As Integer = 1 To Sheet.Rows.Count -1 If trim(sheet(n1,bj1).value).length <> 9 AndAlso trim(sheet(n1,bj1).value).length <> 10 messagebox.show(sheet(n1,bj1).value.Substring(0,2) & "请注意,你的班级列中 第" & n1+1 & "行 有字符宽度不正确,正确格式应为初中20XX级X班或小学20XX级X班","错误提示",MessageBoxButtons.OK) Return Else If sheet(n1,bj1).value.Substring(0,2).contains("小学") = False AndAlso sheet(n1,bj1).value.Substring(0,2).contains("初中") = False messagebox.show(sheet(n1,bj1).value.Substring(0,2) & "请注意,你的班级列中 第" & n1+1 & "行 有字符不规范内容,正确格式应为初中20XX级X班或小学20XX级X班","错误提示",MessageBoxButtons.OK) Return End If End If Next
谢谢老师,我改成这样子,请问这个要不要得,2问用return主要是用户如果导入过程中发现想终止,窗口内按键都点不动了,整个系统其他地方都不能点,如不做滚动条像死机一样,请问这个如何解决
|