日常记录-excel通用导入，导出

 

使用esayexcel 3.1.2

public static <T> List<T> read(Class<T> typeClass, String originalFilename, InputStream inputStream) {
        //读取数据列表
        LinkedList<T> resultList = new LinkedList<>();

        EasyExcel.read(inputStream, typeClass, new AnalysisEventListener<T>() {
            @Override
            public void invoke(T data, AnalysisContext analysisContext) {
                resultList.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                log.info("文件：{}，读取完成，共：{}条", originalFilename, resultList.size());
            }
        }).doReadAllSync();
        return resultList;
    }

    /**
     * 通用导出
     *
     * @param dataList 基础数据
     * @param heads    自定义表头
     * @param response 响应
     */
    public static void download(List dataList, List<List<String>> heads, HttpServletResponse response) {
        downloadExcel(dataList, heads, response);
    }

    private static void downloadExcel(List dataList, List<List<String>> heads, HttpServletResponse response) {
        try {
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = getHorizontalCellStyleStrategy();

            //设置ResponseData
            setResponseData(response, "excel.xlsx");

            EasyExcel.write(response.getOutputStream())
                    //内存处理模式
                    .autoCloseStream(Boolean.FALSE)
                    .inMemory(Boolean.TRUE)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .sheet("sheet")
                    //设置表头
                    .head(heads)
                    //数据集合
                    .doWrite(dataList);
        } catch (Exception e) {
            log.info("下载文件失败" + e.getMessage());
        } finally {
            // 重置response
            resetResponse(response);
        }
    }

    private static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 11);

        headWriteCellStyle.setWriteFont(headWriteFont);

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    // 设置response
    private static void setResponseData(HttpServletResponse response, String fileName) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
    }

    // 重置response
    private static void resetResponse(HttpServletResponse response) {
        response.reset();
        response.setContentType("application/json");
        response.setCharacterEncoding("utf-8");
    }   

 

调用示例：

@PostMapping(value = "readFund")
    public SingleResponse<Integer> readFund(MultipartFile file) throws IOException {
        if (file == null) {
            throw new RuntimeException("文件不能为空");
        }
        List<FundData> readList = ExcelUtil.read(FundData.class, file.getOriginalFilename(), file.getInputStream());

      //todo  可对readList读取的数据做处理 
        return SingleResponse.of(readList .size());
    }

对应的实例类：

@Data
@NoArgsConstructor
@AllArgsConstructor
public class FundData {
    @ExcelProperty(value = "基金代码", index = 0)
    private String fundcode;
    @ExcelProperty(value = "基金名称", index = 1)
    private String fundname;
}