88 lines
2.6 KiB
Perl
88 lines
2.6 KiB
Perl
package SQL2XLS;
|
|
#===============================================================================
|
|
#
|
|
# FILE: SQL2XLS.pm
|
|
#
|
|
# DESCRIPTION: truc qui prend une requête SQL et renvoie un tableau Excel
|
|
#
|
|
# OPTIONS: ---
|
|
# REQUIREMENTS: ---
|
|
# BUGS: ---
|
|
# NOTES: ---
|
|
# AUTHOR: grouch'
|
|
# ORGANIZATION: non
|
|
# VERSION: 1.0
|
|
# CREATED: 28/03/17
|
|
# CHANGE_LOG:
|
|
#
|
|
#===============================================================================
|
|
use Carp;
|
|
use DBI;
|
|
use Modern::Perl;
|
|
use Excel::Writer::XLSX;
|
|
use Exporter;
|
|
use Data::Dumper;
|
|
|
|
#trucs que je comprends pas tout
|
|
our @ISA = qw( Exporter );
|
|
our @EXPORT = ();
|
|
our @EXPORT_OK = qw(SQL2XLS);
|
|
use subs qw(SQL2XLS);
|
|
|
|
#-------------------------------------------------------------------------------
|
|
# SUB SQL2XLS
|
|
# arguments : \%db \%requete, $filename
|
|
#-------------------------------------------------------------------------------
|
|
|
|
sub SQL2XLS{
|
|
my $ref_db = shift;
|
|
my $ref_requete = shift;
|
|
my $filename = shift;
|
|
|
|
# connexion base
|
|
say "dbi:Pg:dbname=${$ref_db}{dbname};host=${$ref_db}{host};port=${$ref_db}{port}";
|
|
my $dbh = DBI->connect("dbi:Pg:dbname=$ref_db->{dbname};host=$ref_db->{host};port=$ref_db->{port}",
|
|
$ref_db->{user},
|
|
$ref_db->{password},
|
|
{AutoCommit => 1, RaiseError => 1, PrintError => 0}
|
|
);
|
|
|
|
#création du fichier Excel
|
|
my $wb = Excel::Writer::XLSX->new($filename);
|
|
$wb->set_properties(
|
|
title => 'SQL2XLS',
|
|
author => 'C.GRUSZKA',
|
|
);
|
|
|
|
#on boucle pour créer tous les onglets du workbook
|
|
foreach my $tab (keys %{$ref_requete}){
|
|
#création de l'onglet
|
|
say "\n\nOnglet $tab";
|
|
my $ws = $wb->add_worksheet($tab);
|
|
$ws->set_zoom(85);
|
|
|
|
#récupèration de la requête SQL associée à l'onglet
|
|
my $sql = $ref_requete->{$tab};
|
|
|
|
#requêtage !
|
|
my $sth = $dbh->prepare($sql);
|
|
$sth->execute;
|
|
my $list = $sth->fetchall_arrayref;
|
|
my $colonnes = $sth->{NAME};
|
|
my $nb_col = scalar @$colonnes;
|
|
say "Nombre de colonnes : $nb_col";
|
|
my $nb_items = scalar @$list;
|
|
say "Nombre de lignes : $nb_items";
|
|
|
|
#construction de l'array de hash des entêtes de colonnes (à tes souhaits)
|
|
my @arraycol;
|
|
foreach my $col (@{$colonnes}){
|
|
push @arraycol, { header => "$col" };
|
|
}
|
|
|
|
#table EXCEL
|
|
$ws->add_table(0,0,$nb_items,$nb_col-1, { data => $list, header_row => 1, autofilter => 1,style => 'Table Style Medium 5', columns => \@arraycol });
|
|
}
|
|
}
|
|
1;
|