Java tutorial
package com.jeans.iservlet.controller.impl; import java.io.IOException; import java.io.PrintWriter; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.HashMap; import java.util.Map; import javax.servlet.http.HttpServletResponse; 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.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.multipart.commons.CommonsMultipartFile; import com.jeans.iservlet.controller.BaseController; import com.jeans.iservlet.enums.asset.AccessoryType; import com.jeans.iservlet.model.AssetConstants; import com.jeans.iservlet.model.hr.Company; import com.jeans.iservlet.model.hr.Department; import com.jeans.iservlet.service.asset.AccessoryService; import com.jeans.iservlet.service.asset.AssetService; import com.jeans.iservlet.service.hr.HRService; import com.jeans.iservlet.utils.ExcelUtils; @Controller @RequestMapping("/import") public class ImportController extends BaseController { @Autowired private HRService hrService; @Autowired private AssetService assetService; @Autowired private AccessoryService acsService; private void showProgressDialog(PrintWriter out, String title) { out.write("<script>parent.__showPrg('" + title + "');</script>"); out.flush(); } private void showProgress(PrintWriter out, String tip, double value) { out.write("<script>parent.__progress('" + tip + "', " + value + ");</script>"); out.flush(); } private void closeProgressDialog(PrintWriter out) { out.write("<script>parent.__hidePrg();</script>"); out.flush(); } private void showError(PrintWriter out, String message) { out.write("<script>parent.__error('" + message + "');</script>"); out.flush(); } private void showInfo(PrintWriter out, String message) { out.write("<script>parent.__info('" + message + "');</script>"); out.flush(); } @RequestMapping(method = RequestMethod.POST, value = "/hr") @ResponseBody 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); } } @RequestMapping(method = RequestMethod.POST, value = "/ci") @ResponseBody 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); } } @RequestMapping(method = RequestMethod.POST, value = "/ac") @ResponseBody 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); } } 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; } private byte parseAssetState(String stateName, byte assetType) { byte r = AssetConstants.IDLE; switch (stateName) { case "": r = AssetConstants.IN_USE; break; case "": r = (assetType == AssetConstants.HARDWARE_ASSET ? AssetConstants.FIXING : AssetConstants.IDLE); break; case "": r = AssetConstants.DISUSE; break; case "": r = AssetConstants.IDLE; break; case "?": r = (assetType == AssetConstants.HARDWARE_ASSET ? AssetConstants.ELIMINATED : AssetConstants.DISUSE); break; } return r; } private byte parseHardwareWarranty(String warrantyName) { byte r = AssetConstants.IMPLIED_WARRANTY; switch (warrantyName) { case "?": r = AssetConstants.IMPLIED_WARRANTY; break; case "?": r = AssetConstants.RENEWAL_WARRANTY; break; case "?": r = AssetConstants.EXPIRED_WARRANTY; break; } return r; } private byte parseHardwareImportance(String importanceName) { byte r = AssetConstants.GENERAL_DEGREE; switch (importanceName) { case "": r = AssetConstants.GENERAL_DEGREE; break; case "??": r = AssetConstants.IMPORTANT_DEGREE; break; case "": r = AssetConstants.KEY_DEGREE; break; } return r; } private AccessoryType parseAccessoryType(String accessoryType) { AccessoryType type = AccessoryType.Others; switch (accessoryType) { case "??": type = AccessoryType.ComputerConsumables; break; case "???": type = AccessoryType.PrinterConsumables; break; case "?": type = AccessoryType.Accessories; break; case "": type = AccessoryType.Tools; break; default: } return type; } }