java导出excel带二级级联验证

最新推荐文章于 2023-10-31 09:00:14 发布
shun0304
最新推荐文章于 2023-10-31 09:00:14 发布
阅读量213
点赞数
文章标签： java excel 开发语言
本文链接：https://blog.csdn.net/shun0304/article/details/133750928
版权
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;
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;
public class ExcelDemo2 {
    public static void main(String[] args) {
        Cascade();
    }

    public static void Cascade() {
        String fileName = "test02.xlsx";
        // 创建一个excel
//        @SuppressWarnings("resource")
        XSSFWorkbook workbook = new XSSFWorkbook();
        //第一级首行
        int firstRow = 1;
        //第一级一共到多少列
        int lastRow = 49;
        //第一级所在列
        int firstCol = 0;
        //第一级所在列的列字符
        String offset = "A";
        int lastCol = firstCol;
        //第二级所在列
        int secondeCol = 3;
        //隐藏sheet页的名称
        String hideSheetName = "level";

        // 创建需要用户填写的sheet
        XSSFSheet sheet = workbook.createSheet("设备列表");
        Row row0 = sheet.createRow(0);
        //第一级
        row0.createCell(firstCol).setCellValue("设备品牌");
        //第二级
        row0.createCell(secondeCol).setCellValue("设备型号");
        //品牌列表
        String[] firstArr = {"葆德","许继","石家庄科林","北京易艾斯德"};
        //设备型号
        String[] baode = {"XWL-PD35A"};
        String[] xuji = {"XD930AB2","XD930AB4","MBU80"};
        String[] kelin = {"KLD8500"};
        String[] eaisd = {"EM600LCD"};

        Map<String,String[]> levelMap = new HashMap<>();
        levelMap.put("葆德",baode);
        levelMap.put("许继",xuji);
        levelMap.put("石家庄科林",kelin);
        levelMap.put("北京易艾斯德",eaisd);

        prepareDataSheet(workbook, hideSheetName, firstArr, levelMap);

        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        // 省规则
        DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(firstArr);
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList provRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
        //验证
        provinceDataValidation.createErrorBox("error", "请选择正确的品牌");
        provinceDataValidation.setShowErrorBox(true);
        provinceDataValidation.setSuppressDropDownArrow(true);
        sheet.addValidationData(provinceDataValidation);

        //对前20行设置有效性
        for(int i = firstRow;i <= lastRow;i++){
            setDataValidation(offset ,sheet,i,secondeCol);
        }
        FileOutputStream os = null;
        try {
            os = new FileOutputStream(fileName);
            workbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(os);
        }
    }

    private static void prepareDataSheet(XSSFWorkbook workbook, String hideSheetName, String[] firstArr, Map<String, String[]> levelMap) {
        //创建一个专门用来存放地区信息的隐藏sheet页
        //因此也不能在现实页之前创建，否则无法隐藏。
        Sheet hideSheet = workbook.createSheet(hideSheetName);
        //这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
        workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);


        //--设置引用的数据 start --//
        int rowId = 0;
        // 设置第一行，存省的信息
        Row brandRow = hideSheet.createRow(rowId++);
        brandRow.createCell(0).setCellValue("第一级");
        for(int i = 0; i < firstArr.length; i ++){
            Cell provinceCell = brandRow.createCell(i + 1);
            provinceCell.setCellValue(firstArr[i]);
        }
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        for(int i = 0; i < firstArr.length; i++){
            String key = firstArr[i];
            String[] secondArr = levelMap.get(key);
            Row row1 = hideSheet.createRow(rowId++);
            row1.createCell(0).setCellValue(key);
            for(int j = 0; j < secondArr.length; j ++){
                Cell cell0 = row1.createCell(j + 1);
                cell0.setCellValue(secondArr[j]);
            }

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


    /**
     * 设置有效性
     * @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;
        int offsetRow = rowNum+1;
        data_validation_list = getDataValidationByFormula(
                "INDIRECT($" + offset + (offsetRow) + ")", 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;
        int lastRow = naturalRowIndex;
        int firstCol = naturalColumnIndex;
        int lastCol = naturalColumnIndex;
        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;
        }
    }
}
生成的带下拉列表