利用XSSFWorkbook 来生成.xlsx类型的Excel表格

左手的ㄟ右手 2022-05-29 22:40 375阅读 0赞

以下是一个demo：

@RequestMapping("/downExample")
    	public void downExample(HttpServletResponse response,HttpServletRequest request){  
        	String advProJson = request.getParameter("advProJson");
        	List<AdvProEntity> advProList = JsonChangeUtil.jsonToList(advProJson, AdvProEntity.class);   	
    		XSSFWorkbook wb = new XSSFWorkbook();
    		XSSFSheet sheet = wb.createSheet("报表");
    		XSSFCellStyle style = wb.createCellStyle();
    		//设置style---cell中水平的对齐方式
    		style.setAlignment(HorizontalAlignment.CENTER);
    		//设置style---cell中垂直方向的对齐方式
    		style.setVerticalAlignment(VerticalAlignment.CENTER);
    		//合并单元格  参数说明：1：开始行 2：结束行  3：开始列 4：结束列  
    		sheet.addMergedRegion(new CellRangeAddress(0,0,0,8));
    		sheet.addMergedRegion(new CellRangeAddress(1,2,8,8));
    		XSSFRow row = sheet.createRow(0);
    		XSSFCell cell = row.createCell(0);
    		cell.setCellValue("报表报告");
    		cell.setCellStyle(style);
    		row = sheet.createRow(1);
    		for(int i=0;i<9;i++){
    			//设置列宽
    			sheet.setColumnWidth(i, 4000);
    			//设置行高
    			//row.setHeight((short)1000);
    			if(i==0){
    				cell = row.createCell(0);
    				cell.setCellValue("日期");
    				cell.setCellStyle(style);
    			}else if(i==8){
    				cell = row.createCell(i);
    				cell.setCellValue("TOTAL");
    				cell.setCellStyle(style);
    			}else{				
    	            Calendar calendar = Calendar.getInstance();  
    	            calendar.setTime(new Date());
    	            calendar.add(Calendar.DAY_OF_MONTH, -6+i-1);            
    	            String date = DateFormatUtils.format(calendar.getTime(), "yyyy-MM-dd");
    				cell = row.createCell(i);
    				cell.setCellValue(date);
    				cell.setCellStyle(style);
    			}
    		}
    		row = sheet.createRow(2);
    		for(int i=0;i<9;i++){
    			if(i==0){
    				cell = row.createCell(i);
    				cell.setCellValue("时段");
    				cell.setCellStyle(style);
    			}else{
    				cell = row.createCell(i);
    				cell.setCellValue("数据");
    				cell.setCellStyle(style);
    			}
    		}
    		
    		for(int k=0;k<24;k++){
    			row = sheet.createRow(2+1+k);
    			cell = row.createCell(0);
    			cell.setCellValue(String.valueOf(k)+":00-"+String.valueOf(k)+":59");
    			cell.setCellStyle(style);
    			for(int g=0;g<8;g++){
    				cell = row.createCell(1+g);
    				cell.setCellValue(advProList.get(k).getMap().get(String.valueOf(g)));
    				cell.setCellStyle(style);
    			}	
    		}
    		
    		try {
    			response.setHeader("content-Disposition","attachment;filename="
    					+ URLEncoder.encode("报表.xlsx","utf-8"));
    			OutputStream out = response.getOutputStream();
    			wb.write(out);
    			out.close();
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}