poi 07版在已有模板中生成下拉框

poi 07版 导出excel 模板, 在已有模板上修改 指定单元格个生成下拉框

controller 层 提供访问接口, 并设置响应类型

    @GetMapping("downloadExcel")
    @ApiOperation("下载模板")
    public void downloadExcel(HttpServletResponse res, HttpServletRequest request) throws IOException, InvalidFormatException {
        OutputStream os = res.getOutputStream();
        String fileName = "招标合同管理信息模板.xlsx";
        try {
            Workbook workbook = zbHtglService.downloadExcel("zbHtgl.xlsx");
//            byte[] byteArray = StreamUtil.getByteArray("zbHtgl.xlsx");
            res.reset();
            if ("IE".equals(getBrowser(request))) {
                fileName = new String(java.net.URLEncoder.encode(fileName, "UTF-8"));
                res.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            } else {
                fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
                res.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            }
            res.setContentType("application/octet-stream; charset=utf-8");
//            os.write(byteArray);
            workbook.write(os);
//            os.flush();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

service 层进行excel 处理

    @Override
    public Workbook downloadExcel(String filename) throws Exception {
        // 字典
        List<SysDictDTO> isFbDic1 = sysDictService.selectByDictType("isFb");
//        List<SysDictDTO> zbxmlxDic1 = sysDictService.selectByDictType("zbxmlx");
        List<SysDictDTO> zbdqDic1 = sysDictService.selectByDictType("zbdq");
        List<SysDictDTO> isShDic1 = sysDictService.selectByDictType("isSh");
        List<SysDictDTO> zbhyDic1 = sysDictService.selectByDictType("zbhy");

        // 备用集合
        List<String> isFbList = new ArrayList<>();
//        List<String> zbxmlxList = new ArrayList<>();
//        List<String> zjfwlxList = new ArrayList<>();
        List<String> zbdqList = new ArrayList<>();
        List<String> isShList = new ArrayList<>();
        List<String> zbhyList = new ArrayList<>();
        for (SysDictDTO sysDictDTO : isFbDic1) {
            isFbList.add(sysDictDTO.getDictName());
        }
//        for (SysDictDTO sysDictDTO : zbxmlxDic1) {
//            zbxmlxList.add(sysDictDTO.getDictName());
//        }
//        for (SysDictDTO sysDictDTO : zjfwlxDic1) {
//            zjfwlxList.add(sysDictDTO.getDictName());
//        }
        for (SysDictDTO sysDictDTO : zbdqDic1) {
            zbdqList.add(sysDictDTO.getDictName());
        }
        for (SysDictDTO sysDictDTO : isShDic1) {
            isShList.add(sysDictDTO.getDictName());
        }
        for (SysDictDTO sysDictDTO : zbhyDic1) {
            zbhyList.add(sysDictDTO.getDictName());
        }

        // 转数组
        String[] isFbDic = new String[isFbDic1.size()];
        isFbList.toArray(isFbDic);
//        String[] zbxmlxDic = new String[zbxmlxDic1.size()];
//        zbxmlxList.toArray(zbxmlxDic);
//        String[] zjfwlxDic = new String[zjfwlxDic1.size()];
//        zjfwlxList.toArray(zjfwlxDic);
        String[] zbdqDic = new String[zbdqDic1.size()];
        zbdqList.toArray(zbdqDic);
        String[] isSh = new String[isShDic1.size()];
        isShList.toArray(isSh);
        String[] zbhy = new String[zbhyDic1.size()];
        zbhyList.toArray(zbhy);
        Map<String, String[]> map = new HashMap<>();
        map.put("记录地区", zbdqDic);
//        map.put("服务类型", zjfwlxDic);
        map.put("是否发布", isFbDic);
//        map.put("类型", zbxmlxDic);
        map.put("行业", zbhy);
        map.put("审核标示", isSh);
//      读取已存在的excel模板
        InputStream resourceAsStream = StreamUtil.class.getResourceAsStream("/excel/" + filename);
//        POIFSFileSystem poifsFileSystem=new POIFSFileSystem(resourceAsStream);
//        Workbook Workbook=new HSSFWorkbook(poifsFileSystem);//得到文档对象
        Workbook workbook = WorkbookFactory.create(resourceAsStream);//得到文档对象
        Sheet sheet = workbook.getSheet("sheet1");
        Row row = sheet.getRow(1);

        int physicalNumberOfCells = row.getPhysicalNumberOfCells();
        for (int i = 0; i < physicalNumberOfCells; i++) {
            Cell cell = row.getCell(i);
            String[] s = map.get(cell.toString());
            if (s != null) {
                // 单元格生成下拉狂
                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
                XSSFDataValidationConstraint dvConstraint1 = (XSSFDataValidationConstraint) dvHelper
                        .createExplicitListConstraint(s);
                // 哪些单元格将被设置为下拉, (首行,尾行,首列,尾列)
                CellRangeAddressList addressList1 = new CellRangeAddressList(2, 65536, i, i);
                XSSFDataValidation dataValidation1 = (XSSFDataValidation) dvHelper.createValidation(
                        dvConstraint1, addressList1);
                sheet.addValidationData(dataValidation1);
            }
        }
        return workbook;
    }