RciTools RXLS - Génération de documents Excel et de feuilles de calcul Open Office depuis Oracle

     

RCI Informatique, Janvier 2008

1 - Introduction

- Oracle Application Express, nommé auparavant Oracle HTML DB, permet de créer des applications interactives, en mode HTML, accédant directement aux données Oracle.
On peut qualifier APEX d'interface graphique à la programmation PL/SQL.

- Oracle Application Express permet d'exporter le contenu d'un rapport au format CSV, sous forme de données brutes, sans mise en forme.

- Depuis la version 3.0 d' Application Express, il est également possible d'utiliser en liaison, un produit commercial d'Oracle nommé BI Publisher, afin de générer des documents aux formats Excel et PDF.

- Il est souvent nécessaire de pouvoir créer des documents Excel avec une mise en page bien précise, avec plusieurs feuilles dans un même classeur, en utilisant attributs typographiques et formules de calcul Excel.

De nombreux outils de "reporting" existent "sur le marché", sous forme de produits indépendants, édités par Oracle ou par d'autres éditeurs.
Mais leur utilisation pose un certain nombre de questions:  mode et complexité de déploiement, coût des licences, temps de réponse pour fabriquer un état, nécessité d'utiliser un serveur d'application distinct, langages différents de PL/SQL, etc.

C'est pourquoi RCI Informatique a décidé la création d'un "package PL/SQL" destiné à la production simple et rapide de documents Excel, en utilisant le format défini par Microsoft et nommé Office XML..

Le langage PL/SQL s'inscrit tout à fait dans la lignée des langages orientés algorithmes, sur lesquels RCI Informatique a travaillé depuis sa création en 1982 :   Algol, Simula, Pascal, Modula 2 et Delphi.

C'est cette lignée de langage  qui a permis la réalisation de programmes fiables, durables, toujours lisibles et maintenables même après de nombreuses années.

Nous avons bénéficié de l'expérience acquise avec le développement d'autres interfaces avec Excel, déjà programmée en Oracle PL/SQL entre 1992 et 1995, et fonctionnant en client-serveur.

Domaines d'utilisation  :

- Tableaux de bord
- Préparation de documents comportant des données Oracle et destinés à être complétés par les utilisateurs
- Présentation de plannings et de diagrammes de Gantt
- Création de fiches regroupées sous forme de feuilles de calcul dans un même classeur
- Création automatisée de feuilles complexes et de formules de calcul Excel générées automatiquement



2 - Objectifs fonctionnels du package RciTools - RXLS  de génération de documents Excel et de feuilles de calcul Open Office



Exemple de présentation de données depuis Oracle dans Excel, avec :
Attributs typographiques, formules de calcul Excel, encadrements, formats numériques, couleur, maître-détail, prise en compte de données manquantes

 

- Rapidité et simplicité de programmation, afin de pouvoir, en seulement quelques instructions, obtenir de véritables résultats, en concentrant l'effort de programmation sur les aspects fonctionnel et métier.

- Utilisation en mode interactif ou en mode "batch", afin de pouvoir obtenir en moins d'une seconde des classeurs Excel simples, depuis une interface HTML, et en quelques secondes ou dizaines de secondes, des classeurs complexes et volumineux.

- Obtention de documents XLS / ou feuilles XML  "à la volée" (directement envoyé à l'utilisateur via le navigateur) ou destinés à être stockés pour archivage ou utilisations ultérieures.

- Disposer d'instructions de "bas niveau" (exemple écrire une formule à une position précise dans une typographie indiquée) et d'instructions plus puissantes telles que la génération de tableaux, de cadres, la définition de styles réutilisables, et ceci en quelques instructions seulement.

- Pouvoir écrire dans un ordre quelconque, dans n'importe quelle cellule de n'importe quelle feuille du classeur en cours de génération, avec possibilités d'effacement, de changements de styles ou de formules, tant que le document n'a pas été généré. Pouvoir simultanément créer plusieurs classeurs différents, avec un seul algorithme.

- Pouvoir s'intégrer facilement à Oracle Application Express, avec utilisation directe, dans les blocs PL/SQL avec accès aux "champs" d'Oracle Application Express.

- Convenir aussi bien à des documents Excel simples, qu'à des applications de type "tableaux de bord" complexes.

- Créer des documents lisibles avec Open Office, et donc des applications s'appuyant sur uniquement des outils "gratuits":  Oracle XE, Oracle APEX,  SQL Developper, Open Office et RXLS.

- Offrir un bon niveau de performances en termes de rapidité de génération

- Être facile à déployer, en particulier pour pouvoir être utilisé dans un contexte d'hébergement mutualisé Oracle Application Express, ou pour des diffusions à grande échelle avec Oracle XE.

- Ne nécessiter, du côté serveur, aucune licence particulière liée au tableur, et pouvoir fonctionner sur une quelconque des plateformes Oracle (Windows, Macintosh OS, Linux, etc. )

- Ne nécessiter aucun serveur d'application, ni langage autre que PL/SQL, fonctionner avec tout serveur Oracle 10g  (Windows, Macintosh OS, Linux, etc.), et pouvoir fonctionner avec la version "gratuite" Oracle XE (10g Express Edition)

 


3 - Caractéristiques du package RciTools - RXLS  de génération de documents Excel (XLS)  et calc Open Office (XML)
 

Description de la version 1.04 de janvier 2008

- Utilise le format de documents tableurs Office 2003 XML
- Ouverture des documents testée avec Excel 2003 Windows, Excel 2004 Macintosh, Excel 2007 Windows et Open Office Calc version 2.3
- Création des documents Excel en deux étapes successives: alimentation des feuilles et cellules, puis génération du document
- Affichage des documents dans un "flot" HTTP, et/ou stockage des documents (en tant que BLOB Oracle ou en tant que documents WebDAV) pour un affichage ultérieur
- Définition de styles des cellules : polices de caractères, taille, attributs typographiques du texte (couleur, gras, italique,..), encadrements et fonds de cellules, alignements
- Attribution et utilisation des noms de styles
- Texte vertical, ou oblique
- Conversion des types de données Oracle en type de données Excel, en tenant compte des limitations d'Excel relatives aux dates et aux nombres
- Moteur SQL intégré, permettant de placer à une position déterminée d'une feuille le résultat d'une requête SQL dynamique
- Mode classique d'utilisateur des curseurs en PL/SQL
- Définition de formule de calcul, relatives, absolues ou semi-relatives
- Nommage de cellules et de zones, possibilité de définir des formules utilisant les noms créés
- Définition des hauteurs de lignes et de colonnes, possibilité de masquage de certaines lignes et colonnes
- Définition de commentaires sur les cellules

- Distribué sous forme d'un  package PL/SQL et d'un ensemble d'exemples commentés.

 

4 - Disponibilité et mode de diffusion de ce package

- Ce package est intégré, depuis le quatrième trimestre 2007,  par RCI Informatique SAS à ses propres développements et aux développement effectués auprès de ses clients.

- Si vous souhaitez pour vos propres projets de développement pouvoir utiliser, dès maintenant, le package RciTools RXLS, contactez nous afin que nous puissions étudier ensemble comment le faire, et dans quelles conditions.
 

RCI Informatique SAS
Tél:  02 35 71 24 59    ou    (33) 2 35 71 24 59
Mail:   rci@wanadoo.fr

 

5 - Interface du package RciTools - RXLS

Description de la version 1.04 de janvier 2008

 

create or replace PACKAGE "RXLS" AS

-- 1.04 de janvier 2008

Type TableStr is table of varchar2 (4000) index by binary_integer;
Type TableInt is table of integer index by binary_integer;
Type TableNb is table of number index by binary_integer;

Type XLS_Style is record (
  xFont varchar2(50), -- Police du texte
  xSize number, -- Corps du texte
  xFontColor varchar2(50), -- couleur du texte

  xFontBold boolean, -- modification typographie
  xFontItalic boolean,
  xFontUnderline boolean,

  xBgColor varchar2(50), -- couleur du fond

  xBorderColor varchar2(50), -- nom de couleur
  xBorderPosition varchar2 (100), -- left,top,bottom,right (en combinaison)
  xBorderWidth integer, -- 1, 2 ou 3

  xHTextAlign varchar2(100), -- alignement horizontal du texte
       -- left, center, justify, right, fill

  xVTextAlign varchar2(100), -- alignement vertical du texte
       -- val possibles : top, center, bottom, justify

  xTextRotate integer, -- rotation du texte

  xVerticalText boolean, -- texte vertical

  xFmt integer -- Format de nombre ou de date , parmi les formats prédéfinis
);


Type XLS_Styles is table of XLS_Style index by binary_integer;

Type XLS_Reference is record (
  xname varchar2 (255),
  rc varchar2 (1), -- r ou c , type de nom
  xref varchar2 (255) -- la référence complète
);

Type XLS_References is table of XLS_Reference index by binary_integer;

Type XLS_value is record (
  vType integer,
  vNumber number,
  vInteger integer,
  vDate date,
  vTxt varchar2(4000),
  vFormula varchar2(255),
  vBlob blob
); -- Type XLS_value

Type XLS_cell is record (
  v XLS_Value,
  fmt varchar2(255),
  styl integer,
  remark varchar2(255)
);

Type XLS_cells is table of XLS_cell index by binary_integer;

Type XLS_row is record (
  row_cells XLS_cells,
  height number
);

Type XLS_rows is table of XLS_row index by binary_integer;


type XLS_WorkSheet is record (
  Name varchar2 (31),
  r XLS_Rows,
  ColWidths TableNb
);

type XLS_WorkSheets is table of XLS_WorkSheet index by binary_integer;

type XLS_WorkBook is record (
  Name varchar2 (255),
  Sheets XLS_WorkSheets,
  styles XLS_Styles,
  refs XLS_References,
  b blob
);


TyposList TableStr; -- Liste des noms de typographies disponibles
FColorNames TableStr; -- Liste des noms de couleurs disponibles (en langue française)
EColorNames TableStr; -- Liste des noms de couleurs disponibles (en langue anglaise)

-- Création d'un nouveau classeur Excel
Procedure NewWorkBook (xWbk in out nocopy XLS_WorkBook);

-- Création d'une nouvelle feuille de calcul dans le classeur indiqué
-- la valeur retournée est le n° d'ordre de la feuille
-- Si le nom comporte des espaces, ceux-ci sont remplacés par des _
-- Longueur maxi des noms: 31 caractères
function NewSheet (xWbk in out nocopy XLS_WorkBook, xName in varchar2) return integer;


-- Largeur d'une colonne
procedure SetColWidth (xWbk in out nocopy XLS_WorkBook,xSheet in integer, xCol in integer, xWidth in number);
-- Hauteur d'une ligne
procedure SetRowHeight (xWbk in out nocopy XLS_WorkBook,xSheet in integer, xRow in integer, xHeigt in number);


-- définition d'un nouveau style
function SetStyle ( xWbk in out nocopy XLS_WorkBook, -- le classeur concerné
  xFont in varchar2 default '', -- Police du texte
  xSize in number default 10, -- Corps du texte
  xFontColor in varchar2 default '', -- couleur du texte


  xFontBold in boolean default false, -- modification typographie
  xFontItalic in boolean default false,
  xFontUnderline in boolean default false,

  xBgColor in varchar2 default '', -- couleur du fond

  xBorderColor in varchar2 default '', -- nom de couleur
  xBorderPosition in varchar2 default '', -- left,top,bottom,right
  xBorderWidth in integer default 0, -- 1, 2 ou 3

  xHTextAlign in varchar2 default '', -- alignement horizontal du texte
     -- left, center, justify, right, fill
     -- valeurs possibles:
  xVTextAlign in varchar2 default '', -- alignement vertical du texte
      -- val possibles : top, center, bottom, justify
  xTextRotate in integer default 0, -- rotation du texte

  xVerticalText in boolean default false , -- texte vertical

  xFmt in integer default '' -- Format de nombre ou de date

) return integer; -- renvoie un n° de style

-- Création d'un rectangle avec bordures
procedure CreateBorderRect ( xWbk in out nocopy XLS_WorkBook, -- le classeur concerné
  xSheet in integer, -- la feuille
  xR in integer, xC in integer, -- le coin en haut à gauche du rectangle
  xW in integer, xH in integer, -- le nb de cellules horizontales et verticales du rectangle
  xStyle in integer, -- le style sur lequel est basé celui des cellules du rectangle
    -- hormis les encadrements,
  xTitle in boolean default false, -- encadrement (ou non) d'une ligne de titre
  xBorderColor in varchar2 default null); -- nom de couleur des bordures


-- Remplissage cellule par un texte
procedure SetCellString ( xWbk in out nocopy XLS_WorkBook,
  xSheet in integer,
  xR in integer, xC in integer,
  xString in varchar2,
  xStyle in integer default NULL);

-- Remplissage cellule par un nombre
procedure SetCellNumber (xWbk in out nocopy XLS_WorkBook,
  xSheet in integer,
  xR in integer, xC in integer,
  xNumber in number,
  xStyle in integer default NULL);

-- Remplissage cellule par une date
procedure SetCellDate ( xWbk in out nocopy XLS_WorkBook,
  xSheet in integer,
  xR in integer, xC in integer,
  xDate in date,
  xStyle in integer default NULL);

-- Commentaire sur une cellule
procedure SetCellComment ( xWbk in out nocopy XLS_WorkBook,
  xSheet in integer,
  xR in integer, xC in integer,
  xComment in varchar2);

-- Formule sur une cellule
-- les éventuelles données sont ignorées
procedure SetCellFormula ( xWbk in out nocopy XLS_WorkBook,
  xSheet in integer,
  xR in integer, xC in integer,
  xFormula in varchar2,
  xStyle in integer default NULL);
     -- exemples de formules possibles:
/*
=RC[-1]
=R[-1]C+RC[-1]
=SUM(R[-4]C:R[-2]C)
=IF(RC[-2]>5,"ok",9)
=MID(RC[-1],2,5)
=RC[-3]/R5C1 (exemple de références relative/absolue)
=UPPER(R[-1]C[-1])
*/

-- Définition d'un nom lié à une cellule
procedure DefineCellRef ( xWbk in out nocopy XLS_WorkBook,
  xSheet in integer,
  xR in integer, xC in integer,
  xName in varchar2);

-- Définition d'un nom lié à un groupe rectangulaire de cellules contigües
procedure DefineRectRef ( xWbk in out nocopy XLS_WorkBook,
  xSheet in integer,
  xTop in integer, xLeft in integer,
  xBottom in integer, xRight in integer,
  xName in varchar2);


-- Remplissage d'une zone Excel par le résultat d'une requête SQL
procedure SQLtoXLS (xSQL in varchar2, xSheet in integer,
  xWbk in out nocopy XLS_WorkBook,
  xR in integer default 1, xC in integer default 1,
  xMaxRows in integer default 100,
  xMaxCols in integer default 10,
  xColnames in boolean default true);



-- Génération depuis la structure du classeur Excel, sous forme de blob
procedure GenXLS (xWbk in out nocopy XLS_WorkBook);

-- Affichage sous forme Excel ou Open Office du classeur généré
procedure Show (xWbk in out nocopy XLS_WorkBook, xExcel in boolean default true);

END RXLS;
 

 


6 - Exemples de mise en oeuvre du package RciTools - RXLS

 

6.1 - Exemple le plus simple de création de classeur Excel



Procedure Test_001 AS
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; f2 integer; f3 integer; -- numéros attribués aux feuilles
Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création de 3 feuilles
  f1 := RXLS.NewSheet (Wbk,'Janvier');
  f2 := RXLS.NewSheet (Wbk,'Février');
  f3 := RXLS.NewSheet (Wbk,'Mars');

  -- Ecriture dans la 1ère feuille, en ligne 3 colonne 2
  RXLS.SetCellString (wbk, f1, 3,2, 'Bonjour');

  -- Ecriture dans la 2ème feuille, en ligne 10 colonne 4
  RXLS.SetCellString (wbk, f2, 10,4, 'Bonsoir');

  -- Fixation à la largeur 150 pixels de la colonne 5 de la feuille 1
  RXLS.SetColWidth (wbk, f1, 5, 150);

  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 1
 

 

6.2 - Lancement et placement du résultat de requêtes SQL dans des feuilles Excel différentes







Procedure Test_002 AS
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; f2 integer; f3 integer; -- numéros attribués aux feuilles


Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création de 2 feuilles
  f1 := RXLS.NewSheet (Wbk,'Départements');
  f2 := RXLS.NewSheet (Wbk,'Employés');

  -- Ecriture dans la 1ère feuille, de la table DEPT
  RXLS.SQLtoXLS (xSQL => 'select * from Dept', xSheet => f1,
                 xWbk => wbk, xR => 3, xC => 2,
                 xMaxRows => 100, xMaxCols => 20);

  -- Ecriture dans la 2e feuille, de la table EMP
  RXLS.SQLtoXLS (xSQL => 'select * from Emp', xSheet => f2,
                 xWbk => wbk, xR => 1, xC => 3,
                 xMaxRows => 20, xMaxCols => 10);

  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 2

 

6.3 - Lancement et placement du résultat de 2 requêtes SQL différentes dans une même feuille  Excel


Procedure Test_003 AS
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéro attribué à la feuille

Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une seule feuille
  f1 := RXLS.NewSheet (Wbk,'Départements et Employés');

  -- Ecriture dans la 1ère feuille, de la table DEPT
  RXLS.SQLtoXLS (xSQL => 'select * from dept', xSheet => f1,
                 xWbk => wbk, xR => 3, xC => 1,
                 xMaxRows => 20, xMaxCols => 10);

  -- Ecriture de la table EMP à droite des départements
  RXLS.SQLtoXLS (xSQL => 'select * from emp', xSheet => f1,
                 xWbk => wbk, xR => 3, xC => 8,
                 xMaxRows => 200, xMaxCols => 20);

  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 3

 

6.4 - Ecriture dans différentes typographies


Procedure Test_004 AS
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéro attribué à la feuille
  styl integer; -- style créé
  wTypo varchar2(100);

Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une seule feuille
  f1 := RXLS.NewSheet (Wbk,'Essais de typographies');

  -- Parcours des typographies prédéfinies
  for m in 1..RXLS.TyposList.count loop
    wTypo := RXLS.TyposList (m); -- obtention du nom de la ni-ème typographie

    -- définition d'un nouveau style
    styl := RXLS.SetStyle (xwbk => wbk, xFont => wTypo, xSize => 18, xFontColor => 'bleu');
    -- Ecriture dans ce style
    RXLS.SetCellString (wbk, f1, m, 1, wTypo);
    RXLS.SetCellString (wbk, f1, m, 2, wTypo || ' >>> ABCDEFG abcdefg 12345', styl);
  end loop;

  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 4
 


6.5 - Texte dans différentes tailles


Procedure Test_005 AS -- Différentes Tailles de typographies
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéro attribué à la feuille
  styl integer; -- style créé

Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une seule feuille
  f1 := RXLS.NewSheet (Wbk,'Essais de typographies');

  -- Ecriture avec des typographies de tailles croissantes
  for m in 6..24 loop
    -- définition d'un nouveau style
    styl := RXLS.SetStyle (xwbk => wbk, xFont => 'georgia', xSize => m, xFontColor => 'rouge');
    -- Ecriture dans ce style
    RXLS.SetCellString (wbk, f1, m-5, 2, 'écriture ABCDEFG abcdefg 12345 en corps ' || m, styl);

    -- Spécification de la hauteur de ligne
    RXLS.SetRowHeight (wbk, f1, m-5 , m * 1.5);

  end loop;

  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 5
 


6.6 - Différentes couleurs prédéfinies


Procedure Test_006 AS -- Différentes couleurs
    wbk RXLS.XLS_WorkBook; -- structure de données du classeur
    f1 integer; -- numéro attribué à la feuille
    wColor varchar2 (50); -- nom d'une couleur
    wBG varchar2 (50); -- couleur du fond de la cellule
    styl integer; -- style créé

Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une seule feuille
  f1 := RXLS.NewSheet (Wbk,'Essais de typographies');

  -- Ecriture avec des couleurs différentes
  for m in 1..RXLS.FcolorNames.count loop
    -- obtention de la m-ième couleur pré-définie
    wColor := RXLS.FcolorNames (m);

    -- fond des cellules
    wBG := 'blanc'; if m in (1,5,6,9,10,11,23,26,29,30) then wBG := 'gris clair'; end if;

    -- définition d'un nouveau style
    styl := RXLS.SetStyle (xwbk => wbk, xFont => 'georgia', xSize => 18, xFontColor => wColor, xBGcolor => wBG);

    -- Ecriture dans ce style
    RXLS.SetCellString (wbk, f1, m, 1, m, styl);
    RXLS.SetCellString (wbk, f1, m, 2, wColor, styl);
    RXLS.SetCellString (wbk, f1, m, 3, ' abcefghijklmnopqtsuvwxyz', styl);

  end loop;

  -- Spécification de la largeur de colonne
  RXLS.SetColWidth (wbk, f1, 2 , 180);
  RXLS.SetColWidth (wbk, f1, 3 , 300);

  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 6

 


6.7 - Différentes couleurs de fonds de cellules


Procedure Test_007 AS -- Différentes couleurs de fonds de cellules
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéro attribué à la feuille
  wColor varchar2 (50); -- nom d'une couleur
  wBG varchar2 (50); -- couleur du fond de la cellule
  styles RXLS.TableInt; -- table de styles
  n integer := 0; styl integer; nbColors integer;
  MaxC integer := 50; MaxR integer := 50;

Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une seule feuille
  f1 := RXLS.NewSheet (Wbk,'Cellules colorées');

  -- Definition de styles avec les différentes couleurs
  nbcolors := RXLS.FcolorNames.count;

  for m in 1..nbcolors loop
    -- obtention de la m-ième couleur
    wColor := RXLS.FcolorNames (m);

    -- définition d'un nouveau style avec cette couleur
    styles (m) := RXLS.SetStyle (xwbk => wbk, xBGcolor => wColor);
  end loop;

  nbcolors := styles.count;

  for nr in 1..MaxR loop
    for nc in 1..MaxC loop
      n := n + 1;
      styl := mod (n, nbcolors) + 1;

      -- Ecriture dans le style suivant
      RXLS.SetCellString (wbk, f1, nr, nc, '', styles(styl) );
    end loop;
  end loop;

  -- Spécification de la largeur des colonnes
  for nc in 1..MaxC loop
    RXLS.SetColWidth (wbk, f1, nc, 24);
  end loop;

  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 7
 


6.8 - Encadrements de cellules



Procedure Test_008 AS -- encadrement de cellules
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéro attribué à la feuille
  wColor varchar2 (50); -- nom d'une couleur
  wBG varchar2 (50); -- couleur du fond de la cellule
  styl integer; -- style créé
  nsCoinHG integer; nsCoinHD integer; nsCoinBG integer; nsCoinBD integer; -- coins des encadrements
  nsGauche integer; nsDroit integer; nsHaut integer; nsBas integer; -- côtés des encadrements



Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une seule feuille
  f1 := RXLS.NewSheet (Wbk,'Encadrements');

  nsCoinHG := RXLS.setStyle (xwbk => wbk,xBgColor => 'jaune pale',xBorderColor => 'bleu', xBorderWidth => 3, xBorderPosition => 'top,left');
  nsCoinHD := RXLS.setStyle (xwbk => wbk,xBgColor => 'jaune pale',xBorderColor => 'bleu', xBorderWidth => 3, xBorderPosition => 'top,right');
  nsCoinBG := RXLS.setStyle (xwbk => wbk,xBgColor => 'jaune pale',xBorderColor => 'bleu', xBorderWidth => 3, xBorderPosition => 'bottom,left');
  nsCoinBD := RXLS.setStyle (xwbk => wbk,xBgColor => 'jaune pale',xBorderColor => 'bleu', xBorderWidth => 3, xBorderPosition => 'bottom,right');

  nsGauche := RXLS.setStyle (xwbk => wbk,xBgColor => 'jaune pale',xBorderColor => 'bleu', xBorderWidth => 3, xBorderPosition => 'left');
  nsDroit := RXLS.setStyle (xwbk => wbk,xBgColor => 'jaune pale',xBorderColor => 'bleu', xBorderWidth => 3, xBorderPosition => 'right');
  nsHaut := RXLS.setStyle (xwbk => wbk,xBgColor => 'jaune pale',xBorderColor => 'bleu', xBorderWidth => 3, xBorderPosition => 'top');
  nsBas := RXLS.setStyle (xwbk => wbk,xBgColor => 'jaune pale',xBorderColor => 'bleu', xBorderWidth => 3, xBorderPosition => 'bottom');


  -- Définition des encadrements (et styles) de cellules, avec espacements
  RXLS.SetCellString (wbk, f1, 2,3, Null , nsCoinHG);
  RXLS.SetCellString (wbk, f1, 2,5, Null , nsHaut);
  RXLS.SetCellString (wbk, f1, 2,7, Null , nsCoinHD);

  RXLS.SetCellString (wbk, f1, 4,3, Null , nsGauche);
  RXLS.SetCellString (wbk, f1, 4,7, Null , nsDroit);

  RXLS.SetCellString (wbk, f1, 6,3, Null , nsCoinBG);
  RXLS.SetCellString (wbk, f1, 6,5, Null , nsBas);
  RXLS.SetCellString (wbk, f1, 6,7, Null , nsCoinBD);


  -- Ecriture de données dans les cellules, en conservant les styles et encadrements déjà définis
  RXLS.SetCellString (wbk, f1, 2,3, 'a');
  RXLS.SetCellString (wbk, f1, 2,5, 'b');
  RXLS.SetCellString (wbk, f1, 2,7, 'c');


  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 8

 

6.9 - Rectangles d'encadrements de cellules


Procedure Test_009 AS -- Rectangles d'encadrements
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéro attribué à la feuille
  myStyle integer;

Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une seule feuille
  f1 := RXLS.NewSheet (Wbk,'Encadrements');

  myStyle := RXLS.SetStyle (xwbk => wbk, xFont => 'georgia', xSize => 18, xFontColor => 'rouge', xBGcolor => 'jaune pale');

  RXLS. CreateBorderRect (xWbk => wbk, xSheet => 1,
                          xR => 2, xC => 3,
                          xW => 3, xH => 4,
                          xStyle => myStyle, xBorderColor => 'bleu');


  myStyle := RXLS.SetStyle (xwbk => wbk, xFont => 'arial', xSize => 12, xFontColor => 'vert sapin', xBGcolor => 'bleu pale delave');

  RXLS. CreateBorderRect (xWbk => wbk, xSheet => 1,
                          xR => 2, xC => 9,
                          xW => 5, xH => 10,
                          xStyle => myStyle, xBorderColor => 'rouge');


  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 9

 

6.10 - Texte à angle droit




Procedure Test_010 AS -- Texte à angle droit
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéro attribué à la feuille
  myStyle integer;
  t rXLS.TableStr;

Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une seule feuille
  f1 := RXLS.NewSheet (Wbk,'Texte à angle droit');

  -- Cellules centrées hozizontalement, vers le bas verticalement, avec un angle de 90°
  myStyle := RXLS.SetStyle (xwbk => wbk, xFont => 'georgia', xSize => 14, xFontColor => 'rouge',
                            xTextRotate => 90, xHTextAlign => 'center', xVTextAlign => 'bottom');

  -- Préparation des données
  t(1) := 'Lundi'; t(2) := 'Mardi'; t(3) := 'Mercredi'; t(4) := 'Jeudi'; t(5) := 'Vendredi'; t(6) := 'Samedi'; t(7) := 'Dimanche';



  for m in 1..7 loop
    RXLS.SetCellString (wbk, f1, 2, 1+m, t(m) , myStyle);
  end loop;

  RXLS.SetRowHeight (wbk, f1, 2, 80); -- hauteur de la ligne 2


  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 10
 

6.11 - Dessin de diagrammes de Gantt, et commentaires sur cellules



Procedure Test_011 AS -- Dessin de diagrammes de Gantt
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéro attribué à la feuille
  myStyle integer;
  t rXLS.TableStr;
  bleu integer; rouge integer; vert integer;

  Procedure Barre (xR in integer, xC in integer, xW in integer, xStyle in integer) is
    begin
      for m in xC..xC+xW loop
        RXLS.SetCellString (wbk, f1, xR, m, '' , xStyle);
      end loop;
    -- Hauteur ligne suivante
    RXLS.SetRowHeight (wbk, f1, xR+1, 10);
    -- Commentaire
    RXLS.SetCellComment ( wbk, f1, xR, xC, 'Diagramme de la ligne ' || xR || ' en style ' || xStyle);
  end; -- Barre

Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une seule feuille
  f1 := RXLS.NewSheet (Wbk,'Texte à angle droit');

  --- déf des styles
  bleu  := RXLS.SetStyle (xwbk => wbk, xBgColor => 'bleu clair');
  rouge := RXLS.SetStyle (xwbk => wbk, xBgColor => 'rouge');
  vert  := RXLS.SetStyle (xwbk => wbk, xBgColor => 'vert sapin');

  -- largeurs des colonnes et hauteur des lignes
  for m in 3..100 loop
    RXLS.SetColWidth (wbk, f1, m, 5);
  end loop;

  Barre ( 3, 3, 20, bleu);
  Barre ( 5, 20, 50, rouge);
  Barre ( 7, 15, 8, bleu);
  Barre ( 9, 30,20, vert);
  Barre ( 11, 6 ,12, vert);

  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 11

 

6.12 - Nombres réels, très grands et très petits nombres



Procedure Test_012 AS
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéros attribués aux feuilles
  x number; y number; z number;

Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une feuille
  f1 := RXLS.NewSheet (Wbk,'Nombres');

  -- Ecriture dans la 1ère feuille, en ligne 1 colonne 1
  x := 22/7;
  RXLS.SetCellNumber (Wbk,f1, 1,1, x);

  for k in 1..100 loop
    y := x * power (10,k);
    z := x / power (10,k);
    RXLS.SetCellNumber (Wbk,f1, k+2,2, y);
    RXLS.SetCellNumber (Wbk,f1, k+2,3, z);
  end loop;

  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 12
 

 

6.13 - Formats de nombres, définition de formules Excel


Procedure Test_013 AS
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéros attribués aux feuilles
  nb integer := 0;
  style1 integer; style2 integer; style3 integer;

  Cursor C is select ename, sal from Emp;

Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une feuille
  f1 := RXLS.NewSheet (Wbk,'Formats de nombres');


  style1 := RXLS.SetStyle (xWbk => wbk, xBgColor => 'jaune pale',
                           xFontColor => 'bleu', xFont => 'garamond', xSize => 10,
                           xBorderPosition => 'top, left, bottom, right',
                           xBorderColor => 'rouge', xBorderWidth => 1, xFmt =>11);

  style2 := RXLS.SetStyle (xWbk => wbk, xBgColor => 'jaune pale',
                           xFontColor => 'bleu', xFont => 'garamond', xSize => 10,
                           xBorderPosition => 'top, left, bottom, right',
                           xBorderColor => 'rouge', xBorderWidth => 1,
                           xFmt => 13);


  nb := 2;
  for m in C loop
    nb := nb + 1;
    RXLS.SetRowHeight (wbk, f1, nb, 16);
    RXLS.SetCellString (wbk, f1, nb,3, m.ename , style1);
    RXLS.SetCellNumber (wbk, f1, nb,4, m.sal , style1);
    RXLS.SetCellFormula (wbk, f1, nb,5, '=RC[-1]' , style2);
  end loop;

  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 13
 

 

6.14 - Formules Excel, calculs de pourcentage, nommage de cellule


Procedure Test_014 AS
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéros attribués aux feuilles
  nb integer := 0;
  style1 integer; style2 integer;

  Cursor C is select ename, sal from Emp;


Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une feuille
  f1 := RXLS.NewSheet (Wbk,'Pourcentages');


  style1 := RXLS.SetStyle (xWbk => wbk, xBgColor => 'jaune pale',
                           xFontColor => 'bleu', xFont => 'garamond', xSize => 10,
                           xBorderPosition => 'top, left, bottom, right',
                           xBorderColor => 'rouge', xBorderWidth => 1, xFmt =>11); -- nb

  style2 := RXLS.SetStyle (xWbk => wbk, xBgColor => 'jaune pale',
                           xFontColor => 'bleu', xFont => 'garamond', xSize => 10,
                           xBorderPosition => 'top, left, bottom, right',
                           xBorderColor => 'rouge', xBorderWidth => 1, xFmt =>41); -- %

  nb := 2;
  for m in C loop
      nb := nb + 1;
      RXLS.SetRowHeight (wbk, f1, nb, 16);
      RXLS.SetCellString (wbk, f1, nb,3, m.ename , style1);
      RXLS.SetCellNumber (wbk, f1, nb,4, m.sal , style1);
      RXLS.SetCellFormula (wbk, f1, nb,5, '=RC[-1]/tot_emp' , style2);
  end loop;

  -- Calcul du total
  RXLS.SetCellFormula (wbk, f1, nb+2,4, '=sum(R3C4:R[-2]C4)' , style1);

  -- Définition du nom de la cellule du total
  RXLS.DefineCellRef (wbk, f1, nb+2, 4, 'tot_emp');



  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 14

 

6.15 - Etat à deux niveaux, avec formules de calcul Excel



Procedure Test_015 AS
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéros attribués aux feuilles
  nb integer := 0; e integer; wFormule varchar2 (100);
  style1 integer; style2 integer; ExisteEmploye boolean;

  Cursor C1 is select Dname, Deptno from Dept order by 1;

  Cursor C2 (xDep in integer) is select ename, sal from Emp
    where deptno = xDep
    order by 1;

  SalTotal number;

Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une feuille
  f1 := RXLS.NewSheet (Wbk,'Etat à deux niveaux');

  RXLS.SetColWidth (wbk, f1, 2, 100); -- Colonne plus large
  RXLS.SetColWidth (wbk, f1, 3, 15); -- Colonne étroite
  RXLS.SetColWidth (wbk, f1, 6, 15); -- Colonne étroite

  -- Définition des styles
  style1 := RXLS.SetStyle (xWbk => wbk, xBgColor => 'jaune pale',
                           xFontColor => 'bleu', xFont => 'garamond', xSize => 10,
                           xBorderPosition => 'top, left, bottom, right',
                           xBorderColor => 'rouge', xBorderWidth => 1, xFmt =>11); -- nb

  style2 := RXLS.SetStyle (xWbk => wbk, xFont => 'garamond', xSize => 12,
                           xFontItalic => true, xFontColor => 'rouge'); -- nb

  -- Titres du tableau
  RXLS.SetCellString (wbk, f1, 1,2, 'Départements' , style2);
  RXLS.SetCellString (wbk, f1, 1,7, 'Totaux/département' , style2);

  nb := 2;

  for m in C1 loop
    nb := nb + 1;
    RXLS.SetCellString (wbk, f1, nb,2, m.Dname , style1);
    ExisteEmploye := false;
    e := nb; -- ligne du 1er employé, s'il existe

    -- Boucle des employés de chaque département
    for p in C2 (m.DeptNo) loop
      RXLS.SetCellString (wbk, f1, nb,4, p.ename , style1);
      RXLS.SetCellNumber (wbk, f1, nb,5, p.sal , style1);
      nb := nb + 1; ExisteEmploye := true;
    end loop; -- employés

    if not ExisteEmploye then
      RXLS.SetCellString (wbk, f1, nb,4, 'aucun employé' , style2);
      nb := nb + 1;
    else -- Formule Excel du total des salaires pour le département
      wFormule := 'sum(R' || e || 'C[-2]:RC[-2])';
      RXLS.SetCellFormula (wbk, f1, nb-1, 7, wFormule, style1);
    end if;
  end loop; -- Départements

  -- Total général
  nb := nb + 2;
  RXLS.SetCellString (wbk, f1, nb,2, 'Total général' , style1);

  select nvl(sum(sal),0) into SalTotal from Emp;
  RXLS.SetCellNumber (wbk, f1, nb,5, salTotal , style1);

  -- hauteurs des lignes
  for k in 1..nb loop RXLS.SetRowHeight (wbk, f1, k, 15); end loop;

  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 15

 

6.16 - Présentation récursive de données



Procedure Test_016 AS
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéros attribués aux feuilles
  nb integer := 0; Hauteur integer;
  style1 integer; ExisteEmploye boolean;


  Function getName (x in integer) return varchar2 is
    Cursor C is select Ename from Emp where empNo = x;
  begin
    for m in C loop return m.Ename; end loop;
    return '???';
  end;

  Function getSal (x in integer) return number is
    Cursor C is select sal from Emp where empNo = x;
  begin
    for m in C loop return m.sal; end loop;
    return 0;
  end;


  Procedure ListeCollaborateurs (x in integer, niveau in integer) is
    Existe boolean := false;
    Cursor Collaborateurs is select Ename, EmpNo from Emp where mgr = x;
  begin
    nb := nb + 2;
    RXLS.SetCellString (wbk, f1, nb ,niveau, getName (x) , style1);
    RXLS.SetCellNumber (wbk, f1, nb ,15, getSal (x) , style1);
    RXLS.SetColWidth (wbk, f1, niveau, 50);

    for m in Collaborateurs loop
      Existe := true;
      ListeCollaborateurs (m.empNo, niveau + 2); -- Appel récursif de la procédure depuis elle-même
    end loop;

  end; -- ListeCollaborateurs

Begin -- test_016

  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une feuille
  f1 := RXLS.NewSheet (Wbk,'Présentation récursive');

  for m in 1..14 loop
    RXLS.SetColWidth (wbk, f1, m, 10);
  end loop;

  -- Définition des styles
  style1 := RXLS.SetStyle (xWbk => wbk, xBgColor => 'jaune pale',
                           xFontColor => 'bleu', xFont => 'garamond', xSize => 10,
                           xBorderPosition => 'top, left, bottom, right',
                           xBorderColor => 'rouge', xBorderWidth => 1, xFmt =>11); -- nb

  ListeCollaborateurs (7839, 3); -- King !

  -- hauteurs des lignes
  for k in 1..nb loop
    if mod (k,2) = 0 then Hauteur := 15; else Hauteur := 5; end if;
    RXLS.SetRowHeight (wbk, f1, k, Hauteur);
  end loop;

  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  RXLS.Show (wbk);
end; -- exemple 16
 

 

6.17 - Dates, formules Excel itératives



Procedure Test_017 AS
  wbk RXLS.XLS_WorkBook; -- structure de données du classeur
  f1 integer; -- numéros attribués aux feuilles
  nb integer := 0;
  style1 integer; style2 integer;
  d date := to_date('31/12/2007', 'DD/MM/YYYY');
  mois integer; jour integer; styleDate integer;


Begin
  RXLS.NewWorkBook (wbk); -- Initialisation d'un nouveau classeur

  -- Création d'une feuille
  f1 := RXLS.NewSheet (Wbk,'Calendrier');


  styleDate := RXLS.SetStyle (xWbk => wbk, xSize=>7, xFontColor => 'rouge',
                              xFont=>'arial', xFmt =>24); -- date


  for m in 1..366 loop
    d := d + 1;
    mois := to_number (extract (month from d));
    jour := to_number (extract (day from d));
    if jour = 1 then
      RXLS.SetCellDate (wbk, f1, jour, mois, d);
    else
      RXLS.SetCellFormula (wbk, f1, jour, mois, '=R[-1]C+1', styleDate );
    end if;
  end loop;

  for m in 1..31 loop RXLS.SetRowHeight (wbk, f1, m, 10); end loop;


  -- Génération du document Excel, (au format XML 2003)
  RXLS.GenXLS (wbk);

  -- Affichage par le navigateur du document Excel
  XLS.Show (wbk);
end; -- exemple 17
 

 

 

 


 





Tous droits réservés, RCI Informatique SAS, 2007-2008

rci@wanadoo.fr

www.rci-informatique.net