[SpringBoot实战系列]SpringBoot使用EasyExcel

目录

开始使用

简单导出excel

easyExcel导出带下拉框的excel

问题：EasyExcel创建excel下拉框，下拉框内容过多时不显示

---

开始使用

1. 导入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.8</version>
</dependency>

简单导出excel

简单的导出excel模板

创建导出字段

@Data
public class ExportExcelTemplate {

    @ExcelProperty(value = "部门名称")
    private String deptName;

    @ExcelProperty(value = "预算年")
    private String budgetYear;

    @ExcelProperty(value = "预算月")
    private String budgetMonth;

    @ExcelProperty(value = "实际预算金额")
    private BigDecimal budgetMoney;

}

导出excel方法

@Override
public void exportExcel() {
    HttpServletResponse response = ServletUtil.getResponse();
    HttpServletRequest request = ServletUtil.getRequest();
    try{
        String fileName = "部门预算划分模板";
        String userAgent = request.getHeader("User-Agent");
        if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
            fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
        } else {
            fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
        }

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        List<ExportExcelTemplate> list = new ArrayList<>(); //测试导出

        EasyExcel.write(response.getOutputStream(), ExportExcelTemplate.class).sheet("模板").doWrite(list);

        log.info("excel模板导出成功");
    }catch (UnsupportedEncodingException e) {
        log.error("导出Excel编码异常", e);
    }catch (IOException e){
        log.error("导出Excel文件异常", e);
    }
}

数据可以根据实际情况导出，这里是没有导出数据，直接导出的excel模板

运行测试

导出就直接在浏览器输入访问地址进行测试 （使用postman或者swagger文档直接访问可能会有问题）

下载成功

打开excel文件

可以看到列明比较拥挤

设置导出excel列的宽度

在导出的字段上加入注解 @ColumnWidth(宽度) 来控制列宽度

重新运行查看导出的excel，列宽度已经变了

easyExcel导出带下拉框的excel

创建下拉框设置注解，标记该字段为下拉框并且为下拉框填充内容

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface DropDownBox {

    /**
     * 固定下拉框内容
     *
     * @return
     */
    String[] fixed() default {};

    /**
     * 动态下拉内容
     * 
     * @return
     */
    Class<? extends ExcelDynamicDrop>[] sourceClass() default {};

    /**
     * 设置下拉框起始行 默认为第二行
     *
     * @return
     */
    int firstRow() default 1;

    /**
     * 设置下拉框结束行 默认为最后一行
     *
     * @return
     */
    int lastRow() default 0x10000;

}

创建注解解析类

@Log4j2
@Data
public class ExcelDropResolve {

    /**
     * 下拉内容
     */
    private String[] source;

    /**
     * 设置下拉框的起始行，默认为第二行
     */
    private int firstRow;

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    private int lastRow;

    public String[] resolveDropSource(DropDownBox dropDownBox) {
        if (dropDownBox == null){
            return null;
        }

        //获取固定的下拉内容
        String[] fixed = dropDownBox.fixed();
        if (fixed.length > 0 ) {
            return fixed;
        }

        //获取动态的下拉内容
        Class<? extends ExcelDynamicDrop>[] classes = dropDownBox.sourceClass();
        if (classes.length > 0) {
            try {
                ExcelDynamicDrop excelDynamicDrop = classes[0].newInstance();
                String[] source = excelDynamicDrop.getSource();
                if (source != null && source.length > 0) {
                    return source;
                }
            } catch (InstantiationException e) {
                e.printStackTrace();
                log.error("解析动态下拉框数据异常", e);
            } catch (IllegalAccessException e) {
                log.error("解析动态下拉框数据异常", e);
                e.printStackTrace();
            }
        }
        return null;
    }

}

实现获取动态下拉内容类

public interface ExcelDynamicDrop {

    /**
     * 获取下拉框内容
     *
     * @return
     */
    String[] getSource();

}

public class ExcelDynamicDropImpl implements ExcelDynamicDrop {

    @Override
    public String[] getSource() {
        DeptMapper deptMapper = SpringContextUtil.getBean(DeptMapper.class);
        List<Dept> depts = deptMapper.selectList(null);

        List<String> data = new ArrayList<>();

        depts.forEach(i -> {
            data.add(i.getDeptId() + "-" + i.getDeptName());
        });
        return data.toArray(new String[]{});
    }

}

因为要使用到的这几个都没有被容器所管理所有不能使用注解​@Resource​ 和 ​​@Autowired​​ 等其他注解来注入类

创建一个​​SpringContextUtil​工具类来获取

@Component
public class SpringContextUtil implements ApplicationContextAware {

    private static ApplicationContext applicationContext;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        SpringContextUtil.applicationContext = applicationContext;
    }

    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    public static <T> T getBean(Class<T> clazz) {
        return applicationContext.getBean(clazz);
    }

    public static <T> T getBean(String name, Class<T> clazz) {
        return applicationContext.getBean(name, clazz);
    }

}

创建Handler 设置下拉框内容

@Data
@AllArgsConstructor
public class DropSheetWriteHandler implements SheetWriteHandler {

    private final Map<Integer, ExcelDropResolve> dropResolveMap;

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

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        dropResolveMap.forEach((k, v) -> {
            // 设置下拉列表的行： 首行，末行，首列，末列
            CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
            // 设置下拉列表的值
            DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
            // 设置约束
            DataValidation validation = helper.createValidation(constraint, rangeList);
            // 阻止输入非下拉选项的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            validation.createErrorBox("提示", "请输入下拉选项中的内容");
            sheet.addValidationData(validation);
        });
    }
}

创建导出工具类

@Log4j2
public class EasyExcelUtil {

    /**
     * 创建即将导出的sheet页（sheet页中含有带下拉框的列）
     * @param head 导出的表头信息和配置
     * @param sheetNo sheet索引
     * @param sheetName sheet名称
     * @param <T> 泛型
     * @return sheet页
     */
    public static <T> WriteSheet writeSelectedSheet(Class<T> head, Integer sheetNo, String sheetName) {
        Map<Integer, ExcelDropResolve> selectedMap = resolveSelectedAnnotation(head);

        return EasyExcel.writerSheet(sheetNo, sheetName)
                .head(head)
                .registerWriteHandler(new DropSheetWriteHandler(selectedMap))
                .build();
    }

    /**
     * 解析表头类中的下拉注解
     * @param head 表头类
     * @param <T> 泛型
     * @return Map<下拉框列索引, 下拉框内容> map
     */
    private static <T> Map<Integer, ExcelDropResolve> resolveSelectedAnnotation(Class<T> head) {
        Map<Integer, ExcelDropResolve> selectedMap = new HashMap<>();

        // getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
        Field[] fields = head.getDeclaredFields();
        for (int i = 0; i < fields.length; i++){
            Field field = fields[i];
            // 解析注解信息
            DropDownBox selected = field.getAnnotation(DropDownBox.class);
            ExcelProperty property = field.getAnnotation(ExcelProperty.class);
            if (selected != null) {
                ExcelDropResolve excelSelectedResolve = new ExcelDropResolve();
                String[] source = excelSelectedResolve.resolveDropSource(selected);
                if (source != null && source.length > 0){
                    excelSelectedResolve.setSource(source);
                    excelSelectedResolve.setFirstRow(selected.firstRow());
                    excelSelectedResolve.setLastRow(selected.lastRow());
                    if (property != null && property.index() >= 0){
                        selectedMap.put(property.index(), excelSelectedResolve);
                    } else {
                        selectedMap.put(i, excelSelectedResolve);
                    }
                }
            }
        }

        return selectedMap;
    }

}

在导出实体类上加上该注解

@Data
public class ExportExcelTemplate {

    //动态下拉内容
    @DropDownBox(sourceClass = ExcelDynamicDropImpl.class)
    @ColumnWidth(15)
    @ExcelProperty(value = "部门名称")
    private String deptName;

    @ColumnWidth(10)
    @ExcelProperty(value = "预算年")
    private String budgetYear;

    //固定下拉内容
    @DropDownBox(fixed = {"1","2","3"})
    @ColumnWidth(10)
    @ExcelProperty(value = "预算月")
    private String budgetMonth;

    @ColumnWidth(17)
    @ExcelProperty(value = "实际预算金额")
    private BigDecimal budgetMoney;

}

更改一下导出方法

@Override
    public void exportExcel() {
        HttpServletResponse response = ServletUtil.getResponse();
        HttpServletRequest request = ServletUtil.getRequest();
        try{
            String fileName = "部门预算划分模板";
            String userAgent = request.getHeader("User-Agent");
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            }

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet writeSheet = EasyExcelUtil.writeSelectedSheet(ExportExcelTemplate.class, 0, "测试sheet");
            excelWriter.write(new ArrayList<String>(), writeSheet);
            excelWriter.finish();

            log.info("excel模板导出成功");
        }catch (UnsupportedEncodingException e) {
            log.error("导出Excel编码异常", e);
        }catch (IOException e){
            log.error("导出Excel文件异常", e);
        }
    }

运行测试结果

目前用于测试的部门数据只有几条

当数据多的时候会有一个问题

问题：EasyExcel创建excel下拉框，下拉框内容过多时不显示

将获取数据方法处更改一下，插入100条数据测试。下拉框不展示下拉数据了

解决办法: 修改拦截器里面写的 就可解决

@Data
@AllArgsConstructor
public class DropSheetWriteHandler implements SheetWriteHandler {

    private final Map<Integer, ExcelDropResolve> dropResolveMap;

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

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        String hiddenName = "hidden";
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet hidden = workbook.createSheet(hiddenName);

        for (Map.Entry<Integer, ExcelDropResolve> entry : dropResolveMap.entrySet()) {
            CellRangeAddressList addressList = new CellRangeAddressList(1, entry.getValue().getLastRow()+500, entry.getKey(), entry.getKey());
            String excelLine = getExcelLine(entry.getKey());

            String[] values = entry.getValue().getSource();
            for (int i = 0, length = values.length; i < length; i++) {
                hidden.createRow(i).createCell(entry.getKey()).setCellValue(values[i]);
            }
            Name category1Name = workbook.createName();
            category1Name.setNameName(hiddenName);
            String refers = "="+hiddenName + "!$"+excelLine+
                    "$1:$"+excelLine +"$"+ (values.length+1);
            DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            writeSheetHolder.getSheet().addValidationData(dataValidation);
        }
        int hiddenIndex = workbook.getSheetIndex("hidden");
        if (!workbook.isSheetHidden(hiddenIndex)) {
            workbook.setSheetHidden(hiddenIndex, true);
        }
    }

    /**
     * @Description 返回excel列标A-Z-AA-ZZ
     * @Author chou
     * @Date 2020/9/8
     * @param num 列数
     * @return java.lang.String
     */
    public static String getExcelLine(int num) {
        String line = "";
        int first = num/26;
        int second = num % 26;
        if (first>0) {
            line = (char)('A'+first-1)+"";
        }
        line += (char)('A'+second)+"";
        return line;
    }
}

学习参考链接

https://codeleading.com/article/81974659139/

https://blog.csdn.net/qq_41505160/article/details/119041989?utm_medium=distribute.pc_relevant.none-task-blog-2~default~baidujs_baidulandingword~default-0.control&spm=1001.2101.3001.4242