PL/SQL (oracle) et Excel

**moulery** · 02/03/2007, 09h15

Bonjour

J'ai cree un fichier tabulation sous pl/sql au format excel .

j'ai une variable client varchar2(30)

certains client sont au format numerique exemple 1234
et A1234 et alphanumerique.

Le probleme quand j'ouvre exel le numerique est calé a droite
et l'alphanumerique à gauche .

j'aimerez que tous soit calé à gauche ( le numerique et l'alphanumerique)
sans passer par excel il y a t'il une astuce via pl/sql .

Merci

**orafrance** · 02/03/2007, 09h37

c'est pas plus simple de changer le format de la cellule pour du texte ? Sinon, essaye en ajoutant un espace à la fin

**taska** · 02/03/2007, 10h14

FYI.

Lors d'une mission récente, il m'a été demandé de créer une fonctionalité de création de fichier Excel qui devait pouvoir être générique dans les environnements monoposte ( Forms6i C/S - Oracle 8I ) et mode Web ( Forms9i - Oracle 10gR2). De plus, cette fonctionalité devait pouvoir être réutilisables par d'autres applicatifs. J'en suis arrivé à ceci.

Le fichier Excel est créé sur le noyau en PL/SQL et ensuite rappatrié sur le poste client à partir des librairies D2KWUTIL ou WEBUTIL.

La méthodologie est la suivante :

1. Chargement sur le noyau de la librairie java JXL.
http://www.andykhan.com/jexcelapi/

2. Création d'une classe permettant de 'WRAPPER' la librairie JXL au language PL/SQL et chargement de celle-ci sur le noyau.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
package com.td;
 
import java.io.BufferedWriter;
import java.io.File;
 
import java.io.FileWriter;
import java.io.IOException;
import java.util.Enumeration;
import java.util.Locale;
import java.util.Properties;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Alignment;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableWorkbook;
import jxl.write.WritableSheet;
import jxl.write.Label;
import jxl.write.Number;
 
 
/**
     * WRAPPER PL/SQL librairie java JEXCEL
     * 
     * @author Thierry Delongeas - 2007
     * @version 1.0
     *
     */
 
public class Ediag
{  static WritableWorkbook wb ;
   static WritableSheet sheet ;
   static Label labeldata ;
   static Number numdata ;
 
 
 
 
  public Ediag() { }
/**
   * Test d'écriture de fichier
   * @param filename chemin complet du fichier à créer
   * @return (String) Exception
   */
public static String writeFileTest ( String filename){ 
try {
        BufferedWriter out = new BufferedWriter(new FileWriter(filename));
        out.write("Test OK");
        out.close();
    } catch (IOException e) { return "writeFileTest :" + filename + " > "+ e.toString();}
    return null;
}
 
/**
   * Lecture de la version Java de la Machine virtuelle
   * 
   * @return java.version
   */
public static String getJavaVersion ( ){
  return (System.getProperty("java.version"));
} 
 
 
 /**
   * Création d'un fichier EXCEL
   * @param filename chemin complet du fichier à créer
   * @return boolean
   */
 
 public static  String openExcel (String filename){
  try { wb =Workbook.createWorkbook(new File(filename));
    }
  catch (Exception e){ log (e.toString()); return (e.toString());}
  return null;
 }  
 
 /**
   * Création d'une feuille 
   * @param name nom de la feuille à créer
   * @param index index de la feuille à créer
   * 
   * @return boolean
   */
 public static  String createSheet (String name, int index){
   try {sheet = wb.createSheet(name, index);}
   catch (Exception e) { return ("createSheet String,int :"+e.toString());}
   return null;
 }
 
 /**
   * Activation d'une feuille 
   * @param name nom de la feuille à utiliser
   */
 public static String setActiveSheet (String name){
    try { sheet = wb.getSheet(name);}
    catch (Exception e) { return ("setActiveSheet String :"+e.toString());}
    return null;
 }
 /**
   * Activation d'une feuille 
   * @param index index de la feuille à utiliser
   */
 
 public static String  setActiveSheet (int index){
 
   try {sheet = wb.getSheet(index);}
   catch (Exception e) { return ("setActiveSheet int :"+e.toString());}
   return null;
 
 }
 
 /**
   * Ecriture d'une cellule 
   * @param col colonne de la feuille sélectionnée
   * @param row ligne de la feuille sélectionnée
   * @param value valeur à écrire
   * 
   */
 
 public static String  writeCell (int col, int row,String value){
 
 
 // Essaie de caster la chaine de caractères en type de donneés double 
 try {  double d = Double.parseDouble(value);
        numdata = new Number( col,row,d);
        sheet.addCell(numdata);
        return null;
      } catch (Exception e) {
           }
 
 // La valeur n'a put être castée , c'est donc un label
 try { labeldata = new Label( col,row,value);
       sheet.addCell(labeldata);
  //     wb.write();
        return null;
      } catch (Exception e) { return e.toString();}
 }
 
 public static String  writeCell (int col, int row,int value){
 try { numdata = new Number( col,row,value);
       sheet.addCell(numdata);
       return null;
     } catch (Exception e) {return e.toString();}
 }
 
 
 public static String  writeCellN (int col, int row,double value){
 try { numdata = new Number ( col,row,value);
       sheet.addCell(numdata);
       return null;
      } catch (Exception e) { return e.toString();}
 }
 
 
 
/**
 *  Merge des cellules 
 * 
 * */
 public static String  mergeCells (int col1, int row1, int col2, int row2) {
   try {   sheet.mergeCells(col1,row1,col2,row2);}
   catch (Exception e) { return ("mergeCells :"+e.toString());}
   return null;
 }
 
 
 /**
   * Fermeture fichier EXCEL
   * @return String
   */
 
 public static  String  closeExcel (){
    try {wb.write();
         wb.close();} 
    catch (Exception e) {return (e.toString());}
    return null;
} 
 
 
 /**
   * Ecriture sur la console
   * @param temp message ) écrire
   */
 private static  void log(String temp){
  System.out.println (temp);
 }
 
  public static void main(String[] args)
  { 
 
 
  }
}

3. Ecriture d'un package PL/SQL. Ce package cré le fichier Excel , à adapter à votre besoin.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
create or replace package ediag.pkg_excel authid current_user is
 
  -- Ecriture du fichier EXCEL
  procedure write_Excel(path     in varchar2,
                        filename in varchar2,
                        groupe   in varchar2?
				date_deb date,
				date_fin date);
 
  -- Mise à jour des tables de références T_PARAMS et ARBRE
  procedure maj_tables_references;
 
  -- Test d'écriture de fichier
  procedure write_File(filename in varchar2);
 
  -- Retourne la valeur de la JVM de la base
  function java_version return varchar2;
 
end;
/
create or replace package body ediag.PKG_EXCEL is
 
  procedure setValeur(val varchar2) as
    language java name 'com.td.test.setValeur(java.lang.String) ';
  function getValeur return number as
    language java name 'com.td.test.getValeur() return int';
 
  function writeFileTest(filename varchar2) return varchar2 as
    language java name 'com.td.Ediag.writeFileTest(java.lang.String) return java.lang.String';
  function getJavaVersion return varchar2 as
    language java name 'com.td.Ediag.getJavaVersion() return java.lang.String';
 
  function openExcel(filename varchar2) return varchar2 as
    language java name 'com.td.Ediag.openExcel(java.lang.String) return java.lang.String';
  function writeExcel return varchar2 as
    language java name 'com.td.Ediag.closeExcel()  return String';
  function closeExcel return varchar2 as
    language java name 'com.td.Ediag.closeExcel()  return String';
 
  function createSheet(name varchar2, idx number) return varchar2 as
    language java name 'com.td.Ediag.createSheet (java.lang.String ,int) return java.lang.String';
  function setActiveSheet(name varchar2) return varchar2 as
    language java name 'com.td.Ediag.setActiveSheet (java.lang.String) return java.lang.String ';
  function setActiveSheet(idx number) return varchar2 as
    language java name 'com.td.Ediag.setActiveSheet (int) return java.lang.String';
 
  function writeCell(icol number, irow number, svalue varchar2)
    return varchar2 as
    language java name 'com.td.Ediag.writeCell (int,int,java.lang.String) return java.lang.String';
  function writeCell(icol number, irow number, svalue number) return varchar2 as
    language java name 'com.td.Ediag.writeCell (int,int,int) return java.lang.String';
 
  procedure mergeCells(col1 number, row1 number, col2 number, row2 number) as
    language java name 'com.td.Ediag.mergeCells (int,int,int,int)';
 
  procedure write_File(filename in varchar2) is
  begin
    dbms_output.put_line(writeFileTest(filename));
  end;
 
  function java_version return varchar2 is
  begin
    return(getJavaVersion);
  end;
 
  procedure maj_tables_references is
    site    varchar2(50) := null;
    tranche number(2) := null;
  begin
 
    -- Suppression des données des tables à mettre à jour
    Execute immediate 'TRUNCATE TABLE T_SITES_TRANCHES';
    Execute immediate 'TRUNCATE TABLE T_PARAMS';
 
    -- Mise à jour de la table tranche_site
    for rec in (select IDX_NODE,
                       IDX_PARENT,
                       LABEL,
                       IDTYPEELEMENT,
                       IDX_ELEMENT
                  from arbre
                 START WITH idx_parent is null
                CONNECT BY PRIOR idx_node = idx_parent) loop
 
      /* NOM DU SITE*/
      if rec.IDX_PARENT = 0 and rec.IDTYPEELEMENT = 'EM' then
        site := upper(rec.label);
      end if;
 
      /* Numéro de la tranche */
      if rec.IDTYPEELEMENT = 'EM' and
         instr(upper(rec.label), 'TRANCHE') >= 1 and
         to_number(substr(rec.label, length(rec.label))) >= 0 then
        tranche := to_number(substr(rec.label, length(rec.label)));
      end if;
 
      if rec.IDTYPEELEMENT = 'EQ' then
        insert into T_SITES_TRANCHES
          (idx_node, site, tranche)
        values
          (rec.idx_node, site, tranche);
      end if;
    end loop;
 
    -- Mise à jour de la table T_PARAMS.
 
    for rec in (select a.idx_parametre, '1' col
                  from arg_parametre a,
                       arg_parametre b,
                       arg_parametre c,
                       arg_parametre d
                 where a.id_argument = 'CODE_PARAMETRE'
                   and a.id_valeur = 'CP_VITVIB'
                   AND b.idx_parametre = a.idx_parametre
                   and b.id_argument = 'UNITE'
                   and b.id_valeur = 'UNIT_MM/S'
                   and c.idx_parametre = a.idx_parametre
                   AND c.id_argument = 'PASSE_HAUT'
                   and c.id_valeur = 'PH_10'
                   and d.idx_parametre = a.idx_parametre
                   and d.id_argument = 'PASSE_BAS'
                   and d.id_valeur = 'PB_1000'
                union
                select a.idx_parametre, '2' col
                  from arg_parametre a,
                       arg_parametre b,
                       arg_parametre c,
                       arg_parametre d,
                       arg_parametre e
                 where a.id_argument = 'CODE_PARAMETRE'
                   and a.id_valeur = 'CP_DPLCABS'
                   AND b.idx_parametre = a.idx_parametre
                   and b.id_argument = 'UNITE'
                   and b.id_valeur = 'UNIT_MICROM'
                   AND e.idx_parametre = a.idx_parametre
                   and e.id_argument = 'DETECTION_NIV'
                   and e.id_valeur = 'DN_CCEQ'
                   and c.idx_parametre = a.idx_parametre
                   AND c.id_argument = 'PASSE_HAUT'
                   and c.id_valeur = 'PH_10'
                   and d.idx_parametre = a.idx_parametre
                   and d.id_argument = 'PASSE_BAS'
                   and d.id_valeur = 'PB_1000') loop
 
      insert into t_params
        (idx_parametre, col)
      values
        (rec.idx_parametre, rec.col);
 
    end loop;
 
    Commit;
 
  END;
 
  procedure write_excel(path     in varchar2,
                        filename in varchar2,
                        groupe   in varchar2
				date_deb date,
				date_fin date) is
 
    type dat_control is table of varchar2(50) index by binary_integer;
    dc dat_control;
 
    idc number := 1;
 
    ret  number;
    ret1 varchar2(512);
    irow number(6) := 0;
    icol number(6) := 1;
 
    ifirstRow number(4);
 
    alv number(4);
    arv number(4);
    ald number(4);
    ard number(4);
 
    idxparam number(1) := 0;
 
    old_equipement number(6) := 0;
 
    datex boolean := false;
 
    strSql varchar2(512);
 
  begin
 
 
 
    dbms_output.put_line('START :' || sysdate);
    dbms_output.put_line(openExcel(path || '\' || filename));
    dbms_output.put_line(createSheet(groupe, 1));
    dbms_output.put_line(setActiveSheet(groupe));
 
 
    for rec in (Select distinct site,
                                to_number(tranche) tranche,
                                ar.idx_node,
                                label,
                                idx_element,
                                eq.designation,
                                pm.idx_point,
                                pm.nom,
                                fct_alarme(pm.idx_point) Alarmes --,
                  from arbre            ar,
                       equipement       eq,
                       point_mesure     pm,
                       parametre        pa,
                       T_SITES_TRANCHES ts
                 where label like ('%' || groupe || '%')
                   and idtypeelement = 'EQ'
                   and eq.IDX_EQUIPEMENT = ar.IDX_ELEMENT
                   and PM.IDX_EQUIPEMENT = eq.IDX_EQUIPEMENT
                   and PA.idx_point = pm.idx_point
                   and exists (Select rowid
                          from t_params
                         where IDX_PARAMETRE = pa.IDX_PARAMETRE)
                   and ts.idx_node = ar.idx_node) loop
 
      -- Parse des valeurs de l'alarme
      alv := to_number(substr(rec.alarmes, 1, instr(rec.alarmes, '-') - 1));
      arv := to_number(substr(rec.alarmes,
                              instr(rec.alarmes, '-', 1, 1) + 1,
                              instr(rec.alarmes, '-', 1, 2) -
                              instr(rec.alarmes, '-', 1, 1) - 1));
      ald := to_number(substr(rec.alarmes,
                              instr(rec.alarmes, '-', 1, 2) + 1,
                              instr(rec.alarmes, '-', 1, 3) -
                              instr(rec.alarmes, '-', 1, 2) - 1));
      ard := to_number(substr(rec.alarmes,
                              instr(rec.alarmes, '-', 1, 3) + 1,
                              instr(rec.alarmes, '-', 1, 4) -
                              instr(rec.alarmes, '-', 1, 3) - 1));
 
      -- Recherche des dates de contrôle de l'équipement courrant
      -- s'il s'agit d'un nouvel equipement
      if old_equipement <> rec.idx_element then
        dc.delete;
        irow      := irow + 3;
        ifirstRow := irow;
        icol      := 10;
        idc       := 0;
        ret1      := writeCell(icol - 1, irow, 'Date EP');
        ret1      := writeCell(icol, irow - 1, 'Vitesse efficace');
        for recDH in (select distinct trunc(DH_CONTROLE) DH_CONTROLE
                        from controle_equipement
                       where idx_equipement = rec.idx_element
			     and DH_CONTROLE between date_deb and date_fin
                       order by trunc(DH_CONTROLE)) loop
          dc(idc) := to_char(recDH.DH_CONTROLE, 'dd/mm/rrrr');
          ret1 := writeCell(icol, irow, dc(idc));
          dbms_output.put_line(ret1);
 
          idc  := idc + 1;
          icol := icol + 1;
        end loop;
        mergeCells(10, irow - 1, icol - 1, irow - 1);
        ret1 := writeCell(icol, irow - 1, 'Déplacement crête à crête');
        for i in 0 .. dc.count - 1 loop
          ret1 := writeCell(icol, irow, dc(i));
          --            dbms_output.put_line (ret1);
 
          icol := icol + 1;
        end loop;
        mergeCells(10 + dc.count,
                   irow - 1,
                   10 + dc.count + dc.count - 1,
                   irow - 1);
        old_equipement := rec.idx_element;
      END IF;
 
      irow := irow + 1;
      ret1 := writeCell(1, irow, rec.site);
      ret1 := writeCell(2, irow, rec.tranche);
      ret1 := writeCell(3, irow, rec.idx_element);
      ret1 := writeCell(4, irow, rec.label);
      ret1 := writeCell(5, irow, alv);
      ret1 := writeCell(6, irow, arv);
      ret1 := writeCell(7, irow, ald);
      ret1 := writeCell(8, irow, ard);
      ret1 := writeCell(9, irow, rec.nom);
 
      -- Récupération de l'IDX_PARAMETRE
      for rec1 in (select tp.idx_parametre, tp.col
                     from parametre pa, t_params tp
                    where pa.idx_point = rec.idx_point
                      and tp.idx_parametre = pa.idx_parametre
                    order by col) loop
 
        -- Récupération des données dates de contrôle
        icol     := 10;
        idxparam := idxparam + 1;
        for rec2 in (select trunc(DH_CONTROLE) DH_CONTROLE, Valeur
                     from ht_parametre
                     where idx_parametre = rec1.idx_parametre
			   and DH_CONTROLE between date_deb and date_fin ) loop
 
          -- Recherche l'index de la colone de DH_CONTROLE
          for i in 0 .. dc.count - 1 loop
 
            if dc(i) = to_char(rec2.dh_controle, 'dd/mm/rrrr') then
              if idxparam = 1 then
                ret1 := writeCell(icol + i, irow, to_char(rec2.valeur));
              else
                ret1 := writeCell(icol + i + dc.count,
                                  irow,
                                  to_char(rec2.valeur));
              end if;
 
            end if;
          end loop;
 
        end loop;
      end loop;
      idxparam := 0;
 
    end loop;
 
    dbms_output.put_line('CE ' || closeExcel());
    dbms_output.put_line('END :' || sysdate);
 
  end;
 
end;
/

j'aimerez que tous soit calé à gauche ( le numerique et l'alphanumerique)

Dans ce cas, il suffit de supprimer ces lignes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
// Essaie de caster la chaine de caractères en type de donneés double 
 try {  double d = Double.parseDouble(value);
        numdata = new Number( col,row,d);
        sheet.addCell(numdata);
        RETURN NULL;
      } catch (Exception e) {
           }

CDLT.

**socrate_man** · 15/03/2007, 10h16

Merci taska,

J’ai commencé la mise en place de votre solution, il me reste le déploiement, je sais ou je vais mettre la class java Ediag.
Pour le package je l’est mit dans la base oracle.

Et si c’est possible de me dire plus d’information sur le WRAPPER.

Merci d’avance

Cordialement.

**taska** · 15/03/2007, 10h44

Envoyé par socrate_man

je sais ou je vais mettre la class java Ediag.

Et si c’est possible de me dire plus d’information sur le WRAPPER.

... autant renommer la classe Ediag en Excel par exemple. Il faut ensuite la charger en base.

... plus d'informations. Lesquelles ?

CDLT.

**socrate_man** · 15/03/2007, 10h53

RE,

Envoyé par taska

...Il faut ensuite la charger en base.

ok, mais comment je vais faire pour charger la calss dans la base, est ce que je doit mettre la calss dans un repertoire bien definie ou bien générie Le jar relatif a cette class, en suite le mettre dans le dessier forms/java.

CRDL.

**taska** · 15/03/2007, 11h07

...Il faut ensuite la charger en base.

Plusieurs solutions sont possibles . Moi, je pratique de la façon suivante :

1. Création d'un jar.
2. Chargement du jar

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

loadjava -user user/pass@db -resolve excel.jar

Ne pas oublier de charger de la même façon en 1er le jar JExcel. Qui devra peut être être recompilé suivant la version de la DB que tu utilises.

CDLT.

**socrate_man** · 16/03/2007, 16h43

Bonjour,

Après chargement des class java dans la base (JXL.jar et EXCEL.jar), j ai trouvé ce problème de droit d'accès pour que java peut lire et écrire ds un répertoire du serveur.

Bien pour que cela marche il faut lancer les lignes des codes suivants par le compte SYS :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'java.io.FilePermission', '<>', 'read ,write, execute, delete');
 
EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
 
EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');

Alors j'ai pense a une autre solution, c’est de lire le fichier EXCel d’un champ blob dans la base.

La question posé mnt, est ce que je peux utiliser la même class de lecture Excel qui existe dans L'API JEXCEL, ou bien je dois l'adapté a fin de lire directement un blob.

Cordialement.

**taska** · 16/03/2007, 17h54

Envoyé par socrate_man

ou bien je dois l'adapté a fin de lire directement un blob.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
wb =Workbook.createWorkbook(new File(filename))
....
    try {wb.WRITE();
         wb.close();}

Je pense que la seule solution c'est de passer par un fichier ...

CDLT.

PL/SQL (oracle) et Excel

PL/SQL Oracle

Discussions similaires

Partager

Partager