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

总结

以上是生活随笔为你收集整理的java excel上传--poi的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。