主题：[求助]动态生成Excel报表(搞定)

有两套EXCEL模板内容相同格式略有不同，根据选择的条件生成不同的报表。

此主题相关图片如下：植被统计表.jpg

此主题相关图片如下：植被统计.jpg

生成植被统计表的正确按钮代码：

If DataTables.Contains("分组统计") '如果存在分组统计
    Dim Names() AS String = {"结果分析表","林种统计表","面积统计表","秋季验收表","补助测算表","坡度统计表","权属统计表","设计汇总表","设计统计表","投资测报表","预算汇总表","植被统计表","验收汇总表","二期补助表"}
    For Each Name As String In Names
        With Tables("分组统计")
            .Select(0 ,0, .Rows.Count - 1, .Cols.Count - 1)
        End with
        Dim Book As New XLS.Book(ProjectPath & "Attachments\" & Name & ".xls") '打开模板
        Book.Build() '生成细节区
        Book.Save("D:\My Documents\退耕报表\"& Name & ".xls") '保存工作簿
        Dim Proc As New Process '打开工作簿
        Proc.File ="D:\My Documents\退耕报表\"& Name & ".xls"
        Proc.Start()
    Next
Else
    MessageBox.Show("先统计!", "提示")
End If

现在想根据条件自动生成报表，提示错误，要怎样写代码才能达到要求？

 

If DataTables.Contains("分组统计") '如果存在分组统计
If e.Form.Controls("ComboBox1")>"" AndAlso e.Form.Controls("ComboBox2")>"" Then
Dim Names() AS String = {"结果分析","林种统计","面积统计","秋季验收","补助测算","坡度统计","权属统计","设计汇总","设计统计","投资测报","预算汇总","植被统计","验收汇总","二期补助"}
Else
    Dim Names() AS String = {"结果分析表","林种统计表","面积统计表","秋季验收表","补助测算表","坡度统计表","权属统计表","设计汇总表","设计统计表","投资测报表","预算汇总表","植被统计表","验收汇总表","二期补助表"}
End If
    For Each Name As String In Names
        With Tables("分组统计")
            .Select(0 ,0, .Rows.Count - 1, .Cols.Count - 1)
        End with
        Dim Book As New XLS.Book(ProjectPath & "Attachments\" & Name & ".xls") '打开模板
        Book.Build() '生成细节区
        Book.Save("D:\My Documents\退耕报表\"& Name & ".xls") '保存工作簿
        Dim Proc As New Process '打开工作簿
        Proc.File ="D:\My Documents\退耕报表\"& Name & ".xls"
        Proc.Start()
    Next
Else
    MessageBox.Show("先统计!", "提示")
End If

正确的：

 

If e.Form.Controls("ComboBox1").Text > "" AndAlso e.Form.Controls("ComboBox2").Text > "" Then

 

这样也行：

 

If e.Form.Controls("ComboBox1").Value > "" AndAlso e.Form.Controls("ComboBox2").Value > "" Then

我希望你能想想这是为什么。

If DataTables.Contains("分组统计") '如果存在分组统计
    If e.Form.Controls("ComboBox1").Value>"" AndAlso e.Form.Controls("ComboBox2").Value>"" Then
        Dim Names() AS String = {"结果分析","林种统计","面积统计","秋季验收","补助测算","坡度统计","权属统计","设计汇总","设计统计","投资测报","预算汇总","植被统计","验收汇总","二期补助"}
    Else
        Dim Names() AS String = {"结果分析表","林种统计表","面积统计表","秋季验收表","补助测算表","坡度统计表","权属统计表","设计汇总表","设计统计表","投资测报表","预算汇总表","植被统计表","验收汇总表","二期补助表"}
    End If
    For Each Name As String In Names
        With Tables("分组统计")
            .Select(0 ,0, .Rows.Count - 1, .Cols.Count - 1)
        End with
        Dim Book As New XLS.Book(ProjectPath & "Attachments\" & Name & ".xls") '打开模板
        Book.Build() '生成细节区
        Book.Save("D:\My Documents\退耕报表\"& Name & ".xls") '保存工作簿
        Dim Proc As New Process '打开工作簿
        Proc.File ="D:\My Documents\退耕报表\"& Name & ".xls"
        Proc.Start()
    Next
Else
    MessageBox.Show("先统计!", "提示")
End If

此主题相关图片如下：2010-6-30 15-10-54.jpg

e.Form能在命令窗口用吗？e.Form只能用在窗口或者控件的事件中，表示触发事件的窗口，每一个事件都有自己的e参数，用于提供事件信息。

 

其他地方只能用Forms("窗口名")的。

 

If DataTables.Contains("分组统计") '如果存在分组统计
    If Forms("操作选项").Controls("ComboBox1").Value >"" AndAlso Forms("操作选项").Controls("ComboBox2").Value >"" Then
        Dim Names() AS String = {"结果分析","林种统计","面积统计","秋季验收","补助测算","坡度统计","权属统计","设计汇总","设计统计","投资测报","预算汇总","植被统计","验收汇总","二期补助"}
    Else
        Dim Names() AS String = {"结果分析表","林种统计表","面积统计表","秋季验收表","补助测算表","坡度统计表","权属统计表","设计汇总表","设计统计表","投资测报表","预算汇总表","植被统计表","验收汇总表","二期补助表"}
    End If
    For Each Name As String In Names
        With Tables("分组统计")
            .Select(0 ,0, .Rows.Count - 1, .Cols.Count - 1)
        End with
        Dim Book As New XLS.Book(ProjectPath & "Attachments\" & Name & ".xls") '打开模板
        Book.Build() '生成细节区
        Book.Save("D:\My Documents\退耕报表\"& Name & ".xls") '保存工作簿
        Dim Proc As New Process '打开工作簿
        Proc.File ="D:\My Documents\退耕报表\"& Name & ".xls"
        Proc.Start()
    Next
Else
    MessageBox.Show("先统计!", "提示")
End If

此主题相关图片如下：2010-6-30 15-49-30.jpg

帮助提示很清楚啊，Names没有定义，变量是有作用范围的，帮助有专门的一章讲述这个问题的

 

应该在最前面定义数组：

 

Dim Names() As String

 

中间：

 

Names =  New String () {"a"，"b"，"c"}

 

通过这几个问题可以看出，老兄基本功还有点欠缺，最好补习帮助文件两三遍，必须细读。

不好意思！只会拿来套用，要灵活掌握代码不可能了（奔五的人）只熟悉业务。请贺老师点透算了

呵呵，本坛的版主就有奔五的呢。

可能只一步之遥，自己无解了。有一个笨办法就是增加一个按钮人工判断。

Dim Names() As String

If DataTables.Contains("分组统计") '如果存在分组统计
    If Forms("操作选项").Controls("ComboBox1").Value >"" AndAlso Forms("操作选项").Controls("ComboBox2").Value >"" Then
        Names =  New String() {"结果分析","林种统计","面积统计","秋季验收","补助测算","坡度统计","权属统计","设计汇总","设计统计","投资测报","预算汇总","植被统计","验收汇总","二期补助"}
    Else
        Names = New String() {"结果分析表","林种统计表","面积统计表","秋季验收表","补助测算表","坡度统计表","权属统计表","设计汇总表","设计统计表","投资测报表","预算汇总表","植被统计表","验收汇总表","二期补助表"}
    End If
    For Each Name As String In Names
        With Tables("分组统计")
            .Select(0 ,0, .Rows.Count - 1, .Cols.Count - 1)
        End with
        Dim Book As New XLS.Book(ProjectPath & "Attachments\" & Name & ".xls") '打开模板
        Book.Build() '生成细节区
        Book.Save("D:\My Documents\退耕报表\"& Name & ".xls") '保存工作簿
        Dim Proc As New Process '打开工作簿
        Proc.File ="D:\My Documents\退耕报表\"& Name & ".xls"
        Proc.Start()
    Next
Else
    MessageBox.Show("先统计!", "提示")
End If