工具类

/**
 * 导出Excel工具类
 */
public class EasyExcelUtil<T> {
    /**
     * 单sheet（Map写入）
     * @param response 响应对象
     * @param headList 头部集合
     * @param dataList 数据集合
     */
    public static void write(HttpServletResponse response, List<ExcelHead> headList, List<Map<String, Object>> dataList) throws IOException {
        ExcelWriterBuilder writerBuilder = EasyExcel.write();
        writerBuilder.file(response.getOutputStream());
        writerBuilder.excelType(ExcelTypeEnum.XLSX);
        //日期转换器
        TimestampStringConverter converter = new TimestampStringConverter();
        writerBuilder.registerConverter(converter).registerWriteHandler(new ColumnWidthStyleStrategy())
                .head(convertHead(headList)).sheet("sheet1")
                .doWrite(convertData(headList, dataList));
    }
    /**
     * 多sheet（Map写入）
     * @param response 响应对象
     * @param headMap 头部Map数据
     * @param dataMap 数据Map数据
     * @param sheetMap sheet Map数据
     */
    public static void multipleWrite(HttpServletResponse response
            , Map<String,List<ExcelHead>> headMap
            , Map<String,List<Map<String, Object>>> dataMap
            , Map<String,String> sheetMap) throws IOException {
        //日期转换器
        TimestampStringConverter converter = new TimestampStringConverter();
        ExcelWriter excelWriter = EasyExcel.write()
                .registerConverter(converter)
                .registerWriteHandler(new ColumnWidthStyleStrategy())
                .file(response.getOutputStream())
                .excelType(ExcelTypeEnum.XLSX).autoCloseStream(true).build();
        int i = 0;
        for (Map.Entry<String,List<ExcelHead>> entry : headMap.entrySet()) {
            WriteSheet writeSheet = EasyExcel
                    .writerSheet(i++, sheetMap.get(entry.getKey()))
                    .head(convertHead(entry.getValue())).build();
            excelWriter.write(convertData(entry.getValue(), dataMap.get(entry.getKey())), writeSheet);
        }
        excelWriter.finish();
    }
    /**
     * 实体写入
     * @param response 响应对象
     * @param sheetName sheet名称
     * @param c 实体类
     * @param list 实体数据
     */
    public static <T> void writeSheet(HttpServletResponse response, String sheetName, Class<T> c, List<T> list) throws IOException {
        EasyExcel.write(response.getOutputStream(), c).sheet(sheetName).doWrite(list);
    }
    /**
     * 读取并存储到实体
     * @param fileName 路径地址
     * @param sheetName sheet名称
     * @param c 实体类
     */
    public static <T> List<T> read(String fileName, String sheetName, Class c) {
        List<T> list = new ArrayList();
        EasyExcel.read(fileName, c, new ReadListener<T>() {
            @Override
            public void invoke(T o, AnalysisContext analysisContext) {
                list.add(o);
            }
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            }
        }).sheet(sheetName).doRead();
        return list;
    }
    /**
     * 读取并存储到实体
     * @param fileName 路径地址
     * @param sheetNo 指定sheet
     */
    public static Map<String,Object> readToMap(String fileName, Integer sheetNo) {
        Map<String,Object> result = new HashMap<>();
        List<Map<String,Object>> dataList = new ArrayList();
        //头部map
        Map<String,String> headMap = new HashMap<>();
        //头部拼音map
        Map<String,String> pinyinMap = new HashMap<>();
        EasyExcel.read(fileName,new AnalysisEventListener<Map<Integer, Object>>() {
            @Override
            public void invoke(Map<Integer, Object> data, AnalysisContext context) {
                Map<String,Object> map = new HashMap<>();
                for (Integer key : data.keySet()) {
                    if(key!=null && data.get(key)!=null) {
                        map.put("field_" + key.toString(), data.get(key));
                    }
                }
                dataList.add(map);
            }
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            }
            @Override
            public void invokeHead(Map<Integer, ReadCellData<?>> head, AnalysisContext context) {
                for (Integer key : head.keySet()) {
                    if(key!=null && head.get(key)!=null && StringUtils.isNotBlank(head.get(key).getStringValue())) {
                        headMap.put("field_" + key.toString(), head.get(key).getStringValue());
                        pinyinMap.put("field_" + key.toString(), Pinyin4jUtils.getPinYinHeadChar(head.get(key).getStringValue()));
                    }
                }
            }
        }).sheet(sheetNo).headRowNumber(1).doRead();
        result.put("headMap",headMap);
        result.put("pinyinMap",pinyinMap);
        result.put("dataList",dataList);
        result.put("count",dataList.size());
        return result;
    }
    /**
     * 读取表头并存储到实体
     * @param fileName 路径地址
     * @param sheetNo 指定sheet
     */
    public static Map<String,Object> readToMapHead(String fileName, Integer sheetNo) {
        Map<String,Object> result = new HashMap<>();
        //头部map
        Map<String,String> headMap = new HashMap<>();
        //头部拼音map
        Map<String,String> pinyinMap = new HashMap<>();
        EasyExcel.read(fileName,new AnalysisEventListener<Map<Integer, Object>>() {
            @Override
            public void invoke(Map<Integer, Object> data, AnalysisContext context) {
            }
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            }
            @Override
            public void invokeHead(Map<Integer, ReadCellData<?>> head, AnalysisContext context) {
                for (Integer key : head.keySet()) {
                    if(key!=null && head.get(key)!=null && StringUtils.isNotBlank(head.get(key).getStringValue())) {
                        headMap.put("field_" + key.toString(), head.get(key).getStringValue());
                        pinyinMap.put("field_" + key.toString(), Pinyin4jUtils.getPinYinHeadChar(head.get(key).getStringValue()));
                    }
                }
            }
        }).sheet(sheetNo).headRowNumber(1).doRead();
        result.put("headMap",headMap);
        result.put("pinyinMap",pinyinMap);
        return result;
    }
    /**
     * 头部转换
     * @param headList 头部集合
     */
    private static List<List<String>> convertHead(List<ExcelHead> headList) {
        List<List<String>> list = new ArrayList<>();
        for (ExcelHead head : headList) {
            list.add(Lists.newArrayList(head.getTitle()));
        }
        //沒有搞清楚head的参数为List<List<String>>,用List<String>就OK了
        return list;
    }
    /**
     * 数据转换
     * @param headList 头部集合
     * @param dataList 数据集合
     */
    private static List<List<Object>> convertData(List<ExcelHead> headList, List<Map<String, Object>> dataList) {
        List<List<Object>> result = new ArrayList();
        //对dataList转为easyExcel的数据格式
        for (Map<String, Object> data : dataList) {
            List<Object> row = new ArrayList();
            for (ExcelHead h : headList) {
                Object o = data.get(h.getFieldName());
                //需要对null的处理，比如age的null，要转为-1
                row.add(handler(o, h.getNullValue()));
            }
            result.add(row);
        }
        return result;
    }
    /**
     * 空值处理
     * @param o 数值
     * @param nullValue 空值置换
     */
    private static Object handler(Object o, Object nullValue) {
        return o != null ? o : nullValue;
    }
}

头部实体类（要和工具类在同一个module或项目下）

/**
 * Excel头部实体
 */
public class ExcelHead<T> {
    private String fieldName;
    private String title;
    private T nullValue;
    public ExcelHead(String fieldName, String title) {
        this.fieldName = fieldName;
        this.title = title;
    }
    public ExcelHead(String fieldName, String title, T nullValue) {
        this.fieldName = fieldName;
        this.title = title;
        this.nullValue = nullValue;
    }
    public String getFieldName() {
        return fieldName;
    }
    public void setFieldName(String fieldName) {
        this.fieldName = fieldName;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public T getNullValue() {
        return nullValue;
    }
    public void setNullValue(T nullValue) {
        this.nullValue = nullValue;
    }
}

注意：真正导出表格的是ExcelWriterSheetBuilder类中的方法，前面只是封装，这个是真正导出用到的；这个类是EasyExcel自带的。

日期转换器

/**
 * 日期转换器
 */
public class TimestampStringConverter implements Converter<Timestamp> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return Timestamp.class;
    }
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    @Override
    public WriteCellData<?> convertToExcelData(Timestamp value, ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) {
        WriteCellData cellData = new WriteCellData();
        String cellValue;
        if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
            cellValue = DateUtils.format(value.toLocalDateTime(), null, globalConfiguration.getLocale());
        } else {
            cellValue = DateUtils.format(value.toLocalDateTime(), contentProperty.getDateTimeFormatProperty().getFormat(),
                    globalConfiguration.getLocale());
        }
        cellData.setType(CellDataTypeEnum.STRING);
        cellData.setStringValue(cellValue);
        cellData.setData(cellValue);
        return cellData;
    }
}

EasyExcel导出工具类

工具类

头部实体类（要和工具类在同一个module或项目下）

日期转换器

热门文章

最新文章

相关电子书