1.导入

前端VUE代码

<el-upload
        class="upload-demo"
        :action="importUrl"
        ref="ImportUpload"
        :multiple="false"
        :on-change="handleChange"
        :before-upload="beforeUpload"
        :on-remove="handleRemove"
        :on-exceed="handleExceed"
        :onError="uploadError"
        :onSuccess="uploadSuccess"
        :file-list="importFile.fileList"
        accept=".csv,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"
>
    <el-button slot="trigger" type="success" >上传excel
    </el-button>
    <div slot="tip" class="el-upload__tip">只支持 xls/xlsx 文件，且不超过10M</div>
</el-upload>

后端控制器

/**
 *文件上传的请求实体
 */
@Data
public class ImportCommand {
    private MultipartFile file;
    private 其他数据;
}

@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
public ResponseDTO<Integer> importExcel(ImportCommand importCommand) {
    ResponseDTO<Integer> responseDTO;
    try {
        InputStream inputStream = importCommand.getFile().getInputStream();
        //后端处理函数
         responseDTO = excelService.importExcel(inputStream);
    } catch (IOException e) {
        responseDTO = new ResponseDTO<>();
        responseDTO = new ResponseDTO<>();
            responseDTO.setStatus(false);
            responseDTO.setMessage("导入失败");
            e.printStackTrace();
    }
    return responseDTO;
}

后端处理函数

//导入接口函数
@Override
public ResponseDTO<Integer> importByExcel(InputStream  inputStream ) {
        ResponseDTO<Integer> responseDTO = new ResponseDTO<>();
        //开启excel监听器
        BaseExcelReadListener listener;
        //只读第一个sheet
        //List<Object> objects = EasyExcel.read(inputStream, listener).sheet().doReadSync();
        //size = objects.size();
        //responseDTO.setMessage(result.getMessage() + "，共 " + (size - dataBeginRowIndex + 1) + " 条");
        //读取全部sheet
        EasyExcel.read(inputStream, listener).doReadAll();
        //这些更具自己的来
        ImportExcelResult result = listener.getResult();
        responseDTO.setStatus(result.getStatus());
        responseDTO.setMessage(result.getMessage());
        return responseDTO;
}

基本监听器(根据需求修改或不要)

public abstract class BaseExcelReadListener<T> extends AnalysisEventListener<T> {
    //返回结果
    protected ImportExcelResult result = ImportExcelResult.UNKNOWN_ERROR;
    public ImportExcelResult getResult() {
        return result;
    }
    public void setResult(ImportExcelResult result) {
        this.result = result;
    }
    protected void setImportResult(ImportExcelResult importExcelResult) {
        result = importExcelResult;
        log.info("[BaseExcelReadListener]setImportResult: " + result.getMessage());
    }
     /**
     * 保存规则实例数据 - 持久化
     */
    protected void saveData(DataModel dataModel) {
    }

}

其中的返回信息枚举(根据需求修改)

public enum ImportExcelResult {
    SUCCESS(0, true, "导入成功"),
    TEMPLATE_HEAD_ERROR(1, false, "导入失败，模板表头不正确"),
    RULE_ERROR(2, false, "导入失败，规则错误，不是当前规则数据"),
    TEMPLATE_DATA_ERROR(3, false, "导入失败，填写数据错误，可能存在名称或条件重复，或者必填值为空"),
    DATA_DUPLICATION_ERROR(4, false, "导入失败，存在重复数据"),
    FAILED(5, false, "导入失败，请稍后重试"),
    UNKNOWN_ERROR(6, false, "导入失败"),
    CHECKED_ERROR(7, false, "导入失败");

    private Integer value;
    private Boolean status;
    private String message;

    public Integer getValue() {
        return value;
    }

    public void setValue(Integer value) {
        this.value = value;
    }

    public Boolean getStatus() {
        return status;
    }

    public void setStatus(Boolean status) {
        this.status = status;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    private ImportExcelResult(Integer value, Boolean status, String message) {
        this.value = value;
        this.status = status;
        this.message = message;
    }

    public static ImportExcelResult getByValue(Integer value) {
        for (ImportExcelResult status : ImportExcelResult.values()) {
            if (status.getValue().equals(value)) {
                return status;
            }
        }
        return null;
    }
}

一个监听器实例

//一个监听器实例
public class ExcelListener extends BaseDataModelImportListener<Map<Integer, String>> {
    /**
     * 通过 AnalysisContext 对象可以获取当前 sheet、以及当前行等数据
     * data 从第 2 行开始，index 为 1
     */
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        log.info("[ExcelListener]解析到一条数据:" + JSON.toJSONString(data));
        ReadRowHolder readRowHolder = context.readRowHolder();
        ReadSheetHolder readSheetHolder = context.readSheetHolder();
        //总行数
        Integer totalRowNumber = readSheetHolder.getApproximateTotalRowNumber();
        //readRowHolder.getRowIndex() 获取当前行号
        Integer currentRowIndex = readRowHolder.getRowIndex();
        //这里进行数据处理 数据是一行一行读取 所以每次data是一行数据 然后每个单元格的数据用data.get(i)来取出
        if(currentRowIndex<5){
        //（除去表头）
        }
        if(currentRowIndex>=5&&currentRowIndex<55){
            String titleData = data.get(0) != null ? data.get(0).trim() : "";
            String valueData = data.get(1) != null ? data.get(1).trim() : "";
            if(出错){
            //base监听器的函数
                setImportResult(ImportExcelResult.TEMPLATE_HEAD_ERROR);
                return;
            }
        }
        if(currentRowIndex==56){
            System.out.println("不管");
        }
        //三级分类加品牌
        if(currentRowIndex>=58){
        //其他处理函数
            handleListData(data, readRowHolder, totalRowNumber);
        }
    }
    //完成所有行的解析调用 用来数据先转换成模型 然后做持久化
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("[ExcelListener] 解析完成");
        //解析完保存函数
        createAndSaveDataModel();
    }

    /**
     * 解析完保存
     */
    private void createAndSaveDataModel() {
        //模型转换 更具需求是否需要或删除
        DataModel dataModel = dataToDataModel();
        //持久化
        saveData(dataModel);
    }
    //判断是否都为数字 -- 用来调皮
    public boolean isDigit(String strNum) {
        Pattern pattern = Pattern.compile("[0-9]{1,}");
        Matcher matcher = pattern.matcher((CharSequence) strNum);
        return matcher.matches();
    }
    /**
     * 转换成  Model
     */
    private DataModel dataToDataModel() {
        DataModel dataModel = new DataModel();
        return dataModel;
    }
}

@Override参考

2.导出

导出工具

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
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 com.alibaba.fastjson.JSON;
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.apache.poi.ss.usermodel.VerticalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;

/**
 * EasyExcel 工具类
 */
@Slf4j
public class EasyExcelUtil {

    /**
     * 一次性导出
     * 不使用模型、自定义表头、自动适应宽度
     *
     * @param response
     * @param dataList
     * @param fileName
     * @param writeSheet
     * @throws Exception
     */
    public static void writeExcelOnce(HttpServletResponse response, List<? extends Object> dataList, String fileName, WriteSheet writeSheet) throws Exception {
        ExcelWriter excelWriter = getExcelWriter(response, fileName);
        excelWriter.write(dataList, writeSheet);
        excelWriter.finish();
    }


    /**
     * 获取 ExcelWriter（用于分批写入）
     * 不使用模型类、自定义表头、自动适应宽度
     *
     * @param response
     * @param fileName
     * @throws Exception
     */
    public static ExcelWriter getExcelWriter(HttpServletResponse response, String fileName) throws Exception {
        return EasyExcel.write(getOutputStream(fileName, response))
                .excelType(ExcelTypeEnum.XLSX)
                .build();
    }

    /**
     * 获取通用 WriteSheet
     * 不使用模型类、自定义表头、自动适应宽度
     *
     * @param headList
     * @param sheetName
     */
    public static WriteSheet getCommonWriteSheet(List<List<String>> headList, String sheetName) {
        WriteSheet writeSheet = new WriteSheet();
        writeSheet.setSheetNo(0);
        writeSheet.setSheetName(sheetName);
        writeSheet.setHead(headList);
        if (writeSheet.getCustomWriteHandlerList() == null) {
            writeSheet.setCustomWriteHandlerList(new ArrayList<>());
        }
        // # 表头策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        // 设置字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // # 内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 自动换行
        contentWriteCellStyle.setWrapped(true);
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        writeSheet.getCustomWriteHandlerList().add(horizontalCellStyleStrategy);
        // 自动适应列宽
        writeSheet.getCustomWriteHandlerList().add(new ColumnWidthStyleStrategy());
        return writeSheet;
    }

    /**
     * 导出通过模型类导出
     * 表头列宽再模型类中通过注解设定
     *
     * @param response
     * @param dataList
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    public static void writeExcelWithModel(HttpServletResponse response, List<? extends Object> dataList, String fileName, String sheetName, Class clazz) throws Exception {
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(getOutputStream(fileName, response), clazz)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet(sheetName)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .doWrite(dataList);
    }

    /**
     * 导出文件时为 Writer 生成 OutputStream.
     *
     * @param fileName 文件名
     * @param response response
     * @return ""
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            throw new Exception("导出excel表格失败!", e);
        }
    }

    /**
     * 读取 Excel 不处理表头
     */
    public static List<LinkedHashMap<Integer, String>> readExcel(InputStream in) {
        return EasyExcel.read(new BufferedInputStream(in))
                .sheet()
                .doReadSync();
    }

    /**
     * 缓冲同步 读取 Excel
     */
    public static List<LinkedHashMap<Integer, String>> readExcel(InputStream in,  List<List<String>> headList) {
        return EasyExcel.read(new BufferedInputStream(in))
                .head(headList)
                .sheet()
                .doReadSync();
    }

    /**
     * 通过反射获取
     */
    public static List<List<String>> getHeadList(Class clazz) {
        Field[] declaredFields = clazz.getDeclaredFields();
        TreeMap<Integer, String> treeMap = new TreeMap<>();
        for (Field field : declaredFields) {
            // 获取原来的访问控制权限
            boolean accessFlag = field.isAccessible();
            // 修改访问控制权限
            field.setAccessible(true);
            // 恢复访问控制权限
            field.setAccessible(accessFlag);
            if (field.isAnnotationPresent(ExcelProperty.class)) {
                ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                treeMap.put(annotation.index(), annotation.value()[0]);
            }
        }
        List<List<String>> headList = new ArrayList<>();
        for (Map.Entry<Integer, String> entry : treeMap.entrySet()) {
            List<String> headColumn = new ArrayList<>();
            headColumn.add(entry.getValue());
            headList.add(headColumn);
        }
        return headList;
    }

    /**
     * 将对象转成 List<List<String>>, 属性值 value 的 List
     *
     * @throws IllegalAccessException
     */
    public static List<List<Object>> toListListData(List<?> objects) throws IllegalAccessException {
        List<List<Object>> listListData = new ArrayList<>();
        for (Object object : objects) {
            ArrayList<Object> listData = new ArrayList<>();
            Class clazz = object.getClass();
            Field[] declaredFields = clazz.getDeclaredFields();
            for (Field field : declaredFields) {
                // 获取原来的访问控制权限
                boolean accessFlag = field.isAccessible();
                // 修改访问控制权限
                field.setAccessible(true);
                Object value = null;
                value = field.get(object);
                // 恢复访问控制权限
                field.setAccessible(accessFlag);
                if (null != value && StringUtils.isNotBlank(value.toString())) {
                    //如果是List, 将List转换为json字符串
                    if (value instanceof Integer) {
                        listData.add(value);
                    } else if (value instanceof Long) {
                        listData.add(value);
                    } else if (value instanceof String) {
                        listData.add(value.toString());
                    } else {
                        listData.add(JSON.toJSONString(value));
                    }
                } else {
                    listData.add("-");
                }
            }
            listListData.add(listData);
        }
        return listListData;
    }
}

一行一行写入数据 中间可以处理

public void exportToExcel(ExportReqBody reqBody, HttpServletResponse response) {
        String fileName = reqBody.getName() + "导出数据";//文件名字
        //根据请求体的模型获取需要导出的表头和内容信息 List<String>理解为一行数据
        List<List<String>> headList = getHeadList(reqBody);
        List<List<String>> commentList = getCommentList(reqBody);
        ExcelWriter excelWriter;
        WriteSheet sheet1;
        try {
//            获取 ExcelWriter（用于分批写入）并且把这个excelWriter写入response
            excelWriter = EasyExcelUtil.getExcelWriter(response, fileName);
//            获取通用 WriteSheet 并且把表头数据写入
            sheet1 = EasyExcelUtil.getCommonWriteSheet(headList, "sheet1");
        } catch (Exception e) {
            log.error("[exportToExcel]导出数据失败", e);
            return;
        }
        // 分批导出数据
        int batchSize = 1000;
        int begIndex = 0;
        int total = commentList.size();
        int endIndex = Math.min(total, batchSize);
        while (begIndex < total) {
//            log.info("[exportToExcel]导出数据：处理第" + begIndex + "条 - 第" + endIndex + "条");
            for (int i = begIndex; i < endIndex; i++) {
                List<List<Object>> dataList = new ArrayList<>();
                        List<Object> rowData = new ArrayList<>();
                        //遍历commentList[begIndex]把数据都add进rowData 可以理解为一行
                        for(int i=0;i<commentList.get(begIndex).size();i++)
                        dataList.add(commentList.get(begIndex).get(i));
                        begIndex++;
                excelWriter.write(dataList, sheet1);
            }
            //分批次导出
            begIndex = endIndex;
            endIndex = Math.min((endIndex + batchSize), total);
        }
        excelWriter.finish();
}

sheet1 = EasyExcelUtil.getCommonWriteSheet(headList, "sheet1");执行结果

控制层

@RequestMapping("/exportToExcel")
public void ExportToExcel(ExportReqBody exportReqBody, HttpServletResponse response) {
    exportToExcelService.exportToExcel(exportReqBody, response);
}