POI excel添加数据有效性验证以及解决DataValidation超长的问题

背景

业务功能的需要，要求要去生产一个带有数据校验的导入模板。

了解工具

XSSFDataValidationConstraint（XSSF数据有效性验证约束）：创建约束的时候，可以指定数据类型，以及数据列。

例如：

String strFormula = hiddenSheetName + "!$"+colStr+"$1:$"+colStr+"$65535";
            XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula);

CellRangeAddressList（单元格地址范围）：指定某列某行。

例如：(自定义的列表，所有的行65535)

 CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, column, column);

DataValidationHelper(数据有效对象)，指定工作表中的数据有效对象

DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheet);

DataValidation（数据验证），添加有效性数据

 操作

@RequestMapping(params = "action=exportProductTemplate")
    public void exportProductTemplate(HttpServletRequest request, HttpServletResponse response) {
        response.reset();
        response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件

        try {
            Long userId = SecurityUtil.getCurrentUserId();
            if(userId == null){
                throw new Exception("用户未登录！");
            }

            ServletContext servletContext=RequestContextUtils.findWebApplicationContext(request).getServletContext();
            String path=servletContext.getRealPath("/template");
            path=path+"/"+"product.xlsx";
            File f=new File(path);
            FileInputStream in=new FileInputStream(f);
            Workbook wb = WorkbookFactory.create(in);
            Sheet sheet = wb.getSheet("Sheet1");

            generateRangeList((XSSFSheet)sheet,BoothInfo.class.getSimpleName(),1,wb,"B");
            generateRangeList((XSSFSheet)sheet,BolongtoTypeEnum.supplier.name(),3, wb, "D");//供应商
            generateRangeList((XSSFSheet)sheet,BolongtoTypeEnum.client.name(),4, wb, "E");//委托方
            generateRangeList((XSSFSheet)sheet,BusinessProduct.class.getSimpleName(),5, wb, "F");//产品名称
            generateRangeList((XSSFSheet)sheet,"Country",14, wb, "O");//原产国

            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            String fileName = URLEncoder.encode("批量导入商品入库模板", "UTF-8")+sdf.format(new Date());
            // 将文件输出
            response.setHeader("Content-disposition",
                    "attachment; filename="+fileName+".xlsx");

            OutputStream ouputStream = response.getOutputStream();
            wb.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        } catch (Exception e) {
            System.out.println("导出Excel失败！");
            e.printStackTrace();
        }
    }
    
    /**
     * 构造有效性数据约束
     */
    private void generateRangeList(XSSFSheet sheet, String type, int column, Workbook wb, String colStr) {
        String[] options = getOptions(type);
        if (options.length == 0){
            return;
        }
        if(String.join(",",options).length()>255){
            //获取所有sheet页个数
            int sheetTotal = wb.getNumberOfSheets();
            String hiddenSheetName = "hiddenSheet" + sheetTotal;
            XSSFSheet hiddenSheet = (XSSFSheet)wb.createSheet(hiddenSheetName);
            Row row;
            //写入下拉数据到新的sheet页中
            for (int i = 0; i < options.length; i++) {
                row = hiddenSheet.createRow(i);
                Cell cell = row.createCell(column);
                cell.setCellValue(options[i]);
            }
            //获取新sheet页内容
            String strFormula = hiddenSheetName + "!$"+colStr+"$1:$"+colStr+"$65535";
            XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula);
            // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
            CellRangeAddressList regions = new CellRangeAddressList(1,65535, column, column);
            // 数据有效性对象
            DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheet);
            DataValidation validation = help.createValidation(constraint, regions);
            sheet.addValidationData(validation);
            //将新建的sheet页隐藏掉
            wb.setSheetHidden(sheetTotal, true);
        }else{
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(options);
            CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, column, column);
            XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
            sheet.addValidationData(validation);
        }
    }

    /**
     * 构造有效性数据
     */
    private String[] getOptions(String type) {
        List<String> options=new ArrayList<String>();
        int size=0;
        Long userId = SecurityUtil.getCurrentUserId();
        if(BoothInfo.class.getSimpleName().equals(type)){
            options = exportProductTemplateApplicationService.findBoothInfoByUserId(userId);
        }else if(BolongtoTypeEnum.supplier.name().equals(type)){
            options = exportProductTemplateApplicationService.findBusinessmanContactByUserIdAndType(userId,BolongtoTypeEnum.supplier.name());
        }else if(BolongtoTypeEnum.client.name().equals(type)){
            options = exportProductTemplateApplicationService.findBusinessmanContactByUserIdAndType(userId,BolongtoTypeEnum.client.name());
        }else if(BusinessProduct.class.getSimpleName().equals(type)){
            options = exportProductTemplateApplicationService.findBusinessProductByUserId(userId);
        }else if("Country".equals(type)){
            options = exportProductTemplateApplicationService.findProductCountry(type);
        }
        size = CollectionUtils.isEmpty(options)?0:options.size();
        return options.toArray(new String[size]);
    }

 解决DataValidation超长问题（超出255）

DataValidation超出255，会到导致该列有效性数据验证无效，这题提供的一个想法就是，新创建一个sheel，把对应的数据放到新的工作表中，在使用XSSFDataValidationConstraint数据有效性约束，就可以解决，上面代码有对应操作。