@RestController

@RequestMapping(value = "/excel")

public class UploadController {

    @Autowired

    private UploadExcelPoiService uploadExcelPoiService;

    /**

     * 导入excel文件

     *

     * @param file param

     * @return rep

     * @throws ApplicationException ex

     */

    @PostMapping("/temporaryIncentive/import")

    @ResponseBody

    ResponseVO<List<List<MonthValueVO>>> importIncentive(@RequestParam("file") MultipartFile file)

        throws ApplicationException {

        return uploadExcelPoiService.importTemporaryIncentive(file);

    }

}

@Service

public class UploadExcelPoiService implements IUploadExcelPoiService {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelTaskService.class);

    /**

     * import TemporaryIncentive

     *

     * @param file param

     * @return ResponseVO

     * @throws ApplicationException ex

     */

    @Override

    public ResponseVO<List<List<MonthValueVO>>> importTemporaryIncentive(MultipartFile file)

        throws ApplicationException {

        if (file == null) {

            return ResponseUtil.resultError("com.xxx.it.iprice.00010104", new ArrayList<>());

        }

        String fileName = file.getOriginalFilename();

        if (fileName == null) {

            return ResponseUtil.resultError("com.xxx.it.iprice.00010105", new ArrayList<>());

        }

        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {

            return ResponseUtil.resultError("com.xxx.it.iprice.00010105", new ArrayList<>());

        }

        boolean isExcel2003 = !fileName.matches("^.+\\.(?i)(xlsx)$");

        List<List<MonthValueVO>> monthList = new LinkedList<>();

        File outputFile = null;

        try {

            String outputFilePath = System.getProperty("java.io.tmpdir") + fileName;

            outputFile = new File(outputFilePath);

            file.transferTo(outputFile);

            LOGGER.info("上传文件名{}, 路径{}", fileName, outputFile.getAbsolutePath());

            InputStream is = new FileInputStream(outputFile);

            Workbook wb;

            if (isExcel2003) {

                wb = new HSSFWorkbook(is);

            } else {

                wb = new XSSFWorkbook(is);

            }

            Sheet sheet = wb.getSheetAt(0);

            if (sheet == null) {

                return ResponseUtil.resultError("com.xxx.it.iprice.00010106", new ArrayList<>());

            }

            int lastRowNum = sheet.getLastRowNum();

            if (lastRowNum <= 0) {

                return ResponseUtil.resultError("com.xxx.it.iprice.00010107", new ArrayList<>());

            }

            // 读取首行

            Row row0 = sheet.getRow(0);

            if (row0 == null) {

                return ResponseUtil.resultError("com.xxx.it.iprice.00010107", new ArrayList<>());

            }

            int lastCloNum = row0.getPhysicalNumberOfCells();

            List<MonthValueVO> singleList = new LinkedList<>();

            int rateIndex = 3;

            // 共lastCloNum-rateIndex+1个月

            for (int c = rateIndex; c <= lastCloNum; c++) {

                String month = row0.getCell(c).getStringCellValue();

                MonthValueVO tmp = new MonthValueVO();

                tmp.setMonth(month);

                singleList.add(tmp);

            }

            for (int r = 1; r <= lastRowNum; r++) {

                Row row = sheet.getRow(r);

                if (row == null) {

                    continue;

                }

                List<MonthValueVO> monthValueVoList = new LinkedList<>();

                for (int colIdx = rateIndex; colIdx <= lastCloNum; colIdx++) {

                    String monthRate = row.getCell(colIdx).getStringCellValue();

                    if (!BasicUtil.isValid(monthRate)) {

                        throw new ExcelImportException("com.xxx.it.iprice.00010108", r + 1, colIdx + 1);

                    }

                    MonthValueVO monthValueVo = new MonthValueVO();

                    monthValueVo.setMonth(singleList.get(colIdx - rateIndex).getMonth());

                    monthValueVo.setValue(monthRate.substring(0, monthRate.length() - 1));

                    monthValueVoList.add(monthValueVo);

                }

                monthList.add(monthValueVoList);

            }

        } catch (IOException e) {

            LOGGER.error(e.getMessage(), e);

        } finally {

            if (outputFile != null) {

                outputFile.delete();

            }

        }

        return ResponseUtil.resultSuccess(monthList);

    }

}

    @GET

    @Path("/temporaryIncentive/export")

    @Consumes(MediaTypes.JSON_UTF8)

    void exportTemporaryIncentive(@QueryParam("sortType") String sortType, @QueryParam("versionId") String versionId,

        @Context HttpServletRequest req, @Context HttpServletResponse resp) throws ApplicationException;

private void exportIncentiveToExcel(List<VerticalPriceVO> verticalPriceList, HttpServletRequest req,

        HttpServletResponse resp) {

        if (verticalPriceList == null || verticalPriceList.size() <= 0) {

            return;

        }

        XSSFWorkbook inWb = new XSSFWorkbook();

        final XSSFSheet inSheet = inWb.createSheet("Temporary Incentive");

        int rowIdx = 0;

        int colIdx = 0;

        XSSFRow row1 = inSheet.createRow(rowIdx);

        final CellStyle dataLStyle = setDataCellStyle(inWb, 8, HorizontalAlignment.LEFT, true);

        final CellStyle dataRStyle = setDataCellStyle(inWb, 8, HorizontalAlignment.RIGHT, true);

        createCell("临时激励", dataLStyle, colIdx, row1);

        colIdx += 3;

        VerticalPriceVO single = verticalPriceList.get(0);

        List<MonthValueVO> monthValueVoList = single.getTempIncentiveRate();

        for (MonthValueVO monthValueVo : monthValueVoList) {

            createCell(monthValueVo.getMonth(), dataRStyle, colIdx, row1);

            colIdx++;

        }

        rowIdx++;

        // 先将所有渠道类型国际化信息查到本地，减少服务调用

        Map<String, String> categoryMap = new HashMap<>();

        try {

            List<LookupItemVO> channelCategoryList =

                lookupItemQueryService.findItemListByClassify(LookupConstants.CHANNEL_CATEGORY);

            categoryMap = channelCategoryList.stream()

                .collect(Collectors.toMap(item -> item.getItemCode() + item.getLanguage(), LookupItemVO::getItemName));

        } catch (ApplicationException e) {

            LOGGER.error("getCountryRegionPricingList error --- ", e);

        }

        if (categoryMap.size() <= 0) {

            LOGGER.error("------------ categoryMap.size()<=0 ----------------");

            return;

        }

        for (VerticalPriceVO ele : verticalPriceList) {

            XSSFRow inRow = inSheet.createRow(rowIdx);

            List<MonthValueVO> tempIncentiveRateList = ele.getTempIncentiveRate();

            String lang = CommonUtils.getLanguage();

            String channelTypeName = categoryMap.get(ele.getChannelType() + lang);

            String[] productArr = new String[] {ele.getOfferingName(), channelTypeName, ele.getDirectAccountName()};

            int colIndex = 0;

            for (; colIndex < productArr.length; colIndex++) {

                createCell(productArr[colIndex], dataLStyle, colIndex, inRow);

            }

            for (MonthValueVO monthValueVo : tempIncentiveRateList) {

                String tempIncentiveRate = monthValueVo.getValue();

                String percent = BasicUtil.isBigDecimal(tempIncentiveRate);

                createCell(percent, dataRStyle, colIndex, inRow);

                colIndex++;

            }

            rowIdx++;

        }

        String fileName = "临时激励";

        if (Constants.LANGUAGE_EN_US.equalsIgnoreCase(CommonUtils.getLanguage())) {

            fileName = "temporaryIncentive";

        }

        writeToExcel(inWb, req, resp, fileName);

    }

/**

     * 写入excel

     *

     * @param workBook param

     * @param req param

     * @param resp param

     * @param name param

     */

    public void writeToExcel(XSSFWorkbook workBook, HttpServletRequest req, HttpServletResponse resp, String name) {

        DateFormat dfNow = new SimpleDateFormat("yyyyMMddHHmmss");

        String fileName = name + BasicUtil.UNDERLINE + dfNow.format(new Date()) + ".xlsx";

        String checkPath = StringVerifyUtil.dealSpecialChar(fileName);

        File outputFile = new File(checkPath);

        FileOutputStream out = null;

        try {

            out = new FileOutputStream(outputFile);

            workBook.write(out); // 保存Excel文件

        } catch (IOException e) {

            LOGGER.error(e.getMessage(), e);

        } finally {

            if (out != null) {

                try {

                    // 关闭文件流

                    out.close();

                } catch (IOException ex) {

                    LOGGER.error(ex.getMessage(), ex);

                }

            }

        }

        downloadExcel(req, resp, outputFile);

    }

    private void downloadExcel(HttpServletRequest req, HttpServletResponse resp, File file) {

        InputStream in = null;

        DownloadOutput download = new DownloadOutput();

        try {

            // 尝试下载文件

            // 根据浏览器判断下载文件名转义

            String agent = req.getHeader("User-Agent").toLowerCase();

            File checkFile = StringVerifyUtil.getValidDirectoryPath(file);

            in = new FileInputStream(checkFile);

            if (agent.contains("msie") || agent.contains("trident")) {

                download.outputFromStream(req, resp, in, file.getName(), true, 0L);

            } else {

                download.outputFromStream(req, resp, in,

                    new String(file.getName().getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1), false,

                    0L);

            }

        } catch (FileNotFoundException e) {

            LOGGER.error("file: {} not found -- ", file.getPath(), e);

        } catch (Exception e) {

            LOGGER.error("{} download error -- ", file.getPath(), e);

        } finally {

            StreamUtil.closeStreams(in);

            if (in != null) {

                try {

                    in.close();

                } catch (IOException e) {

                    LOGGER.error(e.getMessage(), e);

                }

            }

        }

        if (file.delete()) {

            LOGGER.info("{} delete success -- ", file.getPath());

        }

    }