java 上传 excel_java excel上传--poi

工作中很多批量上传需求不同，每个需求都要写一次批量上传代码，太烦。。决定写一个通用工具，此代码复制即用。需要改进的地方请评论区留言

方法调用传参例子：

上传文件中列的字段对应的是对象的属性

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 analysisXls(InputStream in,Class> clazz,String[] fieldsName,String[] fieldsIsNull) {

int totalrows = 0;

int totalCell = 0;

List 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

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 analysisXlsx(InputStream in,Class> clazz,String[] fieldsName,String[] fieldsIsNull){

int totalrows = 0;

int totalCell = 0;

List 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

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 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 "";

}