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.fr