主题：[分享][原创]狐表学习笔记及我做的订单查询系统思路

非常期待下次笔记！ 我是菜鸟，很想学！

楼主你好！很高兴能看到楼主的大作，你的学习思路值得我们初学者学习，我帮助文件也看了2篇了，但是还是不知道从何下手，希望楼主能继续写下去，肯定对初学者有莫大的帮助，在线等哦！

权限设置：第一张图是未设置完成的权限窗口界面，我的思路是这样的：

先建立一张权限表：

这个权限表包含字段为：

用户名，密码，分组，权限，订单新增，订单修改，订单删除(其中订单的，为逻辑列),然后在内部函数中自定义函数,授权：

'args(0)用户名
Dim tb As Table =Tables("授权")
tb.Filter = "[用户] = '" & args(0) & "'" '找出当前行
If tb.Current("订单新增") = True Then
    Tables("订单").AllowAddNew = False
Else
   Tables("订单").AllowAddNew = True
End If
If tb.Current("订单修改") = True Then
    Tables("订单").AllowEdit = True
Else
   Tables("订单").AllowEdit = False
End If
If tb.Current("产品新增") = True Then
  Forms("共享窗口").Controls("productedit").Enabled = True 
Else
Forms("共享窗口").Controls("productedit").Enabled = False
End If
'Return Nothing

然后登录自定义用户名，使用户名存放于全局代码的某个变量中，我设置为：_username，

上面的函数，只获取用户名就可以。

用法为：Functions.Execute("授权",_UserName)

此主题相关图片如下：权限.png

关于老数据批量导入思路:

比如我有很多订单数据,其中订单号唯一,而每条订单编号中,有很多条产品销售记录,怎么从表格中提取这些数据呢?

第一建立三张表,表名为:订单,订单明细,订单备注(订单表订单编号为唯一字段,订单明细为产品销售记录,以订单编号为记录,订单备注为每个编号的订单,有可能有很多条,但是打印到A4纸上,只能打印十行或者多一点,那么每张纸都有一个对当页产品的备注.)

这三个表建立完成之后,考虑如何去做导入.

 由于需要导入的表格文件很多,那么我们不能一张一张的导入,因此我们在帮助中找一下关于取值文件夹结合的例子,然后,再做一个可以自选择目录进行导入的按钮:

Dim dlg As New FolderBrowserDialog '定义文件夹选择框
Dim tx5 As WinForm.TextBox = e.Form.Controls("TextBox5") '定义文本输入框
If dlg.ShowDialog = DialogResult.Ok Then ' 如果选择完毕，点击确定
    tx5.Value = dlg.SelectedPath '文本输入框的值为，刚才选取的路径，
    Dim tb As Table =Tables("授权") '定义授权表
    tb.filter ="[用户]='" & _username & "'" '搜索授权表中用户列中的数值为当前登录用户名的字段。
    tb.Current("导入路径") = dlg.SelectedPath '然后当前行的字段导入路径为刚才选取的路径，
    DataTables("授权").save() '保存授权表。
End If

以上这段代码,可以放到按钮的click事件里，或者是图片控件的click事件里.

然后下面的导入代码比较长

pause = True '停止表事件按钮
Dim path As WinForm.TextBox = e.Form.Controls("TextBox5") '目录,
Dim cmd As new SQLCommand
cmd.C '指定数据源
Dim Listo As String
Dim filess As String
Dim d As Integer
Dim tb As Table = Tables("订单")
Dim oi As Table = Tables("订单明细") '订单明细表
Dim Listorder As String
Dim val() As String '分解字符串
For Each file As String In filesys.GetFiles(path.Value)
 ' MessageBox.Show(file)
    If right(file,3) <> "xls" Then
        MessageBox.Show(file & "文件类型不符!","提示")
        Return
    Else
        Dim Book As New XLS.Book(file)
        Dim Sheet1 As XLS.Sheet = Book.Sheets(0)
        If sheet1(0,0).Value.Replace(" ","") <> "山东鑫迪家居装饰有限公司尚品本色木门订货单" Then
          '  MessageBox.Show(sheet1(0,0).Value.Replace(" ",""))
           MessageBox.Show(file & "工作表不在第一页,请查看!")
            Return
        End If '这段代码是判断工作表是不是在第一页。以及格式是否正确。
        
        If sheet1(2,15).Value <> "" Then
            val=sheet1(2,15).Value.split(":")
            Listorder = val(1)
        ElseIf sheet1(2,14).Value <> "" Then
            val=sheet1(2,14).Value.split(":")
            Listorder = val(1)
        End If '以上判断是订单编号在那一列.
       
        cmd.CommandText = "Select 订单编号 From {订单} Where 订单编号 = '" & listorder & " ' "
        Listo = cmd.ExecuteScalar()       
        If Listo = "" Then
            '判断数据库中订单号是否存在
            tb.AddNew()
            tb.Current("订单编号") = listorder '订单编号
tb.Current("订单状态") = "已发货" '订单编号
            tb.Current("销售区域") = sheet1(4,0).Value.Remove(0,5) '销售区域
            If sheet1(4,4).Value = ""   Then '判断联系人是否为空
                tb.Current("联系人") = sheet1(4,5).Value.Remove(0,4) '联系人
            ElseIf sheet1(4,4).value.Remove(0,3) = "" Then
                tb.Current("联系人") = ""
            Else
                ' MessageBox.Show(sheet1(4,4).Value.Remove(0,3),"联系人")
                tb.Current("联系人") = sheet1(4,4).Value.Remove(0,4) '联系人
            End If
            If sheet1(4,7).Value = "" And sheet1(4,8).Value <> "" Then
               tb.Current("联系电话") = sheet1(4,8).Value.Remove(0,5) '联系电话
            Else
            tb.Current("联系电话") = sheet1(4,7).Value.Remove(0,5) '联系电话
            End If
            If sheet1(4,10).Value = "" And sheet1(4,11).Value <> "" Then
                tb.Current("安装用户") = sheet1(4,11).Value.Remove(0,5) '安装用户
            Else
            tb.Current("安装用户") = sheet1(4,10).Value.Remove(0,5) '安装用户
            End If
            If sheet1(3,14).Value = "" Then
            tb.Current("订货日期") = sheet1(3,15).Value.Remove(0,5) '订货日期
            Else
            tb.Current("订货日期") = sheet1(3,14).Value.Remove(0,5) '订货日期
            End If
            If sheet1(4,15).Value = "" Then
                tb.Current("预定交货日期") = ""
            Else
                tb.Current("预定交货日期") = sheet1(4,15).Value.Remove(0,5) '交货日期
            End If
            For i As Integer = 8 To sheet1.Rows.count - 1 '查找所有行
                Select Case sheet1(i,0).value
                    Case "订单总货款:"
                        If Integer.TryParse(sheet1(i,2).value,d) Then '如果第二列为为数字时
                            tb.Current("订单金额") = sheet1(i,2).Value '订单总金额
                        ElseIf Integer.TryParse(sheet1(i,3).value,d) Then
                            tb.Current("订单金额") = sheet1(i,3).Value '订单总金额
                        End If
                    Case "购货单位业务章/签字:"
                        tb.Current("sys_用户") = sheet1(i,10).value.Remove(0,11) '用户
                End Select
            Next
            
                 For i As Integer = 8 To sheet1.Rows.Count - 1
                If Integer.TryParse(sheet1(i,0).Value,d) AndAlso sheet1(i,0).Value <> "" AndAlso tb.Current("订单编号") <> "" AndAlso sheet1(i,19).Value <> "" Then
                    '判断第一列是否为空 是否为数字,和订单表的订单号码是否为空.
                    oi.AddNew()
                    oi.Current("订单编号")  = tb.Current("订单编号") 

                    oi.Current("门洞尺寸")  = Replace(sheet1(i,1).Value," ","") '

                    oi.Current("门框尺寸")  = Replace(sheet1(i,2).Value," ","") '

                    oi.Current("产品型号")  = Replace(sheet1(i,3).Value," ","") '

                    oi.Current("门扇_素门_材质")  = Replace(sheet1(i,4).Value," ","") '

                    oi.Current("门扇_素门_尺寸")  = Replace(sheet1(i,5).Value," ","") '

                    oi.Current("门扇_油漆_颜色")  = Replace(sheet1(i,6).Value," ","") '

                    oi.Current("门扇_玻璃_名称")  = Replace(sheet1(i,7).Value," ","") '

                    oi.Current("门扇_数量")  = Replace(sheet1(i,8).Value," ","") '

                    If Integer.TryParse(sheet1(i,14).Value,d) Then
                        oi.Current("单套价格")  = Replace(sheet1(i,14).Value," ","") '

                    End If
                    oi.Current("配件_名称")  = Replace(sheet1(i,16).Value," ","") '

                    oi.Current("配件_数量")  = Replace(sheet1(i,17).Value," ","") '

                    oi.Current("配件_单价")  = Replace(sheet1(i,18).Value," ","") '

                    If Integer.TryParse(sheet1(i,19).Value,d) Then
                    oi.Current("合计金额")  = Replace(sheet1(i,19).Value," ","") '

                    End If
                    oi.Current("输入日期")  = CDate(sheet1(3,15).Value.Remove(0,5)) '

                Else
                End If
            Next
            End If
       
        For i As Integer = 8 To sheet1.Rows.Count - 1 '对表做循环
            If Integer.TryParse(sheet1(i,19).Value,d) And sheet1(i,0).Value <> "合计" And sheet1(i,0).value = "" And sheet1(i,14).Value <> ""  Then '如果合计列可以转换为数字
                oi.AddNew()
                oi.Current("订单编号")  = tb.Current("订单编号") 

                oi.Current("单套价格")  = Replace(sheet1(i,14).Value," ","") '

                oi.Current("合计金额") = Replace(sheet1(i,19).Value," ","")
            End If
        Next
      End If
    FileSys.MoveFile(file,"C:\Users\Administrator\Desktop\订单测试\" & filesys.GetName(file))
DataTables("订单").Save()
DataTables("订单明细").Save()
Next
pause = False

 以下附件是表结构.

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:structure.rar

此主题相关图片如下：导入.png