【工具使用】【POI】Excel 模板实现省市区联动

1 前言

最近项目也在收尾，考虑一些基础数据的初始化，其中界面的 Excel 模板导入算是其中一种，当然这种的使用前提是少量数据哈。那导入的模板 Excel 涉及到一个省市区的联动，贴一下实现思路。

2 实现

省市区信息的实体信息：

/**
 * @author kuku
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class StandardAddressDto implements Serializable {
    private static final long serialVersionUID = 1L;

    private Long id;
    private String name;
    private Long parentId;
    private List<StandardAddressDto> children;
}

生成省市区 Excel:

import com.alibaba.fastjson.JSON;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.CollectionUtils;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author kuku
 */
public class ExportTest {

    public static void main(String[] args) {
        buildExcel();
    }

    /**
     * 构建具有联动下拉框的Excel
     */
    public static void buildExcel() {
        // 创建一个excel
        Workbook book = new XSSFWorkbook();
        // 创建需要用户填写的sheet，并写入表头
        XSSFSheet sheetPro = (XSSFSheet) book.createSheet("省市区");
        // 冻结第一行
        sheetPro.createFreezePane(0, 1, 0, 1);
        // 初始化省市区信息的sheet
        buildAreaSheet(book);

        Row row0 = sheetPro.createRow(0);
        row0.createCell(0).setCellValue("省");
        row0.createCell(1).setCellValue("市");
        row0.createCell(2).setCellValue("区");

        // 设置第一列省份的下拉框校验
        int lastRow = 100;
        setDataValidation(sheetPro, 1, lastRow, 0, 0);

        // 设置第二列和第三列的有效性
        for (int i = 2; i < lastRow; i++) {
            setDataValidation(CellReference.convertNumToColString(0), sheetPro, i - 1, i - 1, 1, 1);
            setDataValidation(CellReference.convertNumToColString(1), sheetPro, i - 1, i - 1, 2, 2);
        }

        // 写入Excel文件
        try (FileOutputStream fos = new FileOutputStream("D:/省市区.xlsx")) {
            fos.flush();
            book.write(fos);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 构建地区sheet页，用于下拉框展示的数据源
     *
     * @param book 工作簿
     */
    public static void buildAreaSheet(Workbook book) {
        List<StandardAddressDto> list = searchAddress();
        // 得到第一级省名称，放在列表里
        String[] provinceArr = getProvinces(list);
        // 依次列出各省的市、各市的区
        Map<String, String[]> areaMap = getAreaMap(list);
        // 将有子区域的父区域放到一个数组中
        String[] areaFatherNameArr = new ArrayList<>(areaMap.keySet()).toArray(new String[0]);

        // 创建一个专门用来存放地区信息的隐藏sheet页
        // 因此也不能在现实页之前创建，否则无法隐藏。
        Sheet hideSheet = book.createSheet("area");
        // 这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
        book.setSheetHidden(book.getSheetIndex(hideSheet), true);

        int rowId = 0;
        // 设置第一行，存省的信息
        Row provinceRow = hideSheet.createRow(rowId++);
        provinceRow.createCell(0).setCellValue("省列表");
        for (int i = 0; i < provinceArr.length; i++) {
            Cell provinceCell = provinceRow.createCell(i + 1);
            provinceCell.setCellValue(provinceArr[i]);
        }

        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        for (String key : areaFatherNameArr) {
            String[] son = areaMap.get(key);
            Row row = hideSheet.createRow(rowId++);
            row.createCell(0).setCellValue(key);
            for (int j = 0; j < son.length; j++) {
                Cell cell = row.createCell(j + 1);
                cell.setCellValue(son[j]);
            }

            // 添加名称管理器
            String range = getRange(1, rowId, son.length);
            Name name = book.createName();
            // key不可重复
            name.setNameName(key);
            String formula = "area!" + range;
            name.setRefersToFormula(formula);
        }

    }

    /**
     * 设置第一列省份的有效性
     *
     * @param sheetPro sheet页
     * @param firstRow 第一行
     * @param lastRow  最后一行
     * @param firstCol 第一列
     * @param lastCol  最后一列
     */
    public static void setDataValidation(XSSFSheet sheetPro, int firstRow, int lastRow, int firstCol, int lastCol) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheetPro);
        String formula = getFormula(1, 1, 1, 34);
        // 省规则
        DataValidationConstraint provConstraint = dvHelper.createFormulaListConstraint(formula);
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList provRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
        // 验证
        provinceDataValidation.createErrorBox("error", "请选择正确的省份");
        provinceDataValidation.setShowErrorBox(true);
        provinceDataValidation.setSuppressDropDownArrow(true);
        sheetPro.addValidationData(provinceDataValidation);
    }

    /**
     * 设置有效性
     *
     * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
     * @param sheet  sheet页
     */
    public static void setDataValidation(String offset, XSSFSheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        String formulaString = String.format("INDIRECT($%s%s)", offset, firstRow + 1);
        DataValidation dataValidation = getDataValidationByFormula(formulaString, dvHelper, firstRow, lastRow, firstCol, lastCol);
        sheet.addValidationData(dataValidation);
    }

    /**
     * 加载下拉列表内容
     *
     * @param formulaString 表达式
     * @param dvHelper      XSSFDataValidationHelper
     * @return 返回值
     */
    private static DataValidation getDataValidationByFormula(String formulaString, XSSFDataValidationHelper dvHelper,
                                                             int firstRow, int lastRow, int firstCol, int lastCol) {
        // 加载下拉列表内容
        // 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
        // 如果A2是江苏省，那么此处就是江苏省下的市信息。
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 绑定 数据有效性对象
        XSSFDataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
        dataValidation.setEmptyCellAllowed(true);
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
        // 设置输入错误提示信息
        dataValidation.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
        return dataValidation;
    }

    /**
     * 计算formula
     *
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    public static String getRange(int offset, int rowId, int colCount) {
        String columnLetter1 = CellReference.convertNumToColString(offset);
        String columnLetter2 = CellReference.convertNumToColString(offset + colCount - 1);
        return String.format("$%s$%s:$%s$%s", columnLetter1, rowId, columnLetter2, rowId);
    }

    /**
     * 构建省份下拉框的表达式
     *
     * @param firstRow 第一行
     * @param lastRow  最后一行
     * @param firstCol 第一列
     * @param lastCol  最后一列
     * @return 返回值
     */
    public static String getFormula(int firstRow, int lastRow, int firstCol, int lastCol) {
        String ss = String.format("$%s$%s:$%s$%s", CellReference.convertNumToColString(firstCol), firstRow,
                CellReference.convertNumToColString(lastCol), lastRow);
        return String.format("=%s!%s", "area", ss);
    }

    /**
     * 获取四级地址
     *
     * @return 返回四级地址树形结构
     */
    public static List<StandardAddressDto> searchAddress() {
        return JSON.parseArray("[\n" +
                "    {\n" +
                "        \"id\":\"1\",\n" +
                "        \"name\":\"北京\",\n" +
                "        \"parentId\":4744,\n" +
                "        \"children\":[\n" +
                "            {\n" +
                "                \"id\":11,\n" +
                "                \"name\":\"海淀\",\n" +
                "                \"parentId\":1,\n" +
                "                \"children\":{\n" +
                "                    \"id\":\"111\",\n" +
                "                    \"name\":\"知春路\",\n" +
                "                    \"parentId\":11\n" +
                "                }\n" +
                "            }\n" +
                "        ]\n" +
                "    },\n" +
                "    {\n" +
                "        \"id\":\"2\",\n" +
                "        \"name\":\"山西省\",\n" +
                "        \"parentId\":4744,\n" +
                "        \"children\":[\n" +
                "            {\n" +
                "                \"id\":21,\n" +
                "                \"name\":\"临汾市\",\n" +
                "                \"parentId\":21,\n" +
                "                \"children\":{\n" +
                "                    \"id\":\"211\",\n" +
                "                    \"name\":\"尧都区\",\n" +
                "                    \"parentId\":21\n" +
                "                }\n" +
                "            }\n" +
                "        ]\n" +
                "    }\n" +
                "]\n", StandardAddressDto.class);
    }

    /**
     * 获取省份地址
     *
     * @param list 入参
     * @return 返回值
     */
    public static String[] getProvinces(List<StandardAddressDto> list) {
        return list.stream().map(StandardAddressDto::getName).toArray(String[]::new);
    }

    /**
     * 获取当前地址及其子地址
     * {
     * "北京市":["朝阳区","海淀区"],
     * "河北省":["保定市","石家庄市"]
     * }
     *
     * @param list 入参集合
     * @return 返回值
     */
    public static Map<String, String[]> getAreaMap(List<StandardAddressDto> list) {
        Map<String, String[]> map = new LinkedHashMap<>(48);
        for (StandardAddressDto standardAddressDto : list) {
            String name = standardAddressDto.getName();
            List<StandardAddressDto> children = standardAddressDto.getChildren();
            if (CollectionUtils.isEmpty(children)) {
                System.out.println("为空：" + name);
                continue;
            }
            String[] childrenNames = children.stream().map(StandardAddressDto::getName).toArray(String[]::new);
            map.put(name, childrenNames);
        }

        for (StandardAddressDto standardAddressDto : list) {
            String name = standardAddressDto.getName();
            List<StandardAddressDto> children = standardAddressDto.getChildren();
            if (CollectionUtils.isEmpty(children)) {
                System.out.println("为空：" + name);
                continue;
            }
            for (StandardAddressDto childrenAddress : children) {
                String name1 = childrenAddress.getName();
                List<StandardAddressDto> children1 = childrenAddress.getChildren();
                if (CollectionUtils.isEmpty(children1)) {
                    System.out.println("为空1：" + name1);
                    continue;
                }
                String[] childrenNames = children1.stream().map(StandardAddressDto::getName).toArray(String[]::new);
                map.put(name1, childrenNames);
            }
        }

        return map;
    }

}