实在不行,还是用以下代码,主要问题求专家解决是如何实现多个SHEET一起计算,下面的代码除了SHEET2 '垂求和'外,基本可以实现。
Dim Book As New XLS.Book(ProjectPath & "Attachments\2012 NCB Budget Actuals.xls")
Dim fl As String = ProjectPath & "Reports\2012 NCB Budget Actuals.xls"
Book.Build() '生成细节区
Dim sheet1 As XLS.Sheet = book.Sheets("FM 设施")
Dim sheet2 As XLS.Sheet = book.Sheets("人事")
Dim lastRow As Integer
For i As Integer = 0 To sheet1.Rows.count -1
If sheet1(i,15).Text = "求和" Then
sheet1(i,15).Formula = "=SUM(D" & (i+1) & ":O" & (i+1) & ")"
End If
If sheet1(i, 17).Text = "求差" Then
sheet1(i, 17).Formula = "=P" & (i+1) & "- Q" & (i+1)
End If
If sheet1(i, 18).Text = "变动" Then
sheet1(i, 18).Formula = "=IF(Q" & (i+1) & "=0,0,P" & (i+1) & "/Q" & (i+1) & ")"
End If
If sheet1(i, 3).Text = "垂求和" Then
lastRow = i
Exit For
End If
Next
Dim zm As String = "DEFGHIJKLMNOPQ"
For i As Integer = 0 To zm.Length - 1
sheet1(lastRow, i+3).Formula = "=Sum(" & zm.Chars(i) & "4:" & zm.Chars(i) & "9)"
Next
For i As Integer = 0 To sheet1.Rows.count -1
If sheet2(i,15).Text = "求和" Then
sheet2(i,15).Formula = "=SUM(D" & (i+1) & ":O" & (i+1) & ")"
End If
If sheet2(i, 17).Text = "求差" Then
sheet2(i, 17).Formula = "=P" & (i+1) & "- Q" & (i+1)
End If
If sheet2(i, 18).Text = "变动" Then
sheet2(i, 18).Formula = "=IF(Q" & (i+1) & "=0,0,P" & (i+1) & "/Q" & (i+1) & ")"
End If
If sheet2(i, 3).Text = "垂求和" Then
lastRow = i
Exit For
End If
Next
Book.Save(fl)
Dim Proc As New Process
Proc.File =fl
Proc.Start()