Perl读写Excel

#读取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',$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',$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',$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|$valuen"; } ?# 创建一个新的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,'Province',$format); ? ?$worksheet->write(0,1,'us_ta_1',$format); ? ? ?$worksheet->write(0,2,'us_ta_2',3,'D-value',4,'Divide us_ta_1',5,'Divide us_ta_2',6,'Result',7,8,9,10,11,12,$format); ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?$col =0; ? ?$row = 1; ?for my $key(sort keys %us) ?{ ? ?my @k=split/;/,$us{$key},-1; ? ? $worksheet->write($row,$key,$format2); ? ?for($i=1;$i<13;$i++){ ? ?if($i<7){ ??$worksheet->write($row,$i,$k[$i-1],$format3); ? ??} ? ? ? ?else ? ?{ ? ?$worksheet->write($row,$format4); ? ?} ?? ?} ? $row++; }?