以文本方式查看主题

-  Foxtable(狐表)  (http://www.foxtable.com/bbs/index.asp)
--  专家坐堂  (http://www.foxtable.com/bbs/list.asp?boardid=2)
----  导入数据报错问题求帮助?  (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=75215)

--  作者:lisheng7177
--  发布时间:2015/9/30 13:04:00
--  导入数据报错问题求帮助?

 下载信息  [文件大小:   下载次数: ]
图片点击可在新窗口打开查看点击浏览该文件:参评一览.xls

这是“导出”按钮所用的EXCEL模板

 

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"
    dlg.MultiSelect = True \'允许选择多个文件
    If dlg.ShowDialog =DialogResult.OK Then
        Dim Book As New XLS.Book(dlg.FileName)
        Dim Sheet As XLS.Sheet = Book.Sheets(0)
        Tables("上会评审表").ResumeRedraw()
        Tables("上会评审表").StopRedraw()
        systemready = False
        Dim nms() As String = {"评审分组","工作单位","县公司","材料编号","姓名","性别","身份证号码","文化程度","现岗位名称","参加工作时间","现工种工作时间","原技术资格等级","原等级取得时间","原资格证书工种","原资格证书编号","鉴定等级","申报鉴定工种","鉴定批次","申报性质","理论成绩","实操成绩","工作业绩","潜能成绩","总评成绩","评审资格","备注"}
        Dim dic As new Dictionary(Of DataRow, Integer)
        Dim ls As new List(Of Integer)
        For n As Integer = 1 To Sheet.Rows.Count -1
            Dim sfzhm As String = sheet(n,7).Text
            Dim jdpc As String = sheet(n,18).Text
            Dim dr As DataRow = DataTables("上会评审表").Find("身份证号码 = \'" & sfzhm & "\' And 鉴定批次 = \'" & jdpc & "\'")
            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 m As Integer = 0 To nms.Length - 1
                If DataTables("上会评审表").DataCols(nms(m)).IsBoolean Then
                    If Sheet(dic(key),m).Value = 0 Then
                        key(nms(m)) = False
                    Else
                        key(nms(m)) = True
                    End If
                Else If DataTables("上会评审表").DataCols(nms(m)).Expression > "" Then
                    \'表达式列
                Else If DataTables("上会评审表").DataCols(nms(m)).IsNumeric Then
                    key(nms(m)) = val(Sheet(dic(key),m).Value)
                Else
                    key(nms(m)) = Sheet(dic(key),m).Value
                End If
            Next
        Next
        For Each l As Integer In ls
            Dim ndr As DataRow = DataTables("上会评审表").AddNew
           
            For m As Integer = 0 To nms.Length - 1
                If DataTables("上会评审表").DataCols(nms(m)).IsBoolean Then
                    If Sheet(l,m).Value = 0 Then
                        ndr (nms(m)) = False
                    Else
                        ndr (nms(m)) = True
                    End If
                Else If DataTables("上会评审表").DataCols(nms(m)).Expression > "" Then
                    \'表达式列
                Else If DataTables("上会评审表").DataCols(nms(m)).IsNumeric Then
                    ndr (nms(m)) = val(Sheet(l,m).Value)
                Else
                    ndr (nms(m)) = Sheet(l,m).Value
                End If
            Next
        Next
        Tables("上会评审表").ResumeRedraw()
        systemready = True
    End If
End If

 

这是“导入”按钮代码,现在就是点按钮就报错,不知是为什么?求版主指点,谢谢先!!!!!!


--  作者:大红袍
--  发布时间:2015/9/30 14:23:00
--  

请把excel数据和foxtable例子一起发上来测试。


--  作者:lisheng7177
--  发布时间:2015/9/30 15:12:00
--  
 下载信息  [文件大小:   下载次数: ]
图片点击可在新窗口打开查看点击浏览该文件:2015年09月参评人员(0930).xls

 下载信息  [文件大小:   下载次数: ]
图片点击可在新窗口打开查看点击浏览该文件:鉴定管理系统v2.rar

这是例子,烦庄股版主看看!
--  作者:大红袍
--  发布时间:2015/9/30 15:20:00
--  

代码没问题,这段代码,必须和excel表的各个列对应。

 

Dim nms() As String = {"评审分组","工作单位","县公司","材料编号","姓名","性别","身份证号码","文化程度","现岗位名称","参加工作时间","现工种工作时间","原技术资格等级","原等级取得时间","原资格证书工种","原资格证书编号","鉴定等级","申报鉴定工种","鉴定批次","申报性质","理论成绩","实操成绩","工作业绩","潜能成绩","总评成绩","评审资格","备注"}


--  作者:lisheng7177
--  发布时间:2015/9/30 15:36:00
--  
图片点击可在新窗口打开查看问题是代码是和EXCEL表中的各列对应的
--  作者:大红袍
--  发布时间:2015/9/30 15:40:00
--  
汗,顺序要对应啊。
--  作者:lisheng7177
--  发布时间:2015/9/30 15:43:00
--  
顺序?代码中的顺序我是按EXCEL中有顺序写的,难道不对?
--  作者:大红袍
--  发布时间:2015/9/30 15:54:00
--  

 

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"
    dlg.MultiSelect = True \'允许选择多个文件
    If dlg.ShowDialog =DialogResult.OK Then
        Dim Book As New XLS.Book(dlg.FileName)
        Dim Sheet As XLS.Sheet = Book.Sheets(0)
        Tables("上会评审表").ResumeRedraw()
        Tables("上会评审表").StopRedraw()
        systemready = False     
        Dim nms() As String = {"","评审分组","工作单位","县公司","材料编号","姓名","性别","身份证号码","文化程度","现岗位名称","参加工作时间","现工种工作时间","原技术资格等级","原等级取得时间","原资格证书工种","原资格证书编号","鉴定等级","申报鉴定工种","鉴定批次","申报性质","理论成绩","实操成绩","工作业绩","潜能成绩","总评成绩","备注"}
        Dim dic As new Dictionary(Of DataRow, Integer)
        Dim ls As new List(Of Integer)
        For n As Integer = 1 To Sheet.Rows.Count -1
            Dim sfzhm As String = sheet(n,7).Text
            Dim jdpc As String = sheet(n,18).Text
            Dim dr As DataRow = DataTables("上会评审表").Find("身份证号码 = \'" & sfzhm & "\' And 鉴定批次 = \'" & jdpc & "\'")
            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 m As Integer = 0 To nms.Length - 1
                If nms(m) = "" Then Continue For
                output.show(m)
                If DataTables("上会评审表").DataCols(nms(m)).IsBoolean Then
                    If Sheet(dic(key),m).Value = 0 Then
                        key(nms(m)) = False
                    Else
                        key(nms(m)) = True
                    End If
                Else If DataTables("上会评审表").DataCols(nms(m)).Expression > "" Then
                    \'表达式列
                Else If DataTables("上会评审表").DataCols(nms(m)).IsNumeric Then
                    key(nms(m)) = val(Sheet(dic(key),m).Value)
                Else If DataTables("上会评审表").DataCols(nms(m)).IsDate Then
                    Dim d As Date
                    If Date.TryParse(Sheet(dic(key),m).Value, d)
                        key(nms(m)) = d
                    End If
                Else
                    key(nms(m)) = Sheet(dic(key),m).Value
                End If
            Next
        Next
        For Each l As Integer In ls
            Dim ndr As DataRow = DataTables("上会评审表").AddNew
           
            For m As Integer = 0 To nms.Length - 1
                If nms(m) = "" Then Continue For
                If DataTables("上会评审表").DataCols(nms(m)).IsBoolean Then
                    If Sheet(l,m).Value = 0 Then
                        ndr (nms(m)) = False
                    Else
                        ndr (nms(m)) = True
                    End If
                Else If DataTables("上会评审表").DataCols(nms(m)).Expression > "" Then
                    \'表达式列
                Else If DataTables("上会评审表").DataCols(nms(m)).IsNumeric Then
                    ndr (nms(m)) = val(Sheet(l,m).Value)
                Else If DataTables("上会评审表").DataCols(nms(m)).IsDate Then
                    Dim d As Date
                    If Date.TryParse(Sheet(l,m).Value, d)
                        ndr (nms(m)) = d
                    End If
                Else
                    ndr (nms(m)) = Sheet(l,m).Value
                End If
            Next
        Next
        Tables("上会评审表").ResumeRedraw()
        systemready = True
    End If
End If

 


--  作者:lisheng7177
--  发布时间:2015/9/30 16:18:00
--  
图片点击可在新窗口打开查看图片点击可在新窗口打开查看谢谢版主!!!!!终于搞定了!发现几处改动,虽然不是很明白,但能解决问题了!