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

概述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 joi

@H_502_7@

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',$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!\n\n";	}}#关闭 *** 作excel的对象.print "\n\n"."处理结果已保存至$xlname，正在打开……"."\n\n";$xl->close();system($xlname);

总结

以上是内存溢出为你收集整理的使用perl创建Excel表格统计数据全部内容，希望文章能够帮你解决使用perl创建Excel表格统计数据所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。