用VB,VBA或者其他支持old的组建导出excel文件的时候,通常需要对文件格式进行排版和美化。这里面有两种做法,一,事先用一个设计好格式的excel,打开它往里面付值。 二,使用ole自己的划线和边框调整工具。下面具体看看这种做法下的几个操作的代码
1 画边框
xlSheet.Range("A1:F4").Select
With xlApp.Selection
.Font.name = "黑体"
.Font.Size = 18
.Font.Bold = True
'.Columns.AutoFit
.Columns.ColumnWidth = 13
'.Background = 0
.Borders.LineStyle = xlContinuous '连续线
.Borders.ColorIndex = 1 '黑色
.Borders.Weight = xlThin '细线
End With
2 具体画边框的某一些线的时候
'.Borders(xlInsideVertical).LineStyle = xlContinuous
'.Borders(xlInsideVertical).ColorIndex = 1
'.Borders(xlInsideVertical).Weight = xlThin
共有8种线xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, or xlEdgeTop, xlInsideHorizontal, or xlInsideVertical.
3 合并单元格
xlSheet.Range("A1:F4").Select
With xlApp.Selection
.MergeCells = True
.HorizontalAlignment = -4108 '水平居
.VerticalAlignment = -4108 '垂直居
.Font.name = "黑体"
.Font.Size = 18
.Font.Bold = True
'.Columns.AutoFit
.Columns.ColumnWidth = 13
End With
4 导出excel时候最好的方法是直接让excel显示出来,让用户自己保存或者关闭
Dim xlApp As Excel.Application
Dim xlBook As Object
Dim xlSheet As Object
Screen.MousePointer = vbHourglass
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
'处理
xlApp.Visible = True
Screen.MousePointer = vbDefault
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing