1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
| # !usr/perl/bin/
# Modules nécessaires
use strict;
#use warnings;
use Win32::OLE qw(in with);
use CGI::Carp qw(fatalsToBrowser);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;
use DBI;
$| = 1;
use Cwd;
# database information
my $db="##TA BASE##";
my $host="localhost";
my $userid="root";
my $passwd="";
my $connectionInfo="dbi:mysql:$db;$host";
# make connection to database
my $dbh = DBI->connect($connectionInfo,$userid,$passwd) or die"Couldn't connect to DB" . DBI->errstr;;
# Création d'une nouvelle instance du package pour Excel
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
my $dir = cwd;
#on cherche tout les fichier XLS du dossier courant
my @Listexls = <*.xls>;
foreach my $scalar (@Listexls)
{
# Ouverture du fichier Excel à importer
my $Book = $Excel->Workbooks->Open("$dir/$scalar");
# Affectation du 1er classeur à la variable $Sheet
my $Sheet = $Book->Worksheets(1);
my $line= 2;
my $toto=0;
my $col = 1;
my $cel;
my $sth;
my $query = "INSERT into publi (`ident` , `numero` , `type` , `pt` , `au` , `ca` , `ti` , `so` , `se` , `bs` , `ed` , `la` , `dt` , `ce` , `sc` , `de` , `id` , `ab` , `c1` , `rp` , `em` , `pu` , `pi` , `pa` , `wp` , `sn` , `bn` , `j9` , `ji` , `py` , `pd` , `vl` , `is` , `pn` , `su` , `si` , `bp` , `ep` , `ar` , `pg` , `ga` , `ut`)
VALUES (";
while($Sheet->Cells($line,'A')->{'Value'}){
my $var = $Sheet->Cells($line,$col)->{'Value'};
if ($var =~ /CCC:/){# Si l'élément de la case excel contient CCC alors dernière info on fini la requête
$query .="'$var')";
$line++;
$col = 1;
#print "$query\n";
print "$toto\n";
$toto++;
$sth = $dbh->prepare($query); # et on l'envoi
$sth->execute;
# puis on réinitialise
$query = "INSERT into publi (`ident` , `numero` , `type` , `pt` , `au` , `ca` , `ti` , `so` , `se` , `bs` , `ed` , `la` , `dt` , `ce` , `sc` , `de` , `id` , `ab` , `c1` , `rp` , `em` , `pu` , `pi` , `pa` , `wp` , `sn` , `bn` , `j9` , `ji` , `py` , `pd` , `vl` , `is` , `pn` , `su` , `si` , `bp` , `ep` , `ar` , `pg` , `ga` , `ut`) VALUES (";
}
elsif($var){ #sinon si valeur alors on la glisse dans la requête
$var =~ s/\'/ /g;
$query .="'$var',";
$col++;
}
else{ #sinon on mais '' dans la reqête
$query .="'',";
$col++;
}
}
# }
close $Book;
} |
[Excel] Sans modification sur le fichier
Répondre avec citation
;
Partager