perl 读写excel文件

由于工作任务关系，今天小写了一个perl代码，完成含中文字符的excel文件的解析和重写，记下一些主要代码以便以后查看。

感觉perl对excel文件的操作支持还是蛮到位的。其他语言就不了解了。

 

##引用包：

use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel::FmtUnicode;
use MyExcelFormatter;
use Encode;

。。。

##解析excel文件的子函数

sub GetP2PBusiFlow($$){
 my ($p2pBusiFile,$p2pBusRef) = @_;
 my($iR, $iC, $oWkS, $oWkC);
 my $oExcel = new Spreadsheet::ParseExcel;
 my $fmt = new MyExcelFormatter();
 my $oBook = $oExcel->Parse($p2pBusiFile, $fmt); 
 if(! $oBook){
  print "can not parse $p2pBusiFile\n";
  return 1;
 }
 for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++){ ##sheet页
  $oWkS = $oBook->{Worksheet}[$iSheet];
  my $busiName = $oWkS->{Name};
  for(my $iR = $oWkS->{MinRow}+1 ;
   defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;
   $iR++){   ##行
   #解析第一列城市名称     
   $oWkC = $oWkS->{Cells}[$iR][0];
   ##防止excel格式错误     
    if(!defined $oWkC){
     next;
    }
    my $cityName = $oWkC->Value;
    #解析第一列流量
   $oWkC = $oWkS->{Cells}[$iR][1];
   ##防止excel格式错误     
    if(!defined $oWkC){
     next;
    }
    my $flow = $oWkC->Value; 
    $p2pBusRef->{$busiName}{$cityName} = $flow;
   }
 } 
 return 0; 
}

##写excel文件的子函数

sub SaveToExcel($){
 my ($apportionResltHashRef) = @_;
 my %apportionReslt = %{$apportionResltHashRef};
 my $dstFile = $sdiDir."/Foreign2Mainland.xls";
  my $dstBook = Spreadsheet::WriteExcel->new($dstFile);
  my $bookFormat = &SetBookFmt($dstBook);
 
  &Debug("Save to excel $dstFile ...\n", 0);
  foreach my $busiName (keys %apportionReslt){
   my $flowSheet = $dstBook->add_worksheet( decode("gb2312", $busiName) );
   $flowSheet->set_column(0, 5, 16);
   ##写第一行
   $flowSheet->write(0, 0, "", $bookFormat->{"coltitle"});
   $flowSheet->write(0, 1, decode("gb2312", "回国"),$bookFormat->{"coltitle"});
   $flowSheet->write(0, 2, decode("gb2312", "北京"),$bookFormat->{"coltitle"});
   $flowSheet->write(0, 3, decode("gb2312", "上海"),$bookFormat->{"coltitle"});
   $flowSheet->write(0, 4, decode("gb2312", "广州"),$bookFormat->{"coltitle"});
   ##一行一行写
   my $colNum=1;
   foreach my $forCityName(keys %{$apportionReslt{$busiName}}){
    $flowSheet->write($colNum, 0, decode("gb2312", $forCityName),$bookFormat->{"coltitle"});
    $flowSheet->write($colNum, 1, $p2pBus{$busiName}{$forCityName},$bookFormat->{"data"})
     unless !defined $p2pBus{$busiName}{$forCityName};
    $flowSheet->write($colNum, 2, $apportionReslt{$busiName}{$forCityName}{'北京'},$bookFormat->{"data"})
     unless !defined $apportionReslt{$busiName}{$forCityName}{'北京'};
    $flowSheet->write($colNum, 3, $apportionReslt{$busiName}{$forCityName}{'上海'},$bookFormat->{"data"})
     unless !defined $apportionReslt{$busiName}{$forCityName}{'上海'};
    $flowSheet->write($colNum, 4, $apportionReslt{$busiName}{$forCityName}{'广州'},$bookFormat->{"data"})
     unless !defined $apportionReslt{$busiName}{$forCityName}{'广州'};
    $colNum++;
   }   
  }  
  $dstBook->close(); 
  return;
}

##构建excel文件格式的子函数

sub SetBookFmt($)
{
    my $book = shift;
   
    my %fmt;
   
    $fmt{"coltitle"} = $book->add_format();
    $fmt{"coltitle"}->set_border(2);
    $fmt{"coltitle"}->set_align('center');
    $fmt{"coltitle"}->set_align('top');
    $fmt{"coltitle"}->set_properties(bold => 1);
    $fmt{"coltitle"}->set_font(decode("gb2312",'楷体'));
    $fmt{"coltitle"}->set_size(12);
    #$fmt{"coltitle"}->set_color(1);
    $fmt{"coltitle"}->set_pattern();
    $fmt{"coltitle"}->set_fg_color($book->set_custom_color(20, 97, 202, 255));
    $fmt{"coltitle"}->set_merge(0);
    $fmt{"coltitle"}->set_text_wrap();
   
   
    $fmt{"data"} = $book->add_format();
    $fmt{"data"}->set_border(1);
    $fmt{"data"}->set_align('center');
    $fmt{"data"}->set_align('vcenter');
    $fmt{"data"}->set_text_wrap();
    $fmt{"data"}->set_font(decode("gb2312",'微软雅黑'));
    $fmt{"data"}->set_size(9);
   
    return (\%fmt)
}