s18_excel2txt_product.pl.bak

perl-excel_to_txt

#!/usr/bin/perl 
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

my $AUTO_HOME = $ENV{"AUTO_HOME"};
my $AUTO_DATA = "${AUTO_HOME}\\DATA";
$Win32::OLE::Warn = 3;
# get already active Excel application or open new
my $Excel;

sub main ()
{
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time());
    $year += 1900;
    $mon = sprintf("%02d", $mon + 1);
    $mday = sprintf("%02d", $mday);
    my $today = $year.$mon.$mday;
    # Set Working Directory
    my $myWorkDir = $AUTO_DATA . "\\EXCEL";
    chdir ($myWorkDir);

    # Create New Output Files
    open (FILE1, '>S18_PRODUCT_DEPOSIT_LOAN'.$today.'.TXT') or die "Error: can't open S18_PRODUCT_DEPOSIT_LOAN.txt!";
    close (FILE1);
    open (FILE2, '>S18_PRODUCT_SERVICE'.$today.'.TXT') or die "Error: can't open S18_PRODUCT_SERVICE.txt!";
    close (FILE2);
    open (FILE3, '>S18_PRODUCT_PACKAGE'.$today.'.TXT') or die "Error: can't open S18_PRODUCT_PACKAGE.txt!";
    close (FILE3);
    $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');

    # Seek excel files
    my $fullFileName1 = "";
    my $fullFileName2 = "";
   $fullFileName1 = $myWorkDir . "\\" ."Product Subject - CMBC Product List- V3.0-20040630-Retail.xls";
   $fullFileName2 = $myWorkDir . "\\" ."Product Subject - CMBC Product List- V2.5-20040630-cop.xls"; 
   #print "\n".$fullFileName."\n" ;
   print "\n\n处理个人业务文件\n$fullFileName1----\n\n";
   processOneExcelFile ($fullFileName1,$today);

   print "\n\n处理对公业务文件\n$fullFileName2----\n\n";
   processOneExcelFile ($fullFileName2,$today);

   # Generate Control File
    return (genCtrlFile ());

}

#------------------------------------------------------------------------------
# 子 程 序：processOneExcelFile
# 程序版本：V1.0
# 作    者：yang_dapeng
# 日    期：2004-5-19
# 输    入: EXCEL 文件名称(全称路径)
# 输    出：无
# 描    述：
# 修改记录：
#------------------------------------------------------------------------------
sub processOneExcelFile
{
    my $today = $_[1];
    my @dateArray;

    open (FILE1, '>>S18_PRODUCT_DEPOSIT_LOAN'.$today.'.TXT') or die "Error: can't open S18_PRODUCT_DEPOSIT_LOAN.txt!";
    open (FILE2, '>>S18_PRODUCT_SERVICE'.$today.'.TXT') or die "Error: can't open S18_PRODUCT_SERVICE.txt!";
    open (FILE3, '>>S18_PRODUCT_PACKAGE'.$today.'.TXT') or die "Error: can't open S18_PRODUCT_PACKAGE.txt!";
    #printf "Input Date is " . $inputDate . "\n";

    # open Excel file
    my $Book = $Excel->Workbooks->Open("$_[0]");

    # You can dynamically obtain the number of worksheets, rows, and columns
    # through the Excel OLE interface.  Excel's Visual Basic Editor has more
    # information on the Excel OLE interface.
############################################################
    my $row = 3;
    my $col = 1;
    # process sheet2  存款的产品编码信息
    # select worksheet number (you can also select a worksheet by name)
    print "\n处理源文件sheet2--存款的产品编码信息\n";
    my $Sheet = $Book->Worksheets(2);
    my $tempString = $Sheet->Cells($row,1)->{'Text'};
 while (length($tempString) eq 11)
    {   if (($row%30)==0 )
            {print "\n";
            	}
    	print "..";
  for (my $temrow1 =3;$temrow1 < $row;$temrow1++)
       { my $tempString1 = $Sheet->Cells($temrow1,1)->{'Text'};
        # print $tempString."\n";
        # print $tempString2."\n";
         if ($tempString eq $tempString1 )
          {
      printf ("\n产品_id = $tempString1  第 %d 行和第  %d 行重复，请更正! \n",$temrow1,$row);
      $row++;
      $tempString = $Sheet->Cells($row,1)->{'Text'};
      last
            }
      }
       printf FILE1 $Sheet->Cells($row, 1)->{'Value'};
       foreach  $col(2..21)
   	 { #print "\nrow=".$row."    ";
           #print "col =".$col."\n";
           #print $Sheet->Cells($row, $col)->{'Value'}
         printf FILE1  "\t".$Sheet->Cells($row, $col)->{'Value'};
   	 }
      printf FILE1 "\n";
      $row++;
      $tempString = $Sheet->Cells($row,1)->{'Text'};
     }

############################################################
    my $row = 3;
    my $col = 1;
    # process sheet3  贷款的产品编码信息
    # select worksheet number (you can also select a worksheet by name)
    print "\n处理源文件sheet3--贷款的产品编码信息\n";
    my $Sheet = $Book->Worksheets(3);
    my $tempString = $Sheet->Cells($row,1)->{'Text'};
 while (length($tempString) eq 11)
    {   if (($row%30)==0 )
            {print "\n";
            	}
    	print "..";
  for (my $temrow1 =3;$temrow1 < $row;$temrow1++)
       { my $tempString1 = $Sheet->Cells($temrow1,1)->{'Text'};
        # print $tempString."\n";
        # print $tempString2."\n";
         if ($tempString eq $tempString1 )
          {
      printf ("\n产品_id = $tempString1  第 %d 行和第  %d 行重复，请更正! \n",$temrow1,$row);
      $row++;
      $tempString = $Sheet->Cells($row,1)->{'Text'};
      last
            }
      }
       printf FILE1 $Sheet->Cells($row, 1)->{'Value'};
       foreach  $col(2..21)
   	 { #print "\nrow=".$row."    ";
           #print "col =".$col."\n";
           #print $Sheet->Cells($row, $col)->{'Value'}
         printf FILE1  "\t".$Sheet->Cells($row, $col)->{'Value'};
   	 }
      printf FILE1 "\n";
      $row++;
      $tempString = $Sheet->Cells($row,1)->{'Text'};
     }
    
############################################################
    my $row = 3;
    my $col = 1;
    # process sheet4  结算与贸易融资的产品编码信息
    # select worksheet number (you can also select a worksheet by name)
    print "\n处理源文件sheet4--结算与贸易融资的产品编码信息\n";
    my $Sheet = $Book->Worksheets(4);
    my $tempString = $Sheet->Cells($row,1)->{'Text'};
 while (length($tempString) eq 11)
    {   if (($row%30)==0 )
            {print "\n";
            	}
    	print "..";
  for (my $temrow1 =3;$temrow1 < $row;$temrow1++)
       { my $tempString1 = $Sheet->Cells($temrow1,1)->{'Text'};
        # print $tempString."\n";
        # print $tempString2."\n";
         if ($tempString eq $tempString1 )
          {
      printf ("\n产品_id = $tempString1  第 %d 行和第  %d 行重复，请更正! \n",$temrow1,$row);
      $row++;
      $tempString = $Sheet->Cells($row,1)->{'Text'};
      last
            }
      }
       printf FILE2 $Sheet->Cells($row, 1)->{'Value'};
       foreach  $col(2..16)
   	 { #print "\nrow=".$row."    ";
           #print "col =".$col."\n";
           #print $Sheet->Cells($row, $col)->{'Value'}
         printf FILE2  "\t".$Sheet->Cells($row, $col)->{'Value'};
   	 }
      printf FILE2 "\n";
      $row++;
      $tempString = $Sheet->Cells($row,1)->{'Text'};
     }
 ############################################################
    my $row = 3;
    my $col = 1;
    # process sheet5  服务的产品编码信息
    # select worksheet number (you can also select a worksheet by name)
    print "\n处理源文件sheet5--服务的产品编码信息\n";
    my $Sheet = $Book->Worksheets(5);
    my $tempString = $Sheet->Cells($row,1)->{'Text'};
 while (length($tempString) eq 11)
    {   if (($row%30)==0 )
            {print "\n";
            	}
    	print "..";
  for (my $temrow1 =3;$temrow1 < $row;$temrow1++)
       { my $tempString1 = $Sheet->Cells($temrow1,1)->{'Text'};
        # print $tempString."\n";
        # print $tempString2."\n";
         if ($tempString eq $tempString1 )
          {
      printf ("\n产品_id = $tempString1  第 %d 行和第  %d 行重复，请更正! \n",$temrow1,$row);
      $row++;
      $tempString = $Sheet->Cells($row,1)->{'Text'};
      last
            }
      }
       printf FILE2 $Sheet->Cells($row, 1)->{'Value'};
       foreach  $col(2..11)
   	 { #print "\nrow=".$row."    ";
           #print "col =".$col."\n";
           #print $Sheet->Cells($row, $col)->{'Value'}
         printf FILE2  "\t".$Sheet->Cells($row, $col)->{'Value'};
   	 }
      printf FILE2 "\n";
      $row++;
      $tempString = $Sheet->Cells($row,1)->{'Text'};
     }
 ############################################################
    my $row = 3;
    my $col = 1;
    # process sheet6  产品包编码信息
    # select worksheet number (you can also select a worksheet by name)
    print "\n处理源文件sheet6--产品包编码信息\n";
    my $Sheet = $Book->Worksheets(6);
    my $tempString = $Sheet->Cells($row,1)->{'Text'};
 while (length($tempString) eq 11)
    {   if (($row%30)==0 )
            {print "\n";
            	}
    	print "..";
  for (my $temrow1 =3;$temrow1 < $row;$temrow1++)
       { my $tempString1 = $Sheet->Cells($temrow1,1)->{'Text'};
        # print $tempString."\n";
        # print $tempString2."\n";
         if ($tempString eq $tempString1 )
          {
      printf ("\n产品_id = $tempString1  第 %d 行和第  %d 行重复，请更正! \n",$temrow1,$row);
      $row++;
      $tempString = $Sheet->Cells($row,1)->{'Text'};
      last
            }
      }
       printf FILE1 $Sheet->Cells($row, 1)->{'Value'};
       foreach  $col(2..21)
   	 { #print "\nrow=".$row."    ";
           #print "col =".$col."\n";
           #print $Sheet->Cells($row, $col)->{'Value'}
         printf FILE1  "\t".$Sheet->Cells($row, $col)->{'Value'};
   	 }
      printf FILE1 "\n";
      $row++;
      $tempString = $Sheet->Cells($row,1)->{'Text'};
     }
 

####################################################################
     $row = 3; 
     $col = 1;
    # process sheet7  产品包和产品的对应关系
    # select worksheet number (you can also select a worksheet by name)
    print "\n处理源文件的sheet7--产品包和产品的对应关系\n";
     $Sheet = $Book->Worksheets(7);
     $tempString = $Sheet->Cells($row,1)->{'Text'};
    while (length($tempString) eq 11)
    {   if (($row%30)==0 )
            {print "\n";
            	}
    	print "..";
   printf FILE3 $Sheet->Cells($row, 1)->{'Value'};
    foreach  $col(2..4)
    {
     #print "\nrow=".$row."    ";
     #print "col =".$col."\n";
     #print $Sheet->Cells($row, $col)->{'Value'}
     printf FILE3  "\t" . $Sheet->Cells($row, $col)->{'Value'};
    }
    printf FILE3 "\n";

    $row++;
    $tempString = $Sheet->Cells($row,1)->{'Text'};
    }
###################################################################
    # clean up after ourselves
    $Book->Close;
    close (FILE1);
    close (FILE2);
    close (FILE3);
}

#------------------------------------------------------------------------------
# 子 程 序：genCtrlFile
# 程序版本：V1.0
# 作    者：RUFF (NCR)
# 日    期：2003-10-30
# 输    入:
# 输    出：无
# 描    述：
#
# 修改记录：
#------------------------------------------------------------------------------
sub genCtrlFile()
{
    my $filename;
    my @fields;
    my $basefn;
    my $size;

    unless ( opendir(SRC_DIR, ".") )
    {
        print "ERROR - Unable to open dir .\n";
        return -1;
    }
    while (${filename} = readdir(SRC_DIR))
    {
        if ( $filename eq "." || $filename eq ".." ) { next; }
        # If the file is directory then skip it
        if ( -d "${filename}" ) { next; }
        @fields = split(/\./, $filename);
        $basefn = $fields[0];
        $basefn =~ tr [A-Z][a-z];
        $fields[1]  =~ tr [A-Z][a-z];
        if ($fields[1] eq "txt")
#        if (uc($filename) =~ /TABLE\d{9}.TXT/)
        {
            $size = (stat("${filename}"))[7];
            unless(open(OUTFILE, ">${AUTO_DATA}\\receive\\dir.${basefn}"))
            {
                print STDOUT "\nCan not open file $AUTO_DATA\\receive\\dir.${basefn}\n";
                return -1;
	    }
            print "\n$filename  $size ===>${AUTO_DATA}\\receive\\dir.${basefn}\n";
            print OUTFILE "$filename  $size";
            close(OUTFILE);
            print "Move ${filename} ==>${AUTO_DATA}\\receive\\${filename}\n\n";
            rename("${filename}", "${AUTO_DATA}\\receive\\${filename}");
        }
    }
    closedir(SRC_DIR);
}
######################################################################
# program section

my $ret = main();

exit($ret);