Accéder au contenu principal

Génération de requêtes SQL depuis une feuille Excel

Dans ce billet, je vais présenter un petit utilitaire que j'ai développé et qui m'a permis de gagner beaucoup de temps dans les phases de migration de bases de données. Il permet également de définir tout le référentiel d'une base de données, avec notamment les données qui ne sont pas souvent modifiées, dans une feuille Excel et de générer les requêtes SQL d'insertion ou de mise à jour équivalente. Je me suis appuyé pour cela sur deux librairies :
 Le code source tient en une seule classe.

 package com.alu.convert;  
   
 import java.io.File;  
 import java.io.IOException;  
   
 import biz.source_code.miniTemplator.MiniTemplator;  
   
 import jxl.Sheet;  
 import jxl.Workbook;  
   
 /*  
  * This class is used to generate a file based on an Excel Sheet.  
  * The initial purpose is to generate SQL inserts, but this can be applied to  
  * other type of files (SQL updates, Web pages, etc).  
  */  
   
 public class ExcelToSQL {  
      /**  
       * Input file to process (Excel sheet)  
       */  
      String _inputFile;  
        
      /**  
       * Initialize the Class with the input file to process  
       * @param inputFile  
       */  
      public ExcelToSQL(String inputFile) {  
           _inputFile = inputFile;  
      }  
        
   /**  
    * Start processing the Excel file  
    * Each Sheet will generate a separate File   
    */  
   private void generateWorkbook() {  
     try {  
       Workbook w = Workbook.getWorkbook(new File(_inputFile));  
       for (int i = 0; i < w.getNumberOfSheets(); i++) {  
         Sheet s = w.getSheet(i);  
         generateSQLCommands(i, s);  
       }  
   
       w.close();  
     } catch (Throwable t) {  
       System.out.println(t.toString());  
       t.printStackTrace();  
     }  
   }  
   
   /**  
    * Generate the Blocks for a sheet  
    * @param id - Identifer of the output file (1-Entity)  
    * @param sheet - Sheet to process  
    * @throws IOException  
    */  
   private void generateSQLCommands(int id, Sheet sheet) throws IOException {  
     String objectName = sheet.getName();  
           MiniTemplator t;  
           t = new MiniTemplator("templates/" + objectName + ".tpl");  
       
           try {  
          for (int i = 1; i < sheet.getRows(); i++) {  
            for (int j = 0; (j < sheet.getColumns()); j++) {  
                 String parameterName = sheet.getCell(j, 0).getContents();  
              String value = sheet.getCell(j, i).getContents();  
              t.setVariableOpt(parameterName.toUpperCase(), value);  
            }  
            t.addBlock("dataInput");  
          }  
          t.generateOutput("output/" + id + "-" + objectName + ".sql");  
           } catch (Exception e) {  
       System.out.println(objectName + " - " + e.toString());  
       e.printStackTrace();  
           }  
   }  
     
   /**  
    * Displays the acceptable command line arguments  
    */  
   private static void displayHelp() {  
     System.err.println("Command format: excel2sql ");  
     System.err.println("         excel2sql inputfile outputfile");  
     System.err.println("         excel2sql -v|-version");  
     System.err.println("         excel2sql -h|-help");  
   }  
     
   /**  
    * The main method. Gets the worksheet and then uses the API   
    * to process an Excel sheet  
    * @param args the command line arguments  
    */  
   public static void main(String[] args) {  
     if (args.length == 0) {  
       displayHelp();  
       System.exit(1);  
     }  
   
     if (args[0].equals("-help") || args[0].equals("-h")) {  
       displayHelp();  
       System.exit(1);  
     }  
   
     if (args[0].equals("-version") || args[0].equals("-v")) {  
       System.out.println("v" + Workbook.getVersion());  
       System.exit(0);  
     }  
       
     if (args.length >= 1) {  
       ExcelToSQL excel = new ExcelToSQL(args[0]);  
       excel.generateWorkbook();  
       System.exit(0);  
     }  
   }  
 }  
   

La feuille Excel doit contenir au moins un workbook (onglet). Le nom de l'onglet est utilisé pour faire référence au nom du modèle (template) correspondant dans le répertoire "template". Les noms des entêtes du WorkBook sont utilisés comme des champs dans le modèle. La l'exemple ci-dessous, le nom de l'onglet est "price". Nous allons donc chercher un modèle avec le nom "price.tpl".


Le modèle utilise le format défini dans minitemplator et utilise les noms des colonnes comme noms de champs. Le contenu du fichier "price.tpl" est le suivant :

 drop table prices;  
 <!-- $BeginBlock dataInput -->insert into Prices(Name, Prefix, Buy, Sell) values ('${NAME}', '${PREFIX}', ${BUY}, ${SELL});  
 <!-- $EndBlock dataInput -->  

Les balises <!--$BeginBlock dataInput --> et  <!--$BeginBlock dataInput --> définissent un bloc répétitif.
Les propriétés correspondant aux noms des colonnes sont définies par ${NAME}, ${PREFIX}, ${BUY} et ${SELL}.

Le résultat de l'application du modèle sur le fichier Excel est le suivant :

 drop table prices;  
 insert into Prices(Name, Prefix, Buy, Sell) values ('Afghanistan - Mobile Roshan ', '9379', 0,143, 0,162);  
 insert into Prices(Name, Prefix, Buy, Sell) values ('Afghanistan - Mobile WASEL ', '937500', 0,138, 0,156);  
 insert into Prices(Name, Prefix, Buy, Sell) values ('Afghanistan - Mobile WASEL ', '937501', 0,138, 0,156);  
 insert into Prices(Name, Prefix, Buy, Sell) values ('Afghanistan - Mobile WASEL ', '937502', 0,138, 0,156);  
 insert into Prices(Name, Prefix, Buy, Sell) values ('Afghanistan - Mobile WASEL ', '937503', 0,138, 0,156);  
 insert into Prices(Name, Prefix, Buy, Sell) values ('Afghanistan - Mobile WASEL ', '937504', 0,138, 0,156);  
   

J'ai utilisé ces 2 librairies en raison de leur taille relativement faible. Ces deux librairies peuvent aisément être remplacées par Apache POI pour la lecture de feuilles Excel et par MVel pour le remplacement de modèles.

L'ensemble du projet est disponible ici.
A apprécier sans modération.


Commentaires

Posts les plus consultés de ce blog

Supprimer les partitions Raspberry sur une carte SD sous Windows 7 avec Diskpart

Si vous souhaitez récupérer une ancienne carte SD utilisée pour démarrer un Raspberry pour un autre usage (appareil photo, etc), il vous faudra supprimer les deux partitions créées au moment de l'écriture de l'image sur la carte SD. Vous pouvez voir les partition en sélectionnant Menu Windows/Ordinateur/bouton droit "Gérer". Voici un exemple du résultat final. Vous pouvez supprimer la partition Unix de 7 Gb (ou 4Gb en fonction de la taille de votre carte) en sélectionnant la partition puis en faisant "bouton droit Supprimer". Laissez juste une partition pour pouvoir faire les autres manipulations avec DISKPART. Démarrez l'outil DISKPART en ligne de commande. Une nouvelle fenêtre s'ouvrira. Microsoft DiskPart version 6.1.7601 Copyright (C) 1999-2008 Microsoft Corporation. Sur l'ordinateur : FRVILN0H305806 DISKPART> list disk   N° disque  Statut         Taille   Libre    Dyn  GPT   ---------  -------------  -------  -------  ---  ---   D

Comment lire son écran de téléphone Android sur PC (2)

Voici une nouvelle manière d'accéder à votre téléphone depuis votre PC. Cette méthode utilise le câble USB et Google Chrome. Elle fonctionne donc sur Windows, Mac et Linux. Les pré-requis sont les mêmes que ceux du billet précédent : Téléphone configuré en mode Développement Drivers USB installés sur le PC Google Chrome installé L'application Vysor s'installe comme une extension de Google Chrome. Cherchez dans le Webstore et lancez l'installation. Une fois installée, l'application apparaît dans les extensions. Vous pouvez la lancer de différentes manières. La manière la plus simple est d'ouvrir la fenêtre des applications : chrome://apps/ Une autre manière est de créer un raccourci en cliquant sur « Détails ». Ensuite créez un raccourci sur le bureau, le menu démarrer et/ou la barre de tâche. Vous pouvez également cliquer sur afficher dans la boutique et lancer l'application depuis la boutique. Une fois

Serveur d'impression CUPS sur Raspberry Pi

Je possède une très vieille imprimante laser HP Laserjet 6L noir et blanc de 12 ans d'age qui a survécu à plusieurs imprimantes jet d'encre couleurs tombées en désuétude pour obsolescence programmée. J'envisage tout de même sérieusement de les démanteler pour les recycler en CNC, graveur ou autre.   Je continue donc a utiliser cette bonne petite imprimante parallèle qui me rend bien des services (en 12 ans, j'ai changé le toner 3 fois pour un coût de 20 € la cartouche). J'ai donc dû m'équiper d'une adaptateur USB/Parallèle pour pouvoir la connecter sur un PC récent. Le problème principal est que le driver de cette imprimate n'est reconnu que par un seul de mes PC sous Windows 7. Les autres PC sous Windows 8 ou Windows 10 reconnaissent bien l'imprimante, mais sortent des feuilles avec le code Postscript. L'impression depuis une tablette ou un téléphone Androïd est également tout simplement impossible. Pour remédier à ce problème, j'ai don