Perl读写Excel_perl使用spreadsheet parse save

#读取excel，支持2003 和2007
#LJL
use Encode;
use Spreadsheet::XLSX; 
use Spreadsheet::ParseExcel;


my $file;         #Excel path
my $sheetName;    #WorkSheet name


sub readXlsx2007{
my $excel = Spreadsheet::XLSX -> new ($file,$converter); 
my %field;
my @worksheet = @{$excel -> {Worksheet}};
foreach my $sheet (@{$excel -> {Worksheet}}){
#print encode('gbk',decode('utf8',$sheet->{Name}))."--------".$sheetName;
next if(uc(encode('gbk',decode('utf8',$sheet->{Name})))ne $sheetName);
print "Process the Sheet: ".$sheetName."\n";
my $poiId;
 my $brandName;
 my $address;
    my $lat;
    my $lon; 
    my $province;
    my $city;
    my $county;
    my $phone; 
    my $businessArea;
    my $businessId;    
    my @poiArray;
    my $poiStr;
$sheet -> {MaxRow} ||= $sheet -> {MinRow};        
foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}){
print "Processed [".($row)."/".$sheet -> {MaxRow}."]\n";
$sheet -> {MaxCol} ||= $sheet -> {MinCol};                
foreach my $col ($sheet -> {MinCol} ..  $sheet -> {MaxCol}) {                
my $cell = $sheet -> {Cells} [$row] [$col]; 
my $value = encode('gbk',decode('utf8',$cell->{Val}));
if($row==$sheet -> {MinRow}){
$field{$value}=$col;
next;
}  
$poiId = $value if $col==$field{'POI_ID'};
$brandName = $value if $col==$field{'BRAND_NAME'};
$address = $value if $col==$field{'ADDRESS'};
$lat = $value if $col==$field{'LATITUDE'};
$lon = $value if $col==$field{'LONGTITUDE'};
$province = $value if $col==$field{'PROVINCE'};
$city = $value if $col==$field{'CITY'};
$county = $value if $col==$field{'COUNTY'};
$phone = $value if $col==$field{'PHONE'};
        $businessArea= $value if $col==$field{'BUSINESS_AREA'};
        $businessId= $value if $col==$field{'BUSINESS_ID'};
       
        push @poiArray,$value;
} 
if($row==$row_min){
next;
}
    print "[Source Data]:".$poiId."|".$brandName."|".$address."|".$lat."|".$lon."|".$province."|".$city."|".$county."|".$phone."|".$businessArea."|".$businessId."\n";
$poiStr=$poiId."|".$brandName."|".$address."|".$lat."|".$lon."|".$province."|".$city."|".$county."|".$phone."|".$businessArea."|".$businessId;
my $poi=GEOcoder($poiStr);
print "[Dealer Data]:".$poi."\n";
}
 }
}




sub readXls2003{
my %field;
my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse($file);
if ( !defined $workbook ) {
   die $parser->error(), ".\n";
}
my @worksheet = $workbook->worksheets();
foreach my $currentSheet(@worksheet){
next if(uc(encode('gbk',decode('utf8',$currentSheet->get_name())))ne $sheetName);
print "Process the Sheet: ".$sheetName."\n";

my ( $row_min, $row_max ) = $currentSheet->row_range();
my ( $col_min, $col_max ) = $currentSheet->col_range();
for my $row ( $row_min .. $row_max ) {
print "Processed [".($row)."/".$currentSheet->row_range()."]\n";
my $poiId;
 my $brandName;
 my $address;
   my $lat;
   my $lon; 
   my $province;
   my $city;
   my $county;
   my $phone;
   my $businessArea;
    my $businessId;
    my @poiArray;
    my $poiStr;
  for my $col ( $col_min .. $col_max ) {    
   my $cell = $currentSheet->get_cell( $row, $col );
   next unless $cell;
   my $value = encode('gbk',$cell->value());
   if($row==$row_min){
$field{$value} = $col;
next;
}
$poiId = $value if $col==$field{'POI_ID'};
$brandName = $value if $col==$field{'BRAND_NAME'};
$address = $value if $col==$field{'ADDRESS'};
$lat = $value if $col==$field{'LATITUDE'};
$lon = $value if $col==$field{'LONGTITUDE'};
$province = $value if $col==$field{'PROVINCE'};
$city = $value if $col==$field{'CITY'};
$county = $value if $col==$field{'COUNTY'};
$phone = $value if $col==$field{'PHONE'};
$businessArea= $value if $col==$field{'BUSINESS_AREA'};
        $businessId= $value if $col==$field{'BUSINESS_ID'};
     
        push @poiArray,$value;
}
if($row==$row_min){
next;
}
print "[Source Data]:".$poiId."|".$brandName."|".$address."|".$lat."|".$lon."|".$province."|".$city."|".$county."|".$phone."|".$businessArea."|".$businessId."\n";
$poiStr=$poiId."|".$brandName."|".$address."|".$lat."|".$lon."|".$province."|".$city."|".$county."|".$phone."|".$businessArea."|".$businessId;
my $poi=GEOcoder($poiStr);
print "[Dealer Data]:".$poi."\n";

}
 }

}

========================================================================================================================

#生成excel
#ljl


use Spreadsheet::WriteExcel; 
my %us;
while(($key, $value) = each %us){
print "$key|$value\n";
}




 # 创建一个新的EXCEL文件  
  my $workbook = Spreadsheet::WriteExcel->new('poi_count_top15.xls');  
   
  # 添加一个工作表  
  $worksheet = $workbook->add_worksheet(); 
  
   #  新建一个样式  
  $format = $workbook->add_format(); # Add a format 
  $format->set_bg_color('green');
  $format->set_bold();#设置字体为粗体  
  $format->set_color('red');#设置单元格前景色为红色  
  $format->set_align('center');#设置单元格居中   
  
  
  $format2=$workbook->add_format();
  $format2->set_bg_color('gray');
  $format2->set_bold();#设置字体为粗体 
  $format2->set_align('center');#设置单元格居中 
  
  
  $format3=$workbook->add_format();
  $format3->set_bg_color('orange');
  $format3->set_bold();#设置字体为粗体 
  $format3->set_align('center');#设置单元格居中 
  
  
  $format4=$workbook->add_format();
  $format4->set_bg_color('brown');
  $format4->set_bold();#设置字体为粗体 
  $format4->set_align('center');#设置单元格居中 
   
   
   $worksheet->write(0,0,'Province',$format);
   $worksheet->write(0,1,'us_ta_1',$format);  
   $worksheet->write(0,2,'us_ta_2',$format);  
   $worksheet->write(0,3,'D-value',$format);  
   $worksheet->write(0,4,'Divide us_ta_1',$format);  
   $worksheet->write(0,5,'Divide us_ta_2',$format);  
   $worksheet->write(0,6,'Result',$format);  
   $worksheet->write(0,7,'us_ta_1',$format);  
   $worksheet->write(0,8,'us_ta_2',$format);  
   $worksheet->write(0,9,'D-value',$format);  
   $worksheet->write(0,10,'Divide us_ta_1',$format);  
   $worksheet->write(0,11,'Divide us_ta_2',$format);  
   $worksheet->write(0,12,'Result',$format);                                                              
                                   
   
   $col =0;
   $row = 1;
 for my $key(sort keys %us)
 {
   my @k=split/;/,$us{$key},-1;
    $worksheet->write($row,0,$key,$format2);  
  for($i=1;$i<13;$i++){
 
  if($i<7){
   $worksheet->write($row,$i,$k[$i-1],$format3);  
   }
   
   else
   {
    $worksheet->write($row,$i,$k[$i-1],$format4);
    }
  
 }
  $row++;
}