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

20150729 Created By BaoXinjian

一、摘要

---

将页面上的信息通过调用JXL脚本，将资料导出为Excel

引用的一些包方法

1. 获取输出文件流

1.1. HttpServletResponse： 将文件写到客户端所引用的包

1.2. OutputStream： 从HttpServeltResponse中导出输出文件流

2. 创建Excel

2.1 WorkbookSetting：创建Excel的格式

2.2 WritableWorkbook：创建Excel文件

2.3  WritableSheet：创建Excel工作簿

3. 设定Excel中单元格格式

3.1  WritableFont：定义单元格格式

3.2 WritableCellFormat ：定义单元格内容

 

二、实现分析

---

1. 创建Export Excel Button，触发在CO中的方法，调用导出Excel功能

 

2. 设计CO中的方法

  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());
    }
  }

 

三、测试运行

---

1. 点击导出ExportExcel

 

2. 页面出现保存文件提示框

 

3. 打开导出的Excel

 

Thanks and Regards