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

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  excel报表选择条件  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=71168)

--  作者：woshiabc
--  发布时间：2015/7/6 15:37:00
--  excel报表选择条件

Dim jt As String =e.Form.Controls("ComboBox1").Value \'机台
Dim chepeople As String =e.Form.Controls("ComboBox2").Value\'检查员
Dim sd As Date = e.Form.Controls("DateTimePicker1").Value
Dim ed As Date = e.Form.Controls("DateTimePicker2").Value
Dim Book As New XLS.Book(ProjectPath & "Attachments\\出筒部统计表.xls") \'打开模板
Dim fl As String = ProjectPath & "Reports\\出筒部统计表.xls"
Dim Sheet As XLS.Sheet = Book.Sheets(0)

\'Sheet(3,19).Value = "<检查时间 >= # " & sd & "# And 检查时间 <= #" & ed & "# And 机台 = \'" & jt & "\'>" \'写入打印条件
\'Sheet(3,19).Value = "< 机台 = \'" & jt & "\'>" \'写入打印条件
\'Sheet(3,19).Value = "<检查时间 >= # " & sd & "# And 检查时间 <= #" & ed & "# And 机台 = \'" & jt & "\'>" \'写入打印条件
Book.Build() \'生成报表
Book.Save(fl)
Dim Proc As New Process
Proc.File = fl
Proc.Start()
输入任意条件都可以查询到相关数据，代码要怎样写？比如说：有三个选择框（时间，机台，检查员），选择其中一个选择框或者两个，三个都可以选择相关数据要怎样写代码？（举个例子：我选择时间和检查员，不用选择机台，就可以带出相关数据，要怎样操作？）

--  作者：大红袍
--  发布时间：2015/7/6 15:39:00
--

最基本的查询

http://www.foxtable.com/help/topics/1058.htm

[此贴子已经被作者于2015/7/6 15:38:55编辑过]

--  作者：woshiabc
--  发布时间：2015/7/6 17:03:00
--
这是筛选表，但是我想制作报表

就是根据选条件，生成我自定义的报表

[此贴子已经被作者于2015/7/6 17:04:32编辑过]

--  作者：大红袍
--  发布时间：2015/7/6 17:04:00
--
一样的意思，目的是得到那个Filter就行

--  作者：woshiabc
--  发布时间：2015/7/6 17:06:00
--

Dim Filter As String
With e.Form.Controls("ComboBox1")
If .Value IsNot Nothing Then
Filter = "机台 = \'" & .Value & "\'"
End If
End With
With e.Form.Controls("ComboBox2")
If .Value IsNot Nothing Then
If Filter > "" Then
Filter = Filter & " And "
End If
Filter = Filter & "检查员 = \'" & .Value & "\'"
End If
End With

With e.Form.Controls("DateTimePicker1")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "日期 >= #" & .Value & "#"
End If
End With

With e.Form.Controls("DateTimePicker2")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "日期 <= #" & .Value & "#"
End If
End With
If Filter > "" Then
Tables("ProjectPath & "Attachments\\出筒部统计表.xls").Filter = Filter
End If
最后ProjectPath & "Attachments\\出筒部统计表.xls是我设定的报表，为什么这行代码会报错，显示这个表不存在

改成这样Tables("出筒部统计表").Filter = Filter 也是显示不存在

[此贴子已经被作者于2015/7/6 17:10:35编辑过]

--  作者：大红袍
--  发布时间：2015/7/6 17:16:00
--

Dim jt As String =e.Form.Controls("ComboBox1").Value \'机台
Dim chepeople As String =e.Form.Controls("ComboBox2").Value\'检查员
Dim sd As Date = e.Form.Controls("DateTimePicker1").Value
Dim ed As Date = e.Form.Controls("DateTimePicker2").Value
Dim Book As New XLS.Book(ProjectPath & "Attachments\\出筒部统计表.xls") \'打开模板
Dim fl As String = ProjectPath & "Reports\\出筒部统计表.xls"
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim Filter As String
With e.Form.Controls("ComboBox1")
    If .Value IsNot Nothing Then
        Filter = "机台 = \'" & .Value & "\'"
    End If
End With
With e.Form.Controls("ComboBox2")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "检查员 = \'" & .Value & "\'"
    End If
End With

With e.Form.Controls("DateTimePicker1")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "日期 >= #" & .Value & "#"
    End If
End With

With e.Form.Controls("DateTimePicker2")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "日期 <= #" & .Value & "#"
    End If
End With

If filter > "" Then
    Sheet(3,19).Value = "<" & filter & ">"
Else
    Sheet(3,19).Value = "<all>"
End If

Book.Build() \'生成报表
Book.Save(fl)
Dim Proc As New Process
Proc.File = fl
Proc.Start()

[此贴子已经被作者于2015/7/6 17:16:27编辑过]

--  作者：woshiabc
--  发布时间：2015/7/7 9:36:00
--
没数据出来

--  作者：大红袍
--  发布时间：2015/7/7 9:38:00
--
加入代码，弹出msgbox(filter)看看内容

--  作者：woshiabc
--  发布时间：2015/7/7 10:20:00
--

加入代码后，条件显示没问题

此主题相关图片如下：is8u9jv%ztv42w0)2}bgas.png

但是日期不能筛选

[此贴子已经被作者于2015/7/7 10:30:40编辑过]

--  作者：大红袍
--  发布时间：2015/7/7 10:42:00
--

试试这样改

With e.Form.Controls("DateTimePicker1")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "日期 >= #" & Format(.Value, "yyyy-MM-dd HH:mm:ss") & "#"
    End If
End With

With e.Form.Controls("DateTimePicker2")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "日期 <= #" & Format(.Value, "yyyy-MM-dd HH:mm:ss") & "#"
    End If
End With

[此贴子已经被作者于2015/7/7 10:43:05编辑过]