使用perl创建Excel表格统计数据

use Spreadsheet::WriteExcel;

my $path="E:speakRecognizeexperimentdataResultemotion_speaker"; #设置查询的目录
opendir(TEMPDIR,$path) or die "can't open it:$!";
my @dir = readdir TEMPDIR;#print join "n",@dir;
close TEMPDIR;

#生成excel&设置输出格式
my $xlName = "Statistic.xls";
my $xl = Spreadsheet::WriteExcel->new($xlName);
my $xlsheet = $xl->add_worksheet("Sheet1");  #引号中为excel工作簿中表的名称
$xlsheet->freeze_panes(2,0); #冻结首行
# Add a format
my @headFormat = ($xl->add_format(size=>'15',bg_color=>'21',align=>'center'),$xl->add_format(size=>'15',bg_color=>'25',align=>'center'));
my @dataFormat = ($xl->add_format(bg_color=>'22',align=>'right'),$xl->add_format(bg_color=>'23',align=>'right'));
my @staticFormat = ($xl->add_format(bg_color=>'21',$xl->add_format(bg_color=>'25',align=>'right'));
my $bgColor = 0;
my @columns = qw(A B C D E F G H I J K L M N O P Q R S T);
my $currCol = 0;

sub getRateFormat{
	my ($slisti,$suttCntPerTarg) = @_;
	my $tempData = (substr($slisti/$suttCntPerTarg,6)*100)."%";
	if(index($tempData,".") == -1){substr($tempData,length($tempData)-1,1)=".00%";}
	$tempData;
}
sub AddRow{
	my ($curColumns,$curRows,$Format,$DataDetail) = @_;
	$xlsheet->write($columns[$curColumns].$curRows,$DataDetail->[0],$Format);
	$xlsheet->write($columns[$curColumns+1].$curRows,$DataDetail->[1],$Format);
	$xlsheet->write($columns[$curColumns+2].$curRows,$DataDetail->[2],$Format);
}

foreach my $filename(@dir){

	my @isResult = split /./,$filename;
	if($isResult[2] eq "result")
	{
		print "ndeal $filename begin……n";
		my $false_reject = 0;#错误拒绝个数
		my $false_recept = 0;#错误接受个数
		my $recordCnt = 0;#记录总数
		my $lastutterance = "0000";#上一个要打分的utterance
		my $utteranceCnt = 0;#utterance总个数
		my $lastTarget = "00";#上一个Target
		my @correctPerTarget = ();#每个Target的正确个数
		my $i = -1;
		my $uttCntPerTarg = 0;#每个Target中句子的个数
		my $firstTarget = 0;

		open(TFH_list,"<$filename");
		while (<TFH_list>)
		{
			my @fileline = split /s+/,$_;	### $_代表文件中当前行
			my @curtarget = split //,$fileline[3];
			
			#错误拒绝（本来是正确的被错误拒绝了）
			if($fileline[1] == $curtarget[0] && $fileline[2] == 0)
			{
				$false_reject++;
			}
			#错误接受（本来是错误的被错误接受了）
			elsif($fileline[1] != $curtarget[0] && $fileline[2] == 1)
			{
				$false_recept++;
			}
			if($curtarget[3] ne $lastutterance)
			{
				$utteranceCnt++;
				$lastutterance = $curtarget[3];
				if(0 == $i && $curtarget[0] ne $lastTarget)
				{
					$uttCntPerTarg = $utteranceCnt-1;
					$firstTarget = $curtarget[0]-1;
				}
			}
			#统计每个目标正确接受的个数
			if($curtarget[0] ne $lastTarget)
			{
				$i++;
				$lastTarget = $curtarget[0];
			}
			if ($fileline[1] == $curtarget[0] && $fileline[2] == 1)
			{
				$correctPerTarget[$i]++;
			}
			
			$recordCnt++;
		}
		close(TFH_list);

		
		#写内容（格式是使用上面添加的表内容格式）
		my @outFile = split /_/,$filename;
		my $row = 1;
		my $sumCorrect = 0;
		$xlsheet->merge_range($columns[$currCol].$row.":".$columns[$currCol+2].$row++,$outFile[0],$headFormat[$bgColor]);#"A1:C1"

		my @comment = ("target","correct","rate");
		AddRow($currCol,$row++,$dataFormat[$bgColor],@comment);
				
		foreach my $listi(@correctPerTarget) {
			my @dataItem = ($firstTarget++,$listi,getRateFormat($listi,$uttCntPerTarg));
			AddRow($currCol,@dataItem);
			$sumCorrect += $listi;
		}
		
		my @sumItem = ("SUM:",$sumCorrect."/".$utteranceCnt,getRateFormat($sumCorrect,$utteranceCnt));
		AddRow($currCol,$staticFormat[$bgColor],@sumItem);
		
		my @FRejItem = ("flsRej:",$false_reject."/".$utteranceCnt,getRateFormat($false_reject,@FRejItem);
		
		my @FRecItem = ("flsRec:",$false_recept."/"."(".$recordCnt."-".$utteranceCnt.")",getRateFormat($false_recept,$recordCnt-$utteranceCnt));
		AddRow($currCol,@FRecItem);
		
		$currCol = $currCol + 3;
		$bgColor = !$bgColor;
		print "deal $filename success!nn";
	}
}

#关闭操作excel的对象.
print "nn"."处理结果已保存至$xlName，正在打开……"."nn";
$xl->close();
system($xlName);