use Spreadsheet::WriteExcel;my $path="E:\\speakRecognize\\experiment\\data\\Result\\emotion_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 formatmy @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',align=>'right'), $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,0,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";#上一个要打分的utterancemy $utteranceCnt = 0;#utterance总个数my $lastTarget = "00";#上一个Targetmy @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, $row++, $dataFormat[$bgColor], \@dataItem);$sumCorrect += $listi;}my @sumItem = ("SUM:", $sumCorrect."\/".$utteranceCnt, getRateFormat($sumCorrect,$utteranceCnt));AddRow($currCol, $row++, $staticFormat[$bgColor], \@sumItem);my @FRejItem = ("flsRej:", $false_reject."\/".$utteranceCnt, getRateFormat($false_reject,$utteranceCnt));AddRow($currCol, $row++, $staticFormat[$bgColor], \@FRejItem);my @FRecItem = ("flsRec:", $false_recept."\/"."(".$recordCnt."-".$utteranceCnt.")", getRateFormat($false_recept,$recordCnt-$utteranceCnt));AddRow($currCol, $row++, $staticFormat[$bgColor], \@FRecItem);$currCol = $currCol + 3;$bgColor = !$bgColor;print "deal $filename success!\n\n";}}#关闭操作excel的对象.print "\n\n"."处理结果已保存至$xlName，正在打开……"."\n\n";$xl->close();system($xlName);
