51Testing软件测试论坛

 找回密码
 (注-册)加入51Testing

QQ登录

只需一步,快速开始

微信登录,快人一步

手机号码,快捷登录

查看: 2100|回复: 1
打印 上一主题 下一主题

[原创] 用QTP将两个Excel表格中相对应列的数据输出到一个新的Excel文件中

[复制链接]

该用户从未签到

跳转到指定楼层
1#
发表于 2011-7-29 09:46:35 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
这段时间工作中需要对比大量Excel表格中的数据,所以用QTP实现将需要对比的两个表格中需要对比的列拿出来,发到一个新的Excel表格中,然后在手动绘制新表格中的数据曲线图。
大家如果有更好的方法,希望不吝赐教。代码肯定有需要优化的地方,我还在修改中,先给大家抛个砖。

  1. '========================================================================================================
  2. '功能:将两个xlsx表格中相对应列的数据输出到一个新的xlsx文件中
  3. '输入:两个xlsx
  4. '输出:新的xlsx,事先手动创建
  5. '========================================================================================================
  6. Option Explicit

  7. Dim marfilepath(20) ,julyfilepath(20),myfilepath(20),i,rowcount, ExcelShe,columncount,n
  8. Dim ExcelBook,ExcelSheet,myExcelBook,myExcelSheet,marExcelBook ,marExcelSheet,julyExcelBook ,julyExcelSheet
  9. '=================
  10. '初始化输入
  11. '==================
  12. marfilepath(1) = "I:\19HD_fg_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
  13. julyfilepath(1) = "I:\19HD_fg_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
  14. myfilepath(1) = "I:\19HD_fg_1.3M.xls"

  15. marfilepath(2) = "I:\19HD_fg_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
  16. julyfilepath(2) = "I:\19HD_fg_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
  17. myfilepath(2) = "I:\19HD_fg_500k.xls"

  18. marfilepath(3) = "I:\ssim\19HD_ty_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
  19. julyfilepath(3) = "I:\ssim\19HD_ty_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
  20. myfilepath(3) = "I:\ssim\19HD_ty_1.3M.xlsx"

  21. marfilepath(4) = "I:\ssim\19HD_ty_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
  22. julyfilepath(4) = "I:\ssim\19HD_ty_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
  23. myfilepath(4) = "I:\ssim\19HD_ty_500k.xlsx"

  24. marfilepath(5) = "I:\ssim\19HD_wh_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
  25. julyfilepath(5) = "I:\ssim\19HD_wh_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
  26. myfilepath(5) = "I:\ssim\19HD_wh_1.3M.xlsx"

  27. marfilepath(6) = "I:\ssim\19HD_wh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
  28. julyfilepath(6) = "I:\ssim\19HD_wh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
  29. myfilepath(6) = "I:\ssim\19HD_wh_500k.xlsx"

  30. marfilepath(7) = "I:\ssim\19HD_xw_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
  31. julyfilepath(7) = "I:\ssim\19HD_xw_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
  32. myfilepath(7) = "I:\ssim\19HD_xw_1.3M.xlsx"

  33. marfilepath(8) = "I:\ssim\19HD_xw_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
  34. julyfilepath(8) = "I:\ssim\19HD_xw_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
  35. myfilepath(9) = "I:\ssim\19HD_xw_500k.xlsx"

  36. marfilepath(9) = "I:\ssim\19HD_zh_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
  37. julyfilepath(9) = "I:\ssim\19HD_zh_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
  38. myfilepath(9) = "I:\ssim\19HD_zh_1.3M.xlsx"

  39. marfilepath(10) = "I:\ssim\19HD_zh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
  40. julyfilepath(10) = "I:\ssim\19HD_zh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
  41. myfilepath(10) = "I:\ssim\19HD_zh_500k.xlsx"

  42. marfilepath(11) = "I:\ssim\12HD_fg_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
  43. julyfilepath(11) = "I:\ssim\12HD_fg_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
  44. myfilepath(11) = "I:\ssim\12HD_fg_1.4M.xlsx"

  45. marfilepath(12) = "I:\ssim\12HD_ty_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
  46. julyfilepath(12) = "I:\ssim\12HD_ty_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
  47. myfilepath(12) = "I:\ssim\12HD_ty_1.4M.xlsx"

  48. marfilepath(13) = "I:\ssim\12HD_wh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
  49. julyfilepath(13) = "I:\ssim\12HD_wh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
  50. myfilepath(13) = "I:\ssim\12HD_wh_1.4M.xlsx"

  51. marfilepath(14) = "I:\ssim\12HD_xw_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
  52. julyfilepath(14) = "I:\ssim\12HD_xw_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
  53. myfilepath(14) = "I:\ssim\12HD_xw_1.4M.xlsx"

  54. marfilepath(15) = "I:\ssim\12HD_zh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
  55. julyfilepath(15) = "I:\ssim\12HD_zh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
  56. myfilepath(15) = "I:\ssim\12HD_zh_1.4M.xlsx"

  57. marfilepath(16) = "I:\ssim\HD_fg_6M_1p_VBR_G100R3.mp4_march.xlsx"
  58. julyfilepath(16) = "I:\ssim\HD_fg_6M_1p_VBR_G100R3.mp4_july.xlsx"
  59. myfilepath(16) = "I:\ssim\HD_fg_6M.xlsx"

  60. marfilepath(17) = "I:\ssim\HD_ty_6M_1p_VBR_G100R3.mp4_march.xlsx"
  61. julyfilepath(17) = "I:\ssim\HD_ty_6M_1p_VBR_G100R3.mp4_july.xlsx"
  62. myfilepath(17) = "I:\ssim\HD_ty_6M.xlsx"

  63. marfilepath(18) = "I:\ssim\HD_wh_6M_1p_VBR_G100R3.mp4_march.xlsx"
  64. julyfilepath(18) = "I:\ssim\HD_wh_6M_1p_VBR_G100R3.mp4_july.xlsx"
  65. myfilepath(18) = "I:\ssim\HD_wh_6M.xlsx"

  66. marfilepath(19) = "I:\ssim\HD_xw_6M_1p_VBR_G100R3.mp4_march.xlsx"
  67. julyfilepath(19) = "I:\ssim\HD_xw_6M_1p_VBR_G100R3.mp4_july.xlsx"
  68. myfilepath(19) = "I:\ssim\HD_xw_6M.xlsx"

  69. marfilepath(20) = "I:\ssim\HD_zh_6M_1p_VBR_G100R3.mp4_march.xlsx"
  70. julyfilepath(20) = "I:\ssim\HD_zh_6M_1p_VBR_G100R3.mp4_july.xlsx"
  71. myfilepath(20) = "I:\ssim\HD_zh_6M.xlsx"

  72. '==================
  73. ''创建Excel应用程序的对象
  74. '==================
  75. 'Set fso = CreateObject("Scripting.FileSystemObject")
  76. Set ExcelBook = CreateObject("Excel.Application")
  77. Set ExcelSheet = CreateObject("Excel.Sheet")

  78. '===================
  79. ''分别取出marfilepath和julyfilepath的SSIM_curYUV,并输入到myfilepath的第一列和第二列
  80. '===================
  81. For n=1 to 20

  82. Set marExcelBook = ExcelBook.WorkBooks.Open(marfilepath(n))'打开excel文件工作簿
  83. Set marExcelSheet = marExcelBook.WorkSheets("Sheet1")

  84. Set julyExcelBook = ExcelBook.WorkBooks.Open(julyfilepath(n))
  85. Set julyExcelSheet = julyExcelBook.WorkSheets("Sheet1")

  86. Set ExcelShe = marExcelBook.Worksheets("Sheet1").UsedRange     '查询sheet1中使用的区域
  87. rowcount = ExcelShe.Rows.count  '使用的行
  88. 'columncount = ExcelShe.Columns.count '使用的列

  89. Set myExcelBook = ExcelBook.WorkBooks.Open(myfilepath(n))
  90. Set myExcelSheet = myExcelBook.WorkSheets("Sheet1")

  91. '写入新的Excel中
  92. myExcelSheet.Cells(1,1).Value = "file:" & myfilepath(n)
  93. myExcelSheet.Cells(2,1).Value = "SSIM_curframe_march"
  94. myExcelSheet.Cells(2,2).Value = "SSIM_curframe_july"

  95. For i=3 to rowcount
  96. myExcelSheet.Cells(i,1).Value = marExcelSheet.Cells(i+4,6)
  97. myExcelSheet.Cells(i,2).Value = julyExcelSheet.Cells(i+4,6)
  98. Next

  99. myExcelBook.Save

  100. Next
  101. ExcelBook.Quit'退出ExcelApp对象

  102. Set ExcelBook = Nothing
复制代码
分享到:  QQ好友和群QQ好友和群 QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
收藏收藏
回复

使用道具 举报

  • TA的每日心情
    开心
    2018-7-13 14:04
  • 签到天数: 6 天

    连续签到: 1 天

    [LV.2]测试排长

    2#
    发表于 2011-7-30 22:23:46 | 只看该作者
    都写死了,不太高明。。。。
    回复 支持 反对

    使用道具 举报

    本版积分规则

    关闭

    站长推荐上一条 /1 下一条

    小黑屋|手机版|Archiver|51Testing软件测试网 ( 沪ICP备05003035号 关于我们

    GMT+8, 2024-11-22 15:43 , Processed in 0.073282 second(s), 27 queries .

    Powered by Discuz! X3.2

    © 2001-2024 Comsenz Inc.

    快速回复 返回顶部 返回列表