在Perl中读写Excel表

系统管理员 2022-06-09 05:25 336阅读 0赞

Perl中读写Excel很方便，贴段代码为以后参考。

读写Excel的组件需要另外安装，指令如下：

perl -MCPAN -e shell -> install Spreadsheet::WriteExcel  
   perl -MCPAN -e shell -> install Spreadsheet::ParseExcel

#!/usr/bin/perl
    use Spreadsheet::WriteExcel; #写入Excel数据
    use Spreadsheet::ParseExcel; #读取Excel资料
    
    # 读取数据
    # 使用: LoadStringsFromExcel(fileName);
    sub LoadStringsFromExcel
    {
    	my $parser = Spreadsheet::ParseExcel->new();
    	my $workbook = $parser->parse(@_[0]); #打开传入的文件
    	my $TotalCount = 0;
    
    	if(!defined $workbook) #是否打开成功
    	{
    		print "Failed to open @_[0]\n";
    		die $parser->error(),".\n";
    	}
    
    	$Sheets_Count = $workbook->worksheet_count(); #有多少个Sheet
    
    	#依次访问所有Sheet
    	for ($index=1;$index<=$Sheets_Count;$index++) 
    	{
    		my $worksheet = $workbook->worksheet(@Sheets[$index-1]);
    		my $result;
    		if(!defined $worksheet) #读取Sheet失败
    		{
    			print "Could not get the worksheet \n";
    			last;
    		}
    		else
    		{
    			$result = LoadWordingsFromSheet($worksheet);
    			$TotalCount += $result;
    		}
    	}
    	printf "\nTotal found $TotalCount strings\n";
    	print "Finished!\n";
    }
    
    # 读取Sheet中字串，由LoadStringsFromExcel呼叫
    sub LoadWordingsFromSheet
    {
        my $sheet = $_[0]; #取得传入的sheet
        if(!defined $sheet)
        {
    	   die "Could not get argument!\n";
        }
        
        #得到Sheet中的最小行号及最大行号
        my ($minRow,$maxRow) = $sheet->row_range();
        print "Now, checking ",$sheet->get_name()," \n"; #打印Sheet的名称
    
    	$count = 1;
    	
    	#依次读取每行数据中第一列的数据
        for($i=$minRow;$i<=$maxRow;$i++) 
        {
    		#取到第一列的数据, get_cell(行号，列号)
    		$str = ($sheet->get_cell($i,0))->value(); 
    		$str = trim($str);
    		print $str,"\n";
    		$count++;
        }
    	return $count;
    }
    
    
    #写入Excel
    #使用WriteDataToExcel(文件名)
    sub WriteDataToExcel
    {
    	my $workbook = Spreadsheet::WriteExcel->new(@_[0]);#打开Excel文件
    	if(!defined $workbook) #是否打开成功
    	{
    		print "Failed to open @_[0]\n";
    		die $parser->error(),".\n";
    	}
    	
    	my $worksheet = $workbook->add_worksheet(); #新建一个Sheet
    	if(!defined $sheet)
        {
    	   die "Cannot create new sheet!\n";
        }
    	
    	#写入第一行标题 write(行号，列号，内容)
    	$worksheet->write(0,0,'ID');
    	$worksheet->write(0,1,'RULE');
    	
    	#其它处理
    }