Exportation à partir d'Excel vers MySQL

Regarder dans Excel de l'enregistrement de données en tant que fichier CSV et ensuite de chargement à MySQL. Essayez de formater les données dans Excel, en fonction de la structure 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<scalar (@headers);$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<scalar(@headers);$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