數據庫 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 block
					e.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