EasyExcel工具类封装, 做到一个函数完成简单的读取和导出

目录

1. 工具包目录和依赖
2. 工具类
3. Service实现
4. Dto类
5. Controller实现

---

工具包目录和依赖

1. 工具包目录
   
2. 依赖(请根据自己需要自行修改版本)

    <properties>
        <excel.version>2.2.6</excel.version>
        <fastjson.version>1.2.71</fastjson.version>
    </properties>
  
    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>${excel.version}</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>${fastjson.version}</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>

---

工具类

1. IExcelBase

package com.evildoer.common.core.excel;

import java.util.Collection;

/**
 * @description: 让Service实现该接口保存Excel数据到数据库
 * @author: evildoer
 * @datetime: 2021/3/22 15:46
 */
public interface IExcelBase {

    /**
     * @description: 保存单个对象
     * @author: evildoer
     * @datetime: 2021/3/22 15:46
     */
    public boolean saveDto(Object obj);

    /**
     * @description: 保存多个对象
     * @author: evildoer
     * @datetime: 2021/3/22 15:46
     */
    public boolean batchSaveDto(Collection<Object> list);
}

2. ExcelListener

package com.evildoer.common.core.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;

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

import com.alibaba.fastjson.JSON;

/**
 * 监听类
 */
@Slf4j
public class ExcelListener extends AnalysisEventListener<Object> {

    private IExcelBase excelBase;

    public ExcelListener(){}

    public ExcelListener(IExcelBase excelBase){
        this.excelBase = excelBase;
    }

    /**
     * 每隔1000条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;
    List<Object> list = new ArrayList<Object>();

    @Override
    public void invoke(Object data, AnalysisContext context) {
        list.add(data);
        //log.info("解析到一条数据:{}", JSON.toJSONString(data));
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        log.info("所有数据解析完成！");
    }

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

    /**
     * 返回list
     */
    public List<Object> getData() {
        return this.list;
    }
}

3. ExcelWriterFactroy

package com.evildoer.common.core.excel;

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;

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

public class ExcelWriterFactroy extends ExcelWriter {

    private int sheetNo = 1;

    private final OutputStream outputStream;

    public ExcelWriterFactroy(OutputStream outputStream, ExcelTypeEnum typeEnum) {
        super(outputStream, typeEnum);
        this.outputStream = outputStream;
    }

    public ExcelWriterFactroy write(List<?> list, String sheetName,
                                    Class object) {
        this.sheetNo++;
        try {
            Sheet sheet = new Sheet(sheetNo, 0, object);
            sheet.setSheetName(sheetName);
            this.write(list, sheet);
        } catch (Exception ex) {
            ex.printStackTrace();
            try {
                outputStream.flush();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return this;
    }

    @Override
    public void finish() {
        super.finish();
        try {
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

4. ExcelException

package com.evildoer.common.core.excel;

/**
 * Excel 解析 Exception
 */
public class ExcelException extends RuntimeException {
    public ExcelException(String message) {
        super(message);
    }
}

5. ExcelUtil

package com.evildoer.common.core.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;

/**
 * @author evildoer
 * @description: 工具类
 */
public class ExcelUtil {
    /**
     * 读取 Excel(多个 sheet)
     * @param excel    文件
     * @param rowModel 实体类映射
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, IExcelBase excelBase, Class rowModel) {
        ExcelListener excelListener = new ExcelListener(excelBase);
        ExcelReaderBuilder readerBuilder = getReader(excel, excelListener);
        if (readerBuilder == null) {
            return null;
        }
        readerBuilder.head(rowModel).doReadAll();
        return excelListener.getData();
    }

    /**
     * 读取某个 sheet 的 Excel
     * @param excel    文件
     * @param rowModel 实体类映射
     * @param sheetNo  sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, IExcelBase excelBase, Class rowModel, int sheetNo) {
        return readExcel(excel, excelBase, rowModel, sheetNo, 1);
    }

    /**
     * 读取某个 sheet 的 Excel
     * @param excel       文件
     * @param rowModel    实体类映射
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, IExcelBase excelBase, Class rowModel, int sheetNo,
                                         Integer headLineNum) {
        ExcelListener excelListener = new ExcelListener(excelBase);
        ExcelReaderBuilder readerBuilder = getReader(excel, excelListener);
        if (readerBuilder == null) {
            return null;
        }
        ExcelReader reader = readerBuilder.headRowNumber(headLineNum).build();
        ReadSheet readSheet = EasyExcel.readSheet(sheetNo).head(rowModel).build();
        reader.read(readSheet);
        return excelListener.getData();
    }

    /**
     * 导出 Excel ：一个 sheet，带表头
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 Class
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object    映射实体类，Excel 模型
     */
    public static void writeExcel(HttpServletResponse response, List<?> list,
                                  String fileName, String sheetName, Class object) {
        ExcelWriter writer = new ExcelWriterFactroy(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        WriteSheet mainSheet = EasyExcel.writerSheet(0, sheetName)
                .head(object)
                //设置拦截器或自定义样式
                .registerWriteHandler(getStyleStrategy()).build();
        writer.write(list, mainSheet);
        writer.finish();
    }

    /**
     * 导出 Excel ：多个 sheet，带表头
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 Class
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object    映射实体类，Excel 模型
     */
    public static ExcelWriterFactroy writeExcelWithSheets(HttpServletResponse response, List<?> list,
                                                          String fileName, String sheetName, Class object) {
        ExcelWriterFactroy writer = new ExcelWriterFactroy(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        WriteSheet sheet = EasyExcel.writerSheet(0, sheetName)
                .head(object)
                //设置拦截器或自定义样式
                .registerWriteHandler(getStyleStrategy()).build();
        writer.write(list, sheet);
        return writer;
    }

    //设置样式 去除默认表头样式及设置内容居中
    public static HorizontalCellStyleStrategy getStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
//        contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 11);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 导出文件时为Writer生成OutputStream
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
        //创建本地文件
        String filePath = fileName + ".xlsx";
        File dbfFile = new File(filePath);
        try {
            if (!dbfFile.exists() || dbfFile.isDirectory()) {
                dbfFile.createNewFile();
            }
            fileName = new String(filePath.getBytes(), "ISO-8859-1");
            response.addHeader("Content-Disposition", "filename=" + fileName);
            return response.getOutputStream();
        } catch (IOException e) {
            throw new ExcelException("创建文件失败！");
        }
    }

    /**
     * 返回 ExcelReader
     * @param excel         需要解析的 Excel 文件
     * @param excelListener 监听器
     */
    private static ExcelReaderBuilder getReader(MultipartFile excel,
                                                ExcelListener excelListener) {
        String filename = excel.getOriginalFilename();
        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            throw new ExcelException("文件格式错误！");
        }
        InputStream inputStream;
        try {
            inputStream = new BufferedInputStream(excel.getInputStream());
            return EasyExcel.read(inputStream, excelListener);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}

---

Service实现

1. IExamService接口(继承上面的IExcelBase在Service实现类写存储逻辑)

public interface IExamService extends IExamBase {

}

2. ExamServiceImpl(实现Excel导入逻辑)

@Service
public class ExamServiceImpl implements IExamService {

    @Override
    public boolean saveDto(Object obj) {
    	// 保存Excel读取的单个数据到数据库
        return true;
    }

    @Override
    public boolean batchSaveDto(Collection<Object> list) {
        // 批量保存Excel读取的数据到数据库
        return true;
    }
}

---

Dto类(你的Excel模板类)

@Data
public class ExamDto implements Serializable {
    /**
     * 考试名称
     */
    @ColumnWidth(18)
    @ExcelProperty(value = "考试名称", index = 0)
    private String name;

    /**
     * 科目
     */
    @ColumnWidth(12)
    @ExcelProperty(value = "科目", index = 1)
    private String subject;

    /**
     * 试卷名称
     */
    @ColumnWidth(18)
    @ExcelProperty(value = "试卷名称", index = 2)
    private String paper;

    /**
     * 报名考试人数
     */
    @ColumnWidth(12)
    @ExcelProperty(value = "报名人数", index = 3)
    private Integer person;

    /**
     * 实际考试人数
     */
    @ColumnWidth(18)
    @ExcelProperty(value = "实际考试人数", index = 4)
    private Integer present;

    /**
     * 考试平均分
     */
    @ColumnWidth(9)
    @ExcelProperty(value = "平均分", index = 5)
    private BigDecimal markScore;

    /**
     * 开始时间
     */
    @ColumnWidth(20)
    @ExcelProperty(value = "开始时间", index = 6)
    private String startTime;

    /**
     * 结束时间
     */
    @ColumnWidth(20)
    @ExcelProperty(value = "结束时间", index = 7)
    private String endTime;
}

---

Controller实现

@RestController
@RequestMapping("/exam")
@Slf4j
public class ExamController {

    @ApiOperation(value = "导入Excel", httpMethod = "POST")
    @ApiImplicitParam(name = "excel", value = "Excel文件", required = true, dataType = "MultipartFile")
    @PostMapping("/excel")
    public CommonResult readExcel(@NotNull MultipartFile excel) {
        // return CommonResult.success(ExcelUtil.readExcel(excel, ExamDto.class, 0));     // 保存一个sheet
        return CommonResult.success(ExcelUtil.readExcel(excel, examService, ExamDto.class));
    }

    @ApiOperation(value = "导出Excel", httpMethod = "Get")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "excelName", value = "导出Excel名称", paramType = "path", dataType = "String"),
            @ApiImplicitParam(name = "name", value = "考试名称", paramType = "path", dataType = "String")
            })
    @GetMapping("/excel/{excelName}")
    public CommonResult writeExcel(@PathVariable("excelName") String excelName,
                             @RequestParam(value="name",required = false) String name,
                             HttpServletResponse response) {
        ExamQuery query = new ExamQuery();
        query.setName(name);
        List<ExamDto> list = examService.listAllByQuery(query);
        String sheetName = "sheet";
        ExcelUtil.writeExcel(response, list, excelName, sheetName, ExamDto.class);
        return CommonResult.success();
    }
    
    //导出的 Excel 拥有多个 sheet
    //public void writeExcelWithSheets(HttpServletResponse response) throws IOException {
    //    List<ExamDto> list = getList();
    //    String fileName = "一个 Excel 文件";
    //    String sheetName1 = "第一个 sheet";
    //    String sheetName2 = "第二个 sheet";
    //    String sheetName3 = "第三个 sheet";
    //
    //    ExcelUtil.writeExcelWithSheets(response, list, fileName, sheetName1, new ExamDto())
    //            .write(list, sheetName2, new ExamDto())
    //            .write(list, sheetName3, new ExamDto())
    //            .finish();
    //}
}

创建IServiceBase接口有两个优点:

1. 是可以在saveDto和batchSaveDto方法内部写复杂逻辑
2. 可以根据自己需求在IServiceBase添加新的方法

PS：

1. 记得修改你的Service实现类，将数据存储到数据库
2. 由于我在ExcelListener类只使用了batchSaveDto方法，所以你的存储逻辑只写在batchSaveDto方法内即可，写在saveDto方法内是无效的

参考文献：
https://github.com/alibaba/easyexcel
https://github.com/HowieYuan/easyexcel-encapsulation