OAF_文件系列10_实现OAF将数据资料导出Excel到本地JXL（案例）

  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)  
  {
    super.processFormRequest(pageContext, webBean);
    
      EmpManageAMImpl empam = (EmpManageAMImpl) pageContext.getApplicationModule(webBean); EmployeesVOImpl empvo = empam.getEmployeesSummaryVO(); if ("exportexcel".equals(pageContext.getParameter(EVENT_PARAM))) { System.out.println("exportexcel co"); empexportexcel(pageContext, empvo); } public void empexportexcel(OAPageContext pageContext, EmployeesVOImpl empvo) { HttpServletResponse response = (HttpServletResponse) pageContext.getRenderingContext().getServletResponse(); response.setCharacterEncoding("gb2312"); response.reset(); response.setContentType("application/vnd.ms-excel;charset=UTF-8"); String filenames = "gavin_emp_export.xls"; String[] expLabels = new String[] {"Employee Id","Employee Num","Employee Name","Type","HireDate","Country","Address","Salary"}; String[] expFields = new String[] {"EmployeeId","EmployeeNum","EmployeeName","EmployeeType","HireDate","EmployeeCountry","EmployeeAddress","Salary"}; Class[] colClasses = new Class[] {null,null,null,null,null,null,null,null}; int inId = empvo.getRangeStart(); EmployeesVORowImpl currentRow = (EmployeesVORowImpl) empvo.getCurrentRow(); if (empvo == null) { return; } try{ OutputStream fileos = response.getOutputStream(); response.addHeader("Content-Disposition", (new StringBuilder()).append("attachment; filename=\"").append(filenames).append("\"").toString()); WorkbookSettings workbookSettings = new WorkbookSettings(); workbookSettings.setEncoding("ISO-8859-1"); //乱码处理 WritableWorkbook workbook = Workbook.createWorkbook(fileos, workbookSettings); WritableSheet sheet = workbook.createSheet("Employee List Sheet",0); // 定义格式 字体 下划线 斜体 粗体 颜色 WritableFont wf_h = new WritableFont(WritableFont.ARIAL, 20, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLUE_GREY); WritableCellFormat wcf_h = new WritableCellFormat(wf_h); // 单元格定义 wcf_h.setAlignment(Alignment.LEFT); // 设置对齐方式  wcf_h.setBorder(jxl.format.Border.NONE,BorderLineStyle.NONE); // 定义格式 字体 下划线 斜体 粗体 颜色 WritableFont wf_t = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.WHITE); WritableCellFormat wcf_t = new WritableCellFormat(wf_t); // 单元格定义 wcf_t.setBackground(Colour.BLUE_GREY); // 设置单元格的背景颜色 wcf_t.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式 wcf_t.setVerticalAlignment(VerticalAlignment.TOP); // 设置对齐方式  wcf_t.setBorder(jxl.format.Border.ALL,BorderLineStyle.MEDIUM,Colour.GREY_25_PERCENT); // 定义格式 字体 下划线 斜体 粗体 颜色 WritableFont wf_cs = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK); WritableCellFormat wcf_cs = new WritableCellFormat(wf_cs); // 单元格定义 wcf_cs.setAlignment(Alignment.LEFT); // 设置对齐方式  wcf_cs.setVerticalAlignment(VerticalAlignment.TOP); wcf_cs.setWrap(true); wcf_cs.setBorder(jxl.format.Border.ALL,BorderLineStyle.MEDIUM,Colour.GREY_25_PERCENT); // 定义格式 字体 下划线 斜体 粗体 颜色 WritableFont wf_cn = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK); WritableCellFormat wcf_cn = new WritableCellFormat(wf_cn); // 单元格定义 wcf_cn.setAlignment(Alignment.RIGHT); // 设置对齐方式  wcf_cn.setVerticalAlignment(VerticalAlignment.TOP); wcf_cn.setBorder(jxl.format.Border.ALL,BorderLineStyle.MEDIUM,Colour.GREY_25_PERCENT); Label expLabel = null; // 头 expLabel = new Label(0, 0, "Employee Summary　Report", wcf_h); sheet.addCell(expLabel); sheet.mergeCells(0,0,2,0); sheet.setRowView(0, 500); // 标题 for (int i = 0, j=0; i < expLabels.length; i++, j++) { expLabel = new Label(j, 1, expLabels[i], wcf_t); sheet.addCell(expLabel); } sheet.setRowView(1, 400); // 内容  EmployeesVORowImpl rowfield; rowfield = (EmployeesVORowImpl) empvo.first(); if (rowfield != null) { for (int i = 0; i < expFields.length; i++) //获取每个字段的数据类型String/Date/Number  { colClasses[i] = rowfield.getStructureDef().lookupAttributeDef(expFields[i]).getJavaType(); } } empvo.previous(); while (empvo.hasNext()) { rowfield = (EmployeesVORowImpl) empvo.next(); int rowcount = empvo.getCurrentRowIndex() + 2; for (int i = 0, j=0; i < expFields.length; i++, j++) { if (rowfield.getAttribute(expFields[i]) != null) { if (colClasses[i].equals(Class.forName("oracle.jbo.domain.Date"))) { expLabel = new Label(j, rowcount, rowfield.getAttribute(expFields[i]).toString(), wcf_cs); //Date类型格式  } else if (colClasses[i].equals(Class.forName("oracle.jbo.domain.Number"))) { expLabel = new Label(j, rowcount, rowfield.getAttribute(expFields[i]).toString(), wcf_cn); //Number类型格式  } else if (colClasses[i].equals(Class.forName("java.lang.String"))) { expLabel = new Label(j, rowcount, rowfield.getAttribute(expFields[i]).toString(), wcf_cs); //String类型格式  } } else { expLabel = new Label(j, rowcount, "", wcf_cs); } sheet.addCell(expLabel); } } sheet.setColumnView(0, 15); //设定栏位EmployeeId长度 sheet.setColumnView(1, 15); //设定栏位EmployeeNum长度 sheet.setColumnView(2, 20); //设定栏位EmployeeName长度 sheet.setColumnView(3, 15); //设定栏位Type长度 sheet.setColumnView(4, 15); //设定栏位HireDate长度 sheet.setColumnView(5, 15); //设定栏位Country长度 sheet.setColumnView(6, 15); //设定栏位Address长度 sheet.setColumnView(7, 10); //设定栏位Salary长度 //关闭服务 sheet.getSettings().setShowGridLines(false); workbook.write(); workbook.close(); fileos.close(); response.flushBuffer(); }catch(Exception ex){ empvo.setRangeStart(inId); empvo.setCurrentRow(currentRow); throw new OAException(ex.getMessage()); } }