代码
Dim Book As New XLS.Book '定义一个Excel工作簿
book.Sheets.RemoveAt(0)
Dim fz As String = e.Form.Controls("b").Text
Dim avgs As New List(Of Double)
For Each dc As DataCol In DataTables(fz).DataCols
avgs.add(DataTables(fz).Compute("avg(" & dc.Name & ")"))
Next
Dim zms As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
For Each tn As String In DataTables("主表").GetValues("B", "B is not null and B <> '" & fz & "'")
Dim Sheet As XLS.Sheet = Book.Sheets.Add(tn & "-" & fz) '引用工作簿的第一个工作表
Dim r As Integer = 1
Dim c As Integer = 1
sheet(0, 0).Value = tn
For Each dc As DataCol In DataTables(tn).DataCols
sheet(0, c).Value = dc.name
c += 1
Next
For Each dr As DataRow In DataTables(tn).DataRows
c = 1
For Each dc As DataCol In DataTables(tn).DataCols
sheet(r, c).Value = dr(dc.name)
c += 1
Next
r += 1
Next
r += 1
sheet(r, 0).Value = tn & "/" & fz
For Each dr As DataRow In DataTables(tn).DataRows
c = 1
For Each dc As DataCol In DataTables(tn).DataCols
sheet(r, c).Value = dr(dc.name) / avgs(c-1)
c += 1
Next
r += 1
Next
sheet(r, 0).Value = "average"
c = 1
For Each dc As DataCol In DataTables(tn).DataCols
sheet(r, c).Formula = "=AVERAGE(" & zms(c) & (r - DataTables(tn).DataRows.count + 1) & ":" & zms(c) & r &")"
c += 1
Next
r += 1
sheet(r, 0).Value = "sd"
c = 1
For Each dc As DataCol In DataTables(tn).DataCols
sheet(r, c).Formula = "=STDEV(" & zms(c) & (r - DataTables(tn).DataRows.count + 1) & ":" & zms(c) & r &")"
c += 1
Next
r += 1
sheet(r, 0).Value = "average"
c = 1
For Each dc As DataCol In DataTables(tn).DataCols
sheet(r, c).Formula = "=(" & zms(c) & (r-1) & "/" & "B" & (r-1) & ")*100"
c += 1
Next
r += 1
sheet(r, 0).Value = "sd"
c = 1
For Each dc As DataCol In DataTables(tn).DataCols
sheet(r, c).Formula = "=(" & zms(c) & (r-1) & "/" & "B" & (r-2) & ")*100"
c += 1
Next
r += 2
c = 1
sheet(r, 0).Value = fz
For Each dc As DataCol In DataTables(fz).DataCols
sheet(r, c).Value = dc.name
c += 1
Next
r += 1
For Each dr As DataRow In DataTables(fz).DataRows
c = 1
For Each dc As DataCol In DataTables(fz).DataCols
sheet(r, c).Value = dr(dc.name)
c += 1
Next
r += 1
Next
r += 1
c = 1
sheet(r, 0).Value = "平均"
For Each avg As Double In avgs
sheet(r, c).Value = avg
c += 1
Next
Next
book.Save(ProjectPath & "test.xls")
Dim proc As new Process
proc.File = ProjectPath & "test.xls"
proc.start