Java实现Excel数据导出

 

最近项目中多次用到Excel导出功能，之前的也频繁的使用到导入导出功能。于是决定整理一番，给新入坑的小伙伴示范一下。一入java深似海，从此头皮越来越凉，最近剪头发，Tony老师说，小伙子有秃顶的趋势啊，要不要来个头发护理套餐........迎娶白富美，走上人生巅峰，是越来越没有盼头了。

不多哔哔了，上菜。

首先jar包准备好

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.9</version>
</dependency>

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.9</version>
  <type>pom</type>
</dependency>

前端导出按钮

<button class="TimeSearchbtn Commombtn" onClick="exportExcel()">导出EXCEL</button>

 

请求后台跳转方法

function exportExcel (){
	var start = $("#startTime").val();
	var end = $("#endTime").val();
	var timeType = GLOAB_TIME_TYPE;
	window.location.href="exportData"+Num+"?    start="+start+"&end="+end+"&timeType="+timeType;
}

以上便是前端代码的全部内容了。

后台Controller接收前端请求部分

@RequestMapping(value = "/exportData2", method = RequestMethod.GET)
	public void exportData1(HttpServletResponse response,String start,String end,Integer timeType){
		try{
			String uniqueid = (String) getSession().getAttribute("uniqueid");
			reportServer.exportExcelForDeviceEnergy(response, timeType, uniqueid, start, end);
		}catch(Exception e){
			e.printStackTrace();
		}
	}

Service部分，获取数据源

public void exportExcelForDeviceEnergy(HttpServletResponse response,int timeType,String uniqueid,String start,String end) throws Exception{
		List<?> times = getTimes(timeType,start,end);
		List<?> list = getDevBenifit(timeType,start,end,uniqueid);
		ExcelUtils.exDevBenifit(list, times, response);
	}

接下来就是Excel导出的主要实现部分

public static void exDevBenifit(List<?> list,List<?> times,HttpServletResponse response){
	    	try{
	    		HSSFWorkbook workbook = new HSSFWorkbook();
		    	HSSFSheet sheet = workbook.createSheet("设备产出报表");
		    	sheet.setDefaultColumnWidth(13);
		    	HSSFCellStyle colStyle = createCellStyle(workbook,(short)10,true,true); 
		    	HSSFCellStyle cellStyle = createCellStyle(workbook,(short)10,false,true); 
		    	CellRangeAddress cell_serialNum = new CellRangeAddress(0,1,0,0);
		    	CellRangeAddress cell_sn = new CellRangeAddress(0,1,1,1);
		    	CellRangeAddress cell_devtype = new CellRangeAddress(0,1,2,2);
		    	CellRangeAddress cell_alias = new CellRangeAddress(0,1,3,3);
		    	sheet.addMergedRegion(cell_serialNum);
		    	sheet.addMergedRegion(cell_sn);
		    	sheet.addMergedRegion(cell_devtype);
		    	sheet.addMergedRegion(cell_alias);
		    	HSSFRow row0 = sheet.createRow(0);//第一行
		    	HSSFRow row1 = sheet.createRow(1);//第二行
		    	HSSFCell cell0 = row0.createCell(0);
		    	cell0.setCellStyle(colStyle);
		    	cell0.setCellValue("序号");
		    	HSSFCell cell1 = row0.createCell(1);
		    	cell1.setCellStyle(colStyle);
		    	cell1.setCellValue("设备序列号");
		    	HSSFCell cell2 = row0.createCell(2);
		    	cell2.setCellStyle(colStyle);
		    	cell2.setCellValue("设备类型");
		    	HSSFCell cell3 = row0.createCell(3);
		    	cell3.setCellStyle(colStyle);
		    	cell3.setCellValue("设备名称");
		    	for(int i=0;i<times.size();i++){
		    		String time = (String) times.get(i);
		    		sheet.addMergedRegion(new CellRangeAddress(0,0,i*3+4,i*3+6));
		    		HSSFCell cell_1 = row0.createCell(i*3+4);
		    		cell_1.setCellStyle(colStyle);
		    		cell_1.setCellValue(time);
		    		
		    	}
		    	for(int i=0;i<times.size()*3;i++){
		    		HSSFCell cell_2 = row1.createCell(i+4);
		    		cell_2.setCellStyle(colStyle);
		    		if(i%3==0){
		    			cell_2.setCellValue("发电量(kwh)");
		    		}else if(i%3==1){
		    			cell_2.setCellValue("CO2(kg)");
		    		}else if(i%3==2){
		    			cell_2.setCellValue("收益(元)");
		    		}
		    	}
		    	for(int i=0;i<list.size();i++){
		    		Map map = (Map) list.get(i);
		    		String sn = (String) map.get("sn");
		    		String devtype = (String) map.get("devType"); 
		    		String alias  = (String) map.get("alias");
		    		HSSFRow row = sheet.createRow(i+2);
		    		HSSFCell ce0 = row.createCell(0);
		    		ce0.setCellStyle(cellStyle);
		    		ce0.setCellValue(i+1);
		    		HSSFCell ce1 = row.createCell(1);
		    		ce1.setCellStyle(cellStyle);
		    		ce1.setCellValue(sn);
		    		HSSFCell ce2 = row.createCell(2);
		    		ce2.setCellStyle(cellStyle);
		    		ce2.setCellValue(devtype);
		    		HSSFCell ce3 = row.createCell(3);
		    		ce3.setCellStyle(cellStyle);
		    		ce3.setCellValue(alias);
		    		Map data = (Map) map.get("data");
		    		Iterator ite = data.keySet().iterator();
		    		int num = 1;
		    		while(ite.hasNext()){
		    			String key = (String) ite.next();
		    			Map temp = (Map) data.get(key);
		    			double co2 = (double) temp.get("co2");
		    			double energy = (double) temp.get("energy");
		    			double benifit = (double) temp.get("benifit");
		    			HSSFCell c0 = row.createCell(num*3+1);
		    			c0.setCellStyle(cellStyle);
		    			c0.setCellValue(co2);
		    			HSSFCell c1 = row.createCell(num*3+2);
		    			c1.setCellStyle(cellStyle);
		    			c1.setCellValue(benifit);
		    			HSSFCell c2 = row.createCell(num*3+3);
		    			c2.setCellStyle(cellStyle);
		    			c2.setCellValue(energy);
		    			num++;
		    		}
		    	}
	            String fileName = "设备产出报表.xls";
	            fileName = URLEncoder.encode(fileName, "UTF-8");  
	            response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");    
	            response.setContentType("application/vnd.ms-excel;charset=UTF-8");    
	            OutputStream out =  response.getOutputStream();  
	            workbook.write(out);    
	            out.flush();         
	            out.close(); 
	    	}catch(Exception e){
	    		e.printStackTrace();
	    	}
	    }

 下面单元格合并方法，这里拿出来解释一波，当初也是花了点时间才搞明白。 

new CellRangeAddress(0,1,0,0);创建一个合并单元格，四个参数分别对应需要合并单元格的起始行，结束行，起始列，结束列。行数和列数都是从0开始。然后把新创建的合并格子加入到当前表中就可以了。

比如说合并一个2*2单元格 ,写法是new CellRangeAddress（row,row+1,column,column+1）; 

CellRangeAddress cell_serialNum = new CellRangeAddress(0,1,0,0);
sheet.addMergedRegion(cell_serialNum);

 

表格格式设置方法

private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize,boolean flag,boolean flag1) {  
	        HSSFCellStyle style = workbook.createCellStyle();  
	        //是否居中？
	        if(flag1){  
	            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	        }  
	        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
	        //字号
	        HSSFFont font = workbook.createFont();  
	        //是否加粗？
	        if(flag){  
	            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
	        }  
	        font.setFontHeightInPoints(fontsize);  
	        //设置字号大小
	        style.setFont(font);  
	        return style;  
	    }

没找上传表格的地方截个图凑合下，手动滑稽

以上便是POI导出的全部内容，希望各位看官大老爷不吝赐教，再会。