|
这段时间工作中需要对比大量Excel表格中的数据,所以用QTP实现将需要对比的两个表格中需要对比的列拿出来,发到一个新的Excel表格中,然后在手动绘制新表格中的数据曲线图。
大家如果有更好的方法,希望不吝赐教。代码肯定有需要优化的地方,我还在修改中,先给大家抛个砖。
- '========================================================================================================
- '功能:将两个xlsx表格中相对应列的数据输出到一个新的xlsx文件中
- '输入:两个xlsx
- '输出:新的xlsx,事先手动创建
- '========================================================================================================
- Option Explicit
- Dim marfilepath(20) ,julyfilepath(20),myfilepath(20),i,rowcount, ExcelShe,columncount,n
- Dim ExcelBook,ExcelSheet,myExcelBook,myExcelSheet,marExcelBook ,marExcelSheet,julyExcelBook ,julyExcelSheet
- '=================
- '初始化输入
- '==================
- marfilepath(1) = "I:\19HD_fg_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(1) = "I:\19HD_fg_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
- myfilepath(1) = "I:\19HD_fg_1.3M.xls"
- marfilepath(2) = "I:\19HD_fg_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(2) = "I:\19HD_fg_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
- myfilepath(2) = "I:\19HD_fg_500k.xls"
- marfilepath(3) = "I:\ssim\19HD_ty_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(3) = "I:\ssim\19HD_ty_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
- myfilepath(3) = "I:\ssim\19HD_ty_1.3M.xlsx"
- marfilepath(4) = "I:\ssim\19HD_ty_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(4) = "I:\ssim\19HD_ty_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
- myfilepath(4) = "I:\ssim\19HD_ty_500k.xlsx"
- marfilepath(5) = "I:\ssim\19HD_wh_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(5) = "I:\ssim\19HD_wh_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
- myfilepath(5) = "I:\ssim\19HD_wh_1.3M.xlsx"
- marfilepath(6) = "I:\ssim\19HD_wh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(6) = "I:\ssim\19HD_wh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
- myfilepath(6) = "I:\ssim\19HD_wh_500k.xlsx"
- marfilepath(7) = "I:\ssim\19HD_xw_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(7) = "I:\ssim\19HD_xw_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
- myfilepath(7) = "I:\ssim\19HD_xw_1.3M.xlsx"
- marfilepath(8) = "I:\ssim\19HD_xw_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(8) = "I:\ssim\19HD_xw_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
- myfilepath(9) = "I:\ssim\19HD_xw_500k.xlsx"
- marfilepath(9) = "I:\ssim\19HD_zh_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(9) = "I:\ssim\19HD_zh_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
- myfilepath(9) = "I:\ssim\19HD_zh_1.3M.xlsx"
- marfilepath(10) = "I:\ssim\19HD_zh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(10) = "I:\ssim\19HD_zh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
- myfilepath(10) = "I:\ssim\19HD_zh_500k.xlsx"
- marfilepath(11) = "I:\ssim\12HD_fg_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(11) = "I:\ssim\12HD_fg_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
- myfilepath(11) = "I:\ssim\12HD_fg_1.4M.xlsx"
- marfilepath(12) = "I:\ssim\12HD_ty_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(12) = "I:\ssim\12HD_ty_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
- myfilepath(12) = "I:\ssim\12HD_ty_1.4M.xlsx"
- marfilepath(13) = "I:\ssim\12HD_wh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(13) = "I:\ssim\12HD_wh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
- myfilepath(13) = "I:\ssim\12HD_wh_1.4M.xlsx"
- marfilepath(14) = "I:\ssim\12HD_xw_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(14) = "I:\ssim\12HD_xw_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
- myfilepath(14) = "I:\ssim\12HD_xw_1.4M.xlsx"
- marfilepath(15) = "I:\ssim\12HD_zh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(15) = "I:\ssim\12HD_zh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
- myfilepath(15) = "I:\ssim\12HD_zh_1.4M.xlsx"
- marfilepath(16) = "I:\ssim\HD_fg_6M_1p_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(16) = "I:\ssim\HD_fg_6M_1p_VBR_G100R3.mp4_july.xlsx"
- myfilepath(16) = "I:\ssim\HD_fg_6M.xlsx"
- marfilepath(17) = "I:\ssim\HD_ty_6M_1p_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(17) = "I:\ssim\HD_ty_6M_1p_VBR_G100R3.mp4_july.xlsx"
- myfilepath(17) = "I:\ssim\HD_ty_6M.xlsx"
- marfilepath(18) = "I:\ssim\HD_wh_6M_1p_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(18) = "I:\ssim\HD_wh_6M_1p_VBR_G100R3.mp4_july.xlsx"
- myfilepath(18) = "I:\ssim\HD_wh_6M.xlsx"
- marfilepath(19) = "I:\ssim\HD_xw_6M_1p_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(19) = "I:\ssim\HD_xw_6M_1p_VBR_G100R3.mp4_july.xlsx"
- myfilepath(19) = "I:\ssim\HD_xw_6M.xlsx"
- marfilepath(20) = "I:\ssim\HD_zh_6M_1p_VBR_G100R3.mp4_march.xlsx"
- julyfilepath(20) = "I:\ssim\HD_zh_6M_1p_VBR_G100R3.mp4_july.xlsx"
- myfilepath(20) = "I:\ssim\HD_zh_6M.xlsx"
- '==================
- ''创建Excel应用程序的对象
- '==================
- 'Set fso = CreateObject("Scripting.FileSystemObject")
- Set ExcelBook = CreateObject("Excel.Application")
- Set ExcelSheet = CreateObject("Excel.Sheet")
- '===================
- ''分别取出marfilepath和julyfilepath的SSIM_curYUV,并输入到myfilepath的第一列和第二列
- '===================
- For n=1 to 20
- Set marExcelBook = ExcelBook.WorkBooks.Open(marfilepath(n))'打开excel文件工作簿
- Set marExcelSheet = marExcelBook.WorkSheets("Sheet1")
- Set julyExcelBook = ExcelBook.WorkBooks.Open(julyfilepath(n))
- Set julyExcelSheet = julyExcelBook.WorkSheets("Sheet1")
- Set ExcelShe = marExcelBook.Worksheets("Sheet1").UsedRange '查询sheet1中使用的区域
- rowcount = ExcelShe.Rows.count '使用的行
- 'columncount = ExcelShe.Columns.count '使用的列
- Set myExcelBook = ExcelBook.WorkBooks.Open(myfilepath(n))
- Set myExcelSheet = myExcelBook.WorkSheets("Sheet1")
- '写入新的Excel中
- myExcelSheet.Cells(1,1).Value = "file:" & myfilepath(n)
- myExcelSheet.Cells(2,1).Value = "SSIM_curframe_march"
- myExcelSheet.Cells(2,2).Value = "SSIM_curframe_july"
-
- For i=3 to rowcount
- myExcelSheet.Cells(i,1).Value = marExcelSheet.Cells(i+4,6)
- myExcelSheet.Cells(i,2).Value = julyExcelSheet.Cells(i+4,6)
- Next
- myExcelBook.Save
- Next
- ExcelBook.Quit'退出ExcelApp对象
- Set ExcelBook = Nothing
复制代码 |
|