POI:使用EXECL模板填入数据_poi实现excel模板填充-CSDN博客

本文链接：https://blog.csdn.net/hwt_211/article/details/8086409

本文介绍了一个使用Java实现的通过EXECL模板批量导出新增资产申请凭证的方法。该方法能够处理大量的资产数据，并将其按照规定格式填充到Excel模板中，支持分页显示，同时确保数据正确性和格式一致性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

EXECL 模板：

代码：

//导出新增资产申请凭证
	public void downNewXls(List<TbAddassets> addassets,OutputStream osOutputStream) throws BiffException, IOException, RowsExceededException, WriteException {
		InputStream inputStream = ServletActionContext.getServletContext().getResourceAsStream("/template/excel/new.xls");
		HSSFWorkbook workbook = new HSSFWorkbook(inputStream); //得到新增的模板
		
		int len = addassets.size();
		int num = len%15==0?len/15:len/15+1; //sheet的张数
		DecimalFormat moneyFormat = new DecimalFormat("###,###,###.##");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		//设置单元格样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont font = workbook.createFont();
		cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		font.setFontHeightInPoints((short)13);
		cellStyle.setFont(font);
		
		//HSSFSheet sht = workbook.getSheetAt(0);//得到模板
		int temp = 0 ;
		for (int i = 0; i < num; i++) {
			HSSFSheet sheet = workbook.cloneSheet(0); //克隆
			//设置部门名称
			HSSFRow row = sheet.getRow(1);
			HSSFCell depcell = row.getCell(2);
			depcell.setCellStyle(cellStyle);
			depcell.setCellValue(addassets.get(i).getTbPartment().getName());
			double sum = 0.0f;
			for (int j = 0; j < 15; j++) {
				HSSFRow dateRow = sheet.getRow(3+j);
				HSSFCell cell1 = dateRow.getCell(2);
				cell1.setCellStyle(cellStyle);
				cell1.setCellValue(addassets.get(temp+j).getTbGoodssort().getSortcode());//设置分类码
				
				HSSFCell cell2 = dateRow.getCell(4);//设置资产名称
				cell2.setCellStyle(cellStyle);
				cell2.setCellValue(addassets.get(temp+j).getCalled());
				
//				HSSFCell cell3 = dateRow.getCell(8); //资产id
//				cell3.setCellStyle(cellStyle);
//				cell3.setCellValue(addassets.get(i+j).getAsid());
				
				HSSFCell cell4 = dateRow.getCell(11);
				cell4.setCellStyle(cellStyle);
				cell4.setCellValue(sdf.format(addassets.get(temp+j).getDotime())); //购置时间
				
				double every = addassets.get(temp+j).getEvery();
				HSSFCell cell5 = dateRow.getCell(14);
				cell5.setCellStyle(cellStyle);
				cell5.setCellValue(moneyFormat.format(every));  //设置单价
				
				HSSFCell cell6 = dateRow.getCell(17);
				cell6.setCellStyle(cellStyle);
				cell6.setCellValue(1); //数量
				
				HSSFCell cell7 = dateRow.getCell(19);
				cell7.setCellStyle(cellStyle);
				cell7.setCellValue(moneyFormat.format(every));//合计
				
				HSSFCell cell8 = dateRow.getCell(22);
				cell8.setCellStyle(cellStyle);
				cell8.setCellValue(addassets.get(temp+j).getPoint());//所处位置
				
				HSSFCell cell9 = dateRow.getCell(25);
				cell9.setCellStyle(cellStyle);
				cell9.setCellValue(addassets.get(temp+j).getDetails()); //备注
				sum = sum + every;
				if (temp+j+2>len) {
					break;
				}
			}
			
			HSSFRow  totalRow = sheet.getRow(18); //总计
			HSSFCell cell10 = totalRow.getCell(14);
			cell10.setCellStyle(cellStyle);
			cell10.setCellValue(moneyFormat.format(sum));
			
			HSSFCell cell11 = totalRow.getCell(17);	
			cell11.setCellStyle(cellStyle);
			cell11.setCellValue(15);
			
			HSSFCell cell12 = totalRow.getCell(19);
			cell12.setCellStyle(cellStyle);
			cell12.setCellValue(moneyFormat.format(sum));
			
			temp = temp + 15;
		}
		workbook.removeSheetAt(0); //移除模板
               //给每个sheet起个名字
		for(int m =1 ; m <= num ; m++){
		   workbook.setSheetName(m, "新增列表"+m);
		}


workbook.write(osOutputStream);}

 
//实现下载
HttpServletResponse response = ServletActionContext.getResponse();
			response.addHeader("Content-Type", "application/vnd.ms-excel");
			response.addHeader("content-disposition", "attachment;filename="+URLEncoder.encode("新增资产审核.xls", "utf-8")); 
			
			OutputStream outputStream =	response.getOutputStream();
			downXlsService.downChangeXls(assets, outputStream);
			outputStream.flush();
			outputStream.close();