基于反射和注解的Excel解析工具Demo
原理
反射的运用部分
通过Class的Field,可以给对应Class的Field字段对应的属性设值
注解的运用部分
通过给实体类的属性加上注解,和Excel中的表头做关联,就可以在解析Excel的时候,更加灵活
需要的poi的包
需要依赖的解析Excel的jar包有:
poi-3.10-FINAL.jar
poi-ooxml-3.10-FINAL.jar
poi-ooxml-schemas-3.10-FINAL.jar
xmlbeans-2.6.0.jar
代码实现
创建注解
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUNTIME)
public @interface CellMapping {
String cellName() default "";
}
创建Excel文件
当然,需要注意的是,保存的时候,要存成.xls格式的,不要存.xlsx格式的。可能因为问题,解析不了。
对应的实体类:
public class Car {
@CellMapping(cellName = "汽车品牌")
public String name;
@CellMapping(cellName = "汽车类型")
public String type;
@Override
public String toString() {
return "[name=" + name + ", type=" + type + "]";
}
}
解析工具类:
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class FastExcel {
private Workbook workbook = null;
private Sheet sheet = null;
public FastExcel(String path) throws InvalidFormatException, IOException {
File file = new File(path);
workbook = WorkbookFactory.create(file);
sheet = workbook.getSheetAt(0);
}
public FastExcel(InputStream is) throws InvalidFormatException, IOException {
workbook = WorkbookFactory.create(is);
sheet = workbook.getSheetAt(0);
}
public <T> List<T> praseExcel(Class<T> clazz) {
List<T> rst = new ArrayList<>();
if (sheet == null)
return rst;
int firstRowNum = sheet.getFirstRowNum();
Row row = sheet.getRow(firstRowNum);
short lastCellNum = row.getLastCellNum();
// key:表头,value:对应的列数
Map<String, Integer> cellNames = getCellMapping(row, lastCellNum);
// key:映射的表头名字,value:对应的字段
Map<String, Field> annotations = getFeildMapping(clazz);
int lastRowNum = sheet.getLastRowNum();
Set<String> keys = cellNames.keySet();
try {
for (int rowIndex = (++firstRowNum); rowIndex <= lastRowNum; rowIndex++) {
T inst = clazz.newInstance();
Row r = sheet.getRow(rowIndex);
for (String key : keys) {
Field field = annotations.get(key);
if (field == null)
continue;
Integer col = cellNames.get(key);
Cell cel = r.getCell(col);
if (cel == null)
continue;
field.setAccessible(true);
String val = cel.getStringCellValue();
field.set(inst, val);
}
rst.add(inst);
}
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return rst;
}
/**
* 获取表头和列的映射关系
*
* @param row
* @param lastCellNum
* @return
*/
private Map<String, Integer> getCellMapping(Row row, short lastCellNum) {
// key:表头,value:对应的列数
Map<String, Integer> cellNames = new HashMap<>();
Cell cell;
for (int col = 0; col < lastCellNum; col++) {
cell = row.getCell(col);
String val = cell.getStringCellValue();
cellNames.put(val, col);
}
return cellNames;
}
/**
* 获取对象字段和Excel表头的字段映射关联
*
* @param clazz
* @return
*/
private <T> Map<String, Field> getFeildMapping(Class<T> clazz) {
// key:映射的表头名字,value:对应的字段
Map<String, Field> annotations = new HashMap<>();
Field[] fields = clazz.getDeclaredFields();
if (fields == null || fields.length < 1)
return annotations;
for (Field field : fields) {
CellMapping mapping = field.getAnnotation(CellMapping.class);
if (mapping == null) {
annotations.put(field.getName(), field);
} else {
annotations.put(mapping.cellName(), field);
}
}
return annotations;
}
}
运行结果:
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
public class CarTest {
public static void main(String[] args) {
try {
InputStream is = CarTest.class.getResourceAsStream("汽车.xls");
FastExcel fastExcel = new FastExcel(is);
List<Car> rst = fastExcel.praseExcel(Car.class);
for (Car car : rst) {
System.out.println(car.toString());
}
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
结论:
最终解析结果为:
[name=宝马, type=轿跑]
[name=奔驰, type=suv]
[name=兰博基尼, type=超跑]
[name=特斯拉, type=电动轿跑]
[name=长安, type=面包车]
将实体类字段和Excel表头做绑定,可以不用再写非常多的硬转换代码,提高解析Excel的效率。而且易于维护。
当然,当前只是一个Demo,还没有做到各种数据类型的支持,比如double,时间等等类型的支持。
先记录一下,再优化