HssfWorkBook实现带有下拉框的Excel模板下载

HssfWorkBook实现带有下拉框的Excel模板下载

参考以下ExcelUtil工具类实现或者参看使用这个链接的文档
https://www.cnblogs.com/jjflover/p/13432218.html

ExcelUtil工具类

package cn.trustway.bg.system.zwfw.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.Map;

public class ExcelUtil {
    /**
     * excel导出，有码值的数据使用下拉框展示。
     * 注意：该方式适用简单类型的下拉框，码值（指下拉框内容）不超过255个字符时使用
     * @param col      列名
     * @param boxMap   码值集合
     * @param firstRow 插入下拉框开始行号
     * @param lastRow  插入下拉框结束行号
     * @param firstCol 插入下拉框开始列号
     * @param lastCol  插入下拉框结束行号
     */
    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) {
            String[] str = cols.split(",");
            //指定0-9行，0-0列为下拉框
            CellRangeAddressList cas = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            //创建下拉数据列
            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str);
            //将下拉数据放入下拉框
            dataValidation = new HSSFDataValidation(cas, dvConstraint);
        }
        return dataValidation;
    }

   /**
     * 适用复杂类型下拉框、码值超过255个字符
     * @param col 列名
     * @param boxMap 码值集合
     * @param sheet
     * @param firstRow 插入下拉框开始行号
     * @param lastRow  插入下拉框结束行号
     * @param firstCol 插入下拉框开始列号
     * @param lastCol  插入下拉框结束行号
     * @param wbCreat
     * @return
     */
    public static HSSFDataValidation createBox4MoreThan255(String col, Map<String,String> boxMap, HSSFSheet sheet,int firstRow, int lastRow, int firstCol, int lastCol,HSSFWorkbook wbCreat) {
      //查询码值集合，获取当前列的码值
        String cols = "";
        if (null != boxMap.get(col)) {
            cols = boxMap.get(col);
        }
        //新建隐藏状态的hidden，用来存储码值。
        String[] dataArray = cols.split(",");
        //隐藏状态的hidden页名称不能重复，添加firstCol值区分
        HSSFSheet hidden = wbCreat.createSheet("hidden"+firstCol);
        HSSFCell cell = null;
        //向创建的hidden页添加码值数据。
        for (int i = 0, length = dataArray.length; i < length; i++) {
            String name = dataArray[i];
            HSSFRow row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }
        //将码值hidden页做成excel公式
        Name namedCell = wbCreat.createName();
        namedCell.setNameName("hidden"+firstCol);
        namedCell.setRefersToFormula("hidden"+firstCol+"!$A$1:$A$" + dataArray.length);
        //确定要在哪些单元格生成下拉框
        DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden"+firstCol);

        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);

        //隐藏码值hidden页
        int sheetNum = wbCreat.getNumberOfSheets();
        for (int i = 1; i < sheetNum; i++) {
            wbCreat.setSheetHidden(i,true);
        }
        sheet.addValidationData(validation);
        return validation;
    }
}

测试

下载模板方法(在excel表格中添加两个下拉框)

public void downloadApasTemplate(HttpServletResponse response, List<String> deptNameList) {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet sheet = hssfWorkbook.createSheet();
        HSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("事项名称");
        row.createCell(1).setCellValue("事项编码");
        row.createCell(2).setCellValue("权利编码");
        row.createCell(3).setCellValue("区域名称");
        row.createCell(4).setCellValue("部门名称");
        row.createCell(5).setCellValue("是否开启(填开启或者关闭)");

        Map<String, String> ssbmMap = new HashMap<>();
        //所属部门的数据
        String ssbm = "SSBM";
        StringBuilder builder = new StringBuilder();
        deptNameList.forEach(item -> {
            if (builder.length() > 0) {
                builder.append(",");
            }
            builder.append(item);
        });
        ssbmMap.put(ssbm, builder.toString());
        //所属区域的数据
        Map<String, String> ssqyMap = new HashMap<>();
        Map<String, String> areaMap = this.areaListToMap();
        List<String> areaList = areaMap.entrySet().stream().map(et -> et.getValue()).collect(Collectors.toList());
        String ssqy = "SSqy";
        StringBuilder bu = new StringBuilder();
        areaList.forEach(item -> {
            if (bu.length() > 0) {
                bu.append(",");
            }
            bu.append(item);
        });
        ssqyMap.put(ssqy, bu.toString());

        //从第1行开始，只有100行才有下拉框，添加下拉框至每一行的第4列，参数两个4：第一个4代表从第五列开始，第二个4代表到下拉到第五列就没了，如果第二个4改为5，则表格的第六列也会有这个下拉框
        HSSFDataValidation deptDataValidation = createBox4MoreThan255(ssbm, ssbmMap, sheet,
                1, 100, 4, 4, hssfWorkbook);
        sheet.addValidationData(deptDataValidation);
        HSSFDataValidation areaDataValidation = createBox4MoreThan255(ssqy, ssqyMap, sheet,
                1, 100, 3, 3, hssfWorkbook);
        sheet.addValidationData(areaDataValidation);
        try {
            String filename = "事项库模板.xls";
            filename = URLEncoder.encode(filename, "UTF8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + filename);
            OutputStream outputStream = response.getOutputStream();
            hssfWorkbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            logger.info("模板下载失败{}", e.getMessage());
        }
    }

效果图

其中区域名称和部门名称（部门名称是有的，只是不好截图展示）都是下拉框