基于EasyExcel的数据导入导出（复制可用）

Posted on by hackdl

目录

 

前言：

新建SpringBoot项目，引入下面的依赖

数据导入导出执行原理和思路：

用户端逻辑：

后台开发逻辑：

代码实现

下拉框策略

批注策略

数据读取监听

Excel工具类

创建导入数据模板类

创建数据导出模板

Web接口

结果展示

模板下载

数据导入

数据导出

 

前言：

代码复制粘贴即可用，主要包含的功能有Excel模板下载、基于Excel数据导入、Excel数据导出。

根据实际情况修改一些细节即可，最后有结果展示，可以先看下结果，是否是您想要的。

台上一分钟，台下60秒，不喜勿喷。

觉得有用，麻烦点个赞和关注。

新建SpringBoot项目，引入下面的依赖

注：这个依赖已经整合了 poi 和poi-ooxm，无需单独引入。

如有对于POI和Excel有任何疑问，可移步1小时学会 POI 和 EasyExcel （超级详细）_涉世未深果子狸的博客-CSDN博客_easyexcel poi

        
        
            com.alibaba
            easyexcel
            2.2.7

数据导入导出执行原理和思路：

用户端逻辑：

1. 数据导入

        用户先下载模板，根据模板填入数据，然后点击上传；

2. 数据导出

        用户在界面选择需要导出的数据（导出条件），点击导出。

后台开发逻辑：

1. 模板下载

        利用easyExcel生成文件，然后将文件放进响应流中，同时设置响应头为文件下载，浏览器收到响应之后，回去解析流中的内容，然后进行下载。

2. 文件上传

        用在填写好Excel内容之后，会以文件上传的形式，将文件上传到服务端，此时，我们只需要利用EasyExcel将文件流中的数据读出来即可。

3. 数据导出

        后台在接收到用户的数据导出请求之后，会根据请求中的筛选条件，查询对应数据，再将对应的数据填充进对应的导出模板中，以流的形式响应给浏览器。其实和模板下载的差不错，只是模板下载没有数据，数据导出有数据而已。

 

代码实现

说明：以下代码，是我根据具体业务编写之后整理出来的，会有不足的地方，欢迎指教！

下拉框策略

如果没有此需求，可以不要此类。

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.Map;
import java.util.TreeMap;

/**
 * 这个类的作用主要是给列增加下拉框
 * 主要是为了方便用户填写数据
 */
public class CustomSheetWriteHandler implements SheetWriteHandler {

    /**
     * 存放下拉内容的集合
     * key为列的下标， value为下拉内容数组
     */
    private final Map map = new TreeMap();

    /**
     * 工作簿下标，从0开始
     */
    private int index = 0;

    /**
     * 给多少行添加下拉框，这里默认给2000行
     */
    private final int batchSize = 2000;



    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    /**
     * 宝藏在此：如果下拉框内容总的长度超过255，会导致Cell有下拉框，但是下拉内容显示不了，
     * 这时我们可以新建一个sheet，将其隐藏，然后将里面的内容引用到我们的下拉框列就可以。
     * 值得细品
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // excel下标从0开始，这里第二列的下拉选择内容
        map.put(1, new String[]{"下拉内容一", "下拉内容二"});
        // excel下标从0开始，这里第三列的下拉选择内容
        map.put(3, new String[]{"北京市", "上海市", "重庆市", "天津市"});

        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
        map.forEach((k, v) -> {
            // 创建sheet，突破下拉框255的限制
            // 获取一个workbook
            Workbook workbook = writeWorkbookHolder.getWorkbook();
            // 定义sheet的名称
            String sheetName = "sheet" + k;
            // 1.创建一个隐藏的sheet 名称为 proviceSheet
            Sheet proviceSheet = workbook.createSheet(sheetName);
            // 从第二个工作簿开始隐藏
            this.index++;
            // 设置隐藏
            workbook.setSheetHidden(this.index, true);
            // 2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
            for (int i = 0, length = v.length; i

 

批注策略

给表头添加批注，没有此需求可以不要

import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

/**
 * 自定义拦截器.新增注释,第一行头加批注
 * 这个类的作用主要是给表头添加批注
 */
@Slf4j
public class CommentWriteHandler implements RowWriteHandler {
    @Override
    public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        Sheet sheet = writeSheetHolder.getSheet();
        Drawing> drawingPatriarch = sheet.createDrawingPatriarch();

        // 在第一行 第二列创建一个批注
        Comment comment1 =
                drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)0, 0, (short)1, 1));
        // 输入批注信息
        comment1.setString(new XSSFRichTextString("批注1"));
        // 将批注添加到单元格对象中
        sheet.getRow(0).getCell(0).setCellComment(comment1);

        Comment comment2 =
                drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)1, 0, (short)2, 1));
        // 输入批注信息
        comment2.setString(new XSSFRichTextString("批注2"));
        // 将批注添加到单元格对象中
        sheet.getRow(0).getCell(1).setCellComment(comment2);

        Comment comment3 =
                drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)2, 0, (short)3, 1));
        // 输入批注信息
        comment3.setString(new XSSFRichTextString("批注3"));
        // 将批注添加到单元格对象中
        sheet.getRow(0).getCell(2).setCellComment(comment3);
    }
}

 

数据读取监听

导入数据时，程序解析和读取数据用，必须要！！！

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

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

/**
 * Excel数据解析监听器， 数据解析方法异步执行
 * @param    Excel中数据的类型
 */
@Getter
@Setter
@NoArgsConstructor
public class ExcelListener extends AnalysisEventListener {
    // 加入一个判断标签，判断数据是否已经读取完
    private volatile boolean retryLock = false;

    // 解析完成后的数据集合, 监听对象初始化之后，立即初始化集合对象
    private final List dataList = new ArrayList();

    // 每次最多导入条数
    private final int batchSize = 2000;


    /**
     * 获取解析后的数据集合， 如果数据还没有被解析完成，会对读取该集合的线程进行阻塞，直到数据读取完成之后，进行解锁。
     * 如果一次导入数据超过batchSize条，则以抛异常的形式阻止导入数据
     * @return  解析后的数据集合
     */
    public List getDataList() {
        while (true){
            if (retryLock){
                if (dataList.size() > batchSize){
                    // 手动清空数据内存数据，减少内存消耗
                    dataList.clear();
                    throw new RuntimeException("一次最多导入"+ batchSize +"条数据");
                } else {
                    return dataList;
                }
            }
        }
    }

    /**
     * Excel每解析一行数据，就会调用一次该方法
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     *            analysis context
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        dataList.add(data);
    }

    /**
     * 读取表头内容
     * @param headMap   表头部数据
     * @param context   数据解析上下文
     */
    @Override
    public void invokeHeadMap(Map headMap, AnalysisContext context) {
        //System.out.println("表头：" + headMap);
    }

    /**
     * 流中的数据解析完成之后，就会调用此方法
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 数据解析完成，解锁
        retryLock = true;
    }

    /**
     * 解析过程如果发生异常，会调用此方法
     * @param exception
     * @param context
     */
    @Override
    public void onException(Exception exception, AnalysisContext context){
        throw new RuntimeException("Excel数据异常，请检查或联系管理员！");
    }
}

 

Excel工具类

封装统一的Excel操作入口

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
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 lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

@Slf4j
public class ExcelUtil {
    /**
     * 导出数据为excel文件
     *
     * @param filename       文件名称
     * @param dataResult     集合内的bean对象类型要与clazz参数一致
     * @param clazz          集合内的bean对象类型要与clazz参数一致
     * @param response       HttpServlet响应对象
     */
    public static void export(String filename, List> dataResult, Class> clazz, HttpServletResponse response) {
        response.setStatus(200);
        OutputStream outputStream = null;
        ExcelWriter excelWriter = null;
        try {
            if (StringUtils.isBlank(filename)) {
                throw new RuntimeException("'filename' 不能为空");
            }
            String fileName = filename.concat(".xlsx");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            outputStream = response.getOutputStream();

            // 根据不同的策略生成不同的ExcelWriter对象
            if (dataResult == null){
                excelWriter = getTemplateExcelWriter(outputStream);
            } else {
                excelWriter = getExportExcelWriter(outputStream);
            }

            WriteTable writeTable = EasyExcel.writerTable(0).head(clazz).needHead(true).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(fileName).build();
            // 写出数据
            excelWriter.write(dataResult, writeSheet, writeTable);

        } catch (Exception e) {
            log.error("导出excel数据异常：", e);
            throw new RuntimeException(e);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
            if (outputStream != null) {
                try {
                    outputStream.flush();
                    outputStream.close();
                } catch (IOException e) {
                    log.error("导出数据关闭流异常", e);
                }
            }
        }
    }

    /**
     * 根据不同策略生成不同的ExcelWriter对象， 可根据实际情况修改
     * @param outputStream  数据输出流
     * @return  模板下载ExcelWriter对象
     */
    private static ExcelWriter getTemplateExcelWriter(OutputStream outputStream){
        return EasyExcel.write(outputStream)
                .registerWriteHandler(new CommentWriteHandler())        //增加批注策略
                .registerWriteHandler(new CustomSheetWriteHandler())    //增加下拉框策略
                .registerWriteHandler(getStyleStrategy())               //字体居中策略
                .build();
    }

    /**
     * 根据不同策略生成不同的ExcelWriter对象， 可根据实际情况修改
     * @param outputStream  数据输出流
     * @return  数据导出ExcelWriter对象
     */
    private static ExcelWriter getExportExcelWriter(OutputStream outputStream){
        return EasyExcel.write(outputStream)
                .registerWriteHandler(getStyleStrategy())   //字体居中策略
                .build();
    }

    /**
     *  设置表格内容居中显示策略
     * @return
     */
    private static HorizontalCellStyleStrategy getStyleStrategy(){
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //设置头字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)13);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        // 内容策略
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        // 设置内容水平居中
        writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, writeCellStyle);
    }

    /**
     * 根据Excel模板，批量导入数据
     * @param file  导入的Excel
     * @param clazz 解析的类型
     * @return  解析完成的数据
     */
    public static List> importExcel(MultipartFile file, Class> clazz){
        if (file == null || file.isEmpty()){
            throw new RuntimeException("没有文件或者文件内容为空！");
        }
        List dataList = null;
        BufferedInputStream ipt = null;
        try {
            InputStream is = file.getInputStream();
            // 用缓冲流对数据流进行包装
            ipt = new BufferedInputStream(is);
            // 数据解析监听器
            ExcelListener listener = new ExcelListener();
            // 读取数据
            EasyExcel.read(ipt, clazz,listener).sheet().doRead();
            // 获取去读完成之后的数据
            dataList = listener.getDataList();
        } catch (Exception e){
            log.error(String.valueOf(e));
            throw new RuntimeException("数据导入失败！" + e);
        }
        return dataList;
    }

}

 

创建导入数据模板类

可以理解为这个类是我们和客户之间的约定，程序根据这个模板类生成对应的Excel文件，客户根据Excel文件将数据填充进来。然后用户将填充好的Excel文件上传到我们的程序中，我们还得根据这个模板类来解析读取用户填充的数据。

根据实际业务调整，上面的工具类会根据提供的模板生成对应的Excel文件。

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import lombok.experimental.Accessors;

import javax.validation.constraints.NotEmpty;
import java.io.Serializable;

/**
 * 数据导入的Excel模板实体
 */
@Data
public class ImportExcelVo implements Serializable {
    private static final long serialVersionUID = 1L;

    @ColumnWidth(20)
    @ExcelProperty(value = "公司名称", index = 0)
    private String name;

    @ColumnWidth(20)
    @ExcelProperty(value = "公司联系电话", index = 1)
    private String phone;

    @ColumnWidth(28)
    @ExcelProperty(value = "公司统一社会信用代码", index = 2)
    private String creditCode;

    @ColumnWidth(15)
    @ExcelProperty(value = "区域", index = 3)
    private String province;

    @ColumnWidth(15)
    @ExcelProperty(value = "公司法人", index = 4)
    private String legalPerson;

    @ExcelProperty(value = "备注", index = 5)
    private String remark;
}

 

创建数据导出模板

根据此模板，向用户展示用户可以看到的字段。

根据实际业务调整，上面的工具类会根据提供的模板生成对应的Excel文件。

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import lombok.experimental.Accessors;

import java.io.Serializable;

/**
 * 资质信息导出实体
 */
@Data   // Lombok注解，用于生成getter setter
@Accessors(chain = true) //Lombok注解，链式赋值使用
public class ExportExcelVo implements Serializable {
    private static final long serialVersionUID = 1L;

    @ColumnWidth(25)
    @ExcelProperty(value = "企业名称", index = 0)
    private String name;

    @ColumnWidth(25)
    @ExcelProperty(value = "社会统一信用代码", index = 1)
    private String creditCode;

    @ColumnWidth(15)
    @ExcelProperty(value = "曾用名", index = 2)
    private String formerName;

    @ColumnWidth(15)
    @ExcelProperty(value = "公司法人", index = 3)
    private String legalPerson;

    @ExcelProperty(value = "区域", index = 4)
    private String province;

    @ExcelProperty(value = "录入时间", index = 5)
    private String createTime;

    @ColumnWidth(15)
    @ExcelProperty(value = "公司股东", index = 6)
    private String stockholder;

    @ExcelProperty(value = "企业联系方式", index = 7)
    private String contact;

}

 

Web接口

import lombok.extern.slf4j.Slf4j;
import com.xxx.xxx.domain.vo.ExportExcelVo;
import com.xxx.xxx.domain.vo.ImportExcelVo;
import com.xxx.xxx.util.CommonResponse;
import com.xxx.xxx.util.ExcelUtil;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 事件控制器
 */
@Slf4j
@RestController
@RequestMapping("/api/excel")
public class ExcelController {

    /**
     * excel模板下载
     */
    @RequestMapping(value = "/template", method = RequestMethod.GET)
    public CommonResponse template(HttpServletResponse response){
        String fileName = "导入模板下载" + System.currentTimeMillis();
        try {
            ExcelUtil.export(fileName, null, ImportExcelVo.class, response);
        } catch (Exception e) {
            return CommonResponse.error("模板下载失败" + e.getMessage());
        }
        return CommonResponse.success("模板下载成功！");
    }

    /**
     * Excel批量导入数据
     * @param file 导入文件
     */
    @RequestMapping(value = "/import", method = RequestMethod.POST)
    public CommonResponse importEvents(MultipartFile file){
        try {
            List> list = ExcelUtil.importExcel(file, ImportExcelVo.class);
            System.out.println(list);
            return CommonResponse.success("数据导入完成");
        } catch (Exception e) {
            return CommonResponse.error("数据导入失败！" + e.getMessage());
        }
    }


    /**
     * excel数据导出
     * @param size  导出条数， 也可以是用户需要导出数据的条件
     * @return
     */
    @RequestMapping(value = "/export", method = RequestMethod.GET)
    public CommonResponse export(Long size, HttpServletResponse response){
        // 模拟根据条件在数据库查询数据
        ArrayList excelVos = new ArrayList();
        for (int i = 1; i

至此整个Excel导入导出代码完毕！

 

结果展示

模板下载

  1. 浏览器访问模板下载接口，然后会根据接口返回的内容进行解析

 

  1. Excel文件查看

 

数据导入

  1. 填充数据，在下载下来的模板中填入数据

  1. 利用PostMan进行文件上传

  1. 控制台输出

 

数据导出

  1. 浏览器访问数据导出接口

  1. 内容展示

 

 

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: