poi实现excel级联下拉框

poi功能很强大，这里用来实现一个excel级联的下拉框

package poiExcel;

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

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author : haojiangtao
 * @Description :
 * @Date : 14:20 2019/11/14
 * @Modify :
 **/
public class MyExcel {
    private static final String PRICE_SHEET_NAME = "PRICE_SHEET_NAME";

    private static final String MODEL_TYPE_SHEET_NAME = "MODEL_TYPE_SHEET_NAME";

    private static final int XLS_MAX_ROW = 65535; //0开始

    @Test
    public void cascadeExcel() {
        List<String> headers = Arrays.asList("耗材类型", "品牌型号", "数量", "价格");
        List<Integer> priceList = Arrays.asList(50, 100, 1000, 1200);
        List<String> typeList = Arrays.asList("鼠标", "键盘", "电脑");
        Map<String, List<String>> typeModelMap = new HashMap<>();
        typeModelMap.put("鼠标", Arrays.asList("雷蛇鼠标", "赛睿鼠标", "樱桃鼠标"));
        typeModelMap.put("键盘", Arrays.asList("87键盘", "104键盘"));
        typeModelMap.put("电脑", Arrays.asList("惠普", "戴尔"));
        MyExcel.createStoreInExcelTemplate("f:/类型型号.xls", headers,
                priceList, typeList, typeModelMap);
    }

    private static void createStoreInExcelTemplate(String filePath, List<String> headers, List<Integer> priceList,
                                                   List<String> typeList, Map<String, List<String>> typeModelMap) {
        FileOutputStream out = null;
        File file;
        try {
            //指定文件
            file = new File(filePath);
            //文件流
            out = new FileOutputStream(file);
            //工作簿
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet mainSheet = wb.createSheet("sheet1");
            //将存储下拉框数据的sheet隐藏
            //wb.setSheetHidden(2, true);
            HSSFSheet typeModelSheet = wb.createSheet("sheet2");
            HSSFSheet priceSheet = wb.createSheet("sheet3");
            //初始化表头数据
            initHeaders(wb, mainSheet, headers);
            //价格下拉框，类型型号下拉框
            initPrice(wb, priceSheet, priceList);
            initTypeAndModel(wb, typeModelSheet, typeList, typeModelMap);
            //在主sheet里面设置下拉框校验
            initSheetNameMapping(mainSheet);
            //在主sheet里面设置数量校验
            initCount(mainSheet);
            out.flush();
            wb.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 初始化表头
     *
     * @param wb
     * @param mainSheet
     * @param headers
     */
    private static void initHeaders(HSSFWorkbook wb, HSSFSheet mainSheet, List<String> headers) {
        //表头样式,居中加粗
        HSSFCellStyle style = wb.createCellStyle();
        boldStyle(wb, centerStyle(style));

        //生成sheet1内容，第一个sheet的第一行为标题
        HSSFRow rowFirst = mainSheet.createRow(0);
        //冻结第一行
        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)); //往单元格里写数据
        }
    }

    /**
     * 居中
     *
     * @param cellStyle
     * @return
     */
    public static CellStyle centerStyle(CellStyle cellStyle) {
        // 创建一个居中格式
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return cellStyle;
    }

    /**
     * 加粗
     *
     * @param workbook
     * @param cellStyle
     * @return
     */
    public static CellStyle boldStyle(Workbook workbook, CellStyle cellStyle) {
        Font fontStyle = workbook.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short) 12);
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        cellStyle.setFont(fontStyle);
        return cellStyle;
    }

    /**
     * 设置价格下拉框1.设置值 2.设置下拉框
     *
     * @param wb
     * @param priceSheet
     * @param priceList
     */
    private static void initPrice(HSSFWorkbook wb, HSSFSheet priceSheet, List<Integer> priceList) {
        writePrice(wb, priceSheet, priceList);
        initPriceNameMapping(wb, priceSheet.getSheetName(), priceList.size());
    }

    /**
     * 设置价格下拉框1.设置值
     *
     * @param wb
     * @param priceSheet
     * @param priceList
     */
    private static void writePrice(HSSFWorkbook wb, HSSFSheet priceSheet, List<Integer> priceList) {
        for (int i = 0; i < priceList.size(); i++) {
            HSSFRow row = priceSheet.createRow(i);
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(priceList.get(i));
        }
    }

    /**
     * 设置价格下拉框2.设置下拉框
     *
     * @param workbook
     * @param sheetName
     * @param priceCount
     */
    private static void initPriceNameMapping(HSSFWorkbook workbook, String sheetName, int priceCount) {
        Name name = workbook.createName();
        name.setNameName(PRICE_SHEET_NAME);
        name.setRefersToFormula(sheetName + "!$A$1:$A$" + priceCount);
    }

    /**
     * 设置类型型号级联下拉框 1.设置类型值 2.设置型号下拉框 3.设置类型级联下拉框
     *
     * @param workbook
     * @param tmSheet
     * @param typeList
     * @param typeModelMap
     */
    private static void initTypeAndModel(HSSFWorkbook workbook, HSSFSheet tmSheet,
                                         List<String> typeList, Map<String, List<String>> typeModelMap) {
        writeTypes(workbook, tmSheet, typeList);
        writeModels(workbook, tmSheet, typeList, typeModelMap);
        initTypeNameMapping(workbook, tmSheet.getSheetName(), typeList.size());
    }

    /**
     * 设置类型型号级联下拉框 1.设置类型值
     *
     * @param workbook
     * @param modelTypeSheet
     * @param typeList
     */
    private static void writeTypes(HSSFWorkbook workbook, HSSFSheet modelTypeSheet, List<String> typeList) {
        HSSFRow row = modelTypeSheet.getRow(0) == null ? modelTypeSheet.createRow(0)
                : modelTypeSheet.createRow(0);
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        boldStyle(workbook, cellStyle);
        //if (CollectionUtils.isNotEmpty(modelList)) {
        if (null != typeList) {
            for (int i = 0; i < typeList.size(); i++) {
                HSSFCell cell = row.getCell(i) == null ? row.createCell(i) : row.getCell(i);
                cell.setCellValue(typeList.get(i));
                cell.setCellStyle(cellStyle);
            }
        }
    }

    /**
     * 设置类型型号级联下拉框 2.设置型号下拉框
     *
     * @param workbook
     * @param tmSheet
     * @param typeList
     * @param typeModelMap
     */
    private static void writeModels(HSSFWorkbook workbook, HSSFSheet tmSheet,
                                    List<String> typeList, Map<String, List<String>> typeModelMap) {
        for (int i = 0; i < typeList.size(); i++) {
            String typeName = typeList.get(i);
            List<String> modelList = typeModelMap.get(typeName);
            //if (CollectionUtils.isNotEmpty(modelList)) {
            if (null != modelList) {
                for (int j = 0; j < modelList.size(); j++) {
                    HSSFRow row = tmSheet.getRow(j + 1) == null ? tmSheet.createRow(j + 1)
                            : tmSheet.getRow(j + 1);
                    HSSFCell cell = row.getCell(i) == null ? row.createCell(i) : row.getCell(i);
                    cell.setCellValue(modelList.get(j));
                }
            }
            initModelNameMapping(workbook, tmSheet.getSheetName(), typeName, i, modelList.size());
        }
    }


    private static void initModelNameMapping(HSSFWorkbook workbook, String tmSheetName,
                                             String typeName, int referCol, int modelCount) {
        Name name = workbook.createName();
        name.setNameName(typeName);
        //modelCount + 1 = modelCount + 2 -1
        name.setRefersToFormula(tmSheetName + "!$" + getColumnName(referCol) +
                "$2:$" + getColumnName(referCol) + "$" + (modelCount + 1));
    }

    /**
     * 3.设置类型级联下拉框
     *
     * @param workbook
     * @param tmSheetName
     * @param typeCount
     */
    private static void initTypeNameMapping(HSSFWorkbook workbook, String tmSheetName, int typeCount) {
        Name name = workbook.createName();
        name.setNameName(MODEL_TYPE_SHEET_NAME);
        name.setRefersToFormula(tmSheetName + "!$A$1:$" + getColumnName(typeCount - 1) + "$1");
    }

    /**
     * 转换为A,B,C,D的列
     * @param index
     * @return
     */
    public static String getColumnName(int index) {
        StringBuilder s = new StringBuilder();
        while (index >= 26) {
            s.insert(0, (char) ('A' + index % 26));
            index = index / 26 - 1;
        }
        s.insert(0, (char) ('A' + index));
        return s.toString();
    }

    /**
     * 在主sheet里面设置
     *
     * @param sheet
     */
    private static void initSheetNameMapping(HSSFSheet sheet) {
        DataValidation typeValidation = getDataValidationByFormula(MODEL_TYPE_SHEET_NAME, 0);

        DataValidation shelfValidation = getDataValidationByFormula("INDIRECT($A1)", 1);
        DataValidation priceValidation = getDataValidationByFormula(PRICE_SHEET_NAME, 3);
        // 主sheet添加验证数据
        sheet.addValidationData(typeValidation);
        sheet.addValidationData(shelfValidation);
        sheet.addValidationData(priceValidation);
    }

    public static DataValidation getDataValidationByFormula(String formulaString, int columnIndex) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(1, XLS_MAX_ROW, columnIndex, columnIndex);
        // 数据有效性对象
        DataValidation dataValidationList = new HSSFDataValidation(regions, constraint);
        dataValidationList.createErrorBox("Error", "请选择或输入有效的选项，或下载最新模版重试！");
        String promptText = initPromptText(columnIndex);
        dataValidationList.createPromptBox("", promptText);
        return dataValidationList;
    }

    private static DataValidation getDecimalValidation(int firstRow, int lastRow, int columnIndex) {
        // 创建一个规则：>0的整数
        DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER, DVConstraint.OperatorType.GREATER_OR_EQUAL, "0", "0");
        // 设定在哪个单元格生效
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, columnIndex, columnIndex);
        // 创建规则对象
        HSSFDataValidation decimalVal = new HSSFDataValidation(regions, constraint);
        decimalVal.createPromptBox("", initPromptText(columnIndex));
        decimalVal.createErrorBox("输入值类型或大小有误！", "数值型，请输入大于0 的整数。");
        return decimalVal;
    }

    private static String initPromptText(int columnIndex) {
        String promptText = "";
        switch (columnIndex) {
            case 1:
                promptText = "请下拉选择或输入有效项！且先选择类型！";
                break;
            case 3:
                promptText = "请输入大于0的整数！";
                break;
        }
        return promptText;
    }

    /**
     * 在主sheet中校验数量
     * @param mainSheet
     */
    private static void initCount(HSSFSheet mainSheet) {
        DataValidation quantityValidation = getDecimalValidation(1, XLS_MAX_ROW, 2);
        mainSheet.addValidationData(quantityValidation);
    }

}