Foxtable(狐表) → 用户栏目 → 专家坐堂 → EXCEL的问题


  共有3963人关注过本帖树形打印复制链接

主题：EXCEL的问题
帅哥哟，离线，有人找我吗？
hongye
 小大  31楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：437 积分：2943 威望：0 精华：0 注册：2011/3/15 12:49:00 		  发帖心情 Post By：2021/4/12 11:19:00 [只看该作者]

1、表A的出货编号列为：BYCH-出货日期-款号后2位+自动编号1位
2、表A的客户名称列为：文件名的款号前面的字符
3、表A的箱数是：每个款号的箱数 比如 9C4100561 总共6箱 （1，2~6）
4、表A的数量是：每个款号的总和数量 比如 9C4100561 总共361件 （1是51，2~6是5*62，共361件）
5、表A的出货日期列为：出货日期加上文件的修改日期
6、表A的分仓信息为：文件名的款号后面的“返修”字符（如果有就填入，没有就为空）

以上筛选是按照如果表A的客户名称、款号和出货日期和要导入的数据相同就不导入
 回到顶部
帅哥哟，离线，有人找我吗？
hongye
 小大  32楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：437 积分：2943 威望：0 精华：0 注册：2011/3/15 12:49:00 		  发帖心情 Post By：2021/4/12 11:22:00 [只看该作者]

还有因为规格不确定
Dim s1() As String = {"品类","款号","XS","S","M","L","XL","XXL"}里的"XS","S","M","L","XL","XXL"是变化的，需要考虑
 回到顶部
帅哥哟，离线，有人找我吗？
有点蓝
 小大  33楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：超级版主 帖子：105948 积分：538809 威望：0 精华：9 注册：2015/6/24 9:21:00 		  发帖心情 Post By：2021/4/12 11:46:00 [只看该作者]

1、编号自行合成
2、6、文件名的处理参考：http://www.foxtable.com/webhelp/topics/1338.htm
3、4：拆分一下获取箱数，然后在计算，比如
dim m as string = "2~6"
dim s2() as string = m.split("~")
dim h as integer = val(s2(1)) - val(s2(0))  + 1
msgbox(“箱数=” & h)
5、表A的出货日期列为：出货日期加上文件的修改日期  -- 这个没看懂什么意思，举例说明一下

7、还有因为规格不确定
Dim s1() As String = {"品类","款号","XS","S","M","L","XL","XXL","把所有可能的规格补充到后面即可.....}
 回到顶部
帅哥哟，离线，有人找我吗？
hongye
 小大  34楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：437 积分：2943 威望：0 精华：0 注册：2011/3/15 12:49:00 		  发帖心情 Post By：2021/4/12 12:00:00 [只看该作者]

出货日期
For i As Integer = 1 To x
        For j As Integer = 1 To ColMax
            Rg1 = Ws.Cells(i,j)
            If Rg1.text.Contains("出货日期") Then
                x2 = i
                y2 = j
                Dim chr As String
                Dim chr1 As String
                chr = Ws.Cells(i,j).Value.SubString(5).Trim(" ").Replace("/","-")
                Dim Day As Date = chr
                chr1 = Format(Day,"d")
                Dim ifo As new FileInfo(dlg.FileName)
                Dim Time As Date = format(ifo.LastWriteTime,"T")
                chrq = chr1 & " " & Time
                MessageBox.Show(chrq)
            End If
        Next
[此贴子已经被作者于2021/4/12 12:00:46编辑过]
 回到顶部
帅哥哟，离线，有人找我吗？
有点蓝
 小大  35楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：超级版主 帖子：105948 积分：538809 威望：0 精华：9 注册：2015/6/24 9:21:00 		  发帖心情 Post By：2021/4/12 12:04:00 [只看该作者]

那是：出货日期日期部分加上文件的修改日期的时间部分吧，就按照上面的方式获取即可

 回到顶部
帅哥哟，离线，有人找我吗？
hongye
 小大  36楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：437 积分：2943 威望：0 精华：0 注册：2011/3/15 12:49:00 		  发帖心情 Post By：2021/4/12 14:44:00 [只看该作者]

'''
Dim Book As New XLS.Book("C:\Users\朱建斌\Documents\Foxtable项目\PDF读取项目\箱单\伊芙丽\伊芙丽9c4100561.xlsx")
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim s As String = Sheet(1,3).Value
Dim d As Date = cdate(s.Split("：")(1)) '.replace("/","-")
Output.Show(d)
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 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 j As Integer = 2 To s1.Length - 1
            sum += val(Sheet(i,dict(s1(j))).text)
        Next
        If d2.ContainsKey(kh) Then
            d2(kh) = d2(kh) + sum
        Else
            d2.Add(kh,sum)
        End If
    End If
Next
Dim r As Row
For Each key As String In d2.Keys
    r = Tables("表A").AddNew
    r("款式名称") = d3(key)
    r("款号") = key
    r("数量") = d2(key)
    r("出货日期") = d
Next

图片点击可在新窗口打开查看此主题相关图片如下：01.jpg
图片点击可在新窗口打开查看
这是怎么回事
 回到顶部
帅哥哟，离线，有人找我吗？
hongye
 小大  37楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：437 积分：2943 威望：0 精华：0 注册：2011/3/15 12:49:00 		  发帖心情 Post By：2021/4/12 14:45:00 [只看该作者]

还有箱数里面没有代码，怎么写？

 回到顶部
帅哥哟，离线，有人找我吗？
有点蓝
 小大  38楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：超级版主 帖子：105948 积分：538809 威望：0 精华：9 注册：2015/6/24 9:21:00 		  发帖心情 Post By：2021/4/12 15:15:00 [只看该作者]

'''
Dim Book As New XLS.Book("D:\问题\伊芙丽9c4100561.xlsx")
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim s As String = Sheet(1,3).Value
Dim d As Date = cdate(s.Split("：")(1)) '.replace("/","-")
Output.Show(d)
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 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
            sum = sum * h
        End If
        Output.Show(sum)
        If d2.ContainsKey(kh) Then
            d2(kh) = d2(kh) + sum
        Else
            d2.Add(kh,sum)
        End If
    End If
Next

Dim r As Row
For Each key As String In d2.Keys
    r = Tables("表A").AddNew
    r("款式名称") = d3(key)
    r("款号") = key
    r("箱数") = d2(key)
    r("出货日期") = d
Next
[此贴子已经被作者于2021/4/12 15:25:47编辑过]
 回到顶部
帅哥哟，离线，有人找我吗？
hongye
 小大  39楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：437 积分：2943 威望：0 精华：0 注册：2011/3/15 12:49:00 		  发帖心情 Post By：2021/4/12 15:22:00 [只看该作者]


图片点击可在新窗口打开查看此主题相关图片如下：02.jpg
图片点击可在新窗口打开查看
 回到顶部
帅哥哟，离线，有人找我吗？
有点蓝
 小大  40楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：超级版主 帖子：105948 积分：538809 威望：0 精华：9 注册：2015/6/24 9:21:00 		  发帖心情 Post By：2021/4/12 15:26:00 [只看该作者]

'''
Dim Book As New XLS.Book("D:\问题\伊芙丽9c4100561.xlsx")
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim s As String = Sheet(1,3).Value
Dim d As Date = cdate(s.Split("：")(1)) '.replace("/","-")
Output.Show(d)
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 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
            sum = sum * h
        End If
        Output.Show(sum)
        If d2.ContainsKey(kh) Then
            d2(kh) = d2(kh) + sum
        Else
            d2.Add(kh,sum)
        End If
    End If
Next

Dim r As Row
For Each key As String In d2.Keys
    r = Tables("表A").AddNew
    r("款式名称") = d3(key)
    r("款号") = key
    r("箱数") = d2(key)
    r("出货日期") = d
Next
 回到顶部
总数 46 上一页 1 2 3 4 5 下一页
RSS2.0 | Xhtml无图版 | Xslt无图版 Copyright © 2000 - 2018 foxtable.com Tel: 4000-810-820 粤ICP备11091905号
   Powered By Dvbbs Version 8.3.0
页面执行时间 0.04883 秒, 4 次数据查询