@Test
  public void testJExcel() throws Exception {
    AbstractJExcelView excelView =
        new UnixSafeAbstractJExcelView() {
          @Override
          protected void buildExcelDocument(
              Map<String, Object> model,
              WritableWorkbook wb,
              HttpServletRequest request,
              HttpServletResponse response)
              throws Exception {
            WritableSheet sheet = wb.createSheet("Test Sheet", 0);
            // test all possible permutation of row or column not existing
            sheet.addCell(new Label(2, 4, "Test Value"));
            sheet.addCell(new Label(2, 3, "Test Value"));
            sheet.addCell(new Label(3, 4, "Test Value"));
            sheet.addCell(new Label(2, 4, "Test Value"));
          }
        };

    excelView.render(new HashMap<String, Object>(), request, response);

    Workbook wb = Workbook.getWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
    assertEquals("Test Sheet", wb.getSheet(0).getName());
    Sheet sheet = wb.getSheet("Test Sheet");
    Cell cell = sheet.getCell(2, 4);
    assertEquals("Test Value", cell.getContents());
  }
  public static void addRowTop(WritableSheet ws) throws Exception {

    WritableFont Bwf =
        new WritableFont(
            WritableFont.ARIAL, 16, WritableFont.BOLD, false); // 创建大字体:Arial,大小为18号,粗体,非斜体
    Bwf.setColour(jxl.format.Colour.BLACK); // //字体颜色为红色
    // 创建单元格格式:
    jxl.write.WritableCellFormat CwcfF = new jxl.write.WritableCellFormat(Bwf);
    CwcfF.setAlignment(jxl.write.Alignment.CENTRE); // 设置水平对齐为居中对齐
    CwcfF.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置垂直对齐为居中对齐
    CwcfF.setBorder(jxl.format.Border.TOP, BorderLineStyle.MEDIUM); // 设置顶部边框线为实线(默认是黑色--也可以设置其他颜色)
    CwcfF.setBorder(jxl.format.Border.RIGHT, BorderLineStyle.MEDIUM); // 设置右边框线为实线
    CwcfF.setBorder(jxl.format.Border.BOTTOM, BorderLineStyle.MEDIUM); // 设置顶部框线为实线

    List<Label> labelList = new ArrayList<Label>();
    labelList.add(new Label(0, 0, "商品名称", CwcfF)); //
    labelList.add(new Label(1, 0, "单位", CwcfF)); //
    labelList.add(new Label(2, 0, "销售数量", CwcfF)); //
    labelList.add(new Label(3, 0, "销售成本", CwcfF));
    labelList.add(new Label(4, 0, "销售金额", CwcfF));
    labelList.add(new Label(5, 0, "利润", CwcfF));
    labelList.add(new Label(6, 0, "利润率", CwcfF));
    for (int j = 0; j < labelList.size(); j++) {
      ws.addCell(labelList.get(j));
    }
    for (int i = 0; i < ws.getColumns(); i++) {
      Cell cell = ws.getCell(i, 0);
      ws.setColumnView(i, cell.getContents().length() * 4);
    }
    //		ws.setRowView(0, 80*4);
  }
 public static List<String[]> readExcel(Context ctx, String fileName, Object targetSheet) {
   List<String[]> rowList = new ArrayList<String[]>();
   try {
     InputStream mInputStream = ctx.getResources().getAssets().open(fileName);
     Workbook wb = Workbook.getWorkbook(mInputStream);
     Sheet mSheet = null;
     if (targetSheet instanceof Integer) {
       mSheet = wb.getSheet((Integer) targetSheet);
     } else {
       mSheet = wb.getSheet((String) targetSheet);
     }
     int row = getRowCount(mSheet);
     int columns = getColCount(mSheet);
     Log.d("W", "Total Row: " + row + ", Total Columns: " + columns);
     String[] colArray = new String[columns];
     for (int i = 1; i < row; i++) {
       for (int j = 0; j < columns; j++) {
         Cell temp = mSheet.getCell(j, i);
         String content = temp.getContents();
         Log.d("W", j + " ," + i + " ," + content);
         colArray[j] = content;
       }
       rowList.add(colArray);
     }
     wb.close();
     mInputStream.close();
   } catch (BiffException e) {
     e.printStackTrace();
   } catch (IndexOutOfBoundsException e) {
     e.printStackTrace();
   } catch (IOException e) {
     e.printStackTrace();
   }
   return rowList;
 }
  @Test
  public void testJExcelWithTemplateAndLanguage() throws Exception {
    request.setAttribute(
        DispatcherServlet.LOCALE_RESOLVER_ATTRIBUTE, newDummyLocaleResolver("de", ""));

    AbstractJExcelView excelView =
        new UnixSafeAbstractJExcelView() {
          @Override
          protected void buildExcelDocument(
              Map<String, Object> model,
              WritableWorkbook wb,
              HttpServletRequest request,
              HttpServletResponse response)
              throws Exception {
            WritableSheet sheet = wb.getSheet("Sheet1");
            // test all possible permutation of row or column not existing
            sheet.addCell(new Label(2, 4, "Test Value"));
            sheet.addCell(new Label(2, 3, "Test Value"));
            sheet.addCell(new Label(3, 4, "Test Value"));
            sheet.addCell(new Label(2, 4, "Test Value"));
          }
        };

    excelView.setApplicationContext(webAppCtx);
    excelView.setUrl("template");
    excelView.render(new HashMap<String, Object>(), request, response);

    Workbook wb = Workbook.getWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
    Sheet sheet = wb.getSheet("Sheet1");
    Cell cell = sheet.getCell(0, 0);
    assertEquals("Test Template auf Deutsch", cell.getContents());
  }
Example #5
0
  public ArrayList<String> getCitiesFromExcel() {

    ArrayList<String> cities = new ArrayList<String>();
    try {
      Workbook workbook =
          Workbook.getWorkbook(this.getClass().getResourceAsStream("/resource/cities.xls"));
      int totalsheet = workbook.getNumberOfSheets();
      for (int i = 0; i < totalsheet; i++) {
        Sheet sheet = workbook.getSheet(i);
        int rows = sheet.getRows();

        for (int j = 1; j < rows; j++) {
          Cell cell1 = sheet.getCell(0, j);
          cities.add(cell1.getContents());
        }
      }
      return cities;
    } catch (IOException e) {
      e.printStackTrace();
      return null;
    } catch (BiffException e) {
      e.printStackTrace();
      return null;
    }
  }
 /**
  * Get the cell value. Compensates for empty cells and cells that contain errors.
  *
  * @param C Cell
  * @return
  */
 private static String getCellValue(Cell C) {
   CellType type = C.getType();
   if (type == CellType.EMPTY || type == CellType.ERROR || type == CellType.FORMULA_ERROR) {
     return "";
   } else {
     return C.getContents();
   }
 }
Example #7
0
 /**
  * @param sheet
  * @param numrow
  * @return
  */
 public static ArrayList readrow(WritableSheet sheet, int numrow) {
   ArrayList contentlist = new ArrayList();
   for (int numcol = 0; numcol < sheet.getColumns(); numcol++) {
     Cell cell = sheet.getCell(numcol, numrow);
     String content = cell.getContents();
     contentlist.add(content);
   }
   return contentlist;
 }
 public int getCcIndexForCell(Cell cell, ArrayList<ConnexComposant> ccs) {
   for (int i = 0; i < ccs.size(); i++) {
     if (cell.getRow() >= ccs.get(i).getStartY()
         && cell.getRow() <= ccs.get(i).getEndY()
         && cell.getColumn() >= ccs.get(i).getStartX()
         && cell.getColumn() <= ccs.get(i).getEndX()) {
       return i;
     }
   }
   return -1;
 }
 /**
  * Determine if a cell row is blank.
  *
  * @param Cells Row of cells
  * @return
  */
 private static boolean isBlankRow(Cell[] Cells) {
   if (Cells == null || Cells.length == 0) {
     return true;
   }
   // if first cell is blank, row is considered blank
   Cell c = Cells[0];
   CellType type = c.getType();
   if (type == CellType.EMPTY) {
     return true;
   }
   return false;
 }
Example #10
0
 public static int nextFreeColNumber(WritableSheet sheet) {
   if (sheet == null) return 0;
   int colCount = sheet.getColumns();
   Cell[] cells;
   int i = 0;
   for (; i < colCount; i++) {
     cells = sheet.getColumn(i);
     for (Cell cell : cells) {
       if (cell.getContents() == null || cell.getContents().isEmpty()) return i;
     }
   }
   return i;
 }
Example #11
0
 /**
  * 取得公式内容
  *
  * @param colNum
  * @param rowNum
  * @return
  * @throws FormulaException
  */
 public String getFormula(int colNum, int rowNum) throws FormulaException {
   Cell cell = getCell(colNum, rowNum);
   FormulaCell nfc = (FormulaCell) cell;
   // 如果为公式类型
   if (cell.getType() == CellType.NUMBER_FORMULA
       || cell.getType() == CellType.STRING_FORMULA
       || cell.getType() == CellType.BOOLEAN_FORMULA
       || cell.getType() == CellType.DATE_FORMULA
       || cell.getType() == CellType.FORMULA_ERROR) {
     return nfc.getFormula();
   } else {
     logger.error("cell is not Formula");
     throw new BaseException("cell is not Formula");
   }
 }
  public static List<Map<String, String>> getXlsUserInfoData(String path) {
    try {
      File xlsFile = new File(path);
      Workbook book = Workbook.getWorkbook(xlsFile);
      Sheet[] sheet = book.getSheets();
      if (sheet.length > 0) {
        Sheet page = sheet[0];
        int columns = page.getColumns();
        System.out.println(columns);
        int rows = page.getRows();
        System.out.println(page.getName());
        if (rows < 2) return null;

        int columnsCount = 0;

        List<String> titleList = new ArrayList<String>();
        for (int i = 0; i < columns; i++) {
          // 列    行
          Cell cell = page.getCell(i, 0);
          String key = cell.getContents();
          if (key == null || key.equals("")) {
            continue;
          }
          columnsCount++;
          logger.debug(key);
          titleList.add(key);
        }
        columns = columnsCount;
        List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
        for (int i = 1; i < rows; i++) {
          logger.info("ii:" + i);
          Map<String, String> dataMap = new HashMap<String, String>();
          for (int j = 0; j < columns; j++) {
            String key = titleList.get(j);
            String value = page.getCell(j, i).getContents();
            dataMap.put(key, value);
            logger.debug(key + "  " + value);
          }
          dataList.add(dataMap);
        }
        logger.debug("xls length:" + dataList.size());
        return dataList;
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    }
    return null;
  }
  public void loadExcelData2DB(String excelPath, String sheetName) throws Exception {
    Workbook book = Workbook.getWorkbook(new File(excelPath));
    Sheet st = book.getSheet(sheetName);
    int totalColumns = 220;
    int totalRows = st.getRows();

    // 1. get columns name
    String[] columnNames = new String[totalColumns];
    for (int i = 0; i < totalColumns; i++) {
      columnNames[i] = st.getCell(i, 2).getContents();
    }

    String value = null;
    for (int row = 3; row < totalRows; row++) {
      JewelryEntity entity = new JewelryEntity();
      int col;
      for (col = 0; col < totalColumns; col++) {
        Cell cell = st.getCell(col, row);
        if (CellType.EMPTY == cell.getType()) {
          if (0 == col) {
            break;
          } else {
            value = null;
          }
        } else {
          value = cell.getContents();
          if (0 == col && value != null && value.trim().length() < 1) {
            break;
          }
        }
        this.setValueByJaveReflect(entity, columnNames[col], value);
      }

      if (0 != col) {
        System.out.println(entity);
        this.amazonJewelryDao.saveOrUpdate(entity);
      }
    }
    book.close();
  }
 /** NOTE: Copied from InvestigationExcelReader */
 private void writeSheetToFile(Sheet sheet, File file) throws FileNotFoundException {
   List<String> headers = new ArrayList<String>();
   Cell[] headerCells = sheet.getRow(0); // assume headers are on first
   // line
   ArrayList<Integer> namelessHeaderLocations = new ArrayList<Integer>(); // allow
   // for
   // empty
   // columns,
   // also
   // column
   // order
   // does
   // not
   // matter
   for (int i = 0; i < headerCells.length; i++) {
     if (!headerCells[i].getContents().equals("")) {
       headers.add(headerCells[i].getContents());
     } else {
       headers.add("nameless" + i);
       namelessHeaderLocations.add(i);
     }
   }
   PrintWriter pw = new PrintWriter(file);
   CsvWriter cw = new CsvWriter(pw, headers);
   cw.setMissingValue("");
   cw.writeHeader();
   for (int rowIndex = 1; rowIndex < sheet.getRows(); rowIndex++) {
     Tuple t = new SimpleTuple();
     int colIndex = 0;
     for (Cell c : sheet.getRow(rowIndex)) {
       if (!namelessHeaderLocations.contains(colIndex)) {
         t.set(headers.get(colIndex), c.getContents());
       }
       colIndex++;
     }
     cw.writeRow(t);
   }
   cw.close();
 }
Example #15
0
  /**
   * Récupère la liste des étudiants dans un xls
   *
   * @param uri : l'uri du fichier xls
   * @return la liste remplie
   * @author Foulon Alain
   */
  public static GestionEtudiants getValsFromXls(String uri) throws BiffException, IOException {
    GestionEtudiants liste = new GestionEtudiants();

    try {
      // Récupère le fichier
      File fich = new File(uri);
      WorkbookSettings workSet = new WorkbookSettings(); // config de fichier .xls
      workSet.setLocale(new Locale("fr", "FR")); // vars locales
      Workbook workbook =
          Workbook.getWorkbook(fich, workSet); // ouvre un fichier existant en fct des settings
      Sheet feuille = workbook.getSheet(0); // ouvre la première feuille du fichier

      Cell cellNom = feuille.findCell("NOM"); // trouve la cellule "NOM" dans la feuille
      // soit : REGEX sur "nom, Nom, noms, NOM, NOMS..."
      // soit : fct qui teste les possibilités; (note: la CELL est null si non trouvée)

      int colNom = cellNom.getColumn(); // colonne noms
      int colPrenom = colNom + 1; // colonne prénoms
      int firstRow = cellNom.getRow() + 1; // premiere ligne
      int lastRow = feuille.getRows(); // derniere ligne

      // boucle de remplissage
      for (int i = firstRow, j = 0; i < lastRow; i++) {
        // je récupère les infors
        String nom = feuille.getCell(colNom, i).getContents();
        String prenom = feuille.getCell(colPrenom, i).getContents();

        // j'ajoute l'étudiant dans la liste
        j++;
        liste.add(new Etudiant(j, nom, prenom));
      }
    } catch (IOException ioe) {
      System.out.println("Erreur : " + ioe); // Erreur lors de l'ouverture du fichier Excel
    }

    // je retourne la liste finie
    return liste;
  }
 private boolean writeSheetToFile(Sheet sheet, File file) throws FileNotFoundException {
   List<String> headers = new ArrayList<String>();
   Cell[] headerCells = sheet.getRow(0); // assume headers are on first line
   if (headerCells.length == 0) {
     return false;
   }
   ArrayList<Integer> namelessHeaderLocations =
       new ArrayList<Integer>(); // allow for empty columns, also column order does not matter
   for (int i = 0; i < headerCells.length; i++) {
     if (!headerCells[i].getContents().equals("")) {
       headers.add(headerCells[i].getContents());
     } else {
       headers.add("nameless" + i);
       namelessHeaderLocations.add(i);
     }
   }
   PrintWriter pw = new PrintWriter(file);
   CsvWriter cw = new CsvWriter(pw, headers);
   cw.setMissingValue("");
   cw.writeHeader();
   for (int rowIndex = 1; rowIndex < sheet.getRows(); rowIndex++) {
     Tuple t = new SimpleTuple();
     int colIndex = 0;
     for (Cell c : sheet.getRow(rowIndex)) {
       if (!namelessHeaderLocations.contains(colIndex)
           && colIndex < headers.size()
           && c.getContents() != null) {
         t.set(headers.get(colIndex), c.getContents());
       }
       colIndex++;
     }
     cw.writeRow(t);
   }
   cw.close();
   return true;
 }
Example #17
0
  public String[][] getTableArray() throws Exception {
    String[][] tabArray = null;

    Workbook workbook = Workbook.getWorkbook(new File(inputFile));
    Sheet sheet = workbook.getSheet(sheetName);
    int startRow, startCol, endRow, endCol, ci, cj;
    Cell tableStart = sheet.findCell(tableName);
    startRow = tableStart.getRow();
    startCol = tableStart.getColumn();
    Cell tableEnd = sheet.findCell(tableName, startCol + 1, startRow + 1, 400, 64000, false);
    // Cell tableEnd= sheet.findCell(tableName, startCol+1,startRow+1, 100, 64000,  false);

    endRow = tableEnd.getRow();
    endCol = tableEnd.getColumn();
    System.out.println(
        "startRow="
            + startRow
            + ", endRow="
            + endRow
            + ", "
            + "startCol="
            + startCol
            + ", endCol="
            + endCol);
    tabArray = new String[endRow - startRow - 1][endCol - startCol - 1];
    ci = 0;

    for (int i = startRow + 1; i < endRow; i++, ci++) {
      cj = 0;
      for (int j = startCol + 1; j < endCol; j++, cj++) {
        tabArray[ci][cj] = sheet.getCell(j, i).getContents();
      }
    }

    return (tabArray);
  }
  @Test
  public void setUp() throws Exception {
    try {

      // ---------------------Maximize Window----------------
      driver.manage().window().maximize();

      // ---------------------Open URL----------------

      System.setProperty(
          "webdriver.ie.driver", "C:\\Users\\nitish_ravindran\\Desktop\\IEDriverServer.exe");
      /*WebDriver driver;
      driver=new InternetExplorerDriver();*/
      driver.get("http://blrkec141608d:8080/SBCS-WEB/");

      // -------------------------Get Data from xls-----------------------------

      Workbook excel =
          Workbook.getWorkbook(new File("C:\\Users\\nitish_ravindran\\Desktop\\loginSBCS.xls"));
      Sheet sheet1 = excel.getSheet(0);

      for (int row = 0; row < sheet1.getRows(); row++) {
        for (int col = 0; col < sheet1.getColumns(); col++) {
          Cell u0 = sheet1.getCell(col, row);
          Cell p0 = sheet1.getCell(col, row);
          String usernameXL = u0.getContents();
          String passwordXL = p0.getContents();

          System.out.println(usernameXL);
          System.out.println(passwordXL);
        }
      }
      /*WebElement loginIdAfterInvalid=driver.findElement(By.cssSelector("input[name='j_username']"));
      WebElement passwordAfterInvalid=driver.findElement(By.cssSelector("input[type='password']"));
      WebElement loginButtonAfterInvalid=driver.findElement(By.cssSelector("input[type='submit'][value='Login']"));

      if(loginIdAfterInvalid.isEnabled() && passwordAfterInvalid.isEnabled())
      {
      loginIdAfterInvalid.clear();
      passwordAfterInvalid.clear();
      loginIdAfterInvalid.sendKeys(usernameXL);
      passwordAfterInvalid.sendKeys(passwordXL);
      loginButtonAfterInvalid.click();
      }
      else
      {
      	System.out.println("---------------Both text boxes are disabled----------");
      }


      //-------------------------Wait for new page to load-----------------------------
      //WebElement test=driver.findElement(By.cssSelector("a[href='/SBCS-WEB/AdvancedSearch.do?moduleSelected=Scenarios']"));
      (new WebDriverWait(driver,10)).until(new ExpectedCondition<Boolean>()
      	{
      		public Boolean apply(WebDriver d)
      			{
      				return d.findElement(By.xpath("//a[@href='/SBCS-WEB/AdvancedSearch.do?moduleSelected=Scenarios']")).getAttribute("title").contains("Advanced Search ");
      			}
      	});

      //-------------------------Logout-----------------------------
      WebElement logoutLinkImg=driver.findElement(By.cssSelector("a[href='/SBCS-WEB/logout.do']"));
      logoutLinkImg.click();*/

    } // -------------------------End of Try block-----------------------------

    // -------------------------Catch Blocks-----------------------------

    catch (NoSuchElementException e) {
      System.out.println("-----------Element not found " + e);
    }
    /*catch(AssertionFailedError e)
    {
    	System.out.println("------------Assertion failed " + e);
    }*/

    /*catch(StaleElementReferenceException e)
    {
    	System.out.println("-----------Stale Element" + e);
    }*/
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      // driver.quit();
    }
  }
Example #19
0
  /**
   * Check all the merged cells for borders. If the merge record has borders, then we need to rejig
   * the cell formats to take account of this. This is called by the write method of the
   * WritableWorkbookImpl, so that any new XFRecords that are created may be written out with the
   * others
   */
  void checkMergedBorders() {
    Range[] mcells = mergedCells.getMergedCells();
    ArrayList borderFormats = new ArrayList();
    for (int mci = 0; mci < mcells.length; mci++) {
      Range range = mcells[mci];
      Cell topLeft = range.getTopLeft();
      XFRecord tlformat = (XFRecord) topLeft.getCellFormat();

      if (tlformat != null && tlformat.hasBorders() == true && !tlformat.isRead()) {
        try {
          CellXFRecord cf1 = new CellXFRecord(tlformat);
          Cell bottomRight = range.getBottomRight();

          cf1.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
          cf1.setBorder(
              Border.LEFT,
              tlformat.getBorderLine(Border.LEFT),
              tlformat.getBorderColour(Border.LEFT));
          cf1.setBorder(
              Border.TOP, tlformat.getBorderLine(Border.TOP), tlformat.getBorderColour(Border.TOP));

          if (topLeft.getRow() == bottomRight.getRow()) {
            cf1.setBorder(
                Border.BOTTOM,
                tlformat.getBorderLine(Border.BOTTOM),
                tlformat.getBorderColour(Border.BOTTOM));
          }

          if (topLeft.getColumn() == bottomRight.getColumn()) {
            cf1.setBorder(
                Border.RIGHT,
                tlformat.getBorderLine(Border.RIGHT),
                tlformat.getBorderColour(Border.RIGHT));
          }

          int index = borderFormats.indexOf(cf1);
          if (index != -1) {
            cf1 = (CellXFRecord) borderFormats.get(index);
          } else {
            borderFormats.add(cf1);
          }
          ((WritableCell) topLeft).setCellFormat(cf1);

          // Handle the bottom left corner
          if (bottomRight.getRow() > topLeft.getRow()) {
            // Handle the corner cell
            if (bottomRight.getColumn() != topLeft.getColumn()) {
              CellXFRecord cf2 = new CellXFRecord(tlformat);
              cf2.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf2.setBorder(
                  Border.LEFT,
                  tlformat.getBorderLine(Border.LEFT),
                  tlformat.getBorderColour(Border.LEFT));
              cf2.setBorder(
                  Border.BOTTOM,
                  tlformat.getBorderLine(Border.BOTTOM),
                  tlformat.getBorderColour(Border.BOTTOM));

              index = borderFormats.indexOf(cf2);
              if (index != -1) {
                cf2 = (CellXFRecord) borderFormats.get(index);
              } else {
                borderFormats.add(cf2);
              }

              sheet.addCell(new Blank(topLeft.getColumn(), bottomRight.getRow(), cf2));
            }

            // Handle the cells down the left hand side (and along the
            // right too, if necessary)
            for (int i = topLeft.getRow() + 1; i < bottomRight.getRow(); i++) {
              CellXFRecord cf3 = new CellXFRecord(tlformat);
              cf3.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf3.setBorder(
                  Border.LEFT,
                  tlformat.getBorderLine(Border.LEFT),
                  tlformat.getBorderColour(Border.LEFT));

              if (topLeft.getColumn() == bottomRight.getColumn()) {
                cf3.setBorder(
                    Border.RIGHT,
                    tlformat.getBorderLine(Border.RIGHT),
                    tlformat.getBorderColour(Border.RIGHT));
              }

              index = borderFormats.indexOf(cf3);
              if (index != -1) {
                cf3 = (CellXFRecord) borderFormats.get(index);
              } else {
                borderFormats.add(cf3);
              }

              sheet.addCell(new Blank(topLeft.getColumn(), i, cf3));
            }
          }

          // Handle the top right corner
          if (bottomRight.getColumn() > topLeft.getColumn()) {
            if (bottomRight.getRow() != topLeft.getRow()) {
              // Handle the corner cell
              CellXFRecord cf6 = new CellXFRecord(tlformat);
              cf6.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf6.setBorder(
                  Border.RIGHT,
                  tlformat.getBorderLine(Border.RIGHT),
                  tlformat.getBorderColour(Border.RIGHT));
              cf6.setBorder(
                  Border.TOP,
                  tlformat.getBorderLine(Border.TOP),
                  tlformat.getBorderColour(Border.TOP));
              index = borderFormats.indexOf(cf6);
              if (index != -1) {
                cf6 = (CellXFRecord) borderFormats.get(index);
              } else {
                borderFormats.add(cf6);
              }

              sheet.addCell(new Blank(bottomRight.getColumn(), topLeft.getRow(), cf6));
            }

            // Handle the cells along the right
            for (int i = topLeft.getRow() + 1; i < bottomRight.getRow(); i++) {
              CellXFRecord cf7 = new CellXFRecord(tlformat);
              cf7.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf7.setBorder(
                  Border.RIGHT,
                  tlformat.getBorderLine(Border.RIGHT),
                  tlformat.getBorderColour(Border.RIGHT));

              index = borderFormats.indexOf(cf7);
              if (index != -1) {
                cf7 = (CellXFRecord) borderFormats.get(index);
              } else {
                borderFormats.add(cf7);
              }

              sheet.addCell(new Blank(bottomRight.getColumn(), i, cf7));
            }

            // Handle the cells along the top, and along the bottom too
            for (int i = topLeft.getColumn() + 1; i < bottomRight.getColumn(); i++) {
              CellXFRecord cf8 = new CellXFRecord(tlformat);
              cf8.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf8.setBorder(
                  Border.TOP,
                  tlformat.getBorderLine(Border.TOP),
                  tlformat.getBorderColour(Border.TOP));

              if (topLeft.getRow() == bottomRight.getRow()) {
                cf8.setBorder(
                    Border.BOTTOM,
                    tlformat.getBorderLine(Border.BOTTOM),
                    tlformat.getBorderColour(Border.BOTTOM));
              }

              index = borderFormats.indexOf(cf8);
              if (index != -1) {
                cf8 = (CellXFRecord) borderFormats.get(index);
              } else {
                borderFormats.add(cf8);
              }

              sheet.addCell(new Blank(i, topLeft.getRow(), cf8));
            }
          }

          // Handle the bottom right corner
          if (bottomRight.getColumn() > topLeft.getColumn()
              || bottomRight.getRow() > topLeft.getRow()) {
            // Handle the corner cell
            CellXFRecord cf4 = new CellXFRecord(tlformat);
            cf4.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
            cf4.setBorder(
                Border.RIGHT,
                tlformat.getBorderLine(Border.RIGHT),
                tlformat.getBorderColour(Border.RIGHT));
            cf4.setBorder(
                Border.BOTTOM,
                tlformat.getBorderLine(Border.BOTTOM),
                tlformat.getBorderColour(Border.BOTTOM));

            if (bottomRight.getRow() == topLeft.getRow()) {
              cf4.setBorder(
                  Border.TOP,
                  tlformat.getBorderLine(Border.TOP),
                  tlformat.getBorderColour(Border.TOP));
            }

            if (bottomRight.getColumn() == topLeft.getColumn()) {
              cf4.setBorder(
                  Border.LEFT,
                  tlformat.getBorderLine(Border.LEFT),
                  tlformat.getBorderColour(Border.LEFT));
            }

            index = borderFormats.indexOf(cf4);
            if (index != -1) {
              cf4 = (CellXFRecord) borderFormats.get(index);
            } else {
              borderFormats.add(cf4);
            }

            sheet.addCell(new Blank(bottomRight.getColumn(), bottomRight.getRow(), cf4));

            // Handle the cells along the bottom (and along the top
            // as well, if appropriate)
            for (int i = topLeft.getColumn() + 1; i < bottomRight.getColumn(); i++) {
              CellXFRecord cf5 = new CellXFRecord(tlformat);
              cf5.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf5.setBorder(
                  Border.BOTTOM,
                  tlformat.getBorderLine(Border.BOTTOM),
                  tlformat.getBorderColour(Border.BOTTOM));

              if (topLeft.getRow() == bottomRight.getRow()) {
                cf5.setBorder(
                    Border.TOP,
                    tlformat.getBorderLine(Border.TOP),
                    tlformat.getBorderColour(Border.TOP));
              }

              index = borderFormats.indexOf(cf5);
              if (index != -1) {
                cf5 = (CellXFRecord) borderFormats.get(index);
              } else {
                borderFormats.add(cf5);
              }

              sheet.addCell(new Blank(i, bottomRight.getRow(), cf5));
            }
          }
        } catch (WriteException e) {
          // just log e.toString(), not the whole stack trace
          logger.warn(e.toString());
        }
      }
    }
  }
  /**
   * DOC xqliu Comment method "importToStucture".
   *
   * @param importObject
   * @param selectionFolder
   * @param type
   * @param skip
   * @param rename
   * @param importItemName
   * @return
   */
  public static List<ReturnCode> importToStucture(
      ImportObject importObject,
      IFolder selectionFolder,
      ExpressionType type,
      boolean skip,
      boolean rename,
      String importItemName) {

    List<ReturnCode> importEvent = new ArrayList<ReturnCode>();

    // MOD qiongli 2012-12-20 TDQ-5899(issue 2),should get all patterns from Pattern folder.
    Set<String> names =
        PatternUtilities.getNestFolderPatternNames(
            new HashSet<String>(), ResourceManager.getPatternFolder());

    File importFile = importObject.getObjFile();

    String fileExtName = getFileExtName(importFile);

    if ("csv".equalsIgnoreCase(fileExtName)) { // $NON-NLS-1$
      try {
        CsvReader reader = new CsvReader(new FileReader(importFile), CURRENT_SEPARATOR);
        reader.setEscapeMode(CsvReader.ESCAPE_MODE_DOUBLED);
        reader.setTextQualifier(TEXT_QUAL);
        reader.setUseTextQualifier(USE_TEXT_QUAL);

        reader.readHeaders();
        while (reader.readRecord()) {

          String name = reader.get(PatternToExcelEnum.Label.getLiteral());

          if (names.contains(name)) {
            if (skip) {
              importEvent.add(
                  new ReturnCode(
                      DefaultMessagesImpl.getString("ImportFactory.patternInported", name),
                      false)); //$NON-NLS-1$
              continue;
            }
            if (rename) {
              name = name + "(" + new Date() + ")"; // $NON-NLS-1$ //$NON-NLS-2$
            }
          }

          PatternParameters patternParameters = new ImportFactory().new PatternParameters();
          patternParameters.name = name;
          patternParameters.auther = reader.get(PatternToExcelEnum.Author.getLiteral());
          patternParameters.description = reader.get(PatternToExcelEnum.Description.getLiteral());
          patternParameters.purpose = reader.get(PatternToExcelEnum.Purpose.getLiteral());
          patternParameters.relativePath = reader.get(PatternToExcelEnum.RelativePath.getLiteral());

          for (PatternLanguageType languagetype : PatternLanguageType.values()) {
            String cellStr = reader.get(languagetype.getExcelEnum().getLiteral());
            if (cellStr != null && !cellStr.equals("")) { // $NON-NLS-1$
              patternParameters.regex.put(languagetype.getLiteral(), cellStr);
            }
          }

          try {
            TypedReturnCode<Object> create =
                createAndStorePattern(patternParameters, selectionFolder, type);
            if (create.isOk()) {
              names.add(name);

              importEvent.add(
                  new ReturnCode(
                      DefaultMessagesImpl.getString(
                          "ImportFactory.importPattern",
                          name, //$NON-NLS-1$
                          selectionFolder.getProjectRelativePath().toString()),
                      true));
            } else {
              throw new TalendInternalPersistenceException(create.getMessage());
            }

          } catch (Exception e) {
            importEvent.add(
                new ReturnCode(
                    DefaultMessagesImpl.getString("ImportFactory.SaveFailed", name),
                    false)); //$NON-NLS-1$
          }
        }
        reader.close();
      } catch (Exception e) {
        log.error(e, e);
        importEvent.add(
            new ReturnCode(
                DefaultMessagesImpl.getString("ImportFactory.importFailed"), false)); // $NON-NLS-1$
      }
    }

    if ("xls".equalsIgnoreCase(fileExtName)) { // $NON-NLS-1$
      Map<Integer, PatternLanguageType> expressionMap = new HashMap<Integer, PatternLanguageType>();
      try {
        WorkbookSettings settings = new WorkbookSettings();
        settings.setEncoding("UTF-8"); // $NON-NLS-1$
        Workbook rwb = Workbook.getWorkbook(importFile, settings);
        Sheet[] sheets = rwb.getSheets();
        for (Sheet sheet : sheets) {
          Cell[] headerRow = sheet.getRow(0);

          for (Cell cell : headerRow) {
            for (PatternLanguageType languageType : PatternLanguageType.values()) {
              if (cell.getContents().equals(languageType.getExcelEnum().getLiteral())) {
                expressionMap.put(cell.getColumn(), languageType);
              }
            }
          }

          for (int i = 1; i < sheet.getRows(); i++) {
            Cell[] row = sheet.getRow(i);
            Cell cell = row[0];
            if (CellType.LABEL.equals(cell.getType())) {
              String contents = cell.getContents();
              if (names.contains(contents)) {
                if (skip) {
                  importEvent.add(
                      new ReturnCode(
                          DefaultMessagesImpl.getString(
                              "ImportFactory.patternInported", //$NON-NLS-1$
                              contents),
                          false));
                  continue;
                }
                if (rename) {
                  contents = contents + "(" + new Date() + ")"; // $NON-NLS-1$ //$NON-NLS-2$
                }
              }

              PatternParameters patternParameters = new ImportFactory().new PatternParameters();

              patternParameters.name = contents;
              patternParameters.auther = row[6].getContents();
              patternParameters.description = row[2].getContents();
              patternParameters.purpose = row[1].getContents();
              patternParameters.status = DevelopmentStatus.DRAFT.getLiteral();

              for (int columnIndex : expressionMap.keySet()) {
                String rowContent = row[columnIndex].getContents();
                if (!rowContent.equals("")) { // $NON-NLS-1$
                  patternParameters.regex.put(
                      expressionMap.get(columnIndex).getLiteral(), rowContent);
                }
              }

              try {
                TypedReturnCode<Object> create =
                    createAndStorePattern(patternParameters, selectionFolder, type);
                if (create.isOk()) {
                  names.add(contents);

                  importEvent.add(
                      new ReturnCode(
                          DefaultMessagesImpl.getString(
                              "ImportFactory.importPattern",
                              ((TDQItem) create.getObject())
                                  .getProperty()
                                  .getDisplayName(), //$NON-NLS-1$
                              selectionFolder.getProjectRelativePath().toString()),
                          true));
                } else {
                  throw new TalendInternalPersistenceException(create.getMessage());
                }

              } catch (Exception e) {
                importEvent.add(
                    new ReturnCode(
                        DefaultMessagesImpl.getString("ImportFactory.SaveFailed", contents),
                        false)); //$NON-NLS-1$
              }
            }
          }
        }

        rwb.close();
      } catch (BiffException e) {
        log.error(e, e);
        importEvent.add(
            new ReturnCode(
                DefaultMessagesImpl.getString("ImportFactory.importFailed"), false)); // $NON-NLS-1$
      } catch (IOException e) {
        log.error(e, e);
        importEvent.add(
            new ReturnCode(
                DefaultMessagesImpl.getString("ImportFactory.importFailed"), false)); // $NON-NLS-1$
      }
    }

    importObject.copyJarFiles();

    // ADD xqliu 2012-04-27 TDQ-5149
    checkImportEvent(importItemName, importEvent);
    // ~ TDQ-5149
    return importEvent;
  }
  /**
   * DOC xqliu Comment method "importIndicatorToStucture".
   *
   * @param importObject
   * @param selectionFolder
   * @param skip
   * @param rename
   * @param importItemName
   * @return
   */
  public static List<ReturnCode> importIndicatorToStucture(
      ImportObject importObject,
      IFolder selectionFolder,
      boolean skip,
      boolean rename,
      String importItemName) {

    List<ReturnCode> information = new ArrayList<ReturnCode>();

    Set<String> names = UDIHelper.getAllIndicatorNames(selectionFolder);

    File importFile = importObject.getObjFile();

    String fileExtName = getFileExtName(importFile);

    if ("csv".equalsIgnoreCase(fileExtName)) { // $NON-NLS-1$
      String name = PluginConstant.EMPTY_STRING;
      try {
        CsvReader reader = new CsvReader(new FileReader(importFile), CURRENT_SEPARATOR);
        // MOD zshen EscapeMode default is CsvReader.ESCAPE_MODE_DOUBLED
        reader.setTextQualifier(TEXT_QUAL);
        reader.setUseTextQualifier(USE_TEXT_QUAL);
        reader.readHeaders();

        java.text.SimpleDateFormat simpleDateFormat =
            new java.text.SimpleDateFormat("yyyyMMddHHmmssSSS"); // $NON-NLS-1$

        while (reader.readRecord()) {
          name = reader.get(PatternToExcelEnum.Label.getLiteral());

          if (names.contains(name)) {
            if (skip) {
              information.add(
                  new ReturnCode(
                      DefaultMessagesImpl.getString("ImportFactory.Imported", name),
                      false)); //$NON-NLS-1$
              continue;
            }
            if (rename) {
              name =
                  name
                      + "("
                      + simpleDateFormat.format(new Date())
                      + Math.random()
                      + ")"; //$NON-NLS-1$ //$NON-NLS-2$
            }
          }

          UDIParameters udiParameters = new ImportFactory().new UDIParameters();
          udiParameters.name = name;
          udiParameters.auther = reader.get(PatternToExcelEnum.Author.getLiteral());
          udiParameters.description = reader.get(PatternToExcelEnum.Description.getLiteral());
          udiParameters.purpose = reader.get(PatternToExcelEnum.Purpose.getLiteral());
          udiParameters.relativePath = reader.get(PatternToExcelEnum.RelativePath.getLiteral());
          udiParameters.category = reader.get(PatternToExcelEnum.Category.getLiteral());
          udiParameters.javaClassName = reader.get(PatternToExcelEnum.JavaClassName.getLiteral());
          udiParameters.javaJarPath = reader.get(PatternToExcelEnum.JavaJarPath.getLiteral());
          String[] headers = reader.getHeaders();
          String[] columnsValue = reader.getValues();
          HashMap<String, String> record = new HashMap<String, String>();
          for (int i = 0; i < headers.length; i++) {
            if (columnsValue[i] != null && columnsValue[i].length() > 0) {
              record.put(headers[i], columnsValue[i]);
            }
          }
          for (PatternLanguageType languagetype : PatternLanguageType.values()) {
            String cellStr = record.get(languagetype.getExcelEnum().getLiteral());
            if (cellStr != null && !cellStr.equals("\"\"")) { // $NON-NLS-1$
              udiParameters.regex.put(languagetype.getLiteral(), trimQuote(cellStr));
            }
          }

          udiParameters.setParaMap(buildIndDefPara(record));
          TypedReturnCode<Object> create = createAndStoreUDI(udiParameters, selectionFolder);
          if (create.isOk()) {
            names.add(name);

            // add the suscess message to display.
            information.add(
                new ReturnCode(
                    DefaultMessagesImpl.getString(
                        "ImportFactory.importedSucess" //$NON-NLS-1$
                        ,
                        ((TDQItem) create.getObject()).getProperty().getDisplayName(),
                        selectionFolder.getProjectRelativePath().toString()),
                    true));
          } else {
            throw new TalendInternalPersistenceException(create.getMessage());
          }
        }

        reader.close();
      } catch (Exception e) {
        log.error(e, e);
        information.add(
            new ReturnCode(
                DefaultMessagesImpl.getString("ImportFactory.importedFailed", name),
                false)); //$NON-NLS-1$
      }
    }

    if ("xls".equalsIgnoreCase(fileExtName)) { // $NON-NLS-1$
      Map<Integer, PatternLanguageType> expressionMap = new HashMap<Integer, PatternLanguageType>();
      String contents = PluginConstant.EMPTY_STRING;
      try {
        WorkbookSettings settings = new WorkbookSettings();
        settings.setEncoding("UTF-8"); // $NON-NLS-1$
        Workbook rwb = Workbook.getWorkbook(importFile, settings);
        Sheet[] sheets = rwb.getSheets();
        for (Sheet sheet : sheets) {
          Cell[] headerRow = sheet.getRow(0);

          for (Cell cell : headerRow) {
            for (PatternLanguageType languageType : PatternLanguageType.values()) {
              if (cell.getContents().equals(languageType.getExcelEnum().getLiteral())) {
                expressionMap.put(cell.getColumn(), languageType);
              }
            }
          }

          for (int i = 1; i < sheet.getRows(); i++) {
            Cell[] row = sheet.getRow(i);
            Cell cell = row[0];
            if (CellType.LABEL.equals(cell.getType())) {
              contents = cell.getContents();
              if (names.contains(contents)) {
                if (skip) {
                  continue;
                }
                if (rename) {
                  contents = contents + "(" + new Date() + ")"; // $NON-NLS-1$ //$NON-NLS-2$
                }
              }

              UDIParameters udiParameters = new ImportFactory().new UDIParameters();

              udiParameters.name = contents;
              udiParameters.auther = row[6].getContents();
              udiParameters.description = row[2].getContents();
              udiParameters.purpose = row[1].getContents();
              udiParameters.status = DevelopmentStatus.DRAFT.getLiteral();
              udiParameters.category = row[16].getContents();

              for (int columnIndex : expressionMap.keySet()) {
                String rowContent = row[columnIndex].getContents();
                if (!rowContent.equals("")) { // $NON-NLS-1$
                  udiParameters.regex.put(expressionMap.get(columnIndex).getLiteral(), rowContent);
                }
              }

              createAndStoreUDI(udiParameters, selectionFolder);

              names.add(contents);

              information.add(
                  new ReturnCode(
                      DefaultMessagesImpl.getString(
                          "ImportFactory.importedSucess" //$NON-NLS-1$
                          ,
                          contents),
                      true));
            }
          }
        }

        rwb.close();
      } catch (Exception e) {
        log.error(e, e);
        information.add(
            new ReturnCode(
                DefaultMessagesImpl.getString("ImportFactory.importedFailed", contents),
                false)); //$NON-NLS-1$
      }
    }

    // MOD qiongli 2011-11-28 TDQ-4038.consider to import the definition file.
    if (FactoriesUtil.DEFINITION.equalsIgnoreCase(fileExtName)) {
      String propFilePath =
          importFile
              .getPath()
              .replaceFirst(
                  PluginConstant.DOT_STRING + fileExtName,
                  PluginConstant.DOT_STRING + FactoriesUtil.PROPERTIES_EXTENSION);
      File propFile = new File(propFilePath);
      // just import the definition file which have the realted Property file.
      if (!propFile.exists()) {
        return information;
      }
      String name = importFile.getName();
      try {
        if (names.contains(name)) {
          if (skip) {
            information.add(
                new ReturnCode(
                    DefaultMessagesImpl.getString("ImportFactory.Imported", name),
                    false)); //$NON-NLS-1$
            return information;
          }
          if (rename) {
            name = name + "(" + new Date() + Math.random() + ")"; // $NON-NLS-1$ //$NON-NLS-2$
          }
        }

        IFile elementFile = selectionFolder.getFile(name);
        if (!elementFile.exists()) {
          elementFile.create(new FileInputStream(importFile), false, null);
          ModelElement modelElement = ModelElementFileFactory.getModelElement(elementFile);
          if (modelElement != null) {
            ElementWriterFactory.getInstance()
                .createIndicatorDefinitionWriter()
                .create(modelElement, selectionFolder);
            DefinitionHandler.getInstance().reloadIndicatorsDefinitions();
            names.add(name);
            information.add(
                new ReturnCode(
                    DefaultMessagesImpl.getString(
                        "ImportFactory.importedSucess" //$NON-NLS-1$
                        ,
                        name),
                    true));
          }
        }
      } catch (Exception e) {
        log.error(e);
        information.add(
            new ReturnCode(
                DefaultMessagesImpl.getString("ImportFactory.importedFailed", name),
                false)); //$NON-NLS-1$
      }
    }

    importObject.copyJarFiles();

    // ADD xqliu 2012-04-27 TDQ-5149
    checkImportEvent(importItemName, information);
    // ~ TDQ-5149
    return information;
  }
 private boolean isCellVoid(Cell cell) {
   if (cell == null || cell.equals("") || cell.getType().equals(CellType.EMPTY)) return true;
   else return false;
 }
  public int ImportDefalutXls(Context mContext) {
    try {
      String xlsType = "";
      InputStream is = null;

      String downloadXlsPath = getUpdateXlsPath(mContext) + File.separator + "address.xls";
      File downloadXlsFile = new File(downloadXlsPath);
      if (downloadXlsFile.exists()) {
        LogUtils.i(XlsUtils.class.getName(), "downloadXlsPath:" + downloadXlsPath);
        is = new FileInputStream(downloadXlsFile);
        xlsType = "xls";
      } else {
        AssetManager assets = mContext.getAssets();
        String[] files = assets.list("");

        for (String fileName : files) {
          if (fileName.equals(ConfigUtils.DEFAULT_XLS_NAME)) {
            xlsType = "xls";
            is = assets.open(fileName);
            LogUtils.i(XlsUtils.class.getName(), "assets file");
            break;
          } else if (fileName.equals(ConfigUtils.DEFAULT_XLSX_NAME)) {
            xlsType = "xlsx";
            is = assets.open(fileName);
            break;
          } else {
            return -1;
          }
        }
      }

      if (xlsType.equals("xls")) {
        Workbook wb = null;
        wb = Workbook.getWorkbook(is);
        Sheet sht =
            wb.getSheet(XResources.getString(mContext, ConfigUtils.SHEET_NAME)); // name or number

        int coloums = sht.getColumns();
        int rows = sht.getRows();

        Cell currentCell = null;
        String addressname = XResources.getString(mContext, ConfigUtils.ADDRESS_USERNAME);
        String jobname = XResources.getString(mContext, ConfigUtils.ADDRESS_JOB);
        String mobilephonename = XResources.getString(mContext, ConfigUtils.ADDRESS_MOBILEPHONE);
        String workphonename = XResources.getString(mContext, ConfigUtils.ADDRESS_WORKPHONE);

        LogUtils.i(XlsUtils.class.getName(), "************************************");
        LogUtils.i(XlsUtils.class.getName(), String.format("row[%d],columns[%d]", rows, coloums));

        // delete address db
        DBUtils.getInstances().deleteAll(mContext, AddressModel.TableName);

        for (int i = 1; i < rows; i++) {
          LogUtils.i(XlsUtils.class.getName(), "####################");
          ContactsBean tmp = new ContactsBean();
          for (int j = 0; j < coloums; j++) {
            Cell topCell = sht.getCell(j, 0);
            String topName = topCell.getContents();
            currentCell = sht.getCell(j, i);
            //                        LogUtils.i(XlsUtils.class.getName(),topName);
            if (topName.equals(addressname)) {
              tmp.setName(currentCell.getContents());
              LogUtils.i(
                  XlsUtils.class.getName(), String.format("%s:%s", addressname, tmp.getName()));
            } else if (topName.equals(jobname)) {
              tmp.setJob(currentCell.getContents());
              LogUtils.i(XlsUtils.class.getName(), String.format("%s:%s", jobname, tmp.getJob()));
            } else if (topName.equals(mobilephonename)) {
              tmp.setMobilephone(currentCell.getContents());
              LogUtils.i(
                  XlsUtils.class.getName(),
                  String.format("%s:%s", mobilephonename, tmp.getMobilephone()));
            } else if (topName.equals(workphonename)) {
              tmp.setWorkphone(currentCell.getContents());
              LogUtils.i(
                  XlsUtils.class.getName(),
                  String.format("%s:%s", workphonename, tmp.getWorkphone()));
            } else {
            }
          }

          // insert to db

          DBUtils.getInstances().InsertAddress(mContext, tmp);

          LogUtils.i(XlsUtils.class.getName(), "####################");
        }
        LogUtils.i(XlsUtils.class.getName(), "************************************");

      } else {
        return -2;
      }

      return 1;
    } catch (Exception e) {
      LogUtils.error(e);
    }

    return 0;
  }
  public static void main(String[] args) throws Exception {
    System.out.println(
        "====================================学术论文Excel整理begin================================================");

    System.out.println("========================获取文件夹中的文件begin============");
    String dirPath = "E:/wuyu/学术论文";
    ReadDirFiles readDirFiles = new ReadDirFiles();
    ArrayList filePathList = readDirFiles.getFileList(dirPath);
    System.out.println(
        "========================获取文件夹中的文件end============文件个数:" + filePathList.size());

    // 写入的Excel
    WritableWorkbook book = null;
    WritableSheet sheet = null;
    String path = "E:\\wuyu\\学术论文2011.xls";
    book = Workbook.createWorkbook(new File(path));
    sheet = book.createSheet("KYLW", 0);
    int rowIndex = 1;

    String filePath = "";
    String filePath2 = "";
    for (int filePathIndex = 0; filePathIndex < filePathList.size(); filePathIndex++) {
      filePath = (String) filePathList.get(filePathIndex);
      filePath2 = filePath.replaceAll("\\\\", "\\/");

      InputStream is = new FileInputStream(filePath2); // File is = new File("D:/test2011.xls");
      Workbook rwb = Workbook.getWorkbook(is);
      Sheet rs = (Sheet) rwb.getSheet(0);
      int columnNum = rs.getColumns();
      int rowNum = rs.getRows();
      System.out.println(
          "第" + filePathIndex + "个 Excel:" + filePath2 + ".中行列为:" + rowNum + ", " + columnNum);
      // columnNum = 45;  //学术论文中的特定值

      Cell cell = null;
      String contentTemp = "";
      for (int i = 6; i <= rowNum - 1; i++) {
        if (rs.getCell(19, i).getContents().trim().equals("2011")
            || rs.getCell(19, i).getContents().trim().equals("2011年")
            || (rs.getCell(19, i).getContents().length() >= 4
                && rs.getCell(19, i).getContents().substring(0, 3).equals("2011"))) {
          for (int j = 0; j <= columnNum - 1; j++) {
            cell = rs.getCell(j, i);
            contentTemp = cell.getContents();
            if (j == 0 && contentTemp.equals("") && contentTemp.trim().equals("")) {
              break; // 如果第一列数据为空,说明数据不存在。“论文”中名称是必须的
            }
            //					System.out.print(j+","+i+":"+contentTemp+" ");

            // 写入Excel
            Label label = new Label(j, rowIndex, contentTemp);
            sheet.addCell(label);
          }
          rowIndex++;
        }
      }
      rwb.close();
    }

    book.write();
    book.close();
    System.out.println("学术论文写入结束");
  }
  /** 解析碎片与分面之间的关系,填写assemble表(表格变化) (针对二叉树Binary_tree) */
  public void assemble(String keyword) throws Exception {
    try {
      // 解析数据,数据加到数据库里面
      String catalog = KeywordCatalogDesign.GetKeywordCatalog(keyword);
      String path = catalog + keyword + "-tag_changed.xls";
      File file = new File(path);
      if (!file.exists()) {
        System.out.println(path + "  不存在,请重新生成解析表格...");
      } else {
        System.out.println("\n开始对应: " + path);
        Workbook book = Workbook.getWorkbook(file);
        Sheet sheet = book.getSheet(0);
        int row = sheet.getRows();
        String[] facetname = {
          "definition",
          "feature",
          "implementation",
          "example",
          "operation",
          "application",
          "method",
          "type",
          "relevant",
          "history",
          "description",
          "purpose",
          "explanation",
          "storage",
          "simulator"
        };
        for (int i = 0; i < row; i++) {
          Cell cell0 = sheet.getCell(0, i);
          Cell cell12 = sheet.getCell(12, i);
          if (cell12.getContents().equals("1")) {
            String fragmentid = cell0.getContents().replaceAll("\\+", "\\_");
            int facetid;
            Cell cell13 = sheet.getCell(13, i);
            Cell cell14 = sheet.getCell(14, i);
            Cell cell15 = sheet.getCell(15, i);
            Cell cell16 = sheet.getCell(16, i);
            String[] tag = {
              cell14.getContents(), cell15.getContents(), cell16.getContents(), cell13.getContents()
            };
            for (int m = 0; m < tag.length; m++) {
              for (int n = 0; n < facetname.length; n++) {
                if (tag[m].equals(facetname[n])) {
                  facetid = n + 1;

                  sql = "replace into assemble values (?, ?, ?, ?, ?)";
                  Object[] answerobject = new Object[] {52, facetid, 1, fragmentid, " "};
                  mysqlCon.doSql(sql, answerobject);
                  //									i = mysqlCon.getUpdateCount();
                  //									if (i != -1) {
                  //										System.out.println("数据插入成功!");
                  //									} else {
                  //										System.out.println("数据插入失败!");
                  //									}
                  // 关闭链接
                  mysqlCon.getClose();

                  System.out.println(fragmentid + "   " + facetid + "  52");
                }
              }
            }
          }
        }
      }
    } catch (Exception ex) {
      System.out.println("Error : " + ex.toString());
    }
  }
 /**
  * 获取数据表中某个属性的信息增益
  *
  * @param pt 需要计算信息增益的属性
  * @param t 判断属性,也即属性pt分类时的依据属性。
  * @return 属性pt的信息增益
  */
 public String[] getInformationGain(String pt, String t) {
   String[] gain_and_support = new String[3];
   String gain = null;
   int cla = 0; // 代表分类数目
   BufferedWriter writer = null;
   int part = 0;
   int target = 0;
   String[] attr = PublicData.getAttr();
   for (int p = 0; p < attr.length; p++) {
     if (attr[p].equals(pt)) {
       part = p;
       System.out.println("分类属性是:" + attr[p]);
     }
     if (attr[p].equals(t)) {
       target = p;
       System.out.println("所选属性是:" + attr[p]);
     }
   }
   /** 所有参与计算的变量必须都放到方法内部来,避免下次计算时重复 */
   float yes_to_attr = 0; // 属于当前类的数目
   float no_to_attr = 0;
   BigDecimal info = new BigDecimal(0); // 计算元组分类所需的期望信息
   BigDecimal infoD = new BigDecimal(0); // 存储当前列属性的信息增益
   float sum = 0; // 存储总行数
   java.text.DecimalFormat DataFormat =
       (java.text.DecimalFormat) java.text.DecimalFormat.getInstance();
   // 为了得到整数相处保留三位小数的结果。注意,经过这样的处理后,计算得到的结果是String类型
   DataFormat.applyPattern("##.###"); // 为了得到整数相处保留三位小数的结果
   value = new int[50][2];
   // 以下是用来读取Excel文件中的单元值
   try {
     Workbook book = Workbook.getWorkbook(new File(PublicData.getFile()));
     Sheet sheet = book.getSheet(0);
     for (int i = 1; i < sheet.getRows(); i++) // 默认第一行为属性辨别行,不参与计算
     {
       Cell cell = sheet.getCell(target, i); // 获取当前属性所在列
       Cell cell2 = sheet.getCell(part, i); // 获取结果列,即训练元组最终分类类别那一列
       String c2 = cell2.getContents().replaceAll("\"", "").trim();
       String result = cell.getContents().replaceAll("\"", " ").trim();
       if (list.contains(result) == false && result != null) // 将所选属性加入到list中
       {
         list.add(result);
         cla = list.indexOf(result);
       } else if (list.contains(result) == true)
         cla = list.indexOf(result); // 以当前属性的类别(String类型)所在list中的索引为value的第一维
       if (c2.equals("y") || c2.equals("yes")) // 以二维数组value的第二维的第一列来存储属于最终类的总数
       {
         value[cla][0]++;
         yes_to_attr++; // 全部数据中符合条件占比
       } else // if(c2.equals("n")||c2.equals("no"))
       {
         value[cla][1]++; // 第二列来存储不属于最终列的总数
         no_to_attr++; // 全部数据中不符合数量
       }
     }
     // writer.close();
     book.close();
   } catch (Exception e) {
     e.printStackTrace();
   }
   // 以下是用来计算信息增益的程序段
   System.out.println("属性长度" + list.size());
   sum = yes_to_attr + no_to_attr;
   BigDecimal ysum = mlog.getDivide(yes_to_attr, sum);
   BigDecimal nsum = mlog.getDivide(no_to_attr, sum);
   info = (nsum.multiply(mlog.getLog(nsum))).abs().add((ysum.multiply(mlog.getLog(ysum))).abs());
   String[] at = new String[list.size()];
   System.out.println("属性长度-------------" + list.size());
   for (int p = 0; p < list.size(); p++) {
     if (!(list.get(p).equals("") || list.get(p).equals(null))) {
       at[p] = list.get(p);
     }
     int a = value[p][0];
     int b = value[p][1];
     System.out.println("|" + list.get(p) + "| " + a + " | " + b + " |");
     int c = a + b;
     BigDecimal asum = mlog.getDivide(a, c);
     BigDecimal bsum = mlog.getDivide(b, c); // asum是肯定总量,bsum是否定总量
     BigDecimal csum = mlog.getDivide(c, sum);
     if (a == 0 && b != 0)
       infoD = infoD.add((csum.multiply(bsum.multiply(mlog.getLog(bsum)))).abs());
     else if (b == 0 && a != 0)
       infoD = infoD.add((csum.multiply(asum.multiply(mlog.getLog(asum)))).abs());
     else if (a != 0 && b != 0)
       infoD =
           infoD.add(
               ((csum.multiply(asum.multiply(mlog.getLog(asum)))).abs())
                   .add((csum.multiply(bsum.multiply(mlog.getLog(bsum)))).abs()));
     else if (a == 0 && b == 0) infoD = infoD.add(new BigDecimal(0));
   }
   for (int i = 0; i < at.length; i++) {
     if (i == 0 && at[i] != null) {
       attr_part = at[i];
     } else {
       if (at[i] != null) {
         attr_part = attr_part + "," + at[i];
       }
     }
   }
   System.out.println("attr_part长度是:" + attr_part.length());
   try {
     String f = HEAD + t + LAST;
     System.out.println("正在尝试写入文件-------" + f);
     BufferedWriter w = new BufferedWriter(new FileWriter(new File(f)));
     w.append(attr_part.trim()); // 去掉双引号
     w.flush();
     w.close();
   } catch (IOException e) {
     e.printStackTrace();
   }
   attr_part = "";
   gain = info.subtract(infoD).toString();
   System.out.println("当前属性信息增益:" + infoD);
   list.clear();
   // 清空list表,以便下次存储时不会由于前面的插入元素受干扰
   gain_and_support[0] = gain;
   gain_and_support[1] = yes_to_attr + "";
   gain_and_support[2] = no_to_attr - 1 + "";
   return gain_and_support;
 }
Example #27
0
  /**
   * @param is 要导入Excel的输入流
   * @param sheetName 导入的工作表名称
   * @param entityClass List中对象的类型(Excel中的每一行都要转化为该类型的对象)
   * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 例:{id=编号}
   * @param uniqueFields 指定业务主键组合(即复合主键),这些列的组合不能重复
   * @return List
   * @throws ExcelException @Description 将Excel转化成实体对象List
   */
  public static <T> List<T> excelToList(
      InputStream is,
      String sheetName,
      Class<T> entityClass,
      LinkedHashMap<String, String> fieldMap,
      String[] uniqueFields)
      throws ExcelException {
    // 定义要返回的list
    List<T> resultList = new ArrayList<T>();

    try {
      // 根据excel数据源创建WorkBook
      Workbook wb = Workbook.getWorkbook(is);

      // 获取工作表
      Sheet sheet = wb.getSheet(sheetName);

      // 获取工作表的有效行数
      int realRows = 0;
      for (int i = 0; i < sheet.getRows(); i++) {

        int nullCols = 0;
        for (int j = 0; j < sheet.getColumns(); j++) {
          Cell CurrentCell = sheet.getCell(j, i);
          if (CurrentCell == null || "".equals(CurrentCell.getContents().toString())) {
            nullCols++;
          }
        }

        if (nullCols == sheet.getColumns()) {
          break;
        } else {
          realRows++;
        }
      }

      // 如果Excel中没有任何数据则提示错误信息
      if (realRows <= 1) {
        throw new ExcelException("Excel文件中没有任何数据");
      }

      Cell[] firstRow = sheet.getRow(0);
      String[] excelFieldNames = new String[firstRow.length];
      // 获取Excel的列名
      for (int i = 0; i < firstRow.length; i++) {
        excelFieldNames[i] = firstRow[i].getContents().toString().trim();
      }
      // 判断需要的字段在Excel中是否都存在
      boolean isExist = true;
      List<String> excelFieldList = Arrays.asList(excelFieldNames);
      for (String cnName : fieldMap.values()) {
        if (!excelFieldList.contains(cnName)) {
          isExist = false;
          break;
        }
      }

      // 如果有列名不存在或不匹配,则抛出异常并提示错误
      if (!isExist) {
        throw new ExcelException("Excel中缺少必要的字段,或字段名称有误");
      }

      // 将列名和列号放入Map中,这样通过列名就可以拿到列号
      LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
      for (int i = 0; i < excelFieldNames.length; i++) {
        colMap.put(excelFieldNames[i], firstRow[i].getColumn());
      }

      // 判断是否有重复行
      // 1.获取uniqueFields指定的列
      Cell[][] uniqueCells = new Cell[uniqueFields.length][];
      for (int i = 0; i < uniqueFields.length; i++) {
        int col = colMap.get(uniqueFields[i]);
        uniqueCells[i] = sheet.getColumn(col);
      }
      // 2.从指定列中寻找重复行
      for (int i = 1; i < realRows; i++) {
        int nullCols = 0;
        int length = uniqueFields.length;
        for (int j = 0; j < length; j++) {
          Cell currentCell = uniqueCells[j][i];
          String currentContent = currentCell.getContents().toString().trim();
          Cell sameCell =
              sheet.findCell(
                  currentContent,
                  currentCell.getColumn(),
                  currentCell.getRow() + 1,
                  currentCell.getColumn(),
                  uniqueCells[j][realRows - 1].getRow(),
                  true);
          if (sameCell != null) {
            nullCols++;
          }
        }
        // 复合主键,意味着这些列的组合不能重复,
        // 只有当所有的列都有重复的时候,才被认为是有重复行
        if (nullCols == length) {
          throw new Exception("Excel中有重复行,请检查");
        }
      }

      // 将sheet转换为list
      for (int i = 1; i < realRows; i++) {
        // 新建要转换的对象
        T entity = entityClass.newInstance();

        // 给对象中的字段赋值
        for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
          // 获取英文字段名
          String enNormalName = entry.getKey();
          // 获取中文字段名
          String cnNormalName = entry.getValue();
          // 根据中文字段名获取列号
          int col = colMap.get(cnNormalName);

          // 获取当前单元格中的内容
          String content = sheet.getCell(col, i).getContents().toString().trim();

          // 给对象赋值
          setFieldValueByName(enNormalName, content, entity);
        }

        resultList.add(entity);
      }
    } catch (Exception e) {
      e.printStackTrace();
      // 如果是ExcelException,则直接抛出
      if (e instanceof ExcelException) {
        throw (ExcelException) e;
      } else {
        // 否则将其包装成ExcelException,再将其抛出
        throw new ExcelException("导入ExceL失败");
      }
    }

    return resultList;
  }