java 动态列表 动态表单设计 java动态excel模板

需求描述：java后端开发过程中，为了满足动态生成excel模板并设置指定单元格为下拉框，且下拉框的数据项来源为动态查询的需求，在基于easyExcel的情况下，使用模板填充的方式，完成该需求。

1. control层
 
public void getTemp(HttpServletResponse response) throws IOException{
    tempService.getTemp(response);
}
 
2. service层
 
void getTemp(HttpServletResponse response);
 
3. service实现类（模板获取及导出相关配置设定，业务逻辑处理等）：
 
@Override
public 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();
        
        //向下新增行填充Config
        FillConfig 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 2022/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;
    }

    @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 = 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:$B2
            String 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);
    }

}

通过以上流程，即可实现本文开端描述的需求，如果对您有所帮助，可以点赞收藏哦~