精华内容
下载资源
问答
  • 实体类:复杂表头的情况,给@ExcelProperty的value赋值时,需要具备所有行的表头;使用注解@ExcelIgnore忽略导出的字段是否包含 5.设置导出excel的表头高宽:加在类上或者字段上 EasyExcel特点 Java领域解析,生成...

    目录

    EasyExcel特点

    一.导入excel案例

    二.读取excel的相关技术点

    1.读取excel的方式

    2.读取sheet数量

    3.指定从第几行开始读数据

    三.导出excel

    1.前端发起请求

    2.controller控制层

    3.service层

    4.实体类:复杂表头的情况,给@ExcelProperty的value赋值时,需要具备所有行的表头;使用注解@ExcelIgnore忽略导出的字段是否包含

    5.设置导出excel的表头高宽:加在类上或者字段上


    EasyExcel特点

     Java领域解析,生成Excel比较有名的框架有Apache poi,jxl等,但他们都存在一个严重的问题就是非常的耗内存,如果你的系统并发量不大的话可能还行,但是一旦并发上来后一定会OOM或者JVM频繁的full gc.

    EasyExcel是阿里巴巴开源的一个excel处理框架,以使用简单,节省内存著称,EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。

    EasyExcel采用一行一行的解析模式,并将一行的解析结果以观察者的模式通知处理(AnalysisEventListener)

    如果在项目中已经使用了POI,再引入easyexcel可能会存在冲突,要注意对应的版本

    		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi</artifactId>
    			<version>3.17</version>
    		</dependency>
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-ooxml</artifactId>
    			<version>3.17</version>
    		</dependency>
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-scratchpad</artifactId>
    			<version>3.17</version>
    		</dependency>
    		
    		<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.1.1</version>
            </dependency>

    一.导入excel案例

    需求:校验导入excel表头是否正确,excel行转成实体对象出错时,获取错误信息

    1.前端:使用bootstrap的fileinput控件,设置上传的链接及上传结束后的提示信息

    $("#file").fileinput({            
    		language : 'zh',         
    		uploadUrl :basePath+"roadData/uploadExcelFileNew",                     
    		maxFileCount : 10,//表示允许同时上传的最大文件个数
    		showCaption : true,//是否显示标题
    		browseClass : "btn btn-primary", //按钮样式
    		showPreview:true,       
    	    allowedFileExtensions: ["xls", "xlsx"], 
    		uploadAsync:true,                  
    		msgFilesTooLess:'您必须至少选择{n}个文件才能上传,请点击“浏览”选择您需要上传的文件!',
    		msgFilesTooMany:'超过了单次上传最大文件数{m}个,请分批次上传!',
    		previewFileIcon: '<i class="fa fa-file"></i>',
    		browseLabel : "浏览",
    		msgSelected:'添加了{n}个文件',   
    		dropZoneTitle:'拖拽文件到这里 …<br/>最多支持10个文件上传<br/>',
    	    uploadExtraData:function (previewId, index) {    
    	          var data = {  
    	        	 "isClearFlag": mini.get("isClearFlag").getValue()
    	          };
    	          return data;     
    	     },
    		 previewFileIconSettings: {
    		        'doc': '<i class="fa fa-file-word-o text-primary"></i>',
    		        'xls': '<i class="fa fa-file-excel-o text-success"></i>',
    		        'ppt': '<i class="fa fa-file-powerpoint-o text-danger"></i>',
    		        'jpg': '<i class="fa fa-file-photo-o text-warning"></i>',
    		        'pdf': '<i class="fa fa-file-pdf-o text-danger"></i>',
    		        'zip': '<i class="fa fa-file-archive-o text-muted"></i>',
    		        'htm': '<i class="fa fa-file-code-o text-info"></i>',
    		        'txt': '<i class="fa fa-file-text-o text-info"></i>',
    		        'mov': '<i class="fa fa-file-movie-o text-warning"></i>',
    		        'mp3': '<i class="fa fa-file-audio-o text-warning"></i>',
    		    }, 
    		    layoutTemplates:{       //是否显示预览下的上传按钮      
    		    	actionUpload:''
    		    },
    		    previewFileExtSettings: {
    		        'doc': function(ext) {
    		            return ext.match(/(doc|docx)$/i);
    		        },
    		        'xls': function(ext) {
    		            return ext.match(/(xls|xlsx)$/i);
    		        },
    		        'ppt': function(ext) {
    		            return ext.match(/(ppt|pptx)$/i);
    		        },
    		        'zip': function(ext) {
    		            return ext.match(/(zip|rar|tar|gzip|gz|7z)$/i);
    		        },
    		        'htm': function(ext) {
    		            return ext.match(/(php|js|css|htm|html)$/i);
    		        },
    		        'txt': function(ext) {
    		            return ext.match(/(txt|ini|md)$/i);
    		        },
    		        'mov': function(ext) {
    		            return ext.match(/(avi|mpg|mkv|mov|mp4|3gp|webm|wmv)$/i);
    		        },
    		        'mp3': function(ext) {
    		            return ext.match(/(mp3|wav)$/i);
    		        },
    		    }
    	});   
    	
    	$("#file").on('fileuploaded', function(event, data, previewId, index) {
    		var message = mini.decode(data);   
    	   	if(message.response.type == 200){  
    	   		showMsg("文件上传成功","success",1800,"center","center",closeWindow());    
    	   	}else{
    	   		showMsg(message.response.msg,"danger",3000,"center","center");
    	   	}    
    	});  
    	
    	function closeWindow(action) {
    	    if (window.CloseOwnerWindow) return window.CloseOwnerWindow(action);
    	    else window.close();              
    	}

    2.controller控制层:文件上传调用的方法

     /**
         * @Description 上传文件--使用easyexcel方式解析
         * @author qingyun
         * @Date 2021年5月19日 上午10:26:03
         */
        @RequestMapping("/uploadExcelFileNew")  
    	public String uploadExcelFileNew(@RequestParam("file") MultipartFile file, HttpServletRequest request,HttpServletResponse response) {
    		return roadDataService.uploadExcelFileNew(file,request,response);  
    	}

    3.serice层:处理文件上传逻辑,这里定义一个工具类EasyExcelUtils进行处理,对处理结果根据Message标识判断是否解析正常,正常则设置其他字段值,把数据入库,不正常,则把message返回给前端

     /**
         * @Description 上传文件--使用easyexcel方式解析
         * @author qingyun
         * @Date 2021年5月19日 上午10:26:03
         */
    	public String uploadExcelFileNew(MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
    		Message message = new Message();
    		EasyExcelUtils easyExcelUtils = new EasyExcelUtils(RoadData.class);  //创建工具类时传递class,用于后面比对表头使用
    		try {
    			request.setCharacterEncoding("utf-8");
    			response.setCharacterEncoding("utf-8");
    			InputStream fileInput = file.getInputStream(); // 输入流
    			EasyExcel.read(fileInput,RoadData.class,easyExcelUtils).sheet().doRead();
    			message = easyExcelUtils.getMessage();
    			if(Message.OK == message.getType()) {   //解析完成没有错误
    				List<Object> list = easyExcelUtils.getList();   //使用Object类型存放集合
    				if (null != list && list.size() > 0) {
    					List<GjArea> areaList = getGjArea();  //获取到所有的政区集合
    					Date nowDate = new Date();
    				    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    				    String nowDateStr = sdf.format(nowDate);
    				    List<RoadData> roadDataList = new ArrayList<RoadData>();
    					for (int i = 0;i < list.size();i++) { //设置其他非excel字段的值
    						RoadData roadData = (RoadData) list.get(i);
    						roadData.setId(this.getUUID());
    						roadData.setCreateTime(nowDate);
    						roadData.setCreateUserCode(this.getUserCode());
    						roadData.setCreateUserName(this.getUserEntity().getName());
    						roadData.setUnit(formatUnit(roadData.getUnit(),areaList));
    						roadData.setPci_score(RoadImportUtils.getPCIScore(roadData.getRoadGradeName(), String.valueOf(roadData.getPci()), String.valueOf(roadData.getPciAfter()),String.valueOf(roadData.getRepYear()),roadData.getRectificaFlag()));
    						roadData.setRqi_score(RoadImportUtils.getRQIScore(roadData.getPaveTypeName(), String.valueOf(roadData.getRqi()), String.valueOf(roadData.getRqiAfter()),String.valueOf(roadData.getRepYear()),roadData.getRectificaFlag()));
    						roadData.setRdi_score(RoadImportUtils.getRDIScore(roadData.getRoadGradeName(), roadData.getPaveTypeName(),String.valueOf(roadData.getRdi()), String.valueOf(roadData.getRdiAfter()),String.valueOf(roadData.getRepYear()),roadData.getRectificaFlag()));
    						roadDataList.add(roadData);
    					}
    					String isClearFlag = request.getParameter("isClearFlag");
    					if("true".equals(isClearFlag)) {   //先清除数据再插入,只清楚在这次之前添加的数据
    						String hql = "delete FROM score.road_data WHERE  DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s' ) < '"+nowDateStr+"' ";
    						this.getDao().saveOrUpdateBySql(hql, null);
    					}
    					roadDataRepository.save(roadDataList);
    					message.setType(Message.OK);
    					message.setMsg("文件上传解析成功");
    				}
    			}
    		} catch (Exception e) {
    			e.printStackTrace();
    			Message messageListener = easyExcelUtils.getMessage();
    			if(null == messageListener || null == messageListener.getType()) {
    				message.setType(Message.ERROR);
    				message.setMsg("上传解析文件错误");
    			}else {
    				message = messageListener;
    			}
    		}
    		return message.toJson();
    	}

    4.EasyExcelUtils类:使用@Component注解修饰,使类加载进spring容器中进行管理;数据的类型使用Object进行定义,定义一个Mesage类用于记录解析和验证表头是否与模板一致的信息;创建EasyExcelUtils时,传递一个接收解析excel类的class对象,使用注解ExcelProperty获取到类的字段,并与excel中的进行比对,验证导入的excel是否符合要求。

    /**
     * @Description 使用easyexcel方式解析数据的工具类
     * @author qingyun
     * @Date 2021年5月19日 上午10:35:58
     */
    @Component
    public class EasyExcelUtils extends AnalysisEventListener<Object> {
    
    	List<Object> list = new ArrayList<Object>();
    	
    	Message message = new Message();
    	
    	Class clazz;
    	
    	
    	
    	public EasyExcelUtils() {
    		super();
    		// TODO Auto-generated constructor stub
    	}
    
    	public EasyExcelUtils(Class clazz) {
    		super();
    		this.clazz = clazz;
    	}
    
    	/**
    	 * @Description invoke方法为一行一行读取excel内容
    	 * @author qingyun
    	 * @Date 2021年5月19日 上午10:43:17
    	 */
    	@Override
    	public void invoke(Object data, AnalysisContext context) {
    		list.add(data); 
    	}
    	
    	/**
    	 * @Description invokeHeadMap读取excel表头,校验表头是否正确
    	 * @author qingyun
    	 * @Date 2021年5月19日 上午10:44:43
    	 */
    	@Override
        public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
            Map<Integer, String> head = new HashMap<>();
    		try {
    			head = getIndexNameMap(clazz);   //通过class获取到使用@ExcelProperty注解配置的字段
    		} catch (NoSuchFieldException e) {
    			e.printStackTrace();
    		}
            Set<Integer> keySet = head.keySet();  //解析到的excel表头和实体配置的进行比对
            for (Integer key : keySet) {
                if (StringUtils.isEmpty(headMap.get(key))) {
                	message.setType(Message.ERROR);
                	message.setMsg("表头第"+key+1+"列为空,请参照模板填写");
                    throw new ExcelAnalysisException("解析excel出错,请传入正确格式的excel");
                }
                if (!headMap.get(key).equals(head.get(key))) {
                	message.setType(Message.ERROR);
                	message.setMsg("表头第"+key+1+"列【"+headMap.get(key)+"】与模板【"+head.get(key)+"】不一致,请参照模板填写");
                    throw new ExcelAnalysisException("解析excel出错,请传入正确格式的excel");
                }
            }
        }
    	
        /**
         * @Description 读取完成之后进行的处理
         * @author qingyun
         * @Date 2021年5月19日 上午10:45:13
         */
    	@Override
    	public void doAfterAllAnalysed(AnalysisContext context) {
    		if (null == message || message.getType() == null) {
    			message.setMsg("数据解析完成");
    			message.setType(Message.OK);
    		}
    	}
    
    	/**
    	 * @Description 通过class获取类字段信息
    	 * @author qingyun
    	 * @Date 2021年5月19日 下午1:41:47
    	 */
    	public Map<Integer, String> getIndexNameMap(Class clazz) throws NoSuchFieldException {
    	     Map<Integer, String> result = new HashMap<>();
    	     Field field;
    	     Field[] fields = clazz.getDeclaredFields();     //获取类中所有的属性
    	     for (int i = 0; i < fields.length; i++) {
    	         field = clazz.getDeclaredField(fields[i].getName());
    	         field.setAccessible(true);
    	         ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);//获取根据注解的方式获取ExcelProperty修饰的字段
    	         if (excelProperty != null) {
    	             int index = excelProperty.index();         //索引值
    	             String[] values = excelProperty.value();   //字段值
    	             StringBuilder value = new StringBuilder();
    	             for (String v : values) {
    	                 value.append(v);
    	             }
    	             result.put(index, value.toString());
    	         }
    	     }
    	     return result;
    	 }
    	
    	/**
    	 * @Description 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
    	 * @author qingyun
    	 * @Date 2021年5月19日 下午3:02:49
    	 */
    	@Override
    	public void onException(Exception exception, AnalysisContext context) {
    	    // 如果是某一个单元格的转换异常 能获取到具体行号
    	    // 如果要获取头的信息 配合invokeHeadMap使用
    	    if (exception instanceof ExcelDataConvertException) {
    	        ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
    	    	message.setType(Message.ERROR);
            	message.setMsg("第"+(excelDataConvertException.getRowIndex()+1)+"行,第"+(excelDataConvertException.getColumnIndex()+1)+"列解析异常,请参照模板填写");
    	    }
    	}
    
    	public List<Object> getList() {
    		return list;
    	}
    
    	public void setList(List<Object> list) {
    		this.list = list;
    	}
    
    	public Message getMessage() {
    		return message;
    	}
    
    	public void setMessage(Message message) {
    		this.message = message;
    	}
    	
    	
    	
    	
    }

    5.RoadData实体类:使用@ExcelProperty注解标识字段,指定index和value,用于接收excel的值,以及进行表头的比对

    public class RoadData implements Serializable {
    	private static final long serialVersionUID = 1L;
        private String id;         //id
        @ExcelProperty(value = "年份",index = 0)
        private String repYear;         //年份
        
        @ExcelProperty(value = "养管单位",index = 1)
        private String unit;         //养管单位
        
        @ExcelProperty(value = "路线",index = 2)
        private String roadCode;         //路线编码
        
        @ExcelProperty(value = "上下行",index = 3)
        private String roadDirectName;         //行车方向
        
        @ExcelProperty(value = "路段起点",index = 4)
        private BigDecimal startStake;         //起点桩号
        
        @ExcelProperty(value = "路段终点",index = 5)
        private BigDecimal endStake;         //终点桩号
        
        @ExcelProperty(value = "长度km",index = 6)
        private BigDecimal roadLength;         //长度
        
        @ExcelProperty(value = "技术等级",index = 7)
        private String roadGradeName;         //技术等级
        
        @ExcelProperty(value = "路面类型",index = 8)
        private String paveTypeName;         //路面类型
        
        @ExcelProperty(value = "PQI",index = 9)
        private BigDecimal pqi;         //PQI
        
        @ExcelProperty(value = "PCI",index = 10)
        private BigDecimal pci;         //PCI
        
        @ExcelProperty(value = "RQI",index = 11)
        private BigDecimal rqi;         //rqi
        
        @ExcelProperty(value = "RDI",index = 12)
        private BigDecimal rdi;         //rdi
        
        @ExcelProperty(value = "DR",index = 13)
        private BigDecimal dr;         //dr
        
        @ExcelProperty(value = "IRI",index = 14)
        private BigDecimal iri;         //iri
        
        @ExcelProperty(value = "RD",index = 15)
        private BigDecimal rd;         //rd
        
        private BigDecimal pci_score;         //PCI得分
        
        private BigDecimal rqi_score;         //rqi得分
        
        private BigDecimal rdi_score;         //rdi得分
        
    
        @ExcelProperty(value = "PQI分级",index = 16)
        private String pqiGrade;         //PQI分级
        
        @ExcelProperty(value = "PCI分级",index = 17)
        private String pciGrade;         //PCI分级
        
        @ExcelProperty(value = "RQI分级",index = 18)
        private String rqiGrade;         //RQI分级
        
        @ExcelProperty(value = "RDI分级",index = 19)
        private String rdiGrade;         //RDI分级
        
        @ExcelProperty(value = "区域",index = 20)
        private String theAreaName;         //区域
        
        @ExcelProperty(value = "抽检性质",index = 21)
        private String randomNature;         //抽检性质
        
        @ExcelProperty(value = "PCI整改后得分",index = 22)
        private BigDecimal pciAfter;         //PCI整改后得分
        
        @ExcelProperty(value = "RQI整改后得分",index = 23)
        private BigDecimal rqiAfter;         //RQI整改后得分
        
        @ExcelProperty(value = "RDI整改后得分",index = 24)
        private BigDecimal rdiAfter;         //RDI整改后得分
        
        @ExcelProperty(value = "整改标识",index = 25)
        private String rectificaFlag;         //整改标识(未完成/已完成)
        
    	private String createUserCode;
    	private String createUserName;
    	private Date createTime;
    }

    二.读取excel的相关技术点

    1.读取excel的方式

    (1)源码按路径path的方式

      public static ExcelReaderBuilder read(String pathName, Class head, ReadListener readListener) {
            ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
            excelReaderBuilder.file(pathName);
            if (head != null) {
                excelReaderBuilder.head(head);
            }
            if (readListener != null) {
                excelReaderBuilder.registerReadListener(readListener);
            }
            return excelReaderBuilder;
        }

    (2)按文件输入流的方式读

      public static ExcelReaderBuilder read(InputStream inputStream, Class head, ReadListener readListener) {
            ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
            excelReaderBuilder.file(inputStream);
            if (head != null) {
                excelReaderBuilder.head(head);
            }
            if (readListener != null) {
                excelReaderBuilder.registerReadListener(readListener);
            }
            return excelReaderBuilder;
        }

    (3)按file读取

     public static ExcelReaderBuilder read(File file, Class head, ReadListener readListener) {
            ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
            excelReaderBuilder.file(file);
            if (head != null) {
                excelReaderBuilder.head(head);
            }
            if (readListener != null) {
                excelReaderBuilder.registerReadListener(readListener);
            }
            return excelReaderBuilder;
        }

    2.读取sheet数量

    (1)只能读取到最前面的一个sheet

    EasyExcel.read(fileInput,RoadData.class,easyExcelUtils).sheet().doRead()

    (2)读取所有的sheet:

    EasyExcel.read(fileInput,RoadData.class,easyExcelUtils).doReadAll()

    (3)读取指定的sheet:

    	ExcelReader excelReader = EasyExcel.read(fileInput).build();
    		    // 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
    		    ReadSheet readSheet1 =
    		        EasyExcel.readSheet(0).head(RoadData.class).registerReadListener(easyExcelUtils).build();
    		    ReadSheet readSheet2 =
    		        EasyExcel.readSheet(1).head(RoadData.class).registerReadListener(easyExcelUtils).build();
    		    // 这里注意 一定要把sheet1 sheet2 一起传进去,不然有个问题就是03版的excel 会读取多次,浪费性能
    		    excelReader.read(readSheet1, readSheet2);
    		    // 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
    		    excelReader.finish();

    3.指定从第几行开始读数据

    EasyExcel.read(fileInput,RoadData.class,easyExcelUtils).headRowNumber(1).doReadAll();
    			

    三.导出excel

    1.前端发起请求

    	function download(){  //导出报表
    		var zqCode = "";
    		var year = ""; 
    		var params = {"zqCode":zqCode,"year":year};
        	var url = basePath + "roadData/download";
    		downloadFile(url, params);    //组织和发送下载请求  
    	}
    	
    	//组织和发送下载请求 
    	function downloadFile(path, params) {
    		$("#downloadform").remove();
    		var form = $("<form>"); //定义一个form表单  
    		form.attr("id", "downloadform");
    		form.attr("style", "display:none");
    		form.attr("target", "");
    		form.attr("method", "post");
    		form.attr("action", path);
    		for(var key in params) {
    			var input1 = $("<input>");
    			input1.attr("type", "hidden");
    			input1.attr("name", key);
    			input1.attr("value", params[key]);
    			form.append(input1);
    		}
    		$("body").append(form); //将表单放置在web中  
    		form.submit(); //表单提交()
    	}
    

    2.controller控制层

     @RequestMapping(value = { "/download" }, produces = { "text/html;charset=UTF-8" })
    	@ResponseBody
    	public void download(HttpServletRequest request, HttpServletResponse response) {
    		response.setCharacterEncoding("UTF-8");
    		//String id = request.getParameter("id");
    		roadDataService.download(response);
    	}

    3.service层

    	public void download(HttpServletResponse response) {
            String fileName;
    		try {
    			fileName = "文件名称.xlsx";
    	        fileName = new String(URLEncoder.encode(fileName, "UTF-8").getBytes(), "ISO-8859-1");
    			response.addHeader("Content-Disposition", " attachment;filename=" + fileName);
    			response.setContentType("application/octet-stream");
    			
    	        ServletOutputStream out = response.getOutputStream();
    			EasyExcel.write(out,RoadDataNew.class).sheet("学生列表").doWrite(getData());
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    
    		
    	}

    4.实体类:复杂表头的情况,给@ExcelProperty的value赋值时,需要具备所有行的表头;使用注解@ExcelIgnore忽略导出的字段是否包含

    /**
     * @Description 添加此类是处理复杂表头的情况
     * @author qingyun
     * @Date 2021年5月19日 下午4:51:26
     */
    public class RoadDataNew implements Serializable {
    	private static final long serialVersionUID = 1L;
    	@ExcelIgnore
        private String id;         //id
        @ExcelProperty(value = {"年份","年份"},index = 0)
        private String repYear;         //年份
        
        @ExcelProperty(value = {"养管单位","养管单位"},index = 1)
        private String unit;         //养管单位
        
        @ExcelProperty(value = {"路线情况","路线"},index = 2)
        private String roadCode;         //路线编码
        
        @ExcelProperty(value = {"路线情况","上下行"},index = 3)
        private String roadDirectName;         //行车方向
        
        @ExcelProperty(value = {"路段起点","路段起点"},index = 4)
        private BigDecimal startStake;         //起点桩号
        
        @ExcelProperty(value = {"路段终点","路段终点"},index = 5)
        private BigDecimal endStake;         //终点桩号
        
        @ExcelProperty(value = {"长度km","长度km"},index = 6)
        private BigDecimal roadLength;         //长度
        
        @ExcelProperty(value = {"路面情况","技术等级"},index = 7)
        private String roadGradeName;         //技术等级
        
        @ExcelProperty(value = {"路面情况","路面类型"},index = 8)
        private String paveTypeName;         //路面类型
        
        @ExcelProperty(value = {"PQI","PQI"},index = 9)
        private BigDecimal pqi;         //PQI
        
        @ExcelProperty(value = {"PCI","PCI"},index = 10)
        private BigDecimal pci;         //PCI
        
        @ExcelProperty(value = {"RQI","RQI"},index = 11)
        private BigDecimal rqi;         //rqi
        
        @ExcelProperty(value = {"RDI","RDI"},index = 12)
        private BigDecimal rdi;         //rdi
        
        @ExcelProperty(value = {"DR","DR"},index = 13)
        private BigDecimal dr;         //dr
        
        @ExcelProperty(value = {"IRI","IRI"},index = 14)
        private BigDecimal iri;         //iri
        
        @ExcelProperty(value = {"RD","RD"},index = 15)
        private BigDecimal rd;         //rd
        
        @ExcelIgnore
        private BigDecimal pci_score;         //PCI得分
        
        @ExcelIgnore
        private BigDecimal rqi_score;         //rqi得分
        
        @ExcelIgnore
        private BigDecimal rdi_score;         //rdi得分
        
    
        @ExcelProperty(value = {"PQI分级","PQI分级"},index = 16)
        private String pqiGrade;         //PQI分级
        
        @ExcelProperty(value = {"PCI分级","PCI分级"},index = 17)
        private String pciGrade;         //PCI分级
        
        @ExcelProperty(value = {"RQI分级","RQI分级"},index = 18)
        private String rqiGrade;         //RQI分级
        
        @ExcelProperty(value = {"RDI分级","RDI分级"},index = 19)
        private String rdiGrade;         //RDI分级
        
        @ExcelProperty(value = {"区域","区域"},index = 20)
        private String theAreaName;         //区域
        
        @ExcelProperty(value = {"抽检性质","抽检性质"},index = 21)
        private String randomNature;         //抽检性质
        
        @ExcelProperty(value = {"PCI整改后得分","PCI整改后得分"},index = 22)
        private BigDecimal pciAfter;         //PCI整改后得分
        
        @ExcelProperty(value = {"RQI整改后得分","RQI整改后得分"},index = 23)
        private BigDecimal rqiAfter;         //RQI整改后得分
        
        @ExcelProperty(value = {"RDI整改后得分","RDI整改后得分"},index = 24)
        private BigDecimal rdiAfter;         //RDI整改后得分
        
        @ExcelProperty(value = {"整改标识","整改标识"},index = 25)
        private String rectificaFlag;         //整改标识(未完成/已完成)
        
        @ExcelIgnore
    	private String createUserCode;
        
        @ExcelIgnore
    	private String createUserName;
        
        @ExcelIgnore
    	private Date createTime;
    }

    导出截图

    5.设置导出excel的表头高宽:加在类上或者字段上

    @HeadRowHeight(40)   //设置投行高
    @ContentRowHeight(20)//设置文本行高
    @ColumnWidth(10)     //设置列宽

    展开全文
  • 懒得写废话了。。。package com.ruoyi.common.utils.poi;import com.ruoyi.framework.config.RuoYiConfig;import ...import org.apache.poi.ss.usermodel.Cell;import org.apache.poi...

    懒得写废话了。。。

    package com.ruoyi.common.utils.poi;

    import com.ruoyi.framework.config.RuoYiConfig;

    import com.ruoyi.framework.web.domain.AjaxResult;

    import org.apache.poi.ss.usermodel.Cell;

    import org.apache.poi.ss.usermodel.Row;

    import org.apache.poi.ss.usermodel.Sheet;

    import org.apache.poi.ss.usermodel.Workbook;

    import org.apache.poi.ss.util.CellRangeAddress;

    import org.apache.poi.xssf.streaming.SXSSFWorkbook;

    import org.slf4j.Logger;

    import org.slf4j.LoggerFactory;

    import java.io.File;

    import java.io.FileOutputStream;

    import java.io.OutputStream;

    import java.lang.reflect.Field;

    import java.util.ArrayList;

    import java.util.Comparator;

    import java.util.List;

    import java.util.UUID;

    public class DocumentUtil {

    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    private String sheetName;

    private Workbook wb;

    private Sheet sheet;

    private List list;

    private List columns;

    private List fields;

    private int maxDepth;

    public Class clazz;

    public DocumentUtil(Class clazz)

    {

    this.clazz = clazz;

    }

    public void init(List list, List columns, String sheetName)

    {

    if (list == null)

    {

    list = new ArrayList();

    }

    this.list = list;

    this.fields = new ArrayList<>();

    this.columns = columns;

    this.sheetName = sheetName;

    this.wb = new SXSSFWorkbook(500);

    }

    public AjaxResult exportExcel(List list, List columns, String sheetName)

    {

    this.init(list, columns, sheetName);

    return exportExcel();

    }

    private AjaxResult exportExcel() {

    OutputStream out = null;

    try {

    sheet = wb.createSheet(sheetName);

    generateHeader(this.columns);

    this.fields.sort((a, b) -> b.getFirstCol() - a.getFirstCol());

    generateData(this.list,maxDepth + 1);

    String filename = encodingFilename(sheetName);

    out = new FileOutputStream(getAbsoluteFile(filename));

    wb.write(out);

    return AjaxResult.success(filename);

    }

    catch (Exception ex) {

    System.out.print(ex.getMessage());

    }

    return null;

    }

    public String encodingFilename(String filename)

    {

    filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";

    return filename;

    }

    public String getAbsoluteFile(String filename)

    {

    String downloadPath = RuoYiConfig.getDownloadPath() + filename;

    File desc = new File(downloadPath);

    if (!desc.getParentFile().exists())

    {

    desc.getParentFile().mkdirs();

    }

    return downloadPath;

    }

    private void generateHeader(List columns) {

    maxDepth = getMaxDepth(this.columns, 0);

    generateHeader(columns, null, maxDepth);

    }

    private void generateHeader(List columns, Column parent, int maxDepth) {

    int colIndex = 0, rowIndex = 0;

    if (parent != null) {

    colIndex = parent.getFirstCol();

    rowIndex = parent.getLastRow() + 1;

    }

    for (Column column : columns) {

    Row row = sheet.getRow(rowIndex);

    if (row == null) {

    row = sheet.createRow(rowIndex);

    }

    column.setFirstRow(rowIndex);

    column.setLastRow(rowIndex);

    column.setFirstCol(colIndex);

    column.setLastCol(colIndex);

    if (column.getChildrens() != null && column.getChildrens().size() > 0) {

    column.setLastCol(colIndex + column.getChildrens().size() - 1);

    generateHeader(column.getChildrens(), column, maxDepth);

    } else {

    if (column.getWidth() > 0) {

    this.sheet.setColumnWidth(colIndex, column.getWidth() * 256);

    }

    this.fields.add(column);

    column.setLastRow(maxDepth);

    }

    if (parent != null && column.getLastCol() > parent.getLastCol()) {

    parent.setLastCol(column.getLastCol());

    }

    if (column.getLastRow() > column.getFirstRow() || column.getLastCol() > column.getFirstCol()) {

    sheet.addMergedRegion(new CellRangeAddress(column.getFirstRow(), column.getLastRow(), column.getFirstCol(), column.getLastCol()));

    }

    Cell cell = row.createCell(column.getFirstCol());

    cell.setCellValue(column.getName());

    colIndex = column.getLastCol() + 1;

    }

    }

    private int generateData(List list, int firstRow) {

    int lastRow = firstRow;

    int totalRow = 0;

    for (T item : list) {

    Row row = sheet.getRow(lastRow);

    if (row == null) {

    row = sheet.createRow(lastRow);

    }

    if (item.getChildrens() != null && item.getChildrens().size() > 0) {

    int total = generateData(item.getChildrens(), lastRow);

    lastRow += total - 1;

    totalRow += total;

    } else {

    totalRow++;

    }

    Class cls = item.getClass();

    for (Field field : cls.getDeclaredFields()) {

    Column column = this.fields.stream().filter(p -> p.getField().equals(field.getName())).findFirst().get();

    try {

    field.setAccessible(true);

    Object obj = field.get(item);

    String value = obj == null ? "" : obj.toString();

    Cell cell = row.createCell(column.getFirstCol());

    cell.setCellValue(value);

    if (lastRow > firstRow) {

    sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, column.getFirstCol(), column.getLastCol()));

    }

    } catch (Exception ex) {

    System.out.print(ex.getMessage());

    }

    }

    lastRow++;

    firstRow = lastRow;

    }

    return totalRow;

    }

    private > int getMaxDepth(List list, int depth) {

    int maxDepth = 0;

    for (TreeNode item : list)

    {

    item.setDepth(depth);

    if (item.getChildrens() != null)

    {

    int res = getMaxDepth(item.getChildrens(), depth + 1);

    if (res > maxDepth) maxDepth = res;

    }

    if (depth > maxDepth) maxDepth = depth;

    }

    return maxDepth;

    }

    }

    用到的实体类:

    package com.ruoyi.common.utils.poi;

    import java.util.List;

    public abstract class TreeNode {

    private int depth;

    private List childrens;

    public int getDepth() {

    return depth;

    }

    public void setDepth(int depth) {

    this.depth = depth;

    }

    public List getChildrens() {

    return childrens;

    }

    public void setChildrens(List childrens) {

    this.childrens = childrens;

    }

    }

    package com.ruoyi.common.utils.poi;

    import java.util.List;

    public class Column extends TreeNode {

    public Column(String name, String field) {

    this.name = name;

    this.field = field;

    }

    public Column(String name, List childrens) {

    this.name = name;

    this.setChildrens(childrens);

    }

    private String field;

    private String name;

    private int width;

    private int firstRow;

    private int lastRow;

    private int firstCol;

    private int lastCol;

    public String getField() {

    return field;

    }

    public void setField(String field) {

    this.field = field;

    }

    public String getName() {

    return name;

    }

    public void setName(String name) {

    this.name = name;

    }

    public int getWidth() {

    return width;

    }

    public void setWidth(int width) {

    this.width = width;

    }

    public int getFirstRow() {

    return firstRow;

    }

    public void setFirstRow(int firstRow) {

    this.firstRow = firstRow;

    }

    public int getLastRow() {

    return lastRow;

    }

    public void setLastRow(int lastRow) {

    this.lastRow = lastRow;

    }

    public int getFirstCol() {

    return firstCol;

    }

    public void setFirstCol(int firstCol) {

    this.firstCol = firstCol;

    }

    public int getLastCol() {

    return lastCol;

    }

    public void setLastCol(int lastCol) {

    this.lastCol = lastCol;

    }

    }

    展开全文
  • Java导出Excel 复杂表头

    2021-03-12 17:42:08
    一种是直接使用模板直接添加数据就可以=== 复杂表头 依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</...

    文章标题

    导出表格

    导出表格的方式在我的理解有两种
    一种是直接用代码控制表头==== 简单的表头
    一种是直接使用模板直接添加数据就可以=== 复杂的表头

    依赖

    <dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi-ooxml</artifactId>
       <version>3.9</version>
    </dependency>
    
    <!--下面是我直接测试表格,因为要在浏览器导出,直接创建Springboot 简单访问-->
     <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
                <version>2.3.4.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
            <dependency>
                <groupId>javax.servlet</groupId>
                <artifactId>javax.servlet-api</artifactId>
                <version>4.0.1</version>
            </dependency>
    
    package com.example.demo.test;
    
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    import java.net.URLEncoder;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    /**
     * @program: Ecology1
     * @description: this is a class
     * @author: Mr.zeng
     * @create: 2021-03-12 10:02
     **/
    
    public class TestTemplateExcel {
        /*
         *
         */
        private static final long serialVersionUID = 1L;
    
        /**
         * 生成excel并下载
         */
        public void exportExcel(HttpServletResponse response, HttpServletRequest request) {
            String userName = request.getParameter("userName");
            File newFile = createNewFile();
            // File newFile = new File("d:/ss.xls");
            // 新文件写入数据,并下载*****************************************************
            InputStream is = null;
            Workbook workbook = null;
            Sheet sheet = null;
            try {
                is = new FileInputStream(newFile);// 将excel文件转为输入流
    
                workbook=XlsImpUtil.create(is);
    //            workbook = new XSSFWorkbook(is);// 创建个workbook,
                // 获取第一个sheet
                sheet = workbook.getSheetAt(0);
            } catch (Exception e1) {
                e1.printStackTrace();
            }
    
            if (sheet != null) {
                try {
                    // 写数据
                    FileOutputStream fos = new FileOutputStream(newFile);
                    Row row = sheet.getRow(3);
                    if (row == null) {
                        row = sheet.createRow(3);
                    }
                    Cell cell = row.getCell(0);
                    if (cell == null) {
                        cell = row.createCell(0);
                    }
    
                    // TODO 定义一个list集合假数据
                    List<Map<String, Object>> lst = new ArrayList();
                    Map<String, Object> map1 = new HashMap<String, Object>();
                    // 只能添加11个
                    for (int i = 0; i < 11; i++) {
                        map1.put("id" + i, i);
                        lst.add(map1);
                    }
                    row=sheet.getRow(1);
                    String sj="填报日期:2020年03月24日 15:52 星期四";
                    cell=row.getCell(0);
                    cell.setCellValue(sj);
                    for (int m = 0; m < lst.size(); m++) {
                        Map<String, Object> map = lst.get(m);
                        row=sheet.getRow(m+3);
    //                    row = sheet.createRow((int) m + 3);
                        for (int i = 0; i < 5; i++) {
                            String value = map.get("id" + m) + "";
                            if (value.equals("null")) {
                                value = "0";
                            }
                            if(row.getRowNum()>= 7 && row.getRowNum()<=12){
                                cell=row.getCell(i+2);
                            }else {
                                cell=row.getCell(i+2);
                            }
    
                        /*    cell = row.createCell(i);*/
                            cell.setCellValue(value);
                        }
    
                    }
                    // 填报人
                    row=sheet.getRow(14);
                    String tbr="\t\t填报人:"+userName;
                    cell=row.getCell(0);
                    cell.setCellValue(tbr);
    
                    workbook.write(fos);
                    fos.flush();
                    fos.close();
                    // 下载
                    InputStream fis = new BufferedInputStream(new FileInputStream(
                            newFile));
    //                    HttpServletResponse response = /*ServletActionContext.getResponse();*/null;
                    byte[] buffer = new byte[fis.available()];
                    fis.read(buffer);
                    fis.close();
                    response.reset();
                    response.setContentType("text/html;charset=UTF-8");
                    OutputStream toClient = new BufferedOutputStream(
                            response.getOutputStream());
                    response.setContentType("application/x-msdownload");
                    String newName = URLEncoder.encode(
                            "活动报表" + System.currentTimeMillis() + ".xlsx",
                            "UTF-8");
                    response.addHeader("Content-Disposition",
                            "attachment;filename=\"" + newName + "\"");
                    response.addHeader("Content-Length", "" + newFile.length());
                    toClient.write(buffer);
                    toClient.flush();
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    try {
                        if (null != is) {
                            is.close();
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
            // 删除创建的新文件
             this.deleteFile(newFile);
        }
        /**
         * 复制文件
         *
         * @param s
         *            源文件
         * @param t
         *            复制到的新文件
         */
        public void fileChannelCopy(File s, File t) {
            try {
                InputStream in = null;
                OutputStream out = null;
                try {
                    in = new BufferedInputStream(new FileInputStream(s), 1024);
                    out = new BufferedOutputStream(new FileOutputStream(t), 1024);
                    byte[] buffer = new byte[1024];
                    int len;
                    while ((len = in.read(buffer)) != -1) {
                        out.write(buffer, 0, len);
                    }
                } finally {
                    if (null != in) {
                        in.close();
                    }
                    if (null != out) {
                        out.close();
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        private String getSispPath() {
            String classPaths = "C:\\Users\\zp\\IdeaProjects\\Ecology1\\src\\main\\resources";
    //        String classPaths="/usr/weaver/ecology/classbean/com/api/zp";
            String[] aa = classPaths.split("/");
            String sispPath = "";
            for (int i = 1; i < aa.length - 2; i++) {
                sispPath += aa[i] + "/";
            }
            return sispPath;
        }
        /**
         * 读取excel模板,并复制到新文件中供写入和下载
         *
         * @return
         */
        public File createNewFile() {
            // 读取模板,并赋值到新文件************************************************************
           final String name="C:\\Users\\zp\\IdeaProjects\\demo\\活动报表";
    //       String name="/usr/weaver/ecology/classbean/com/api/zp/活动报表";
            // 文件模板路径
            String path =  name+".xls";
            File file = new File(path);
            // 保存文件的路径
            String realPath =  "";
            // 新的文件名
            String newFileName = name + System.currentTimeMillis() + ".xls";
            // 判断路径是否存在
            File dir = new File(realPath);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            // 写入到新的excel
            File newFile = new File(newFileName);
            try {
                newFile.createNewFile();
                // 复制模板到新文件
                fileChannelCopy(file, newFile);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return newFile;
        }
        /**
         * 下载成功后删除
         *
         * @param files
         */
        private void deleteFile(File... files) {
            for (File file : files) {
                if (file.exists()) {
                    file.delete();
                }
            }
        }
        }
    
    
    import com.fasterxml.jackson.databind.exc.InvalidFormatException;
    import org.apache.poi.POIXMLDocument;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.PushbackInputStream;
    
    /**
     * @program: Ecology
     * @description: this is a class
     * @author: Mr.zeng
     * @create: 2021-03-12 11:34
     **/
    
    public class XlsImpUtil {
        public static Workbook create(InputStream inp) throws IOException, InvalidFormatException, org.apache.poi.openxml4j.exceptions.InvalidFormatException {
            if (!inp.markSupported()) {
                inp = new PushbackInputStream(inp, 8);
            }
            if (POIFSFileSystem.hasPOIFSHeader(inp)) {
                return new HSSFWorkbook(inp);
            }
            if (POIXMLDocument.hasOOXMLHeader(inp)) {
                return new XSSFWorkbook(OPCPackage.open(inp));
            }
            throw new IllegalArgumentException("你的excel版本目前poi解析不了");
        }
    }
    

    我的Controller

    import com.example.demo.test.TestTemplateExcel;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    /**
     * @program: Ecology1
     * @description: this is a class
     * @author: Mr.zeng
     * @create: 2021-03-12 10:52
     **/
    @Controller
    @RequestMapping("/excel")
    public class ExcelMainController {
    
        @RequestMapping("/port")
        public String excelport(HttpServletResponse response, HttpServletRequest request){
             new TestTemplateExcel().exportExcel(response,request);
             return "";
        }
    }
    
    展开全文
  • 效果图 <!... <... <head>...复杂表头+select下拉框默认值+单元格合并</title> <script src="../../../static/lib/layui/layui.js" charset="utf-8"></script> <style&g

    效果图在这里插入图片描述

    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>复杂表头+select下拉框默认值+单元格合并</title>
        <script src="../../../static/lib/layui/layui.js" charset="utf-8"></script>
        <style>
            /*select下拉框显示*/
            td[data-field="qualityStatus"]>div {
                overflow: inherit;
            }
            /*去除行点击、hover背景色*/
            .layui-table tbody tr:hover , .layui-table-hover , .layui-table-click{
                background-color: rgba(255,255,255,0);
            }
        </style>
    </head>
    <body>
        <div class="layui-fluid">
            <div class="layui-row layui-col-space15">
                <div class="layui-col-md12">
                    <div class="layui-card">
                        <div class="layui-card-body">
                            <table class="layui-hide" id="qua_standard_table" lay-filter="qua_standard_table"></table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </body>
    <script>
        layui.use(['element','upload','laydate','table','form'], function(){
            var element = layui.element
                ,table = layui.table,
                laypage = layui.laypage,
                form = layui.form,
                upload = layui.upload,
                laydate = layui.laydate;
            //模拟数据
            var selectdata = [
                {
                    amountOfMoney: 5000,
                    basicMeasurement: "kg",
                    certificateNumber: "内容15",
                    concession: null,
                    contentId: null,
                    disqualification: null,
                    explains: "内容67",
                    files: null,
                    id: 2,
                    inspectContent: "内容22",
                    inspectContentNumber: "内容0000",
                    inspector: null,
                    inspectorDate: null,
                    inspectorDescription: null,
                    isNumber: null,
                    main: null,
                    manufacturer: "内容44",
                    materialId: 14,
                    materialName: "内容4",
                    materialNo: "内容一",
                    materialSize: "2*3",
                    meId: null,
                    orderAmount: 100,
                    orderId: 1,
                    orderPrice: 20,
                    orderedInAmount: 0,
                    projectName: "其他",
                    purchaseMeasurement: "kg",
                    qualified: null,
                    qualityStatus: null,
                    qualityTestedInAmount: 9,
                    standard: "内容11",
                    undetectedCount: 91,
                },
                {
                    amountOfMoney: 5000,
                    basicMeasurement: "kg",
                    certificateNumber: "内容14",
                    concession: null,
                    contentId: null,
                    disqualification: null,
                    explains: "内容66",
                    files: null,
                    id: 2,
                    inspectContent: "内容33",
                    inspectContentNumber: "内容1111",
                    inspector: null,
                    inspectorDate: null,
                    inspectorDescription: null,
                    isNumber: null,
                    main: null,
                    manufacturer: "内容44",
                    materialId: 14,
                    materialName: "内容4",
                    materialNo: "内容一",
                    materialSize: "2*3",
                    meId: null,
                    orderAmount: 100,
                    orderId: 1,
                    orderPrice: 20,
                    orderedInAmount: 0,
                    projectName: "其他",
                    purchaseMeasurement: "kg",
                    qualified: null,
                    qualityStatus: null,
                    qualityTestedInAmount: 9,
                    standard: "内容001",
                    undetectedCount: 91,
                },
                {
                    amountOfMoney: 5000,
                    basicMeasurement: "kg",
                    certificateNumber: "内容15",
                    concession: null,
                    contentId: null,
                    disqualification: null,
                    explains: "内容67",
                    files: null,
                    id: 1,
                    inspectContent: "内容22",
                    inspectContentNumber: "内容0000",
                    inspector: null,
                    inspectorDate: null,
                    inspectorDescription: null,
                    isNumber: null,
                    main: null,
                    manufacturer: "内容45",
                    materialId: 13,
                    materialName: "内容3",
                    materialNo: "内容二",
                    materialSize: "2*3",
                    meId: null,
                    orderAmount: 100,
                    orderId: 1,
                    orderPrice: 20,
                    orderedInAmount: 0,
                    projectName: "其他",
                    purchaseMeasurement: "kg",
                    qualified: null,
                    qualityStatus: null,
                    qualityTestedInAmount: 12,
                    standard: "内容11",
                    undetectedCount: 88,
                },
                {
                    amountOfMoney: 5000,
                    basicMeasurement: "kg",
                    certificateNumber: "内容14",
                    concession: null,
                    contentId: null,
                    disqualification: null,
                    explains: "内容66",
                    files: null,
                    id: 1,
                    inspectContent: "内容33",
                    inspectContentNumber: "内容1111",
                    inspector: null,
                    inspectorDate: null,
                    inspectorDescription: null,
                    isNumber: null,
                    main: null,
                    manufacturer: "内容45",
                    materialId: 13,
                    materialName: "内容3",
                    materialNo: "内容二",
                    materialSize: "2*3",
                    meId: null,
                    orderAmount: 100,
                    orderId: 1,
                    orderPrice: 20,
                    orderedInAmount: 0,
                    projectName: "其他",
                    purchaseMeasurement: "kg",
                    qualified: null,
                    qualityStatus: null,
                    qualityTestedInAmount: 12,
                    standard: "内容001",
                    undetectedCount: 88,
                }
            ]
    
            //主页面数据
            table.render({
                elem: '#qua_standard_table',
                id:'qua_standard_table',
                //url:'',
                data:selectdata,
                method:'POST',
                title: '数据表',
                height: 'full-60',
                cellMinWidth: 120,
                size: 'lg',
                cols:[[
                    {align:'center',rowspan:2, title:'序号',width:100,type:'numbers',field: 'num',fixed:'left'},
                    {align:'center',rowspan:2,field: 'id', title: 'ID',hide:true},//隐藏列
                    {align:'center',rowspan:2,field:'materialNo', title:'表头',width:100},
                    {align:'center',rowspan:2,field:'materialName', title:'表头'},
                    {align:'center',rowspan:2,field:'projectName', title:'表头'},
                    {align:'center',rowspan:2,field:'purchaseMeasurement', title:'表头'},
                    {align:'center',rowspan:2,field:'inspectContentNumber', title:'表头'},
                    {align:'center',rowspan:2,field:'inspectContent', title:'表头'},
                    {align:'center',rowspan:2,field:'standard', title:'表头'},
                    {align:'center',rowspan:2,field:'explains', title:'表头'},
                    {align:'center',rowspan:2,field:'qualityStatus', title:'结果',templet: function (d) {
                        if(d.qualityStatus == null){
                            return '<select name="qualityStatus" lay-filter="testSelect" lay-verify="required" >' +
                                '        <option value="0" selected>合格</option>' +
                                '        <option value="1">不合格</option>' +
                                '      </select>';
                        }
    
                    }},
                    {align:'center',rowspan:2,field:'certificateNumber', title:'表头',templet: function (d) {
                        if(d.certificateNumber==null || d.certificateNumber==''){
                            return '无'
                        }else{
                            return d.certificateNumber
                        }
                    }},
                    {align:'center',rowspan:2,field:'undetectedCount', title:'表头'},
                    {align:'center',field:'resultNum', title:'表头',colspan:3},
                    {align:'center',rowspan:2,field:'qualityTestedInAmount', title:'表头'},
                    {align:'center',rowspan:2,field:'manufacturer', title:'表头'},
                    {align:'center',rowspan:2,field:'certificate', title:'表头', templet: function (d) {
                        return '<span class="layui-breadcrumb" lay-separator="|">' +
                            '<a href="javascript:;" lay-event="qua_standard_edit">添加附件</a>' +
                            '</span>';
                    },fixed:'right'},
                    {align:'center',rowspan:2,width:100,field: 'operation', title: '操作', templet: function (d) {
                        return '<span class="layui-breadcrumb" lay-separator="|">' +
                            '<a href="javascript:;" lay-event="qua_standard_del">删除</a>' +
                            '</span>';
                    },fixed:'right'}
                ],
                    [
                        {align:'center',field:'qualified', title:'表头', edit: 'number'},
                        {align:'center',field:'disqualification', title:'表头', edit: 'number'},
                        {align:'center',field:'concession', title:'表头', edit: 'number'},
                    ]
                ],
                done: function (res, curr, count) {
                    element.init();
                    $('#qua_standard_table').siblings('div').find('dl').find('.layui-this').click();//模拟点击 初始化数据
                    merge(res);//合并单元格
                }
            });
    
            $('#receiptForm1').on('keyup','.layui-table-edit',function () {
                this.value=this.value.replace(/^(0+)|[^\d]+/g,'')//TODO
            });
    
            //行事件
            table.on('tool(qua_standard_table)', function(obj){
    
                if(obj.event === 'qua_standard_edit'){
                    layer.msg('添加')
                }else if(obj.event === 'qua_standard_del'){
                    layer.msg('删除')
                }
            });
    
        });
        //layui 结束
    
        //合并开始
        function merge(res) {
            var data = res.data;
            var mergeIndex = 0;//定位需要添加合并属性的行数
            var mark = 1; //这里涉及到简单的运算,mark是计算每次需要合并的格子数
            var _number = 1;//保持序号列数字递增
            var columsName = ['num','number','typeName','projectName','unit','suppliedNum','qualified','disqualification','concession','sum','manufacturer','certificate','operation'];//需要合并的列名称
            var columsIndex = [0,2,3,4,5,12,13,14,15,16,17,18,19];//需要合并的列索引值
            var mergeCondition = 'id';//需要合并的 首要条件  在这个前提下进行内容相同的合并
            var tdArrL = $('.layui-table-fixed-l > .layui-table-body').find("tr");//序号列左定位产生的table tr
            var tdArrR = $('.layui-table-fixed-r > .layui-table-body').find("tr");//操作列定右位产生的table tr
    
            for (var k = 0; k < columsName.length; k++) { //这里循环所有要合并的列
                var trArr = $(".layui-table-main>.layui-table").find("tr");//所有行
                for (var i = 1; i < res.data.length; i++) { //这里循环表格当前的数据
    
                    if (data[i][mergeCondition] === data[i-1][mergeCondition]) {
                        var tdCurArr = trArr.eq(i).find("td").eq(columsIndex[k]);//获取当前行的当前列
                        var tdPreArr = trArr.eq(mergeIndex).find("td").eq(columsIndex[k]);//获取相同列的第一列
    
                        if (data[i][columsName[k]] === data[i-1][columsName[k]]) { //后一行的值与前一行的值做比较,相同就需要合并
                            mark += 1;
                            tdPreArr.each(function () {//相同列的第一列增加rowspan属性
                                $(this).attr("rowspan", mark);
                            });
                            tdCurArr.each(function () {//当前行隐藏
                                $(this).css("display", "none");
                            });
                        }else {
                            mergeIndex = i;
                            mark = 1;//一旦前后两行的值不一样了,那么需要合并的格子数mark就需要重新计算
                        }
                    } else {
                        mergeIndex = i;
                        mark = 1;//一旦前后两行的值不一样了,那么需要合并的格子数mark就需要重新计算
                    }
    
    
                }
                mergeIndex = 0;
                mark = 1;
            }
    
    
    
    
    
            //操作左右定位列的表格
            $.each($("#qua_standard_table").siblings('.layui-table-view').find('.layui-table-main>.layui-table').find("tr"),function (i,v) {
                if ($(v).find('td').eq(2).css('display') === 'none') {
                    tdArrL.eq(i).find('td').css('display','none');
                    tdArrR.eq(i).find('td').css('display','none');
                } else {
                    tdArrL.eq(i).find('td').find('.laytable-cell-numbers').html(_number++);
                    tdArrL.eq(i).find('td').css('height',$(v).find('td').eq(2)[0].clientHeight);
                    tdArrR.eq(i).find('td').css('height',$(v).find('td').eq(2)[0].clientHeight);
    
                }
            })
    
    
    
        }
        //合并结束
    </script>
    </html>
    
    展开全文
  • 使用easy excel导出复杂表头的excel 今天想写一个双层表头的excel导出,一开始使用的是poi来画发现太麻烦, 于是就想到了使用easy-excel的模板填充来实现,将导出写成了一个简单的工具类, 供参考 最终是要实现为这样...
  • <template> <div> <el-table:data="tableData":span-method="arraySpanMethod"style="width:100%;margin-top:20px" ...:header-cell-style="headerStyle">...el-table-columnprop="id"label="123"ref=...
  • lowagie.text 导出word表格带复杂表头的内容 使用lowagie.text 导出word表格带多表头的时候怎么处理呢? 其实蛮简单的,知道rowspan和colspan是什么意思就好操作了。 同理设置表格标题的时候也是一样。 ...
  • 100 }101 102 /**103 * 此方法生成表头并写入表头名称104 *105 * @param nodes 节点106 * @param sheet 工作簿107 * @param style 单元格样式108 * @return 数据加载开始行109 */ 110 public static int ...
  • Java 根据JSON串生成复杂表头 (使用Apache POI 工具)
  • 表头 cols: [ [ { field: "category", title: "类别", align: "center", rowspan: 2 }, { field: "code", title: "代码", align: "center", rowspan: 2 }, { field: "name", title: "名称", align: "center", ...
  • import java.io.File; import java.io.FileOutputStream;... c、创建iframe,iframe的src可以是文件地址url来直接下载文件 (2)导出文件名称可能会出现中文乱码,需要转码, (3)注意表头索引位置
  • layui复杂表头数据excel纯前端导出 前言: 最近需要完成一个layui复杂表头的前端导出的功能,尝试过js-xlsx,但普通版不能设置表格样式,也尝试过各位大佬魔改版,但似乎都未能解决样式问题,遗憾放弃,最终在尝试...
  • 最近项目里用到复杂表头excel导出。复杂表头,一般就是有很多合并单元格了,使用POI直接去写的话呢,只需要操作合并单元格,写入合并单元格数据就好了。但是写代码的方式定制表头,太啰嗦,列多了,表头复杂了,写的...
  • easypoi获取复杂表头excel

    千次阅读 2021-12-02 17:24:10
    引入poi poi-ooxml 4.1.2 一.将表格拆作四部分处理 line3为第一部分 line5-9为第二部分 line12-15为第三部分 line16以后的为第四部分 二、获取文件流,使用ExcelImportUtil.importExcel()读取文件,由于一个...
  • 快速实现基于easypoi实现复杂表头的导入和导出 先在项目的pom文件中加入easypoi的依赖 cn.afterturn easypoi-web 3.2.0 先从导入下手, 这个类是vo类 @Data public class PotentialVehicleExcelDto { @Excel(name ...
  • 1. elementUI 实现复杂表格 表头由多行多列组成, 左侧和右侧部分列固定, 中间部分为动态列 最终效果图 1.1 表格实现 HtML 内容 <el-table :data="tableData" style="width: 100%" ref="tableRef" :...
  • 2,项目需求显示为如下图所示,故涉及复杂表头的处理和显示 3,本篇文章主要以复杂表头的处理讲解为主,简单表头导入导出均引入xlsx之类的插件即可使用 循环对应产品属性的参与者信息并且标记填充展示 功能分析:...
  • php 导出复杂表头表头格式错乱$Head = array(array('value' => $year."年省直单位申购车辆申请呈批表",'col' => 19,'row' => 3,'children' =>array(array('value' => $date,'col' => 4,'row' =>...
  • 在开源项目华夏ERP的基础上进行开发,华夏ERP使用的的easyui框架,下面主要记录导出excel表头的信息和表格底部的信息的处理。我的项目中表头表尾的信息都是用div标签包裹的,主要是单据编号、日期、联系方式等信息。...
  • VUE实现复杂表头导出Excle的实现,(已测试成功) 我的项目前端是vuex加element实现的,里面有导出的示例, 不过只有一级和二级,可拓展三四级表头具体看自己业务需求 如下示例,先布局好,此处参照项目的示例即可, ...
  • 效果如上,代码如下 layui.extend({ excel:basePath+'/js/layui_exts/excel' }); layui.use(['excel'],function(){ var excel = layui.excel;... {id: 'ID', username: 'wang0', age: '', sex: '', score: '100',...
  • 本文旨在解决使用Easy-POI导入复杂表头(导出)时,遇上的EXCEL文件中的字段无法正常映射到Bean的问题,官方的示例和其他的CSDN文章基本上没有很详细的教程,于是自己写一个 这是需要导入的表结构: 这是结构中需要在...
  • Document姓名一月二月收入支出收入支出张三10元20元15元25元李四100元200元150元250元导出excelfunctiontableToExcel(){//要导出的数据varexportData=[{name:'张三',month1: {income:'10元',outlay:'20元'},month2: ...
  • 前端Vue导入复杂表头、导出复杂表头 一般情况下都是有我们的后端来操作这个文件的读写,但总有特殊情况嘛,就比如我公司后台人员配置较少,没时间…,于是我就自己开始鼓捣了,先展示一下成果,上图: 表格导入效果(需要...
  • 三层以上复杂表头会出现数据串行问题,如图1会出现串行问题 解决方案 靠上rowspan,不要靠下rowspan,这样合并数据就不会串行啦
  • #### 1. 数据表格接口数据---```json{"code": 0,"msg": "用户信息","count": 5,"data":[{"id": 1,"name": "张三","sex": "女","prov": "河南","city": "郑州市","dist": "金水区"},{"id": 2,"name": "李四","sex": ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 34,935
精华内容 13,974
关键字:

复杂表头