使用poi给输出到excel中的数据的某一列加上下拉框

添加poi依赖
 <!-- excel工具 -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.1.2</version>
            </dependency>

 @ApiOperation(value = "导出Excel")
    @PostMapping(value = "/export", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
    public ResponseEntity<byte[]> exportToExcel() {
        List<Material> materialList = materialService.selectAll();
        try(Workbook workbook = new HSSFWorkbook()){

            //创建新表格Material
            Sheet sheet = workbook.createSheet("Material");
            //创建第一行标题行
            Row headerRow = sheet.createRow(0);

            //创建单元格并设置标题
            headerRow.createCell(0).setCellValue("科目");
            headerRow.createCell(1).setCellValue("名称");
            headerRow.createCell(2).setCellValue("计量单位");
            headerRow.createCell(3).setCellValue("计价方式（元/单位）");

            
            if (materialList.size()>0){

            //创建单元格并填充数据

                int rowNum = 1;
                for(Material material : materialList){
                    Row row = sheet.createRow(rowNum++);
                    row.createCell(0).setCellValue(material.getSubject());
                    row.createCell(1).setCellValue(material.getName());
                    row.createCell(2).setCellValue(material.getUnit());
                    BigDecimal unitPrice = material.getUnitPrice();
                    row.createCell(3).setCellValue(String.valueOf(unitPrice));
                }
            }else {
                    Row row = sheet.createRow(1);
                    row.createCell(0).setCellValue("");
                    row.createCell(1).setCellValue("");
                    row.createCell(2).setCellValue("");
                    row.createCell(3).setCellValue("");

            }

            //生成类实例，对表格进行验证，
            //例如为单元格设置数据验证规则或检索现有数据验证规则。
            DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();


            //设置下拉框生效的起始行，终止行，起始列，终止列
            CellRangeAddressList addressList = new CellRangeAddressList(
                    1, 10000, 0, 0);


            //设置下拉框的数据
            DataValidationConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
                    new String[]{"1.原材料", "2.专用材料", "3.低值易耗品","4.技术资料","5.专业燃料费","6.其他"});
            DataValidation validation = dataValidationHelper.createValidation(dvConstraint, addressList);

            //设置是否适配xls和xlsx
            if(validation instanceof HSSFDataValidation){
                validation.setSuppressDropDownArrow(false);
            }else{
                validation.setSuppressDropDownArrow(true);
                validation.setShowErrorBox(true);
            }

            sheet.addValidationData(validation);



            //调整每列的宽度以适应内容长度
            for(int i = 0; i < headerRow.getLastCellNum(); i++){
                sheet.autoSizeColumn(i);
            }
            //创建一个字节数组输出流，并将工作簿写入该流中
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            workbook.write(bos);
            //设置HTTP相应头信息，告知客服端以Excel文件的形式进行下载
            HttpHeaders headers = new HttpHeaders();
            headers.setContentType(MediaType.parseMediaType("application/vnd.ms-excel"));
            headers.setContentDisposition(ContentDisposition.builder("attachment")
                    .filename("material.xls", StandardCharsets.UTF_8)
                    .build());

            return ResponseEntity.ok()
                    .headers(headers)
                    .contentLength(bos.size())
                    .body(bos.toByteArray());
        }catch (IOException e) {
            e.printStackTrace();
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }
                
```![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/91e50995e0d4496f86d87542960a4e1f.png)