贴出我的代码,很好用,有兴趣的朋友,可以试一试。
Dim flg As New SaveExcelFlags
'flg.RowNumber = True
flg.CellStyle = True
Dim t As Table=Tables("" & e.form.name & "_table1")
'获取可见列的列数
Dim n As Integer=0
For Each c As col In t.cols
If c.visible=True
n=n+1
End If
Next
n=math.min(n,t.cols.count)
t.SaveExcel("E:\表A.Xls","表A",flg)
Dim App As New MSExcel.Application
App.Visible = True
Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("E:\表A.Xls")
Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
Dim Rg As MSExcel.Range
For Each c As col In t.cols
If c.Isnumeric AndAlso c.caption<>"年" AndAlso c.caption<>"月" AndAlso c.caption<>"日"
Ws.Range(Ws.Cells(1,c.Index+1), Ws.Cells(100,c.Index+1)).NumberFormat = "#,##0.00" '会计专用
End If
Next
'设置标题
Rg = Ws.Range("1:1")
Rg.Insert(MSExcel.XlInsertShiftDirection.xlShiftDown)
Dim Rg1 As MSExcel.Range
Rg1 = Ws.Range(Ws.Cells(1,1), Ws.Cells(1,n)) '设置标题的范围
App.DisplayAlerts = False
Rg1.Merge '合并
Rg1.Value =e.Form.controls("表名称").text '设置标题
rg1.HorizontalAlignment = MSExcel.Constants.xlCenter '水平居中
With Rg1.Font
.Name = "黑体" '字体
.Size = 20 '字号
.Bold = True '加粗
.Italic = True '斜体
' .ColorIndex = 3 '颜色 '标题设置字体等
End With
Rg1.EntireColumn.AutoFit '自动调整列宽
Rg1.EntireRow.AutoFit
Rg1 .WrapText =True '引用单个单元格
'设置日期
Rg = Ws.Range("2:2")
Rg.Insert(MSExcel.XlInsertShiftDirection.xlShiftDown)
'Dim Rg1 As MSExcel.Range
Rg1 = Ws.Range(Ws.Cells(2,1), Ws.Cells(2,n)) '设置标题的范围
App.DisplayAlerts = False
Rg1.Merge '合并
Rg1.Value =e.Form.controls("日期").text '设置标题
rg1.HorizontalAlignment = MSExcel.Constants.xlCenter '水平居中
With Rg1.Font
.Name = "黑体" '字体
.Size = 12 '字号
'.Bold = True '加粗
'.Italic = True '斜体
' .ColorIndex = 3 '颜色 '标题设置字体等
End With
Rg1.EntireColumn.AutoFit '自动调整列宽
Rg1.EntireRow.AutoFit
Rg1 .WrapText =True '引用单个单元格
Ws.PrintPreview '预览