excel导出mysql_从Excel导出到MySQL

# wanna be generic implementation of xls to mysql upsert in perl

# by this time you should have your mysql connection open ...

use Spreadsheet::XLSX;

use Text::Iconv;

#

# -----------------------------------------------------------------------------

# runs the insert sql by passed data part

# by convention is assumed that the first column is unique and update could

# be performed on it ... should there be duplicates the update should fail

# -----------------------------------------------------------------------------

sub RunUpsertSql {

my $self = shift ;

my $table_name = shift ;

my $refHeaders = shift ;

my $refData = shift ;

my $data_str = '' ;

my @headers = @$refHeaders ;

my @data = @$refData ;

print ( "\@data : @data" ) ;

print ( "\@headers: @headers" ) ;

my $sql_str = " INSERT INTO $table_name " ;

$sql_str .= '(' ;

for ( $i=0; $i

$sql_str .= " $headers[$i] " . ' , ' ;

} #eof for

for (1..3) { chop ( $sql_str) } ;

$sql_str .= ')' ;

foreach my $cellValue ( @data ) {

# replace the ' chars with \'

$cellValue =~ s|\'|\\\'|g ;

$data_str .= "'" . "$cellValue" . "' , " ;

}

#eof foreach ' replacement

# remove the " , " at the end

for (1..3) { chop ( $data_str ) } ;

$sql_str .= " VALUES (" . "$data_str" . ')' ;

$sql_str .= ' ON DUPLICATE KEY UPDATE ' ;

for ( $i=0; $i

$sql_str .= "$headers[$i]" . ' = ' . "'" . "$data[$i]" . "' , " ;

} #eof for

for (1..3) { chop ( $sql_str) } ;

print ( "sql_str : $sql_str " );

$sth = $dbh->prepare($sql_str ) ;

$sth->execute( );

}

#eof sub RunUpsertSql

#

# -----------------------------------------------------------------------------

# walk trough the Excel and build the data part of the insert sql

# -----------------------------------------------------------------------------

sub ParseExcel {

my $self = shift ;

print ( " == START == " ) ;

# not sure if it could work without the next line

# for utf8 strings - slavic , japanese etc.

my $converter = Text::Iconv -> new ("utf-8", "utf-8");

# http://search.cpan.org/~dmow/Spreadsheet-XLSX-0.13-withoutworldwriteables/lib/Spreadsheet/XLSX.pm

my $objExcelParser = Spreadsheet::XLSX -> new ("$FileInputExcel", $converter);

# iterate the sheets

foreach my $objSheet (@{$objExcelParser-> {Worksheet}}) {

print("Sheet: " . $objSheet->{'Name'});

my $rowCount = 0 ;

# iterate the rows

my @headerData = ();

foreach my $row ($objSheet -> {'MinRow'} .. $objSheet -> {'MaxRow'}) {

my @rowData = ();

$objSheet -> {'MaxCol'} ||= $objSheet -> {'MinCol'};

# iterate the coloumns

foreach my $col ($objSheet -> {'MinCol'} .. $objSheet -> {'MaxCol'}) {

my $cell = $objSheet -> {'Cells'} [$row] [$col];

if ($cell) {

#debug printf("( %s , %s ) => %s\n", $row, $col, $cell -> {'Val'});

# the unformatted value

#my $cellValue = $cell->{'Val'} ;

# push the formatted value

push ( @rowData , $cell->value() ) if $rowCount != 0 ;

push ( @headerData , $cell->value() ) if $rowCount == 0 ;

} #eof if the cell is defined

}

#eof foreach col

# by convention the name of the xls sheet is the same as the table name

$self->RunUpsertSql ( $objSheet->{'Name'} , \@headerData , \@rowData)

if $rowCount != 0 ;

$rowCount++ ;

}

#eof foreach row

}

#eof foreach $objSheet

print ( " == STOP == " ) ;

} #eof sub ParseExcel