导出excel 自定义模板

CustomSheetWriteHandler

package org.springblade.common.utils;


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.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.Map;

public class CustomSheetWriteHandler implements SheetWriteHandler {

	private Map<Integer, String[]> mapDropDown;

	public CustomSheetWriteHandler(Map<Integer, String[]> mapDropDown) {
		this.mapDropDown = mapDropDown;
	}

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

	}

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

		//获取工作簿
		Sheet sheet = writeSheetHolder.getSheet();
		///开始设置下拉框
		DataValidationHelper helper = sheet.getDataValidationHelper();
		//设置下拉框
		for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
			/*起始行、终止行、起始列、终止列  起始行为1即表示表头不设置**/
			//这里设置65535可能又问题，因为这个是excel的最大行数，如果数据量超过这个数，就会报错
			CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
			/*设置下拉框数据**/
			DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
			DataValidation dataValidation = helper.createValidation(constraint, addressList);

			//阻止输入非下拉选项的值
			dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
			dataValidation.setShowErrorBox(true);
			dataValidation.setSuppressDropDownArrow(true);
			dataValidation.createErrorBox("提示", "输入值与单元格定义格式不一致");
			dataValidation.createPromptBox("填写说明", "填写内容只能为下拉数据集中的类型");

			sheet.addValidationData(dataValidation);
		}
	}
}

controller

	/**
	 * 下载导入模板
	 *
	 * @param response
	 * @throws IOException
	 */
	@GetMapping("/downloadExcelTemplate")
	public void downloadExcelTempplate(HttpServletResponse response) throws IOException {
		List<RegionAnalysisParkAttractExcel> list = new ArrayList<>();

		//定义一个map key是需要添加下拉框的列的index value是下拉框数据
		Map<Integer, String[]> mapDropDown = new HashMap<>();
		List<String> list1=regionAnalysisParkAttractService.getAllProject();
		String[] belongOwner =  list1.toArray(new String[list.size()]);
		String[] year =  {"2018","2019","2020","2021","2022","2023","2024"};
		String[] month =  {"01","02","03","04","05","06","07","08","09","10","11","12"};
		//下拉选在Excel中对应的列
		mapDropDown.put(4,belongOwner);
		mapDropDown.put(0,year);
		mapDropDown.put(1,month);
		// 这里URLEncoder.encode可以防止中文乱码 easyexcel没有关系
		String fileName = URLEncoder.encode("园区招商引资到位资金模板", "UTF-8");
		response.setContentType("application/vnd.ms-excel");
		response.setCharacterEncoding("utf-8");
		response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
		//需要下拉框处理，关键是registerWriteHandler
		EasyExcel.write(response.getOutputStream(), RegionAnalysisParkAttractExcel.class).sheet("园区招商引资到位资金模板").registerWriteHandler(new CustomSheetWriteHandler(mapDropDown)).doWrite(list);
	}

实体类

/*
 *      Copyright (c) 2018-2028, Chill Zhuang All rights reserved.
 *
 *  Redistribution and use in source and binary forms, with or without
 *  modification, are permitted provided that the following conditions are met:
 *
 *  Redistributions of source code must retain the above copyright notice,
 *  this list of conditions and the following disclaimer.
 *  Redistributions in binary form must reproduce the above copyright
 *  notice, this list of conditions and the following disclaimer in the
 *  documentation and/or other materials provided with the distribution.
 *  Neither the name of the dreamlu.net developer nor the names of its
 *  contributors may be used to endorse or promote products derived from
 *  this software without specific prior written permission.
 *  Author: Chill 庄骞 (smallchill@163.com)
 */
package org.springblade.modules.ks.regionAnalysisParkAttract.excel;


import com.alibaba.excel.annotation.ExcelIgnore;
import lombok.Data;

import java.util.Date;
import java.math.BigDecimal;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import java.io.Serializable;


/**
 * 区域分析_园区招商引资到位资金 Excel实体类
 *
 * @author BladeX
 * @since 2023-10-25
 */
@Data
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(18)
public class RegionAnalysisParkAttractExcel implements Serializable {

	private static final long serialVersionUID = 1L;

	/**
	 * 年份
	 */
	@ColumnWidth(20)
	@ExcelProperty("年份")
	private String year;
	/**
	 * 月份
	 */
	@ColumnWidth(20)
	@ExcelProperty("月份")
	private String month;

	/**
	 * 金额
	 */
	@ColumnWidth(20)
	@ExcelProperty("到位资金(亿元)")
	private BigDecimal value;

	@ColumnWidth(20)
	@ExcelProperty("目标资金(亿元)")
	private BigDecimal inMoney;

	/**
	 * 园区id
	 */
	@ColumnWidth(20)
	@ExcelProperty("区域")
	private Long regionId;

	/**
	 * 环比
	 */
	@ColumnWidth(20)
	@ExcelProperty("完成情况(%)")
	private String percent;

	/**
	 * 园区名字
	 */
	@ColumnWidth(20)
	@ExcelProperty("园区名字")
	@ExcelIgnore
	private String regionName;
	/**
	 * 是否已删除
	 */
	@ColumnWidth(20)
	@ExcelProperty("是否已删除")
	@ExcelIgnore
	private Integer isDeleted;

}

参考链接：导出带下拉选项的Excel基于EasyExcel实现_easyexcel 下拉框-CSDN博客