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

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  代码生成多个多栏数据工作表  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=180997)

--  作者：北狐
--  发布时间：2022/10/21 19:59:00
--  代码生成多个多栏数据工作表

Dim ReportDate As String =Format( e.Form.Controls("EndDate2").Value ,"yyyy年MM月") \'定义开始日期
Dim st As Date = Date.Now

\'EXCEl报表_父表数据
Dim Book As New XLS.Book(ProjectPath & "Attachments\\应收账款校准表定银价.xls")
Book.Build() \'生成细节区
Dim lj As String = ProjectPath & "" & ReportDate & "报表\\" & ReportDate & "应收账款校准表定银价.xls" \'定义保存路径及文件名
Book.Save(lj) \'保存工作簿

\'\'应付账款
\'excel模板应设置页面宽度\\高度为自动,如设置为1页,则分页无效
\'分栏分页
\'EXCEL_VBA_明细表数据
Dim tb As Table = Tables("应收账款明细表")
\'tb.Filter = "定银价<>0"
Dim nms() As String = {"客户名称", "定银价", "销售代号"}

Dim prs As Integer = 25 \'每页细节区行数
Dim fl As Integer = 3 \'每页栏数
Dim ls As Integer = 5 \'每栏列数
Dim bts As Integer = 1 \'表头数
Dim bws As Integer = 0 \'表尾数

Dim csh As Integer \'每页初始行
Dim s,g,lz,zh As Integer
zh=prs+bts+bws \'每页总行数

Dim App As New MSExcel.Application
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Open(lj)
Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
App.ScreenUpdating = False
For p As Integer = 1 To math.Ceiling(tb.Rows.Count /( fl*prs)) - 1 \'p分页
Ws.Rows("1:" & zh).Copy(Ws.Range("a" & p*zh+1))
Next

Ws.Cells.PageBreak = MSExcel.XlPageBreak.xlPageBreakNone\'清除所有分页符
Ws.Columns(fl*ls+1).PageBreak = MSExcel.XlPageBreak.xlPageBreakManual \'纵向分页

For p As Integer = 0 To math.Ceiling(tb.Rows.Count /( fl*prs)) - 1 \'p分页
csh=bts+p*zh
s = 0
g = 1

For r As Integer = P*FL*prs To math.min(tb.Rows.Count - 1,( p + 1) *fl* prs - 1) \'在每页数据内循环

lz = (Int(s/prs)) * ls+1 \'指定数据列数
If s/prs = Int((s+1)/prs) Then g = 1
ws.cells(csh+g,lz) = r+1

For m As Integer = 0 To nms.Length-1
ws.cells(csh+g,lz+m+1) = tb.rows(r)(nms(m))
Next
g = g + 1 \'每页每列各数据累计次数
s = s + 1 \'每页各数据累计次数

Next
\'msgbox((p+1)*zh+1)
Ws.Rows((p+1)*zh+1).PageBreak = MSExcel.XlPageBreak.xlPageBreakManual
Next

\'\'定银价
\'excel模板应设置页面宽度\\高度为自动,如设置为1页,则分页无效
\'分栏分页
\'EXCEL_VBA_明细表数据
Dim tb1 As Table = Tables("应收账款明细表")
tb1.Filter = "定银价<>0"
Dim nms1() As String = {"客户名称", "定银价", "销售代号"}

Dim prs1 As Integer = 25 \'每页细节区行数
Dim fl1 As Integer = 3 \'每页栏数
Dim ls1 As Integer = 5 \'每栏列数
Dim bts1 As Integer = 1 \'表头数
Dim bws1 As Integer = 0 \'表尾数

Dim csh1 As Integer \'每页初始行
Dim s1, g1, lz1, zh1 As Integer
zh = prs1 + bts1 + bws1 \'每页总行数

Dim Ws1 As MSExcel.WorkSheet = Wb.WorkSheets(2)
App.ScreenUpdating = False
For p1 As Integer = 1 To math.Ceiling(tb1.Rows.Count / ( fl1 * prs1)) - 1 \'p分页
Ws1.Rows("1:" & zh1).Copy(Ws.Range("a" & p1 * zh1 + 1))
Next

Ws1.Cells.PageBreak = MSExcel.XlPageBreak.xlPageBreakNone\'清除所有分页符
Ws1.Columns(fl1 * ls1 + 1).PageBreak = MSExcel.XlPageBreak.xlPageBreakManual \'纵向分页
MessageBox.Show(1)
For p1 As Integer = 0 To math.Ceiling(tb1.Rows.Count / ( fl1 * prs1)) - 1 \'p分页
csh1 = bts1 + p1 * zh1
s1 = 0
g1 = 1

For r1 As Integer = P1 * FL1 * prs1 To math.min(tb1.Rows.Count - 1, ( p1 + 1) * fl1 * prs1 - 1) \'在每页数据内循环

lz1 = (Int(s1 / prs1)) * ls1 + 1 \'指定数据列数
If s1 / prs1 = Int((s1 + 1) / prs1) Then g = 1
ws1.cells(csh1 + g1, lz1) = r1 + 1

For m1 As Integer = 0 To nms1.Length - 1
ws1.cells(csh1 + g1, lz1 + m1 + 11) = tb1.rows(r1)(nms1(m1))
Next
调试在下面报错
g1 = g1 + 1 \'每页每列各数据累计次数
s1 = s1 + 1 \'每页各数据累计次数

Next
\'msgbox((p1+1)*zh1+1)
Ws1.Rows((p1 + 1) * zh1 + 1).PageBreak = MSExcel.XlPageBreak.xlPageBreakManual
Next
调试在上面报错

MessageBox.Show("耗时: " & (Date.Now - st).TotalSeconds & "秒") \'计算并显示执行代码所花费的秒数
Wb.Save
App.ScreenUpdating = True
App.Visible = True

报错如下，求老师帮忙看下是哪里出错

.NET Framework 版本：4.0.30319.1026
Foxtable 版本：2022.8.18.1
错误所在事件：窗口,主界面,Button45,Click
详细错误信息：
不能设置类 Range 的 PageBreak 属性

--  作者：有点蓝
--  发布时间：2022/10/22 8:55:00
--
调试看是哪一句代码出错

--  作者：北狐
--  发布时间：2022/10/25 16:27:00
--
Ws1.Rows((p1 + 1) * zh1 + 1).PageBreak = MSExcel.XlPageBreak.xlPageBreakManual
这句

--  作者：有点蓝
--  发布时间：2022/10/25 16:36:00
--
这个代码看不出有什么问题。做个例子发上来测试一下