ExcelUtil/src/main/java/excel/util/ImportRuleSets.java

   1 /*
   2  * Copyright 2004-2007 MTBJ, Inc.
   3  *
   4  * Licensed under the Apache License, Version 2.0 (the "License");
   5  * you may not use this file except in compliance with the License.
   6  * You may obtain a copy of the License at
   7  *
   8  *      http://www.apache.org/licenses/LICENSE-2.0
   9  *
  10  * Unless required by applicable law or agreed to in writing, software
  11  * distributed under the License is distributed on an "AS IS" BASIS,
  12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  13  * See the License for the specific language governing permissions and
  14  * limitations under the License.
  15  */
  16
  17 package excel.util;
  18
  19 import java.io.BufferedWriter;
  20 import java.io.File;
  21 import java.io.FileInputStream;
  22 import java.io.FileNotFoundException;
  23 import java.io.FileOutputStream;
  24 import java.io.FileWriter;
  25 import java.io.IOException;
  26 import java.io.InputStream;
  27 import java.io.OutputStream;
  28 import java.io.PrintStream;
  29 import java.io.Writer;
  30 import java.text.SimpleDateFormat;
  31 import java.util.ArrayList;
  32 import java.util.Date;
  33 import java.util.HashMap;
  34
  35 import org.apache.poi.hssf.usermodel.HSSFCell;
  36 import org.apache.poi.hssf.usermodel.HSSFRow;
  37 import org.apache.poi.hssf.usermodel.HSSFSheet;
  38 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  39 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  40
  41 import com.dtrules.xmlparser.XMLPrinter;
  42
  43 public class ImportRuleSets {
  44
  45     String defaultColumns[]={"number","comments","dsl","table"};
  46
  47     // These are the default columns for the decision tables.
  48     String columns[] = defaultColumns;
  49
  50     /**
  51      * Return the column number for the given column name.
  52      * @param s
  53      * @return
  54      */
  55     int getColumn(String s){
  56        for(int i=0; i< columns.length && columns[i]!= null; i++){
  57            if(columns[i].equalsIgnoreCase(s)) return i;
  58        }
  59        return -1;
  60     }
  61
  62     /**
  63      * Convert all the Excel files in the given directory, and all sub
  64      * directories
  65      * @param directory
  66      * @param sb
  67      * @return true if some file to convert was found.
  68      * @throws Exception
  69      */
  70     private boolean convertFiles(File directory,XMLPrinter out, int depth) throws Exception{
  71         boolean xlsFound = false;
  72         File[] files = directory.listFiles();
  73         for(int i=0; i < files.length; i++){
  74             if(files[i].isDirectory()){
  75                 if(convertFiles(files[i],out,depth+1)){
  76                    for(int j=0;j<depth;j++)System.out.print(" ");
  77                    xlsFound = true;
  78                 }
  79             }else{
  80                 if(files[i].getName().endsWith(".xls")){
  81                   for(int j=0;j<depth;j++)System.out.print(" ");
  82                   convertDecisionTable(files[i], out);
  83                   xlsFound = true;
  84                 }
  85             }
  86         }
  87         return xlsFound;
  88     }
  89
  90     /**
  91      * Convert all the excel files in the given directory, and all sub directories,
  92      * returning a String Buffer of all the XML produced.
  93      * @param directory
  94      * @param destinationFile
  95      * @return
  96      * @throws Exception
  97      */
  98         public void convertDecisionTables(String directory,String destinationFile) throws Exception{
  99                   XMLPrinter out = new XMLPrinter("decision_tables",new FileOutputStream(destinationFile));
 100                   convertFiles(new File(directory),out,0);
 101                   out.close();
 102     }
 103
 104         private String getCellValue(HSSFSheet sheet, int row, int column){
 105             if(row > sheet.getLastRowNum()) return "";
 106             HSSFRow  theRow = sheet.getRow(row);
 107             if(theRow==null)return "";
 108             HSSFCell cell = theRow.getCell((short)column);
 109             if(cell==null)return "";
 110             switch(cell.getCellType()){
 111                 case HSSFCell.CELL_TYPE_BLANK :     return "";
 112                 case HSSFCell.CELL_TYPE_BOOLEAN :   return cell.getBooleanCellValue()? "true": "false";
 113                 case HSSFCell.CELL_TYPE_NUMERIC :{
 114                     Double v = cell.getNumericCellValue();
 115                     if(v.doubleValue() == (v.longValue())){
 116                         return Long.toString(v.longValue());
 117                     }
 118                     return Double.toString(v);
 119                 }
 120                 case HSSFCell.CELL_TYPE_STRING :
 121                     String v = cell.getRichStringCellValue().getString().trim();
 122                     return v;
 123
 124                 default :                           return "";
 125             }
 126     }
 127     /**
 128      * Looks for the value in some column, and returns that index.  This way we can be a bit more
 129      * flexible in our format of the EDD.
 130      * @param value
 131      * @param sheet
 132      * @param row
 133      * @return  the Index of the value, or -1 if not found.
 134      */
 135     private int findvalue(String value, HSSFSheet sheet, int row){
 136         HSSFRow theRow = sheet.getRow(row);
 137         if(theRow==null)return -1;
 138         for(int i=0;i<theRow.getLastCellNum();i++){
 139             String v = getCellValue(sheet,row,i).trim();
 140             v=v.replaceAll(" ", "");
 141             if(v.equalsIgnoreCase(value))return i;
 142         }
 143         return -1;
 144     }
 145
 146     public void convertEDD(String excelFileName, String outputXMLName) throws Exception {
 147         InputStream  input = new FileInputStream(new File(excelFileName));
 148         OutputStream xstrm = new FileOutputStream(outputXMLName);
 149
 150         // If the EDD is an XML file, We assume no conversion is necessary.
 151         if(excelFileName.endsWith(".xml")){
 152             // Transfer bytes from in to out
 153             byte[] buf = new byte[1024];
 154             int len;
 155             while ((len = input.read(buf)) > 0) {
 156                 xstrm.write(buf, 0, len);
 157             }
 158             input.close();
 159             xstrm.close();
 160             return;
 161
 162         }else if(! (excelFileName.endsWith(".xls"))) throw new Exception("EDD Excel File name is invalid");
 163         // If the EDD is an Excel file, we need to convert the thing.
 164
 165         HSSFWorkbook wb = new HSSFWorkbook(input);
 166         HSSFSheet sheet = wb.getSheetAt(0);
 167
 168         // Open the EDD.xml output file
 169
 170         XMLPrinter   xout = new XMLPrinter("entity_data_dictionary", xstrm);
 171
 172         // Write out a header in the EDD xml file.
 173         xout.opentag("edd_header");
 174            xout.printdata("edd_create_stamp",
 175                 new SimpleDateFormat("EEE, d MMM yyyy HH:mm:ss Z").format(new Date())
 176            );
 177            xout.printdata("Excel_File_Name",excelFileName);
 178         xout.closetag();
 179         xout.opentag("edd");
 180
 181
 182         // Get the indexes of the columns we need to write out the XML for this EDD.
 183         int rows = sheet.getLastRowNum();
 184         int entityIndex    = findvalue("entity",sheet,0);
 185         int attributeIndex = findvalue("attribute",sheet,0);
 186         int typeIndex      = findvalue("type",sheet,0);
 187         int subtypeIndex   = findvalue("subtype",sheet,0);
 188         int defaultIndex   = findvalue("defaultvalue",sheet,0);
 189         int inputIndex     = findvalue("input",sheet,0);
 190         int accessIndex    = findvalue("access",sheet,0);
 191         int commentIndex   = findvalue("comment",sheet,0);      // optional
 192         int sourceIndex    = findvalue("source",sheet,0);       // optional
 193
 194         // Some columns we just have to have.  Make sure we have them here.
 195         if(entityIndex <0 || attributeIndex < 0 || typeIndex < 0 || defaultIndex < 0 || accessIndex < 0 || inputIndex <0 ){
 196             String err = " Couldn't find the following column header(s): "+
 197               (entityIndex<0?" entity":"")+
 198               (attributeIndex<0?" attribute":"")+
 199               (typeIndex<0?" type":"")+
 200               (defaultIndex<0?" default value":"")+
 201               (accessIndex<0?" access":"")+
 202               (inputIndex<0?" input":"");
 203             throw new Exception("This EDD may not be valid, as we didn't find the proper column headers\n"+err);
 204         }
 205
 206         // Go through each row, writing out each entry to the XML.
 207         for(int row = 1; row <=rows; row++){
 208             String entityname = getCellValue(sheet,row,entityIndex);    // Skip all the rows that have no Entity
 209             if(entityname.length()>0){
 210                 String src     = sourceIndex>=0 ? getCellValue(sheet,row,sourceIndex):"";
 211                 String comment = commentIndex>=0 ? getCellValue(sheet,row,commentIndex):"";
 212                 xout.opentag("entry");
 213                 xout.opentag("entity",
 214                         "entityname"        , entityname,
 215                         "attribute"         , getCellValue(sheet,row,attributeIndex),
 216                         "type"              , getCellValue(sheet,row,typeIndex),
 217                         "subtype"           , getCellValue(sheet,row,subtypeIndex),
 218                         "default"           , getCellValue(sheet,row,defaultIndex),
 219                         "access"            , getCellValue(sheet,row,accessIndex),
 220                         "input"             , getCellValue(sheet,row,inputIndex)
 221                 );
 222                 xout.closetag();
 223                 if(comment.length()>0)xout.printdata("comment",getCellValue(sheet,row,commentIndex));
 224                 if(src    .length()>0 )xout.printdata("source", getCellValue(sheet,row,sourceIndex));
 225                 xout.closetag();
 226             }
 227         }
 228         xout.closetag();
 229         xout.close();
 230     }
 231     /**
 232      * Pulls the ATTRIBUTE name out of the next cell.  The assumption is that
 233      * all ATTRIBUTES (Including the main sections of the decision table) are
 234      * all in the first column of a row, followed by a colon.
 235      *
 236      * We ignore numeric Attribute names.
 237      * @param sheet
 238      * @param row
 239      * @return
 240      */
 241     private String getNextAttrib(HSSFSheet sheet, int row){
 242         String value      = getCellValue(sheet, row, 0).trim();
 243         int    colonIndex = value.indexOf(":");
 244         if(colonIndex>1){
 245            String attrib  = value.substring(0,colonIndex);
 246            attrib  = attrib.replaceAll(" ", "_").toLowerCase();
 247            try {
 248                           Integer.parseInt(attrib);
 249                           attrib = "";
 250                    } catch (NumberFormatException e) { }
 251            return attrib;
 252         }
 253         return "";
 254     }
 255    /**
 256     * Pulls the ATTRIBUTE Value out of the next cell.  If no attribute value
 257     * is found, then the value of column 0 is returned, whitespace trimmed off.
 258     *
 259         * Ah, but we add a wrinkle.  If the value
 260     * we find in column 0 is a number, then we return the value from column
 261     * column 3.
 262     *
 263     * @param sheet
 264     * @param row
 265     * @return
 266     */
 267     private String getNextAttribValue(HSSFSheet sheet, int row){
 268         String value      = getCellValue(sheet, row, 0).trim();
 269         int    colonIndex = value.indexOf(":");
 270         if(colonIndex>1){
 271            value = value.substring(colonIndex+1).trim();
 272         }
 273         try{
 274                 Integer.parseInt(value);
 275                 value = getCellValue(sheet,row,2).trim();
 276         }catch(NumberFormatException e){};
 277         return value;
 278     }
 279     /**
 280      * Returns the value of the number column.  You don't have to have one
 281      * of these.
 282      *
 283      * @param sheet
 284      * @param row
 285      * @return
 286      */
 287     private String getNumber(HSSFSheet sheet, int row){
 288         int field = getColumn("number");
 289         if(field==-1)return "";
 290         String value = getCellValue(sheet,row, field);
 291         return value.trim();
 292     }
 293     /**
 294      * Any Section can get the DSL specified in the section from this call.
 295      *
 296      * @param sheet
 297      * @param row
 298      * @return
 299      */
 300     private String getDSL(HSSFSheet sheet, int row){
 301         int field = getColumn("dsl");
 302         if(field==-1)throw new RuntimeException("No DSL Column");
 303         String value = getCellValue(sheet,row, field);
 304         return value.trim();
 305     }
 306     /**
 307      * Returns the contents of the Comment column... You don't have to have one
 308      * of these.
 309      *
 310      * @param sheet
 311      * @param row
 312      * @return
 313      */
 314     private String getComments(HSSFSheet sheet, int row){
 315         int field = getColumn("comments");
 316         if(field==-1)return "";
 317         String value = getCellValue(sheet,row, field);
 318         return value.trim();
 319     }
 320
 321     /**
 322      * Returns the Table value
 323      * of these.
 324      *
 325      * @param sheet
 326      * @param row
 327      * @return
 328      */
 329     private String getTableValue(HSSFSheet sheet, int row, int tableIndex){
 330         int field = getColumn("table");
 331         if(field==-1)return "";
 332         String value = getCellValue(sheet,row, field+tableIndex);
 333         return value.trim();
 334     }
 335
 336     /**
 337      * Returns the value of the Requirement Reference column... You don't have to have one
 338      * of these.
 339      *
 340      * @param sheet
 341      * @param row
 342      * @return
 343      */
 344     private String getRequirement(HSSFSheet sheet, int row){
 345         int field = getColumn("requirement");
 346         if(field==-1)return "";
 347         String value = getCellValue(sheet,row, field);
 348         return value.trim();
 349     }
 350
 351
 352     /**
 353      * Returns the index of the heading of the next block.
 354      * @param sheet
 355      * @param row
 356      * @return
 357      */
 358     int nextBlock(HSSFSheet sheet, int row){
 359         String attrib = getNextAttribValue(sheet, row);
 360         while(attrib == ""){
 361             row++;
 362             attrib = getNextAttribValue(sheet, row);
 363             if(row > sheet.getLastRowNum()) return row-1;
 364         }
 365         return row;
 366     }
 367
 368     /**
 369      * Reads the decision table out of an Excel spreadsheet and generates the
 370      * approriate XML.
 371      * @param file
 372      * @param sb
 373      * @return true if at least one decision table was found in this file
 374      * @throws Exception
 375      */
 376         public boolean convertDecisionTable(File file,XMLPrinter out) throws Exception{
 377                 if(! (file.getName().endsWith(".xls"))) return false;
 378
 379                 InputStream input = new FileInputStream(file.getAbsolutePath());
 380         POIFSFileSystem fs = new POIFSFileSystem( input );
 381         HSSFWorkbook wb = new HSSFWorkbook(fs);
 382         boolean tablefound = false;
 383         for(int i=0; i< wb.getNumberOfSheets(); i++){
 384             tablefound |= convertOneSheet(file.getName(),wb.getSheetAt(i),out);
 385         }
 386         return tablefound;
 387
 388         }
 389     /**
 390      * Returns true if the given sheet describes a valid DecisionTable.
 391      *
 392      * @param filename
 393      * @param sheet
 394      * @param sb
 395      * @return
 396      */
 397     private boolean convertOneSheet(String filename, HSSFSheet sheet,XMLPrinter out){
 398
 399         columns = defaultColumns;
 400
 401         // The first row of a decision table has to provide the decision table name.  This is required!
 402         // Must be the first row, must have a NAME: tag, followed by a decision table name!
 403         String attrib = getNextAttrib(sheet, 0);
 404         String value  = getNextAttribValue(sheet, 0);
 405         if(!attrib.equalsIgnoreCase("name") || value.length()==0){
 406                 return false;
 407         }
 408         out.opentag("decision_table");
 409
 410         String dtName = value.replaceAll("[\\s]+", "_");
 411
 412         ArrayList<String> attributes = new ArrayList<String>();
 413
 414         int rowIndex = 1;
 415         // Go through the attribute rows, identified by some tag followed by a colon.  When we reach a heading
 416         // tag of CONDITIONS: then we stop!  These headers we preserve in the resulting XML.
 417         for(rowIndex=1;true;rowIndex++){
 418
 419           attrib = getNextAttrib(sheet, rowIndex);
 420           value  = getNextAttribValue(sheet, rowIndex);
 421
 422           // Once we transitioned to the table upon seeing the CONDITIONS: tag.  We are
 423           // grandfathering that into our parsing.  Any other segment (Context or Initial Actions)
 424           // requires a blank line to step the processing on.  First optional segment is Contexts:,
 425           // followed by Initial_Actions:, followed by the Condition table.
 426           if( attrib.equalsIgnoreCase("conditions") || (attrib.length()==0 && value.length()==0)){
 427               break;
 428           }
 429           String v = value.trim();
 430           attributes.add(attrib);
 431           attributes.add(v);
 432
 433           // If we have a columns attribute, then the column numbers and order are specfied there.
 434           // COLUMNS:  number, comment, requirements, dsl, table
 435           if(attrib.equalsIgnoreCase("columns")){
 436               v = v.substring(2).trim();
 437               columns = v.split("[,\\s]+");
 438           }
 439
 440         }
 441         out.printdata("table_name",dtName);
 442         out.printdata("xls_file",filename);
 443         out.opentag("attribute_fields");
 444           for(int i=0; i< attributes.size(); i+=2){
 445               out.printdata(attributes.get(i),attributes.get(i+1));
 446           }
 447         out.closetag();
 448
 449         rowIndex = nextBlock(sheet, rowIndex);
 450         attrib = getNextAttrib(sheet, rowIndex);
 451
 452         if(attrib.equalsIgnoreCase("contexts")){
 453             rowIndex++;
 454             out.opentag("contexts");
 455             while(true){
 456                 attrib           = getNextAttrib(sheet, rowIndex);
 457                 if(attrib.length()>0)break;
 458                 String context   = getDSL(sheet, rowIndex);
 459                 if(context != "")
 460                 {
 461                     out.opentag("context_details");
 462                     out.printdata("context_description",context);
 463                     out.closetag();
 464                 }
 465                 rowIndex++;
 466             }
 467             out.closetag();
 468         }
 469
 470         rowIndex = nextBlock(sheet, rowIndex);
 471         attrib = getNextAttrib(sheet, rowIndex);
 472
 473         if(attrib.equalsIgnoreCase("initial_actions")){
 474             rowIndex++;
 475             out.opentag("initial_actions");
 476             while(isAction(sheet,rowIndex)){
 477                 String initialActionDescription = getDSL(sheet, rowIndex);
 478
 479                 if(initialActionDescription != "")
 480                 {
 481                     out.opentag("initial_action_details");
 482                     String actionNumber = getNumber(sheet, rowIndex);
 483                     out.printdata("initial_action_number",actionNumber);
 484
 485                     String initialActionComment = getComments(sheet, rowIndex);
 486                     out.printdata("initial_action_comment",initialActionComment);
 487
 488                     out.printdata("initial_action_description",initialActionDescription);
 489                     out.closetag();
 490                 }
 491                 rowIndex++;
 492             }
 493             out.closetag();
 494         }
 495
 496         rowIndex = nextBlock(sheet, rowIndex);
 497         attrib = getNextAttrib(sheet, rowIndex);
 498         rowIndex++;
 499
 500         out.opentag("conditions");
 501         while(isCondition(sheet, rowIndex)){
 502
 503                 String conditionDescription = getDSL(sheet, rowIndex);
 504
 505                 if(conditionDescription != "") {
 506                         out.opentag("condition_details");
 507                         String conditionNumber = getNumber(sheet, rowIndex);
 508                 out.printdata("condition_number",conditionNumber);
 509
 510                         String conditionComment = getNumber(sheet, rowIndex);
 511                 out.printdata("condition_comment",conditionComment);
 512
 513                 out.printdata("condition_description",conditionDescription);
 514
 515                         for(int j=0; j<16;j++){
 516                                 String columnValue =getTableValue(sheet, rowIndex, j);
 517                                 if(columnValue.equals(""))columnValue = "-";
 518                                 if ((columnValue.equalsIgnoreCase("*")) ||
 519                                     (columnValue.equalsIgnoreCase("-")) ||
 520                                     (columnValue.equalsIgnoreCase("y")) ||
 521                                     (columnValue.equalsIgnoreCase("n"))) {
 522                                     out.printdata("condition_column","column_number",""+(j+1),"column_value",columnValue,null);
 523                                 }else{
 524         //                              if(columnValue != "")
 525         //                      throw new Exception("Undesired value in the condition matrix");
 526                                 }
 527                         }
 528                 out.closetag();
 529                 }
 530             rowIndex++;
 531         }
 532         out.closetag();
 533
 534         rowIndex = nextBlock(sheet, rowIndex);
 535         attrib = getNextAttrib(sheet, rowIndex);
 536         rowIndex++;
 537
 538         out.opentag("actions");
 539                 while(isAction(sheet,rowIndex)){
 540                     String actionDescription = getDSL(sheet, rowIndex);
 541                 if(actionDescription.length()>0){
 542                     out.opentag("action_details");
 543                         String actionNumber = getNumber(sheet, rowIndex);
 544                         out.printdata("action_number",actionNumber);
 545
 546                         String actionComment = getComments(sheet, rowIndex);
 547                     out.printdata("action_comment",actionComment);
 548
 549                     out.printdata("action_description",actionDescription);
 550
 551                         for(int j=0; j<16;j++){
 552                                 String columnValue = getTableValue(sheet, rowIndex, j);
 553                                 if (columnValue.equalsIgnoreCase("x") ||
 554                                         columnValue.equalsIgnoreCase("s")    ) {
 555                             out.printdata("action_column","column_number",""+(j+1),"column_value",columnValue,null);
 556                                 }else{
 557                                         if(columnValue.length() != 0){
 558                                    System.out.println(dtName+": Undesired value '"+columnValue+"' in the action matrix ("+j+","+rowIndex+")");
 559                                         }
 560                                 }
 561                         }
 562                     out.closetag();
 563                 }
 564                 rowIndex++;
 565
 566                 }
 567             out.closetag();
 568             out.closetag();
 569                 return true;
 570         }
 571
 572         /**
 573          * We used to do something really smart.  Now we just return false at the end of the spread
 574          * sheet or if we encounter another block.
 575          * @param sheet
 576          * @param rowIndex
 577          * @return
 578          */
 579         private boolean isAction(HSSFSheet sheet, int rowIndex){
 580              String attrib = getNextAttrib(sheet, rowIndex);
 581              if (attrib.length()>0) return false;
 582                  if(rowIndex > sheet.getLastRowNum()) return false;
 583              return true;
 584         }
 585
 586     /**
 587      * We used to do something really smart.  Now we just return false at the end of the spread
 588      * sheet or if we encounter another block.
 589      * @param sheet
 590      * @param rowIndex
 591      * @return
 592      */
 593     private boolean isCondition(HSSFSheet sheet, int rowIndex){
 594          String attrib = getNextAttrib(sheet, rowIndex);
 595          if (attrib.length()>0) return false;
 596          if(rowIndex > sheet.getLastRowNum()) return false;
 597          return true;
 598     }
 599
 600         public void setContents(File aFile, String aContents)
 601     throws FileNotFoundException, IOException {
 602                 if (aFile == null) {
 603                 throw new IllegalArgumentException("File should not be null.");
 604                 }
 605
 606                 //declared here only to make visible to finally clause; generic reference
 607                 Writer output = null;
 608                 try {
 609                 //use buffering
 610                 //FileWriter always assumes default encoding is OK!
 611                 output = new BufferedWriter( new FileWriter(aFile) );
 612                 output.write( aContents );
 613                 }
 614                 finally {
 615                 //flush and close both "output" and its underlying FileWriter
 616                 if (output != null) output.close();
 617                 }
 618         }
 619
 620
 621 }