工作中很多批量上传需求不同，每个需求都要写一次批量上传代码，太烦。。决定写一个通用工具，此代码复制即用。需要改进的地方请评论区留言
方法调用传参例子：
上传文件中列的字段对应的是对象的属性
String[] fieldsName = new String[]{"carModelName","salePrice"}; 此参数是上传文件中列必传字段对应对象的属性
String[] fieldsIsNull = new String[]{"carModelName"}; 注：数组属性顺序必须和文件中顺序一样，解析文件时赋值不会赋错字段
public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
public static final String OFFICE_EXCEL_2007_POSTFIX = "xlsx";
public static final String EMPTY = "";
public static final String POINT = ".";/
**
* 通过反射处理以获得的数据
* @param
*/
public T reflectDeal(T className, Cell cell, String fieldName){
Class aClass = null;
try {
aClass = className.getClass();
Field[] declaredFields = aClass.getDeclaredFields();
for (Field field:declaredFields) {
field.setAccessible(true);
String names = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1, fieldName.length());
if(field.getName().equals(fieldName) && field.getType().getName().equals("java.math.BigDecimal")){
aClass.getMethod("set"+names,BigDecimal.class).invoke(className,new BigDecimal(""+cell));
}else if(field.getName().equals(fieldName) && field.getType().getName().equals("java.lang.String")){
aClass.getMethod("set"+names,String.class).invoke(className,cell.getStringCellValue().toString());
}else if(field.getName().equals(fieldName) && field.getType().getName().equals("java.lang.Integer")){
aClass.getMethod("set"+names,Integer.class).invoke(className,cell);
}else if(field.getName().equals(fieldName) && field.getType().getName().equals("java.lang.Double")){
aClass.getMethod("set"+names,Double.class).invoke(className,cell);
}
}
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return className;
}
/**
* 本段代码解析xls文件
* @param
*/
public List<T> analysisXls(InputStream in,Class<?> clazz,String[] fieldsName,String[] fieldsIsNull) {
int totalrows = 0;
int totalCell = 0;
List<T> list = new ArrayList();
logger.info("==================== 开始解析xls文件 =========================");
//流读取文件
//创建文件
HSSFWorkbook wb = null;
try {
wb = new HSSFWorkbook(in);
//读取页数
for(int num = 0 ; num < wb.getNumberOfSheets() ; num ++){
Sheet xs = wb.getSheetAt(num);
if(xs == null){
continue;
}
totalrows = xs.getLastRowNum()+1;
for (int rnum = 1 ; rnum < totalrows ; rnum ++){
boolean flag = false;
T o = null;
try {
o = (T)clazz.newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
Row row = xs.getRow(rnum);
if(row != null){
totalCell = row.getLastCellNum();
int nullCellNumb = 0;
for (int cnum = 0 ; cnum < totalCell ; cnum ++){
Cell cell = row.getCell(cnum);
if(null == cell){
nullCellNumb++;
if(nullCellNumb>=10){
break;
}
continue;
}
if( cell != null && !"".equals(cell.toString().trim())){
this.reflectDeal(o, cell, fieldsName[cnum]);
}else {
for (int x=0;x<fieldsIsNull.length;x++){
if(fieldsIsNull[x].equals(fieldsName[cnum])){
flag = true;
break;
}
}
}
}
if (flag){
continue;
}
list.add(o);
}else {
return list;
}
}
}
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
in.close();
if(null != wb){
wb.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return list;
}
/**
* 本段代码解析xlsx文件
* @param
* @param clazz
* @param fieldsName
* @return
*/
public List<T> analysisXlsx(InputStream in,Class<?> clazz,String[] fieldsName,String[] fieldsIsNull){
int totalrows = 0;
int totalCell = 0;
List<T> list = new ArrayList();
logger.info("==================== 开始解析xlsx文件 =========================");
//流读取文件
//创建文件
XSSFWorkbook wb = null;
try {
wb = new XSSFWorkbook(OPCPackage.open(in));
//读取页数
for(int num = 0 ; num < wb.getNumberOfSheets() ; num ++){
Sheet xs = wb.getSheetAt(num);
if(xs == null){
continue;
}
totalrows = xs.getLastRowNum()+1;
for (int rnum = 1 ; rnum < totalrows ; rnum ++){
boolean flag = false;
T o = (T)clazz.newInstance();
Row row = xs.getRow(rnum);
if(row != null){
totalCell = row.getLastCellNum();
int nullCellNumb = 0;
for (int cnum = 0 ; cnum < totalCell ; cnum ++){
Cell cell = row.getCell(cnum);
if(null == cell){
nullCellNumb++;
if(nullCellNumb>=10){
break;
}
continue;
}
if( cell != null && !"".equals(cell.toString().trim())){
nullCellNumb = 0;
this.reflectDeal(o, cell, fieldsName[cnum]);
}else {
for (int x=0;x<fieldsIsNull.length;x++){
if(fieldsIsNull[x].equals(fieldsName[cnum])){
flag = true;
break;
}
}
}
}
if (flag){
continue;
}
list.add(o);
}else {
return list;
}
}
}
} catch (IOException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
logger.info("======= XSSF创建文件失败 ====");
e.printStackTrace();
}finally {
try {
in.close();
if(null != wb){
wb.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return list;
}
/**
*此段代码区分e'xcel版本，分别调用哪个方法
* @param
* @param clazz
* @param fieldsName
* @return
* @throws IOException
*/
public List<T> readxlsAndXlsx(InputStream in,Class<?> clazz,String[] fieldsName,String fileName,String[] fieldsIsNull) {
if(null != in ){
String postfix = ExcelImport.getpostfix(fileName);
if(OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
return this.analysisXls(in,clazz,fieldsName,fieldsIsNull);
}else if(OFFICE_EXCEL_2007_POSTFIX.equals(postfix)){
return this.analysisXlsx(in,clazz,fieldsName,fieldsIsNull);
}else {
return null;
}
}
return null;
}
/**
* 获取文件后缀名
* @param path
* @return
*/
public static String getpostfix(String path){
if(path == null || EMPTY.equals(path.trim())){
return "";
}
if(path.contains(POINT)){
return path.substring(path.lastIndexOf(POINT)+1,path.length());
}
return "";
}
