Introduction

Pour réaliser des extractions Excel, il faut l'objet graphique Adelia Visual Studio Client Excel ou faire appel à la méthode Exporter_Liste d'une liste graphique.
Nous aborderons ici la solution du client Excel dans le cadre d'une sortie de document.

Il est nécessaire qu'Excel soit installé sur le poste qui exécute le programme Visual pour que le client Excel fonctionne.

 

Mise en œuvre

Le lien avec l'application Excel est effectué au travers d'un objet graphique. Il faut donc créer un programme interactif et placer dans une fenêtre l'objet graphique Client Excel.

Cet objet est nécessaire mais il n'est pas obligatoire d'afficher la fenêtre.

Initialisation

En premier lieu, il faut établir une communication entre le programme Adelia Visual et le logiciel Microsoft Excel. Pour cela on utilise la méthode APPLI_CONNECTER de l'objet graphique Client Excel (ici nommé CLI_EXCEL).

Par exemple :

Déclaration
Num_Bin_4  CodeRetour
Bool       TopVisible
Alpha(260) NomModele
Alpha(50)  NomFeuille
Alpha(260) NomClasseur
Bool       SauverModif
Traitement
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL APPLI_CONNECTER CodeRetour

On va ensuite rendre non visibilite l'application Excel avec la méthode APPLI_FIXER_VISIBILITE.

Le fait de rendre non visible la feuille alimentée permet d'obtenir un temps de traitement bien meilleur.

TopVisible = *FAUX
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL APPLI_FIXER_VISIBILITE TopVisible CodeRetour

Ensuite il existe deux solutions pour réaliser une sortie vers un classeur Excel :

  1. Ouvrir un modèle existant
  2. Créer un nouveau classeur

Ouverture d'un modèle existant :

Utiliser la méthode CLASS_OUVRIR et activer la feuille dans laquelle on souhaite écrire à l'aide de la méthode FEUIL_ACTIVER :

NomModele  = 'C:\Modele1.xls'
NomFeuille = 'MaFeuil'
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL CLASS_OUVRIR  NomModele  CodeRetour
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL FEUIL_ACTIVER NomFeuille CodeRetour

Création d'un nouveau classeur :

Créer un nouveau classeur de type feuille et fixer le nom de la feuille active avec les méthodes CLASS_CREER et FEUIL_FIXER_NOM.

NomFeuille = 'MaFeuil'
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL CLASS_CREER \_EXL_FEUILLE_CALCUL CodeRetour
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL FEUIL_FIXER_NOM NomFeuille CodeRetour

Ecriture des données

Il faudra ensuite traiter les informations à envoyer vers Excel afin de composer votre document.
Pour cela reportez-vous à la partie détaillant l'écriture et la mise en forme ci après.

Enregistrement/Affichage du document

Une fois la feuille générée, plusieurs possibilités s'offrent au développeur :

  1. Afficher la feuille à votre utilisateur sans faire d'enregistrement, 
  2. Enregistrer la feuille puis fermer le classeur et l'application Excel,
  3. Enregistrer la feuille et l'afficher à l'utilisateur.
/* ___ Pour rendre l'application visible (points 1,2 et 3) ______________________________
TopVisible = *VRAI
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL APPLI_FIXER_VISIBILITE TopVisible CodeRetour

/* ___ Pour enregistrer le classeur (points 2 et 3) _____________________________________
NomClasseur = 'C:\NouveauClasseur.xls'
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL CLASS_ENREGISTRER_SOUS NomClasseur CodeRetour

* ___ Pour fermer le classeur après l'avoir enregistré (point 2) ________________________
SauverModif = *Vrai
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL CLASS_FERMER SauverModif NomClasseur CodeRetour

* ___ Pour déconnecter le programme de l'application Excel (points 1, 2 et 3) ___________
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL APPLI_DECONNECTER CodeRetour

Ecriture du détail de la feuille et mise en forme

Cellules et plages

En préambule, il faut savoir qu'afin de traiter de l'écriture ou de la mise en forme de cellules dans Microsoft Excel vous devez manipuler une plage.
La définition d'une plage est constituée de la position de la cellule de départ et si nécessaire de la position de la cellule de fin avec le symbole : comme séparateur.
La position d'une cellule est représentée par la position dans la colonne suivie de la ligne.
Par exemple : B7 => ce qui représente la colonne B et la ligne 7
Pour une plage vous pouvez écrire par exemple : A1:D1 => ce qui représente les 4 premières colonnes de la première ligne.
Il est également possible de définir une plage portant sur plusieurs colonnes et plusieurs lignes tel que : A1:D123 => ce qui représente les 123 premières lignes des 4 premières colonnes.

Limitations

Il faut noter que Microsoft Excel comporte des limitations en fonction de la version utilisée.

Par exemple, dans sa version 2000 il n'est pas possible d'avoir plus de 65536 lignes et 256 colonnes. Il existe encore bien d'autres limitations toutes aussi importantes à prendre en compte dans votre application telles que :

  • Largeur des colonnes 255 caractères
  • Hauteur des lignes 409 points
  • Longueur du contenu des cellules (texte) 32 767 caractères
  • Nombre maximal de couleurs par classeur 56
  • Nombre maximal de styles de cellules par classeur 4 000
  • D'autres limitations basées sur la limite de la taille mémoire

Méthodes pour l'écriture et la mise en forme de cellule

Afin de pouvoir positionner une information dans une cellule, vous avez à votre disposition la méthode PLAGE_FIXER_VALEURS qui nécessite une plage de cellules, une donnée simple ou un tableau de données à écrire ainsi que le nombre d'éléments à prendre en compte. Vous pourriez avoir un tableau de 200 postes et pour autant ne vouloir en écrire que 10.
Une autre possibilité est de vous appuyer sur le système de formule de calcul au travers de la méthode PLAGE_FIXER_FORMULE_LOCAL. Vous avez donc la possibilité d'utiliser toutes les fonctions offertes par la version d'Excel de la même manière que vous auriez rédigé la formule.
En ce qui concerne la mise en forme des cellules, de nombreuses méthodes sont à disposition dont les principales sont :

  • PLAGE_AUTO_AJUSTER
  • PLAGE_FIXER_HAUTEUR_LIGNE
  • PLAGE_FIXER_LARGEUR_COLONNE
  • PLAGE_FUSIONNER
  • PLAGE_FIXER_ALIGNEMENT_HORIZ
  • PLAGE_FIXER_ALIGNEMENT_VERT
  • PLAGE_FIXER_ORIENTATION
  • PLAGE_PERMETTRE_RENVOI_LIGNE
  • PLAGE_FIXER_CONTOUR
  • PLAGE_FIXER_COULEUR_FOND
  • PLAGE_FIXER_POLICE
  • PLAGE_FIXER_STYLES_POLICE
  • PLAGE_FIXER_COULEUR_POLICE
  • PLAGE_FIXER_FORMAT_EDITION_LOCAL

Exemple d'une sortie d'un entête :

Déclaration
Alpha(100)  TAB_ALP_CONTENU_LIGNE(50)
Num_Bin_4   W_NB4_NB_ELEMENTS
Num_E(9,0)  W_NUM_NUMERO_LIGNE
Num_E(9,0)  W_NUM_1ERE_LIGNE
Alpha(200)  W_ALP_PLAGE
Num_Bin_4   W_NB4_CODE_RETOUR
Alpha(250)  W_ALP_POLICE
Num_E(6,2)  W_NUM_TAILLE
Num_Bin_4   W_NB4_STYL_LIG
Num_Bin_4   W_NB4_EP_TRAIT
Num_Bin_4   W_NB4_ALIGNH
Num_Bin_4   W_NB4_ALIGNV
Num_Bin_4   W_NB4_HAUT
Bool        W_BOO_GRAS
Bool        W_BOO_ITAL
Bool        W_BOO_BARRE
Num_Bin_4   W_NB4_SOULIG
Traitement
/* ___ Information de mise en page ______________________________________________________
W_ALP_POLICE      = 'Arial'
W_NUM_TAILLE      = 9
W_NB4_STYL_LIG    = _EXL_STYLE_LIGNE_CONTINU
W_NB4_EP_TRAIT    = _EXL_EPAISSEUR_LIGNE_MOYENNE
W_NB4_ALIGNH      = _EXL_ALIGNEMENT_HORIZ_CENTRE
W_NB4_ALIGNV      = _EXL_ALIGNEMENT_VERT_CENTRE
W_NB4_HAUT        = 60
W_BOO_GRAS        = *FAUX
W_BOO_ITAL        = *FAUX
W_BOO_BARRE       = *FAUX
W_NB4_SOULIG      = _EXL_SOULIGNEMENT_AUCUN

TAB_ALP_CONTENU_LIGNE(1)  = 'Client'
TAB_ALP_CONTENU_LIGNE(2)  = 'Projet'
TAB_ALP_CONTENU_LIGNE(3)  = 'Budget initial'
TAB_ALP_CONTENU_LIGNE(4)  = 'Début'
TAB_ALP_CONTENU_LIGNE(5)  = 'Fin'
TAB_ALP_CONTENU_LIGNE(6)  = 'Conso'
TAB_ALP_CONTENU_LIGNE(7)  = 'RàF'
TAB_ALP_CONTENU_LIGNE(8)  = 'Gain/Dérive'
TAB_ALP_CONTENU_LIGNE(9)  = 'Commentaires techniques'
W_NB4_NB_ELEMENTS         = 9

/* ___ Définir la plage (de la colonne A jusqu'à I pour la première ligne) ______________
W_NUM_NUMERO_LIGNE = 1
W_ALP_PLAGE   = 'A' /// W_NUM_NUMERO_LIGNE /// ': ' // I' /// W_NUM_NUMERO_LIGNE

/* ___ Ecrire la ligne __________________________________________________________________
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL  PLAGE_FIXER_VALEURS   W_ALP_PLAGE TAB_ALP_CONTENU_LIGNE W_NB4_NB_ELEMENTS W_NB4_CODE_RETOUR

/* ___ Définir la police de caractère ___________________________________________________
W_ALP_POLICE = ''
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL  PLAGE_FIXER_POLICE   W_ALP_PLAGE W_ALP_POLICE W_NUM_TAILLE W_NB4_CODE_RETOUR

/* ___ Définir le style des cellules ____________________________________________________
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL  PLAGE_FIXER_STYLES_POLICE  W_ALP_PLAGE W_BOO_GRAS W_BOO_ITAL W_BOO_BARRE W_NB4_SOULIG W_NB4_CODE_RETOUR

/* ___ Définir l'alignement des cellules ________________________________________________
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL  PLAGE_FIXER_ALIGNEMENT_HORIZ  W_ALP_PLAGE W_NB4_ALIGNH W_NB4_CODE_RETOUR
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL  PLAGE_FIXER_ALIGNEMENT_VERT  W_ALP_PLAGE W_NB4_ALIGNV W_NB4_CODE_RETOUR

/* ___ Définir le contour des cellules __________________________________________________
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL  PLAGE_FIXER_CONTOUR  W_ALP_PLAGE W_NB4_STYL_LIG W_NB4_EP_TRAIT W_NB4_CODE_RETOUR

/* ___ Ajuster la taille des colonnes au contenu des titres de colonnes _________________
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL  PLAGE_AUTO_AJUSTER  W_ALP_PLAGE W_NB4_CODE_RETOUR

/* ___ Définir la hauteur de la ligne ___________________________________________________
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL  PLAGE_FIXER_HAUTEUR_LIGNE  W_ALP_PLAGE W_NB4_HAUT W_NB4_CODE_RETOUR

/* ___ Changer la couleur de fond des colonnes __________________________________________
/* ___ R0 V0 B255 (BLEU) ________________________________________________________________
W_NB4_VALEUR_RVB = (0)+(0*256)+(255*65536) /\* Rouge+Vert*256+Bleu*65536
Appeler_Methode FEN_VIS_EDITION.CLI_EXCEL  PLAGE_FIXER_COULEUR_FOND  W_ALP_PLAGE W_NB4_VALEUR_RVB W_NB4_CODE_RETOUR

/* ___ Changer la couleur du texte ______________________________________________________
/* ___ R255 V255 B255 (BLANC) ___________________________________________________________
W_NB4_VALEUR_RVB = (255)+(255*256)+(255*65536) /\* Rouge+Vert*256+Bleu*65536
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL  PLAGE_FIXER_COULEUR_POLICE  W_ALP_PLAGE W_NB4_VALEUR_RVB W_NB4_CODE_RETOUR

/* ___ Mémoriser la position de la première ligne de détail _____________________________
W_NUM_1ERE_LIGNE = W_NUM_NUMERO_LIGNE + 1

Exemple d'écriture des lignes de détails d'une liste :

Traitement
W_NUM_NUMERO_LIGNE = W_NUM_NUMERO_LIGNE + 1

/* ___ Les données utilisées dans cet exemple proviennent _______________________________
/* ___ de différents fichiers traités auparavant ________________________________________

/* ___ 'Client' _________________________________________________________________________
W_ALP_ELEMENT = CL_LIB_CLIENT
W_ALP_PLAGE   = 'A' /// W_NUM_NUMERO_LIGNE /// ': ' // 'A' /// W_NUM_NUMERO_LIGNE
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL  PLAGE_FIXER_VALEURS  W_ALP_PLAGE W_ALP_ELEMENT 1 W_NB4_CODE_RETOUR

/* ___ 'Projet' _________________________________________________________________________
W_ALP_ELEMENT = PJ_DESIGN_PRJ
W_ALP_PLAGE   = 'B' /// W_NUM_NUMERO_LIGNE /// ': ' // 'B' /// W_NUM_NUMERO_LIGNE
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL  PLAGE_FIXER_VALEURS  W_ALP_PLAGE W_ALP_ELEMENT 1 W_NB4_CODE_RETOUR

/* ___ 'Budget initial' _________________________________________________________________
W_NUM_ELEMENT_15_2 = S1_CHG_GLOB_PRJ
W_ALP_PLAGE   = 'C' /// W_NUM_NUMERO_LIGNE /// ': ' // 'C' /// W_NUM_NUMERO_LIGNE
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL  PLAGE_FIXER_VALEURS  W_ALP_PLAGE W_NUM_ELEMENT_15_2 1 W_NB4_CODE_RETOUR

/* ___ 'Début' __________________________________________________________________________
SI PJ_DATE_DEBUT <> \*LOVAL
W_ALP_PLAGE   = 'D' /// W_NUM_NUMERO_LIGNE /// ': ' // 'D' /// W_NUM_NUMERO_LIGNE
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL   PLAGE_FIXER_VALEURS  W_ALP_PLAGE PJ_DATE_DEBUT 1 W_NB4_CODE_RETOUR
FIN
* 'Fin'
SI PJ_DATE_FIN_PRJ <> \*LOVAL
W_ALP_PLAGE   = 'E' /// W_NUM_NUMERO_LIGNE /// ': ' // 'E' /// W_NUM_NUMERO_LIGNE
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL   PLAGE_FIXER_VALEURS  W_ALP_PLAGE PJ_DATE_FIN_PRJ 1 W_NB4_CODE_RETOUR
FIN

* 'Conso'
W_NUM_ELEMENT_15_2 = S1_CHG_CONS_T
W_ALP_PLAGE   = 'F' /// W_NUM_NUMERO_LIGNE /// ': ' // 'F' /// W_NUM_NUMERO_LIGNE
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL   PLAGE_FIXER_VALEURS  W_ALP_PLAGE W_NUM_ELEMENT_15_2 1 W_NB4_CODE_RETOUR

* 'RàF'
W_NUM_ELEMENT_15_2 = S1_CHG_RAF
W_ALP_PLAGE   = 'G' /// W_NUM_NUMERO_LIGNE /// ': ' // 'G' /// W_NUM_NUMERO_LIGNE
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL   PLAGE_FIXER_VALEURS  W_ALP_PLAGE W_NUM_ELEMENT_15_2 1 W_NB4_CODE_RETOUR

* 'Gain/Dérive'
* la formule est composée comme suit   =C2-(F2+G2)
W_ALP_FORMULE = '=C' // W_NUM_NUMERO_LIGNE /// '-(F' // W_NUM_NUMERO_LIGNE /// '+G' // W_NUM_NUMERO_LIGNE /// ')'
W_ALP_PLAGE   = 'H' /// W_NUM_NUMERO_LIGNE /// ': ' // 'H' /// W_NUM_NUMERO_LIGNE
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL   PLAGE_FIXER_FORMULE_LOCAL  W_ALP_PLAGE W_ALP_FORMULE W_NB4_CODE_RETOUR

* 'Commentaires techniques'
W_ALP_ELEMENT = S1_COM_TECHNIQU
W_ALP_PLAGE   = 'I' /// W_NUM_NUMERO_LIGNE /// ': ' // 'I' /// W_NUM_NUMERO_LIGNE
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL   PLAGE_FIXER_VALEURS  W_ALP_PLAGE W_ALP_ELEMENT 1 W_NB4_CODE_RETOUR

Exemple de mise en forme finale de la liste :

Traitement
W_ALP_POLICE = 'Arial'
W_NUM_TAILLE = 8
W_NB4_STYL_LIG = _EXL_STYLE_LIGNE_CONTINU
W_NB4_EP_TRAIT = _EXL_EPAISSEUR_LIGNE_FINE
W_NB4_ALIGNH = _EXL_ALIGNEMENT_HORIZ_CENTRE
W_NB4_ALIGNV = _EXL_ALIGNEMENT_VERT_CENTRE
W_NB4_HAUT = 12
W_BOO_GRAS = *FAUX
W_BOO_ITAL = *FAUX
W_BOO_BARRE = *FAUX
W_NB4_SOULIG = _EXL_SOULIGNEMENT_AUCUN

* De la première ligne de sortie du détail jusqu'à la dernière ligne
W_ALP_PLAGE = 'A' /// W_NUM_1ERE_LIGNE /// ': ' // 'I' /// W_NUM_NUMERO_LIGNE

* Définir la police de caractère
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL PLAGE_FIXER_POLICE W_ALP_PLAGE W_ALP_POLICE W_NUM_TAILLE W_NB4_CODE_RETOUR

* Définir le style des cellules
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL PLAGE_FIXER_STYLES_POLICE W_ALP_PLAGE W_BOO_GRAS W_BOO_ITAL W_BOO_BARRE W_NB4_SOULIG W_NB4_CODE_RETOUR

* Définir la hauteur de la ligne
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL PLAGE_FIXER_HAUTEUR_LIGNE W_ALP_PLAGE W_NB4_HAUT W_NB4_CODE_RETOUR

* Définir le contour des cellules
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL PLAGE_FIXER_CONTOUR W_ALP_PLAGE W_NB4_STYL_LIG W_NB4_EP_TRAIT W_NB4_CODE_RETOUR

* Définir l'alignement des cellules
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL PLAGE_FIXER_ALIGNEMENT_VERT W_ALP_PLAGE W_NB4_ALIGNV W_NB4_CODE_RETOUR

* ---------------------------------------------------------------------------------------------------------------------------

* Définir l'alignement de certaines cellules
W_ALP_PLAGE = 'C' /// W_NUM_1ERE_LIGNE /// ': ' // 'H' /// W_NUM_NUMERO_LIGNE
W_NB4_ALIGNH = _EXL_ALIGNEMENT_HORIZ_CENTRE
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL PLAGE_FIXER_ALIGNEMENT_HORIZ W_ALP_PLAGE W_NB4_ALIGNH W_NB4_CODE_RETOUR

* Ajuster la taille des colonnes au contenu des titres de colonnes
W_ALP_PLAGE = 'A1' /// ': ' // 'I' /// W_NUM_NUMERO_LIGNE
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL PLAGE_AUTO_AJUSTER W_ALP_PLAGE W_NB4_CODE_RETOUR

* Fixer le format des cellules (nombres, dates, ...)
W_ALP_PLAGE = 'D' /// W_NUM_1ERE_LIGNE /// ': ' // 'E' /// W_NUM_NUMERO_LIGNE
W_ALP_FORMAT = 'mmm-aa'
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL PLAGE_FIXER_FORMAT_EDITION_LOCAL W_ALP_PLAGE W_ALP_FORMAT W_NB4_CODE_RETOUR

* Signe en couleur
W_ALP_PLAGE = 'H' /// W_NUM_1ERE_LIGNE /// ': ' // 'H' /// W_NUM_NUMERO_LIGNE
W_ALP_FORMAT = 'Vert+0,00;Rouge-0,00'
APPELER_METHODE FEN_VIS_EDITION.CLI_EXCEL PLAGE_FIXER_FORMAT_EDITION_LOCAL W_ALP_PLAGE W_ALP_FORMAT W_NB4_CODE_RETOUR

2 commentaires

  1. Baudichaud Ghislain dit :

    Bonjour,

    Est-ce qu'il y a une méthode qui permet de 

    • dupliquer des lignes d'un fichier Excel ?
    • insertion des lignes au milieu d'un tableau Excel (pour faire une maquette avec des cellules pour le haut et des cellules pour le bas du fichier, et l'insertion de ligne entre les deux)

    s'il vous plaît ?

    Merci d'avance.

     

  2. Julien Eymery dit :

    Il n'y a pas de méthode permettant de dupliquer directement des lignes, il va falloir lire les valeurs avec PLAGE_OBTENIR_VALEURS puis les retranscrire avec PLAGE_FIXER_VALEURS.

    Il n'y a pas de méthode permettant d'insérer des lignes.