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;}@Overridepublic 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        }    }}