easypoi生成Excel下拉框选项

系统管理员 2022-10-08 00:47 459阅读 0赞

/**
         * excel 导出
         * @param list
         * @param pojoClass
         * @param fileName
         * @param response
         * @param exportParams
         * @param selectList
         * @throws IOException
         */
        private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams,List<String> selectList) throws IOException {
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
            selectList(workbook, 0, 0, new String[]{"北京","上海","天津","广州","深圳"});
            downLoadExcel(fileName, response, workbook);
        }
    
        /**
         * 下载
         *
         * @param fileName 文件名称
         * @param response
         * @param workbook excel数据
         */
        private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
            try {
                response.setCharacterEncoding("UTF-8");
                response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
                workbook.write(response.getOutputStream());
            } catch (Exception e) {
                throw new IOException(e.getMessage());
            }
        }
    
    
        /**
         * 生成下拉列表
         * 
         * @param workbook
         * @param firstCol
         * @param lastCol
         * @param datas
         */
        public static void selectList(Workbook workbook,int firstCol,int lastCol,String[] datas ){
            Sheet sheet = workbook.getSheetAt(0);
            //生成下拉列表
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 90000, firstCol, lastCol);
            //生成下拉框内容
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(datas);
            DataValidation validation = dvHelper.createValidation(dvConstraint, cellRangeAddressList);
            //设置错误信息提示
            validation.setShowErrorBox(true);
            //对sheet页生效
            sheet.addValidationData(validation);
        }