使用EasyExcel导出模板并设置级联下拉及其原理分析_easypoi2.2.6导出模板时动态设置下拉数据

一、概述

项目中有时会遇到需要导出一个Excel模板，然后在导出的Excel中填充数据，最终再调用接口批量把Excel中的数据导入到数据库当中的需求。

其中级联下拉选择，手机号校验，性别校验等都是比较常见的校验。

这里就已上面三种情况，使用EasyExcel，结合POI来进行说明。

网上已经有一些使用POI完成上述功能的文章了。这里就以EasyExcel为主体，POI为辅来进行实现。

二、Maven坐标准备

<!--easyExcel相关坐标-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>
<!--poi相关坐标-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

三、模板导出具体实现

3.1 实现省市级联下拉

1）准备好级联写出处理器策略实现类

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.*;

public class CascadeWriteHandler implements SheetWriteHandler {

	private List<String> largeList;			// 大类的字符串集合
	Map<String, List<String>> siteMap;		// 大类和小类的对应关系的map集合

	public CascadeWriteHandler(List<String> largeList, Map<String, List<String>> siteMap) {
		this.largeList = largeList;
		this.siteMap = siteMap;
	}

	@Override
	public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

	}

	@Override
	public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
		//获取工作簿
		Sheet sheet = writeSheetHolder.getSheet();
		Workbook book = writeWorkbookHolder.getWorkbook();
		//创建一个专门用来存放地区信息的隐藏sheet页
		//因此不能在现实页之前创建，否则无法隐藏。
		Sheet hideSheet = book.createSheet("site");
		book.setSheetHidden(book.getSheetIndex(hideSheet), true);
		// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
		int rowId = 0;
		Row proviRow = hideSheet.createRow(rowId++);
		proviRow.createCell(0).setCellValue("大类列表");
		for (int i = 0; i < largeList.size(); i++) {
			Cell proviCell = proviRow.createCell(i + 1);
			proviCell.setCellValue(largeList.get(i));
		}
		Iterator<String> keyIterator = this.siteMap.keySet().iterator();
		while (keyIterator.hasNext()) {
			String key = keyIterator.next();
			List<String> son = siteMap.get(key);

			Row row = hideSheet.createRow(rowId++);
			row.createCell(0).setCellValue(key);
			for (int i = 0; i < son.size(); i++) {
				Cell cell = row.createCell(i + 1);
				cell.setCellValue(son.get(i));
			}
			// 添加名称管理器
			String range = getRange(1, rowId, son.size());
			Name name = book.createName();
			name.setNameName(key);
			String formula = "site!" + range;
			name.setRefersToFormula(formula);
		}

		///开始设置（大类小类）下拉框
		DataValidationHelper dvHelper = sheet.getDataValidationHelper();
		// 大类规则
		DataValidationConstraint expConstraint = dvHelper.createExplicitListConstraint(largeList.toArray(new String[]{}));
		CellRangeAddressList expRangeAddressList = new CellRangeAddressList(1, 999, 0, 0);
		setValidation(sheet, dvHelper, expConstraint, expRangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");

		// 小类规则(各单元格按个设置)
		// "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，如果A2是浙江省，那么此处就是浙江省下面的市
		// 为了让每个单元格的公式能动态适应，使用循环挨个给公式。
		// 循环几次，就有几个单元格生效，次数要和上面的大类影响行数一一对应，要不然最后几个没对上的单元格实现不了级联
		for (int i = 2; i < 1000; i++) {
			CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, 1, 1);
			DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($A$" + i + ")");
			setValidation(sheet, dvHelper, formula, rangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");
		}
	}

	/**
	 * 设置验证规则
	 * @param sheet			sheet对象
	 * @param helper		验证助手
	 * @param constraint	createExplicitListConstraint
	 * @param addressList	验证位置对象
	 * @param msgHead		错误提示头
	 * @param msgContext	错误提示内容
	 */
	private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList, String msgHead, String msgContext) {
		DataValidation dataValidation = helper.createValidation(constraint, addressList);
		dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
		dataValidation.setShowErrorBox(true);
		dataValidation.setSuppressDropDownArrow(true);
		dataValidation.createErrorBox(msgHead, msgContext);
		sheet.addValidationData(dataValidation);
	}

	/**
	 * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
	 * @param rowId    第几行
	 * @param colCount 一共多少列
	 * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
	 * @author denggonghai 2016年8月31日 下午5:17:49
	 */
	public 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;
		}
	}
}
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

2）准备好导出实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class CascadeVO {

    @ExcelProperty("省")
    private String largeType ;

    @ExcelProperty("市")
    private String smallType ;
}

1
2
3
4
5
6
7
8
9
10
11
12

3）编写Controller接口导出模板

@RestController
@RequestMapping("/excel")
public class ExcelController {
    
    @SneakyThrows
    @GetMapping("/downloadCascade")
    public void downloadCascade(HttpServletResponse response){
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("导出模板-级联下拉框", "UTF-8").replaceAll("\\+", "%20");
        // 设置文件名称
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        List<CascadeVO> dataList = new ArrayList<>();

        // 准备要写出的数据（这里可以从数据库中查询出来后再进行）
        // 查询所有的省名称
        List<String> provNameList = new ArrayList<String>();
        provNameList.add("浙江省");
        provNameList.add("广东省");

        // 整理数据，放入一个Map中，mapkey存放父地点，value 存放该地点下的子区域
        Map<String, List<String>> siteMap = new HashMap<String, List<String>>();
        siteMap.put("浙江省", CollUtil.newArrayList("杭州市", "金华市", "宁波市"));
        siteMap.put("广东省", CollUtil.newArrayList("广州市", "深圳市", "韶光市"));

        // 写出数据
        EasyExcel.write(response.getOutputStream(), CascadeVO.class)
                .sheet("sheet1")
                .registerWriteHandler(new CascadeWriteHandler(provNameList, siteMap))
                .doWrite(dataList);
    }
}
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

4）导出示例

3.2 实现性别校验

下拉框都类似，创建的是一种显示列表约束：createExplicitListConstraint

1）准备下拉框处理器策略实现类

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DropDownWriteHandler implements SheetWriteHandler {

    List<String> dropDown;                  // 下拉框显示的数值

    public DropDownWriteHandler(List<String> dropDown) {
        this.dropDown = dropDown;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 开始设置 男/女下拉框
        // 定义一个map key是需要添加下拉框的列的index value是下拉框数据
        Map<Integer, String[]> mapDropDown = new HashMap<>(3);
        //性别下拉选项
        String[] downArray = dropDown.toArray(new String[dropDown.size()]); // {"男", "女"};
        //下拉选在Excel中对应的列
        mapDropDown.put(2, downArray);
        // 获取Sheet表
        Sheet sheet = writeSheetHolder.getSheet();
        //设置下拉框
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
            // 起始行、终止行、起始列、终止列  起始行为1即表示表头不设置
            CellRangeAddressList addressList = new CellRangeAddressList(1, 999, entry.getKey(), entry.getKey());
            // 设置下拉框数据 (设置长度为0的数组会报错，所以这里需要判断)
            if (entry.getValue().length > 0) {
                //创建显式列表约束
                DataValidationConstraint constraint = dvHelper.createExplicitListConstraint(entry.getValue());
                // 指定行列约束以及错误信息
                DataValidation dataValidation = dvHelper.createValidation(constraint, addressList);
                dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                dataValidation.setShowErrorBox(true);
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.createErrorBox("提示", "你输入的值未在备选列表中，请下拉选择合适的值");
                sheet.addValidationData(dataValidation);
            }
        }
    }
}

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

2）准备好导出实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class DropDownVO {

    @ExcelProperty("性别（男/女）")
    private String gender ;
}

1
2
3
4
5
6
7
8
9

3）编写Controller接口导出模板

@RestController
@RequestMapping("/excel")
public class ExcelController {
	@SneakyThrows
    @GetMapping("downloadDropDown")
    public void downloadDropDown(HttpServletResponse response){
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("导出模板-普通下拉框", "UTF-8").replaceAll("\\+", "%20");
        // 设置文件名称
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        List<CascadeVO> dataList = new ArrayList<>();

        // 准备要写出的数据（这里可以从数据库中查询出来后再进行）
        List<String> dropDown = CollUtil.newArrayList("男", "女");

        // 写出数据
        EasyExcel.write(response.getOutputStream(), CascadeVO.class)
                .sheet("sheet1")
                .registerWriteHandler(new DropDownWriteHandler(dropDown))
                .doWrite(dataList);
    }
}
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

3.3 实现手机号和数字校验

可以创建自定义约束：createExplicitListConstraint

也可以创建普通的数值类约束来进行简单的单元格约束：createNumericConstraint

1）准备自定义处理器策略实现类

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;

/**
 * 号码下拉框处理策略
 */
public class NumberWriteHandler  implements SheetWriteHandler {

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 获取Sheet表
        Sheet sheet = writeSheetHolder.getSheet();
        // 验证助手
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        //设置电话号码校验规则
        DataValidationConstraint CustomConstraint = dvHelper.createCustomConstraint("AND(LEFT(D2,1)=\"1\",MID(D2,2,1)+0>=3,MID(D2,2,1)+0<=9,LEN(D2)=11,ISNUMBER(D2+0))");
        // 指定行列约束以及错误信息
        setValidation(sheet, dvHelper, CustomConstraint, addressList, "提示", "请输入11位正确的手机号码");
        // 设置1-100范围数字校验规则
        DataValidationConstraint numericConstraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.INTEGER, DataValidationConstraint.OperatorType.BETWEEN, "1", "100");
        // 指定行列约束以及错误信息
        setValidation(sheet, dvHelper, numericConstraint, addressList, "提示", "请输入1-100之间的数字");
    }

    /**
     * 设置验证规则
     * @param sheet			sheet对象
     * @param helper		验证助手
     * @param constraint	createExplicitListConstraint
     * @param addressList	验证位置对象
     * @param msgHead		错误提示头
     * @param msgContext	错误提示内容
     */
    private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList, String msgHead, String msgContext) {
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        dataValidation.setShowErrorBox(true);
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.createErrorBox(msgHead, msgContext);
        sheet.addValidationData(dataValidation);
    }
}
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

2）准备好导出实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class PhoneVO {

    @ExcelProperty("手机号")
    private String phone ;
    
    @ExcelProperty("1-100之间的数字")
    private String number ;
}
1
2
3
4
5
6
7
8
9
10
11

3）编写Controller接口导出模板

@RestController
@RequestMapping("/excel")
public class ExcelController {
	@SneakyThrows
    @GetMapping("downloadDropDown")
    public void downloadDropDown(HttpServletResponse response){
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("导出模板-电话", "UTF-8").replaceAll("\\+", "%20");
        // 设置文件名称
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        // 写出数据
        List<PhoneVO> dataList = new ArrayList<>();
        EasyExcel.write(response.getOutputStream(), CascadeVO.class)
                .sheet("sheet1")
                .registerWriteHandler(new NumberWriteHandler())
                .doWrite(dataList);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3.4 整合三种策略导出模板

@RestController
@RequestMapping("/excel")
public class ExcelController {
	@SneakyThrows
    @GetMapping("downloadAll")
    public void downloadAll(HttpServletResponse response){
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("物业人员", "UTF-8").replaceAll("\\+", "%20");
        // 设置文件名称
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        List<CascadeVO> dataList = new ArrayList<>();

        // 准备要写出的数据
        // 1. 级联下拉数据
        List<String> provNameList = new ArrayList<String>();
        provNameList.add("浙江省");
        provNameList.add("广东省");
        // 整理数据，放入一个Map中，mapkey存放父地点，value 存放该地点下的子区域
        Map<String, List<String>> siteMap = new HashMap<String, List<String>>();
        siteMap.put("浙江省", CollUtil.newArrayList("杭州市", "金华市", "宁波市"));
        siteMap.put("广东省", CollUtil.newArrayList("广州市", "深圳市", "韶光市"));
        // 2.性别下拉框数据
        List<String> dropDown = CollUtil.newArrayList("男", "女");
        
        // 写出数据
        EasyExcel.write(response.getOutputStream(), CascadeVO.class)
                .sheet("sheet1")
                .registerWriteHandler(new CascadeWriteHandler(provNameList, siteMap))
                .registerWriteHandler(new DropDownWriteHandler(dropDown))
                .registerWriteHandler(new NumberWriteHandler())
                .doWrite(dataList);
    }
}
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

四、策略使用源码分析

4.1 DataValidationHelper

它一个接口，其中定义了各种验证接口。

public interface DataValidationHelper {
    // 创建公式列表约束规则
    DataValidationConstraint createFormulaListConstraint(String var1);
    // 创建显式列表约束规则
    DataValidationConstraint createExplicitListConstraint(String[] var1);
    // 创建数值约束规则
    DataValidationConstraint createNumericConstraint(int var1, int var2, String var3, String var4);
    // 创建文本长度约束规则
    DataValidationConstraint createTextLengthConstraint(int var1, String var2, String var3);
    // 创建十进制约束规则
    DataValidationConstraint createDecimalConstraint(int var1, String var2, String var3);
    // 创建整数制约束规则
    DataValidationConstraint createIntegerConstraint(int var1, String var2, String var3);
    // 创建日期制约束规则
    DataValidationConstraint createDateConstraint(int var1, String var2, String var3, String var4);
    // 创建时间约束规则
    DataValidationConstraint createTimeConstraint(int var1, String var2, String var3);
    // 创建自定义约束规则
    DataValidationConstraint createCustomConstraint(String var1);
    // 创建验证规则
    DataValidation createValidation(DataValidationConstraint var1, CellRangeAddressList var2);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

• 使用时可以先用sheet对象获取到验证助手，然后通过验证助手接口中定义的规则创建不同的验证策略。

 DataValidationHelper dvHelper = sheet.getDataValidationHelper();
1

• 创建好了验证策略以后再使用验证助手创建验证规则

DataValidationConstraint numericConstraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.INTEGER, DataValidationConstraint.OperatorType.BETWEEN, "1", "100");
1

• 创建好了验证策略以后再使用验证助手创建验证规则，并设置验证错误的提示

DataValidation dataValidation = helper.createValidation(constraint, addressList);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setShowErrorBox(true);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createErrorBox(msgHead, msgContext);
sheet.addValidationData(dataValidation);
1
2
3
4
5
6

4.2 DataValidationConstraint

在创建数值约束（createNumericConstraint）时，需要传递一些int类型数值。

而如何传递这些数值需要参考这个类：DataValidationConstraint。它也是一个接口，源码如下：

public interface DataValidationConstraint {
    int getValidationType();

    int getOperator();

    void setOperator(int var1);

    String[] getExplicitListValues();

    void setExplicitListValues(String[] var1);

    String getFormula1();

    void setFormula1(String var1);

    String getFormula2();

    void setFormula2(String var1);

    public static final class OperatorType {
        public static final int BETWEEN = 0;
        public static final int NOT_BETWEEN = 1;
        public static final int EQUAL = 2;
        public static final int NOT_EQUAL = 3;
        public static final int GREATER_THAN = 4;
        public static final int LESS_THAN = 5;
        public static final int GREATER_OR_EQUAL = 6;
        public static final int LESS_OR_EQUAL = 7;
        public static final int IGNORED = 0;

        private OperatorType() {
        }

        public static void validateSecondArg(int comparisonOperator, String paramValue) {
            switch(comparisonOperator) {
            case 0:
            case 1:
                if (paramValue == null) {
                    throw new IllegalArgumentException("expr2 must be supplied for 'between' comparisons");
                }
            default:
            }
        }
    }

    public static final class ValidationType {
        public static final int ANY = 0;
        public static final int INTEGER = 1;
        public static final int DECIMAL = 2;
        public static final int LIST = 3;
        public static final int DATE = 4;
        public static final int TIME = 5;
        public static final int TEXT_LENGTH = 6;
        public static final int FORMULA = 7;

        private ValidationType() {
        }
    }
}
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

这个接口中有两个内部类，OperatorType操作类型的内部类和ValidationType验证类型的内部类。

里面的成员变量的名称都很见名知意。可以根据策略的不同林火选择。

五、级联下拉原理分析-原生Excel级联

关于级联下拉是如何写出来的，其实是对直接使用wps或者office创建一对级联下拉框来进行实现的。并不是想象出来的。

所以先了解直接使用wps或者office创建一对级联下拉框是如何操作的，就知道代码的每一行是什么意思了。

5.1 准备省市源数据

在Excel中准备sheet1和sheet2两个工作表。然后在sheet2中准备省市源数据。

5.2 使用名称管理器创建省市关系

1）选择公式-名称管理器-点击新建

2）输入名称并选择引用位置

这里的Sheet2!$A$2:$A$4就代表这是工作表Sheet2下面的A2到A4单元格

3）确定并查看刚才的名称管理器内容

4）按照如上方法再添加广东的数据

至此的话名称管理器的数据就准备完成。

5.3 准备省市下拉框位置

在sheet1中先创建如下单元格数据。

5.4 创建数据有效性

1）打开有效性设置框

选中省下面的那个单元格，点击击菜单栏中的数据，找到有效性功能，再点击它。

2）设置有效性条件为序列并选择来源

设置完成后点击确定即可

3）效果如下

4）按相同的方法设置市下拉框

下拉框都创建完成了，但是此时还没有级联的效果，因为市区也就是二级下拉列表不能直接选择区域，而是需要用公式动态生成

5.5 使用公式完成级联下拉

公式=INDIRECT($G$2)的作用就是有效性数据会从名称管理器中获取key与单元格 A2 值相同的数据，如果A2是浙江省，那么此处就是浙江省下面的市。

5.6 最终效果

六、级联下拉原理分析-代码和实操对应关系

6.1 隐藏sheet

//创建一个专门用来存放地区信息的隐藏sheet页
//因此不能在现实页之前创建，否则无法隐藏。
Sheet hideSheet = book.createSheet("site");
book.setSheetHidden(book.getSheetIndex(hideSheet), true);
1
2
3
4

实操中我们的下拉框有效性数据以及名称管理器创建省市关系数据都是从sheet2中来。

但是实际我们导出的目标不需要有冗余的sheet2，所以在代码中把它隐藏起来，但是里面的数据也是存在的。

6.2 给隐藏sheet填充数据

for (int i = 0; i < largeList.size(); i++) {
    Cell proviCell = proviRow.createCell(i + 1);
    proviCell.setCellValue(largeList.get(i));
}
Iterator<String> keyIterator = siteMap.keySet().iterator();
while (keyIterator.hasNext()) {
    String key = keyIterator.next();
    List<String> son = siteMap.get(key);

    Row row = hideSheet.createRow(rowId++);
    row.createCell(0).setCellValue(key);
    for (int i = 0; i < son.size(); i++) {
        Cell cell = row.createCell(i + 1);
        cell.setCellValue(son.get(i));
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

这一步其实就是实现下面这一步：

6.3 添加名称管理器

// 添加名称管理器
String range = getRange(1, rowId, son.size());
Name name = book.createName();
name.setNameName(key);
String formula = "site!" + range;
name.setRefersToFormula(formula);
1
2
3
4
5
6

这一步其实就是实现下面这一步：

6.4 设置股数据有效性中的规则

///开始设置（大类小类）下拉框
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
// 大类规则
DataValidationConstraint expConstraint = dvHelper.createExplicitListConstraint(largeList.toArray(new String[]{}));
CellRangeAddressList expRangeAddressList = new CellRangeAddressList(1, 999, 0, 0);
setValidation(sheet, dvHelper, expConstraint, expRangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");

// 小类规则(各单元格按个设置)
for (int i = 2; i < 1000; i++) {
    CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, 1, 1);
    DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($A$" + i + ")");
    setValidation(sheet, dvHelper, formula, rangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");
}
1
2
3
4
5
6
7
8
9
10
11
12
13

这一步其实就是实现下面这两步：

七、小结

如果不懂基础的Excel是如何操作的话，想要再代码中实现导出这样的模板是非常困难的。

这也是我最近做的一个需求，从没有思路到慢慢探索的全过程。

希望能够给自己也给大家再解决Excel的问题上提供新的解决问题的思路吧。