POI导出Execle前后台实例

刺骨的言语ヽ痛彻心扉 2022-09-27 15:24 325阅读 0赞

1、后台代码

@SuppressWarnings({ "rawtypes", "resource" })
    	@RequestMapping(value = "/exportLinkAnalysis.do", method = RequestMethod.POST)
    	public void exportLinkAnalysis(LinkAnalysis linkAnalysis,HttpServletRequest request, HttpServletResponse response) {
    	    try {
    	    response.setCharacterEncoding("UTF-8");
    	    //创建workbook   
    	    HSSFWorkbook workbook = new HSSFWorkbook();   
    	    //创建sheet页  
    	    HSSFSheet sheet = workbook.createSheet("同环比分析");  
    	    //设置列宽
    	    sheet.setDefaultColumnWidth(15);
    	    HSSFCellStyle style = workbook.createCellStyle(); // 样式对象      
                style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直      
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平   
    	        
                //查询参数获取
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    			SimpleDateFormat sdf2 = new SimpleDateFormat(
    					"yyyy-MM-dd HH:mm:ss");
    			String timeStr = sdf.format(linkAnalysis.getBeginTime())
    					+ " 00:00:00";
    			linkAnalysis.setBeginTime(sdf2.parse(timeStr));
    			String timeStr2 = sdf.format(linkAnalysis.getEndTime())
    					+ " 23:59:59";
    			linkAnalysis.setEndTime(sdf2.parse(timeStr2));
    
    			List<Map> m = linkAnalysisService.queryPage(linkAnalysis);
    			
    			//插入表头
    			//创建单元格  
    			//设定表格第一行	
    	        HSSFRow row = sheet.createRow(0); 
    	        HSSFCell c0 = row.createCell(0); 
    	        c0.setCellValue(new HSSFRichTextString("部门"));  
    	        HSSFCell c1 = row.createCell(1); 
    	        c1.setCellValue(new HSSFRichTextString("水表总数")); 
    	        HSSFCell c2 = row.createCell(2); 
    	        c2.setCellValue(new HSSFRichTextString("总水量（T）")); 
    	        HSSFCell c3 = row.createCell(3); 
    	        c3.setCellValue(new HSSFRichTextString("同比递增")); 	        
    	        HSSFCell c4 = row.createCell(7); 
    	        c4.setCellValue(new HSSFRichTextString("同比递减")); 
    	        HSSFCell c5 = row.createCell(11); 
    	        c5.setCellValue(new HSSFRichTextString("环比递增")); 
    	        HSSFCell c6 = row.createCell(15); 
    	        c6.setCellValue(new HSSFRichTextString("环比递减")); 
    	        //设置单元格样式
    	        c3.setCellStyle(style);
    	        c4.setCellStyle(style);
    	        c5.setCellStyle(style);
    	        c6.setCellStyle(style);
    	        
    	        //设定表格第二行	
    	        HSSFRow row1 = sheet.createRow(1); 
    	        for(int i = 0; i < 4; i ++){
    	        	HSSFCell c7 = row1.createCell(i*4+3); 
    		        c7.setCellValue(new HSSFRichTextString("30%"));
    		        c7.setCellStyle(style);
    	        }
    	        for(int i = 0; i < 4; i ++){
    	        	HSSFCell c8 = row1.createCell(i*4+5); 
    		        c8.setCellValue(new HSSFRichTextString("30%以上")); 
    		        c8.setCellStyle(style);
    	        }
    
    	        //设定表格第三行	
    	        HSSFRow row2 = sheet.createRow(2);
    	        for(int i = 0; i < 8; i ++){
    	        	HSSFCell c9 = row2.createCell(i*2+3); 
    	        	c9.setCellValue(new HSSFRichTextString("水表数量")); 
    	        }
    	        for(int i = 0; i < 8; i ++){
    	        	HSSFCell c9 = row2.createCell(i*2+4); 
    	        	c9.setCellValue(new HSSFRichTextString("水量(T)")); 
    	        }
    	        
    	        //第一行的位置格式，firstRow, lastRow, firstCol, lastCol
    	        CellRangeAddress region1 = new CellRangeAddress(0, 2, 0, 0);   
    	        CellRangeAddress region2 = new CellRangeAddress(0, 2, 1, 1);
    	        CellRangeAddress region3 = new CellRangeAddress(0, 2, 2, 2);
    	        CellRangeAddress region4 = new CellRangeAddress(0, 0, 3, 6);
    	        CellRangeAddress region5 = new CellRangeAddress(0, 0, 7, 10);
    	        CellRangeAddress region6 = new CellRangeAddress(0, 0, 11, 14);
    	        CellRangeAddress region7 = new CellRangeAddress(0, 0, 15, 18);
    	        //第二行的位置格式
    	        CellRangeAddress region8 = new CellRangeAddress(1, 1, 3, 4);
    	        CellRangeAddress region9 = new CellRangeAddress(1, 1, 5, 6);
    	        CellRangeAddress region10 = new CellRangeAddress(1, 1, 7, 8);
    	        CellRangeAddress region11 = new CellRangeAddress(1, 1, 9, 10);
    	        CellRangeAddress region12 = new CellRangeAddress(1, 1, 11, 12);
    	        CellRangeAddress region13 = new CellRangeAddress(1, 1, 13, 14);
    	        CellRangeAddress region14 = new CellRangeAddress(1, 1, 15, 16);
    	        CellRangeAddress region15 = new CellRangeAddress(1, 1, 17, 18);
    
    	        sheet.addMergedRegion(region1);  //调用格式 
    	        sheet.addMergedRegion(region2);  //调用格式 
    	        sheet.addMergedRegion(region3);  //调用格式 
    	        sheet.addMergedRegion(region4);  //调用格式
    	        sheet.addMergedRegion(region5);  //调用格式 
    	        sheet.addMergedRegion(region6);  //调用格式
    	        sheet.addMergedRegion(region7);  //调用格式 
    	        sheet.addMergedRegion(region8);  //调用格式 
    	        sheet.addMergedRegion(region9);  //调用格式 
    	        sheet.addMergedRegion(region10);  //调用格式 
    	        sheet.addMergedRegion(region11);  //调用格式
    	        sheet.addMergedRegion(region12);  //调用格式 
    	        sheet.addMergedRegion(region13);  //调用格式
    	        sheet.addMergedRegion(region14);  //调用格式 
    	        sheet.addMergedRegion(region15);  //调用格式 
    	        
    		        
    	        //插入数据
    	        for(int k = 0; k < m.size(); k++){
    		        HSSFRow row3 = sheet.createRow(3+k);//设定第三开始行
    		        HSSFCell c10 = row3.createCell(0); 
    		        c10.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("areaName"))));
    			    HSSFCell c11 = row3.createCell(1); 
    			    c11.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("meterTotal"))));
    			    HSSFCell c12 = row3.createCell(2); 
    			    c12.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("waterTotal"))));
    			    
    			    HSSFCell c13 = row3.createCell(3); 
    			    c13.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Ynum0"))));
    			    HSSFCell c14 = row3.createCell(4); 
    			    c14.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("YweterTotal0"))));
    			    HSSFCell c15 = row3.createCell(5); 
    			    c15.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Ynum1"))));		    
    			    HSSFCell c16 = row3.createCell(6); 
    			    c16.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("YweterTotal1"))));
    			    HSSFCell c17 = row3.createCell(7); 
    			    c17.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Ynum2"))));
    			    HSSFCell c18 = row3.createCell(8); 
    			    c18.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("YweterTotal2"))));
    			    HSSFCell c19 = row3.createCell(9); 
    			    c19.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Ynum3"))));
    			    HSSFCell c20 = row3.createCell(10); 
    			    c20.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("YweterTotal3"))));
    			    HSSFCell c21 = row3.createCell(11); 
    			    c21.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Mnum0"))));
    			    HSSFCell c22 = row3.createCell(12); 
    			    c22.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("MweterTotal0"))));
    			    HSSFCell c23 = row3.createCell(13); 
    			    c23.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Mnum1"))));
    			    HSSFCell c24 = row3.createCell(14); 
    			    c24.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("MweterTotal1"))));
    			    HSSFCell c25 = row3.createCell(15); 
    			    c25.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Mnum2"))));
    			    HSSFCell c26 = row3.createCell(16); 
    			    c26.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("MweterTotal2"))));
    			    HSSFCell c27 = row3.createCell(17); 
    			    c27.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Mnum3"))));
    			    HSSFCell c28 = row3.createCell(18); 
    			    c28.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("MweterTotal3"))));
    		      
    	        }
    	        String titleName="同环比分析";
    	        String fileName =titleName+DateUtil.generateStandardDateTimeFormat2(new Date()) + ".xls";
    	        response.reset();    
    	        response.setContentType("application/x-msdownload");    
    	        response.setHeader("Content-Disposition","attachment; filename="+java.net.URLEncoder.encode(fileName, "UTF-8"));    
    	        ServletOutputStream outStream=null;    
    	       
    	        try{    
    	            outStream = response.getOutputStream();    
    	            workbook.write(outStream);    
    	        }catch(Exception e)    
    	        {    
    	         e.printStackTrace();    
    	        }finally{    
    	            outStream.close();    
    	        }    
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
        
        }
    		
    	/**
    	 * 
    	 * @Title: exportLinkAnalysisDetail 
    	 * @Description: 同环比分析详情导出
    	 * @return void
    	 * @author Long jun
    	 * @date 2017年3月2日 上午11:46:27
    	 */
    	@SuppressWarnings({ "rawtypes", "resource", "deprecation" })
    	@RequestMapping(value = "/exportLinkAnalysisDetail.do", method = RequestMethod.POST)
    	public void exportLinkAnalysisDetail(LinkAnalysis linkAnalysis,HttpServletRequest request, HttpServletResponse response) {
    		try {
    			response.setCharacterEncoding("UTF-8");
    			 //创建workbook   
    	        HSSFWorkbook workbook = new HSSFWorkbook();   
    	        //创建sheet页  
    	        HSSFSheet sheet = workbook.createSheet("同环比分析详情");  
    	        sheet.setDefaultColumnWidth(15);
    	        HSSFCellStyle style = workbook.createCellStyle(); // 样式对象      
                style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直      
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平   
    	        
                //查询参数获取
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    			SimpleDateFormat sdf2 = new SimpleDateFormat(
    					"yyyy-MM-dd HH:mm:ss");
    			String beginTime = sdf.format(linkAnalysis.getBeginTime());
    			String timeStr = beginTime + " 00:00:00";
    			linkAnalysis.setBeginTime(sdf2.parse(timeStr));
    			String endTime = sdf.format(linkAnalysis.getEndTime());
    			String timeStr2 = endTime + " 23:59:59";
    			linkAnalysis.setEndTime(sdf2.parse(timeStr2));
    			
    			
    			List<Map> m = new ArrayList<Map>();
    			if(linkAnalysis.getQueryId() == null || linkAnalysis.getQueryId().length() < 2){
    				m = linkAnalysisService.queryDetailPage(linkAnalysis);
    			}else if(linkAnalysis.getQueryId().equals("Mnum0")||linkAnalysis.getQueryId().equals("Mnum1")||linkAnalysis.getQueryId().equals("Mnum2")||linkAnalysis.getQueryId().equals("Mnum3")){
    				m = linkAnalysisService.queryAnMeter(linkAnalysis);
    			}else{
    				m = linkAnalysisService.queryMomMeter(linkAnalysis);
    			}
    			
    			//获取上年同比的开始时间和结束时间
    			String beginTime1 = Utils.getSpecifiedDateBefore(timeStr, 1, "year");
    			String endTime1 = Utils.getSpecifiedDateBefore(timeStr2, 1, "year");
    			//间隔天数
    			int differentDays = Utils.differentDays(sdf2.parse(timeStr), sdf2.parse(timeStr2));
    			//获取上期环比的开始时间和结束时间
    			String beginTime2 = Utils.getSpecifiedDateBefore(timeStr,differentDays,"day");
    			String endTime2 = beginTime;
    			
    			//插入表头
    			//创建单元格  
    			//设定表格第一行	
    	        HSSFRow row = sheet.createRow(0); 
    	        HSSFCell c0 = row.createCell(0); 
    	        c0.setCellValue(new HSSFRichTextString("水表编号"));  
    	        HSSFCell c1 = row.createCell(1); 
    	        c1.setCellValue(new HSSFRichTextString("水表名称")); 
    	        HSSFCell c2 = row.createCell(2); 
    	        c2.setCellValue(new HSSFRichTextString("本期水量（T）")); 
    	        HSSFCell c3 = row.createCell(3); 
    	        c3.setCellValue(new HSSFRichTextString("上年同比"+beginTime1+"~"+endTime1)); 	        
    	        HSSFCell c4 = row.createCell(6); 
    	        c4.setCellValue(new HSSFRichTextString("上期环比"+beginTime2+"~"+endTime2)); 
    	        
    	        //设置单元格样式
    	        c3.setCellStyle(style);
    	        c4.setCellStyle(style);
    	        
    	        //设定表格第二行	
    	        HSSFRow row1 = sheet.createRow(1); 
    	        HSSFCell c5 = row1.createCell(3); 
            	c5.setCellValue(new HSSFRichTextString("同比水量(T)"));
            	
    	        for(int i = 0; i < 2; i ++){
    	        	HSSFCell c6 = row1.createCell(i*3+4); 
    	        	c6.setCellValue(new HSSFRichTextString("正负(T)")); 
    	        }
    	        for(int i = 0; i < 2; i ++){
    	        	HSSFCell c7 = row1.createCell(i*3+5); 
    	        	c7.setCellValue(new HSSFRichTextString("比例(%)")); 
    	        }
    	        HSSFCell c8 = row1.createCell(6); 
    	        c8.setCellValue(new HSSFRichTextString("环比水量(T)"));
    	        
    	        
    	        //第一行的位置格式
    	        Region region1 = new Region(0, (short)0, 1, (short)0);   
    	        Region region2 = new Region(0, (short)1, 1, (short)1);
    	        Region region3 = new Region(0, (short)2, 1, (short)2);        
    	        Region region4 = new Region(0, (short)3, 0, (short)5);
    	        Region region5 = new Region(0, (short)6, 0, (short)8);
    
    	        sheet.addMergedRegion(region1);  //调用格式 
    	        sheet.addMergedRegion(region2);  //调用格式 
    	        sheet.addMergedRegion(region3);  //调用格式 
    	        sheet.addMergedRegion(region4);  //调用格式
    	        sheet.addMergedRegion(region5);  //调用格式 
    		        
    	        //插入数据
    	        for(int k = 0; k < m.size(); k++){
    		        HSSFRow row3 = sheet.createRow(2+k);//设定第三开始行
    		        HSSFCell c10 = row3.createCell(0); 
    		        c10.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("meterCode"))));
    			    HSSFCell c11 = row3.createCell(1); 
    			    c11.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("meterName"))));
    			    HSSFCell c12 = row3.createCell(2); 
    			    c12.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("waterTotal"))));
    			    
    			    HSSFCell c13 = row3.createCell(3); 
    			    c13.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("waterTotalY"))));
    			    HSSFCell c14 = row3.createCell(4); 
    			    c14.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("zf1"))));
    			    HSSFCell c15 = row3.createCell(5); 
    			    c15.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("rateY"))));	
    			    
    			    HSSFCell c16 = row3.createCell(6); 
    			    c16.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("waterTotalM"))));
    			    HSSFCell c17 = row3.createCell(7); 
    			    c17.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("zf2"))));
    			    HSSFCell c18 = row3.createCell(8);  
    			    c18.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("rateM"))));
    			   
    		      
    	        }
    	        String titleName="同环比分析详情";
    	        String fileName =titleName+DateUtil.generateStandardDateTimeFormat2(new Date()) + ".xls";
    	        response.reset();    
    	        response.setContentType("application/x-msdownload");    
    	        response.setHeader("Content-Disposition","attachment; filename="+java.net.URLEncoder.encode(fileName, "UTF-8"));    
    	        ServletOutputStream outStream=null;    
    	       
    	        try{    
    	            outStream = response.getOutputStream();    
    	            workbook.write(outStream);    
    	        }catch(Exception e)    
    	        {    
    	         e.printStackTrace();    
    	        }finally{    
    	            outStream.close();    
    	        }    
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
        
        }

2、前台代码

//导出同环比分析
    function linkAnalysisExport(){
        //设置高级查询条件
        var param = $('#grid_linkAnalysis').datagrid('options').queryParams;
    
        var form = $("<form method='post'></form>");
        form.attr("action",getRootPath()+"/runindex/linkAnalysis/exportLinkAnalysis.do");
        $.each(param,function(key,value){
                var attr=null;
                if(value){
                    attr = $("<input>").attr("type","hidden").attr("name",key).attr("value",value);
                }else{
                    attr = $("<input>").attr("type","hidden").attr("name",key).attr("value","");
                }
                if(attr!=null){
                    form.append(attr);
                }
        });
        form.appendTo("body").submit();
    }

3、导出效果

![Center][]

![Center 1][]

[Center]: /images/20220708/5f58f679cfb94745940563a513dceb37.png
[Center 1]: /images/20220708/4d6797f47a5249b5915a9bd1376423d9.png