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) 10); //设置字体名字; font.setFontName("宋体"); //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; } ?