Common operations Summary for creating Excel reports in VBScript _vbs

After the use of QTP automated testing, often need to write test results in Excel, here is a number of commonly used to Excel operation methods are summarized, sorted, easy to use when consulted. Office Excel Version 2003 is supported and version 2007 is not supported.

VBScript code

On Error Resume Next Dim filename, sheetname, Text, Excelapp, excelbook, excelsheet filename = "D:/book1.xls" Sheetnam E = "new table" Text = "Hello QTP!"
Hello, quicktestprofessional! " Set excelapp = CreateObject ("Excel.Application") set excelbook= ExcelApp.Workbooks.Open (FileName) Set excelsheet = Excel Book.Sheets.Add ' Insert Sheet ' Set excelsheet = ExcelBook.Sheets.Item (sheetname) ' Get the specified worksheet ' *************** operation on the datasheet *********** For I=1 to ExcelBook.Sheets.Count If excelbook.sheets (i). Name=sheetname Then excelapp.displayalerts=false excelbook.sheets (i). Delete ' Remove sheet excelapp.displayalerts=true Exit for end If Next excelsheet.name = SheetName ' Rename sheet ' *************** Operations on text *************** excelsheet.cells (1,2) = text Excelsheet.range ("B2", "B20"). Value = Text excelsheet.cells (1,2). Font.Name = "Verdana" Sets the font excelsheet.cells (1,2). Font.Size = 25 ' Sets the font size excelsheet.cells (1,2). Font.Color = RGB (0, 0, 255) ' Sets the font color Excelsheet.cells (2,2). Font.Bold = True ' text bold excelsheet.cells (3,2). Font.Italic = True ' text skew excelsheet.cells (4,2). Font.underline = True ' text underlined excelsheet.cells (5,2). Font.strikethrough = True ' text plus strikethrough excelsheet.cells (6,2). Characters (2, 2). Font.superscript = True ' Sets the text superscript excelsheet.cells (7,2). Characters (2, 2). Font.subscript = True ' Sets the text subscript ' *************** The operation of the cell *************** excelsheet.columns ("B"). ColumnWidth = 40 ' Set column width ' excelsheet.columns (' B '). AutoFit ' automatically adjusts column width excelsheet.range (' B11 '). Rowheight=40 ' Set row height ' excelsheet.rows (11). Rows.autofit ' automatically adjusts row height Excelsheet.range ("B8", "D8"). Merge ' merges cells, horizontal direction excelsheet.range ("B18", "B19"). Merge ' merges cells, vertically excelsheet.range ("B8", "D8"). Borders.color = RGB (0,255,0) ' Sets the cell border color Excelsheet.range ("B12"). Interior.Color = RGB (255,0,0) ' Sets the cell background color excelsheet.cells (9,2). WrapText = True ' automatic line wrapping excelsheet.cells (10,2). HorizontalAlignment = 3 ' Set horizontal alignment, 1 normal, 2 to left, 3 centered, 4 to right ' 5 padding, 62 to aligned, 7 to center across columns, 8 distributed to Excelsheet.cells (11,2). VerticalAlignment = 1 ' Set vertical alignment, 1 up, 2 centered, 3 down ' 42-aligned, 5 distributed to Excelsheet.range ("B14"). Borders (1). Linestyle=1 ' SetThe left Border style excelsheet.range ("B14") is placed. Borders (2). linestyle=2 ' Sets the right border style excelsheet.range ("B14"). Borders (3). Linestyle=3 ' Sets the top border style excelsheet.range (' B14 '). Borders (4). Linestyle=4 ' Sets the bottom border style excelsheet.range ("B15"). ClearContents ' clears cell contents excelsheet.range (' B16 '). Formula= "=1+10" sets the cell formula Excelsheet.range ("B17"). AddComment ("Hello" & vblf & "QTP") ' Inserts annotation excelsheet.range ("B17"). Comment.visible=true ' show annotations ' Excelsheet.range ("B17"). Clearcomments ' clears annotations, same as delete annotation effect ' excelsheet.range (' B17 '). Comment.delete ' Delete annotation, same as clear annotation effect ' excelsheet.saveas (' D:\Book2.xls ') ' Save as Excelbook.save excelbook.close excelapp.quit S Et excelbook = Nothing Set excelapp = no systemutil.closeprocessbyname "Excel.exe" ' if there is still a Excel.exe process, you can use this to close process if E
 Rr.number>0 Then MsgBox Err.Description end If on Error GoTo 0

Add:

Excelapp.displayalerts = False ' Turn off compatibility check
Excelbook = ExcelApp.Workbooks.Add ' new Excel
ExcelSheet = Excelbook.activesheet ' Activates the first table
excelsheet.columns ("A:e"). AutoFit () ' Set A to E column AutoFit column width
excelbook.saveas ("D:\Book2.xls", Fileformat:=excel.xlfileformat.xladdin) ' File Save As