主题：指定行

再次麻烦老师，如何限制一页最多是导出15列，30行，如果走出范围自动换页显示，打印？

Dim dt As DataTable = DataTables("工资摘要名称")
Dim t As Table = Tables("职工资料")
Dim f1 As String = (projectPath & "\模板\工资表.xls")
Dim f0 As String = (projectpath & "\工资\汇总工资")
If not FileSys.DirectoryExists(f0) Then
    FileSys.CreateDirectory(f0)
End If
Dim f2 As String = (f0 & "\工资.xls")
If FileSys.FileExists(f2) Then
    FileSys.DeleteFile(f2)
End If
FileSys.CopyFile(f1,f2, True)
Dim App As New MSExcel.Application
Dim wb As MSExcel.WorkBook = App.WorkBooks.open(f2)
Dim ws1 As MSExcel.WorkSheet = wb.WorkSheets("工资")
Dim jzkms As List(of String) = dt.GetValues("记账科目", "是否录入 = true", "序号")
Dim idx As Integer = 3
Dim rg = ws1.Range("C:C")
Dim cs As new List(of String)
For Each jzkm As String In jzkms
    Dim gzzys As List(Of String) = dt.GetValues("工资摘要", "记账科目 = '" & jzkm & "'", "序号")
    Dim pidx As Integer = idx
    For i As Integer = 0 To gzzys.count-1
        Rg.EntireColumn.Insert(MSExcel.XlInsertShiftDirection.xlShiftToRight)
        ws1.cells(2, idx).Value = gzzys(i)
        ws1.cells(2, idx).HorizontalAlignment = MSExcel.Constants.xlCenter '水平居中
       
        ws1.cells(4, idx).Formula = "=sum(" & ws1.cells(5, idx).address & ":" & ws1.cells(6,idx).address & ")"
        idx += 1
        cs.add(gzzys(i))
    Next
   
    Dim nrg = ws1.range(ws1.cells(1, pidx).address, ws1.cells(1,idx-1).address)  ''''
    nrg.Borders.Linestyle = MSExcel.XlLineStyle.xlContinuous  '边框线型
    nrg.Borders.Weight = MSExcel.XlBorderWeight.xlThin'边框粗细
    nrg.Borders.ColorIndex = 1'边框颜色
    nrg.merge
    ws1.cells(1, pidx).value = jzkm
    nRg.HorizontalAlignment = MSExcel.Constants.xlCenter '水平居中
   
   
    Dim nrg1 = ws1.range(ws1.cells(3, pidx).address, ws1.cells(3,idx-1).address)  ''''
    nrg1.Borders.Linestyle = MSExcel.XlLineStyle.xlContinuous  '边框线型
    nrg1.Borders.Weight = MSExcel.XlBorderWeight.xlThin'边框粗细
    nrg1.Borders.ColorIndex = 1'边框颜色
    nrg1.merge
    ws1.cells(3,pidx).Formula = "=sum(" & ws1.cells(4, pidx).address & ":" & ws1.cells(4,idx-1).address & ")"
    nRg1.HorizontalAlignment = MSExcel.Constants.xlCenter '水平居中
   
Next
Dim s As String = ws1.cells(1, idx).address.Substring(1,1)
ws1.range(s & ":" & s).delete
ws1.cells(3,s).Formula = "=sum(" & ws1.cells(4, 3).address & ":" & ws1.cells(4,idx).address & ")"
rg = ws1.range("6:6")
Dim nidx = 0
For Each r As Row In t.Rows
    Rg.EntireRow.Insert(MSExcel.XlInsertShiftDirection.xlShiftDown)
    ws1.cells(r.Index+5, 1).Value = r.Index+1
    ws1.cells(r.Index+5, 2).Value = r("姓名")
    ws1.cells(r.Index+5, idx).Formula = "=sum(" & ws1.cells(r.Index+5, 3).address & ":" & ws1.cells(r.Index+5,idx-1).address & ")"
    output.show(ws1.cells(r.Index+5, idx).Formula)
    For i As Integer = 0 To cs.count-1
        'ws1.cells(r.Index+5, i+3).value= DataTables("工资明细表").compute("sum(金额)", "工资摘要 = '" & cs(i) & "' and 姓名 = '" & r("姓名") & "'")
    Next
    If nidx > 0 AndAlso nidx Mod 15 = 0
        ws1.hPageBreaks.Add(ws1.Cells(r.Index+5,1))
    End If
    nidx += 1
Next
ws1.range(nidx+5 & ":" & nidx+6).Delete
Ws1.PageSetup.PrintTitleRows = Ws1.range("1:4").Address
Ws1.PageSetup.PrintArea = Ws1.UsedRange.Address   '打印工作表的使用区域
App.Visible = True
'ws1.UsedRange.Formula = ws1.UsedRange.Formula
'app.ActiveWindow.DisplayZeros = False

 

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:123456.xls

如何利用vars("cc")来显示日期？

 

<"字符日期：" & [!cc] >

可以，参考代码

 

Dim App As New MSExcel.Application
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Open("D:\test.xls")
Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
Dim rg = ws.Columns(3)
rg.delete
app.visible = True

在模式窗口进行汇总表时，主窗口总闪一下，象刷新一样，请问老师如何不出现这种状况？

 做个例子看看。

张　三　与　张三