相关推荐recommended
基于EasyExcel的数据导入导出(复制可用)
作者:mmseoamin日期:2023-12-25

目录

 

前言:

新建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 < length; i++) {
                // i:表示你开始的行数 0表示你开始的列数
                proviceSheet.createRow(i).createCell(0).setCellValue(v[i]);
            }
            Name category1Name = workbook.createName();
            category1Name.setNameName(sheetName);
            // 4 $A:$A$N代表 以A列1行开始获取N行下拉数据
            category1Name.setRefersToFormula(sheetName + "!$A:$A$" + (v.length));
            // 5 将刚才设置的sheet引用到你的下拉列表中,1表示从行的序号1开始(开始行,通常行的序号为0的行是表头),50表示行的序号50(结束行),表示从行的序号1到50,k表示开始列序号和结束列序号
            CellRangeAddressList addressList = new CellRangeAddressList(1, batchSize, k, k);
            DataValidationConstraint constraint8 = helper.createFormulaListConstraint(sheetName);
            DataValidation dataValidation3 = helper.createValidation(constraint8, addressList);
            // 阻止输入非下拉选项的值
            dataValidation3.setErrorStyle(DataValidation.ErrorStyle.STOP);
            dataValidation3.setShowErrorBox(true);
            dataValidation3.setSuppressDropDownArrow(true);
            dataValidation3.createErrorBox("提示", "此值与单元格定义格式不一致");
            // validation.createPromptBox("填写说明:","填写内容只能为下拉数据集中的单位,其他单位将会导致无法入仓");
            writeSheetHolder.getSheet().addValidationData(dataValidation3);
        });
    }
}

 

批注策略

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

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 <= size; i++) {
            ExportExcelVo excelVo = new ExportExcelVo();
            excelVo.setContact(String.valueOf(10000000000L + i));
            excelVo.setName("公司名称" + i);
            excelVo.setCreditCode("社会性用代码" + i);
            excelVo.setProvince("地区" + i);
            excelVo.setLegalPerson("法人" + i);
            excelVo.setFormerName("曾用名" + i);
            excelVo.setStockholder("投资人" + i);
            excelVo.setCreateTime(new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒").format(new Date()));
            excelVos.add(excelVo);
        }
        String fileName = "数据导出" + System.currentTimeMillis();
        try {
            ExcelUtil.export(fileName, excelVos, ExportExcelVo.class, response);
        } catch (Exception e) {
            return CommonResponse.error("数据导出成功" + e.getMessage());
        }
        return CommonResponse.success("数据导出失败!");
    }
}

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

 

结果展示

模板下载

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

基于EasyExcel的数据导入导出(复制可用),5c11ec5ef078c2e9ab318f17a309f488.png,第1张

基于EasyExcel的数据导入导出(复制可用),6907756ec7a18d6638f9b59f63ffc9f1.png,第2张

 

  1. Excel文件查看

基于EasyExcel的数据导入导出(复制可用),a44a437f9f788da739ed744901229ef1.png,第3张

 

数据导入

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

基于EasyExcel的数据导入导出(复制可用),a064bf2c17b114a0160b27ea3f9667bb.png,第4张

  1. 利用PostMan进行文件上传

基于EasyExcel的数据导入导出(复制可用),a3788d12dcb0346810a18e7dbeb22317.png,第5张

  1. 控制台输出

基于EasyExcel的数据导入导出(复制可用),b06a00448aed2d5c509620be0466e9a4.png,第6张

 

数据导出

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

基于EasyExcel的数据导入导出(复制可用),836f530736f33286e2945109ef5ab9d8.png,第7张

基于EasyExcel的数据导入导出(复制可用),cb2b1fc7c9c13d5bcafb65fb26958ed6.png,第8张

  1. 内容展示

基于EasyExcel的数据导入导出(复制可用),1d2724006a81bef37ceb7070be7b5a62.png,第9张