Excel导入

     * <p>解析表格数据并插入临时表</p>
     *
     * @param excelFiles 请求参数对象list
     * @param request    请求对象
     * @return Map
     * @apiNote zhiwei.zhang, 2018-1-3
     */
    @RequestMapping(value = "/uploadFromExcel", method = RequestMethod.POST)
    public Map<String, Object> uploadFromExcel(@RequestParam("excelFiles") List<MultipartFile> excelFiles, HttpServletRequest request) {

        IRequest requestContext = createRequestContext(request);

        Locale locale = IRequestUtils.getLocale(requestContext.getLocale());

        Map<String, Object> importDate = new HashMap<>(7);

        // 判断enctype属性是否为multipart/form-data
        boolean isMultipart = ServletFileUpload.isMultipartContent(request);

        //若enctype属性不为multipart/form-data，则返回错误信息
        if (!isMultipart) {

            String errorMessage = messageSource.getMessage(PromptConstants.MESSAGE_FILE_TYPE_ERROR, null, locale);

            importDate.put("message", errorMessage);

            importDate.put("success", false);

            return importDate;

        }
        try {

            //用导入入口方法，解析表格,插入临时表
            importDate = commonImportService.analysisData(requestContext, excelFiles.get(0));
        } catch (FileUploadException e) {

            String errorMessage = messageSource.getMessage(PromptConstants.MESSAGE_ERROR_FILE, null, locale);

            importDate.put("message", errorMessage);

            importDate.put("success", false);

            return importDate;
        } catch (Exception e) {

            String errorMessage = "";

            if (e.getCause() != null) {

                errorMessage = e.getCause().getMessage();

            } else {

                errorMessage = e.getMessage();

            }

            importDate.put("message", errorMessage);

            importDate.put("success", false);

            return importDate;

        }

        importDate.put("success", true);

        return importDate;
    }

 @Transactional(propagation = Propagation.REQUIRED, rollbackFor = {Exception.class, RuntimeException.class})
    public Map<String, Object> analysisData(IRequest request, MultipartFile file) throws Exception {

        Map<String, Object> resultMap = new HashMap<>(3);

        InputStream inputStream = null;

        //创建新的工作簿对象
        Workbook workbook = null;

        try {

            //获取输入流
            inputStream = file.getInputStream();

            String originalFileName = file.getOriginalFilename();

            if (originalFileName.endsWith(FND_COMMON_IMPORT_FILE_ENDWITH_XLSX) || originalFileName.endsWith(FND_COMMON_IMPORT_FILE_ENDWITH_CSV)) {

                //使用XSSF的方式处理Excel文件
                workbook = new XSSFWorkbook(inputStream);

            } else if (originalFileName.endsWith(FND_COMMON_IMPORT_FILE_ENDWITH_XLS)) {

                //使用HSSF的方式处理Excel文件
                workbook = new HSSFWorkbook(inputStream);

            } else {

                //如果不是上述的三种文件格式，抛出异常
                throw new IOException(PromptConstants.MESSAGE_IMPORT_FILE_FORMAT_ERROR);

            }
        } catch (IOException e) {

            //捕获异常
            throw new IOException(PromptConstants.MESSAGE_IMPORT_FILE_OPEN_ERROR);

        } finally {

            //关闭输入流
            if (inputStream != null) {
                inputStream.close();
            }

        }

        //创建临时表集合
        List<CommonImpIface> commonImpIfaces = new ArrayList<>();

        //ID生成算法获取Group ID
        SnowflakeIdWorker idWorker = new SnowflakeIdWorker(0, 0);
        Long groupId = idWorker.nextId();

        resultMap.put("groupId", groupId.toString());

        // 获取sheet页的数量
        int sheetNumb = workbook.getNumberOfSheets();

        for (int numb = 0; numb < sheetNumb; numb++) {

            Sheet sheet = workbook.getSheetAt(numb);

            if (sheet.getPhysicalNumberOfRows() == 0) {

                continue;

            }

            // 循环行
            for (int numbRow = sheet.getFirstRowNum(); numbRow <= sheet.getLastRowNum(); numbRow++) {

                Row row = sheet.getRow(numbRow);

                if (null == row) {

                    continue;

                }

                CommonImpIface commonImpIface = new CommonImpIface();

                commonImpIface.setGroupId(groupId);

                int numbCell = row.getFirstCellNum();

                int lastNum = row.getLastCellNum();

                if (lastNum > 100) {

                    lastNum = 100;

                }

                for (; numbCell < lastNum; numbCell++) {

                    Cell cell = row.getCell(numbCell);

                    String value = null;

                    if (null == cell) {

                        value = "";

                        continue;

                    }

                    // 格式化number
                    DecimalFormat df = new DecimalFormat("#.#########");

                    // String字符日期格式化
                    SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");

                    // 格式化数字
                    DecimalFormat df2 = new DecimalFormat("0.00");

                    switch (cell.getCellTypeEnum()) {

                        case STRING:

                            value = cell.getRichStringCellValue().getString();

                            break;

                        case NUMERIC:

                            if ("General".equals(cell.getCellStyle().getDataFormatString())) {

                                value = df.format(cell.getNumericCellValue());

                            } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {

                                value = sdf.format(cell.getDateCellValue());

                            } else if (HSSFDateUtil.isCellDateFormatted(cell)) {

                                value = sdf.format(cell.getDateCellValue());

                            } else {

                                value = df2.format(cell.getNumericCellValue());

                            }
                            break;

                        case BOOLEAN:

                            value = String.valueOf(cell.getBooleanCellValue());

                            break;

                        case BLANK:

                            value = "";

                            break;

                        default:

                            value = "";

                            break;

                    }

                    PropertyDescriptor descriptor = getPropertyDescriptor(CommonImpIface.class,
                            "column" + (numbCell + 1));

                    Method method = descriptor.getWriteMethod();

                    method.invoke(commonImpIface, new Object[]{value});
                }
                commonImpIfaces.add(commonImpIface);
            }
        }
        fndCommonImpInterfaceService.insert(commonImpIfaces.subList(1, commonImpIfaces.size()), request);

        resultMap.put("data", commonImpIfaces);

        return resultMap;
    }