实际应用中,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);
}