Java实现任意类Excel导入导出

实际应用中，Excel导入导出很常见的操作，实例通过Java反射和注解的机制实现任意类Excel操作。
注解可理解为程序的标记语言，无任何语义，Java虚拟机不解释执行该行代码。编程人员可运用这一特性为特定的方法，属性，类加上自定义语义，在利用Java的反射机制field.getAnnotation实现自己的逻辑判断，比如本实例中@Excel注解标记类属性是否为Excel操作字段。

package annotate;

import java.lang.annotation.Retention;
import java.lang.annotation.Target;
import java.lang.annotation.ElementType;
import java.lang.annotation.RetentionPolicy;

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
//excel表格注解     
public @interface Excel {
	//字段excel表头名称
	public String name() default "";
	
	//字段是否为必填项
	public boolean require() default false;
	
}

package test;

import annotate.Excel;

public class Student {
@Excel(name="编码",require=true)
private int id;
@Excel(name="姓名",require=true)
private String name;
@Excel(name="性别",require=true)
private String sex;
@Excel(name="年龄")
private int age;
private int num;
public Student(){

}
public Student(int id, String name, String sex, int age) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
}

public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getSex() {
return sex;
}

public void setSex(String sex) {
this.sex = sex;
}

public int getAge() {
return age;
}

public void setAge(int age) {
this.age = age;
}

@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + "]";
}
}

package test;

import java.io.File;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import annotate.Excel;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import unity.SpellHelper;


@SuppressWarnings("unchecked")
public class excel {

    public static void main(String[] args){
		List<Student> students = new ArrayList<Student>();
		for(int i=0;i<10;i++){
			Student student = new Student(i, "测试"+i,i%2==0?"男":"女" , i*10);
			students.add(student);
		}
		out(Student.class,new File("D:\\学生信息.xls"),students, "学生信息","红色为必填项\n编码为数字\n");
		List<Student> list = (List<Student>)in(new File("D:\\学生信息.xls"),Student.class);
		//JDK1.8 Lambda表达式
		list.stream().forEach(student -> System.out.println(student.toString()));
    }
    
	/***
	 * 导入Excel
	 * @param file Excel文件
	 * @param classz 实例类
	 * @return
	 */
    public static Object in(File file,Class<?> classz){
		int i=0,j=0;
    	try {
    		Workbook rwb=Workbook.getWorkbook(file);
    		Sheet rs=rwb.getSheet(0);
            int clos=rs.getColumns();//得到所有的列
            int rows=rs.getRows();//得到所有的行
            Map<String, String> fieldMap = new HashMap<String, String>();
            StringBuffer fieldNames = new StringBuffer("");
            for(Field field: classz.getDeclaredFields()){
            	if(field.isAnnotationPresent(Excel.class)){
            		Excel excel = field.getAnnotation(Excel.class);
            		fieldMap.put(excel.name(), field.getName());
            	}
            }
            for(j=0;j<clos;j++){
            	String name = rs.getCell(j, 0).getContents();
            	fieldNames.append(fieldMap.get(name.trim())+",");
            }
            if(fieldNames.length()>1)
            	fieldNames.setLength(fieldNames.length()-1);
            String filedNames[] = fieldNames.toString().split(",");
            List<Object> list = new ArrayList<Object>();
            for (i = 1; i < rows; i++) {
            	Object entity = classz.newInstance();
                for (j = 0; j < clos; j++) {
                	Field field = classz.getDeclaredField(filedNames[j]);
                	String data = rs.getCell(j, i).getContents().trim();
                	Excel excel = field.getAnnotation(Excel.class);
                	if((null == data || data.isEmpty()) && field.getAnnotation(Excel.class).require())
                		return "第"+(i+1)+"行"+(j+1)+"必填项不能为空值";
                	if("性别".equals(excel.name().trim()) && !("男".equals(data) || "女".equals(data)))
                		return "性别只能为男，女"; 
                	Object object = null;
                	Method method = classz.getMethod("set"+SpellHelper.captureName(filedNames[j]),field.getType());
                	if(null != data && !data.isEmpty()){
                		//单元格数据类型校验
                    	if(field.getType() == int.class || field.getType() == Integer.class)
                    		object = Integer.parseInt(data);
                    	else if(field.getType() == long.class || field.getType() == Long.class)
                    		object = Long.parseLong(data);
                    	else if(field.getType() == short.class || field.getType() == Short.class)
                    		object = Short.parseShort(data);
                    	else if(field.getType() == float.class || field.getType() == Float.class)
                    		object = Float.parseFloat(data);
                    	else if(field.getType() == double.class || field.getType() == Double.class)
                    		object = Double.parseDouble(data);
                    	//日期格式校验
                    	else if(filedNames[j].endsWith("date") && excel.require()){
                    		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    		sdf.parse(data);
                    		object = data;
                    	}
                    	else
                    		object = data;
                    	method.invoke(entity, object);
                	}
                }
            	list.add(entity);
            }
            return list;
            
    	} catch (Exception e) {
    		e.printStackTrace();
    		return "第"+(i+1)+"行"+(j+1)+"列数据格式不正确";
		}
    }
    
    /***
	 * 导出Excel
	 * @param file Excel文件
	 * @param obj 待导出数据（只支持列表或实体）
	 * @param sheetName（Excel表Sheet名称）
	 * @param 格式说明
	 */
	public static void out(Class<?> clazz,File file,Object obj,String sheetName,String str_intro){
		try {
			List<Object> list;
			//传入对象是否为列表
			if(obj instanceof List){
				list = (List<Object>)obj;
			}
			else{
				list = new ArrayList<Object>();
				list.add(obj);
			}
			if (!file.exists()) {
				file.getParentFile().mkdirs();
                file.createNewFile();
            }
			WritableWorkbook wwb = Workbook.createWorkbook(file);
        	//创建工作表
            WritableSheet ws = wwb.createSheet(sheetName, 0);
            //Excel字体样式
            WritableFont blackFont = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);  
            WritableFont redFont = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);  
            //设置为文本格式
            WritableCellFormat blackFormat = new WritableCellFormat(NumberFormats.TEXT);
            WritableCellFormat redFormat = new WritableCellFormat(NumberFormats.TEXT);
            blackFormat.setFont(blackFont);
            redFormat.setFont(redFont);
			Field[] fields = clazz.getDeclaredFields();
			//注解@excel属性集合
			StringBuffer fileNames = new StringBuffer();
			//Excel默认行宽高
            ws.getSettings().setDefaultColumnWidth(15);
            ws.getSettings().setDefaultRowHeight(300);
			//写入表头信息
			int col=0;
			for(Field field : fields){
				if (field.isAnnotationPresent(Excel.class)) {  
                	Excel excel = field.getAnnotation(Excel.class);
                	fileNames.append("get"+SpellHelper.captureName(field.getName())+",");
                	ws.addCell(new Label(col++,0,excel.name(),excel.require()?redFormat:blackFormat));
                }  
			}
            //写入元数据
            for(int i=0;i<list.size();i++){
            	setCell(ws,blackFormat,i+1,list.get(i),fileNames.toString().split(","));
            }
            //格式说明表
            WritableSheet intro = wwb.createSheet("格式说明", 1);
            //合并单元格
            intro.mergeCells(0, 0, 20, 20);
            WritableFont introFont = new WritableFont(WritableFont.ARIAL,14,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);  
            WritableCellFormat introFormat = new WritableCellFormat(introFont);  
            //自动换行
            introFormat.setWrap(true);
            //顶部对齐
            introFormat.setVerticalAlignment(VerticalAlignment.TOP);
            //提示信息
            intro.addCell(new Label(0, 0, str_intro, introFormat));
            wwb.write();
            wwb.close();
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
		}
		
	}
	
	/***
	 * 初始化excel单元格内容
	 * @param ws Excel单元页Sheet名称
	 * @param row 单元格行号
	 * @param obj 待写入对象
	 * @param fieldName 待写入对象属性名称集合
	 */
	private static void setCell(WritableSheet ws,WritableCellFormat format,int row,Object obj,String[] fieldName){
		try {
			for(int i=0;i<fieldName.length;i++){
				if(!fieldName[i].isEmpty()){
					Method method = obj.getClass().getMethod(fieldName[i], new Class[]{});
					Object value = method.invoke(obj, new Object[]{});
					ws.addCell(new Label(i,row,value==null?"":String.valueOf(value),format));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
		}
	}
}

/***
   * 首字母大写（通过ASCII码实现）
   * @param name
   * @return
   */
  public static String captureName(String name) {
   char[] cs=name.toCharArray();
         cs[0]-=32;
         return String.valueOf(cs); 
  }