如何给导出的excel 添加 标题 和 合计
导出代码如下:
Dim Rs_Data As New AdoDB.Recordset, Irowcount As Integer, Icolcount As Integer, ExApp As Object
Set ExApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set ExApp = CreateObject("Excel.Application")
End If
Dim xlApp As Object, xlBook As Object,xlSheet As Object, xlQuery As Object
With Rs_Data
If .State = adStateOpen Then .Close
.ActiveConnection = Conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Source = SQLofBB4 ' strOpen
.Open
End With
With Rs_Data
Irowcount = .RecordCount
'字段总数
Icolcount = .Fields.Count
End With
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks().Add
Set xlSheet = xlBook.Worksheets("sheet1")
xlApp.Visible = True
'添加查询语句,导入EXCEL数据
Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.RANGE("a1"))
With xlQuery
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = 1 ' xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
End With
xlQuery.FieldNames = True '显示字段名
xlQuery.Refresh
With xlSheet
.RANGE(.Cells(1, 1), .Cells(1, Icolcount)).Font.Name = "黑体"
.RANGE(.Cells(1, 1), .Cells(1, Icolcount)).Font.Size = 10
'设标题为黑体字
End With
With xlSheet.pagesetup
.Orientation = 2 'xlLandscape
End With
xlApp.Application.Visible = True
数据导出如下图所示:
想做成如下图所示效果
请通过 VBA 代码 添加 以下内容
1、顶部插入几行,并加上标题
2、底部加上那几个合计