JAVA POI 三级联动

1.依赖

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>

2.代码

package com.example.demo.test;

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.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

public class PoiTest {

    private static List<String> provinces = new ArrayList<>();
    private static Map<String, List<String>> city = new HashMap<>();
    private static Map<String, List<String>> county = new HashMap<>();

    static {
        provinces.add("贵州省");
        provinces.add("四川省");
        provinces.add("广东省");

        city.put("贵州省", Arrays.asList("贵阳市", "遵义市"));
        city.put("四川省", Arrays.asList("成都市", "绵阳市"));
        city.put("广东省", Arrays.asList("广州市", "深圳市"));

        county.put("贵阳市", Arrays.asList("花溪区", "白云区"));
        county.put("遵义市", Arrays.asList("红花岗区", "汇川区"));
        county.put("成都市", Arrays.asList("锦江区", "青羊区"));
        county.put("绵阳市", Arrays.asList("涪城区", "游仙区"));
        county.put("广州市", Arrays.asList("荔湾区", "越秀区"));
        county.put("深圳市", Arrays.asList("罗湖区", "福田区", "宝安区", "光明区"));
    }

    public static void main(String[] args) throws IOException {
        File file = new File("G:/bak/v1/excel7.xlsx");
        Workbook workbook;
        boolean isXls = file.getName().endsWith(".xls");
        if(isXls){
            workbook = new HSSFWorkbook();
        }else{
            workbook = new XSSFWorkbook();
        }

        process(workbook, workbook.createSheet("test"), 0, 200, isXls);

        FileOutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);
        outputStream.close();

        System.out.println("===============done==============");
    }


    public static void process(Workbook workbook, Sheet targetSheet, int fromRow, int endRow, boolean isXls) {
        String hiddenSheetName = "base_data";
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
        workbook.setSheetHidden(1, true);

        //========省=========
        int rowIndex = 0;
        Row row = hiddenSheet.createRow(rowIndex++);
        for (int i = 0; i < provinces.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(provinces.get(i));
        }
        String columnLetter = columnIndexToLetter(provinces.size());
        String provincesName = "省名称管理器";
        //创建省名称管理器
        createName(workbook, provincesName, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, columnLetter, rowIndex));
        //创建省数据有效性
        createDataValidation(provincesName, fromRow, endRow, 0, isXls, targetSheet);

        //=========市==========
        for (String province : city.keySet()) {
            row = hiddenSheet.createRow(rowIndex++);
            List<String> data = city.get(province);
            for (int i = 0; i < data.size(); i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(data.get(i));
            }
            //创建市名称管理器
            columnLetter = columnIndexToLetter(data.size());
            createName(workbook, province, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, columnLetter, rowIndex));
        }
        //创建市数据有效性
        createDataValidation("INDIRECT($A1)", fromRow, endRow, 1, isXls, targetSheet);

        //=========区县==========
        for (String city : county.keySet()) {
            row = hiddenSheet.createRow(rowIndex++);
            List<String> data = county.get(city);
            for (int i = 0; i < data.size(); i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(data.get(i));
            }
            //创建区县名称管理器
            columnLetter = columnIndexToLetter(data.size());
            createName(workbook, city, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, columnLetter, rowIndex));
        }
        //创建区县数据有效性
        createDataValidation("INDIRECT($B1)", fromRow, endRow, 2, isXls, targetSheet);
    }

    /**
     * 创建数据有效性
     *
     * @param formula 公式
     * @param fromRow 起止行
     * @param endRow 结束行
     * @param columnIndex 所在列
     * @param isXls 是否是xls格式
     * @param sheet sheet
     */
    public static void createDataValidation(String formula, int fromRow, int endRow, int columnIndex, boolean isXls, Sheet sheet) {
        DataValidation dataValidation;
        CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, columnIndex, columnIndex);
        if(isXls){
            DVConstraint constraint = DVConstraint.createFormulaListConstraint(formula);
            dataValidation = new HSSFDataValidation(regions, constraint);
        }else{
            XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,formula);
            DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheet);
            dataValidation = help.createValidation(constraint, regions);
        }
        sheet.addValidationData(dataValidation);
    }

    /**
     * 创建名称管理器
     *
     * @param workbook workbook
     * @param nameName 管理器名称
     * @param formula 数据范围
     */
    public static void createName(Workbook workbook, String nameName, String formula) {
        Name name = workbook.createName();
        name.setNameName(nameName);
        name.setRefersToFormula(formula);
    }

    /**
     * excel列传字母
     *
     * @param columnIndex 列的位置
     * @return 列对应的字母
     */
    public static String columnIndexToLetter(int columnIndex) {
        if (columnIndex <= 0) {
            return null;
        }
        String columnStr = "";
        columnIndex--;
        do {
            if (columnStr.length() > 0) {
                columnIndex--;
            }
            columnStr = ((char) (columnIndex % 26 + (int) 'A')) + columnStr;
            columnIndex = (int) ((columnIndex - columnIndex % 26) / 26);
        } while (columnIndex > 0);
        return columnStr;
    }
}