本地传输电路的提取

#!/usr/bin/perl
##############
#	本地传输电路的提取
#
#
#
##############


use Cwd;
use Data::Dumper;
use Spreadsheet::ParseExcel::FmtUnicode;
use Spreadsheet::ParseExcel;

my ($file_name,$sheet_name,$sheet_num,$row,$col,$cell);
############本站
my ($local_col,$local_row);
my ($local_site,$local_site_col,$local_site_row);
my ($local_SDH,$local_SDH_col,$local_SDH_row);
############对
my ($remote_col,$remote_row);
my ($remote_site,$remote_site_col,$remote_site_row);
my ($remote_SDH,$remote_SDH_col,$remote_SDH_row);
############插1,局向,新电路编号,使用日期,备注
my ($insert,$insert_col,$insert_row);
my ($forward,$forward_col,$forward_row);
my ($cir,$cir_col,$cir_row);
my ($date,$date_col,$date_row);
my ($remark,$remark_col,$remark_row);


my ($iC,$iR);
my $work_cell;


open (LOG,">log.log");


print "file name:\n";
$file_name=<STDIN>;
chomp $file_name;

my $oExcel = new Spreadsheet::ParseExcel; #创建excel对象
my $oFmtC = Spreadsheet::ParseExcel::FmtUnicode->new(Unicode_Map=>"CP936"); #设置字符集为CP936
my $oBook = $oExcel->Parse($file_name,$oFmtC); #打开文件
my $work_sheet;
for($sheet_num=2;$sheet_num < $oBook->{SheetCount};$sheet_num++){
	$work_sheet=$oBook->{Worksheet}[$sheet_num];
#	print LOG "$sheet_num";
#	print LOG "$work_sheet->{Name}\n";
NEXT:
#	for($iR = $work_sheet->{MinRow}; defined $work_sheet->{MaxRow} && $iR <= $work_sheet->{MaxRow}; $iR++)
	for($iR = 2; defined $work_sheet->{MaxRow} && $iR <= $work_sheet->{MaxRow}; $iR++)

	{
		for( $iC = $work_sheet->{MinCol}; defined $work_sheet->{MaxCol} && $iC <= $work_sheet->{MaxCol}; $iC++ )
		{
			$work_cell=$work_sheet->{Cells}[$iR][$iC];
			my $value = $work_cell->Value if($work_cell);
			my $flag=vali($value);
			next NEXT if($flag=~/next/);
		}
		w_local_site();
		w_local_SDH();
		w_remote_site();
		w_remote_SDH();
		w_insert();
		w_forward();
		w_cir();
		w_date();
		w_remark();
		write_line();
	}
}

sub vali(){
	my $value=shift;
	if($value=~/本端/){
		$local_site_col=$iC;
		$local_SDH_col=$iC+1;
	}
	if($value=~/对端/){
		$remote_site_col=$iC;
		$remote_SDH_col=$iC+1;
	}
	if($value=~/插1/){
		$insert_col=$iC;
	}
	if($value=~/局向/){
		$forward_col=$iC;
	}
	if($value=~/新电路编号/){
		$cir_col=$iC;
	}
	if($value=~/使用日期/){
		$date_col=$iC;
	}
	if($value=~/备注/){
		$remark_col=$iC;
		$iR++;
		return "next";
	}
}

sub w_local_site(){
	$work_cell=$work_sheet->{Cells}[$iR][$local_site_col];
	$local_site=$work_cell->Value if($work_cell);
}
		
sub w_local_SDH(){
	$work_cell=$work_sheet->{Cells}[$iR][$local_SDH_col];
	$local_SDH=$work_cell->Value if($work_cell);
}
sub w_remote_site(){
	$work_cell=$work_sheet->{Cells}[$iR][$remote_site_col];
	$remote_site=$work_cell->Value if($work_cell);
}
sub w_remote_SDH(){
	$work_cell=$work_sheet->{Cells}[$iR][$remote_SDH_col];
	$remote_SDH=$work_cell->Value if($work_cell);	
}
sub w_insert(){
	$work_cell=$work_sheet->{Cells}[$iR][$insert_col];
	$insert=$work_cell->Value if($work_cell);	
}
sub w_forward(){
	$work_cell=$work_sheet->{Cells}[$iR][$forward_col];
	$forward=$work_cell->Value if($work_cell);		
}
sub w_cir(){
	$work_cell=$work_sheet->{Cells}[$iR][$cir_col];
	$cir=$work_cell->Value if($work_cell);	
}
sub w_date(){
	$work_cell=$work_sheet->{Cells}[$iR][$date_col];
	$date=$work_cell->Value if($work_cell);	
}
sub w_remark(){
	$work_cell=$work_sheet->{Cells}[$iR][$remark_col];
	$remark=$work_cell->Value if($work_cell);	
}

sub write_line(){
	print LOG "$local_site|$local_SDH|$remote_site|$remote_SDH|$insert|$forward|$cir|$date|$remark\n"
	
}