通过URL获取Excel并为指定单元格添加下拉列表

    /**
     * 修改下载模板
     */
    public void exportTemplate(String path, HttpServletRequest request, HttpServletResponse response) {
        FileOutputStream fileOutputStream = null;
        try {
            Workbook workbook = getWorkbook(path);
            //TODO 查询数据库中数据
            List<SysCode> list = sysCodeService.list(new QueryWrapper<SysCode>().eq("module_name", "案件操作小类"));
            SysCode sysCode = new SysCode();
            sysCode.setDictName("解除措施");
            sysCode.setDictCode("10");
            list.add(sysCode);
            String[] args = new String[list.size()];
            for (int i = 0; i < list.size(); i++) {
                SysCode code = list.get(i);
                args[i] = code.getDictName() + "_" + code.getDictCode();
            }
            selectList(workbook, 2, 2, args);
            downLoadExcel("案件导入模板", workbook, request, response);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fileOutputStream != null) {
                try {
                    fileOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 根据URL获取workbook
     * @param path
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(String path) throws Exception {
        String type = path.substring(path.lastIndexOf(".") + 1);
        Workbook wb;
        //根据文件后缀（xls/xlsx）进行判断
        InputStream input = new URL(path).openStream();
        if ("xls".equals(type)) {
            //文件流对象
            wb = new HSSFWorkbook(input);
        } else if ("xlsx".equals(type)) {
            wb = new XSSFWorkbook(input);
        } else {
            throw new Exception("文件 类型错误");
        }
        return wb;
    }

    /**
     * firstRow 開始行號 根据此项目，默认为2(下标0开始)
     * lastRow  根据此项目，默认为最大65535
     * firstCol 区域中第一个单元格的列号 (下标0开始)
     * lastCol 区域中最后一个单元格的列号
     * strings 下拉内容
     */
    public static void selectList(Workbook workbook, int firstCol, int lastCol, String[] strings) {
        Sheet sheet = workbook.getSheetAt(0);
        //  生成下拉列表
        //  生成下拉框内容
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                .createExplicitListConstraint(strings);
        //  只对(x，x)单元格有效
        CellRangeAddressList addressList = new CellRangeAddressList(2, 65535, firstCol, lastCol);
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

    //下载模板excel
    public static void downLoadExcel(String fileName, Workbook workbook, HttpServletRequest request,
                                     HttpServletResponse response) throws IOException {
        OutputStream output = null;
        BufferedOutputStream bufferedOutPut = null;
        try {
            // 重置响应对象  这里可能会导致前后端跨域
            //response.reset();
            String UserAgent = request.getHeader("USER-AGENT").toLowerCase();
            // 指定下载的文件名--设置响应头
            if (UserAgent.indexOf("firefox") >= 0) {
                response.setHeader("content-disposition", "attachment;filename=\"" + new String(fileName.getBytes("UTF-8"), "iso-8859-1") + ".xlsx\"");
            } else {
                response.setHeader("Content-Disposition",
                        "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
            }
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            // 编码
            response.setCharacterEncoding("UTF-8");
            output = response.getOutputStream();
            bufferedOutPut = new BufferedOutputStream(output);
            workbook.write(bufferedOutPut);
            bufferedOutPut.flush();
        } catch (Exception e) {

        } finally {
            if (bufferedOutPut != null) {
                bufferedOutPut.close();
            }
            if (output != null) {
                output.close();
            }
        }
    }