论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2014-11-02 18:59 |只看该作者 |倒序浏览

#!/usr/bin/perl -w

use strict;
use warnings;
use DBI;
use Encode;
use strict;
use Time::localtime;
use Data:

umper;
use Time::Local;
use Spreadsheet:

arseExcel;
use POSIX qw(strftime);

my $dbh = DBI->connect( 'DBI:mysql:altadb',
                     '',
                     '',
                     {RaiseError=>1,AutoCommit=>0}
                  )
      || die "Database connection not made: $DBI::errstr";

my $parser = Spreadsheet:

arseExcel->new();
my $workbook = $parser->parse('products.xls');

if ( !defined $workbook ) {
die $parser->error(), ".\n";
}

for my $worksheet ( $workbook->worksheets() ) {

my ( $row_min, $row_max ) = $worksheet->row_range();
#my ( $col_min, $col_max ) = $worksheet->col_range();

#3944
my $count = 0;
for my $row ( $row_min .. 18 ) {
         my $catalog = $worksheet->get_cell( $row, 0 )->unformatted();
         next unless $catalog;
   if($catalog =~ /[^0-9a-zA-Z]/) {
next;
   } else {
my $sth = $dbh->prepare("select t.catalog from kc_product t where t.catalog = '$catalog'"

;
$sth->execute();
if (my @ary = $sth->fetchrow_array()) {
print "exits

ary \n";
next;
}

#$count = $count +1;
my $Cname = $worksheet->get_cell( $row, 1 )->unformatted();
my $Pname = $worksheet->get_cell( $row, 2 )->unformatted();
my $cas = ($worksheet->get_cell( $row, 3 )? $worksheet->get_cell( $row, 3 )->unformatted():'');

my $sql = q{insert INTO kc_product (catalog, title, chemical_name, cas) VALUES (?, ?, ?, ?)};
#$sth = $dbh->prepare("insert INTO kc_product (catalog, title, chemical_name, cas) VALUES (?, ?, ?, ?)"

;

      print $count." ".$sql."\n" ;
      $count = $count + 1;
      $sth = $dbh->prepare($sql);
      $sth->bind_param(1, $catalog);
      $sth->bind_param(2, $Cname);
      $sth->bind_param(3, $Pname);
$sth->bind_param(4,$cas);
$sth->execute();

#my $sql = "INSERT INTO kc_product (catalog, title, chemical_name, cas) VALUES ('$catalog', '$Cname', '$Pname', '$cas')";
#$dbh->do("$sql"

|| die "Couldn't insert record : $DBI::errstr";
print "insert $catalog,qq/$Cname/,$Pname,qq/$cas/\n";

}
}
}
$dbh->disconnect;