VB中对Excel编程操作实例

VB中对Excel编程操作实例

vb excel编程技巧,实例,格式,合并单元格,表格线,页面横向

Dim xlApp As excel.Application

Dim xlBook As excel.Workbook

Dim xlSheet As excel.Worksheet

Set xlApp = CreateObject("Excel.Application")

Set xlBook = xlApp.Workbooks.Add

Set xlSheet = xlBook.Worksheets(1)

Dim xRange As excel.Range

Dim m5 As String

Dim msgss As Boolean

'On Error GoTo 99

With xlSheet

If abcd = True Then tmp2 = "台," Else tmp2 = "个,"

If dataM > 5 Then m5 = " 数量:" & dataM & tmp2 Else m5 = " "

.Cells(1, 1).Value = lb1.Caption & m5 &

Label15.Caption

.Cells(2, 1).Value = "基本信息"

.Cells(6, 1).Value = "额定性能" & Chr(10) &

Text1.Text & "rpm"

.Cells(11, 1).Value = "堵转"

.Columns(1).ColumnWidth = 8 '表格宽度

.Columns(2).ColumnWidth = 18 '表格宽度

'.Range(.Cells(1, 1), .Cells(1, 5)).MergeCells = True '合并

.Range(.Cells(2, 1), .Cells(5, 1)).MergeCells = True '合并单元格

.Range(.Cells(6, 1), .Cells(10, 1)).MergeCells = True '合并单元格

.Range(.Cells(11, 1), .Cells(13, 1)).MergeCells = True '合并单元格

.Range(.Cells(1, 1), .Cells(13, dataM + 2)).Borders.LineStyle = xlContinuous '表格线

.Range(.Cells(2, 1), .Cells(13, dataM +

2)).HorizontalAlignment = xlCenter

.Cells(1, 1).Font.Size = 12

.Cells(1, 1) = "黑体"

.rowS(1).RowHeight = 30'行高

For i = 2 To 13

.rowS(i).RowHeight = 20

Next

For i = 0 To dataM

.Columns(i + 3).ColumnWidth = 15 '表格宽度

If i <> 0 Then .Cells(2, i + 2).Font.Size = 5

.Cells(2, i + 2).Value = Label1(i).Caption

If dataM > 5 And i <> 0 And abcd = True Then

.Cells(3, i + 2).Value = Trim(Left(Right(Label1(i).Caption, 7), 3))

Else

.Cells(3, i + 2).Value = Label2(i).Caption

End If

If i = 0 Or dataM < 6 Then .Cells(3, i + 2).Value =

Label2(i).Caption

.Cells(4, i + 2).Value = Label3(i).Caption

.Cells(5, i + 2).Value = Label4(i).Caption

.Cells(6, i + 2).Value = Label5(i).Caption

.Cells(7, i + 2).Value = Label6(i).Caption

.Cells(8, i + 2).Value = Label7(i).Caption

.Cells(9, i + 2).Value = Label8(i).Caption

.Cells(10, i + 2).Value = Label9(i).Caption

.Cells(11, i + 2).Value = Label10(i).Caption

.Cells(12, i + 2).Value = Label11(i).Caption

.Cells(13, i + 2).Value = Label12(i).Caption

Next

'设置格式

'.Columns(10).NumberFormatLocal = "0.00_ "

.rowS(6).NumberFormatLocal = "0.000_ "

.rowS(7).NumberFormatLocal = "0.0_ "

.rowS(8).NumberFormatLocal = "0.0000_ "

.rowS(9).NumberFormatLocal = "0_ "

.rowS(10).NumberFormatLocal = "0_ "

.rowS(11).NumberFormatLocal = "0.000_ "

.rowS(12).NumberFormatLocal = "0.0_ "

.rowS(13).NumberFormatLocal = "0_ "

'.rowS(7).Hidden = True

'.rowS(8).Hidden = True

'.rowS(10).Hidden = True

'.rowS(12).Hidden = True

'.Names = lb1.Caption

.PageSetup.Orientation = 2'页面为横向

End With

98

If toexcel = 1 Then

If abcd = True Then tmp2 = "台数据" Else tmp2 = "个数据"

If dataM > 5 Then