Java tutorial
package com.jeans.iservlet.action.admin; import java.io.File; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.HashMap; import java.util.Map; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.struts2.convention.annotation.Action; import org.apache.struts2.convention.annotation.Result; import org.springframework.beans.factory.annotation.Autowired; import com.jeans.iservlet.action.TinyAction; import com.jeans.iservlet.model.AssetConstants; import com.jeans.iservlet.model.hr.Company; import com.jeans.iservlet.service.asset.AssetService; import com.jeans.iservlet.service.hr.HRService; import com.jeans.iservlet.utils.ExcelUtils; import com.jeans.iservlet.view.hr.HRUnitNode; /** * ?Actions * * @author majorli * */ public class DataImportAction extends TinyAction { private HRService hrService; private AssetService assetService; @Autowired public void setHrService(HRService hrService) { this.hrService = hrService; } @Autowired public void setAssetService(AssetService assetService) { this.assetService = assetService; } private File data; private String dataContentType; private String dataFileName; public File getData() { return data; } public void setData(File data) { this.data = data; } public String getDataContentType() { return dataContentType; } public void setDataContentType(String dataContentType) { this.dataContentType = dataContentType; } public String getDataFileName() { return dataFileName; } public void setDataFileName(String dataFileName) { this.dataFileName = dataFileName; } private Map<String, Object> results = new HashMap<String, Object>(); public Map<String, Object> getResults() { return results; } public void setResults(Map<String, Object> results) { this.results = results; } private boolean checkDataFile() { if (null == dataContentType || null == dataFileName || null == data) { results.put("code", 1); results.put("tip", ""); return false; } if (data.length() > 104857600) { results.put("code", 2); results.put("tip", "????100M"); return false; } if (!dataContentType.equals("application/vnd.ms-excel") && !dataContentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) { results.put("code", 3); results.put("tip", "??Excel"); return false; } return true; } /** * ?? * * @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; } } /** * ? * * @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; } } private byte parseAssetCatalog(String catalogName, byte assetType) { if (assetType == AssetConstants.GENERIC_ASSET) { return AssetConstants.GENERIC_IT_ASSET; } else if (assetType == AssetConstants.HARDWARE_ASSET) { byte r = AssetConstants.OTHER_EQUIPMENT; switch (catalogName) { case "": r = AssetConstants.NETWORK_EQUIPMENT; break; case "": r = AssetConstants.SECURITY_EQUIPMENT; break; case "?": r = AssetConstants.SERVER_EQUIPMENT; break; case "": r = AssetConstants.STORAGE_EQUIPMENT; break; case "": r = AssetConstants.INFRASTRUCTURE_EQUIPMENT; break; case "?": r = AssetConstants.TERMINATOR_EQUIPMENT; break; case "": r = AssetConstants.MOBILE_EQUIPMENT; break; case "?": r = AssetConstants.PRINTER_EQUIPMENT; break; default: r = AssetConstants.OTHER_EQUIPMENT; } return r; } else if (assetType == AssetConstants.SOFTWARE_ASSET) { byte r = AssetConstants.OTHER_SOFTWARE; switch (catalogName) { case "?": r = AssetConstants.OPERATING_SYSTEM_SOFTWARE; break; case "?": r = AssetConstants.DATABASE_SYSTEM_SOFTWARE; break; case "": r = AssetConstants.MIDDLEWARE_SOFTWARE; break; case "": r = AssetConstants.STORAGE_SYSTEM_SOFTWARE; break; case "?": r = AssetConstants.SECURITY_SOFTWARE; break; case "": r = AssetConstants.OFFICE_SOFTWARE; break; case "": r = AssetConstants.APPLICATION_SOFTWARE; break; default: r = AssetConstants.OTHER_SOFTWARE; } return r; } else { return AssetConstants.GENERIC_IT_ASSET; } } private byte parseSoftwareType(String softwareTypeName) { byte r = AssetConstants.COMMERCIAL_SOFTWARE; switch (softwareTypeName) { case "?": r = AssetConstants.COMMERCIAL_SOFTWARE; break; case "/?": r = AssetConstants.OPEN_SOURCE_SOFTWARE; break; case "?": r = AssetConstants.FREE_SOFTWARE; break; case "": r = AssetConstants.TRIAL_SOFTWARE; break; case "?": r = AssetConstants.CUSTOM_DEVELOPED_SOFTWARE; break; case "?": r = AssetConstants.SELF_DEVELOPED_SOFTWARE; break; default: r = AssetConstants.OTHER_TYPE_SOFTWARE; } return r; } }