數據庫 2 excel

今天寫了個excel 2 mysql，順便在網上查了下 mysql 2 excel。 看到一篇博客，感覺不錯，為了方便，就copy了。

使用Java Annotation写Excel 工具类

package com.excel.export.util;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/** *  * <p>导出excel 工具包</p> * @author liuqing * @version 1.0 * @see 导出excel 注解 * */@Retention(value=RetentionPolicy.RUNTIME)@Target(value=ElementType.FIELD)public @interface AllowExcel {boolean value() default true;String name();}

写实现类

package com.excel.export.util;import java.io.Serializable;import java.util.Date;/** *  * @author liuqing * @version 1.0 * @datetime 2011-11-24 * @see 学生测试对象 * */public class Student implements Serializable {private static final long serialVersionUID = -8141916472359874289L;@AllowExcel(name="编号")private Integer id;@AllowExcel(name="姓名")private String name;@AllowExcel(name="生日")private Date date;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Date getDate() {return date;}public void setDate(Date date) {this.date = date;}}

POI 实现类

package com.excel.export.util;import java.io.FileOutputStream;import java.io.IOException;import java.io.Serializable;import java.lang.reflect.Field;import java.util.ArrayList;import java.util.Collection;import java.util.Date;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;/** *  * @author liuqing * @see excel 导出工具类 * @version 1.0 * @datetime 2011-11-24 * */public class ExportExcel<T extends Serializable> {private HSSFWorkbook hw = new HSSFWorkbook();/** * 生成sheet 名称 */private String sheetName = "shtteName";private boolean showHeader = true;/** * 缓存成员变量 */private List<String> fieldNameCaches = new ArrayList<String>();public void createHeader(Collection<T> data) throws IllegalAccessException {//Sheet 创建工作表HSSFSheet sheet = hw.createSheet(sheetName);int j = 0;for (T t:data) {Field[] fields = t.getClass().getDeclaredFields();/** * 加入允许字段缓存数据 * if == 0时表示要添加缓存数据 */if (j == 0) {HSSFRow headRow = null;if (this.showHeader) {headRow = sheet.createRow(0);}int i = 0;for (Field field:fields) {//判断Excel 安全允许注解AllowExcel allowExcel = field.getAnnotation(AllowExcel.class);if (allowExcel != null && allowExcel.value()) {//显示关部信息if (this.showHeader) {HSSFCell cell = headRow.createCell(i);cell.setCellValue(allowExcel.name());}this.fieldNameCaches.add(field.getName());}System.out.println(allowExcel.name());i++;}}j++;//创建产生行数据    HSSFRow hssfRow = sheet.createRow(j);    this.setCellValueToRow(t, hssfRow);}}/** * 输出Excel Row 信息 * @param t T extends Serializable * @param hssfRow HSSFRow * @return HSSFRow  * @throws IllegalAccessException */public HSSFRow setCellValueToRow(T t,HSSFRow hssfRow) throws IllegalAccessException {Field fields[] = t.getClass().getDeclaredFields();//定义Excel 输出行数int i = 0;for (Field field:fields) {//缓存中是否存在允许字段if (this.isCacheFiledName(field.getName())) {HSSFCell cell = hssfRow.createCell(i);i++;field.setAccessible(true);Object obj = field.get(t);//类型转换if (obj instanceof Integer ) {cell.setCellValue((Integer)obj);}else if (obj instanceof String) {cell.setCellValue((String)obj);}else if (obj instanceof Date) {cell.setCellValue((Date)obj);}else if (obj instanceof Double) {cell.setCellValue((Double)obj);}else if (obj instanceof Boolean) {cell.setCellValue((Boolean)obj);}else if (obj instanceof Float) {cell.setCellValue((Float)obj);}else if (obj instanceof Long) {cell.setCellValue((Long)obj);}else {throw new TypeNotPresentException("类型不支持", null);}}}return hssfRow;}/** * 判断Cache 是否有对应的FiledName * @param fieldName String * @return boolean */public boolean isCacheFiledName(String fieldName) {if (fieldName == null) {return false;}for (String fieldNameCache:this.fieldNameCaches) {if (fieldName.equals(fieldNameCache)) {return true;}}return false;}public static void main(String args[]) throws IllegalAccessException {ExportExcel<Student> export = new ExportExcel<Student>();List<Student> list = new ArrayList<Student>();Student stu = new Student();stu.setId(123);stu.setDate(new Date());stu.setName("liquing");Student stu1 = new Student();stu1.setId(1231);stu1.setDate(new Date());stu1.setName("liquing1");Student stu2 = new Student();stu2.setId(1231);stu2.setDate(new Date());stu2.setName("liquing1");list.add(stu);list.add(stu1);list.add(stu2);export.createHeader(list);FileOutputStream out = null;try {out = new FileOutputStream("d:\\um.xls");export.hw.write(out);} catch (Exception e) {// TODO: handle exception}finally {if (out != null) {try {out.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}/** * 查找对应的类自定义方法 * @param methodName * @return boolean */public boolean isClassMethod(String methodName) {if (methodName != null) {if ("getClass".equals(methodName)) {return false;}if (methodName.startsWith("get") || methodName.startsWith("is")|| methodName.startsWith("set")) {return true;}return false;}return false;}}

相關鏈接：http://mianhuaman.iteye.com/blog/1277240