示例#1
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");
   }
 }
 /**
  * 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();
   }
 }
 /**
  * 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;
 }
  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();
  }
 private boolean isCellVoid(Cell cell) {
   if (cell == null || cell.equals("") || cell.getType().equals(CellType.EMPTY)) return true;
   else return false;
 }
  /**
   * 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;
  }
  /**
   * 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;
  }