llp-blog

Appearance

基于注解和EasyExcel指定单元格下拉选内容 ​

１.场景描述 ​

要求基于EasyExcel进行导出，有的单元格的内容必须是通过下拉选内容进行编辑，以便于后续修改导入，规避用户随意录入数据。

image-20230103181039765

image-20230103181054652

2.编写测试 ​

这里只省略部分代码，避免喧宾夺主。

大概思路就是根据业务要求查询出数据，并利用EasyExcel进行导出。

java
@PostMapping("/export")
@ApiOperation("明细统计导出")
public void exportRiskDetailReport(@Valid DetailReportDto detailReportDto) throws ParseException, IOException {
        EasyExcelUtil.excelDropdownExport(1, DetailReportViewDto.class, "风险排查明细统计数据" + DateUtil.format(new Date(), "yyyyMMddHHmmssS"), list, null);
    }
}
@PostMapping("/export")
@ApiOperation("明细统计导出")
public void exportRiskDetailReport(@Valid DetailReportDto detailReportDto) throws ParseException, IOException {
        EasyExcelUtil.excelDropdownExport(1, DetailReportViewDto.class, "风险排查明细统计数据" + DateUtil.format(new Date(), "yyyyMMddHHmmssS"), list, null);
    }
}

3.代码实现 ​

导出工具类

在导出时我们需要将自定义的处理器注册到EasyExcel中。

java
@Slf4j
public class EasyExcelUtil { 
	public static void excelDropdownExport(int firstRow,Class head, String excelname, List data, String sheetName) {
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = requestAttributes.getResponse();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        try {
            // 这里URLEncoder.encode可以防止浏览器端导出excel文件名中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(excelname, "UTF-8").replaceAll("\\+", "+");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), head)
                    //导出时将单元格数据转换为Number类型，导入时将数据转换为Long类型
                    .registerConverter(new LongStringConverter())
                    //自定义下拉选内容处理器
                    .registerWriteHandler(new CellDropdownHandler(firstRow,data))
                    .sheet(sheetName == null ? "Sheet1" : sheetName).doWrite(data);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("导出数据失败: " + e.getMessage());
        }
    }
}
@Slf4j
public class EasyExcelUtil { 
	public static void excelDropdownExport(int firstRow,Class head, String excelname, List data, String sheetName) {
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = requestAttributes.getResponse();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        try {
            // 这里URLEncoder.encode可以防止浏览器端导出excel文件名中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(excelname, "UTF-8").replaceAll("\\+", "+");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), head)
                    //导出时将单元格数据转换为Number类型，导入时将数据转换为Long类型
                    .registerConverter(new LongStringConverter())
                    //自定义下拉选内容处理器
                    .registerWriteHandler(new CellDropdownHandler(firstRow,data))
                    .sheet(sheetName == null ? "Sheet1" : sheetName).doWrite(data);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("导出数据失败: " + e.getMessage());
        }
    }
}

定义下拉选注解

这里我采用的是固定内容下拉，将需要下拉的内容通过注解进行传递，当然如果需要从数据库中获取下拉内容也可以直接指定Class对象，在后续处理中从Spring上下文中获取对象,比如通过下面这样的代码

java
@Component
public final class SpringUtils implements BeanFactoryPostProcessor, ApplicationContextAware 
{   
	public static <T> T getBean(Class<T> clz) throws BeansException
    {
        T result = (T) beanFactory.getBean(clz);
        return result;
    }
}
@Component
public final class SpringUtils implements BeanFactoryPostProcessor, ApplicationContextAware 
{   
	public static <T> T getBean(Class<T> clz) throws BeansException
    {
        T result = (T) beanFactory.getBean(clz);
        return result;
    }
}
java
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface SelectData {

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

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

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

}

核心代码

单元格下拉内容处理

java
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
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.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

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

/**
 * 单元格下拉
 */
public class CellDropdownHandler implements CellWriteHandler {


    private int firstRow;

    private int lastRow;

    private List<Object> list;

    private CellDropdownHandler() {
    }
	//通过构造的方式传入 firstRow 内容的起始行 list要导出的数据集合
    public CellDropdownHandler(int firstRow,List<Object> list) {
        this.list = list;
        this.firstRow = firstRow;
        lastRow = list.size();
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        if(CollectionUtils.isNotEmpty(list)){
            Sheet sheet = writeSheetHolder.getSheet();
            DataValidationHelper helper = sheet.getDataValidationHelper();
            String fieldName = head.getFieldName();
            Class clazz = writeSheetHolder.getClazz();
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                if(fieldName.equals(field.getName()) && field.isAnnotationPresent(SelectData.class) && field.isAnnotationPresent(ExcelProperty.class)){
                    SelectData selectData = field.getAnnotation(SelectData.class);
                    // 设置下拉单元格的首行 末行 首列 末列   【因为我的业务是第一行是列头，第二行是内容  所以入参下标从1开始，行数通过外层list集合获取】
                    CellRangeAddressList rangeList = new CellRangeAddressList(firstRow, lastRow, columnIndex, columnIndex);
                    // 下拉列表约束数据
                    DataValidationConstraint constraint = helper.createExplicitListConstraint(selectData.values());
                    // 设置约束
                    DataValidation validation = helper.createValidation(constraint, rangeList);
                    // 阻止输入非下拉选项的值
                    validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                    validation.setShowErrorBox(true);
                    validation.setSuppressDropDownArrow(true);
                    validation.createErrorBox("提示", "此值与单元格定义格式不一致");
                    sheet.addValidationData(validation);
                }
            }
        }
    }

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

    }

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

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }
}
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
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.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

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

/**
 * 单元格下拉
 */
public class CellDropdownHandler implements CellWriteHandler {


    private int firstRow;

    private int lastRow;

    private List<Object> list;

    private CellDropdownHandler() {
    }
	//通过构造的方式传入 firstRow 内容的起始行 list要导出的数据集合
    public CellDropdownHandler(int firstRow,List<Object> list) {
        this.list = list;
        this.firstRow = firstRow;
        lastRow = list.size();
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        if(CollectionUtils.isNotEmpty(list)){
            Sheet sheet = writeSheetHolder.getSheet();
            DataValidationHelper helper = sheet.getDataValidationHelper();
            String fieldName = head.getFieldName();
            Class clazz = writeSheetHolder.getClazz();
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                if(fieldName.equals(field.getName()) && field.isAnnotationPresent(SelectData.class) && field.isAnnotationPresent(ExcelProperty.class)){
                    SelectData selectData = field.getAnnotation(SelectData.class);
                    // 设置下拉单元格的首行 末行 首列 末列   【因为我的业务是第一行是列头，第二行是内容  所以入参下标从1开始，行数通过外层list集合获取】
                    CellRangeAddressList rangeList = new CellRangeAddressList(firstRow, lastRow, columnIndex, columnIndex);
                    // 下拉列表约束数据
                    DataValidationConstraint constraint = helper.createExplicitListConstraint(selectData.values());
                    // 设置约束
                    DataValidation validation = helper.createValidation(constraint, rangeList);
                    // 阻止输入非下拉选项的值
                    validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                    validation.setShowErrorBox(true);
                    validation.setSuppressDropDownArrow(true);
                    validation.createErrorBox("提示", "此值与单元格定义格式不一致");
                    sheet.addValidationData(validation);
                }
            }
        }
    }

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

    }

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

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }
}
  • 参数说明

firstRow是只excel内容从哪一行开始，下表从0开始，除去表头。

lastRow是excel的最后一行

list要导出的数据集合

4.测试效果 ​

当下拉单元格填入和下拉不一致的内容时，给出提示并阻止继续编辑单元格。

image-20230103181039765

下拉内容如下

image-20230103181054652