EasyExcel 列 固定下拉选项的做法

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

 

接前面的2篇随笔,继续写。此处看不到的源码,往前面2个博客里找

 

1 在我们的实体类的列上加注解,第12列【index是从0开始】 列头为性别,列内容从下拉选项中选 男,女

EasyExcel  列 固定下拉选项的做法

 

 

 效果如下: 性别  列 只能下拉选

EasyExcel  列 固定下拉选项的做法

 

 

 

 

 

 

 2 上改动代码:

 测试main方法如下:

package com.excel.caluator.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.excel.caluator.aop.ExplicitConstraint;
import com.excel.caluator.excel.entity.AttendanceDemo;
import com.excel.caluator.excel.sheet.CreateTemplateWriteHandler;

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

/**
 * @description:
 * @author:
 * @createDate: 2021/7/29
 * @version: 1.0
 */
public class DemoUtils {
    public static void main(String[] args) {


        String fileName="C:\\Users\\xx\\Desktop\\55.xlsx";

        createTemplate(fileName,"demo001",AttendanceDemo.class,
                AttendanceDemo.bigTitle,AttendanceDemo.getHeadHeight(),11);




    }



    public  static  void createTemplate(String fileName,
                                        String sheetName,
                                        Class<?> model, String title,int heardHeight,int cellIndex){

        //下拉列表集合
        Map<Integer, String[]> explicitListConstraintMap = new HashMap<>();
        //循环获取对应列得下拉列表信息
        Field[] declaredFields = model.getDeclaredFields();
        for (int i = 0; i < declaredFields.length; i++) {
            Field field = declaredFields[i];
            //解析注解信息
            ExplicitConstraint explicitConstraint = field.getAnnotation(ExplicitConstraint.class);
            resolveExplicitConstraint(explicitListConstraintMap,explicitConstraint);
        }

        EasyExcel.write(fileName)
                .excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
                .registerWriteHandler(new CreateTemplateWriteHandler(title, heardHeight, cellIndex,explicitListConstraintMap))
                .head(model)
                .useDefaultStyle(true).relativeHeadRowIndex(1)
                .doWrite(null);



    }

    /**
     * 解析注解内容 获取下列表信息
     * @param explicitConstraint
     * @return
     */
    public static Map<Integer, String[]> resolveExplicitConstraint(Map<Integer, String[]> explicitListConstraintMap, ExplicitConstraint explicitConstraint){
        if (explicitConstraint == null) {
            return null;
        }
        //固定下拉信息
        String[] source = explicitConstraint.source();
        if (source.length > 0) {
            explicitListConstraintMap.put(explicitConstraint.indexNum(), source);
        }

        return explicitListConstraintMap;
    }



}

  

  自定义注解代码

package com.excel.caluator.aop;

import java.lang.annotation.*;


/**
 * @author kuangql
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExplicitConstraint {


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

    /**
     * 列标号必须和字段下标一致
     * @return 0
     */
    int indexNum() default 0;

}

  

 

3  实体类属性加  注解

    //https://blog.csdn.net/tangwei3150/article/details/118389822   下拉列选项,可以参考

    @ExplicitConstraint(source={"男","女"},indexNum = 11)
    @ExcelProperty(value = {"性别"}, index = 11)
    private String sex;

  

4  对比前2偏,这里多加了一个构造器。以及  

afterSheetCreate方法加了一些处理下拉选择的代码
package com.excel.caluator.excel.sheet;

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.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.HashMap;
import java.util.Map;

/**
 *
 *
 * 创建模板
 * @author:
 * @date: 2020/11/30 13:48
 * @description: TODO
 */
public class CreateTemplateWriteHandler implements SheetWriteHandler {


    /**
     * 第一行内容
     */
    private String firstTitle;


    /**
     * 实体模板类的行高
     */
    private int height;


    /**
     * 实体类 最大的列坐标 从0开始算
     */
    private int  lastCellIndex;



    private Map<Integer, String[]> explicitListConstraintMap = new HashMap<>();

    public CreateTemplateWriteHandler(String firstTitle, int height, int cellCounts,Map<Integer, String[]> explicitListConstraintMap) {
        this.firstTitle = firstTitle;
        this.height = height;
        this.lastCellIndex = cellCounts;
        this.explicitListConstraintMap = explicitListConstraintMap;

    }

    public CreateTemplateWriteHandler(String firstTitle, int height, int cellCounts) {
        this.firstTitle = firstTitle;
        this.height = height;
        this.lastCellIndex = cellCounts;


    }

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

    }


    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);




        DataValidationHelper helper = sheet.getDataValidationHelper();

        // k 为存在下拉数据集的单元格下表 v为下拉数据集
        explicitListConstraintMap.forEach((k, v) -> {
            // 设置下拉单元格的首行 末行 首列 末列   【因为我的业务是第一行是描述 ,第二行是列头,第三行是内容  所以入参下标从2开始,暂定5000行,可以写最大行,也可以根据业务而定】
            CellRangeAddressList rangeList = new CellRangeAddressList(2, 5000, k, k);
            // 下拉列表约束数据
            DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
            // 设置约束
            DataValidation validation = helper.createValidation(constraint, rangeList);
            // 阻止输入非下拉选项的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            validation.createErrorBox("提示", "此值与单元格定义格式不一致");
            sheet.addValidationData(validation);
        });

//----------和之前的逻辑一样

        Row row1 = sheet.createRow(0);
        row1.setHeight((short) height);
        //字体样式
        Font font = workbook.createFont();
        font.setColor((short)2);
        Cell cell = row1.createCell(0);

        //单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);
        cell.setCellStyle(cellStyle);

        //设置单元格内容
        cell.setCellValue(firstTitle);


        //合并单元格  --> 起始行, 终止行   ,起始列,终止列
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, lastCellIndex));


    }
}

5 改动完成,效果如上面截图,有不懂的可以加群问群主,参考博客  https://blog.csdn.net/tangwei3150/article/details/118389822

备注:我这个是固定下拉值写法,参考的博客是动态的下拉值,业务需求要求比较灵活可以参考 https://blog.csdn.net/tangwei3150/article/details/118389822 博客的做法,

固定列的可以就用我这种,减少开发量,用起来比较方便。  这块我没写生成工具类,可以自行包装一个,只写了main方法。

 

上一篇:使用easyexcel遇到的问题


下一篇:easyExcel同一单元格部分文字颜色、样式修改