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 :

• jxl.jar pour la lecture de la feuille Excel
• minitemplator.jar pour la génération de fichier à partir d'un template

 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.