POI通过读取Excel模板生成Excel文件

 

/**
	 * 导出
	 * @param mapping
	 * @param form
	 * @param request
	 * @param response
	 * @return
	 */
	public ActionForward export(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) {
		
		String checkBoxValueList = request.getParameter("ckv");
		if(!CommonUtil.isNotNullorEmtry(checkBoxValueList)) {
			this.saveMessages(request, "export.fail");
			return mapping.findForward("export.fail");
		}
		List<Order> listOrder = orderService.exportOrderList(checkBoxValueList);
		
		try {
//			SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddhhmm");
//			Calendar calendar = Calendar.getInstance();
			
			ServletOutputStream os = response.getOutputStream(); //获得输出流
			response.reset();	//清空输出流
			String fileName = new String("订单列表".getBytes("gb2312"), "ISO8859-1") +".xls";
			response.setHeader("Content-disposition", "attachment; filename="+ fileName); //设定输出文件头
			response.setContentType("application/msexcel"); //定义输出类型

			String filePath = request.getSession().getServletContext().getRealPath("/excel/model/order.xls");

			HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath));	//读取excel模板
			try {
				HSSFSheet sheet = workbook.getSheetAt(0);	//读取第一个工作簿
				HSSFRow row;
				HSSFCell cell = null;
				int rownum = 3;	//添加的起始行
				HSSFCellStyle style = this.getStyle(workbook);
				Iterator<Order> it = listOrder.iterator();
				Order order = null;
								
				while(it.hasNext()) {
					order = new Order();
					order = it.next();
					
					row = sheet.createRow(rownum);
					
					myCreateCell(1, String.valueOf(rownum-2), row, cell, style);	//列1
					myCreateCell(2, order.getAdName(), row, cell, style);	//列2
					myCreateCell(3, order.getSmallAreaName(), row, cell, style);	//列1	
										
					rownum++;
				}
								
				workbook.write(os);
				os.flush();
				os.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	private void myCreateCell(int cellnum, String value, HSSFRow row, HSSFCell cell, HSSFCellStyle style) {
		cell = row.createCell((short) cellnum);
		cell.setCellValue(new HSSFRichTextString(value));
		cell.setCellStyle(style);
	}
	
	public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
		//设置字体;
		HSSFFont font = workbook.createFont();
		//设置字体大小;
		font.setFontHeightInPoints((short) 5);
		//设置字体名字;
		font.setFontName("Courier New");
		//font.setItalic(true);
		//font.setStrikeout(true);
		//设置样式;
		HSSFCellStyle style = workbook.createCellStyle();
		//设置底边框;
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		//设置底边框颜色;
		style.setBottomBorderColor(HSSFColor.BLACK.index);
		//设置左边框;
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		//设置左边框颜色;
		style.setLeftBorderColor(HSSFColor.BLACK.index);
		//设置右边框;
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		//设置右边框颜色;
		style.setRightBorderColor(HSSFColor.BLACK.index);
		//设置顶边框;
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		//设置顶边框颜色;
		style.setTopBorderColor(HSSFColor.BLACK.index);
		//在样式用应用设置的字体;
		style.setFont(font);
		//设置自动换行;
		style.setWrapText(false);
		//设置水平对齐的样式为居中对齐;
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		//设置垂直对齐的样式为居中对齐;
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return style;
	}