扩展Struts2,傻瓜化将数据导出为excel

以前做开发，在有数据导出为excel的时候，不加思索就一个cell一个cell地拼凑完成一个excel内存流，输入出到客户端了事，有天想这些代码80%都是重复的，能不能不那么麻烦地ctrl c、ctrl v来做这件事。尝试了下，还可以。（代码部份中是演示）
首先定义一个Annotation

@Retention(RetentionPolicy.RUNTIME)
public @interface Excel {
String title() default "";
}

在需要导出为excel的类上加上此Excel注解，如：

class User {
public User(String name, Integer age) {
  this.name = name;
  this.age = age;
}
private String name;
private Integer age;
@Excel(title = "姓名")
public String getName() {
  return name;
}
public void setName(String name) {
  this.name = name;
}
@Excel(title = "年龄")
public Integer getAge() {
  return age;
}
public void setAge(Integer age) {
  this.age = age;
}
}

现在相当于配置内容有了，只需写一个工具类，完成向excel数据格式的转换。
这有个简单实现：

public static void export(List list, OutputStream os) {
  List<String> titles = new ArrayList<String>();
  Hashtable<String, List> data = parseData(list, titles);
  try {
   builderExcel(data, titles, list.size(), os);
  } catch (Exception e) {
   e.printStackTrace();
  }
}
private static void builderExcel(Hashtable<String, List> data,
   List<String> titles, int len, OutputStream os) throws Exception {
  WritableWorkbook workbook = Workbook.createWorkbook(os);
  WritableSheet ws = workbook.createSheet("sheet1", 0);//
  for (int i = 0; i < titles.size(); i++) {
   Label labelC = new Label(i, 0, titles.get(i));
   ws.setColumnView(i, 20);
   ws.addCell(labelC);
   
  }
  for (int i = 0; i < len; i++) {
   for (int j = 0; j < titles.size(); j++) {
    List<Object> context = data.get(titles.get(j));
    Object o = context.get(i);
    WritableCell labelC = null;
    if (o instanceof String) {
     labelC = new Label(j, i + 1, o.toString());
    } 
    else if(o instanceof Double)
    {
     labelC = new Number(j, i + 1, (Double)o);
    }
    else if(o instanceof Date)
    {
     labelC = new DateTime(j, i + 1, (Date)o);
    }
    ws.addCell(labelC);
   }
  }
  workbook.write();
  workbook.close();
}
private static Hashtable parseData(List list, List titles) {
  Hashtable<String, List> data = new Hashtable<String, List>();
  for (Object obj : list) {
   Method[] methods = obj.getClass().getMethods();
   for (Method m : methods) {
    if (m.isAnnotationPresent(Excel.class)) {
     Excel excel = m.getAnnotation(Excel.class);
     String title = excel.title();
     List<Object> cols = data.get(title);
     if (cols == null) {
      titles.add(title);
      cols = new ArrayList<Object>();
     }
     try {
      cols.add(m.invoke(obj));
     } catch (IllegalArgumentException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
     } catch (IllegalAccessException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
     } catch (InvocationTargetException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
     }
     data.put(title, cols);
    }
   }
  }
  return data;
}
}

最后一步，扩展struts2的resulttype.

public class ExcelResult extends StrutsResultSupport { 
    private String dataList = "dataList"; 
    private String fileName = "fileName"; 
    protected void doExecute(String arg0, ActionInvocation actionInvocation) 
     throws Exception { 
ActionContext ctx = actionInvocation.getInvocationContext(); 
HttpServletRequest request = (HttpServletRequest) ctx 
  .get(org.apache.struts2.StrutsStatics.HTTP_REQUEST); 
HttpServletResponse response = (HttpServletResponse) ctx 
  .get(org.apache.struts2.StrutsStatics.HTTP_RESPONSE); 
String excelfile = (String) request.getAttribute(fileName); 
setXlsHeader(response, excelfile); 
OutputStream os = response.getOutputStream(); 
List list = (List) request.getAttribute(dataList); 
ExcelExporter.export(list, os); 
os.close(); 
    } 
    public void setDataList(String dataList) { 
this.dataList = dataList; 
    } 
    public void setFileName(String fileName) { 
this.fileName = fileName; 
    } 
    private void setXlsHeader(HttpServletResponse rs, String fn) 
     throws UnsupportedEncodingException { 
rs.setContentType("application/vnd.ms-excel"); 
String fileName = new String(fn.getBytes("GBK"), "ISO-8859-1"); 
rs.setHeader("Content-disposition", "attachment; filename=" + fileName); 
    } 
} 

现在看看程序员需要做的事：

List<User> userList = userService.findAll();
request.setAttribute("dataList",userList);

在struts2的配置文件中加入
<result name="success" type="excel">
</result>
当然你得在需要导出的类中加入Excel的注解和定义一下excel的resulttype.

评论

1 楼 yoyo837 2011-11-15  

如果一开始导出 加进度框,如何知道文件导出完成从而取消进度框呢