在解決了excel兼容性問題之后, 終于可以開始制作這個小工具了, 這個工具主要是用來通過excel文件獲得JavaBean對象和Map映射的.
先說簡單的, 轉(zhuǎn)換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方法, 代碼中有一些過期的方法, 但是沒關(guān)系... 也有點(diǎn)懶不想找替代方法了
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());
}
}
接著是轉(zhuǎn)換成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;
}
總體上和轉(zhuǎn)換map的代碼差不多, 邏輯是一樣的, 為了裝逼用了泛型, 關(guān)鍵是map和javabean的轉(zhuǎn)換, 用了一個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;
}
以上代碼不是很嚴(yán)謹(jǐn), 希望看到問題的能交流一下, 也歡迎大家提問.
