List of usage examples for org.apache.poi.ss.usermodel Sheet getRow
Row getRow(int rownum);
From source file:com.javabean.excel.ExcelDocumentReader.java
public List<String> GetColumnName(int sheetIndex) { List<String> columnNameList; Sheet sheet; Cell cell;/*from w w w . ja va 2 s . c om*/ int columnCount; String stringTemp; sheet = GetSheet(sheetIndex); if (sheet == null) { return null; } Row row = sheet.getRow(0); columnCount = row.getLastCellNum(); columnNameList = new ArrayList(); for (int i = 0; i < columnCount; i++) { cell = row.getCell(i); stringTemp = Convert.NullToString(cell.getStringCellValue(), ReturnStringValue.NULL); columnNameList.add(stringTemp); } return columnNameList; }
From source file:com.javabean.excel.ExcelDocumentReader.java
public List<List<String>> GetDataRowList(int sheetIndex) { List<List<String>> dataRowList; Sheet sheet; Row row;//www . j a v a 2 s . co m int rowCount; int columnCount; sheet = GetSheet(sheetIndex); if (sheet == null) { return null; } rowCount = sheet.getLastRowNum(); row = sheet.getRow(0); columnCount = row.getLastCellNum(); dataRowList = new ArrayList<>(); for (int i = 0; i < rowCount + 1; i++) { List<String> dataRow = GetOneDataRow(sheet, i, columnCount); if (dataRow != null) { dataRowList.add(dataRow); } } return dataRowList; }
From source file:com.javabean.excel.ExcelDocumentReader.java
private List<String> GetOneDataRow(Sheet sheet, int rowIndex, int columnCount) { List<String> dataRow; Cell cell;//from w w w . ja v a 2s.c o m Row row; String stringTemp; dataRow = new ArrayList<>(); try { row = sheet.getRow(rowIndex); for (int i = 0; i < columnCount; i++) { cell = row.getCell(i); cell.setCellType(CELL_TYPE_STRING); if (cell == null) { stringTemp = "NULL"; } else { stringTemp = cell.getStringCellValue(); } dataRow.add(stringTemp); } } catch (Exception ex) { dataRow = null; } return dataRow; }
From source file:com.jeans.iservlet.action.admin.DataImportAction.java
/** * ??//from ww w . j a v a 2s .co 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
/** * ?/* w ww. j av a 2 s . c om*/ * * @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; } }
From source file:com.jeans.iservlet.controller.impl.ImportController.java
@RequestMapping(method = RequestMethod.POST, value = "/hr") @ResponseBody/* ww w. j a v a 2s .c om*/ 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/*from ww w. j av a2 s . 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/*from w w w.ja v a2 s . c om*/ 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.jkoolcloud.tnt4j.streams.parsers.ActivityExcelSheetParser.java
License:Apache License
/** * Gets field raw data value resolved by locator. * * @param locator// w w w.j a v a 2 s . c o m * activity field locator * @param cData * MS Excel document sheet representing activity object data fields * @param formattingNeeded * flag to set if value formatting is not needed * @return raw value resolved by locator, or {@code null} if value is not resolved * * @throws ParseException * if exception occurs while resolving raw data value */ @Override protected Object resolveLocatorValue(ActivityFieldLocator locator, ActivityContext cData, AtomicBoolean formattingNeeded) throws ParseException { Object val = null; String locStr = locator.getLocator(); Sheet sheet = cData.getData(); if (StringUtils.isNotEmpty(locStr)) { CellReference ref = new CellReference(locStr); boolean cellFound = false; if (ref.getRow() < 0 || ref.getCol() < 0) { throw new ParseException( StreamsResources.getStringFormatted(MsOfficeStreamConstants.RESOURCE_BUNDLE_NAME, "ActivityExcelRowParser.unresolved.cell.reference", locStr), sheet.getWorkbook().getSheetIndex(sheet)); } Row row = sheet.getRow(ref.getRow()); if (row != null) { Cell cell = row.getCell(ref.getCol()); if (cell != null) { val = getCellValue(cell); cellFound = true; } else { val = row; } } logger().log(OpLevel.TRACE, StreamsResources.getString(MsOfficeStreamConstants.RESOURCE_BUNDLE_NAME, "ActivityExcelRowParser.resolved.cell.value"), locStr, sheet.getWorkbook().getMissingCellPolicy(), toString(val)); } return val; }
From source file:com.jwm123.loggly.reporter.ReportGenerator.java
License:Apache License
public byte[] build(List<Map<String, String>> row) throws IOException { List<String> colLabels = new ArrayList<String>(); Sheet reportSheet = workbook.getSheet("Report"); if (reportSheet == null) { reportSheet = workbook.createSheet("Report"); }//from w ww . j av a 2s .com Row firstRow = reportSheet.getRow(0); if (firstRow == null) { firstRow = reportSheet.createRow(0); int cellNum = 0; for (Map<String, String> col : row) { for (String key : col.keySet()) { Cell cell = firstRow.createCell(cellNum++); setCellValue(cell, key); } } } for (int i = 0; i < firstRow.getLastCellNum(); i++) { Cell cell = firstRow.getCell(i); if (cell != null) { colLabels.add(cell.getStringCellValue()); } } Row newRow = reportSheet.createRow(reportSheet.getLastRowNum() + 1); int lastIndex = -1; for (Map<String, String> col : row) { for (String key : col.keySet()) { int colNum = -1; Cell cell = null; if (colLabels.contains(key)) { colNum = colLabels.indexOf(key); lastIndex = colNum; } if (colNum == -1) { lastIndex++; colNum = lastIndex; shiftColumns(reportSheet, colNum, key); colLabels.add(colNum, key); } cell = newRow.getCell(colNum); if (cell == null) { cell = newRow.createCell(colNum); } setCellValue(cell, col.get(key)); } } for (int i = 0; i < firstRow.getLastCellNum(); i++) { reportSheet.autoSizeColumn(i); } ByteArrayOutputStream baos = new ByteArrayOutputStream(); workbook.write(baos); return baos.toByteArray(); }