apache poi 实现Excel 下拉联动

原文链接：Java poi实现Excel 下拉联动

Java 实现Excel 下拉联动，本示例中实现了省市区乡镇村联动。适用于03版本Excel。

依赖

        <dependency>
            <groupId>org.apache.poigroupId>
            <artifactId>poiartifactId>
            <version>3.17version>
        dependency>
        
        <dependency>
            <groupId>org.apache.poigroupId>
            <artifactId>poi-ooxmlartifactId>
            <version>3.17version>
        dependency>
        
        <dependency>
            <groupId>org.apache.poigroupId>
            <artifactId>ooxml-schemasartifactId>
            <version>1.1version>
        dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

ExcelTest

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

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
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 zhouchuangbin
 * @Date 2021/8/2
 * @Description
 */
public class ExcelTest {

    /**
     * 影响最大行数
     */
    private static final int XLS_MAX_ROW = 60000;

    /**
     * 导出模板
     *
     * @param provinceList
     * @param areaFatherNameList
     * @param areaMap
     * @throws IOException
     */
    public static void exportHSSFTemplate(List<String> provinceList, List<String> areaFatherNameList, Map<String, List<String>> areaMap) {

        String[] tileList = new String[]{"姓名", "手机号", "省", "市", "县", "乡镇", "村"};

        //创建工作簿对象
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet");
        sheet.setDefaultColumnWidth(18);
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = getStyle(wb);
        HSSFCell cell = null;
        int provinceIndex = 0;
        for (int i = 0; i < tileList.length; i++) {
            String title = tileList[i];
            if ("省".equals(title)) {
                provinceIndex = i;
            }
            cell = row.createCell(i);
            cell.setCellValue(title);
            cell.setCellStyle(style);

        }
        //创建隐藏目录
        createHideSheetHSSF(wb, provinceList, areaFatherNameList, areaMap);

        //如果省市区的excel位置不是如上tileList中的位置，需要变更则需要INDIRECT中所在的列名称
        // 省规则
        DVConstraint provConstraint = DVConstraint.createExplicitListConstraint(provinceList.toArray(new String[]{}));
        CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, XLS_MAX_ROW, provinceIndex, provinceIndex);
        DataValidation provinceDataValidation = new HSSFDataValidation(provRangeAddressList, provConstraint);
        provinceDataValidation.createErrorBox("error", "请选择正确的省份");
        sheet.addValidationData(provinceDataValidation);


        //市规则
        CellRangeAddressList cityRange = new CellRangeAddressList(1, XLS_MAX_ROW, provinceIndex + 1, provinceIndex + 1);
        DataValidation cityValidation = new HSSFDataValidation(cityRange, DVConstraint.createFormulaListConstraint("INDIRECT($C1)"));
        cityValidation.createErrorBox("error", "请选择正确的市");
        sheet.addValidationData(cityValidation);

        //区县规则
        CellRangeAddressList areaRange = new CellRangeAddressList(1, XLS_MAX_ROW, provinceIndex + 2, provinceIndex + 2);
        DataValidation areaValidation = new HSSFDataValidation(areaRange, DVConstraint.createFormulaListConstraint("INDIRECT($D1)"));
        areaValidation.createErrorBox("error", "请选择正确的县");
        sheet.addValidationData(areaValidation);

        //区县乡镇
        CellRangeAddressList townRange = new CellRangeAddressList(1, XLS_MAX_ROW, provinceIndex + 3, provinceIndex + 3);
        DataValidation townValidation = new HSSFDataValidation(townRange, DVConstraint.createFormulaListConstraint("INDIRECT($E1)"));
        townValidation.createErrorBox("error", "请选择正确的乡镇");
        sheet.addValidationData(townValidation);

        //村规则
        CellRangeAddressList villageRange = new CellRangeAddressList(1, XLS_MAX_ROW, provinceIndex + 4, provinceIndex + 4);
        DataValidation villageValidation = new HSSFDataValidation(villageRange, DVConstraint.createFormulaListConstraint("INDIRECT($F1)"));
        villageValidation.createErrorBox("error", "请选择正确的村");
        sheet.addValidationData(villageValidation);

        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream("d://excel_template.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 创建隐藏页
     *
     * @param wb
     * @param provinceArr
     * @param areaFatherNameArr
     * @param areaMap
     */
    public static void createHideSheetHSSF(HSSFWorkbook wb, List<String> provinceArr, List<String> areaFatherNameArr, Map<String, List<String>> areaMap) {

        //创建一个专门用来存放地区信息的隐藏sheet页
        HSSFSheet hideSheet = wb.createSheet("area");

        int rowId = 0;
        // 设置第1行，存省的信息
        Row provinceRow = hideSheet.createRow(rowId++);
        for (int i = 0; i < provinceArr.size(); i++) {
            Cell provinceCell = provinceRow.createCell(i);
            provinceCell.setCellValue(provinceArr.get(i));
        }
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        for (int i = 0; i < areaFatherNameArr.size(); i++) {
            String key = areaFatherNameArr.get(i);
            List<String> son = areaMap.get(key);
            Row prow = hideSheet.createRow(rowId++);
            prow.createCell(0).setCellValue(key);
            System.out.println(key);
            for (int j = 0; j < son.size(); j++) {
                Cell cell = prow.createCell(j + 1);
                cell.setCellValue(son.get(j));
            }
            // 添加名称管理器
            String range = getRange(1, rowId, son.size());
            Name name = wb.createName();
            //key不可重复
            name.setNameName(key);
            String formula = "area!" + range;
            name.setRefersToFormula(formula);
        }
        wb.setSheetHidden(1, true);

    }


    /**
     * 计算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) {
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
                if ((colCount - 25) % 26 == 0) {// 边界值
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }


    /**
     * 样式
     *
     * @param wb
     * @return
     */
    private static HSSFCellStyle getStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //cellStyle.setWrapText(true);//自动换行

        HSSFFont font = wb.createFont();
        font.setFontName("宋体");//设置字体名称
        font.setFontHeightInPoints((short) 12);//字体大小
        font.setBold(true);
        cellStyle.setFont(font);
        return cellStyle;
    }


    public static void main(String[] args) {
        //省数据
        List<String> provinceArr = Arrays.asList("广东省", "河北省");
        //所有区域父类（只到最后一层的上级即可）
        List<String> areaFatherNameArr = Arrays.asList("广东省", "河北省", "深圳市", "衡水市", "宝安区", "武强县", "西乡街道办事处", "街关镇");
        //父子类关系
        Map<String, List<String>> areaMap = new HashMap<>();
        areaMap.put("广东省", Arrays.asList("深圳市"));
        areaMap.put("河北省", Arrays.asList("衡水市"));
        areaMap.put("深圳市", Arrays.asList("宝安区"));
        areaMap.put("衡水市", Arrays.asList("武强县"));
        areaMap.put("宝安区", Arrays.asList("西乡街道办事处"));
        areaMap.put("武强县", Arrays.asList("街关镇"));
        areaMap.put("西乡街道办事处", Arrays.asList("西乡社区居委会", "固戍社区居委会", "南昌社区居委会", "永丰社区居委会"));
        areaMap.put("街关镇", Arrays.asList("五里屯村委会", "北关村委会", "西关村委会", "南关村委会"));

        exportHSSFTemplate(provinceArr, areaFatherNameArr, areaMap);

    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224