Dim dt As Table = Tables("计划事件")
Dim dt1 As Table = Tables("词典")
Dim Book As New XLS.Book '定义一个Excel工作簿
Dim Sheet As XLS.Sheet = Book.Sheets(0) '引用工作簿的第一个工作表
Dim Style As Xls.Style = Book.NewStyle '新建一个线框样式
Style.BorderTop = XLS.LineStyleEnum.Thin
Style.BorderBottom = XLS.LineStyleEnum.Thin
Style.BorderLeft = XLS.LineStyleEnum.Thin
Style.BorderRight = XLS.LineStyleEnum.Thin
Style.BorderColorTop = Color.Black
Style.BorderColorBottom = Color.Black
Style.BorderColorLeft = Color.Black
Style.BorderColorRight = Color.Black
Style.AlignHorz=XLS.AlignHorzEnum.Center
Style.AlignVert=XLS.AlignHorzEnum.Center
Dim St2 As Xls.Style = Book.NewStyle '新建一个时间格式化样式
St2.Format = "yyyy-MM-dd"
St2.BorderTop = XLS.LineStyleEnum.Thin
St2.BorderBottom = XLS.LineStyleEnum.Thin
St2.BorderLeft = XLS.LineStyleEnum.Thin
St2.BorderRight = XLS.LineStyleEnum.Thin
St2.BorderColorTop = Color.Black
St2.BorderColorBottom = Color.Black
St2.BorderColorLeft = Color.Black
St2.BorderColorRight = Color.Black
St2.AlignHorz=XLS.AlignHorzEnum.Center
St2.AlignVert=XLS.AlignHorzEnum.Center
'St2.FontSize = 8
Sheet.Cols(1).Width =100
Sheet.Cols(2).Width =100
Sheet.Cols(3).Width =100
For r As Integer = 0 To dt.Rows.Count - 1 '填入数据
For c As Integer = 0 To dt.Cols.Count-1
Sheet(0, c).Value ="统计表" '第一行写入表标题(从第一列到最后一列)
Sheet.MergeCell(0,0,1,c+1)
Sheet(0, c).style=style
Sheet(1, c).Value = dt.Cols(c).Name '第二行写入列标题
If r<2 Then
Sheet(r +2, c).Value = dt.rows(r)(c) '第三行开始填充数据
Sheet(r+2,c).Style = Style
Else
Sheet(r +1, c).Value = dt.rows(r)(c) '第三行开始填充数据
End If
Next
Next
For r1 As Integer = 0 To dt1.Rows.Count - 1 '填入数据
Dim cnt As Integer = Sheet.rows.count
For c1 As Integer = 0 To dt.Cols.Count-1
Sheet(r1+2, 1).Value = dt1.rows(r1)(0) '在 计划 列填入 词典 表里面的 第一列 的值
Sheet(r1+2, 4).Value = r1+1 '在 周期 列填入 序号
Next
Next
For r2 As Integer = 0 To Sheet.rows.count-1
For c1 As Integer = 0 To dt.Cols.Count-1
If dt.Cols(c1).IsDate
Sheet(r2,c1).Style = St2
Else
Sheet(r2,c1).Style = Style
End If
Next
Next
Book.Save("c:\reports\test.xls")
Dim Proc As New Process
Proc.File = "c:\reports\test.xls"
Proc.Start()
运行效果如下:
此主题相关图片如下:12122.png