Dim dt As Table=Tables("统计表2")
Dim nms(dt.Cols.Count -1) As String
Dim caps(dt.Cols.Count-1) As String
Dim szs(dt.Cols.Count-1) As Integer
For i As Integer = 0 To Tables("统计表2").Cols.Count - 1
nms(i) = Tables("统计表2").Cols(i).name
caps(i) =Tables("统计表2").Cols(i).caption
szs(i) = 130
Next
Dim Book As New XLS.Book '定义一个Excel工作簿
Dim Sheet As XLS.Sheet = Book.Sheets(0) '引用工作簿的第一个工作表
Dim st As XLS.Style = Book.NewStyle '日期列的显示格式
st.Format = "yyyy-MM-dd"
Dim nt As XLS.Style = Book.NewStyle '日期列的显示格式
nt.Format = "0.00"
Dim x_c As String="" '记录第一层的列名
Dim k As Integer=0
Dim Style As XLS.Style = Book.NewStyle() '定义新样式
Style.AlignHorz = XLS.AlignHorzEnum.Center
此主题相关图片如下:123.png
Dim t As String = "aa"
Dim idx As Integer = 0
For c As Integer = 0 To nms.length -1
Dim ar() As String = caps(c) .split("_")
Sheet(0, c).Value = ar(0) '指定列标题 只要tables的列数超过256,这句就报错;tables列数不超过256列不报错。
Sheet.Cols(c).Width = 90
If ar.length = 2 Then
Sheet(1, c).Value = ar(1)
End If
If t <> ar(0) And c<>nms.length -1
If c - idx <> 1
Sheet.MergeCell(0,idx,1,c - idx)
Sheet(0,idx).style = Style
End If
t = ar(0)
idx = c
ElseIf c=nms.length -1 And c - idx <> 1
Sheet.MergeCell(0,idx,1,c - idx+1)
Sheet(0,idx).style = Style
End If
If dt.Cols(nms(c)).IsDate Then '如果是日期列
Sheet.Cols(c).Style = st '设置显示格式
End If
If dt.Cols(nms(c)).IsNumeric
Sheet.Cols(c).Style = nt '设置显示格式
End If
Next
For r As Integer = 0 To dt.Rows.Count - 1 '填入数据
For c As Integer = 0 To nms.length -1
If dt.rows(r).IsNull(nms(c)) = False
If dt.Cols(nms(c)).IsNumeric
Sheet(r +2, c).Value = round2(dt.rows(r)(nms(c)),2)
Else
Sheet(r +2, c).Value = dt.rows(r)(nms(c))
End If
End If
Next
Next
Dim dlg As New SaveFileDialog '定义一个新的SaveFileDialog
dlg.Filter= "Excel文件|*.xlsx" '设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then
Book.Save(dlg.FileName)
Dim Proc As New Process
Proc.File = dlg.FileName
Proc.Start()
End If
[此贴子已经被作者于2021/12/7 13:57:10编辑过]