需求描述：java后端开发过程中，为了满足动态生成excel模板并设置指定单元格为下拉框，且下拉框的数据项来源为动态查询的需求，在基于easyExcel的情况下，使用模板填充的方式，完成该需求。
1. control层
public void getTemp(HttpServletResponse response) throws IOException{tempService.getTemp(response);}
2. service层
void getTemp(HttpServletResponse response);
3. service实现类（模板获取及导出相关配置设定，业务逻辑处理等）：
@Overridepublic void getTemp(HttpServletResponse response){//动态模板需填充的数据List<Object> data = new ArrayList<>();//下拉列表1数据项List<String> selectList1 = new ArrayList<>();//下拉列表2数据项List<String> selectList2 = new ArrayList<>();//将下拉列表数据放置在一个map中Map<Integer, List<String>> selectMap = new HashMap<>();selectMap.put(1,selectList1);selectMap.put(2,selectList2);//此处firstRow为需要设置下拉框的起始行位置，可根据自身需求需要调整设置//此处lastRow为需要设置下拉框的截止行位置，可根据自身需求需要调整设置Integer firstRow = 0；Integet lastRow = 100;//使用模板填充导出的相关配置OutputStream out = null;BufferedOutputStream bos = null;try {//获取到已提前制作好的填充模板String templateFileName = FileUtil.getPath() + "template" + File.separator + "Temp.xlsx";//为动态生成的模板命名（带时间区分）String fileNameWithTime = "导入模板" + DateTimeUtil.formatDate(new Date(), DateTimeUtil.SHORT_TIME_FORMAT) + ".xlsx";//设置字符编码标准等response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");String fileName = URLEncoder.encode(fileNameWithTime,"utf-8");response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));out = response.getOutputStream();bos = new BufferedOutputStream(out);ExcelWriter excelWriter = EasyExcel.write(bos).withTemplate(templateFileName).build();//此处用到的"registerWriteHandler()"用于设置相应格式，“SelectSheetWriteHandler(selectMap,firstRow,lastRow)”为封装的设置单元格下拉框的工具类 WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(new SelectSheetWriteHandler(selectMap,firstRow,100)).build();//向下新增行填充ConfigFillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();excelWriter.fill(data,fillConfig,writeSheet);excelWriter.finish();bos.flush();}catch (IOException e){e.printStackTrace();response.reset();response.setCharacterEncoding("utf-8");response.setContentType("application/json");try {response.getWriter().println("打印失败");}catch (IOException ex){ex.printStackTrace();}}}
4.SelectSheetWriteHandler工具类封装
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;/*** @author XXX* @Description: 处理下拉* @date /8/10 17:39*/public class SelectSheetWriteHandler implements SheetWriteHandler {
/*** 下拉框数据项Map, key为第几列，List<String>为下拉框数据项*/private Map<Integer, List<String>> selectMap;/*** 设置下拉框位置首行*/private Integer firstRow;/*** 设置下拉框位置末行*/private Integer lastRow;/*** 数据字典集*/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'};/*** @param selectMap 设置下拉框的数据项* @param firstRow 设置下拉框的开始行（实质从firstRow的下一行开始生效）* @param lastRow 设置下拉框的最后一行*/public SelectSheetWriteHandler(Map<Integer, List<String>> selectMap,Integer firstRow, Integer lastRow){this.selectMap = selectMap;this.firstRow = firstRow;this.lastRow = lastRow;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder){}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {if(selectMap == null || selectMap.size() ==0){return;}Sheet sheet = writeSheetHolder.getSheet();/// 开始设置下拉框DataValidationHelper helper = sheet.getDataValidationHelper();// 设置下拉框String dictSheetName = "字典sheet";Workbook workbook = writeWorkbookHolder.getWorkbook();//数据字典的sheet页Sheet dictSheet = workbook.createSheet(dictSheetName);//将数据字典的sheet隐藏（对用户不可见）workbook.setSheetHidden(workbook.getSheetIndex(dictSheet),true);for (Map.Entry<Integer, List<String>> entry : selectMap.entrySet()) {/*** 起始行、终止行、起始列、终止列 **/CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, 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.createExplicitListConstraint((String[]) entry.getValue().toArray());DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + entry.getKey());DataValidation dataValidation = helper.createValidation(constraint, addressList);/*** 处理Excel兼容性问题 **/if (dataValidation instanceof HSSFDataValidation) {dataValidation.setSuppressDropDownArrow(false);} else {dataValidation.setSuppressDropDownArrow(true);dataValidation.setShowErrorBox(true);}//阻止输入非下拉框的值dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);dataValidation.createErrorBox("提示", "此值与单元格定义格式不一致！");sheet.addValidationData(dataValidation);}}/*** 将数字列转化成为字母列* 主要作用在于根据传入的列获取数据字典sheet中对应列* @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;}}
5. FileUtil工具类
public final class FileUtil {public static InputStream getResourcesFileInputStream(String fileName){return Thread.currentThread().getContextClassLoader().getResourceAsStream("" + fileName);}public static String getPath(){return FileUtil.class.getResource("/").getPath();}public static File createNewFile(String pathName){File file = new File(getPath() + pathName);if(file.exists()){file.delete();}else{if(!file.getParentFile().exists()){file.getParentFile().mkdirs();}}return file;}public static File readFile(String pathName){return new File(getPath() + pathName);}}
通过以上流程，即可实现本文开端描述的需求，如果对您有所帮助，可以点赞收藏哦~
