JAVA动态生成excel模板；列自定义下拉框赋值

哈喽，2023大家开工大吉啊！财源滚滚！

业务需求：需要生成excel模板，且对部分列设置下拉框，进行动态赋值，效果如下：

拿上图举例：针对省这一列，不是填写，而是选择数据，也就是说我们生成excel文件的时候需要把数据填充到下拉框的列中。

大体逻辑就是：java生成excel文件，在生成excel文件的时候将部分列是设置成下拉框，并赋值。

而在 Java 中，操作 excel 目前有两个主流框架，分别是：

apache 的 poi
Apache POI是基于DOM方式进行解析，将文件直接加载内存，速度较快，适合文件数据量不大的应用场景。它分别对不同格式的文件提供不同的文件解析：

    HSSF：  操作Microsoft Excel格式。
    XSSF：  操作Microsoft Excel  OOXML格式。

HSSF主要用于解析.xls格式的Excel文件，而XSSF主要用于解析.xlsx格式的Excel文件

Java Excel
Java Excel是一开放源码项目，通过它Java开发人员可以操作Excel文件(读取，创建，更新等)。

由于其小巧 易用的特点, 逐渐已经取代了 POI-excel的地位。

HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls；

XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx。

对于不同版本的EXCEL文档要使用不同的工具类，如果使用错了，会提示如下错误信息：

org.apache.poi.openxml4j.exceptions.InvalidOperationExceptionorg.apache.poi.poifs.filesystem.OfficeXmlFileException

java生成excel文件逻辑：
1：创建excel
2：在excel中创建表格
3：在表格中输入列名
4：输入数据

代码如下

	 //导出标头信息
    List<String> headers = Arrays.asList("客户姓名", "客户联系方式", "服务对象", "联系方式", "性别", "出生日期(yyyy-MM-dd)", "身份证号", "关系", "健康状况", "省", "市", "区", "服务地址详情", "一级来源渠道", "二级来源渠道", "首洽日期(yyyy-MM-dd)");
    
   // 创建workbook；可以理解为excel
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
    
   //创建sheet：可以理解为在excel中创建表格
    HSSFSheet sheet = hssfWorkbook.createSheet();
    
   // 创建表头，供用户输入：可以理解为为表格输入列名
    initHeaders(hssfWorkbook, sheet, headers);
	
	//对性别做下拉处理；由标头可知性别位于第五列（下标从0开始，也就是4代表性别）
   String sexType = "sex";
    Map<String, String> sexMap = new HashMap<>();
    //key放性别的字段，value逗号拼接性别的所有值
    sexMap.put(sexType, "男,女,未知");
    // 1000表示该列填充1000行
    HSSFDataValidation sexValidation = ExcelUtil.createBox(sexType, sexMap, 1, 1000, 4, 4);
    if (sexValidation != null) {
        sheet.addValidationData(sexValidation);
    }

    String relationType = "relation";
    Map<String, String> relationTypeMap = new HashMap<>();
    relationTypeMap.put(relationType, relationName);
    HSSFDataValidation relationValidation = ExcelUtil.createBox(relationType, relationTypeMap, 1, 1000, 7, 7);
    if (relationValidation != null) {
        sheet.addValidationData(relationValidation);
    }

	//省市区三级联动处理
    SysCity cityParam = new SysCity();
    List<SysCity> araeList = sysCityMapper.selectSysCityList(cityParam);

    //所有省级信息:去除ID为36的请选择信息
    List<SysCity> proList = araeList.stream().filter(model -> "1".equals(model.getType().toString()) && !"36".equals(model.getId().toString())).collect(Collectors.toList());
    
    HSSFSheet mapSheet = hssfWorkbook.createSheet("MAP");
    hssfWorkbook.setSheetHidden(hssfWorkbook.getSheetIndex(mapSheet), false);
    //所有一级
    List<String> mapOneList = new ArrayList<String>();
    //市区关系
    Map<String, List<String>> map = new HashMap<String, List<String>>();


    proList.stream().forEach(model -> {
        mapOneList.add(model.getCityname());
        //获取对应市级名称信息
        List<String> cityNameList = araeList.stream().filter(as -> (as.getPid().toString()).equals(model.getId().toString())).map(ma -> ma.getCityname()).collect(Collectors.toList());
        map.put(model.getCityname(), cityNameList);
        //获取市级信息
        List<SysCity> cityList = araeList.stream().filter(as -> (as.getPid().toString()).equals(model.getId().toString())).collect(Collectors.toList());
        cityList.stream().forEach(city -> {
            //获取对应市级名称信息
            List<String> conturyNameList = araeList.stream().filter(as -> (as.getPid().toString()).equals(city.getId().toString())).map(ma -> ma.getCityname()).collect(Collectors.toList());
            map.put(city.getCityname(), conturyNameList);
        });


    });

    // 3.写入数据 将数据写入sheet中并做好关联关系
    writeData(hssfWorkbook, mapSheet, mapOneList, map);
    // 4.设置数据有效性
    setDataValid(hssfWorkbook, sheet, mapOneList, map, "1", "so");

    /**
     *
     * 渠道来源二级联动处理
     *
     */

    List<BaseSourceChannel> channelList = new BaseSourceChannel();
    //一级线索
    List<String> fatherMap = new ArrayList<String>();
    //二级线索
    Map<String, List<String>> sonMap = new HashMap<String, List<String>>();
    //一级线索
    List<BaseSourceChannel> parentSourceList = channelList.stream().filter(x -> "1".equals(x.getType())).collect(Collectors.toList());

    parentSourceList.stream().forEach(source -> {
        fatherMap.add(source.getSourceName());
        //一级对应的二级线索
        List<String> sonSourceList = channelList.stream().filter(x -> x.getPid().equals(source.getId())).map(y -> y.getSourceName()).collect(Collectors.toList());

        sonMap.put(source.getSourceName(), sonSourceList);

    });
    HSSFSheet sourceSheet = hssfWorkbook.createSheet("sourceMap");
    writeData(hssfWorkbook, sourceSheet, fatherMap, sonMap);
    setDataValid(hssfWorkbook, sheet, fatherMap, sonMap, "2", "source");


    String filename = UUID.randomUUID().toString() + "_测试模板.xlsx";

    String downloadPath = Global.getDownloadPath() + filename;
    File desc = new File(downloadPath);
    if (!desc.getParentFile().exists()) {
        desc.getParentFile().mkdirs();
    }
    FileOutputStream out = null;
    try {
        out = new FileOutputStream(downloadPath);
        hssfWorkbook.write(out);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (out != null) {
            try {
                out.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    return AjaxResult.success(filename);

好了，代码已经提供，有什么不明白的欢迎互相讨论。

createBox 方法如下

   /**
     *  excel导出，有码值的数据使用下拉框展示。
     * @param col             列名
     * @param boxMap          码值集合
     * @param firstRow        插入下拉框开始行号
     * @param lastRow         插入下拉框结束行号
     * @param firstCol        插入下拉框开始列号
     * @param lastCol         插入下拉框结束行号
     * @return
     */
    public static HSSFDataValidation createBox(String col, Map<String, String> boxMap, int firstRow, int lastRow, int firstCol, int lastCol) {
        HSSFDataValidation dataValidation = null;
        //查询码值表
        String cols = "";
        if(null != boxMap.get(col)) {
            cols = boxMap.get(col);
        }
        //设置下拉框
        if(cols.length() > 0 && null != cols) {
            String str[] = cols.split(",");
            //指定行，列为下拉框
            CellRangeAddressList cas = new CellRangeAddressList(firstRow , lastRow , firstCol , lastCol);
            //创建下拉数据列
            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str);
            //将下拉数据放入下拉框
            dataValidation = new HSSFDataValidation(cas, dvConstraint);
        }
        return dataValidation;
    }

生成页面表头方法：initHeaders

/**
 * 生成主页面表头
 *
 * @param wb
 * @param mainSheet
 * @param headers
 */
private void initHeaders(HSSFWorkbook wb, HSSFSheet mainSheet, List<String> headers) {
    //表头样式
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
    //字体样式
    HSSFFont fontStyle = wb.createFont();
    fontStyle.setFontName("微软雅黑");
    fontStyle.setFontHeightInPoints((short) 12);
    style.setFont(fontStyle);
    //生成主内容
    HSSFRow rowFirst = mainSheet.createRow(0);//第一个sheet的第一行为标题
    mainSheet.createFreezePane(0, 1, 0, 1); //冻结第一行
    //写标题
    for (int i = 0; i < headers.size(); i++) {
        HSSFCell cell = rowFirst.createCell(i); //获取第一行的每个单元格
        mainSheet.setColumnWidth(i, 4000); //设置每列的列宽
        cell.setCellStyle(style); //加样式
        cell.setCellValue(headers.get(i)); //往单元格里写数据
    }
}