POI模板下载级联下拉

package com.**.**.service.common.impl;

import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.CellReference;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;

/**
 * @author : wangbin
 * @Description: TODO
 * @date Date : 2019-10-21 15:30
 */
@Service
@Slf4j
public class ExcelTemplateServiceImpl implements IExcelTemplateService{

    private static final String DICT_SHEET = "DICT_SHEET";

    @Autowired
    private DataDictMapper dataDictMapper;

    @Autowired
    private IPreparationGwbmgxService preparationGwbmgxService;

    private List<PreparationGwlx> postLst = null;

    private Long deptIdSheet = null;

    @Autowired
    private DepartmentMapper departmentMapper;

    @Override
    public boolean getExcelTemplate(ExcelReqVO excelReqVO,HttpServletRequest request, HttpServletResponse response)throws IOException {

        // 1.准备需要生成excel模板的数据
        List<ExportDefinition> edList = getModelData(excelReqVO.getDeptId());

        // 2.生成导出模板
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = createExportSheet(edList, wb,excelReqVO.getDeptId());

        // 3.创建数据字典sheet页
        createDictSheet(edList, wb);

        // 4.设置数据有效性
        setDataValidation(edList, sheet);

        // 5.保存excel到本地
//        OutputStream os = new FileOutputStream(excelReqVO.getFilePath()+"personTemplate4.xls");

        String excelFileName = "personTemplate.xls";
        ServletOutputStream outputStream = response.getOutputStream();

        //根据浏览器判断文件名转码乱码问题
        if(request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0){
            excelFileName = URLEncoder.encode(excelFileName,"UTF-8");
        }else {
            excelFileName = new String(excelFileName.getBytes("UTF-8"),"ISO8859-1");
        }
        response.setContentType("application/octet-stream");
        //直接下载文件名设置
        response.setHeader("Content-Disposition","attachment;filename="+excelFileName);
        //隐藏Sheet
        wb.setSheetHidden(1,true);
        wb.write(outputStream);
        wb.close();
        log.info("模板生成成功");
        return true;
    }

    /** 获取数据字典中的值 */
    private Object getDict(String dict) {
        return initDict().get(dict);
    }
    private Map<String, Object> initDict(){
        List<DataDictRespVO> dataDictRespVOList = dataDictMapper.selectDataDictLst();
        List<String> postNameLst = Lists.newArrayList();
        if(!EmptyUtils.isEmpty(postLst)){
            for (PreparationGwlx item : postLst) {
                postNameLst.add(item.getName());
            }
        }
        List<String> personTypeLst = Lists.newArrayList();
        List<String> rankOneLst = Lists.newArrayList();
        List<String> ranlTwoLst = Lists.newArrayList();
        List<String> rankThreeLst = Lists.newArrayList();
        List<String> positionLst = Lists.newArrayList();
        List<String> rankLst = Lists.newArrayList();
        List<String> educationLst = Lists.newArrayList();
        List<String> nationalityLst = Lists.newArrayList();
        for (DataDictRespVO item : dataDictRespVOList) {
            if("person_type".equals(item.getColumnName()) && ("1".equals(item.getItemCode()) ||
                    "2".equals(item.getItemCode()) ||
                    "3".equals(item.getItemCode()))){
                personTypeLst.add(item.getItemValue());
            }
            if("rank_one".equals(item.getColumnName())){
                rankOneLst.add(item.getItemValue());
            }
            if("rank_two".equals(item.getColumnName())){
                ranlTwoLst.add(item.getItemValue());
            }
            if("rank_three".equals(item.getColumnName())){
                rankThreeLst.add(item.getItemValue());
            }
            if("position".equals(item.getColumnName())){
                positionLst.add(item.getItemValue());
            }
            if("nationality".equals(item.getColumnName())){
                nationalityLst.add(item.getItemValue());
            }
            if("rank".equals(item.getColumnName())){
                rankLst.add(item.getItemValue());
            }
            if("education".equals(item.getColumnName())){
                educationLst.add(item.getItemValue());
            }
        }

        Map<String, Object> dict = new HashMap<>();
        List<String> list = new ArrayList<>();
        dict.put("personType-dict", personTypeLst);
        Map<String, List<String>> subMap = new HashMap<>();
        subMap.put("军官", rankOneLst);
        subMap.put("士官", ranlTwoLst);
        subMap.put("义务兵", rankThreeLst);
        dict.put("rank-dict", subMap);
        dict.put("position-dict", positionLst);
        dict.put("rankLevel-dict", rankLst);
        dict.put("education-dict", educationLst);
        dict.put("nationality-dict", nationalityLst);
        dict.put("post-dict", postNameLst);
        list.add("男");
        list.add("女");
        dict.put("gender-dict",list);
        return dict;
    }

    @Override
    public List<ExportDefinition> getModelData(Long deptId){
        //TODO此处存在一定风险
        postLst = preparationGwbmgxService.queryPostByDepart(new PreparationGwbmgx(deptId));
        this.deptIdSheet = deptId;
        // 1.准备需要生成excel模板的数据
        List<ExportDefinition> edList = new ArrayList<>(1);
        edList.add(new ExportDefinition("姓名(必填)", "name", null, null, null));
        edList.add(new ExportDefinition("人员类别(必填)", "person_type", "personType-dict", "rank-dict", "military_rank"));
        edList.add(new ExportDefinition("军衔(必填)", "military_rank", "rank-dict", "", ""));
        edList.add(new ExportDefinition("职务", "position", "position-dict", null, null));
        edList.add(new ExportDefinition("职务级别", "rank", "rankLevel-dict", null, null));
        edList.add(new ExportDefinition("岗位", "post", "post-dict", null, null));
        edList.add(new ExportDefinition("军官证号(必填)", "officerNum", null, null, null));
        edList.add(new ExportDefinition("职级时间(yyyy-MM-dd)", "militaryRankTime", null, null, null));

        edList.add(new ExportDefinition("身份证号(必填)", "idNum", null, null, null));
        edList.add(new ExportDefinition("性别(必填)", "gender", "gender-dict", null, null));
        edList.add(new ExportDefinition("出生年月(yyyy-MM-dd)", "bronDate", null, null, null));
        edList.add(new ExportDefinition("籍贯", "birthPlace", null, null, null));
        edList.add(new ExportDefinition("民族(必填)", "nationality", "nationality-dict", null, null));
        edList.add(new ExportDefinition("毕业院校", "graduated", null, null, null));
        edList.add(new ExportDefinition("联系电话", "phoneNumber", null, null, null));
        edList.add(new ExportDefinition("联系地址", "address", null, null, null));
        edList.add(new ExportDefinition("学历", "education", "education-dict", null, null));
        edList.add(new ExportDefinition("备注", "remarks", null, null, null));
//        edList.add(new ExportDefinition("部门名称", "deptName", null, null, null));
//        edList.add(new ExportDefinition("deptId", "deptId", null, null, null));
        edList.add(new ExportDefinition("ID", "ID", null, null, null));
        return edList;
    }

    private void createDataValidateSubList(Sheet sheet, ExportDefinition ed) {
        int rowIndex = ed.getRowIndex();
        CellRangeAddressList cal;
        DVConstraint constraint;
        CellReference cr;
        DataValidation dataValidation;
        log.info("ed"+ed);
        for (int i = 0; i < 200; i++) {
            int tempRowIndex = ++rowIndex;
            cal = new CellRangeAddressList(tempRowIndex, tempRowIndex, ed.getCellIndex(), ed.getCellIndex());
            cr = new CellReference(rowIndex, ed.getCellIndex() - 1, true, true);
            constraint = DVConstraint.createFormulaListConstraint("INDIRECT(" + cr.formatAsString() + ")");
            dataValidation = new HSSFDataValidation(cal, constraint);
            dataValidation.setSuppressDropDownArrow(false);
            dataValidation.createPromptBox("操作提示", "请选择下拉选中的值");
            dataValidation.createErrorBox("错误提示", "请从下拉选中选择，不要随便输入");
            sheet.addValidationData(dataValidation);
        }
    }

    /**
     * @param edList
     * @param sheet
     */
    @Override
    public void setDataValidation(List<ExportDefinition> edList, Sheet sheet) {
        for (ExportDefinition ed : edList) {
            if (ed.isValidate()) {// 说明是下拉选
                DVConstraint constraint = DVConstraint.createFormulaListConstraint(ed.getField());
                if (null == ed.getRefName()) {// 说明是一级下拉选
                    createDataValidate(sheet, ed, constraint);
                } else {// 说明是二级下拉选
                    createDataValidateSubList(sheet, ed);
                }
            }
        }
    }

    /**
     * @param sheet
     * @param ed
     * @param constraint
     */
    private void createDataValidate(Sheet sheet, ExportDefinition ed, DVConstraint constraint) {
        CellRangeAddressList regions = new CellRangeAddressList(ed.getRowIndex() + 1, ed.getRowIndex() + 100, ed.getCellIndex(), ed.getCellIndex());
        DataValidation dataValidation = null;
        dataValidation = new HSSFDataValidation(regions, constraint);
        dataValidation.setSuppressDropDownArrow(false);
        if("post".equals(ed.getField())){
            // 设置提示信息
            dataValidation.createPromptBox("操作提示", "多选请按照[主任,科员,**]模式输入");
        }else {
            // 设置提示信息
            dataValidation.createPromptBox("操作提示", "请选择下拉选中的值");
            // 设置输入错误信息
            dataValidation.createErrorBox("错误提示", "请从下拉选中选择，不要随便输入");
        }
        sheet.addValidationData(dataValidation);
    }

    /**
     * @param edList
     * @param wb
     */
    @Override
    public void createDictSheet(List<ExportDefinition> edList, Workbook wb) {
        Sheet sheet = wb.createSheet(DICT_SHEET);
        RowCellIndex rci = new RowCellIndex(0, 0);
        for (ExportDefinition ed : edList) {
            String mainDict = ed.getMainDict();
            if (null != mainDict && null == ed.getRefName()) {// 是第一个下拉选
                List<String> mainDictList = (List<String>) getDict(mainDict);
                    String refersToFormula = createDictAndReturnRefFormula(sheet, rci, mainDictList);
                // 创建 命名管理
                createName(wb, ed.getField(), refersToFormula);
                ed.setValidate(true);

            }
            if (null != mainDict && null != ed.getSubDict() && null != ed.getSubField()) {// 联动时加载ed.getSubField()的数据
                ExportDefinition subEd = fiterByField(edList, ed.getSubField());// 获取需要级联的那个字段
                if (null == subEd) {
                    continue;
                }
                subEd.setRefName(ed.getPoint());// 保存主下拉选的位置
                subEd.setValidate(true);
                Map<String, List<String>> subDictListMap = (Map<String, List<String>>) getDict(ed.getSubDict());
                for (Map.Entry<String, List<String>> entry : subDictListMap.entrySet()) {
                    String refersToFormula = createDictAndReturnRefFormula(sheet, rci, entry.getValue());
                    // 创建 命名管理
                    createName(wb, entry.getKey(), refersToFormula);
                }
            }
        }
    }

    /**
     * @param sheet
     * @param rci
     * @param
     * @return
     */
    private String createDictAndReturnRefFormula(Sheet sheet, RowCellIndex rci, List<String> datas) {
        Row row = sheet.createRow(rci.incrementRowIndexAndGet());
        rci.setCellIndex(0);
        int startRow = rci.getRowIndex();
        int startCell = rci.getCellIndex();
        for (String dict : datas) {
            row.createCell(rci.incrementCellIndexAndGet()).setCellValue(dict);

        }
        int endRow = rci.getRowIndex();
        int endCell = rci.getCellIndex();
        String startName = new CellReference(DICT_SHEET, startRow, startCell, true, true).formatAsString();
        String endName = new CellReference(endRow, endCell, true, true).formatAsString();
        String refersToFormula = startName + ":" + endName;


        log.info(refersToFormula);
        return refersToFormula;
    }

    /**
     * @param wb
     * @param nameName
     *            表示命名管理的名字
     * @param refersToFormula
     */
    private void createName(Workbook wb, String nameName, String refersToFormula) {
        Name name = wb.createName();
        name.setNameName(nameName);
        name.setRefersToFormula(refersToFormula);
    }

    private ExportDefinition fiterByField(List<ExportDefinition> edList, String field) {
        for (ExportDefinition ed : edList) {
            if (Objects.equals(ed.getField(), field)) {
                return ed;
            }
        }
        return null;
    }

    /**
     * @param edList
     * @param wb
     */
    @Override
    public Sheet createExportSheet(List<ExportDefinition> edList, Workbook wb,Long deptId) {
        Department dept = departmentMapper.selectById(deptId);
        Sheet sheet = wb.createSheet(dept.getName());

        RowCellIndex rci = new RowCellIndex(0, -1);
        Row row = sheet.createRow(rci.getRowIndex());

        CellReference cr = null;
        for (ExportDefinition ed : edList) {
            row.createCell(rci.incrementCellIndexAndGet()).setCellValue(ed.getTitle());
            ed.setRowIndex(rci.getRowIndex());
            ed.setCellIndex(rci.getCellIndex());
            cr = new CellReference(ed.getRowIndex() + 1, ed.getCellIndex(), true, true);
            ed.setPoint(cr.formatAsString());
        }

        sheet.setDefaultColumnWidth(18);
        sheet.setDefaultRowHeight((short) 400);
        //设置人员ID列隐藏
        sheet.setColumnWidth(18,1);
//        sheet.setColumnWidth(19,1);
        return sheet;
    }

}

package com.**.**.service.common.impl;

import lombok.Data;

/**
 * @author : wangbin
 * @Description: TODO
 * @date Date : 2019-10-21 9:15
 */
@Data
public class ExportDefinition {

    private String title; // 标题
    private String field; // 字段
    private int rowIndex; // 所在的行
    private int cellIndex; // 所在的列
    private String mainDict; // 主字典-用于加载主字典的数据
    private String subDict; // 子字典-用于加载subField的数据
    private String subField; // 即需要级联的字典
    private String refName; // 主字段所在的位置
    private String point; // 标题的坐标
    private boolean validate;// 是否设置数据的有限性
    public ExportDefinition(String title, String field, String mainDict, String subDict, String subField) {
        this.title = title;
        this.field = field;
        this.mainDict = mainDict;
        this.subDict = subDict;
        this.subField = subField;
    }
}

package com.**.**.service.common.impl;

/**
 * @author : wangbin
 * @Description: TODO
 * @date Date : 2019-10-21 9:14
 */
public class RowCellIndex {

    /** 单元格的行索引 */
    private int rowIndex;
    /** 单元格的列索引 */
    private int cellIndex;
    public RowCellIndex(int rowIndex, int cellIndex) {
        this.rowIndex = rowIndex;
        this.cellIndex = cellIndex;
    }
    public int getRowIndex() {
        return rowIndex;
    }
    public void setRowIndex(int rowIndex) {
        this.rowIndex = rowIndex;
    }
    public int getCellIndex() {
        return cellIndex;
    }
    public void setCellIndex(int cellIndex) {
        this.cellIndex = cellIndex;
    }
    public int incrementRowIndexAndGet() {
        this.rowIndex++;
        return this.getRowIndex();
    }
    public int incrementCellIndexAndGet() {
        this.cellIndex++;
        return this.getCellIndex();
    }
}