java工具类9-excel导出导入

/**
     * 实物入库前信息批量导出Excel
     * 
     * @param
     * @return
     */
    @SystemLog(description="实物入库前信息批量导出Excel", recordParams=true)
    @RequestMapping("/batchExportObjectInfo")
    @ResponseBody
    public BaseResult batchExportObjectInfo(ObjectBeforeParam param, HttpServletResponse response,
            HttpServletRequest request) {
        BaseResult result = new BaseResult();
        User user = WebUtil.getLoginUser();
        if (user == null || StringUtils.isBlank(user.getOrgCode())) {
            result.fail();
            result.setMessage("当前用户找不到相应的机构，请联系管理员！");
        }
        Boolean flag = WebUtil.isSuperSystemUser();
        if (!flag) {
            param.setCurrentOrgCode(user.getOrgCode());
        }

        List<StoreBeforeExport> list = storeBeforeService.searchByCondition(param);
        String fileName = "实物入库前信息列表";
        String[] colNames = { "包号", "物品状态", "登记人", "发送机构号", "接收人", "接收机构号", "接收时间", "发送时间" };
        LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
        map.put("包号", "packageNo");
        map.put("物品状态", "obState");
        map.put("登记人", "registrant");
        map.put("发送机构号", "sendOrgCode");
        map.put("接收人", "recipient");
        map.put("接收机构号", "receiveOrgCode");
        map.put("接收时间", "receiveDate");
        map.put("发送时间", "sendDate");
        try {
            ExcelUtil.downloadExcel(fileName, colNames, map, list, response, request);
            result.success();
            result.setMessage("导出Excel成功");
        } catch (BusinessException ex) {
            result.fail();
            result.setMessage("导出Excel失败");
        }
        return result;
    }

    @Override
    public Map<String, String> batchRegister(MultipartFile file, User user) {
        String fileName = file.getOriginalFilename(); // 原始文件名
        Map<String, String> result = new HashMap<String, String>();
        List<Object> tList = new ArrayList<Object>();
        if (!file.isEmpty()) {
            String fileType = fileName.substring(fileName.lastIndexOf("."));// 后缀名
            if (".xlsx".equals(fileType)) {
                // ------------开始读.xlsx表格数据----------------
                try {
                    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
                    // 循环工作表Sheet
                    for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
                        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
                        if (xssfSheet == null) {
                            continue;
                        }
                        // 循环行Row
                        for (int rowNum = 3; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                            if (xssfRow == null) {
                                continue;
                            }
                            Object form = new Object();
                            // 循环列Cell
                            for (int cellNum = 0; cellNum < xssfRow.getLastCellNum(); cellNum++) {
                                XSSFCell xssfCell = xssfRow.getCell(cellNum);
                                if (xssfCell == null) {
                                    continue;
                                }
                                if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                                    xssfCell.getBooleanCellValue();
                                } else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                    xssfCell.getNumericCellValue();
                                }
                                if (0 == xssfCell.getColumnIndex()) {// getColumnIndex()是列标
                                    String packageNo = getValue(xssfCell);
                                    form.setPackageNo(packageNo);
                                } else if (1 == xssfCell.getColumnIndex()) {
                                    String pn = getValue(xssfCell);
                                    if(StringUtils.isNotBlank(pn)&& pn.length()>5){
                                        result.put("code", ObjectParamCheckConstant.PAGE_NO_IS_VALID.getKey());
                                        result.put("desc", ObjectParamCheckConstant.PAGE_NO_IS_VALID.getValue());
                                        return result;
                                    }
                                    int pageNumber = Integer.parseInt(pn);
                                    form.setPageNumber(pageNumber);
                                } else if (2 == xssfCell.getColumnIndex()) {
                                    String obName = getValue(xssfCell);
                                    form.setObName(obName);
                                } 
                                else if (3 == xssfCell.getColumnIndex()) {
                                    String receiveOrgCode = getValue(xssfCell);
                                    form.setReceiveOrgCode(receiveOrgCode);
                                } else if (4 == xssfCell.getColumnIndex()) {
                                    String sd = getValue(xssfCell);
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                    try {
                                        Date sendDate = sdf.parse(sd);
                                        form.setSendDate(sendDate);
                                    } catch (ParseException ex) {
                                        logger.info(ex.getMessage());
                                    }
                                } else if (5 == xssfCell.getColumnIndex()) {
                                    String rd = getValue(xssfCell);
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                    try {
                                        Date recReadyDate = sdf.parse(rd);
                                        form.setRecReadyDate(recReadyDate);
                                    } catch (ParseException ex) {
                                        logger.info(ex.getMessage());
                                    }
                                } else if (6 == xssfCell.getColumnIndex()) {
                                    String remark = getValue(xssfCell);
                                    form.setRemark(remark);
                                }
                            }
                            // 批量保存数据库
                            if (rowNum != 0 && rowNum != 1 && rowNum != 2 && form.getPackageNo() != null
                                    && StringUtils.isNotBlank(form.getPackageNo())) { // 去除表头和无效行
                                String id = OrderUtils.getUUID();
                                form.setRegistrant(user.getUserName());
                                form.setSendOrgCode(user.getOrgCode());
                                form.setId(id);
                                form.setObState(0);
                                form.setLegalCode(user.getLegalCode());
                                tList.add(form);
                            }
                        }
                    }
                } catch (IOException ex) {
                    logger.info(ex.getMessage());
                }
            }

            else if (".xls".equals(fileType)) {
                // ------------开始读.xls表格数据----------------
                try {
                    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(file.getInputStream());
                    // 循环工作表Sheet
                    for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                        if (hssfSheet == null) {
                            continue;
                        }
                        // 循环行Row
                        for (int rowNum = 3; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                            HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                            if (hssfRow == null) {
                                continue;
                            }
                            Object form = new Object();
                            // 循环列Cell
                            for (int cellNum = 0; cellNum < hssfRow.getLastCellNum(); cellNum++) {
                                HSSFCell hssfCell = hssfRow.getCell(cellNum);
                                if (hssfCell == null) {
                                    continue;
                                }
                                if (hssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                                    hssfCell.getBooleanCellValue();
                                } else if (hssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                    hssfCell.getNumericCellValue();
                                }
                                if (0 == hssfCell.getColumnIndex()) {// getColumnIndex()是列标
                                    String packageNo = getValue(hssfCell);
                                    form.setPackageNo(packageNo);
                                } else if (1 == hssfCell.getColumnIndex()) {
                                    String pn = getValue(hssfCell);
                                    if(StringUtils.isNotBlank(pn)&& pn.length()>5){
                                        result.put("code", ObjectParamCheckConstant.PAGE_NO_IS_VALID.getKey());
                                        result.put("desc", ObjectParamCheckConstant.PAGE_NO_IS_VALID.getValue());
                                        return result;
                                    }
                                    int pageNumber = Integer.parseInt(pn);
                                    form.setPageNumber(pageNumber);
                                } else if (2 == hssfCell.getColumnIndex()) {
                                    String obName = getValue(hssfCell);
                                    form.setObName(obName);
                                } 
                                else if (3 == hssfCell.getColumnIndex()) {
                                    String receiveOrgCode = getValue(hssfCell);
                                    form.setReceiveOrgCode(receiveOrgCode);
                                } else if (4 == hssfCell.getColumnIndex()) {
                                    String sd = getValue(hssfCell);
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                    try {
                                        Date sendDate = sdf.parse(sd);
                                        form.setSendDate(sendDate);
                                    } catch (ParseException ex) {
                                        logger.error(ex.getMessage());
                                    }
                                } else if (5 == hssfCell.getColumnIndex()) {
                                    String rd = getValue(hssfCell);
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                    try {
                                        Date receiveDate = sdf.parse(rd);
                                        form.setReceiveDate(receiveDate);
                                    } catch (ParseException ex) {
                                        logger.info(ex.getMessage());
                                    }
                                } else if (6 == hssfCell.getColumnIndex()) {
                                    String remark = getValue(hssfCell);
                                    form.setRemark(remark);
                                }
                            }
                            // 批量保存数据库
                            if (rowNum != 0 && rowNum != 1 && form.getPackageNo() != null
                                    && StringUtils.isNotBlank(form.getPackageNo())) { // 去除表头和无效行
                                String id = OrderUtils.getUUID();
                                form.setRegistrant(user.getUserName());
                                form.setSendOrgCode(user.getOrgCode());
                                form.setId(id);
                                form.setObState(0);
                                form.setLegalCode(user.getLegalCode());
                                tList.add(form);
                            }
                        }
                    }
                } catch (IOException ex) {
                    logger.info(ex.getMessage());
                    result.put("code", ObjectParamCheckConstant.EXCEL_PARSE_FAIL.getKey());
                    result.put("desc", ObjectParamCheckConstant.EXCEL_PARSE_FAIL.getValue());
                    return result;
                }
            }
        }
        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW); // 事物隔离级别，开启新事务，这样会比较安全些。
        TransactionStatus status = transactionManager.getTransaction(def); // 获得事务状态
        for (Object o : tList) {
            int cnt = 0;
            for (Object o1 : tList) {
                if (StringUtils.isNotBlank(o.getPackageNo()) && StringUtils.isNotBlank(o1.getPackageNo())
                        && o.getPackageNo().equals(o1.getPackageNo())) {
                    cnt++;
                    if (cnt > 1) {
                        result.put("code", ObjectParamCheckConstant.PACKAGENO_IS_DUPLICATED.getKey());
                        result.put("desc", ObjectParamCheckConstant.PACKAGENO_IS_DUPLICATED.getValue());
                        return result;
                    }
                }
            }
        }
        for (Object o : tList) {
            Map<String, String> checkResult = objectParamCheckService.checkBeforeStoreExcel(o);
            if (checkResult != null && !ObjectParamCheckConstant.EXCEL_RECOED_CHECK_SUCCESSED.getKey()
                    .equals(checkResult.get("code"))) {
                return checkResult;
            }
        }

        for (Object t : tList) {
            ObjectOperate objectOperate = new ObjectOperate();
            objectOperate.setId(OrderUtils.getUUID());
            objectOperate.setLegalCode(user.getLegalCode()); // 设置法人代码
            objectOperate.setCreateOrgCode(user.getOrgCode());
            objectOperate.setCreator(user.getUserName());
            objectOperate.setCreateDate(new Date());
            objectOperate.setObId(t.getId());
            objectOperate.setOpType("0");
            if(objectMapper.insert(t) !=1 || objectOperateMapper.insert(objectOperate) !=1){
                transactionManager.rollback(status);
                result.put("code", ObjectParamCheckConstant.EXCEL_IMPORT_FAIL.getKey());
                result.put("desc", ObjectParamCheckConstant.EXCEL_IMPORT_FAIL.getValue());
                return result;
            }

        }
        transactionManager.commit(status);
        result.put("code", ObjectParamCheckConstant.EXCEL_IMPORT_SUCCESS.getKey());
        result.put("desc", ObjectParamCheckConstant.EXCEL_IMPORT_SUCCESS.getValue());
        return result;
            
    }