主题：[求助]动态生成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

那个位置如果是空值：笨办法：

 

if Wb.Sheets(1).range("A5").Value = ""
    Wb.Sheets(1).range("A5").Value = "合计"

End if

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，就能达到目的

一共才4个，连续几个if...........then 就可以了。