Perl例程：从txt文件提取数据到excel文件指定位置

功能：
1.读取当前路径下txt文件中符合特定匹配条件的数据；
2.将读取的数据保存到excel表指定位置。

#!/usr/bin/perl

use Cwd;
my $dir = getcwd;
use strict;
use Win32;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);
$Win32::OLE::Warn = 3;

my @log_file_name=("origin.txt","test.txt");	#2个待读取文件
my $outexcell="H266_performance_results.xlsx";	#写入数据到该输出excel文件
use File::Copy;
copy("./H266_performance.xlsx",$outexcell) or die "copy xlsx file failed: &!"; #复制模板excel文件
#get already active excel application object or open new
my $Excel =Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application','Quit');
use Encode;
#open excel file
my $Book = $Excel->Workbooks->Open("$dir\/$outexcell") or die "Could not open the $dir\/$outexcell !!!\n";
my $Sheet=$Book->Worksheets("Sheet1");	#找到excel中对应的sheet表，名称可根据需求更改

my $file_idx=0;
for($file_idx=0; $file_idx<2; $file_idx++)
{
    #get file name
    my $infile="$dir\/$log_file_name[$file_idx]";
    #open file
    if(open(IN1,"<$infile"))
    {
        print "find $infile\n";
        my $result_num=0;	
        my $count_num=0;
        my %variable=("Bitrate"=>0,"Y-PSNR">=0,"U-PSNR">=0,"V-PSNR">=0,"YUV-PSNR">=0);
        #scan file
        while(my $aline=<IN1>)
        {
            #$aline=~s/\n$//;	#可能是使用一个空格分界
            $aline=~s/^\s+//;
            my @array=split(/\s+/,$aline);	#使用至少一个空格分界
			
            if($#array gt 1)
            {
                #print "Array > 1!\n"; //测试
                #find data:Bitrate,Y-PSNR,U-PSNR,V-PSNR,YUV-PSNR
                #if(($array[0] eq "1") && ($array[1] eq "a"))
                if(($array[0] eq "1") && ($array[1] eq "a"))	#符合特定匹配条件
                {
                    print "find data!\n";
                    $variable{"Bitrate"}=$array[2];	#创建元素“Bitrate”
                    $variable{"Y-PSNR"}=$array[3];	#创建元素“Y-PSNR”
                    $variable{"U-PSNR"}=$array[4];	#创建元素“U-PSNR”
                    $variable{"V-PSNR"}=$array[5];	#创建元素“V-PSNR”
                    $variable{"YUV-PSNR"}=$array[6];#创建元素“YUV-PSNR”
                    $count_num=$count_num+5;
                }
            }	
            my @result=values(%variable);
            if($count_num eq $#result)
            {
                $count_num=0;
                $result_num++;
                print "$result_num $variable{'Bitrate'} $variable{'Y-PSNR'} 
                $variable{'U-PSNR'} $variable{'V-PSNR'} $variable{'YUV-PSNR'} \n";
                my $col=0;
                if($file_idx eq 0)
                {
                    $Sheet->Cells($result_num+2, $col+1)->{'Value'}=$variable{"Bitrate"}; 
                    $Sheet->Cells($result_num+2, $col+4)->{'Value'}=$variable{"Y-PSNR"}; 
                    $Sheet->Cells($result_num+2, $col+7)->{'Value'}=$variable{"U-PSNR"}; 
                    $Sheet->Cells($result_num+2, $col+10)->{'Value'}=$variable{"V-PSNR"}; 
                    $Sheet->Cells($result_num+2, $col+13)->{'Value'}=$variable{"YUV-PSNR"}; 
                }
                else
                {
                    $Sheet->Cells($result_num+2, $col+2)->{'Value'}=$variable{"Bitrate"}; 
                    $Sheet->Cells($result_num+2, $col+5)->{'Value'}=$variable{"Y-PSNR"}; 
                    $Sheet->Cells($result_num+2, $col+8)->{'Value'}=$variable{"U-PSNR"}; 
                    $Sheet->Cells($result_num+2, $col+11)->{'Value'}=$variable{"V-PSNR"}; 
                    $Sheet->Cells($result_num+2, $col+14)->{'Value'}=$variable{"YUV-PSNR"}; 
                }
            }		
        }
        close(IN1);
        $Book->Save;
    }
    else
    {
        print "Warning:cannot open input file $infile\n";
    }
}
$Book->Close;
$Excel->Quit();