使用perl读取Excel
环境
- windows 7
- ActiveState Perl
- Win32::OLE[perl package]
基本功能
- 循环处理多个sheet
- 读取Excel单元，提取interface信息
- 格式化标准输出
- 格式化写入文件
解析结果
Perl代码
#!/usr/bin/perl -wuse strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';#################################################
# Main-routine
#################################################
$Win32::OLE::Warn = 3; # die on errors...my $modulename ="";
my $Sheet ="";
my $Book ="";my @inports;
my @outports;
my @vgports;my $excelvalue=""; my $Has_Help = "";
my $work = ""; if( $#ARGV < 0 ) {&print_usage;exit;
}
my $filename ="";
&parse_argv;if( $Has_Help =~ /TRUE/ ) #显示帮助说明{&print_usage;exit;}if($work =~ /TRUE/ ) #读取表格生成verilog接口文件{chomp($filename);# get already active Excel application or open newmy $Excel = Win32::OLE->GetActiveObject('Excel.Application')|| Win32::OLE->new('Excel.Application', 'Quit'); # open Excel file$Book = $Excel->Workbooks->Open("$filename"); # You can dynamically obtain the number of worksheets, rows, and columns# through the Excel OLE interface. Excel's Visual Basic Editor has more# information on the Excel OLE interface. Here we just use the first# worksheet, rows 1 through 4 and columns 1 through 3.# select worksheet number 1 (you can also select a worksheet by name)#for(my $sheetnum=1; $sheetnum < 3;$sheetnum++) #手动设置sheet数量，循环处理每个sheetfor(my $sheetnum=2; $sheetnum < 12 ;$sheetnum++) #手动设置sheet数量，循环处理每个sheet{$Sheet = $Book->Worksheets($sheetnum);&write_result; #写入文件}for(my $sheetnum=2; $sheetnum < 12;$sheetnum++) #手动设置sheet数量，循环处理每个sheet{$Sheet = $Book->Worksheets($sheetnum); #手动设置sheet数量&output_result; #Windows终端显示结果}# clean up after ourselves$Book->Close;print "\nParse Complete!\n";exit;}
else{&print_usage;exit;}#################################################
# Sub-routine: print_usage() 帮助说明
#################################################
sub print_usage {print "\nUsage: excel2verilog.pl [-option] <excel_file> \\\n"; print " [-w <excel_file>] \\\n";print " [-h] \n\n";print "For example:\n";print " perl excel2verilog.pl -w C:/Users/D/Desktop/test.xls\n"; print " perl excel2verilog.pl -h \n"; print "\n";print " Please modify the quantity of sheets\(see line 59 and line 65\)\n"; print "\n";
}#################################################
# Sub-routine : parse_argv() 参数读入
#################################################
sub parse_argv {my $all_arg = "-h|-w";for(my $i=0; $i<=$#ARGV; $i++) {if( $ARGV[$i] =~ /-w/ ) {$i++;if(!defined $ARGV[$i]){$Has_Help = "TRUE";}$work = "TRUE";$filename = $ARGV[$i];}elsif( $ARGV[$i] =~ /-h/ ) {$Has_Help = "TRUE";}else { ### other options$Has_Help = "TRUE";}}
}#################################################
# Sub-routine : get io 得到excel表格中的有用参数存入数组中
#################################################
sub get_io{@inports =(); #使用全局变量，分析一个sheet需要清空数组@outports =();@vgports =();$modulename ="";my $ipnamerow; my $ipnamecol; my $pinnamerow; my $pinnamecol; my $endrow; foreach my $row (1..100) #将端口数据存入数组,注意扫描范围{foreach my $col (1..5) #处理1-5行得到“IP name”/“Pin name”/“Size”的位置{# skip empty cellsnext unless defined $Sheet->Cells($row,$col)->{'Value'};# get position $excelvalue = $Sheet->Cells($row,$col)->{'Value'}; if($excelvalue =~ /ip name/i){$ipnamerow = $row; $ipnamecol = $col; $modulename = $Sheet->Cells($ipnamerow,($ipnamecol+1))->{'Value'};#printf("%s,%s\n",$ipnamerow,$ipnamecol);} if($excelvalue =~ /pin name/i){$pinnamerow = $row; $pinnamecol = $col; #printf("%s\n",$pinnamerow);} if($excelvalue =~ /size/i){$endrow = $row - 1; #printf("%s\n",$endrow);} }}foreach my $row (($pinnamerow + 1)..$endrow) #端口处理，存入数组{next unless defined $Sheet->Cells($row,$pinnamecol)->{'Value'};foreach my $col ($pinnamecol){# skip empty cellsnext unless defined $Sheet->Cells($row,$col)->{'Value'};if(($Sheet->Cells($row,$col)->{'Value'}) eq "IN"){next; # skip "IN" cells}if(($Sheet->Cells($row,$col)->{'Value'}) eq "OUT"){next; # skip "OUT" cells}$_ = $Sheet->Cells($row,$col+1)->{'Value'};if(/P|G|I\/O/) #inout{push(@vgports,$Sheet->Cells($row,$col)->{'Value'});}elsif(/\bO\/D\b|\bO\/A\b/) #output{push(@outports,$Sheet->Cells($row,$col)->{'Value'});}elsif(/\bI\/A\b|\bI\/D\b/) #input {push(@inports,$Sheet->Cells($row,$col)->{'Value'});}}}
}#################################################
# Sub-routine : output_result 格式化输出verilog代码
#################################################
sub output_result{&get_io; #得到表格中的数据printf("module %s\(\n",$modulename);foreach my $vgport (@vgports){$_ = $vgport; if(/(.*)\<(\d*)\:/i){printf("inout [%2d:0] %s,\n",$2,$1);} else{printf("inout %s,\n",$vgport);}}print "\n";foreach my $inport (@inports){$_ = $inport; if(/(.*)\<(\d*)\:/i){printf("input [%2d:0] %s,\n",$2,$1);}else{printf("input %s,\n",$inport);}}print "\n";my $n = @outports;my $i =0;foreach my $outport (@outports){$_ = $outport; if(/(.*)\<(\d*)\:/i){printf("output [%2d:0] %s",$2,$1);} else{printf("output %s",$outport);}$i++;if($i < $n){print(",");} print("\n");}print "\);\n";print "\n\n\n";print "endmodule\n";print "\n\n\n";
}#################################################
# Sub-routine : write_result 格式化写入verilog 代码
#################################################
sub write_result{&get_io;unlink "$modulename.v";open(MODULE, ">> $modulename.v") || die ("Could not open file tempA.txt! \n");printf MODULE ("module %s\(\n",$modulename);foreach my $vgport (@vgports){$_ = $vgport; if(/(.*)\<(\d*)\:/i){printf MODULE ("inout [%2d:0] %s,\n",$2,$1);} else{printf MODULE ("inout %s,\n",$vgport);}}print MODULE "\n";foreach my $inport (@inports){$_ = $inport; if(/(.*)\<(\d*)\:/i){printf MODULE ("input [%2d:0] %s,\n",$2,$1);}else{printf MODULE ("input %s,\n",$inport);}}print MODULE "\n";my $n = @outports;my $i =0;foreach my $outport (@outports){$_ = $outport; if(/(.*)\<(\d*)\:/i){printf MODULE ("output [%2d:0] %s",$2,$1);} else{printf MODULE ("output %s",$outport);}$i++;if($i < $n){print MODULE (",");} print MODULE ("\n");}print MODULE "\);\n";print MODULE "\n\n\n";print MODULE "endmodule\n";close MODULE
}参考资料
功能丰富的 Perl: 用 Perl 读写 Excel 文件