Common operations Summary for creating Excel reports in VBScript _vbs

Source: Internet
Author: User
Tags border color

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.