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

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  EXCEL报表的问题  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=77174)

--  作者：douglas738888
--  发布时间：2015/11/12 11:35:00
--  EXCEL报表的问题
老师，下面这段代码当天测试是好的，隔天打开就报错，弹出窗面是否保存，或者是REPORTS已打开，或者是正在被使用，是否是我的地址设置有误？
EXCEL报表是在WebBrowser控件上打开的

Dim Book1 As New XLS.Book(ProjectPath & "Attachments\\财务收支年报表1.xls")
Dim fl As String = ProjectPath & "Reports\\财务收支年报表1.xls"

Dim y As Integer = Date.Today.Year
Dim dt1 As New Date(y, 1, 1)
Dim dt2 As New Date(y, 12, 31)
Dim Filter As String
Filter = "<记账日期 >= #" & dt1 & "# And 记账日期 <= #" & dt2 & "#>"

Dim y1 As Integer = Date.Today.Year
Dim dt11 As New Date(y1, 1, 1)
Dim dt22 As New Date(y1, 12, 31)
Dim Filter1 As String
Filter1 = "<出款日期 >= #" & dt11 & "# And 出款日期 <= #" & dt22 & "#>"

Dim Sheet1 As XLS.Sheet = Book1.Sheets(0)
Dim Sheet2 As XLS.Sheet = Book1.Sheets(1)
Dim Sheet3 As XLS.Sheet = Book1.Sheets(2)
Sheet1(5,12).Value = filter \'写入打印条件
Sheet2(4,12).Value = filter1 \'写入打印条件
Book1.Build() \'生成报表
Book1.Save(fl)

Dim App As New MSExcel.Application
try
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Open(fl)
Dim Ws1 As MSExcel.WorkSheet = Wb.WorkSheets("Sheet1") \'指定要复制的工作表
Dim Ws2 As MSExcel.WorkSheet = Wb.WorkSheets("Sheet2")
Dim Ws3 As MSExcel.WorkSheet = Wb.WorkSheets("Sheet3")

Dim Rg As MSExcel.Range = Ws3.Range("A1:A100") \'可指定任意单元格或单元格区域
\'Rg.RowHeight = 40 \'行高40磅
Rg.ColumnWidth = 17 \'列宽20磅
Dim Rg1 As MSExcel.Range = Ws3.Range("B1:B100") \'可指定任意单元格或单元格区域
\'Rg.RowHeight = 40 \'行高40磅
Rg1.ColumnWidth = 13 \'列宽20磅
Dim Rg2 As MSExcel.Range = Ws3.Range("C1:C100") \'可指定任意单元格或单元格区域
\'Rg.RowHeight = 40 \'行高40磅
Rg2.ColumnWidth = 12 \'列宽20磅
Dim Rg3 As MSExcel.Range = Ws3.Range("D1:D100") \'可指定任意单元格或单元格区域
\'Rg.RowHeight = 40 \'行高40磅
Rg3.ColumnWidth = 11 \'列宽20磅

Ws1.UsedRange.Copy
ws3.activate
ws3.Cells(1,1).Select
ws3.paste
Ws2.UsedRange.Copy
ws3.activate
\'ws3.Cells(1,Ws1.UsedRange.Columns.Count+2).Select \'横向拷贝
ws3.Cells(Ws1.UsedRange.Rows.Count+1, 1).Select \'纵向拷贝
ws3.paste
Wb.Save
\'app.Visible = True

catch ex As exception
msgbox(ex.message)
App.Quit
End try

e.Form.Controls("WebBrowser1").AddRess = fl

--  作者：大红袍
--  发布时间：2015/11/12 11:46:00
--

在前面加上代码

e.Form.Controls("WebBrowser1").AddRess = Nothing

Dim ps As System.Diagnostics.Process() = System.Diagnostics.Process.GetProcessesByName("Excel")

For Each p As System.Diagnostics.Process In ps

    If ps.MainWindowTitle = Nothing Then p.kill

Next

--  作者：douglas738888
--  发布时间：2015/11/12 12:01:00
--
老师，代码加上后报错，“不是SYSTEM.ARRAY的成员，代码错误 If ps.MainWindowTitle = Nothing Then p.kill

我是用按钮控件写入代码的，点击按钮EXCEL模板在WebBrowser控件显示EXCEL表

--  作者：大红袍
--  发布时间：2015/11/12 12:32:00
--

e.Form.Controls("WebBrowser1").AddRess = Nothing

Dim ps As System.Diagnostics.Process() = System.Diagnostics.Process.GetProcessesByName("Excel")

For Each p As System.Diagnostics.Process In ps

    If p.MainWindowTitle = Nothing Then p.kill

Next

--  作者：客人
--  发布时间：2015/11/12 15:21:00
--
还在有问题：

老师看看我加了您上面给的代码，不知位置和代码对吗，最后一行代码加上还是原来报错和不显示EXCEL的问题，不加的话WebBrowser1不显示EXCEL表也不报错。

e.Form.Controls("WebBrowser1").AddRess = Nothing

Dim ps As System.Diagnostics.Process() = System.Diagnostics.Process.GetProcessesByName("Excel")
For Each p As System.Diagnostics.Process In ps
If p.MainWindowTitle = Nothing Then p.kill
Next

Dim Book1 As New XLS.Book(ProjectPath & "Attachments\\财务收支年报表1.xls")
Dim fl As String = ProjectPath & "Reports\\财务收支年报表1.xls"
Dim y As Integer = Date.Today.Year
Dim dt1 As New Date(y, 1, 1)
Dim dt2 As New Date(y, 12, 31)
Dim Filter As String
Filter = "<记账日期 >= #" & dt1 & "# And 记账日期 <= #" & dt2 & "#>"
Dim y1 As Integer = Date.Today.Year
Dim dt11 As New Date(y1, 1, 1)
Dim dt22 As New Date(y1, 12, 31)
Dim Filter1 As String
Filter1 = "<出款日期 >= #" & dt11 & "# And 出款日期 <= #" & dt22 & "#>"
Dim Sheet1 As XLS.Sheet = Book1.Sheets(0)
Dim Sheet2 As XLS.Sheet = Book1.Sheets(1)
Dim Sheet3 As XLS.Sheet = Book1.Sheets(2)
Sheet1(5,12).Value = filter \'写入打印条件
Sheet2(4,12).Value = filter1 \'写入打印条件
Book1.Build() \'生成报表
Book1.Save(fl)

Dim App As New MSExcel.Application
try
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Open(fl)
Dim Ws1 As MSExcel.WorkSheet = Wb.WorkSheets("Sheet1") \'指定要复制的工作表
Dim Ws2 As MSExcel.WorkSheet = Wb.WorkSheets("Sheet2")
Dim Ws3 As MSExcel.WorkSheet = Wb.WorkSheets("Sheet3")

Dim Rg As MSExcel.Range = Ws3.Range("A1:A100") \'可指定任意单元格或单元格区域
\'Rg.RowHeight = 40 \'行高40磅
Rg.ColumnWidth = 17 \'列宽20磅
Dim Rg1 As MSExcel.Range = Ws3.Range("B1:B100") \'可指定任意单元格或单元格区域
\'Rg.RowHeight = 40 \'行高40磅
Rg1.ColumnWidth = 13 \'列宽20磅
Dim Rg2 As MSExcel.Range = Ws3.Range("C1:C100") \'可指定任意单元格或单元格区域
\'Rg.RowHeight = 40 \'行高40磅
Rg2.ColumnWidth = 12 \'列宽20磅
Dim Rg3 As MSExcel.Range = Ws3.Range("D1:D100") \'可指定任意单元格或单元格区域
\'Rg.RowHeight = 40 \'行高40磅
Rg3.ColumnWidth = 11 \'列宽20磅

Ws1.UsedRange.Copy
ws3.activate
ws3.Cells(1,1).Select
ws3.paste
Ws2.UsedRange.Copy
ws3.activate
\'ws3.Cells(1,Ws1.UsedRange.Columns.Count+2).Select \'横向拷贝
ws3.Cells(Ws1.UsedRange.Rows.Count+1, 1).Select \'纵向拷贝
ws3.paste
Wb.Save
\'app.Visible = True

catch ex As exception
msgbox(ex.message)
App.Quit
End try
e.Form.Controls("WebBrowser1").AddRess = fl

--  作者：大红袍
--  发布时间：2015/11/12 15:26:00
--

加一句

Wb.Save

\'app.Visible = True

app.quit

--  作者：douglas738888
--  发布时间：2015/11/12 16:00:00
--
老师，我还是上个测试文件，帮忙指导下下，还是存在问题，谢谢！！
下载信息  [文件大小：   下载次数： ]
点击浏览该文件:测试.zip

--  作者：大红袍
--  发布时间：2015/11/12 16:03:00
--

测试没有问题啊。你把你任务管理器所有的excel.exe的进程结束，再重新测试。

--  作者：douglas738888
--  发布时间：2015/11/12 16:27:00
--

老师我关机重启，还是这样，我的是2010EXCEL

此主题相关图片如下：未标题-1.jpg

--  作者：大红袍
--  发布时间：2015/11/12 17:02:00
--

看看

重要提示：

1、如果执行上面代码的时候，没有在WebBrowser显示Excel报表，而是在一个新的IE窗口显示，那么请双击Foxtable目录下的Office.reg文件，将其添加到注册表中。

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