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