在解决了excel兼容性问题之后, 终于可以开始制作这个小工具了, 这个工具主要是用来通过excel文件获得JavaBean对象和Map映射的.
先说简单的, 转换map
public static List<Map<String,Object>> importExcel(InputStream in) throws Exception {
List<Map<String,Object>> mapList = new ArrayList<Map<String,Object>>();
Workbook workbook = WorkbookFactory.create(in);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
List<String> keys = new ArrayList<String>();
for(int i = 0; i < row.getLastCellNum(); i++){
Cell cell = row.getCell(i);
keys.add(String.valueOf(getValue(cell)));
}
for(int i = 0; i < sheet.getLastRowNum(); i++){
Row currentRow = sheet.getRow(i + 1);
Map<String, Object> map = new HashMap<String, Object>();
for(int j = 0; j < currentRow.getLastCellNum(); j++){
map.put(keys.get(j), getValue(currentRow.getCell(j)));
}
mapList.add(map);
}
return mapList;
}
代码比较简单, 不多解释, 为了让excel单元格取值简单, 封装了一个getValue方法, 代码中有一些过期的方法, 但是没关系... 也有点懒不想找替代方法了
private static Object getValue(Cell cell) {
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return cell.getBooleanCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return cell.getNumericCellValue();
} else {
return String.valueOf(cell.getStringCellValue());
}
}
接着是转换成JavaBean的方法
public static <T> List<T> importExcel(InputStream in, Class<T> c) throws Exception {
List<T> list = new ArrayList<T>();
Workbook workbook = WorkbookFactory.create(in);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
List<String> keys = new ArrayList<String>();
for(int i = 0; i < row.getLastCellNum(); i++){
Cell cell = row.getCell(i);
keys.add(getValue(cell));
}
for(int i = 0; i < sheet.getLastRowNum(); i++){
Row currentRow = sheet.getRow(i + 1);
Map<String, String> map = new HashMap<String, String>();
for(int j = 0; j < currentRow.getLastCellNum(); j++){
map.put(keys.get(j), getValue(currentRow.getCell(j)));
}
T t = mapToObject(c,map);
list.add(t);
}
return list;
}
总体上和转换map的代码差不多, 逻辑是一样的, 为了装逼用了泛型, 关键是map和javabean的转换, 用了一个mapToObject的方法
private static <T> T mapToObject(Class<T> c,Map<String, Object> map) throws Exception {
BeanInfo beanInfo = Introspector.getBeanInfo(c);
T t = c.newInstance();
PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
for(int i = 0; i < propertyDescriptors.length; i++){
PropertyDescriptor descriptor = propertyDescriptors[i];
String propertyName = descriptor.getName();
if(map.containsKey(propertyName)){
Object value = map.get(propertyName);
Object[] args = new Object[1];
args[0] = value;
//这里捕获异常为了让不正常的值可以暂时跳过不影响正常字段的赋值
try {
descriptor.getWriteMethod().invoke(t, args);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
return t;
}
以上代码不是很严谨, 希望看到问题的能交流一下, 也欢迎大家提问.
