使用EasyExcel 2.2.10导入导出表格数据

使用EasyExcel 2.2.10导入导出表格数据

maven官网提示有漏洞，可升级到3.0版本解决。
pom文件：

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.10</version>
</dependency>

ExcelUtils

public class ExcelUtils {
    public ExcelUtils() {
    }

    public static void readExcel(File file, int headLineNum, ReadListener readListener) throws IOException {
        readExcel((InputStream)(new FileInputStream(file)), headLineNum, readListener);
    }

    public static void readExcel(InputStream inputStream, int headLineNum, ReadListener readListener) throws IOException {
        ExcelReaderBuilder excelReaderBuilder = EasyExcelFactory.read(inputStream, readListener);
        excelReaderBuilder.headRowNumber(headLineNum);
        ExcelReader reader = excelReaderBuilder.build();
        reader.read();
    }

    public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName, String sheetName, BaseRowModel object) throws IOException {
        ExcelWriter writer = ((ExcelWriterBuilder)((ExcelWriterBuilder)EasyExcelFactory.write().file(getOutputStream(fileName, response)).head(object.getClass())).autoCloseStream(Boolean.FALSE).excelType(ExcelTypeEnum.XLSX).needHead(Boolean.TRUE)).autoCloseStream(Boolean.FALSE).build();
        ExcelWriterSheetBuilder writerSheetBuilder = (ExcelWriterSheetBuilder)((ExcelWriterSheetBuilder)((ExcelWriterSheetBuilder)(new ExcelWriterSheetBuilder(writer)).sheetName(sheetName).head(object.getClass())).registerWriteHandler(new AbstractVerticalCellStyleStrategy() {
            protected WriteCellStyle headCellStyle(Head head) {
                WriteCellStyle writeCellStyle = new WriteCellStyle();
                writeCellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
                return writeCellStyle;
            }

            protected WriteCellStyle contentCellStyle(Head head) {
                WriteCellStyle writeCellStyle = new WriteCellStyle();
                writeCellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
                return writeCellStyle;
            }
        })).needHead(Boolean.TRUE);
        writerSheetBuilder.doWrite(list);
    }

    public static <T> void writeExcel(HttpServletResponse response, List<List<T>> list, String fileName, String sheetName, List<List<String>> head) throws IOException {
        ExcelWriter writer = ((ExcelWriterBuilder)((ExcelWriterBuilder)EasyExcelFactory.write().file(getOutputStream(fileName, response)).head(head)).autoCloseStream(Boolean.FALSE).excelType(ExcelTypeEnum.XLSX).needHead(Boolean.TRUE)).autoCloseStream(Boolean.FALSE).build();
        AbstractVerticalCellStyleStrategy verticalCellStyleStrategy = new AbstractVerticalCellStyleStrategy() {
            protected WriteCellStyle headCellStyle(Head head) {
                WriteCellStyle writeCellStyle = new WriteCellStyle();
                writeCellStyle.setDataFormat(Short.valueOf((short)0));
                writeCellStyle.setHidden(false);
                writeCellStyle.setLocked(true);
                writeCellStyle.setQuotePrefix(true);
                writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
                writeCellStyle.setWrapped(true);
                writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                writeCellStyle.setRotation(Short.valueOf((short)0));
                writeCellStyle.setIndent(Short.valueOf((short)10));
                writeCellStyle.setFillBackgroundColor(IndexedColors.WHITE1.getIndex());
                writeCellStyle.setShrinkToFit(Boolean.TRUE);
                writeCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
                return writeCellStyle;
            }

            protected WriteCellStyle contentCellStyle(Head head) {
                WriteCellStyle writeCellStyle = new WriteCellStyle();
                writeCellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
                return writeCellStyle;
            }
        };
        ExcelWriterSheetBuilder writerSheetBuilder = (ExcelWriterSheetBuilder)((ExcelWriterSheetBuilder)((ExcelWriterSheetBuilder)(new ExcelWriterSheetBuilder(writer)).sheetName(sheetName).head(head)).registerWriteHandler(verticalCellStyleStrategy)).needHead(Boolean.TRUE);
        writerSheetBuilder.doWrite(list);
    }

    public static <T> void writeExcel(HttpServletResponse response, List<List<String>> head, List<List<T>> list, String fileName, String sheetName, SheetWriteHandler sheetWriteHandler, CellWriteHandler cellWriteHandler) throws IOException {
        ExcelWriter writer = ((ExcelWriterBuilder)((ExcelWriterBuilder)((ExcelWriterBuilder)((ExcelWriterBuilder)EasyExcelFactory.write().file(getOutputStream(fileName, response)).head(head)).autoCloseStream(Boolean.FALSE).excelType(ExcelTypeEnum.XLSX).needHead(Boolean.TRUE)).autoCloseStream(Boolean.FALSE).registerWriteHandler(sheetWriteHandler)).registerWriteHandler(cellWriteHandler)).build();
        AbstractVerticalCellStyleStrategy verticalCellStyleStrategy = new AbstractVerticalCellStyleStrategy() {
            protected WriteCellStyle headCellStyle(Head head) {
                WriteCellStyle writeCellStyle = new WriteCellStyle();
                writeCellStyle.setFillBackgroundColor(IndexedColors.WHITE1.getIndex());
                WriteFont writeFont = new WriteFont();
                writeFont.setBold(false);
                writeFont.setFontHeightInPoints(Short.valueOf((short)10));
                writeCellStyle.setWriteFont(writeFont);
                return writeCellStyle;
            }

            protected WriteCellStyle contentCellStyle(Head head) {
                WriteCellStyle writeCellStyle = new WriteCellStyle();
                writeCellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
                return writeCellStyle;
            }
        };
        ExcelWriterSheetBuilder writerSheetBuilder = (ExcelWriterSheetBuilder)((ExcelWriterSheetBuilder)((ExcelWriterSheetBuilder)(new ExcelWriterSheetBuilder(writer)).sheetName(sheetName).head(head)).registerWriteHandler(verticalCellStyleStrategy)).needHead(Boolean.TRUE);
        writerSheetBuilder.doWrite(list);
    }
}

表格导入及导入模板下载

@Slf4j
public class FileUploadParserUtils {

    @SuppressWarnings({"unchecked", "rawtypes"})
    public static ExcelOutPutDTO outPut(MultipartFile file, Long fileMaxSize, boolean skipFileCheck, int headNum) throws IOException {
        if (file.getSize() > fileMaxSize * 1024) {
            throw new PbServiceException("文件大小超过指定大小");
        }
        if (!skipFileCheck) {
            String fileType = CheckFileTypeUtil.getFileType(file.getInputStream(), "xlsx");
            if (fileType == null || !fileType.equalsIgnoreCase("XLSX")) {
                throw new PbServiceException("文件类型错误");
            }
        }
        log.info("文件格式检查完成");
        ExcelOutPutDTO excelOutPutDTO = new ExcelOutPutDTO();
        ExcelUtils.readExcel(file.getInputStream(), headNum, new ReadListener() {
            public void invokeHeadMap(Map headMap, AnalysisContext context, int rowIndex) {
                if (rowIndex == headNum - 1) {
                    Map<String, Integer> map = new HashMap<>(headMap.size(), 1);
                    headMap.forEach(
                            (key, value) -> map.put((String) value, (Integer) key)
                    );
                    excelOutPutDTO.setHeaderMap(map);
                }
            }
            @Override
            public void onException(Exception e, AnalysisContext analysisContext) throws Exception {

            }

            @Override
            public void invokeHead(Map headMap, AnalysisContext context) {
                invokeHeadMap(ConverterUtils.convertToStringMap(headMap, (AnalysisContext) context.currentReadHolder()), context, context.readRowHolder().getRowIndex());
            }

            @Override
            public void invoke(Object o, AnalysisContext analysisContext) {

                Map<Integer, String> valueMap = (Map<Integer, String>) o;
                excelOutPutDTO.getContentList().add(valueMap);
            }

            @Override
            public void extra(CellExtra cellExtra, AnalysisContext analysisContext) {

            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {

            }

            @Override
            public boolean hasNext(AnalysisContext analysisContext) {
                return true;
            }
        });


        return excelOutPutDTO;
    }

    public static ExcelOutPutDTO outPut(MultipartFile file) throws IOException {
        return outPut(file, 500L, false, 2);
    }

    public static ExcelOutPutDTO outPut(MultipartFile file, int headNum) throws IOException {
        return outPut(file, 500L, false, headNum);
    }

    public static ExcelOutPutDTO outPut(MultipartFile file, boolean skipFileCheck) throws IOException {
        return outPut(file, 500L, skipFileCheck, 2);
    }

    public static ExcelOutPutDTO outPut(MultipartFile file, boolean skipFileCheck, int headNum) throws IOException {
        return outPut(file, 500L, skipFileCheck, headNum);
    }

    public static String safeMapGet(Map<String, Integer> headMap, Map<Integer, String> content, String headName) {
        Integer index = headMap.get(headName);
        if (index != null) {
            return content.getOrDefault(index, "");
        }
        return "";
    }

    @Data
    public static class ExcelOutPutDTO {
        private Map<String, Integer> headerMap;
        private List<Map<Integer, String>> contentList = new ArrayList<>();
    }


    public static void template(HttpServletResponse response, List<String> head) throws IOException {
        List<List<String>> headList = makeHead(head, "数据导入模版");
        ExcelUtils.writeExcel(response, new ArrayList<>(), "文件导入模版", "文件导入模版", headList);
    }

    public static List<List<String>> makeHead(List<String> head, String headName) {
        return head.stream().map(
                item -> {
                    List<String> temp = new ArrayList<>();
                    temp.add(headName);
                    temp.add(item);
                    return temp;
                }
        ).collect(Collectors.toList());
    }

}

表格导出

 @GetMapping("single")
    public void single(PlayerRegQueryParam singleQueryParam, final HttpServletResponse response) {
        List<List<String>> headList = Lists.newArrayList();
        headList.add(Collections.singletonList("活动名称"));
        headList.add(Collections.singletonList("提交时间"));
        headList.add(Collections.singletonList("姓名"));
        headList.add(Collections.singletonList("性别"));
        headList.add(Collections.singletonList("年龄"));
        headList.add(Collections.singletonList("身份证"));
        headList.add(Collections.singletonList("联系方式"));
        headList.add(Collections.singletonList("报名项目"));

        int pn = 0;
        List<List<String>> bodyList = Lists.newArrayList();
        List<PlayerVO> resultList = Lists.newArrayList();
        AtomicLong size = new AtomicLong();
        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        do {
            PaobaPage paobaPage = new PaobaPage();
            paobaPage.setPn(pn);
            paobaPage.setPs(ps);
            resultList = registrationService.singlePage(singleQueryParam, paobaPage).getContent();
            resultList.forEach(
                    singleSignupVO -> {
                        List<String> info = Lists.newArrayList();
                        StringBuilder itemNames = new StringBuilder();
                        info.add(singleSignupVO.getActName());
                        info.add(singleSignupVO.getCreateTime() == null ? "" : singleSignupVO.getCreateTime().format(df));
                        info.add(singleSignupVO.getPlayerName());
                        info.add(singleSignupVO.getSex() == null ? " " : singleSignupVO.getSex().getName());
                        info.add(singleSignupVO.getAge() + "");
                        info.add(singleSignupVO.getCardId());
                        info.add(singleSignupVO.getPhone());
                        singleSignupVO.getSingleItems().forEach(
                                item -> {
                                    itemNames.append(item.getItemName() + " ");
                                }
                        );
                        info.add(itemNames.toString());
                        bodyList.add(info);
                        size.incrementAndGet();
                    }
            );
            pn++;
        } while (CollectionUtil.isNotEmpty(resultList));

        try {
            ExcelUtils.writeExcel(response, headList, bodyList, "活动个人报名数据", "数据", null, null);
        } catch (IOException e) {
            e.printStackTrace();
            throw new PbServiceException("导出错误" + e.getMessage());
        }

    }