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