EXCEL VBA編程的一些小結_自動測試腳本語言

EXCEL VBA編程的一些小結

發表于：2007-04-28來源：作者：點擊數：標簽：編程 VBA 小結最近 Excel

最近單位內部的項目里要用到些報表EXCEL的生成，雖說JAVA 的POI可以有這能力，但覺得還是可能比較麻煩，因此還是轉用 .net 來搞，用visual studio 2003配合office 2003，用到了一些VBA，因此小結并歸納之，選了些資料歸納在這里，以備今后查考首先創建 Excel

最近單位內部的項目里要用到些報表EXCEL的生成，雖說JAVA 的POI可以有這能力，但覺得還是可能比較麻煩，因此還是轉用.net來搞，用visual studio 2003配合office 2003，用到了一些VBA，因此小結并歸納之，選了些資料歸納在這里，以備今后查考

首先創建 Excel 對象，使用ComObj:

Dim ExcelID as Excel.Application

Set ExcelID as new Excel.Application

1) 顯示當前窗口：

ExcelID.Visible := True;

2) 更改 Excel 標題欄：

ExcelID.Caption := '應用程序調用 Microsoft Excel';

3) 添加新工作簿：

ExcelID.WorkBooks.Add;

4) 打開已存在的工作簿：

ExcelID.WorkBooks.Open( 'C:\Excel\Demo.xls' );

5) 設置第2個工作表為活動工作表：

ExcelID.WorkSheets[2].Activate;

或 ExcelID.WorkSheets[ 'Sheet2' ].Activate;

6) 給單元格賦值：

ExcelID.Cells[1,4].Value := '第一行第四列';

7) 設置指定列的寬度（單位：字符個數），以第一列為例：

ExcelID.ActiveSheet.Columns[1].ColumnsWidth := 5;

8) 設置指定行的高度（單位：磅）（1磅＝0.035厘米），以第二行為例：

ExcelID.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米

9) 在第8行之前插入分頁符：

ExcelID.WorkSheets[1].Rows[8].PageBreak := 1;

10) 在第8列之前刪除分頁符：

ExcelID.ActiveSheet.Columns[4].PageBreak := 0;

11) 指定邊框線寬度：

ExcelID.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;

1-左 2-右 3-頂 4-底 5-斜( \ ) 6-斜( / )

12) 清除第一行第四列單元格公式：

ExcelID.ActiveSheet.Cells[1,4].ClearContents;

13) 設置第一行字體屬性：

ExcelID.ActiveSheet.Rows[1].Font.Name := '隸書';

ExcelID.ActiveSheet.Rows[1].Font.Color := clBlue;

ExcelID.ActiveSheet.Rows[1].Font.Bold := True;

ExcelID.ActiveSheet.Rows[1].Font.UnderLine := True;

14) 進行頁面設置：

a.頁眉：

ExcelID.ActiveSheet.PageSetup.CenterHeader := '報表演示';

b.頁腳：

ExcelID.ActiveSheet.PageSetup.CenterFooter := '第&P頁';

c.頁眉到頂端邊距2cm：

ExcelID.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;

d.頁腳到底端邊距3cm：

ExcelID.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;

e.頂邊距2cm：

ExcelID.ActiveSheet.PageSetup.TopMargin := 2/0.035;

f.底邊距2cm：

ExcelID.ActiveSheet.PageSetup.BottomMargin := 2/0.035;

g.左邊距2cm：

ExcelID.ActiveSheet.PageSetup.LeftMargin := 2/0.035;

h.右邊距2cm：

ExcelID.ActiveSheet.PageSetup.RightMargin := 2/0.035;

i.頁面水平居中：

ExcelID.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;

j.頁面垂直居中：

ExcelID.ActiveSheet.PageSetup.CenterVertically := 2/0.035;

k.打印單元格網線：

ExcelID.ActiveSheet.PageSetup.PrintGridLines := True;

15) 拷貝操作：

a.拷貝整個工作表：

ExcelID.ActiveSheet.Used.Range.Copy;

b.拷貝指定區域：

ExcelID.ActiveSheet.Range[ 'A1:E2' ].Copy;

c.從A1位置開始粘貼：

ExcelID.ActiveSheet.Range.[ 'A1' ].PasteSpecial;

d.從文件尾部開始粘貼：

ExcelID.ActiveSheet.Range.PasteSpecial;

16) 插入一行或一列：

a. ExcelID.ActiveSheet.Rows[2].Insert;

b. ExcelID.ActiveSheet.Columns[1].Insert;

17) 刪除一行或一列：

a. ExcelID.ActiveSheet.Rows[2].Delete;

b. ExcelID.ActiveSheet.Columns[1].Delete;

18) 打印預覽工作表：

ExcelID.ActiveSheet.PrintPreview;

19) 打印輸出工作表：

ExcelID.ActiveSheet.PrintOut;

20) 工作表保存：

If not ExcelID.ActiveWorkBook.Saved then

ExcelID.ActiveSheet.PrintPreview

End if

21) 工作表另存為：

ExcelID.SaveAs( 'C:\Excel\Demo1.xls' );

22) 放棄存盤：

ExcelID.ActiveWorkBook.Saved := True;

23) 關閉工作簿：

ExcelID.WorkBooks.Close;

24) 退出 Excel：

ExcelID.Quit;

25) 設置工作表密碼:

ExcelID.ActiveSheet.Protect "123", DrawingObjects:=True, Contents:=True, Scenarios:=True

26) EXCEL的顯示方式為最大化

ExcelID.Application.WindowState = xlMaximized

27) 工作薄顯示方式為最大化

ExcelID.ActiveWindow.WindowState = xlMaximized

28) 設置打開默認工作薄數量

ExcelID.SheetsInNewWorkbook = 3

29) '關閉時是否提示保存(true 保存；false 不保存)

ExcelID.DisplayAlerts = False

30) 設置拆分窗口,及固定行位置

ExcelID.ActiveWindow.SplitRow = 1

ExcelID.ActiveWindow.FreezePanes = True

31) 設置打印時固定打印內容

ExcelID.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"

32) 設置打印標題

ExcelID.ActiveSheet.PageSetup.PrintTitleColumns = ""

33) 設置顯示方式(分頁方式顯示)

ExcelID.ActiveWindow.View = xlPageBreakPreview

34) 設置顯示比例

ExcelID.ActiveWindow.Zoom = 100

35) 讓Excel 響應 DDE 請求

Ex.Application.IgnoreRemoteRequests = False

用VB操作EXCEL

Private Sub Command3_Click()

On Error GoTo err1

Dim i As Long

Dim j As Long

Dim objExl As Excel.Application '聲明對象變量

Me.MousePointer = 11 '改變鼠標樣式

Set objExl = New Excel.Application '初始化對象變量

objExl.SheetsInNewWorkbook = 1 '將新建的工作薄數量設為1

objExl.Workbooks.Add '增加一個工作薄

objExl.Sheets(objExl.Sheets.Count).Name = "book1" '修改工作薄名稱

objExl.Sheets.Add , objExl.Sheets("book1") ‘增加第二個工作薄在第一個之后

objExl.Sheets(objExl.Sheets.Count).Name = "book2"

objExl.Sheets.Add , objExl.Sheets("book2") ‘增加第三個工作薄在第二個之后

objExl.Sheets(objExl.Sheets.Count).Name = "book3"

objExl.Sheets("book1").Select '選中工作薄<book1>

For i = 1 To 50 '循環寫入數據

For j = 1 To 5

If i = 1 Then

objExl.Selection.NumberFormatLocal = "@" '設置格式為文本

objExl.Cells(i, j) = " E " & i & j

Else

objExl.Cells(i, j) = i & j

End If

objExl.Rows("1:1").Select '選中第一行

objExl.Selection.Font.Bold = True '設為粗體

objExl.Selection.Font.Size = 24 '設置字體大小

objExl.Cells.EntireColumn.AutoFit '自動調整列寬

objExl.ActiveWindow.SplitRow = 1 '拆分第一行

objExl.ActiveWindow. SplitColumn = 0 '拆分列

objExl.ActiveWindow.FreezePanes = True '固定拆分 objExl.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1" '設置打印固定行

objExl.ActiveSheet.PageSetup.PrintTitleColumns = "" '打印標題 objExl.ActiveSheet.PageSetup.RightFooter = "打印時間: " & _

Format(Now, "yyyy年mm月dd日 hh:MM:ss")

objExl.ActiveWindow.View = xlPageBreakPreview '設置顯示方式

objExl.ActiveWindow.Zoom = 100 '設置顯示大小

'給工作表加密碼

objExl.ActiveSheet.Protect "123", DrawingObjects:=True, _

Contents:=True, Scenarios:=True

objExl.Application.IgnoreRemoteRequests = False

objExl.Visible = True '使EXCEL可見

objExl.Application.WindowState = xlMaximized 'EXCEL的顯示方式為最大化

objExl.ActiveWindow.WindowState = xlMaximized '工作薄顯示方式為最大化

objExl.SheetsInNewWorkbook = 3 '將默認新工作薄數量改回3個

Set objExl = Nothing '清除對象

Me.MousePointer = 0 '修改鼠標

Exit Sub

err1:

objExl.SheetsInNewWorkbook = 3

objExl.DisplayAlerts = False '關閉時不提示保存

objExl.Quit '關閉EXCEL

objExl.DisplayAlerts = True '關閉時提示保存

Set objExl = Nothing

Me.MousePointer = 0

End Sub

一般在搞透視表時，是先用錄制宏的方法來實現的，當然可以再看下代碼
Dim excel As Excel.Application
        Dim xBk As Excel._Workbook
        Dim xSt As Excel._Worksheet
        Dim xRange As Excel.Range
        Dim xPivotCache As Excel.PivotCache
        Dim xPivotTable As Excel.PivotTable
        Dim xPivotField As Excel.PivotField
        Dim cnnsr As String, sql As String
        Dim RowFields() As String = {"", "", ""}
        Dim PageFields() As String = {"", "", "", "", "", ""}
        'SERVER     是服務器名或服務器的IP地址
        'DATABASE 是數據庫名
        'Table           是表名

        Try
            ' 開始導出
            cnnsr = "ODBC;DRIVER=SQL Server;SERVER=" + SERVER
            cnnsr = cnnsr + ";UID=;APP=Report Tools;WSID=ReportClient;DATABASE=" + DATABASE
            cnnsr = cnnsr + ";Trusted_Connection=Yes"

            excel = New Excel.ApplicationClass
            xBk = excel.Workbooks.Add(True)
            xSt = xBk.ActiveSheet

            xRange = xSt.Range("A4")
            xRange.Select()

            ' 開始
            xPivotCache = xBk.PivotCaches.Add(SourceType:=2)
            xPivotCache.Connection = cnnsr
            xPivotCache.CommandType = 2

            sql = "select * from " + Table

            xPivotCache.CommandText = sql
            xPivotTable = xPivotCache.CreatePivotTable(TableDestination:="Sheet1!R3C1", TableName:="數據透視表1", DefaultVersion:=1)

            '準備行字段
            RowFields(0) = "字段1"
            RowFields(1) = "字段2"
            RowFields(2) = "字段3"
            '準備頁面字段
            PageFields(0) = "字段4"
            PageFields(1) = "字段5"
            PageFields(2) = "字段6"
            PageFields(3) = "字段7"
            PageFields(4) = "字段8"
            PageFields(5) = "字段9"
            xPivotTable.AddFields(RowFields:=RowFields, PageFields:=PageFields)

            xPivotField = xPivotTable.PivotFields("數量")
            xPivotField.Orientation = 4

            ' 關閉工具條
            'xBk.ShowPivotTableFieldList = False
            'excel.CommandBars("PivotTable").visible = False

            excel.Visible = True

        Catch ex As Exception
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            xBk.Close(0)
            excel.Quit()
            MessageBox.Show(ex.Message, "報表工具", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End Try
又如：
PivotCaches.Add利用快取記憶體中快速運算建立一個樞紐分析表。須傳遞二個參數，如下：
SourceType：xlDatabase，Excel清單或資料庫。
xlExternal，外部資料庫。xlConsolidation，多種彙總資料範圍。 xlPivotTable，別的樞紐分析表。
SourceData：資料來源。
步驟2，指定資料來源為目前的工作表。
CreatePivotTable，參數如下：
TableDestination ：必須參數，指定樞紐分析表的列印位置。
TableName ：選擇性，樞紐分析表名稱。
步驟3，指定樞紐分析表列印位置，在CreatePivotTable的TableDestination 指定。
版面配置。指定每個區塊顯示的欄位。
PivotTables("Pivot1").AddFields，參數如下：
RowFields：指定列(R)區塊的欄位。
ColumnFields：指定欄(C)區塊對映的欄位。
PageFields：指定頁(P)區塊的欄位。

Sub Macro1()
' Macro1 巨集表
'
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'依產品類別查詢銷售人員月銷售量'!R1C1:R356C6").CreatePivotTable TableDestination _
:=Range("H1"), TableName:="樞紐分析表1"
ActiveSheet.PivotTables("樞紐分析表1").SmallGrid = False
ActiveSheet.PivotTables("樞紐分析表1").AddFields RowFields:="銷售員", _
ColumnFields:="日期", PageFields:="產品類別"
ActiveSheet.PivotTables("樞紐分析表1").PivotFields("總計").Orientation = _
xlDataField
ActiveWindow.ScrollColumn = 7
Range("I13").Select
Range("I3").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 12
ActiveWindow.SmallScroll ToRight:=-7
Range("I1").Select
ActiveSheet.PivotTables("樞紐分析表1").PivotFields("產品類別").CurrentPage = _
"糖果類"
End Sub

原文轉自：http://www.anti-gravitydesign.com

軟件測試 > 測試開發技術 > 軟件測試開發語言 > 自動測試腳本語言 >