java poi导出带有下拉框的模板(1)

java poi导出带有下拉框的模板(1)

1. 实体类

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class ImportCommonUser {

    @Excel(name = "账号类型",width = 25,isImportField = "true_st",replace = {"长期_1", "临时_2"}, suffix = "账号")
    private Integer accountCycle;

    @Excel(name = "工号",width = 25, isImportField = "true_st")
    private String wkno;

    @Excel(name = "姓名",width = 25, isImportField = "true_st")
    private String userName;

    @Excel(name = "角色",width = 25, isImportField = "true_st")
    private String roleName;

2. 控制器

@PostMapping("/downloadModel")
    public ResponseInfo downloadModel(HttpServletRequest request, HttpServletResponse response, @RequestBody Map map){
        //下载模板名称
        String fileName = (String) map.get("fileName");
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName(fileName);
        response.setContentType("application/x-download;charset=UTF-8");
        response.addHeader("Content-disposition", "filename=demo.xls");
        Workbook workbook = null;
            if (StringUtils.isBlank(fileName)) {
                fileName = "user";
            }
            List<ImportCommonUser> list = new ArrayList<>();
            workbook = ExcelExportUtil.exportExcel(exportParams, ImportCommonUser.class, list);
            List<String> roleNames = roleService.queryRoleNames();
            //这里是下拉框
            selectRoleList(workbook,3,3, roleNames.toArray(new String[roleNames.size()]));
        //这里是自己加的 带下拉框的代码
        selectList(workbook, 0, 0, new String[]{"长期账号","临时账号"});
        OutputStream fos = null;
        try {
            fos = response.getOutputStream();
            workbook.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
            request.setAttribute("resultCode",500);
            return new ResponseInfo(false);
        } finally {
            try {
                if (fos != null) {
                    fos.close();
                }
            } catch (IOException e) {
                request.setAttribute("resultCode",500);
                e.printStackTrace();
            }
        }
        request.setAttribute("resultCode",200);
        return null;
    }
 /**
     * 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);
        //  生成下拉列表
        //  只对(x，x)单元格有效
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, firstCol, lastCol);
        //  生成下拉框内容
        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(strings);
        HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
        //  对sheet页生效
        sheet.addValidationData(dataValidation);
    }

    public static void selectRoleList(Workbook workbook,int firstCol,int lastCol,String[] strings ){
        Sheet sheet = workbook.getSheetAt(0);
        //  生成下拉列表
        //  只对(x，x)单元格有效
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, firstCol, lastCol);
        //  生成下拉框内容
        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(strings);
        HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
        //  对sheet页生效
        sheet.addValidationData(dataValidation);
    }

3. 效果图