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

Dim flst As WinForm.CheckedListBox = e.Form.Controls("文件列表")

Dim jdt As WinForm.ProgressBar = e.Form.Controls("进度条")

jdt.Maximum = flst.Items.Count * 100

jdt.Minimum = 0

For fid As Integer = 0 To flst.Items.Count -1

\'\'-----导出文件-----\'

Dim fn As String = flst.Items(fid)

Dim bookname As String = fn.Remove(fn.IndexOf("["))

Dim sheetname As String = fn.SubString(fn.IndexOf("[")+1,fn.LastIndexOf("]")-fn.IndexOf("[")-1)

Dim Book As New XLS.Book(bookname)

Dim sheet As XLS.Sheet = book.sheets(sheetname)

\'-----导入文件

Dim tblname As WinForm.ComboBox = e.Form.Controls("cbo目标表")

Dim dt As DataTable = DataTables(tblname.Text)

\'-------导出配置定义-----------------\'

Dim bth As WinForm.CheckBox = e.Form.Controls("标题")

Dim qsh As Integer = cint(e.Form.Controls("有效起始行").text)

\'----无标题行时,起始行须-1-----

If bth.Checked = False Then

qsh = qsh -1

End If

Dim dcb As WinForm.CheckedListBox = e.Form.Controls("表字段0")

Dim drb As WinForm.CheckedListBox = e.Form.Controls("表字段")

\'-----导出表字段列表--------\'

Dim dc As new List(of String)

dc.AddRange(dcb.ComboList.Split("|"))

\'-----导入表字段列表-----\'

Dim dr As new List(of String)

dr.AddRange(drb.ComboList.Split("|"))

\'------获取导出表数据-----

For r As Integer = qsh To sheet.rows.Count -1

\'-----进度条----

jdt.Value = fid*100 + cint(r*100/sheet.Rows.Count)

\'MessageBox.Show(jdt.value)

\'-----取出一行导出行各列数据-----

Dim data(dr.Count) As String

For i As Integer = 0 To dc.Count - 1

\'-----判定标记列-----\'

\'MessageBox.Show("dc(i)->" & dc(i))

If dc(i).Contains("标-->") = True Then

data(i) = dc(i).SubString(dc(i).IndexOf(">")+1)

Else

Dim zdh As Integer

zdh=cint(dc(i).Remove(dc(i).IndexOf("列")).Trim("第"," "))

data(i) = sheet(r,zdh-1).value

End If

\'-----判断筛选列表是否有效

\'--------筛选列列表-------------\'

Dim sxlst As WinForm.CheckedListBox = e.Form.Controls("筛选列表")

If sxlst.Items.Count > 0 Then

\'-----判断是否符合导出条件-----

Dim bds As String = "" \'-----筛选表达式

\'--------遍历条件-----

For Each S As String In sxlst.Items

\'-----获取列间逻辑符-----

Dim ljf As String

ljf= s.SubString(0,s.IndexOf("."))

ljf = iif(ljf="并且"," AND ",IIF(ljf="或者"," OR ",iif(ljf="排除","Not","")))

\'-----\'获取筛选列的在导出表中的值

Dim sxl,sxlz,ysf,ysz As String \'筛选列\\筛选列值\\运算符\\运算值

Dim sxid As Integer \'筛选列在导出列表的列位置

sxl = s.Substring( s.IndexOf(".")+1,s.Indexof("[") - s.indexof(".")-1 )

ysf = s.SubString( s.IndexOf("[")+1,s.IndexOf("]") - s.IndexOf("[") -1 )

ysz = s.SubString( s.IndexOf("(")+1,s.LastIndexOf(")") - s.IndexOf("(") -1 )

sxid = Functions.Execute("IndexStr",drb.ComboList,"|",sxl)

sxlz =data(sxid) \'----对应的导出数据

\'-----\'将运算符转为表达式可用的符号

ysf = iif(ysf="等于", "=" , iif(ysf="不等于", " <> " ,iif(ysf="大于", " > " ,iif(ysf="大于等于", " >= " ,iif(ysf="小于", " < " ,iif(ysf="小于等于", " <= " ,iif(ysf="包含"," like "," not like ")))))))

\'MessageBox.Show("列逻辑符=\'" & ljf & "\' sxid="\' & sxid & "\' sxl=\'" & sxl & "\' ysf=\'" & ysf & "\' ysz=\'" & ysz & "\'")

\'-----筛选表达式------ data(sxid)-->是取出导出对应列的值

\'-----导入值和运算值的数值类型判断

Dim exp As String

If IsNumeric(sxlz) = True Then

exp = cexp(" {0} {1} {2}",data(sxid),ysf,ysz)

ElseIf Isdate(sxlz) =True Then

exp = cexp(" #{0}# {1} #{2}#",data(sxid),ysf,ysz)

Else

If ysf.Contains("like") Then

exp = data(sxid).Contains(ysz)

Else

exp = """" & data(sxid) & """" & ysf & """" & ysz & """"

End If

bds = bds & ljf & exp

\'MessageBox.Show(bds)

\'------根据计算表达式计算结果判定导出数据是否符合-------\'

If eval(bds) = False Then

Continue For

End If

\'-----根据导入模式导入数据-----

\'------添加模式-----

Dim hl As WinForm.RadioButton = e.Form.Controls("添加")

If hl.Checked = True Then

Dim ndr As DataRow = dt.AddNew

For i As Integer = 0 To dr.Count - 1

ndr(dr(i)) = Data(i)

End If

\'-----更新和跳过模式-----\'

Dim gx As WinForm.RadioButton = e.Form.Controls("更新")

Dim fq As WinForm.RadioButton = e.Form.Controls("放弃")

If gx.Checked = True Or fq.Checked = True Then

\'MessageBox.Show("进入更新模式")

\'-----判定关键字段为第几字段----

Dim key1 As String = e.Form.Controls("key1").Value

Dim key2 As String = e.Form.Controls("key2").value

Dim data1,data2 As String

\'----根据关键字段在字段列表中的位置,取出要导入的数据值

For i As Integer = 0 To dr.Count -1

\'MessageBox.Show(dr(i) & "->" & dr(i) = key1 )

If dr(i) = key1 Then

data1 = data(i)

End If

If dr(i) = key2 Then

data2 =data(i)

End If

\' MessageBox.Show(key1 & "=" & data1 & "--" & key2 & "=" & data2)

\'----查找导入表的关键字段同值数据行的条件-----

Dim find As DataRow

Dim exp As String

If data1 <> "" And data2 <> "" Then

exp = cexp("[" & key1 & "] = \'{0}\' and [" & key2 & "] = \'{1}\'",data1,data2)

ElseIf data1 <> "" Then

exp = cexp("[" & key1 & "] = \'{0}\'",data1)

ElseIf data2 <> "" Then

exp = cexp("[" & key2 & "] = \'{0}\'",data2)

End If

\'----查找导入表符合条件的行------\'

If exp IsNot Nothing Then

find = DataTables(tblname.Value).find(exp)

End If

\'MessageBox.Show("exp=" & exp )

If find IsNot Nothing Then

\'-------找到后,模式为更新就更新数据,否则跳过-------

If gx.Checked = True Then

For i As Integer = 0 To dr.Count - 1

find(dr(i)) = Data(i)

End If

Else

\'------没找到,添加数据------

Dim ndr As DataRow = dt.AddNew

For i As Integer = 0 To dr.Count - 1

ndr(dr(i)) = Data(i)

End If

jdt.Value = jdt.Maximum