package com.dp.util;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* Excel通用处理器
* @author jkxydp
*
*/
public class AllPurposeExcelProcesser {
/**
* 通过调用POI读Excel的较为通用的方式,其中约定:excel的第一行中每列的内容为字符串,并且与你定义的model中的字段相对应,你的类遵循JavaBean标准
* @param <T> 你希望读取后组装的数据结构定义
* @param excel 你要读取的文件
* @param modelType 你希望组装的数据的定义的字节码,该字节码描述中必须包含一个无参构造器
* @param sheetName 你要读取的文件中的工作表的名称
* @return 一个装载了读取后获得对象的List
* @throws FileNotFoundException
* @throws IOException
* @throws IllegalArgumentException
* @throws SecurityException
* @throws InstantiationException
* @throws IllegalAccessException
* @throws InvocationTargetException
* @throws NoSuchMethodException
*/
public static <T> List<T> read(File excel,Class<T> modelType,String sheetName) throws FileNotFoundException, IOException, IllegalArgumentException, SecurityException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException{
Method[] methods = modelType.getMethods();
Workbook wb = getWorkBook(excel);
if(wb == null)
return null;
List<T> models = new ArrayList<T>(); //创建容器,用于装置所有读取出来的model
Sheet sheet = wb.getSheet(sheetName); //根据提供的sheet名称拿到整张表
int rows = sheet.getPhysicalNumberOfRows();
Row zeroRow = sheet.getRow(0); //拿到第零行表格,解析列与字段之间的对应关系
Map<Integer,Method> colMark = new HashMap<Integer, Method>(); //存储方法与字段的对应关系
for(short i = 0; i < zeroRow.getLastCellNum(); i ++){
Cell tip = zeroRow.getCell(i);
if(null != tip && tip.getCellType() == Cell.CELL_TYPE_STRING) {
String curTipName = tip.getStringCellValue();
for(int j = 0; j < methods.length; j ++) {
if(("set" + curTipName.toUpperCase().charAt(0) + curTipName.substring(1)).equals(methods[j].getName())) {
colMark.put((int)i, methods[j]);
}
}
}
}
Cell curCell = null;
boolean flag = true;
for(int i = 1; i < rows; i ++) {
Row aModel = sheet.getRow(i);
if(null != aModel) {
T model = modelType.getConstructor().newInstance();
for(int col = 0; col < colMark.size(); col ++) {
curCell = aModel.getCell(col);
if(null != curCell){
switch (curCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
case Cell.CELL_TYPE_FORMULA:
case Cell.CELL_TYPE_BLANK:
if(colMark.get(col).getParameterTypes()[0] == String.class) {
colMark.get(col).invoke(model, curCell.getStringCellValue());
} else {
flag = false;
}
break;
case Cell.CELL_TYPE_NUMERIC:
if(colMark.get(col).getParameterTypes()[0] == int.class || colMark.get(col).getParameterTypes()[0] == Integer.class) {
colMark.get(col).invoke(model, (int)Math.round(curCell.getNumericCellValue()));
} else if (colMark.get(col).getParameterTypes()[0] == Double.class || colMark.get(col).getParameterTypes()[0] == double.class) {
colMark.get(col).invoke(model, curCell.getNumericCellValue());
} else if(colMark.get(col).getParameterTypes()[0] == Float.class || colMark.get(col).getParameterTypes()[0] == float.class) {
colMark.get(col).invoke(model, (float)curCell.getNumericCellValue());
} else if(colMark.get(col).getParameterTypes()[0] == Long.class || colMark.get(col).getParameterTypes()[0] == long.class) {
colMark.get(col).invoke(model, Math.round(curCell.getNumericCellValue()));
} else if(colMark.get(col).getParameterTypes()[0] == Short.class || colMark.get(col).getParameterTypes()[0] == short.class) {
colMark.get(col).invoke(model, (short)Math.round(curCell.getNumericCellValue()));
} else if(colMark.get(col).getParameterTypes()[0] == Byte.class || colMark.get(col).getParameterTypes()[0] == byte.class) {
colMark.get(col).invoke(model, (byte)Math.round(curCell.getNumericCellValue()));
} else if(colMark.get(col).getParameterTypes()[0] == Date.class) {
colMark.get(col).invoke(model, curCell.getDateCellValue());
}else {
flag = false;
}
break;
case Cell.CELL_TYPE_BOOLEAN:
if(colMark.get(col).getParameterTypes()[0] == Boolean.class || colMark.get(col).getParameterTypes()[0] == boolean.class) {
colMark.get(col).invoke(model, curCell.getBooleanCellValue());
} else {
flag = false;
}
break;
default:
break;
}
if(!flag) break;
}
}
if(flag) models.add(model);
flag = true;
}
}
return models;
}
/**
* Excel处理2003与2007差异
*/
private static Workbook getWorkBook(File excel) throws FileNotFoundException, IOException {
return excel.getName().endsWith("xls") ?
new HSSFWorkbook(new BufferedInputStream(new FileInputStream(excel))) :
excel.getName().endsWith("xlsx") ?
new XSSFWorkbook(new BufferedInputStream(new FileInputStream(excel))):null;
}
}
这是今天上班的时候工作做完了,无聊时写的一个相对通用的基于POI3.6的读取Excel的类,今后还会把它完善。
今后无聊就写点工具类,试着实现一个C/S模式分布式的MVC框架,希望我的理想不是梦!