Dim Book1 As New XLS.Book("C:\\Documents and Settings\\Administrator\\My Documents\\下载\\管理项目1\\销售清单导入标准表.xls")
Dim Sheet1 As XLS.Sheet = Book1.Sheets(0)

Dim dtb As New DataTableBuilder("销售清单")
dtb.AddDef("客户简称", Gettype(String),6)
dtb.AddDef("车型",Gettype(String),16)
dtb.AddDef("计划编号",Gettype(String),44)
dtb.AddDef("部件名称",Gettype(String),40)
dtb.AddDef("图号",Gettype(String),50)
dtb.AddDef("计划数量",Gettype(Integer))
dtb.AddDef("生产数量",Gettype(Integer))
dtb.AddDef("车号",Gettype(String),16)
dtb.AddDef("出库单编号",Gettype(String),16)
dtb.Build()

\'Tables("执行动态").StopRedraw()
Dim d As Date = Date.Today
Dim Cols1() As String = {"计划编号","出库数量"}
Dim Cols2() As String = {"计划编号","生产数量"}
For dr1 As Integer = 1 To Sheet1.Rows.Count -1
    Dim dr2 As DataRow = DataTables("执行动态").Find("计划编号 = \'" & sheet1(dr1,0).Text & "\'")
    If dr2 Is Nothing Then \'如果不存在该计划编号的产品
        MessageBox.Show("没有" & sheet1(dr1,0).Text & "的计划编号!", "提示")
    Else
        If dr2.IsNull("生产数量") OrElse dr2("生产数量") = 0 Then
            For i As Integer = 0 To Cols1.Length - 1
                dr2(Cols2(i)) = sheet1(dr1,i).Text
            Next
            Dim bh As String = "XS" & Format(d,"yyMMdd")\'生成编号的前缀
            Dim max As String
            Dim idx As Integer
            max = DataTables("执行动态").Compute("Max(出库单编号)","出库单编号 like \'" & bh & "%\'") \'取得该天最大编号
            If max > "" Then \'如果存在最大单据编号
                idx = CInt(max.Substring(bh.Length,3)) + 1 \'获得最大单据编号的后三位顺序号,并加1
            Else
                idx = 1 \'顺序号等于1
            End If
            dr2("出库单编号") = bh & Format(idx,"000")
            dr2("交付日期") = Date.Today
            Dim dr As DataRow = DataTables("销售清单").AddNew()
            dr("计划编号") = dr2("计划编号")
            dr("生产数量") =dr2("生产数量")
            dr("出库单编号") =dr2("出库单编号")
            Dim dr3 As DataRow = dr2.GetParentRow("订单")
            If  dr3 IsNot Nothing Then
                dr("客户简称") = dr3("客户简称")
                dr("车型") = dr3("车型")
                dr("部件名称") = dr3("部件名称")
                dr("图号") = dr3("图号")
            End If
        Else
            systemready = False
            Dim drr As DataRow = DataTables("执行动态").AddNew()
            For j As Integer = 0 To Cols1.Length - 1
                drr(Cols2(j)) = sheet1(dr1,j).Text
            Next
            \'     drr("出库单编号") = "XS" & Format(d,"yyMMdd")\' & Format(idx,"00")
            Dim bh As String = "XS" & Format(d,"yyMMdd")\'生成编号的前缀
            Dim max As String
            Dim idx As Integer
            max = DataTables("执行动态").Compute("Max(出库单编号)","出库单编号 like \'" & bh & "%\'") \'取得该天最大编号
            If max > "" Then \'如果存在最大单据编号
                idx = CInt(max.Substring(bh.Length)) + 1 \'获得最大单据编号的后三位顺序号,并加1
            Else
                idx = 1 \'顺序号等于1
            End If
            dr2("出库单编号") = bh & Format(idx,"000")
            dr2("交付日期") = Date.Today
            Dim drd As DataRow = DataTables("销售清单").AddNew()
            drd("计划编号") = drr("计划编号")
            drd("生产数量") =drr("生产数量")
            drd("出库单编号") =drr("出库单编号")
            Dim dr4 As DataRow = drr.GetParentRow("订单")
            If  dr4 IsNot Nothing Then
                drd("客户简称") = dr4("客户简称")
                drd("车型") = dr4("车型")
                drd("部件名称") = dr4("部件名称")
                drd("图号") = dr4("图号")
            End If
            systemReady = True
        End If
    End If
Next
MainTable= Tables("销售清单")