【开发心得】EasyExcel导入导出

概述:
使用 easyExcel 3.x版本导出参考

环境:

jdk1.8

Springboot 2.5.7 

easyExcel 3.0.5

easyPoi 5.0.0

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
            <exclusions>
                <exclusion>
                    <groupId>javax.servlet</groupId>
                    <artifactId>servlet-api</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-schemas</artifactId>
                </exclusion>
            </exclusions>
        </dependency>


        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.xmlgraphics</groupId>
                    <artifactId>batik-all</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.santuario</groupId>
                    <artifactId>xmlsec</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>de.rototor.pdfbox</groupId>
                    <artifactId>graphics2d</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

【模板类参考.脱敏】

@Data
@NoArgsConstructor
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER) // 表头样式
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER) // 内容样式
@HeadFontStyle(bold = BooleanEnum.TRUE,color = -1)
public class DataOutput {
    @ColumnWidth(value = 20)
    @ExcelProperty(value = {"id"} ,index = 0)
    private String uuid;
    @ColumnWidth(value = 20)
    @ExcelProperty(value = {"名称"} ,index = 1)
    private String name;
    @ContentStyle(dataFormat = 49)
    @ColumnWidth(value = 20)
    @ExcelProperty(value = {"时间点"} ,index = 2)
    private Long materialId;
    @ExplicitConstraint(source = {"类型1", "类型2", "类型3"}) // 固定下拉内容
    @ColumnWidth(value = 20)
    @ExcelProperty(value = {"类型"} ,index = 4)
    private String type;
    @ExplicitConstraint(sourceClass = ScopeExplicitConstraint.class) // 动态下拉内容
    @ColumnWidth(value = 20)
    @ExcelProperty(value = {"使用范围"} ,index = 8)
    private String scope;
}

【合并列】

package com.xxx.output;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.metadata.CellExtra;
import com.xxx.UploadDataListener;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;

import java.io.*;
import java.lang.reflect.Field;
import java.util.List;

/**
 * @author
 * @date 2020-12-01 13:34
 **/
@Slf4j
public class ExcelAnalysisHelper<T> {
    public List<T> getList(File file, Class<T> clazz) {
        try (InputStream inputStream = new FileInputStream(file)) {
            return getList(inputStream, clazz, 0, 1);
        } catch (FileNotFoundException e) {
            log.error("file not found:{}", e.getMessage());
        } catch (IOException e) {
            log.error("file io exception:{}", e.getMessage());
        }
        return null;
    }

    public List<T> getList(InputStream inputStream, Class<T> clazz, Integer sheetNo, Integer headRowNumber) {
        UploadDataListener<T> listener = new UploadDataListener<>(headRowNumber);
        EasyExcel.read(inputStream, clazz, listener).extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doRead();
        List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();
        if (CollectionUtils.isEmpty(extraMergeInfoList)) {
            return listener.getData();
        }
        List<T> data = explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);
        return data;
    }

    /**
     * 处理合并单元格
     *
     * @param data               解析数据
     * @param extraMergeInfoList 合并单元格信息
     * @param headRowNumber      起始行
     * @return 填充好的解析数据
     */
    private List<T> explainMergeData(List<T> data, List<CellExtra> extraMergeInfoList, Integer headRowNumber) {
//        循环所有合并单元格信息
        extraMergeInfoList.forEach(cellExtra -> {
            int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNumber;
            int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;
            int firstColumnIndex = cellExtra.getFirstColumnIndex();
            int lastColumnIndex = cellExtra.getLastColumnIndex();
//            获取初始值
            Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, data);
//            设置值
            for (int i = firstRowIndex; i <= lastRowIndex; i++) {
                for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {
                    setInitValueToList(initValue, i, j, data);
                }
            }
        });
        return data;
    }

    /**
     * 设置合并单元格的值
     *
     * @param filedValue  值
     * @param rowIndex    行
     * @param columnIndex 列
     * @param data        解析数据
     */
    public void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {
        T object = data.get(rowIndex);

        for (Field field : object.getClass().getDeclaredFields()) {
            // 提升反射性能，关闭安全检查
            field.setAccessible(true);
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                if (annotation.index() == columnIndex) {
                    try {
                        field.set(object, filedValue);
                        break;
                    } catch (IllegalAccessException e) {
                        log.error("analysis data error: {}", e.getMessage());
                    }
                }
            }
        }
    }


    /**
     * 获取合并单元格的初始值
     * rowIndex对应list的索引
     * columnIndex对应实体内的字段
     *
     * @param firstRowIndex    起始行
     * @param firstColumnIndex 起始列
     * @param data             列数据
     * @return 初始值
     */
    private Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> data) {
        Object filedValue = null;
        T object = data.get(firstRowIndex);
        for (Field field : object.getClass().getDeclaredFields()) {
            // 提升反射性能，关闭安全检查
            field.setAccessible(true);
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                if (annotation.index() == firstColumnIndex) {
                    try {
                        filedValue = field.get(object);
                        break;
                    } catch (IllegalAccessException e) {
                        log.error("analysis error:{}", e.getMessage());
                    }
                }
            }
        }
        return filedValue;
    }
}

package com.xxx.output;

/**
 * @Author: martin
 * @Date: 2021/12/15 6:36 下午
 * @Description:
 */

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

/**
 * @author
 * @date
 */
public class ExcelFillCellMergeStrategy implements CellWriteHandler {

    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public ExcelFillCellMergeStrategy() {
    }

    public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();
        if (curRowIndex > mergeRowIndex) {
            Cell firstCell = cell.getRow().getCell(0); // 当前行第一个cell
            Cell preFirstCell = cell.getSheet().getRow(curRowIndex - 1).getCell(0); // 上一行第一个cell
            if (firstCell != null && preFirstCell != null) {
                if (!firstCell.getStringCellValue().equals(preFirstCell.getStringCellValue())) {
                    return; // 首行不一致，跳过合并
                }
            }
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
        if (cell.getCellType() == CellType.BLANK) {
            return;
        }
        Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
        if (curData.equals(preData)) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
}

【下拉框-动态】

package com.xxx.output;

import java.lang.annotation.*;

/**
 * @Author: martin
 * @Date: 2021-12-22 10:46
 * @Description:
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExplicitConstraint {
    //定义固定下拉内容
    String[]source()default {};
    //定义动态下拉内容，
    Class[]sourceClass()default {};
}

package com.xxx.output;

/**
 * @Author: martin
 * @Date: 2021-12-22 10:47
 * @Description:
 */
public interface ExplicitInterface {
    /**
     * 下拉列表的内容数组
     * @return
     */
    String[] source();
}

package com.xxx.output;

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.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;

/**
 * @Author: martin
 * @Date: 2021-12-22 11:08
 * @Description:
 */
public class ExcelExplicitStrategy implements SheetWriteHandler {
    // 处理下拉列表
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 下拉列表集合
        Map<Integer, String[]> explicitListConstraintMap = new HashMap<>();
        // 循环获取对应列得下拉列表信息
        Field[] declaredFields = XXX.class.getDeclaredFields();
        for (int i = 0; i < declaredFields.length; i++) {
            Field field = declaredFields[i];
            // 解析注解信息
            ExplicitConstraint explicitConstraint = field.getAnnotation(ExplicitConstraint.class);
            String[] explicitArray = resolveExplicitConstraint(explicitConstraint);
            if (explicitArray != null && explicitArray.length > 0) {
                explicitListConstraintMap.put(i, explicitArray);
            }
            // 通过sheet处理下拉信息
            Sheet sheet = writeSheetHolder.getSheet();
            DataValidationHelper helper = sheet.getDataValidationHelper();
            explicitListConstraintMap.forEach((k, v) -> {
                CellRangeAddressList rangeList = new CellRangeAddressList();
                CellRangeAddress addr = new CellRangeAddress(1, 1000, k, k);
                rangeList.addCellRangeAddress(addr);
                DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
                DataValidation validation = helper.createValidation(constraint, rangeList);
                sheet.addValidationData(validation);
            });
        }
    }

    /**
     * 解析注解内容 获取下列表信息
     *
     * @param explicitConstraint
     * @return
     */
    public String[] resolveExplicitConstraint(ExplicitConstraint explicitConstraint) {
        if (explicitConstraint == null) {
            return null;
        }
        //固定下拉信息
        String[] source = explicitConstraint.source();
        if (source.length > 0) {
            return source;
        }
        //动态下拉信息
        Class<? extends ExplicitInterface>[] classes = explicitConstraint.sourceClass();
        if (classes.length > 0) {
            ExplicitInterface explicitInterface = null;
            try {
                explicitInterface = classes[0].newInstance();
                String[] source1 = explicitInterface.source();
                if (source1.length > 0) {
                    return source1;
                }
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}

public class ScopeExplicitConstraint implements ExplicitInterface {
    @Override
    public String[] source() {
        List<String> list = new ArrayList<>();
        for (ScopeEnum item : ScopeEnum.values()) {
            list.add(item.name);
        }
        return list.toArray(new String[list.size()]);
    }
}

 

【导出参考】

 public void exportData(HttpServletResponse response, List<Long> dataIds) {
        PageRequest pageRequest = PageRequest.of(0, dataIds.size());
        List<xxxOutput> resultList = xxxService.getEditDocument(dataIds, pageRequest);
        try {
//            设置第几列合并
            int[] mergeColumnIndex = {0, 1, 2, 3}; // 合并第一列，所以赋值0，如果需要合并多列，直接逗号分隔：int[] mergeColumnIndex = {0,1,2}
//            需要从第几行开始合并
            int mergeRowIndex = 1;
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //  设置文件名称
//            String fileName = URLEncoder.encode("xxx", "UTF-8");
            String fileName = this.getXlsFileName();
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            //  sheet名称
            EasyExcel.write(response.getOutputStream(), xxx.class)
//                    excel版本
                    .excelType(ExcelTypeEnum.XLSX)
//                    是否自动关流
                    .autoCloseStream(Boolean.TRUE)
                    .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumnIndex))
                    .registerWriteHandler(new ExcelExplicitStrategy())
                    .sheet("xxx导出").doWrite(resultList);
        } catch (Exception e) {
            log.error("export xxx error: {}", e.getMessage());
        }

    }