/** * 取得公式内容 * * @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; }