使用perl读取Excel
环境
基本功能
- 循环处理多个sheet
- 读取Excel单元，提取interface信息
- 格式化标准输出
- 格式化写入文件
解析结果
Perl代码
#!/usr/bin/perl -w
use 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 new
my $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数量，循环处理每个sheet
for(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 cells
next 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 cells
next 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 文件