Easyexcel导出带下拉框选项excel模板（解决下拉框超50个的问题）

1、为了避免excel下拉框选项过多会导致内容不显示(或者生成的时候报错：String literals in formulas can't be bigger than 255 characters ASCII easyexcel)，将下拉框的内容都存储在另一个新建的固定的sheet页，再通过引用公式关联单元格的下拉框内容，从而形成能够存储多数值的下拉框。

2、导出代码(这里演示的是一次性导出多个模板，采用压缩包的形式下载，并且表头是动态的)：

    
    
    

     

      
      
      

       
       
       

        
        
        /**
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
         * 导出模板
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
         * @param response
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
         * @param businessViewId
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
         */
       
       
       
      
      
      
     

      
      
      

       
       
       
    
        
        
        @Override
       
       
       
      
      
      
     

      
      
      

       
       
       
    
        
        
        public 
        
        
        void 
        
        
        exportTemplate
        
        
        (HttpServletResponse response, String businessViewId) 
        
        
        throws IOException {
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
                String[] split = businessViewId.split(
        
        
        ",");
       
       
       
      
      
      
     

     

      
      
      

       
       
       
        
        
        
        String 
        
        
        zipName 
        
        
        = 
        
        
        "模板.zip";
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
                response.setCharacterEncoding(
        
        
        "utf-8");
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
                response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
                response.setHeader(
        
        
        "Content-Disposition", 
        
        
        "attachment;filename*=UTF-8''" + URLEncoder.encode(zipName, 
        
        
        "UTF-8"));
       
       
       
      
      
      
     

      
      
      

       
       
       
        
        
        
        ServletOutputStream 
        
        
        out 
        
        
        = response.getOutputStream();
       
       
       
      
      
      
     

      
      
      

       
       
       
        
        
        
        ZipOutputStream 
        
        
        zipOutputStream 
        
        
        = 
        
        
        new 
        
        
        ZipOutputStream(out);
       
       
       
      
      
      
     

     

      
      
      

       
       
       
        
        
        
        for (String viewid : split) {
       
       
       
      
      
      
     

     

      
      
      

       
       
       

        
        
                    List<BusinessField> field = baseMapper.findBusinessFieldByViewId(viewid);
       
       
       
      
      
      
     

      
      
      

       
       
       
            
        
        
        //根据表id查询父表id
       
       
       
      
      
      
     

      
      
      

       
       
       
            
        
        
        String 
        
        
        parentId 
        
        
        = 
        
        
        this.findParentIdByViewId(viewid);
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
                    List<BusinessField> parentFieldList = baseMapper.findBusinessFieldByViewId(parentId);
       
       
       
      
      
      
     

      
      
      

       
       
       
            
        
        
        //定义表头
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
                    List<List<String>> headList = 
        
        
        new 
        
        
        ArrayList<>();
       
       
       
      
      
      
     

      
      
      

       
       
       
            
        
        
        //定义数据体
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
                    List<List<Object>> dataList = 
        
        
        new 
        
        
        ArrayList<>();
       
       
       
      
      
      
     

      
      
      

       
       
       
            
        
        
        // 指定标红色的列
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
                    List<Integer> columns = Arrays.asList();
       
       
       
      
      
      
     

      
      
      

       
       
       
            
        
        
        // 指定批注
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
                    HashMap<Integer, String> annotationsMap = 
        
        
        new 
        
        
        HashMap<>();
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
                    HashMap<Integer, List<String>> dropDownMap = 
        
        
        new 
        
        
        HashMap<>();
       
       
       
      
      
      
     

      
      
      

       
       
       
            
        
        
        //主表字段
       
       
       
      
      
      
     

      
      
      

       
       
       
            
        
        
        for (
        
        
        int 
        
        
        i 
        
        
        = 
        
        
        0;i<parentFieldList.size();i++){
       
       
       
      
      
      
     

      
      
      

       
       
       
                
        
        
        BusinessField 
        
        
        businessField 
        
        
        = parentFieldList.get(i);
       
       
       
      
      
      
     

      
      
      

       
       
       

        
        
                        headList.add(Lists.newArrayList(businessField.getName()));
       
       
       
      
      
      
     

      
      
      

       
       
       
                
        
        
        if (StringUtils.isNotBlank(businessField.getControlType())){
       
       
       
      
      
      
     

      
      
      

       
       
       
                    
        
        
        if (businessField.getControlType().contains(