EasyExcel3.0读写Excel、CSV

EasyExcel3.0读写Excel、CSV

依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

写工具类

package com.xiaobu.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.WriteContextImpl;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.xiaobu.entity.WidthAndHeightData;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.io.IOException;
import java.io.OutputStream;
import java.util.*;

/**
 * @author 小布
 * @version 1.0.0
 * @date on  2021/5/27 16:36
 * {@link WriteContextImpl#finish(boolean onException) 会自动关闭流}
 */
public class EasyExcelUtils<T> {
    /**
     * 写单个表头
     */
    public static List<List<String>> simpleHead(String[] headArr) {
        List<List<String>> headTitles = new ArrayList<>();
        for (String s : headArr) {
            List<String> head = new ArrayList<>();
            head.add(s);
            headTitles.add(head);
        }
        return headTitles;
    }

    /**
     * horizontalCellStyleStrategy
     * 默认策略
     *
     * @return com.alibaba.excel.write.style.HorizontalCellStyleStrategy
     * @author 小布
     * @date 2022/1/17 17:13
     */
    public static HorizontalCellStyleStrategy horizontalCellStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("微软雅黑");
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容格式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("微软雅黑");
        contentWriteFont.setFontHeightInPoints((short) 9);
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }


    /**
     * EasyExcel不支持Map的写入  将LinkedHashMap 转成 List<List<Object>>
     *
     * @param list list
     * @return java.util.List<java.util.List < java.lang.Object>>
     * @author 小布
     * @date 2022/1/25 9:10
     */
    public static List<List<Object>> map2List(List<LinkedHashMap<String, Object>> list) {
        List<List<Object>> excelList = new ArrayList<>();
        for (LinkedHashMap<String, Object> map : list) {
            List<Object> data = new ArrayList<>();
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                data.add(entry.getValue());
                // if (isImage(String.valueOf(entry.getValue()))) {
                //     // 设置超链接
                //     WriteCellData<String> hyperlink = new WriteCellData<>(String.valueOf(entry.getValue()));
                //     HyperlinkData hyperlinkData = new HyperlinkData();
                //     hyperlink.setHyperlinkData(hyperlinkData);
                //     hyperlinkData.setAddress(String.valueOf(entry.getValue()));
                //     hyperlinkData.setHyperlinkType(HyperlinkData.HyperlinkType.URL);
                //     data.add(hyperlink);
                // } else {
                //     data.add(entry.getValue());
                // }
            }
            excelList.add(data);
        }
        return excelList;
    }

    public static Boolean isImage(String value) {
        String s = value.toLowerCase();
        if (s.contains(".jpg")) {
            return true;
        }
        if (s.contains(".png")) {
            return true;
        }
        if (s.contains(".gif")) {
            return true;
        }
        return s.contains(".bmp");
    }

    public static List<List<Object>> map2ListMap(List<String> keysList, List<Map<String, Object>> list) {
        if (null == list || 0 == list.size()) {
            return new ArrayList<>();
        }
        List<List<Object>> excelList = new ArrayList<>();
        for (Map<String, Object> map : list) {
            List<Object> data = new ArrayList<>();
            for (String key : keysList) {
                data.add(map.getOrDefault(key.toLowerCase(Locale.ROOT), map.get(key)));
            }
            excelList.add(data);
        }
        return excelList;
    }

    public static HorizontalCellStyleStrategy horizontalCellStyleStrategyAuto() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("等线");
        headWriteFont.setFontHeightInPoints((short) 11);
        headWriteCellStyle.setShrinkToFit(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容格式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("等线");
        contentWriteFont.setFontHeightInPoints((short) 11);
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setShrinkToFit(true);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * writeWithCustomizeCellStyle
     * 自定义的默认样式写数据
     *
     * @param out       out
     * @param sheetName sheetName
     * @param head      head
     * @param data      data
     * @author 小布
     * @date 2022/1/18 9:53
     */
    public void writeWithCustomize(OutputStream out, String sheetName, List<List<String>> head, List<T> data) throws IOException {
        try {
            EasyExcel.write(out, WidthAndHeightData.class)
                    .registerWriteHandler(horizontalCellStyleStrategy())
                    .sheet(sheetName).head(head)
                    .doWrite(data);
        } finally {
            out.close();
        }

    }

    /**
     * 默认的样式写文件
     */
    public void writeWithDefaultCellStyle(OutputStream out, String sheetName, String[] headArr, List<T> data) throws IOException {
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcelFactory.write(out).build();
            // 动态添加表头，适用一些表头动态变化的场景
            WriteSheet sheet = new WriteSheet();
            sheet.setSheetName(sheetName);
            sheet.setSheetNo(0);
            // 创建一个表格，用于 Sheet 中使用
            WriteTable table = new WriteTable();
            table.setTableNo(1);
            table.setHead(simpleHead(headArr));
            // 写数据
            excelWriter.write(data, sheet, table);
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
            out.close();
        }
    }

    /**
     * writeWithCustomizeExcludeColumn
     * 动态排除要写入的字段
     *
     * @param out                     out
     * @param sheetName               sheetName
     * @param head                    head
     * @param excludeColumnFiledNames excludeColumnFiledNames
     * @param data                    data
     * @author 小布
     * @date 2022/1/26 13:40
     */
    public void writeWithCustomizeExcludeColumn(OutputStream out, String sheetName, List<List<String>> head, Set<String> excludeColumnFiledNames, List<T> data) throws IOException {
        try {
            EasyExcel.write(out, WidthAndHeightData.class).excludeColumnFiledNames(excludeColumnFiledNames)
                    .registerWriteHandler(horizontalCellStyleStrategy())
                    .sheet(sheetName).head(head)
                    .doWrite(data);
        } finally {
            out.close();
        }

    }
}

读

package com.xiaobu.entity;

import cn.hutool.json.JSONUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.DependsOn;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author 小布
 * @version 1.0.0
 * @date on  2021/6/17 16:31
 */
@Slf4j
@DependsOn("springContextUtils")
public class ExcelListener<T> extends AnalysisEventListener<T> {
    /**
     * 默认给一万 过大效率会变低
     */
    private static final int BATCH_COUNT = 10000;
    List<T> list = new ArrayList<>();
    /**
     * 是否保存数据
     */
    Boolean saveData;
    List<String> headList = new ArrayList<>();

    public ExcelListener() {
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSONUtil.toJsonStr(data));
        list.add(data);
        // 把BATCH_COUNT倍数的数据全部save
        if (saveData && list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (saveData) {
            // 把最后的数据统一保存起来 有可能数据不是BATCH_COUNT的整数倍
            saveData();
        }
    }

    /**
     * 读取表头 考虑表头信息如何处理
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        super.invokeHeadMap(headMap, context);
        for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
            headList.add("item" + (entry.getKey() + 1));
        }
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        log.info("存储数据库成功！");
    }

    public List<String> getHeadList() {
        return headList;
    }
}

web下载

注意要关闭流只能在外面关也就是执行完excelWriter.finish()之后 在这里用try-with-resource 语法糖可能会导致先关闭流然后执行excelWriter.finish();导致数据无法写入

正确示例：

    public void exportData(HttpServletResponse response,) {
        ExcelWriter excelWriter = null;
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        String fileName = "export.xlsx";
        // 设置文件头：最后一个参数是设置下载文件名
        response.setHeader("Content-Disposition", "attachment;fileName=" + new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
        // 设置文件ContentType类型，这样设置，会自动判断下载文件类型
        try {
            excelWriter = EasyExcel.write(response.getOutputStream(), WidthAndHeightData.class).registerWriteHandler(EasyExcelUtils.horizontalCellStyleStrategy()).build();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

错误示例：

    public void exportData(HttpServletResponse response) {
        ExcelWriter excelWriter = null;
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        String fileName = "export.xlsx";
        // 设置文件头：最后一个参数是设置下载文件名
        response.setHeader("Content-Disposition", "attachment;fileName=" + new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
        // 设置文件ContentType类型，这样设置，会自动判断下载文件类型
        try (OutputStream out = response.getOutputStream()) {
            excelWriter = EasyExcel.write(out, WidthAndHeightData.class).registerWriteHandler(EasyExcelUtils.horizontalCellStyleStrategy()).build();
            int index = 0;
          
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

编译后的源码(可以看出流先关闭了然后写数据导致excel没有数据)

    public void exportData(HttpServletResponse response) {
        ExcelWriter excelWriter = null;
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        String fileName = "export.xlsx";
        response.setHeader("Content-Disposition", "attachment;fileName=" + new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
        try {
            OutputStream out = response.getOutputStream();
            Throwable var6 = null;

            try {
                excelWriter = ((ExcelWriterBuilder)EasyExcel.write(out, WidthAndHeightData.class).registerWriteHandler(EasyExcelUtils.horizontalCellStyleStrategy())).build();
                List<List<String>> head = EasyExcelUtils.simpleHead(headArr);
                    WriteSheet writeSheet = ((ExcelWriterSheetBuilder)EasyExcel.writerSheet(index, dataSourceName).head(head)).build();
                    excelWriter.write(lists, writeSheet);
                out.flush();
            } catch (Throwable var47) {
                var6 = var47;
                throw var47;
            } finally {
                if (out != null) {
                    if (var6 != null) {
                        try {
                            out.close();
                        } catch (Throwable var46) {
                            var6.addSuppressed(var46);
                        }
                    } else {
                        out.close();
                    }
                }

            }
        } catch (IOException var49) {
            var49.printStackTrace();
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }

        }

    }

下载csv

https://github.com/alibaba/easyexcel/issues/2609

public void exportCsvData(HttpServletResponse response){
        ExcelWriter excelWriter=null;
        response.setContentType("text/csv;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        String fileName="export.csv";
        // 设置文件头：最后一个参数是设置下载文件名
        response.setHeader("Content-Disposition","attachment;fileName="+new String(fileName.getBytes(StandardCharsets.UTF_8),StandardCharsets.ISO_8859_1));
        // 设置文件ContentType类型，这样设置，会自动判断下载文件类型
        try{
        excelWriter=EasyExcel.write(response.getOutputStream(),WidthAndHeightData.class).excelType(ExcelTypeEnum.CSV).charset(Charset.forName("GBK")).registerWriteHandler(EasyExcelUtils.horizontalCellStyleStrategy()).build();
        }catch(IOException e){
        e.printStackTrace();
        }finally{
        // 千万别忘记finish 会帮忙关闭流
        if(excelWriter!=null){
        excelWriter.finish();
        }
        }
        }