备注：动态字段导出主要是用了反射的原理，跟excel需要导出的字段一一映射。话不多说，直接上代码；
1.生成的动态模板如图：
如上图，如果下拉框里不是选择的值，会给用户提示，下拉框用来限制用户导入只能选择下拉框中的值
先放controller层调用
@RestController@RequestMapping("/test")@Slf4j@RequiredArgsConstructorpublic 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<String> names = Arrays.asList("姓名","工号","年龄","下拉列");//设置第三行示例数据List<User> userList = new ArrayList<>();User user = new User("张三", "B112233", "18", "下拉值01");userList.add(user);//列字段的英文名称,根据英文名称对应示例数据List<String> fieldEn = Arrays.asList("name","workCode","age","select");//设置第几列显示下拉框，用户只能选择下拉框中的值，非下拉框则不允许保存excelMap<Integer, List<String>> selectMap = new HashMap<>(8);//设置下拉框的值List<String> 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<String> names = Arrays.asList("姓名(自定义)","工号（自定义）","年龄（自定义名称）","下拉列（自定义名称）");//列字段的英文名称,根据英文名称对应示例数据List<String> fieldEn = Arrays.asList("name","workCode","age","select");//要导出的数据List<User> 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<UserExcel> 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.ponent;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 .URLEncoder;import java.util.*;/*** @program: sharing-backstage* @Description: 动态excel工具类* @Author: zwx* @Date: /5/12 13:42*/@Component@Slf4jpublic class DynamicExcelUtils {/*** 动态excel模板下载* @param response 响应* @param title大标题说明，多行使用转义符\n换行* @param names列集合* @param fieldEn 列英文名称* @param selectMap 下拉框* @param fileName 自定义文件名称* @param sheetName 自定义sheet页名称*/public void excelDownloadLink(HttpServletResponse response, String title,List<String> names, List<String> fieldEn ,List list ,Map<Integer, List<String>> selectMap,String sheetName, String fileName) {try {//设置表格第三行的示例数据的值List<List<String>> 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();}}}/*** 动态excel数据导出* @param response 响应* @param fileName 自定义文件名称* @param sheetName 自定义sheet页名称* @param list数据集合*/public void excelExportData(HttpServletResponse response,List<String> names,List<String> fieldEn,List list ,String sheetName, String fileName){try {//设置返回数据的值跟动态列一一对应List<List<String>> 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();}}}/*** 普通excel数据导出(实体类方式)* @param response 响应* @param fileName 自定义文件名称* @param sheetName 自定义sheet页名称* @param list数据集合* @param clazz 实体对象字节码对象*/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<List<String>> setData(List list,List<String> fieldEn){List<List<String>> datas = new ArrayList<>();//对象反射转map方法List<Map<Object, Object>> maps = new ArrayList<>();if (CollectionUtil.isNotEmpty(list)){for (Object o : list) {Class<?> aClass = o.getClass();Field[] fields = aClass.getDeclaredFields();Map<Object, Object> map = new HashMap<>(40);for (Field field : fields) {map.put(field.getName(), getResult(field.getName(), o));}maps.add(map);}for (Map<Object, Object> map : maps) {//用于接收返回数据行？List<String> data = new LinkedList<String>();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<List<String>> head(String[] header, String bigTitle) {List<String> head0 = null;List<List<String>> list = new LinkedList<List<String>>();for (String h : header) {head0 = new LinkedList<>();head0.add(bigTitle);head0.add(h);list.add(head0);}return list;}/*** 数据动态头传入*/private List<List<String>> headData(String[] header) {List<String> head0 = null;List<List<String>> list = new LinkedList<List<String>>();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;/*** @program: sharing-backstage* @Description: 标题样式工具类* @Author: zwx* @Date: /2/21 16:34*/public class TitleStyleUtils extends AbstractCellStyleStrategy {private List<String> names;private Workbook workbook;public TitleStyleUtils(){}public TitleStyleUtils (List<String> names){this.names = names;}@Overrideprotected void initCellStyle(Workbook workbook) {this.workbook = workbook;}@Overrideprotected 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);}}}}@Overrideprotected 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;/*** @program: sharing-backstage* @Description: 处理下拉* @Author: zwx* @Date: /3/3 15:16*/public class SelectSheetWriteHandler implements SheetWriteHandler {private Map<Integer, List<String>> 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<Integer, List<String>> selectMap) {this.selectMap = selectMap;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic 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<Integer, List<String>> 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:$B2String 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);}}/*** 将数字列转化成为字母列** @param num* @return*/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;/*** @program: sharing-backstage* @Description: 动态表头设置自适应宽度* @Author: zwx* @Date: /1/25 15:26*/public class AutoWidthHandler extends AbstractColumnWidthStyleStrategy {private Map<Integer, Map<Integer, Integer>> cache = new HashMap<>();@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);if (needSetWidth) {Map<Integer, Integer> 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<CellData> 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;}}}}}
实体类：
/*** @program: sharing-backstage* @Description: 用户实体类* @Author: zwx* @Date: /5/12 14:21*/@Data@AllArgsConstructor@NoArgsConstructorpublic class User {/*** 姓名*/private String name;/*** 工号*/private String workCode;/*** 年龄*/private String age;/*** 下拉框*/private String select;}
/*** @program: sharing-backstage* @Description: 用户EXCEL类* @Author: zwx* @Date: /5/12 15:58*/@Data@NoArgsConstructor@AllArgsConstructorpublic class UserExcel {/*** 姓名*/@ExcelProperty(value = {"姓名"}, index = 0)private String name;/*** 测试名称*/@ExcelIgnoreprivate String testName;/*** 工号*/@ExcelProperty(value = {"工号"}, index = 1)private String workCode;/*** 年龄*/@ExcelProperty(value = {"年龄"}, index = 2)private String age;/*** 下拉框*/@ExcelProperty(value = {"下拉框"}, index = 3)private String select;}
