POI实现Excel省市区三级联动java实现-Toy模板网

这篇具有很好参考价值的文章主要介绍了POI实现Excel省市区三级联动java实现。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

2003版Excel

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

import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
/**
 * @ClassName: test3
 * @Author: yongtao.ding on 2019/5/29 9:48
 * @Version: 1.0
 */
public class test3 {

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

    public static void Cascade() {
        // 创建一个excel
        @SuppressWarnings("resource")
        Workbook book = new HSSFWorkbook();

        // 创建需要用户填写的sheet
        HSSFSheet sheetPro = (HSSFSheet) book.createSheet("省市县");
        Row row0 = sheetPro.createRow(0);
        row0.createCell(0).setCellValue("省");
        row0.createCell(1).setCellValue("市");
        row0.createCell(2).setCellValue("区");


        // 查询所有的省名称
        List<String> provinceArr = new ArrayList<String>();
        provinceArr.add("安徽省");
        provinceArr.add("浙江省");

        // 整理数据，放入一个Map中，mapkey存放父地点，value 存放该地点下的子区域
        Map<String, List<String>> areaMap = new HashMap<String, List<String>>();
        areaMap.put("浙江省", Lists.newArrayList("杭州市", "宁波市"));
        areaMap.put("安徽省", Lists.newArrayList("芜湖市", "滁州市"));
        areaMap.put("芜湖市", Lists.newArrayList("戈江区", "三山区"));
        areaMap.put("滁州市", Lists.newArrayList("来安县", "凤阳县"));

        // 创建一个专门用来存放地区信息的隐藏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.size(); i++) {
            Cell provinceCell = provinceRow.createCell(i + 1);
            provinceCell.setCellValue(provinceArr.get(i));
        }
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        Iterator<String> keyIterator = areaMap.keySet().iterator();
        while (keyIterator.hasNext()) {
            String key = keyIterator.next();
            List<String> son = areaMap.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();
            // key不可重复
            name.setNameName(key);
            String formula = "area!" + range;
            name.setRefersToFormula(formula);
        }

        // 省规则
        DVConstraint provConstraint = DVConstraint.createExplicitListConstraint(provinceArr.toArray(new String[] {}));
        CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 20, 0, 0);
        DataValidation provinceDataValidation = new HSSFDataValidation(provRangeAddressList, provConstraint);
        provinceDataValidation.createErrorBox("error", "请选择正确的省份");
        sheetPro.addValidationData(provinceDataValidation);

        // 对前20行设置有效性
        for (int i = 2; i < 20; i++) {
            setDataValidation("A", sheetPro, i, 2);
            setDataValidation("B", sheetPro, i, 3);
        }
        FileOutputStream os = null;
        try {
            os = new FileOutputStream("D:/testbbb8888888888.xls");
            book.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(os);
        }
    }


    /**
     * 设置有效性
     * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
     * @param sheet
     * @param rowNum 行数
     * @param colNum 列数
     */
    public static void setDataValidation(String offset, HSSFSheet sheet, int rowNum, int colNum) {
        DVConstraint formula = DVConstraint.createFormulaListConstraint("INDIRECT($" + offset + "$" + rowNum + ")");
        int firstRow = rowNum - 1;
        int lastRow = rowNum - 1;
        int firstCol = colNum - 1;
        int lastCol = colNum - 1;
        CellRangeAddressList rangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidation cacse = new HSSFDataValidation(rangeAddressList, formula);
        cacse.createErrorBox("error", "请选择正确的市");
        cacse.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
        sheet.addValidationData(cacse);
    }

    /**
     *  计算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;
        }
    }

}

2007版Excel

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

import com.google.common.collect.Lists;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
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;

/**
 * @ClassName: test
 * @Author: yongtao.ding on 2019/5/29 9:48
 * @Version: 1.0
 */
public class test {

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

    public static void Cascade() {
        // 创建一个excel
        @SuppressWarnings("resource")
        Workbook book = new XSSFWorkbook();

        // 创建需要用户填写的sheet
        XSSFSheet sheetPro = (XSSFSheet) book.createSheet("省市县");
        Row row0 = sheetPro.createRow(0);
        row0.createCell(0).setCellValue("省");
        row0.createCell(1).setCellValue("市");
        row0.createCell(2).setCellValue("区");

        // 查询所有的省名称
        List<String> provinceArr = new ArrayList<String>();
        provinceArr.add("安徽省");
        provinceArr.add("浙江省");

        // 整理数据，放入一个Map中，mapkey存放父地点，value 存放该地点下的子区域
        Map<String, List<String>> areaMap = new HashMap<String, List<String>>();
        areaMap.put("浙江省", Lists.newArrayList("杭州市", "宁波市"));
        areaMap.put("安徽省", Lists.newArrayList("芜湖市", "滁州市"));
        areaMap.put("芜湖市", Lists.newArrayList("戈江区", "三山区"));
        areaMap.put("滁州市", Lists.newArrayList("来安县", "凤阳县"));


        // 创建一个专门用来存放地区信息的隐藏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.size(); i++) {
            Cell provinceCell = provinceRow.createCell(i + 1);
            provinceCell.setCellValue(provinceArr.get(i));
        }
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        Iterator<String> keyIterator = areaMap.keySet().iterator();
        while (keyIterator.hasNext()) {
            String key = keyIterator.next();
            List<String> son = areaMap.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();
            // key不可重复
            name.setNameName(key);
            String formula = "area!" + range;
            name.setRefersToFormula(formula);
        }

        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheetPro);
        // 省规则
        DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(provinceArr.toArray(new String[] {}));
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 20, 0, 0);
        DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
        // 验证
        provinceDataValidation.createErrorBox("error", "请选择正确的省份");
        provinceDataValidation.setShowErrorBox(true);
        provinceDataValidation.setSuppressDropDownArrow(true);
        sheetPro.addValidationData(provinceDataValidation);

        // 对前20行设置有效性
        for (int i = 2; i < 20; i++) {
            setDataValidation("A", sheetPro, i, 2);
            setDataValidation("B", sheetPro, i, 3);
        }
        FileOutputStream os = null;
        try {
            os = new FileOutputStream("D:/testbbb.xlsx");
            book.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(os);
        }
    }


    /**
     * 设置有效性
     * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
     * @param sheet
     * @param rowNum 行数
     * @param colNum 列数
     */
    public static void setDataValidation(String offset, XSSFSheet sheet, int rowNum, int colNum) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        DataValidation data_validation_list;
        data_validation_list = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum, dvHelper);
        sheet.addValidationData(data_validation_list);
    }

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

    /**
     *  计算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;
        }
    }

}

时间校验2003版

package com.dbappsecurity.controller;

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.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @ClassName: test2
 * @Author: yongtao.ding on 2019/5/29 10:15
 * @Version: 1.0
 */
public class test2 {

    public static void testConstraintExcel2003() throws IOException {
        Workbook book = new HSSFWorkbook();
        Sheet sheet = book.createSheet("第一张工作表");

        // 只能输入时间，范围在2000年到2020年
        // 规则
        DVConstraint dateConstraint =
                DVConstraint.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "1900-01-01", "2100-01-01", "yyyy-Mm-dd");
        // 范围
        CellRangeAddressList rangeAddressList = new CellRangeAddressList(0, 10, 0, 0);
        // 范围 + 规则
        DataValidation dataValidation = new HSSFDataValidation(rangeAddressList, dateConstraint);
        dataValidation.createErrorBox("error", "输入的格式不正确");

        // 应用时间校验规则
        sheet.addValidationData(dataValidation);

        // 下拉式校验
        String[] provList = new String[] {"安徽省", "浙江省", "山西省"};
        DVConstraint provConstraint = DVConstraint.createExplicitListConstraint(provList);
        CellRangeAddressList provRangeAddressList = new CellRangeAddressList(0, 10, 1, 1);
        DataValidation provinceDataValidation = new HSSFDataValidation(provRangeAddressList, provConstraint);
        provinceDataValidation.createErrorBox("error", "请选择正确的省份");
        sheet.addValidationData(provinceDataValidation);

        FileOutputStream os = null;
        try {
            os = new FileOutputStream("D:/testConstraintExcel2003.xls");

            book.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            os.close();
        }
    }

    public static void main(String[] args) throws IOException {
        testConstraintExcel2003();
    }
}

数据校验2007版

@Test
// 单个单元格校验
public void testConstraintExcel2007() {
	Workbook book = new XSSFWorkbook();
	Sheet sheet = book.createSheet("第一张工作表");
	
	XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
	
	// 这里校验值准入输入0-100之间整数
	// 规则
	DataValidationConstraint dvConstraint = dvHelper.createIntegerConstraint(OperatorType.BETWEEN, "0", "100");
	
	// 范围
	CellRangeAddressList rangeAddressList = new CellRangeAddressList(0, 10, 0, 0);
	// 范围 + 规则
	DataValidation validation = dvHelper.createValidation(dvConstraint, rangeAddressList);
	validation.createErrorBox("error", "你必须输入一个整数");
	validation.setShowErrorBox(true);
	validation.setSuppressDropDownArrow(true);
	// 应用时间校验规则
	sheet.addValidationData(validation);
	
	// 下拉式校验这里需要调用setShowErrorBox，否则可能填错数据没有弹窗告警
	String[] provList = new String[] {"安徽省", "浙江省", "山西省"};
	DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(provList);
	CellRangeAddressList provRangeAddressList = new CellRangeAddressList(0, 10, 1, 1);
	DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
	provinceDataValidation.createErrorBox("error", "请选择正确的省份");
	provinceDataValidation.setShowErrorBox(true);
	provinceDataValidation.setSuppressDropDownArrow(true);
	sheet.addValidationData(provinceDataValidation);
	
	FileOutputStream os = null;
	try {
		os = new FileOutputStream("D:/testConstraintExcel2007.xlsx");
		
		book.write(os);
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		IOUtils.closeQuietly(os);
	}
}

文章来源地址https://www.toymoban.com/news/detail-741059.html

到了这里，关于POI实现Excel省市区三级联动java实现的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！