原生poi导入导出实现

导出模板

    /**
     *  下载导入模板
     */
    @GetMapping("/downloadImportTemplate")
    public void downloadImportTemplate(ContractRuleDTO contractRuleDTO, HttpServletResponse response) throws Exception{
        try {
        	//根据业务获取动态表头
            RegularContractRuleVo regularContractRuleTrendsData = contractRuleService.findRegularContractRuleTrendsData(contractRuleDTO);
            List<LinkedHashMap<String, String>> filedMapList = regularContractRuleTrendsData.getFiledMap();
            LinkedHashMap<String, String> titleMap = filedMapList.get(0);
            if(CollectionUtil.isEmpty(titleMap)){
                throw new ServiceException("表头不能为空");
            }
            Iterator<String> iterator = titleMap.keySet().iterator();
            ArrayList<String> titleList = new ArrayList<>();
            while (iterator.hasNext()) {
                String key = iterator.next();
                String value = titleMap.get(key);
                titleList.add(value);
            }
            // 文件名编码，解决乱码问题
            String fileName = "批量导入模板.xlsx";
            fileName = URLEncoder.encode(fileName, "utf-8");
            // 设置请求
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-download");
            response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);
            final String sheetName = "批量导入XXX";// 设置EXCEL默认工作表空间名称
            //调用下面的Excel工具类
            DynamicExcelUtil.getExcelTitleTemplate(response, sheetName, titleList,null);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

批量导入(原生POI模式)

    @PostMapping("/batchImportContractQuotation")
    public R batchImportContractQuotation(@RequestParam(name = "file") MultipartFile file,ContractRuleVo contractRuleVo){
        try {
            String s = basicRatePricingService.batchImportContractQuotation(file, contractRuleVo);
            if(!"操作成功".equals(s)){
                return R.fail(s);
            }
            return R.success(s);
        }catch (Exception e){
            return R.success(e.getMessage());
        }
    }


    /**
     * 批量导入(原生POI模式)
     * @param multipartFile
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public String batchImportContractQuotation(MultipartFile multipartFile,ContractRuleVo contractRuleVo){
        Workbook wb = null;
        //业务条件校验
        verifyData(contractRuleVo);
        //获取规则id
        Long ruleId = contractRuleVo.getContractRuleDTO().getRuleId();
        //根据规则id查找规则基本信息
        MmsQuotationRuleInfoVo mmsQuotationRuleInfoVo = mmsQuotationRuleService.selectMmsQuotationRuleById(ruleId.toString());
        //业务因子
        List<MmsQuotationRuleFactorVo> mmsQuotationRuleFactorVo = mmsQuotationRuleInfoVo.getMmsQuotationRuleFactorVo();
        //阶梯报价
        List<MmsQuotationRuleSegmentedVo> mmsQuotationRuleSegmentedVo = mmsQuotationRuleInfoVo.getMmsQuotationRuleSegmentedVo();
        //维度报价
        List<MmsQuotationRuleDimensionVo> mmsQuotationRuleDimensionVo = mmsQuotationRuleInfoVo.getMmsQuotationRuleDimensionVo();
        try {
            if (multipartFile != null) {
                // 兼容Excel新版本和老版本，xlsx和xls不兼容
                wb = DynamicExcelUtil.getWorkbook(multipartFile);
                // 获取第一页数据
                Sheet sheet = wb.getSheetAt(0);
                // 获取第一页总行数
                int rowCount = sheet.getPhysicalNumberOfRows();
                if(rowCount==0){
                    return "请填写数据";
                }

                //拿第一行的表头
                Row titleRow = sheet.getRow(0);
                // 行不为空
                if (titleRow != null) {
                    LinkedHashMap<Integer,String> headerMap = new LinkedHashMap<>();
                    //获取表头的总列数
                    int lastCellNum = titleRow.getPhysicalNumberOfCells();
                    //总列数
                    int cellCount = lastCellNum+1;
                    for (int i = 0; i < cellCount-1; i++) {
                        //依次拿到表头名称
                        String cellValue = DynamicExcelUtil.getCellValue(titleRow.getCell(i));
                        if(StaticParameter.EFFECTIVE_DATE_LABEL.equals(cellValue)){
                            headerMap.put(i,StaticParameter.EFFECTIVE_DATE);
                        }else if(StaticParameter.EXPIRATION_DATE_LABEL.equals(cellValue)){
                            headerMap.put(i,StaticParameter.EXPIRATION_DATE);
                        }else if(StaticParameter.REMARKS_LABEL.equals(cellValue)){
                            headerMap.put(i,StaticParameter.REMARKS);
                        }
                        //业务因子
                        for (MmsQuotationRuleFactorVo quotationRuleFactorVo : mmsQuotationRuleFactorVo){
                            if(ObjectUtil.equals(cellValue,quotationRuleFactorVo.getColumnName())){
                                headerMap.put(i,StringUtils.toCamelCase(quotationRuleFactorVo.getField()));
                                break;
                            }
                        }
                        if(CollectionUtil.isNotEmpty(mmsQuotationRuleSegmentedVo)){
                            for (MmsQuotationRuleSegmentedVo quotationRuleSegmentedVo : mmsQuotationRuleSegmentedVo) {
                                if(ObjectUtil.equals(cellValue,quotationRuleSegmentedVo.getColumnName())){
                                    headerMap.put(i,quotationRuleSegmentedVo.getPriceName()+StaticParameter.SPLIT_FLAG+quotationRuleSegmentedVo.getId());
                                    break;
                                }
                            }
                        }

                        if(CollectionUtil.isNotEmpty(mmsQuotationRuleDimensionVo)){
                            for (MmsQuotationRuleDimensionVo quotationRuleDimensionVo : mmsQuotationRuleDimensionVo) {
                                if(ObjectUtil.equals(cellValue,quotationRuleDimensionVo.getColumnName())){
                                    headerMap.put(i,quotationRuleDimensionVo.getPriceName()+StaticParameter.SPLIT_FLAG+quotationRuleDimensionVo.getId());
                                    break;
                                }
                            }
                        }
                    }

                    ArrayList<BasicRatePricing> list = new ArrayList<>();
                    // 开始循环取出每一行的数据,从第二行开始
                    for (int i = 1; i < rowCount; i++) {
                        Row dataRow = sheet.getRow(i);
                        BasicRatePricing basicRatePricing = new BasicRatePricing();
                        //1.填充前端传的固定值
                        Map<String,Object> dataMap = BeanUtil.beanToMap(basicRatePricing);
                        for(int j=0;j<cellCount-1;j++){
                            String cellValue = DynamicExcelUtil.getCellValue(dataRow.getCell(j));
                            String value = headerMap.get(j);
                            //2.存储表格中的固定值
                            if(value.indexOf(StaticParameter.SPLIT_FLAG) == -1){
                                if(dataMap.containsKey(value)){
                                    dataMap.put(value,cellValue);
                                }
                            }else {
                                String[] strArr = StringUtils.split(value,StaticParameter.SPLIT_FLAG);
                                if(dataMap.containsKey(strArr[0])){
                                    dataMap.put(strArr[0],strArr[1]);
                                }
                                String valueFile = strArr[0].replace(StaticParameter.RESERVE_FACTOR,StaticParameter.RESERVE_VALUE);
                                if(dataMap.containsKey(valueFile)){
                                    dataMap.put(valueFile,cellValue);
                                }
                            }
                        }
                        BasicRatePricing basicRatePricing2 = BeanUtil.mapToBean(dataMap,BasicRatePricing.class,true);
                        BeanUtil.copyProperties(basicRatePricing2,basicRatePricing);
                        list.add(basicRatePricing);
                    }
                    //批量新增
                    saveBatch(list);
                }
            } else {
                log.info("++++++++++++传入文件为空+++++++++++++");
                return "传入文件为空";
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return "操作成功";
    }

操作Excel的工具类

public class DynamicExcelUtil {

    /**
     * 标题单元格默认长度
     */
    private static final int DEFAULT_COLUMN_SIZE = 20;


    /**
     * 用于动态生成导出模板
     *
     * @param response 用于下载
     * @param sheetName 工作表空间名称
     * @param titleList
     * @param title Excle模板标题，不需要则传null
     * @throws Exception
     */
    public static void getExcelTitleTemplate(HttpServletResponse response, String sheetName,
                                             ArrayList<String> titleList, String title) throws Exception {
        Workbook workBook = new XSSFWorkbook();
        // 生成一个表格
        Sheet sheet = workBook.getSheet(sheetName);
        if (sheet == null) {
            sheet = workBook.createSheet(sheetName);
        }
        // 最新Excel列索引,从0开始
        int lastRowIndex = sheet.getLastRowNum();
        if (lastRowIndex > 0) {
            lastRowIndex++;// 如果已经存在了工作表空间则从下一行开始
        }
        if (!StringUtils.isBlank(title)) {
            // 如果需要合并单元格显示一个大的title
            Row titleRow = sheet.createRow(0);
            Cell cellTiltle1 = titleRow.createCell(0);
            // 单元格设置值
            cellTiltle1.setCellValue(title);
            lastRowIndex++;
        }

        // 设置表格默认列宽度
        sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
        // 产生表格表头列标题行
        Row row = sheet.createRow(0);
        for (int i = 0; i < titleList.size(); i++) {
            Cell cell = row.createCell(i);
            //设值单元格样式
            cell.setCellStyle(createCellHeadStyle(workBook));
            // 填充表头文本
            RichTextString text = new XSSFRichTextString(titleList.get(i));
            cell.setCellValue(text);
        }
        OutputStream outputStream = response.getOutputStream();
        workBook.write(outputStream);// HSSFWorkbook写入流，下载
        outputStream.flush();// 刷新流
        outputStream.close();// 关闭流

    }

    /**
     * 创建单元格表头样式
     *
     * @param workbook 工作薄
     * @return
     */
    private static CellStyle createCellHeadStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置对齐样式
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成字体
        Font font = workbook.createFont();
        font.setColor(IndexedColors.BLACK.index);
        font.setBold(true);
        font.setFontHeightInPoints((short)12);
        // 把字体应用到当前的样式
        style.setFont(font);
        return style;
    }

    /**
     * 获得Cell内容
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                case NUMERIC: // 数字
                    value = cell.getNumericCellValue() + "";
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("0.00").format(cell.getNumericCellValue());
                    }
                    break;
                case STRING: // 字符串
                    value = cell.getStringCellValue();
                    break;
                case BOOLEAN: // Boolean
                    value = cell.getBooleanCellValue() + "";
                    break;
                case FORMULA: // 公式
                    value = cell.getCellFormula() + "";
                    break;
                case BLANK: // 空值
                    value = "";
                    break;
                case ERROR: // 故障
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }

    /**
     * MultipartFile对象转为File对象
     * @param file
     * @return
     * HSSF － 提供读写Microsoft Excel格式档案的功能。
     * XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。
     * HWPF － 提供读写Microsoft Word格式档案的功能。
     * HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
     * HDGF － 提供读写Microsoft Visio格式档案的功能。
     * @throws IOException
     */
    // 兼容Excel新版本和老版本，xlsx和xls不兼容
    public static Workbook getWorkbook(MultipartFile file) throws IOException {
        Workbook workbook = null;
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith("xls")) {//适应2003
            POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
            workbook = new HSSFWorkbook(pois);
        } else if (fileName.endsWith("xlsx")) {//适应2007
            workbook = new XSSFWorkbook(file.getInputStream());
        }
        return workbook;
    }
}