Shulou(Shulou.com)06/03 Report--

This article mainly introduces "what are the common operations of Vbscript to generate Excel reports". In the daily operation, I believe that many people have doubts about what Vbscript reports are commonly used to generate Excel reports. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what are the common operations of Vbscript to generate Excel reports". Next, please follow the editor to study!

After the use of QTP automated testing, it is often necessary to write the test results into Excel. Here, some common methods of Excel operation are summarized and sorted out to facilitate reference when using. Office Excel version 2003 is supported, not version 2007.

Vbscript code

On Error Resume Next Dim FileName, SheetName, Text, ExcelApp, ExcelBook, ExcelSheet FileName = "D:/Book1.xls" SheetName = "new table" Text = "Hello QTP! Hello, QuickTestProfessional! "Set ExcelApp = CreateObject (" Excel.Application ") Set ExcelBook= ExcelApp.Workbooks.Open (FileName) Set ExcelSheet = ExcelBook.Sheets.Add 'insert worksheet' Set ExcelSheet = ExcelBook.Sheets.Item (SheetName) 'get specified worksheet' * Operation on the datasheet * For item1 To ExcelBook.Sheets.Count If ExcelBook.Sheets (I) .worksheet .DisplayAlerts = False ExcelBook.Sheets (I). Delete 'Delete worksheet ExcelApp.DisplayAlerts=True Exit For End If Next ExcelSheet.Name = SheetName' rename worksheet'* Operation on text * ExcelSheet.Cells (1) 2) = Text ExcelSheet.Range ("B2") "B20"). Value = Text ExcelSheet.Cells (1Magne2). Font.Name = "Verdana" 'set font ExcelSheet.Cells (1Zhone2). Font.Size = 25' set font size ExcelSheet.Cells (1Power2). Font.Color = RGB (0,0) Font.Bold = True 'text bold ExcelSheet.Cells (3). Font.Italic = True' text tilt ExcelSheet.Cells (4). Font.Underline = True 'text with underline ExcelSheet.Cells (5). Font.Strikethrough = True' text with delete line ExcelSheet.Cells (6). Characters (2). Font.Superscript = True 'set text superscript ExcelSheet.Cells (7) .Characters (2) 2). Font.Subscript = True 'set text subscript' * Operation on cells * ExcelSheet.Columns ("B"). ColumnWidth = 40 'set column width' ExcelSheet.Columns ("B"). AutoFit 'automatically adjust column width ExcelSheet.Range ("B11"). RowHeight=40' set row height 'ExcelSheet.Rows (11). Rows.AutoFit' Automatically adjust row height ExcelSheet.Range ("B8") "D8") .Merge 'merge cells Horizontal ExcelSheet.Range ("B18", "B19"). Merge 'merges cells, vertically ExcelSheet.Range ("B8", "D8"). Borders.Color = RGB (0pc255p0)' sets the cell border color ExcelSheet.Range ("B12"). Interior.Color = RGB (255pc0) 'sets the cell background color ExcelSheet.Cells (9Power2). WrapText = True' automatic line wrapping ExcelSheet.Cells (10line 2). HorizontalAlignment = 3 'set horizontal alignment, 1 regular, 2 to the left 3 center, 4 right'5 fill, 6 ends aligned, 7 span columns centered, 8 scattered aligned ExcelSheet.Cells (11L2). VerticalAlignment = 1 'sets vertical alignment, 1 up, 2 center, 3 lower' 4 ends aligned 5 scattered alignment ExcelSheet.Range ("B14"). Borders (1). LineStyle=1 'sets the left border style ExcelSheet.Range ("B14"). 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 lower border style ExcelSheet.Range ("B15"). ClearContents' clears the cell content ExcelSheet.Range ("B14"). B16 "). Formula=" = 1 "10" 'sets the cell formula ExcelSheet.Range ("B17"). AddComment ("Hello" & vbLf & "QTP")' insert annotation ExcelSheet.Range ("B17"). Comment.Visible=True 'display annotation' ExcelSheet.Range ("B17"). ClearComments' clear annotation Same as deleting annotations' ExcelSheet.Range ("B17") .Comment.Delete 'delete annotations have the same effect as clearing annotations' ExcelSheet.SaveAs ("D:\ Book2.xls") 'Save as ExcelBook.Save ExcelBook.Close ExcelApp.Quit Set ExcelBook = Nothing Set ExcelApp = Nothing SystemUtil.CloseProcessByName "Excel.exe" if there are still Excel.exe processes, you can use this sentence to close the process If Err.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 ExcelExcelSheet = ExcelBook.ActiveSheet 'activate the first table ExcelSheet.Columns ("ExcelExcelSheet E"). AutoFit ()' set A to E columns automatically adjust column width ExcelBook.SaveAs ("D:\ Book2.xls", FileFormat:=Excel.XLFileFormat.xlAddIn) 'file saved here, on the "what are the common operations of Vbscript to generate Excel reports" the study is over, I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.