easyexcel生成动态模板（模板支持下拉框），动态字段导出excel表格，常规字段导出excel表格

备注：动态字段导出主要是用了反射的原理，跟excel需要导出的字段一一映射。话不多说，直接上代码；

1.生成的动态模板如图：

如上图，如果下拉框里不是选择的值，会给用户提示，下拉框用来限制用户导入只能选择下拉框中的值

先放controller层调用

@RestController
@RequestMapping("/test")
@Slf4j
@RequiredArgsConstructor
public class TestExcelController {

    private final DynamicExcelUtils excelUtils;

    @GetMapping("v1/test01.do")
    @CrossOrigin
    @ApiOperation(value = "测试动态模板生成下载")
    public void test01(HttpServletResponse response) {
        //标题头数据
        String title =  "说明：n"+
                "1、带*号的字段必填n"+
                "2、员工工号只能输入英文和数字，长度限制10。n"+
                "3、员工姓名长度限制20。n"+
                "4、下拉框选项只能选择模板提供的下拉选项。n"+
                "5、年龄只能输入数字。n"+
                "6、第三行数据为示例数据，可以删除输入正式数据。n"+
                "7、导入表格时本说明不要删除。";
        //需要生成的excel列字段名称
        List names = Arrays.asList("姓名","工号","年龄","下拉列");
        //设置第三行示例数据
        List userList = new ArrayList<>();
        User user = new User("张三", "B112233", "18", "下拉值01");
        userList.add(user);
        //列字段的英文名称,根据英文名称对应示例数据
        List fieldEn = Arrays.asList("name","workCode","age","select");
        //设置第几列显示下拉框，用户只能选择下拉框中的值，非下拉框则不允许保存excel
        Map> selectMap = new HashMap<>(8);
        //设置下拉框的值
        List selectList = Arrays.asList("下拉字段1", "下拉字段2", "下拉字段3");
        //设置excel第几列为下拉列
        selectMap.put(3,selectList);
        //设置sheet页名称
        String sheetName = "测试sheet页";
        //设置文件名称
        String fileName = "测试生成动态模板";
        //以上所有数据都是支持动态设置的
        excelUtils.excelDownloadLink(response,title,names,fieldEn,userList,selectMap,sheetName,fileName);
    }

    @GetMapping("v1/test02.do")
    @CrossOrigin
    @ApiOperation(value = "测试excel导出动态数据")
    public void test02(HttpServletResponse response) {
        //自定义需要导出的excel列字段名称
        List names = Arrays.asList("姓名(自定义)","工号（自定义）","年龄（自定义名称）","下拉列（自定义名称）");
        //列字段的英文名称,根据英文名称对应示例数据
        List fieldEn = Arrays.asList("name","workCode","age","select");
        //要导出的数据
        List userList = new ArrayList<>();
        User user1 = new User("张三01", "B11", "18", "下拉值01");
        User user2 = new User("张三02", "B112", "19", "下拉值02");
        User user3 = new User("张三03", "B11223", "20", "下拉值03");
        userList.add(user1);
        userList.add(user2);
        userList.add(user3);
        //设置sheet页名称
        String sheetName = "测试sheet页";
        //设置文件名称
        String fileName = "测试导出动态excel数据";
        excelUtils.excelExportData(response,names,fieldEn,userList,sheetName,fileName);

    }

    @GetMapping("v1/test03.do")
    @CrossOrigin
    @ApiOperation(value = "测试excel导出普通通用实体类型数据")
    public void test03(HttpServletResponse response) {

        List userExcels = new ArrayList<>();
        userExcels.add(new UserExcel("张01","张测试1","B001","16","下拉01"));
        userExcels.add(new UserExcel("张02","张测试2","B002","17","下拉02"));
        userExcels.add(new UserExcel("张03","张测试3","B003","18","下拉03"));
        //设置sheet页名称
        String sheetName = "测试sheet页";
        //设置文件名称
        String fileName = "测试导出excel数据";
        excelUtils.excelExportOrdinaryData(response,sheetName,fileName,userExcels,UserExcel.class);
    }

}

写的excel工具类，包含了动态生成模板，动态导出数据，和普通导出数据

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import net.sharing.backstage.oa.constant.Constants;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;

@Component
@Slf4j
public class DynamicExcelUtils {

    
    public void excelDownloadLink(HttpServletResponse response, String title,List names, List fieldEn ,List list ,Map> selectMap,String sheetName, String fileName) {
        try {
            //设置表格第三行的示例数据的值
            List> datas = setData(list,fieldEn);
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + Constants.EXCEL);
            response.setCharacterEncoding("UTF-8");
            EasyExcel.write(response.getOutputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .head(head(CollectionUtil.isNotEmpty(names) ? names.toArray(new String[0]) : new String[0], title))
                    //开启内存模式才能使用动态设置标题样式
                    .inMemory(true)
                    .registerWriteHandler(new TitleStyleUtils(names))
                    .registerWriteHandler(new SelectSheetWriteHandler(selectMap))
                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25))
                    .sheet(sheetName)
                    .doWrite(CollectionUtil.isNotEmpty(datas) ? datas:new ArrayList());
        } catch (IOException e) {
            e.printStackTrace();
            response.reset();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/json");
            try {
                response.getWriter().println("打印失败");
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }


    
    public void excelExportData(HttpServletResponse response,List names,List fieldEn,List list ,String sheetName, String fileName){
        try {

            //设置返回数据的值跟动态列一一对应
            List> datas = setData(list,fieldEn);
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + Constants.EXCEL);
            response.setCharacterEncoding("UTF-8");
            EasyExcel.write(response.getOutputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .head(headData(CollectionUtil.isNotEmpty(names) ? names.toArray(new String[0]) : new String[0]))
                    .registerWriteHandler(new AutoWidthHandler())
                    .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 25, (short) 25))
                    .sheet(sheetName)
                    .doWrite(CollectionUtil.isNotEmpty(datas) ? datas:new ArrayList());

        } catch (IOException e) {
            e.printStackTrace();
            response.reset();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/json");
            try {
                response.getWriter().println("打印失败");
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }

    
    public void excelExportOrdinaryData(HttpServletResponse response,String sheetName, String fileName, List list, Class clazz){
        try {

            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + Constants.EXCEL);
            response.setCharacterEncoding("UTF-8");
            EasyExcel.write(response.getOutputStream(),clazz)
                    .excelType(ExcelTypeEnum.XLSX)
                    .head(clazz)
                    //设置默认样式及写入头信息开始的行数
                    .relativeHeadRowIndex(0)
                    .registerWriteHandler(new AutoWidthHandler())
                    .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 25, (short) 25))
                    .sheet(sheetName)
                    .doWrite(list);
        } catch (IOException e) {
            e.printStackTrace();
            response.reset();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/json");
            try {
                response.getWriter().println("打印失败");
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }


    }

    
    private List> setData(List list,List fieldEn){
        List> datas = new ArrayList<>();
        //对象反射转map方法
        List> maps = new ArrayList<>();
        if (CollectionUtil.isNotEmpty(list)){
            for (Object o : list) {
                Class aClass = o.getClass();
                Field[] fields = aClass.getDeclaredFields();
                Map map = new HashMap<>(40);

                for (Field field : fields) {
                    map.put(field.getName(), getResult(field.getName(), o));
                }
                maps.add(map);
            }
            for (Map map : maps) {
                //用于接收返回数据行？
                List data = new LinkedList();
                for (int i = 0; i < fieldEn.size(); i++) {
                    Object o = map.get(fieldEn.get(i));
                    data.add(Objects.isNull(o) ? Constants.CHAR : o.toString());
                }
                datas.add(data);
            }
        }
        return datas;
    }

    
    private static Object getResult(Object fieldName, Object o) {
        try {
            Class aClass = o.getClass();
            Field declaredField = aClass.getDeclaredField(fieldName.toString());
            declaredField.setAccessible(true);
            PropertyDescriptor pd = new PropertyDescriptor(declaredField.getName(), aClass);
            Method readMethod = pd.getReadMethod();

            return readMethod.invoke(o);
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IntrospectionException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return null;
    }

    
    private List> head(String[] header, String bigTitle) {
        List head0 = null;
        List> list = new LinkedList>();
        for (String h : header) {
            head0 = new LinkedList<>();
            head0.add(bigTitle);
            head0.add(h);
            list.add(head0);
        }
        return list;
    }
    
    private List> headData(String[] header) {
        List head0 = null;
        List> list = new LinkedList>();
        for (String h : header) {
            head0 = new LinkedList<>();
            head0.add(h);
            list.add(head0);
        }
        return list;
    }


}

其他样式设置类：

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;

import java.util.List;


public class TitleStyleUtils extends AbstractCellStyleStrategy {

    private List names;

    private Workbook workbook;

    public TitleStyleUtils(){

    }

    public TitleStyleUtils (List names){
        this.names = names;
    }
    @Override
    protected void initCellStyle(Workbook workbook) {
        this.workbook = workbook;
    }

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        //设置标题说明样式（excel第一行，多个换行符拼接的标题说明文字）
        if(cell.getColumnIndex()==0 && relativeRowIndex==0){
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setWrapText(true);
            //设置 水平居中
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            //设置 垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
            Font font = workbook.createFont();
            //颜色
            font.setColor(IndexedColors.RED.getIndex());
            //加粗
            font.setBold(true);
            //字体
            font.setFontName("宋体");
            //大小
            font.setFontHeightInPoints((short) 12);
            //高度
            workbook.getSheetAt(0).getRow(0).setHeight((short) 3888);
            //宽度
            workbook.getSheetAt(0).setColumnWidth(cell.getColumnIndex(), 10240);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
        }
        //设置表头样式（excel第二行，动态列名称）
        if (relativeRowIndex==1){
            for (int i = 0; i < names.size(); i++) {
                if (cell.getColumnIndex() == i){
                    CellStyle cellStyle = workbook.createCellStyle();
                    //设置 水平居中
                    cellStyle.setAlignment(HorizontalAlignment.LEFT);
                    //设置 垂直居中
                    cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
                    Font font = workbook.createFont();
                    //颜色
                    font.setColor(IndexedColors.BLACK.getIndex());
                    //加粗
                    font.setBold(true);
                    font.setFontName("宋体");
                    font.setFontHeightInPoints((short) 12);
                    cellStyle.setFont(font);
                    cell.setCellStyle(cellStyle);
                }
            }
        }

    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {

    }
}

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.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.List;
import java.util.Map;


public class SelectSheetWriteHandler implements SheetWriteHandler {
    private Map> selectMap;

    private char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L',
            'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};

    public SelectSheetWriteHandler(Map> selectMap) {
        this.selectMap = selectMap;
    }

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

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (selectMap == null || selectMap.size() == 0) {
            return;
        }
        // 需要设置下拉框的sheet页
        Sheet curSheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = curSheet.getDataValidationHelper();
        String dictSheetName = "字典sheet";
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        // 数据字典的sheet页
        Sheet dictSheet = workbook.createSheet(dictSheetName);
        for (Map.Entry> entry : selectMap.entrySet()) {
            // 设置下拉单元格的首行、末行、首列、末列
            CellRangeAddressList rangeAddressList = new CellRangeAddressList(2, 65533, entry.getKey(), entry.getKey());
            int rowLen = entry.getValue().size();
            // 设置字典sheet页的值 每一列一个字典项
            for (int i = 0; i < rowLen; i++) {
                Row row = dictSheet.getRow(i);
                if (row == null) {
                    row = dictSheet.createRow(i);
                }
                row.createCell(entry.getKey()).setCellValue(entry.getValue().get(i));
            }
            String excelColumn = getExcelColumn(entry.getKey());
            // 下拉框数据来源 eg:字典sheet!$B1:$B2
            String refers = dictSheetName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen;
            // 创建可被其他单元格引用的名称
            Name name = workbook.createName();
            // 设置名称的名字
            name.setNameName("dict" + entry.getKey());
            // 设置公式
            name.setRefersToFormula(refers);
            // 设置引用约束
            DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + entry.getKey());
            // 设置约束
            DataValidation validation = helper.createValidation(constraint, rangeAddressList);
            if (validation instanceof HSSFDataValidation) {
                validation.setSuppressDropDownArrow(false);
            } else {
                validation.setSuppressDropDownArrow(true);
                validation.setShowErrorBox(true);
            }
            // 阻止输入非下拉框的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.createErrorBox("提示", "此值与单元格定义格式不一致！");
            // 添加下拉框约束
            writeSheetHolder.getSheet().addValidationData(validation);
        }
    }

    
    private String getExcelColumn(int num) {
        String column = "";
        int len = alphabet.length - 1;
        int first = num / len;
        int second = num % len;
        if (num <= len) {
            column = alphabet[num] + "";
        } else {
            column = alphabet[first - 1] + "";
            if (second == 0) {
                column = column + alphabet[len] + "";
            } else {
                column = column + alphabet[second - 1] + "";
            }
        }
        return column;
    }
}

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import net.sharing.backstage.oa.constant.Constants;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.util.CollectionUtils;

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


public class AutoWidthHandler extends AbstractColumnWidthStyleStrategy {
    private Map> cache = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map maxColumnWidthMap = cache.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>(50);
                cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= Constants.ZERO) {
                if (columnWidth > Constants.TWO_HAN) {
                    columnWidth = Constants.TWO_HAN;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    private Integer dataLength(List cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(Constants.ZERO);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }

}

实体类：

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    
    private String name;
    
    private String workCode;
    
    private String age;
    
    private String select;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserExcel {

    
    @ExcelProperty(value = {"姓名"}, index = 0)
    private String name;
    
    @ExcelIgnore
    private String testName;
    
    @ExcelProperty(value = {"工号"}, index = 1)
    private String workCode;
    
    @ExcelProperty(value = {"年龄"}, index = 2)
    private String age;
    
    @ExcelProperty(value = {"下拉框"}, index = 3)
    private String select;

}