List of usage examples for org.apache.poi.ss.usermodel Row getCell
Cell getCell(int cellnum, MissingCellPolicy policy);
From source file:com.elecnor.ecosystem.serviceimpl.LicenseDirectoryServiceImpl.java
License:Open Source License
public ArrayList<ExcelErrorDetails> validateLocalRowData(Row row) { int rowNumber = row.getRowNum(); ArrayList<ExcelErrorDetails> rowErrorList = new ArrayList<ExcelErrorDetails>(); String stringToCheck = ""; int colNum;//w w w. j a v a 2 s .c om String fieldToCheck = ""; boolean valueNotValid = false; // validation for License Number fieldToCheck = "LOCAL_LICENSE_DATA_LICENSE_NO"; colNum = readFromLocalHeader(null, fieldToCheck); try { stringToCheck = String .valueOf((int) (row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getNumericCellValue())); } catch (IllegalStateException illegalStateException) { rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "License No. should not have characters")); valueNotValid = true; } if (!valueNotValid) { if (stringToCheck.equals("0")) { rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "License Number cannot be null")); } } // validation for Local Jurisdiction fieldToCheck = "LOCAL_LICENSE_DATA_LOCAL_JURISDICTION"; colNum = readFromLocalHeader(null, fieldToCheck); try { stringToCheck = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim(); } catch (IllegalStateException illegalStateException) { rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "Local Jurisdiction should have characters")); valueNotValid = true; } if (!valueNotValid) { if (stringToCheck.equals("")) { rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "Local Jurisdiction cannot be null")); } } // validation for Expiry Date fieldToCheck = "LOCAL_LICENSE_DATA_EXPIRY_DATE"; colNum = readFromHeader(null, fieldToCheck); try { stringToCheck = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getStringCellValue(); } catch (IllegalStateException illegalStateException) { rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "Expiry date should be in proper format and with '-' or '.' as separators")); valueNotValid = true; } if (!valueNotValid) { if (stringToCheck == null) { rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "Expiry Date cannot be null")); } if (!stringToCheck.matches("^(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}$")) { rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "Wrong Date Format")); } } if (!rowErrorList.isEmpty()) { return rowErrorList; } else return null; }
From source file:com.elecnor.ecosystem.serviceimpl.LicenseDirectoryServiceImpl.java
License:Open Source License
public LicenseDirectory getLocalLicenseDetails(Row row, DomainDetail domainDetail, UserDetail userDetail) { LicenseDirectory localLicDirectory = new LicenseDirectory(); String inputDateInString = ""; Date inputDate = null;/* ww w . j a v a2 s . co m*/ DateFormat dateFormat = new SimpleDateFormat("MM-dd-yyyy"); String checkValue; localLicDirectory.setLicenseSubmittedBy(userDetail); localLicDirectory.setDomainDetail(domainDetail); localLicDirectory.setType("LOCAL"); localLicDirectory.setStatus("ACTIVE"); try { checkValue = String.valueOf((int) (row.getCell(0, Row.CREATE_NULL_AS_BLANK).getNumericCellValue())); if (checkValue.equalsIgnoreCase("0")) { localLicDirectory.setLicenseNumber(""); } else { localLicDirectory.setLicenseNumber(checkValue); } } catch (Exception e1) { localLicDirectory.setLicenseNumber(""); } try { localLicDirectory .setLocalJurisdiction(row.getCell(1, Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim()); } catch (Exception e1) { localLicDirectory.setLocalJurisdiction(""); } try { localLicDirectory .setPrimaryPerson(row.getCell(3, Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim()); } catch (Exception e1) { localLicDirectory.setPrimaryPerson(""); } try { inputDateInString = row.getCell(2, Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim(); inputDate = dateFormat.parse(inputDateInString); localLicDirectory.setExpiryDate(inputDate); } catch (Exception e1) { localLicDirectory.setExpiryDate(null); } return localLicDirectory; }
From source file:com.exilant.exility.core.XLSReader.java
License:Open Source License
/** * purpose of this method to create ValueList along with types and column * name. Simple design followed : Just have ColumnMetaData object which * contains everything. For a Cell we will have one columnMetaData object * and it will have values across the// w w w .j ava2 s. c o m * * @param row * @throws Exception */ private void readARow(Row row, int nbrColumnsInARow) throws ExilityException { Value[] columnValues = new Value[nbrColumnsInARow]; Value aColumnValue = null; String rawValue = null; for (int c = 0; c < nbrColumnsInARow; c++) { Cell cell = row.getCell(c, Row.CREATE_NULL_AS_BLANK); ColumnMetaData columnInfo = this.columnsData.get(new Integer(c)); int xlsColumnDataType = columnInfo.getXlsDataType(); DataValueType exilDataType = null; int cellType = cell.getCellType(); if (xlsColumnDataType != XLSReader.UNKNOWN_TYPE) { cellType = xlsColumnDataType; } try { switch (cellType) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { rawValue = DateUtility.formatDate(cell.getDateCellValue()); /* * returns yyyy-mm-dd hh:mm:ss.sss full date with time. */ exilDataType = DataValueType.DATE; } else { double decimalNumber = cell.getNumericCellValue(); rawValue = NumberToTextConverter.toText(decimalNumber); boolean isDecimal = rawValue.contains("."); if (isDecimal) { exilDataType = DataValueType.DECIMAL; } else { exilDataType = DataValueType.INTEGRAL; } } break; case Cell.CELL_TYPE_STRING: rawValue = cell.getStringCellValue().trim(); exilDataType = DataValueType.TEXT; break; case Cell.CELL_TYPE_FORMULA: rawValue = cell.getStringCellValue().trim(); exilDataType = DataValueType.TEXT; break; case Cell.CELL_TYPE_BLANK: rawValue = cell.getStringCellValue(); exilDataType = DataValueType.NULL; columnInfo.setExilDataType(exilDataType); break; case Cell.CELL_TYPE_BOOLEAN: rawValue = cell.getBooleanCellValue() ? BooleanValue.TRUE : BooleanValue.FALSE; exilDataType = DataValueType.BOOLEAN; break; default: String msg = columnInfo.getColumnName() + XLSReader.INVALID_COLUMN_TYPE + row.getRowNum(); Spit.out(msg); } } catch (Exception e) { // Trying to set valueType value and expected valueType value // for column in row String[] params = { this.getXlsTypeAsText(cell.getCellType()), this.getXlsTypeAsText(cellType), columnInfo.getColumnName(), "" + row.getRowNum() }; String message = this.replaceMessageParams(XLSReader.DATATYPE_MISMATCH, params); throw new ExilityException(message); } if (xlsColumnDataType == XLSReader.UNKNOWN_TYPE && cellType != Cell.CELL_TYPE_BLANK) { columnInfo.setXlsDataType(cellType); columnInfo.setExilDataType(exilDataType); } exilDataType = columnInfo.getExilDataType(); aColumnValue = Value.newValue(rawValue, exilDataType); columnValues[c] = aColumnValue; this.columnsData.put(new Integer(c), columnInfo); } this.rows.add(columnValues); }
From source file:com.exilant.exility.core.XlxUtil.java
License:Open Source License
/*** * extract fields from spread sheet into dc * //from www . ja va 2 s. c om * @param table * table element of spread sheet * @param dc * dc * @param useDictionaryForDataType * refer to data dictionary or use DataType as present in spread * sheet */ private void extractValues(Sheet sheet, DataCollection dc, boolean useDictionaryForDataType) { int n = sheet.getLastRowNum(); // if there are no values, following for loop will not execute.. for (int i = 1; i <= n; i++) // first row is header { Row row = sheet.getRow(i); if (row == null) { continue; } // value row should have just two cells in it int nbrCells = row.getLastCellNum(); if (nbrCells < 1) { continue; } String fieldName = row.getCell(0, Row.CREATE_NULL_AS_BLANK).getStringCellValue(); if (fieldName.length() == 0) { continue; // no name } Cell dataCell = null; String fieldValue = EMPTY_STRING; if (nbrCells > 1) // value is present { dataCell = row.getCell(1, Row.CREATE_NULL_AS_BLANK); fieldValue = this.getTextValue(dataCell); } if (useDictionaryForDataType) { dc.addValueAfterCheckingInDictionary(fieldName, fieldValue); } else { dc.addValue(fieldName, fieldValue, this.getExilityType(dataCell)); } } }
From source file:com.exilant.exility.core.XlxUtil.java
License:Open Source License
/*** * get data types of column based on actual values in the sheet * /*from w ww. jav a 2 s. co m*/ * @param sheet * @param nbrCells * @param rowStart * @param rowEnd * @return */ private DataValueType[] getExilityTypes(Sheet sheet, int nbrCells) { DataValueType[] types = new DataValueType[nbrCells]; // though NULL is default (as of now that is the first one in ENUM) let // us explicitly populate it for (int i = 0; i < nbrCells; i++) { types[i] = DataValueType.NULL; } int rowStart = sheet.getFirstRowNum(); int rowEnd = sheet.getLastRowNum(); int nbrFound = 0; // which cell to start? We will go by the first cell of the first // physucal row Row firstRow = sheet.getRow(sheet.getFirstRowNum()); int startingCellIdx = firstRow.getFirstCellNum(); int endCellIdx = startingCellIdx + nbrCells; for (int i = rowStart; i <= rowEnd; i++) { Row row = sheet.getRow(i); if (row == null) { continue; } for (int j = startingCellIdx; j < endCellIdx; j++) { // do we already know this type? if (types[j] != DataValueType.NULL) { continue; } Cell cell = row.getCell(j, Row.RETURN_BLANK_AS_NULL); if (cell == null) { continue; } types[j] = this.getExilityType(cell); nbrFound++; if (nbrFound == nbrCells) { return types; } } } // we will treat unknown ones as text for (int i = 0; i < nbrCells; i++) { if (types[i] == DataValueType.NULL) { types[i] = DataValueType.TEXT; } } return types; }
From source file:com.github.camaral.sheeco.processor.PayloadFiller.java
License:Apache License
public static <T> List<SpreadsheetViolation> fillAttributes(final T payload, final Row row, final List<Attribute> attributes, final FormulaEvaluator evaluator) { final List<SpreadsheetViolation> violations = new ArrayList<>(); for (final Attribute attr : attributes) { final Cell cell = row.getCell(attr.getColumnIndex(), Row.CREATE_NULL_AS_BLANK); evaluator.evaluateInCell(cell);/*from w ww .j a va2s.c om*/ if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { violations.add(new SpreadsheetViolation("serializer.spreadsheet.cell.error", cell)); continue; } try { attr.setValue(payload, cell); } catch (final InvalidCellValueException e) { violations.add(new SpreadsheetViolation("serializer.spreadsheet.cell.invalid", cell)); continue; } catch (final InvalidCellFormatException e) { violations.add(new SpreadsheetViolation("serializer.spreadsheet.cell.type.invalid", cell)); continue; } } return violations; }
From source file:com.ibm.db2j.GExcel.java
License:Open Source License
/** * looks for the column definition and initializes the following attributes : * /*from www. j ava 2 s . co m*/ * - numberOfColumns * - columnIndexes * - columnNames * * If a column which contains no values is ignored. * * If firstRowIsMetaData is true, the column names will be extract from the first row of the spreadsheet. * Else, they will be automatically generated : COLUMN1, COLUMN2... * * @param sheet */ private void findColumns(Sheet sheet) { numberOfColumns = 0; columnIndexes = new ArrayList<Integer>(); columnNames = new ArrayList<String>(); Row firstRow = sheet.getRow(firstRowIndex); int columnLabelIndex = 1; if (firstRowIsMetaData) { //For each column for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) { //Get the first cell in the column Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK); columnIndexes.add(cell.getColumnIndex()); int cellType = cell.getCellType(); if (Cell.CELL_TYPE_FORMULA == cellType) { cellType = cell.getCachedFormulaResultType(); // System.out.println("cell type is now getCachedFormulaResultType() = " + cellType ); } //Build the column names depending on it's type switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: // case Cell.CELL_TYPE_FORMULA: // DO NOT USE: getCellFormula() !!! // System.out.println("cell type string" ); // Note: Javadoc on method getStringCellValue() states: // "get the value of the cell as a string - for numeric cells we throw an exception. For blank cells we return an empty string. // For formulaCells that are not string Formulas, we throw an exception" ++numberOfColumns; columnNames.add(cell.getStringCellValue().replaceAll("[\\ ]", "_")); // Note we should not have to do this in future... once defect is fixed break; case Cell.CELL_TYPE_NUMERIC: // System.out.println("cell type numeric " + // ( DateUtil.isCellDateFormatted( cell ) ? "date: " + cell.getDateCellValue().toString() : "num: " + cell.getNumericCellValue() ) ); ++numberOfColumns; columnNames.add(DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue().toString() : "" + cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: // System.out.println("cell type boolean" ); ++numberOfColumns; columnNames.add("" + cell.getBooleanCellValue()); break; default: // System.out.println("cell type default" ); ++numberOfColumns; columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex); break; } columnLabelIndex++; } } else { //For each column for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) { //Get the first cell in the column Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK); columnIndexes.add(cell.getColumnIndex()); columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex++); } } }
From source file:com.ibm.db2j.GExcel.java
License:Open Source License
/** * This method checks if the spreadsheet is well typed. * This means that all values in each column have the same excel type. * /*w w w. j a va 2s .co m*/ * Returns true if the spreadsheet is well typed, else returns false. * * In addition, this method tries to deduce the excel types from each columns and initializes the attribute : columnTypes. * If a column is empty, then its type is CELL_WITH_NO_TYPE. * The attribute columnTypes must only be used if the spreadsheet is well typed. In the other cases, columnTypes is not significant. * * @param columnIndexes * @return returns true if the spreadsheet is well typed, else returns false */ private boolean checkSheetTypeConsistency(List<Integer> columnIndexes) { boolean isConsistent = true; int firstRow = firstRowIndex; if (firstRowIsMetaData) { ++firstRow; } Row currentRow; Cell cell; int index; for (int i = firstRow; i <= lastRowIndex; ++i) { currentRow = sheet.getRow(i); if (currentRow != null) { index = 0; for (int j = firstColumnIndex; j <= lastColumnIndex; ++j) { cell = currentRow.getCell(j, Row.CREATE_NULL_AS_BLANK); if (cell != null) { // logger.logInfo("Checking non-null cell: " + cell); int cellType = -1; try { cellType = evaluator.evaluateInCell(cell).getCellType(); } catch (Exception e) { logger.logWarning(GDBMessages.DSWRAPPER_GEXCEL_CELL_TYPE_EVALUATION_FAILURE, "Unable to evaluate type for cell at row " + i + " col " + j + ": " + cell); isConsistent = false; continue; } switch (cellType) { case Cell.CELL_TYPE_STRING: isConsistent = checkSheetConsistencySubMethod(cell, index, isConsistent); ++index; break; case Cell.CELL_TYPE_NUMERIC: isConsistent = checkSheetConsistencySubMethod(cell, index, isConsistent); ++index; break; case Cell.CELL_TYPE_BOOLEAN: isConsistent = checkSheetConsistencySubMethod(cell, index, isConsistent); ++index; break; default: if (index < columnIndexes.size() && columnIndexes.get(index) == cell.getColumnIndex()) { // The cell is null for this column ++index; } break; } } } } } return isConsistent; }
From source file:com.jeans.iservlet.action.admin.DataImportAction.java
/** * ??//from ww w .ja va 2 s . c o m * * @return * @throws Exception */ @Action(value = "hr-import", results = { @Result(name = SUCCESS, type = "json", params = { "root", "results", "contentType", "text/plain", "encoding", "UTF-8" }) }) public String uploadHRData() throws Exception { if (!checkDataFile()) { return SUCCESS; } int deptCount = 0, emplCount = 0; try (Workbook workBook = WorkbookFactory.create(data)) { Sheet deptSheet = workBook.getSheet(""); Sheet emplSheet = workBook.getSheet(""); if (null == deptSheet || null == emplSheet) { results.put("code", 4); results.put("tip", "????Sheet"); return SUCCESS; } Company comp = getCurrentCompany(); // deptSheet: 1?04?5?????? int last = deptSheet.getLastRowNum(); for (int rn = 1; rn <= last; rn++) { Row r = deptSheet.getRow(rn); // ??""??? String flag = StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL))); if (!"".equals(flag)) continue; // ?name? String name = ExcelUtils.getCellValueAsString(r.getCell(0, Row.RETURN_BLANK_AS_NULL)); if (StringUtils.isBlank(name)) continue; else name = StringUtils.trim(name); // ?alias? String alias = ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL)); if (StringUtils.isBlank(alias)) alias = name.substring(0, 15); else alias = StringUtils.trim(alias); // ?ID(superiorId)???????? String superior = ExcelUtils.getCellValueAsString(r.getCell(2, Row.RETURN_BLANK_AS_NULL)); long superiorId = 0; if (StringUtils.isBlank(superior)) { superiorId = comp.getId(); } else { HRUnitNode suprDept = hrService.getDepartmentByName(comp, superior); if (null == suprDept) continue; else superiorId = suprDept.getId(); } // ???listOrder??short999??1999 short listOrder = 999; try { double order = (double) ExcelUtils.getCellValue(r.getCell(3, Row.RETURN_BLANK_AS_NULL)); if (order < 1) listOrder = 1; else if (order > 999) listOrder = 999; else listOrder = (short) Math.round(order); } catch (ClassCastException e) { log(e); listOrder = 999; } hrService.appendDept(name, alias, superiorId, listOrder); deptCount++; } // emplSheet: 1?08?9??????????????admin? last = emplSheet.getLastRowNum(); for (int rn = 1; rn <= last; rn++) { Row r = emplSheet.getRow(rn); // ??""??? String flag = StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(8, Row.RETURN_BLANK_AS_NULL))); if (!"".equals(flag)) continue; // ???name? String name = ExcelUtils.getCellValueAsString(r.getCell(0, Row.RETURN_BLANK_AS_NULL)); if (StringUtils.isBlank(name)) continue; else name = StringUtils.trim(name); // ?ID(deptId)??? String deptName = ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL)); long deptId = 0; if (StringUtils.isBlank(deptName)) { continue; } else { HRUnitNode dept = hrService.getDepartmentByName(comp, deptName); if (null == dept) continue; else deptId = dept.getId(); } // ???listOrder??short999??1999 short listOrder = 999; try { double order = (double) ExcelUtils.getCellValue(r.getCell(2, Row.RETURN_BLANK_AS_NULL)); if (order < 1) listOrder = 1; else if (order > 999) listOrder = 999; else listOrder = (short) Math.round(order); } catch (ClassCastException e) { log(e); listOrder = 999; } // ???????admin? boolean leader = "".equals( StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(3, Row.RETURN_BLANK_AS_NULL)))); boolean supervisor = "".equals( StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL)))); boolean auditor = "".equals( StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(5, Row.RETURN_BLANK_AS_NULL)))); boolean iter = "".equals( StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(6, Row.RETURN_BLANK_AS_NULL)))); boolean admin = "".equals( StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(7, Row.RETURN_BLANK_AS_NULL)))); hrService.appendEmpl(name, deptId, listOrder, leader, supervisor, auditor, iter, admin); emplCount++; } results.put("code", 0); results.put("tip", "???" + deptCount + "" + emplCount + "??"); log("?HR??" + deptCount + "" + emplCount + "??"); return SUCCESS; } catch (Exception e) { log(e); results.put("code", 4); results.put("tip", "???" + deptCount + "" + emplCount + "??"); log("?HR??" + deptCount + "" + emplCount + "??"); return SUCCESS; } }
From source file:com.jeans.iservlet.action.admin.DataImportAction.java
/** * ?//from w ww . ja v a 2 s . com * * @return * @throws Exception */ @Action(value = "ci-import", results = { @Result(name = SUCCESS, type = "json", params = { "root", "results", "contentType", "text/plain", "encoding", "UTF-8" }) }) public String uploadCIData() throws Exception { if (!checkDataFile()) { return SUCCESS; } int hardCount = 0, softCount = 0; try (Workbook workBook = WorkbookFactory.create(data)) { Sheet hardSheet = workBook.getSheet(""); Sheet softSheet = workBook.getSheet(""); if (null == hardSheet || null == softSheet) { results.put("code", 4); results.put("tip", "????Sheet"); return SUCCESS; } Company comp = getCurrentCompany(); ExcelUtils.setNumberFormat("#"); SimpleDateFormat sdf = new SimpleDateFormat("yyyymm"); // hardSheet: 1?015?160? int last = hardSheet.getLastRowNum(); for (int rn = 1; rn <= last; rn++) { Row r = hardSheet.getRow(rn); // ??""??? String flag = StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(0, Row.RETURN_BLANK_AS_NULL))); // ???name? String name = StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(3, Row.RETURN_BLANK_AS_NULL))); if (!"".equals(flag) || StringUtils.isBlank(name)) { continue; } Map<String, Object> hardware = new HashMap<String, Object>(); hardware.put("company", comp); hardware.put("type", AssetConstants.HARDWARE_ASSET); hardware.put("name", name); hardware.put("code", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL)))); hardware.put("catalog", parseAssetCatalog( StringUtils.trim( ExcelUtils.getCellValueAsString(r.getCell(2, Row.RETURN_BLANK_AS_NULL))), AssetConstants.HARDWARE_ASSET)); hardware.put("vendor", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL)))); hardware.put("modelOrVersion", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(5, Row.RETURN_BLANK_AS_NULL)))); hardware.put("assetUsage", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(6, Row.RETURN_BLANK_AS_NULL)))); hardware.put("purchaseTime", ExcelUtils.getCellValueAsDate(r.getCell(7, Row.RETURN_BLANK_AS_NULL), sdf)); try { double q = (double) ExcelUtils.getCellValue(r.getCell(8, Row.RETURN_BLANK_AS_NULL)); if (q < 1) { hardware.put("quantity", 1); } else { hardware.put("quantity", (int) Math.round(q)); } } catch (Exception e) { hardware.put("quantity", 1); } try { hardware.put("cost", BigDecimal .valueOf((double) ExcelUtils.getCellValue(r.getCell(9, Row.RETURN_BLANK_AS_NULL)))); } catch (Exception e) { hardware.put("cost", new BigDecimal(0)); } hardware.put("state", AssetConstants.IDLE); hardware.put("sn", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(10, Row.RETURN_BLANK_AS_NULL)))); hardware.put("configuration", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(11, Row.RETURN_BLANK_AS_NULL)))); hardware.put("warranty", AssetConstants.IMPLIED_WARRANTY); hardware.put("location", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(12, Row.RETURN_BLANK_AS_NULL)))); hardware.put("ip", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(13, Row.RETURN_BLANK_AS_NULL)))); hardware.put("importance", AssetConstants.GENERAL_DEGREE); hardware.put("owner", null); hardware.put("comment", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(14, Row.RETURN_BLANK_AS_NULL)))); hardware.put("financialCode", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(15, Row.RETURN_BLANK_AS_NULL)))); assetService.newAsset(hardware); hardCount++; } // softSheet: 1?012?130? last = softSheet.getLastRowNum(); for (int rn = 1; rn <= last; rn++) { Row r = softSheet.getRow(rn); // ??""??? String flag = StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(0, Row.RETURN_BLANK_AS_NULL))); // ???name? String name = StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(2, Row.RETURN_BLANK_AS_NULL))); if (!"".equals(flag) || StringUtils.isBlank(name)) { continue; } if (StringUtils.isBlank(name)) continue; Map<String, Object> software = new HashMap<String, Object>(); software.put("company", comp); software.put("type", AssetConstants.SOFTWARE_ASSET); software.put("name", name); software.put("catalog", parseAssetCatalog( StringUtils.trim( ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL))), AssetConstants.SOFTWARE_ASSET)); software.put("vendor", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(3, Row.RETURN_BLANK_AS_NULL)))); software.put("modelOrVersion", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL)))); try { double q = (double) ExcelUtils.getCellValue(r.getCell(5, Row.RETURN_BLANK_AS_NULL)); if (q < 1) { software.put("quantity", 1); } else { software.put("quantity", (int) Math.round(q)); } } catch (Exception e) { software.put("quantity", 1); } try { software.put("cost", BigDecimal .valueOf((double) ExcelUtils.getCellValue(r.getCell(6, Row.RETURN_BLANK_AS_NULL)))); } catch (Exception e) { software.put("cost", new BigDecimal(0)); } software.put("purchaseTime", ExcelUtils.getCellValueAsDate(r.getCell(7, Row.RETURN_BLANK_AS_NULL), sdf)); software.put("assetUsage", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(8, Row.RETURN_BLANK_AS_NULL)))); software.put("state", AssetConstants.IN_USE); software.put("softwareType", parseSoftwareType( StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(9, Row.RETURN_BLANK_AS_NULL))))); software.put("license", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(10, Row.RETURN_BLANK_AS_NULL)))); software.put("expiredTime", ExcelUtils.getCellValueAsDate(r.getCell(11, Row.RETURN_BLANK_AS_NULL), sdf)); software.put("comment", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(12, Row.RETURN_BLANK_AS_NULL)))); assetService.newAsset(software); softCount++; } results.put("code", 0); results.put("tip", "???" + hardCount + "" + softCount + ""); log("?CI??" + hardCount + "" + softCount + ""); return SUCCESS; } catch (Exception e) { log(e); results.put("code", 4); results.put("tip", "???" + hardCount + "" + softCount + ""); log("?CI??" + hardCount + "" + softCount + ""); return SUCCESS; } }