poi导出模版复制

由于工作需要，excel每次个工作表格式一样，每次手工填写太麻烦，于是想到了服务器自己来做这个工作。

 

 

protected HSSFWorkbook wb = null;
	protected HSSFSheet sheet;
	protected HSSFRow row = null;   
	protected HSSFCell cell = null; 
	public void export(String url,BailChargeDTO list,String sDate,String eDate, OutputStream output) throws Exception {
	
POIFSFileSystem fs = null;
		try {
			fs = new POIFSFileSystem(new FileInputStream(url));
			wb = new HSSFWorkbook(fs);
			
			int sdate = Integer.parseInt(sDate);
			int edate = Integer.parseInt(eDate);
			
			for(int date = sdate ; date <= edate ; ++date){
		
				HSSFSheet fromsheet = wb.getSheet("template");
				if (fromsheet != null && wb.getSheet(String.valueOf(date)) == null) {
					HSSFSheet newsheet = wb.createSheet(String.valueOf(date));
					new PoiExcelSheetCopy().copyRows(wb, fromsheet, newsheet, fromsheet.getFirstRowNum(), fromsheet.getLastRowNum());
				}
				
				sheet = wb.getSheet(String.valueOf(date));//获取工作表名字
				row = sheet.createRow(1);
				Cell ztCell = row.createCell(0);
				ztCell.setCellValue(" 日期：" + date);
				
				// 创建单元格样式对象  
				HSSFCellStyle cellstyle = wb.createCellStyle();
				HSSFCellStyle textstyle = wb.createCellStyle();
				HSSFCellStyle sumstyle = wb.createCellStyle();
	            
				// 创建字体对象  
				Font dateFont = wb.createFont();  
				dateFont.setFontHeightInPoints((short)18);    // 将字体大小设置为18px  
				dateFont.setFontName("宋体");             // 将“华文行楷”字体应用到当前单元格上  
				dateFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	            cellstyle.setFont(dateFont);                    // 将字体应用到样式上面  
	            cellstyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	            ztCell.setCellStyle(cellstyle); 
//	            
	            Font textFont = wb.createFont();  
	            textFont.setFontHeightInPoints((short)18);    // 将字体大小设置为18px  
	            textFont.setFontName("宋体");             // 将“华文行楷”字体应用到当前单元格上  
				textstyle.setFont(textFont);                    // 将字体应用到样式上面  
				textstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				
				Font suFont = wb.createFont();  
				suFont.setFontHeightInPoints((short)18);    // 将字体大小设置为18px  
				suFont.setFontName("宋体");             // 将“华文行楷”字体应用到当前单元格上  
				suFont.setColor(HSSFColor.RED.index);
				sumstyle.setFont(suFont);                    // 将字体应用到样式上面  
				sumstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				
				//
				int rownum = 4;
				List<AgentAccountDetailDTO> dtoList = new BailChargeQueryExport().sortMap(list.getAgentAccount(),date).get(String.valueOf(date));
				int len = dtoList.size();
				for (int j = 0; j < len; j++) {
					AgentAccountDetailDTO dto = dtoList.get(j);
	
					row = sheet.createRow(rownum);
					row.setHeightInPoints(20);
					Cell c = row.createCell(0);
					Cell d = row.createCell(1);
					Cell e = row.createCell(2);
					Cell f = row.createCell(3);
					Cell h = row.createCell(4);
					Cell i = row.createCell(5);
					Cell k = row.createCell(6);
					Cell g = row.createCell(7);
					
					c.setCellValue(new HSSFRichTextString(String.valueOf(dto.getAgentName())));
					d.setCellValue(dto.getAgentId());
					e.setCellValue(dto.getAgentLink());
					f.setCellValue(Double.parseDouble(dto.getRfee().toString()));
	//				row.createCell(2).setCellValue(new HSSFRichTextString(dto.getRuser()));
	//				row.createCell(4).setCellValue(dto.getRdate());
					g.setCellValue(dto.getTransDesc());
					
					c.setCellStyle(textstyle);
					d.setCellStyle(textstyle);
					e.setCellStyle(textstyle);
					f.setCellStyle(textstyle);
					g.setCellStyle(textstyle);
					h.setCellStyle(textstyle);
					i.setCellStyle(textstyle);
					k.setCellStyle(textstyle);
					//
					rownum++;
				}
				row = sheet.createRow(rownum);
				sheet.setForceFormulaRecalculation(true);
				row.setHeightInPoints(20);
				Cell c = row.createCell(0);
				c.setCellValue("总数");
				c.setCellStyle(sumstyle);
				Cell sum = row.createCell(3);
				sum.setCellType(HSSFCell.CELL_TYPE_FORMULA);//设置为公式
				
				// 设置公式内容
				String formula = "SUM(D5:D"+ rownum +")";//计算一列总数
				if(HSSFCell.CELL_TYPE_FORMULA == sum.getCellType()){
					sum.setCellFormula(formula);
					
				}
				sum.setCellStyle(sumstyle);
				
			}
			wb.setActiveSheet(1);//激活滴2个工作表，保存打开
			wb.write(output);
			
		} catch (IOException e) {
			throw new Exception("服务器报表文件位置异常,请报告管理员!谢谢!");
		} 

}
	//对数据筛选
	public Map<String ,List<AgentAccountDetailDTO>> sortMap(List<AgentAccountDetailDTO> list, int sdate){
		Map<String ,List<AgentAccountDetailDTO>> map = new HashMap<String ,List<AgentAccountDetailDTO>>(); 
		List<AgentAccountDetailDTO> dtoList = new ArrayList<AgentAccountDetailDTO>();  //重新声明一个数组list
		 for(Iterator<AgentAccountDetailDTO> it = list.iterator();it.hasNext();){
			 AgentAccountDetailDTO dto = (AgentAccountDetailDTO)it.next();
	            if(Integer.parseInt(dto.getRdate()) == (sdate)){ //如果已经存在这个数组，就放在这里 
	            	dtoList.add(dto);
	            }
	        }
		 map.put(String.valueOf(sdate), dtoList);
 
		return map;
	}

WPS 打开不能自动求和，ms excel 打开后可以自动求和。