java 导出excel

绝地灬酷狼 2022-07-20 16:14 365阅读 0赞

页面：

$("#exportBtn").live("click", function () {
                   
                	window.open('/exportMoneyConsume?type=1&incident=1');
                });

后台：

controller

@Get("/exportMoneyConsume")
    	@LoginCheckRequired
    	public String exportMoneyConsume(Invocation inv,MoneyConsumeView param) throws Exception {
    		
    		
    		String fileName="adf"+System.currentTimeMillis()+".xls";
    		String path=Constants.UPLOAD_BASE_FOLD +"/"+fileName;
    			
    		
    		exportMoneyConsume(path,param);
    		
    		downLoad(inv, fileName, path);
    		
    		return "@";
    	}

Service：

public void exportMoneyConsume(String path, MoneyConsumeView param) {
    
    		List<MoneyConsumeView> list = MoneyConsumeDAO.exportMoneyConsume(param);
    
    		// 1.创建Excel工作薄对象
    		HSSFWorkbook wb = new HSSFWorkbook();
    		// 2.创建Excel工作表对象
    		HSSFSheet sheet = wb.createSheet("new Sheet");
    
    		// 3.创建单元格样式
    		CellStyle cellStyle = wb.createCellStyle();
    		// 设置这些样式
    		cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		sheet.setDefaultColumnWidth(20);
    
    		int i = 0;
    		// 4.创建Excel工作表的行
    		HSSFRow row = sheet.createRow(i);
    		row.createCell(0).setCellValue("");
    		row.createCell(1).setCellValue("aa");
    		row.createCell(2).setCellValue("bb");
    		row.createCell(3).setCellValue("df");
    		row.createCell(4).setCellValue("fasasf");
    		row.createCell(5).setCellValue("adsf");
    		row.createCell(6).setCellValue("asdf");
    		row.createCell(7).setCellValue("asdf");
    		row.createCell(8).setCellValue("asdf");
    		row.createCell(9).setCellValue("asfd");
    		row.createCell(10).setCellValue("safd");
    		row.createCell(11).setCellValue("asfd");
    
    		for (MoneyConsumeView moneyConsume : list) {
    
    			if (moneyConsume.getLevel() == null) {
    				continue;
    			}
    
    			String fileName = SchoolLevelEnum.getName(moneyConsume.getLevel());
    			if (StringUtils.isNotBlank(fileName)) {
    				moneyConsume.setLevelName(fileName);
    			}
    
    			row = sheet.createRow(i + 1);
    			row.createCell(0).setCellValue(i + 1);
    			row.createCell(1).setCellValue(areaCacheService.getAreaNameCache(moneyConsume.getAreaCode()));
    			row.createCell(2).setCellValue(moneyConsume.getLevelName());
    			row.createCell(3).setCellValue(schoolCacheService.getSchoolNameCache(moneyConsume.getSchoolId()));
    			row.createCell(4).setCellValue(moneyConsume.getUserName());
    			row.createCell(5)
    					.setCellValue(DateUtils.formatDate(moneyConsume.getCreateTime(), DateUtils.FORMAT_DAY_TIME));
    			row.createCell(6).setCellValue(moneyConsume.getName());
    			row.createCell(7).setCellValue(moneyConsume.getPrice());
    			row.createCell(8).setCellValue(moneyConsume.getStudentCount());
    			row.createCell(9).setCellValue(moneyConsume.getDiscount());
    			row.createCell(10).setCellValue(moneyConsume.getPayAmount());
    			if(moneyConsume.getPresentBalance()!=null){
    				row.createCell(11).setCellValue(moneyConsume.getPresentBalance());
    			}else{
    				row.createCell(11).setCellValue(0);
    			}
    			
    
    			i = i + 1;
    
    		}
    
    		// 设置sheet名称和单元格内容
    		wb.setSheetName(0, "第一张工作表");
    		// 设置单元格内容 cell.setCellValue("单元格内容");
    
    		// 最后一步，将文件存到指定位置
    
    		try {
    			FileOutputStream fout = new FileOutputStream(path);
    			wb.write(fout);
    			fout.close();
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    
    	}

public void downLoad(Invocation inv, String fileName, String path) throws IOException {
    		
    		File file = new File(path);// 构造要下载的文件
    		if (file.exists()) {
    
    		    InputStream ins = null;
    			BufferedInputStream bins = null;
    			OutputStream outs = null;
    			BufferedOutputStream bouts = null;
    
    			try {
    
    				ins = new FileInputStream(path);// 构造一个读取文件的IO流对象
    				bins = new BufferedInputStream(ins);// 放到缓冲流里面
    				outs = inv.getResponse().getOutputStream();// 获取文件输出IO流
    				bouts = new BufferedOutputStream(outs);
    
    				inv.getResponse().setContentType("application/x-download");// 设置response内容的类型
    				inv.getResponse().setHeader("Content-disposition",
    						"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));// 设置头部信息
    
    				int bytesRead = 0;
    				byte[] buffer = new byte[8192];
    
    				// 开始向网络传输文件流
    				while ((bytesRead = bins.read(buffer, 0, 8192)) != -1) {
    
    					bouts.write(buffer, 0, bytesRead);
    				}
    				bouts.flush();// 这里一定要调用flush()方法
    
    
    			} catch (Exception e) {
    
    				e.printStackTrace();
    			} finally {
    
    				if (bouts != null) {
    
    					bouts.close();
    					bouts = null;
    				}
    				if (outs != null) {
    					
    					outs.close();
    					outs = null;
    				}
    				
    				if (bins != null) {
    
    					bins.close();
    					bins = null;
    				}
    				if (ins != null) {
    
    					ins.close();
    					ins = null;
    				}
    
    				file.delete();
    			}
    
    		} else {
    			logger.info("导出的文件不存在");
    		}
    		
    	}