poi 渲染本地模板

/**
	 * 得到一个已有的工作薄的POI对象
	 * 
	 * @return
	 */
	public static HSSFWorkbook getExcelForPath(String excelPath) {
		HSSFWorkbook wb = null;
		FileInputStream fis = null;
		File f = new File(excelPath);
		try {
			if (f != null) {
				fis = new FileInputStream(f);
				wb = new HSSFWorkbook(fis);
			}
		} catch (Exception e) {
			return null;
		} finally {
			if (fis != null) {
				try {
					fis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return wb;
	}

	
	
	
	
	
	//------------------------------------------
	
	@RequestMapping("/exportExcel")
	public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
  
		List<DataBsData> list = service.queryAllList(map);

// 获取本地路径
		String path = request.getSession().getServletContext().getRealPath("/");
		String filePath = path + File.separator + "template" + File.separator + "abccd.xls";
		log.info(" filePath is {}", filePath);
		
        HSSFWorkbook wb = ExcelRender.getExcelForPath(filePath);
		renderWorkbook(list, wb);
		try {
			log.info(" start download ........");
			response.reset();
			OutputStream os = response.getOutputStream();
			response.setContentType("application/msexcel");
			String fileName = "测试渲染的表";
			fileName = StringUtils.isNotBlank(version) ? fileName.concat(version) : fileName;
			response.setHeader("Content-disposition",
					"attachment; filename=" + new String(fileName.getBytes("GB2312"), "8859_1") + ".xls");
			wb.write(os);
			os.close();
		} catch (Exception e) {
			try {
				response.reset();
				response.getWriter().write(e.getMessage());
			} catch (IOException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

	}

	/**
	 * 
	 * @Title: renderWorkbook 
     @Description: TODO(渲染数据) 
      	 */
	private void renderWorkbook(List<DataBsData> list, HSSFWorkbook wb) {
		org.apache.poi.ss.usermodel.Cell cell = null;
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
// 自动换行
		cellStyle.setWrapText(true);
		// 得到第一个Sheet
		HSSFSheet sheet1 = wb.getSheetAt(0);
		log.info(" wb.getSheetAt(0) is {}", sheet1.getDefaultColumnWidth());
		HSSFRow row = sheet1.getRow(0);

		for (int i = 0; i < list.size(); i++) {
			DataBsData item = list.get(i);
//			角标 x y 都是从零开始 
//			表头第四列 开始 render
			row = sheet1.createRow(i + 4);
				
			row.setHeight((short)900);
			
			cell = row.createCell(0);
			cell.setCellValue(item.getOrgName());
			cell.setCellStyle(cellStyle);
			
			cell = row.createCell(1);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(item.getVa());
			
			cell = row.createCell(2);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(item.getVb());
			cell = row.createCell(3);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(item.getVc());
			
			cell = row.createCell(4);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(item.getVd());
			
			
			
			cell = row.createCell(5);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(item.getVe());
			 
		}
	}

• 注意区分模板, 当前使用 xls 格式! xlsx 格式修改名称无用!

自动换行的设置：

HSSFCellStyle cellStyle=workbook.createCellStyle();         
cellStyle.setWrapText(true);         
cell.setCellStyle(cellStyle);