List of usage examples for org.apache.poi.ss.usermodel Row getCell
Cell getCell(int cellnum, MissingCellPolicy policy);
From source file:com.jeans.iservlet.controller.impl.ImportController.java
@RequestMapping(method = RequestMethod.POST, value = "/hr") @ResponseBody//from www. j a v a 2 s . c o m public void importHR(@RequestParam("data") CommonsMultipartFile data, HttpServletResponse response) throws IOException { PrintWriter out = response.getWriter(); if (null == data) { showError(out, "??"); out.close(); return; } int deptCount = 0, emplCount = 0; String info = null; try (Workbook workBook = WorkbookFactory.create(data.getInputStream())) { Sheet deptSheet = workBook.getSheet(""); Sheet emplSheet = workBook.getSheet(""); if (null == deptSheet || null == emplSheet) { data.getInputStream().close(); showError(out, "????Sheet"); out.close(); return; } Company comp = getCurrentCompany(); // deptSheet: 1?04?5?????? int total = deptSheet.getLastRowNum() + emplSheet.getLastRowNum(); double progress = 0.0; double step = 100.0 / (double) total; showProgressDialog(out, getCurrentCompany().getAlias() + ""); 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))); progress += step; 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); } showProgress(out, "" + name, progress);// // ?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 { Department suprDept = hrService.findDepartmentByName(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; } if (null != hrService.createDept(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))); progress += step; 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); } showProgress(out, "" + name, progress); // // ?ID(deptId)??? String deptName = ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL)); long deptId = 0; if (StringUtils.isBlank(deptName)) { continue; } else { Department dept = hrService.findDepartmentByName(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)))); if (null != hrService.createEmpl(name, deptId, listOrder, leader, supervisor, auditor, iter, admin)) { emplCount++; } } info = "?" + deptCount + "" + emplCount + "??"; } catch (Exception e) { log(e); info = "????" + deptCount + "" + emplCount + "??"; } finally { data.getInputStream().close(); closeProgressDialog(out); showInfo(out, info); out.close(); log(info); } }
From source file:com.jeans.iservlet.controller.impl.ImportController.java
@RequestMapping(method = RequestMethod.POST, value = "/ci") @ResponseBody// www . ja v a 2s . com public void importCI(@RequestParam("data") CommonsMultipartFile data, HttpServletResponse response) throws IOException { PrintWriter out = response.getWriter(); if (null == data) { showError(out, "??"); out.close(); return; } int hardCount = 0, softCount = 0; String info = null; try (Workbook workBook = WorkbookFactory.create(data.getInputStream())) { Sheet hardSheet = workBook.getSheet(""); Sheet softSheet = workBook.getSheet(""); if (null == hardSheet || null == softSheet) { data.getInputStream().close(); showError(out, "????Sheet"); out.close(); return; } Company comp = getCurrentCompany(); ExcelUtils.setNumberFormat("#"); SimpleDateFormat sdf = new SimpleDateFormat("yyyymm"); int total = hardSheet.getLastRowNum() + softSheet.getLastRowNum(); double progress = 0.0; double step = 100.0 / (double) total; showProgressDialog(out, getCurrentCompany().getAlias() + "?"); // hardSheet: 1?019?200? 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(4, Row.RETURN_BLANK_AS_NULL))); progress += step; if (!"".equals(flag) || StringUtils.isBlank(name)) { continue; } showProgress(out, "" + name, progress); // 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("financialCode", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(2, Row.RETURN_BLANK_AS_NULL)))); hardware.put("catalog", parseAssetCatalog( StringUtils.trim( ExcelUtils.getCellValueAsString(r.getCell(3, Row.RETURN_BLANK_AS_NULL))), AssetConstants.HARDWARE_ASSET)); hardware.put("vendor", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(5, Row.RETURN_BLANK_AS_NULL)))); hardware.put("modelOrVersion", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(6, Row.RETURN_BLANK_AS_NULL)))); hardware.put("assetUsage", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(7, Row.RETURN_BLANK_AS_NULL)))); hardware.put("sn", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(8, Row.RETURN_BLANK_AS_NULL)))); hardware.put("configuration", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(9, Row.RETURN_BLANK_AS_NULL)))); hardware.put("purchaseTime", ExcelUtils.getCellValueAsDate(r.getCell(10, Row.RETURN_BLANK_AS_NULL), sdf)); try { double q = (double) ExcelUtils.getCellValue(r.getCell(11, 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(12, Row.RETURN_BLANK_AS_NULL)))); } catch (Exception e) { hardware.put("cost", new BigDecimal(0)); } hardware.put("state", parseAssetState( StringUtils.trim( ExcelUtils.getCellValueAsString(r.getCell(13, Row.RETURN_BLANK_AS_NULL))), AssetConstants.HARDWARE_ASSET)); hardware.put("warranty", parseHardwareWarranty(StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(14, Row.RETURN_BLANK_AS_NULL))))); hardware.put("location", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(15, Row.RETURN_BLANK_AS_NULL)))); hardware.put("ip", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(16, Row.RETURN_BLANK_AS_NULL)))); hardware.put("importance", parseHardwareImportance(StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(17, Row.RETURN_BLANK_AS_NULL))))); hardware.put("owner", hrService.findEmployeeByName(getCurrentCompany(), StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(18, Row.RETURN_BLANK_AS_NULL))))); hardware.put("comment", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(19, Row.RETURN_BLANK_AS_NULL)))); if (null != assetService.create(hardware)) { hardCount++; } } // softSheet: 1?013?140? 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))); progress += step; if (!"".equals(flag) || StringUtils.isBlank(name)) { continue; } showProgress(out, "" + name, progress); // 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)))); software.put("assetUsage", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(5, Row.RETURN_BLANK_AS_NULL)))); software.put("purchaseTime", ExcelUtils.getCellValueAsDate(r.getCell(6, Row.RETURN_BLANK_AS_NULL), sdf)); try { double q = (double) ExcelUtils.getCellValue(r.getCell(7, 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(8, Row.RETURN_BLANK_AS_NULL)))); } catch (Exception e) { software.put("cost", new BigDecimal(0)); } software.put("state", parseAssetState( StringUtils.trim( ExcelUtils.getCellValueAsString(r.getCell(9, Row.RETURN_BLANK_AS_NULL))), AssetConstants.SOFTWARE_ASSET)); software.put("softwareType", parseSoftwareType(StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(10, Row.RETURN_BLANK_AS_NULL))))); software.put("license", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(11, Row.RETURN_BLANK_AS_NULL)))); software.put("expiredTime", ExcelUtils.getCellValueAsDate(r.getCell(12, Row.RETURN_BLANK_AS_NULL), sdf)); software.put("comment", StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(13, Row.RETURN_BLANK_AS_NULL)))); if (null != assetService.create(software)) { softCount++; } } info = "??" + hardCount + "" + softCount + ""; } catch (Exception e) { log(e); info = "?????" + hardCount + "" + softCount + ""; } finally { data.getInputStream().close(); closeProgressDialog(out); showInfo(out, info); out.close(); log(info); } }
From source file:com.jeans.iservlet.controller.impl.ImportController.java
@RequestMapping(method = RequestMethod.POST, value = "/ac") @ResponseBody//ww w. j a va 2 s . c o m public void importAC(@RequestParam("data") CommonsMultipartFile data, HttpServletResponse response) throws IOException { PrintWriter out = response.getWriter(); if (null == data) { showError(out, "??"); out.close(); return; } int count = 0; String info = null; try (Workbook workBook = WorkbookFactory.create(data.getInputStream())) { Sheet acsSheet = workBook.getSheet(""); if (null == acsSheet) { data.getInputStream().close(); showError(out, "????Sheet"); out.close(); return; } Company comp = getCurrentCompany(); int total = acsSheet.getLastRowNum(); double progress = 0.0; double step = 100.0 / (double) total; showProgressDialog(out, getCurrentCompany().getAlias() + "???"); // acsSheet: 1?06?70? int last = acsSheet.getLastRowNum(); for (int rn = 1; rn <= last; rn++) { Row r = acsSheet.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))); progress += step; if (!"".equals(flag) || StringUtils.isBlank(name)) { continue; } showProgress(out, "???" + name, progress); // AccessoryType type = parseAccessoryType( StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL)))); String brand = StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(3, Row.RETURN_BLANK_AS_NULL))); String model = StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL))); String description = StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(5, Row.RETURN_BLANK_AS_NULL))); String unit = StringUtils .trim(ExcelUtils.getCellValueAsString(r.getCell(6, Row.RETURN_BLANK_AS_NULL))); if (null != acsService.create(comp, type, name, brand, model, unit, description)) { count++; } } info = "????" + count + "?"; } catch (Exception e) { log(e); info = "???????" + count + "?"; } finally { data.getInputStream().close(); closeProgressDialog(out); showInfo(out, info); out.close(); log(info); } }
From source file:com.lw.common.utils.ExcelUtil.java
private List<String> parseDataRow(Row row, int size) { List<String> rst = new ArrayList<>(); Cell cell;/*from ww w. j av a 2 s.co m*/ for (int i = 0; i < size; i++) { cell = row.getCell(i, Row.RETURN_BLANK_AS_NULL); if (cell == null) { rst.add(""); } else { cell.setCellType(Cell.CELL_TYPE_STRING); rst.add(cell.getStringCellValue().trim()); } } return rst; }
From source file:com.opengamma.financial.security.equity.GICSCodeDescription.java
License:Open Source License
/** * Load S&P GICS code mappings from an Apace POI HSSFWorkbook * @param workbook HSSFWorkbook to parse S&P GCIS Excel * @param gicsMap Map to add mappings to *///from w w w . ja v a 2 s. c o m static void processGICSExcelWorkbook(Workbook workbook, Map<String, String> gicsMap) { //Assume 1 sheet Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { return; } for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) { Row row = sheet.getRow(rowNum); if (row == null) { continue; } for (int cellNum = 0; cellNum < row.getPhysicalNumberOfCells(); cellNum++) { Cell cell = row.getCell(cellNum, Row.CREATE_NULL_AS_BLANK); if (isNumeric(cell)) { //worst case if the Excel file is in an incorrect (or updated) format // is that number -> random or empty string mappings will be created gicsMap.put(getGICSCellValue(cell), getGICSCellValue(row, cellNum + 1)); } } } }
From source file:com.opengamma.financial.security.equity.GICSCodeDescription.java
License:Open Source License
/** * Get the value of the Apache POI Cell specified by the row and cell num (column) as a String. * If row,cellNum defines a null or blank cell, an empty String is returned * @param row Apace POI Row//www .j av a2 s.c o m * @param cellNum cell number in Row * @return String value of specified cell, or empty String if invalid cell */ static String getGICSCellValue(Row row, int cellNum) { return getGICSCellValue(row.getCell(cellNum, Row.CREATE_NULL_AS_BLANK)); }
From source file:com.opengamma.integration.copier.sheet.reader.SimpleXlsSheetReader.java
License:Open Source License
private static Cell getCellSafe(Row rawRow, int column) { return rawRow.getCell(column, Row.CREATE_NULL_AS_BLANK); }