'''
Dim dlg As New OpenFileDialog '定义一个新的OpenFileDialog
dlg.Filter= "Excel文件|*.xlsx" '设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then '如果用户单击了确定按钮
MessageBox.Show("你选择的是:" & dlg.FileName,"提示") '提示用户选择的文件
Dim Str1 As String = dlg.FileName
Dim fx As String
If Str1 > "" AndAlso Str1.Contains("返修") Then
fx = "返修单"
End If
Dim Book As New XLS.Book(Str1)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim s As String = Sheet(1,3).Value
Dim ifo As new FileInfo(Str1)
Dim Time As Date = format(ifo.LastWriteTime,"T")
Dim d As Date = cdate(s.Split(":")(1)) & " " & Time '.replace("/","-")
Dim s1() As String = {"箱号","品类","款号","XXXS","XXS","XS","S","M","L","XL","XXL","XXXL","F","60","65","70","75","80","85","90","95","100","105","110","115","120","125","130","135","140","145","150","155","160","165","170","175","180","185","190","195","200"}
Dim dict As new Dictionary(of String,Integer)
For i As Integer = 0 To Sheet.Cols.Count - 1
Dim k As Integer = Array.IndexOf(s1, Sheet(2,i).Text)
If k > -1
dict.Add(s1(k),i)
End If
Next
Dim hs As Integer
Dim lxs As Integer
Dim d2 As new Dictionary(of String,Integer)
Dim d3 As new Dictionary(of String,String)
For i As Integer = 3 To Sheet.Rows.Count - 1
Dim pl As String = Sheet(i,dict("品类")).text
If pl > "" Then
Dim kh As String = Sheet(i,dict("款号")).text
If d3.ContainsKey(kh) = False Then d3.Add(kh,pl)
Dim sum As Integer = 0
For Each key As String In dict.Keys
If key = "箱号" OrElse key = "品类" OrElse key = "款号" Then Continue For
sum += val(Sheet(i,dict(key)).text)
Next
Dim xh As String = Sheet(i,dict("箱号")).text
If xh.Contains("~") Then
Dim s3() As String = xh.split("~")
Dim h As Integer = val(s3(1)) - val(s3(0)) + 1
lxs = h
sum = sum * h
End If
If d2.ContainsKey(kh) Then
d2(kh) = d2(kh) + sum
Else
d2.Add(kh,sum)
End If
End If
Next
Dim r As Row
Dim bhq As String
For Each key As String In d2.Keys
Dim drs As DataRow
drs = DataTables("表A").Find("款号 = '" & key.ToUpper() & "'and 出货件数 = '" & d2(key) & "' and 出货日期 = '" & d & "' ")
If drs Is Nothing Then
r = Tables("表A").AddNew
Dim khmc As String
khmc = FileSys.GetName(dlg.FileName).SubString(0,3)
r("客户名称") = khmc
r("出货日期") = d
r("款式名称") = d3(key)
r("款号") = key.ToUpper()
'r("箱数") = hs
r("出货件数") = d2(key)
r("分仓信息") = fx
Dim dq As Date = Format( cdate(r("出货日期")),"yyyy-MM-dd")
Dim bkh As String = r("款号").SubString(r("款号").Length - 3)
Dim bh As String ="BYCF-" & cdate(s.Split(":")(1)) & "-" & key.SubString(key.Length - 3)
If r("出货编号").StartsWith(bh) = False '如果单据编号前缀不符
Dim max As String
Dim idx As Integer
Dim flt As String
flt = "BYCF-"' And " & dq & " = #" & cdate(s.Split(":")(1)) & "# And " & bkh & " = " & key.SubString(key.Length - 3) & " And [_Identify] <> " & r("_Identify")
max = Tables("表A").Compute("Max(出货编号)",flt) '取得该月的相同工程代码的最大单据编号
If max > "" Then '如果存在最大单据编号
idx = CInt(max.Substring(12,4)) + 1 '获得最大单据编号的后四位顺序号,并加1
Else
idx = 1 '否则顺序号等于1
End If
r("出货编号") = bh & Format(idx,"0")
End If
End If
Next
End If
其他都好了,现在出现2个问题:
1、箱数的代码不会,能不能帮忙一下
2、如果没有箱号则数据不会导入到表A
以上问题能不能帮忙改改