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

错误找到了,原来是在Windows xp的电脑上报表事件设有代码删除后就正常了，与操作系统和NetFramwork无关

还有一小问题就是生成的报表村名上面是空格，实际这一栏为合计值，是否可以用代码加上“合计”。如图

 

此主题相关图片如下：2010-7-1 16-13-31.jpg

一、单个的报表可以实现用代码插入单元格“合计”，以下是分别是四个报表的代码。

With Tables("分组统计")
    .Select(0 ,0, .Rows.Count - 1, .Cols.Count - 1)
End With
If Forms("选项").Controls("ComboBox1").Value >"" Andalso Forms("选项").Controls("CheckBox2").Checked=True Then
    Dim Book As New XLS.Book(ProjectPath & "Attachments\林分属性表.xls") '打开模板
    Book.Build() '生成细节区
    Book.Save("d:\My Documents\Reports\林分属性表.xls") '保存工作簿
    Dim App As New MSExcel.Application
    Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\My Documents\Reports\林分属性表.xls")
    Dim Wd As MSExcel.Window = Wb.Windows(1)    '表示当前工作簿窗口
    Wd.DisplayZeros = False                 '不显示0值
    App.Visible = True
    Wb.Sheets(1).range("A7").Value = "合计"
    Wb.Save
Else
    Dim Book As New XLS.Book(ProjectPath & "Attachments\林分属性.xls") '打开模板
    Book.Build() '生成细节区
    Book.Save("d:\My Documents\Reports\林分属性.xls") '保存工作簿
    Dim App As New MSExcel.Application
    Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\My Documents\Reports\林分属性.xls")
    Dim Wd As MSExcel.Window = Wb.Windows(1)    '表示当前工作簿窗口
    Wd.DisplayZeros = False                 '不显示0值
    App.Visible = True
    Wb.Save
End If

 

With Tables("分组统计")
    .Select(0 ,0, .Rows.Count - 1, .Cols.Count - 1)
End With
If Forms("选项").Controls("ComboBox1").Value >"" Andalso Forms("选项").Controls("CheckBox2").Checked=True Then
    Dim Book As New XLS.Book(ProjectPath & "Attachments\插花统计表.xls") '打开模板
    Book.Build() '生成细节区
    Book.Save("d:\My Documents\Reports\插花统计表.xls") '保存工作簿
   Dim App As New MSExcel.Application
    Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\My Documents\Reports\插花统计表.xls")
    Dim Wd As MSExcel.Window = Wb.Windows(1)    '表示当前工作簿窗口
    Wd.DisplayZeros = False                 '不显示0值
    App.Visible = True
    Wb.Sheets(1).range("A5").Value = "合计"
    Wb.Save
Else
    Dim Book As New XLS.Book(ProjectPath & "Attachments\插花统计.xls") '打开模板
    Book.Build() '生成细节区
    Book.Save("d:\My Documents\Reports\插花统计.xls") '保存工作簿
    Dim App As New MSExcel.Application
    Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\My Documents\Reports\插花统计.xls")
    Dim Wd As MSExcel.Window = Wb.Windows(1)    '表示当前工作簿窗口
    Wd.DisplayZeros = False                 '不显示0值
    App.Visible = True
    Wb.Save
End If

 

 

With Tables("分组统计")
    .Select(0 ,0, .Rows.Count - 1, .Cols.Count - 1)
End With
If Forms("选项").Controls("ComboBox1").Value >"" Andalso Forms("选项").Controls("CheckBox2").Checked=True Then
    Dim Book As New XLS.Book(ProjectPath & "Attachments\林种统计表.xls") '打开模板
    Book.Build() '生成细节区
    Book.Save("d:\My Documents\Reports\林种统计表.xls") '保存工作簿
    Dim App As New MSExcel.Application
    Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\My Documents\Reports\林种统计表.xls")
    Dim Wd As MSExcel.Window = Wb.Windows(1)    '表示当前工作簿窗口
    Wd.DisplayZeros = False                 '不显示0值
    App.Visible = True
    Wb.Sheets(1).range("A6").Value = "合计"
    Wb.Save
Else
    Dim Book As New XLS.Book(ProjectPath & "Attachments\林种统计.xls") '打开模板
    Book.Build() '生成细节区
    Book.Save("d:\My Documents\Reports\林种统计.xls") '保存工作簿
    Dim App As New MSExcel.Application
    Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\My Documents\Reports\林种统计.xls")
    Dim Wd As MSExcel.Window = Wb.Windows(1)    '表示当前工作簿窗口
    Wd.DisplayZeros = False                 '不显示0值
    App.Visible = True
    Wb.Save
End If

 

With Tables("分组统计")
    .Select(0 ,0, .Rows.Count - 1, .Cols.Count - 1)
End With
If Forms("选项").Controls("ComboBox1").Value >"" Andalso Forms("选项").Controls("CheckBox2").Checked=True Then
    Dim Book As New XLS.Book(ProjectPath & "Attachments\经营类型表.xls") '打开模板
    Book.Build() '生成细节区
    Book.Save("d:\My Documents\Reports\经营类型表.xls") '保存工作簿
    Dim App As New MSExcel.Application
    Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\My Documents\Reports\经营类型表.xls")
    Dim Wd As MSExcel.Window = Wb.Windows(1)    '表示当前工作簿窗口
    Wd.DisplayZeros = False                 '不显示0值
    App.Visible = True
    Wb.Sheets(1).range("A7").Value = "合计"
    Wb.Save
Else
    Dim Book As New XLS.Book(ProjectPath & "Attachments\经营类型.xls") '打开模板
    Book.Build() '生成细节区
    Book.Save("d:\My Documents\Reports\经营类型.xls") '保存工作簿
    Dim App As New MSExcel.Application
    Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\My Documents\Reports\经营类型.xls")
    Dim Wd As MSExcel.Window = Wb.Windows(1)    '表示当前工作簿窗口
    Wd.DisplayZeros = False                 '不显示0值
    App.Visible = True
    Wb.Save
End If

 

二、怎样在四个报表一次性生成的代码增加插入单元格“合计”，"林分属性表","插花统计表","林种统计表","经营类型表"，对应插入单元格“合计”于"A7","A5","A6","A7"。

 

Dim Names() As String
If DataTables.Contains("分组统计") '如果存在分组统计
    If Forms("选项").Controls("ComboBox1").Value >"" Andalso Forms("选项").Controls("CheckBox2").Checked=True 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\Reports\"& Name & ".xls") '保存工作簿
        Dim App As New MSExcel.Application
        Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\My Documents\Reports\"& Name & ".xls")
        Dim Wd As MSExcel.Window = Wb.Windows(1)    '表示当前工作簿窗口
        Wd.DisplayZeros = False                 '不显示0值
        App.Visible = True
        Wb.Save
    Next
Else
    MessageBox.Show("先统计!", "提示")
End If

     报表事件标记要配合模板进行在相应位置标记<合计>，但是模板这个位置已经是<村名>不能动了，因此这个方法可能行不通。

     用下面的方法应该可行只是自己不会写代码，搞了多次不成功，希望大师出手！

     Dim App As New MSExcel.Application
    Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\My Documents\Reports\经营类型表.xls")
    Dim Wd As MSExcel.Window = Wb.Windows(1)    '表示当前工作簿窗口
    Wd.DisplayZeros = False                 '不显示0值
    App.Visible = True
    Wb.Sheets(1).range("A7").Value = "合计"
    Wb.Save

Dim Names() As String
If DataTables.Contains("分组统计") '如果存在分组统计
    If Forms("选项").Controls("ComboBox1").Value >"" Andalso Forms("选项").Controls("CheckBox2").Checked=True 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\Reports\"& Name & ".xls") '保存工作簿
        Dim App As New MSExcel.Application
        Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\My Documents\Reports\"& Name & ".xls")
        Dim Wd As MSExcel.Window = Wb.Windows(1)    '表示当前工作簿窗口
        Wd.DisplayZeros = False                 '不显示0值
        App.Visible = True

if Wb.Sheets(1).range("A5").Value = ""    '4个报表分别是A7,A5,A6,A7
    Wb.Sheets(1).range("A5").Value = "合计"

End if

        Wb.Save
    Next
Else
    MessageBox.Show("先统计!", "提示")
End If

 

如果蓝色字符能够智能变为A7,A5,A6,A7，就能达到目的

试过了不行的 

此主题相关图片如下：2010-8-1 20-48-33.png


此主题相关图片如下：2010-8-1 20-49-48.png

还是不行

 

此主题相关图片如下：2010-8-2 9-47-16.png

虽然不报错但“合计”的位置不正确。

此主题相关图片如下：2010-8-2 10-50-51.png

 

Dim Names() As String
If DataTables.Contains("分组统计") '如果存在分组统计
    If Forms("选项").Controls("ComboBox1").Value >"" Andalso Forms("选项").Controls("CheckBox2").Checked=True 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\Reports\"& Name & ".xls") '保存工作簿
        Dim App As New MSExcel.Application
        Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\My Documents\Reports\"& Name & ".xls")
        Dim Wd As MSExcel.Window = Wb.Windows(1)    '表示当前工作簿窗口
        Wd.DisplayZeros = False                 '不显示0值
        App.Visible = True
        If Wb.Sheets(1).range("A5").Value IsNot Nothing Then
            Wb.Sheets(1).range("A5").Value = "合计"
        End If
        If Wb.Sheets(1).range("A6").Value IsNot Nothing Then
            Wb.Sheets(1).range("A6").Value = "合计"
        End If
        If Wb.Sheets(1).range("A7").Value IsNot Nothing Then
            Wb.Sheets(1).range("A7").Value = "合计"
        End If
        Wb.Save
    Next
Else
    MessageBox.Show("先统计!", "提示")
End If

可能还要加入指定报表的代码----如果是“林分属性表”和A7空则插入“合计”

在菜单中单个报表已经搞定了，现在是按钮生成批量（多个）报表，就是不会写指定报表代码，请各位指点！

感谢e-png! 终于搞定了，到此Excel报表清除“0”值、插入“合计”完美了。

Dim Names() As String
If DataTables.Contains("分组统计") '如果存在分组统计
    If Forms("选项").Controls("ComboBox1").Value >"" Andalso Forms("选项").Controls("CheckBox2").Checked=True 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\Reports\"& Name & ".xls") '保存工作簿
        Dim App As New MSExcel.Application
        Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\My Documents\Reports\"& Name & ".xls")
        Dim Wd As MSExcel.Window = Wb.Windows(1)    '表示当前工作簿窗口
        Wd.DisplayZeros = False                 '不显示0值
        App.Visible = True
        If name = "林分属性表"  Then
            Wb.Sheets(1).range("A7").Value = "合计"
        End If
        If name = "插花统计表" Then
            Wb.Sheets(1).range("A5").Value = "合计"
        End If
        If name = "林种统计表" Then
            Wb.Sheets(1).range("A6").Value = "合计"
        End If
        If name = "经营类型表" Then
            Wb.Sheets(1).range("A7").Value = "合计"
        End If
        Wb.Save
    Next
Else
    MessageBox.Show("先统计!", "提示")
End If