VBS生成Excel报告的常用操作【转】.docVIP

VBS生成?Excel?报告的常用?操作使用QTP?自动化测试?结束后，经常需要将?测试结果写?入Exce?l中，这里就把一?些常用对E?xcel操?作的方法进?行归纳、整理，方便使用时?查阅。支持Off?ice Excel? 2003版?本，不支持20?07版本。On?Error??Resum?e?Next?? Dim?FileN?ame,?Sheet?Name,?Text,?Excel?App,?Excel?Book,?Excel?Sheet??? FileN?ame?=?D:/Book1?.xls?? Sheet?Name?=?新建表?? Text?=?Hello??QTP?!?你好,?Quick?TestP?rofes?siona?l?!?? ?? Set?Excel?App?=?Creat?eObje?ct(Excel?.Appli?catio?n)?? Set?Excel?Book=?Excel?App.Workb?ooks.Open(FileN?ame)?? Set?Excel?Sheet??=?Excel?Book.Sheet?s.Add???????????????插入工作表??? Set?Excel?Sheet??=?Excel?Book.Sheet?s.Item(Sheet?Name)??获得指定工?作表?? ?? ?***************?对数据表的?操作?***************?? For?i=1?To?Excel?Book.Sheet?s.Count??? ????If?Excel?Book.Sheet?s(i).Name=Sheet?Name?Then?? ????????Excel?App.Displ?ayAle?rts=False??? ????????Excel?Book.Sheet?s(i).Delet?e??????????????????删除工作表??? ????????Excel?App.Displ?ayAle?rts=True?? ????????Exit?For?? ????End?If?? Next?? Excel?Sheet?.Name?=?Sheet?Name?????????????????????????重命名工作?表?? ?? ?? ?***************?对文字的操?作?***************?? Excel?Sheet?.Cells?(1,2)?=?Text?? Excel?Sheet?.Range?(B2,B20).Value??=?Text?? Excel?Sheet?.Cells?(1,2).Font.Name?=?Verda?na?????????????设置字体?? Excel?Sheet?.Cells?(1,2).Font.Size?=?25????????????????????设置字号?? Excel?Sheet?.Cells?(1,2).Font.Color??=?RGB(0,?0,?255)???????设置字体颜?色?? Excel?Sheet?.Cells?(2,2).Font.Bold?=?True??????????????????文字加粗?? Excel?Sheet?.Cells?(3,2).Font.Itali?c?=?True????????????????文字倾斜?? Excel?Sheet?.Cells?(4,2).Font.Under?line?=?True?????????????文字加下划?线?? Excel?Sheet?.Cells?(5,2).Font.Strik?ethro?ugh?=?True?????????文字加删除?线?? Excel?Sheet?.Cells?(6,2).Chara?cters?(2,?2).Font.Super?scrip?t?=?True??设定文字上?标?? Excel?Sheet?.Cells?(7,2).Chara?cters?(2,?2).Font.Subsc?ript?=?True????设定文字下?标?? ?? ?? ?***************?对单元格的?操作?***************?? Excel?Sheet?.Colum?ns(B).Colum?nWidt?h?=?40????????????设置列宽?? Excel?Sheet?.Colum?ns(B).AutoF?it????????????????????自动调整列?宽?? Excel?Sheet?.Range?(B11).RowHe?ight=40????????????????设置行高?? Excel?Sheet?.Rows(11).Rows.AutoF?it?