拿到excel 再去读取,很难受,搞个工具类,舒服的一,上代码记录一下:
方法调用实例:
@ApiOperation(value = "StaffInfoManageImport", notes = "") @ResponseBody @RequestMapping("/importExcel") @UserPermission(userIdentity="admin") @Transactional(rollbackFor=Exception.class) public Result importExcel(@RequestParam(name="file",required=false) MultipartFile multFile) { EmplUser emplUser = null; //导入错误行数 int flag = 1; DmDHrStaffInfo dmDHrStaffInfo = null; EmplUserPermission emplUserPermission = null; Result result = new Result(); try { //得到excel中需要导入的数据 StaffInfoManage s = new StaffInfoManage(); String fileName = multFile.getOriginalFilename(); Workbook wb = ImportExeclUtil.chooseWorkbook(fileName, multFile.getInputStream()); List<StaffInfoManage> readDateListT = ImportExeclUtil.readDateListT(wb, s, 2, 0); for (StaffInfoManage staf : readDateListT) { flag++; emplUser = new EmplUser(); dmDHrStaffInfo = new DmDHrStaffInfo(); emplUserPermission = new EmplUserPermission(); if(staf.getMail()!=null && !staf.getMail().equals("")){ emplUser.setUserId(staf.getMail());//邮箱号码 } if(staf.getMobilePhone()!=null && !staf.getMobilePhone().equals("")){ emplUser.setMobilePhone(staf.getMobilePhone());//移动电话 }else{ throw new ServiceException("邮箱和电话不能为空!!!"); } emplUser.setState("2");//填报状态,未填写 emplUser.setUserName(staf.getUserName());//姓名 emplUser.seteMail(staf.getMail());//邮箱 dmDHrStaffInfo.setMail(staf.getMail()); dmDHrStaffInfo.setTwolevelDepartment(staf.getTwolevelDepartment());//二级部门 dmDHrStaffInfo.setThreelevelmechanism(staf.getThreelevelmechanism());//三级机构 dmDHrStaffInfo.setPostDesc(staf.getPostDesc());//岗位名称 dmDHrStaffInfo.setExecutiveDirector(staf.getExecutiveDirector());//直属主管 dmDHrStaffInfo.setPostLevel(staf.getPostLevel());//岗位标准等级 dmDHrStaffInfo.setPositionLevel(staf.getPositionLevel());//个人职级 dmDHrStaffInfo.setEntryChannels(staf.getEntryChannels());//入职途径 emplUserService.save(emplUser); emplUserPermission.setUserId(emplUser.getUserId()); emplUserPermission.setPhone(staf.getMobilePhone()); emplUserPermission.setName(staf.getUserName()); emplUserPermission.setEmail(staf.getMail()); emplUserPermission.setAdmin("common"); emplUserPermissionService.save(emplUserPermission); dmDHrStaffInfo.setUserNo(emplUser.getUserId()); dmDHrStaffInfo.setUserId(emplUser.getUserId()); dmDHrStaffInfoService.save(dmDHrStaffInfo); } result.setMessage("导入成功"); result.setCode(ResultCode.SUCCESS); } catch (IOException e) { result.setMessage("第"+flag+"行数据错误,导入失败!"); result.setCode(ResultCode.FAIL); TransactionAspectSupport.currentTransactionStatus().setRollbackOnly(); return result; } catch (Exception e) { result.setMessage("第"+flag+"行数据错误,不允许添加重复数据!"); result.setCode(ResultCode.FAIL); TransactionAspectSupport.currentTransactionStatus().setRollbackOnly(); return result; } return result; }
下面是工具类:
package com.unicom.sh.pom.file.util; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Field; import java.math.BigDecimal; import java.text.DecimalFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Locale; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.unicom.sh.pom.file.IsNeeded; import com.unicom.sh.pom.file.IsRequired; import org.apache.commons.beanutils.PropertyUtils; /** * excel 工具类 * @author fz * @version 0.1 (2018年12月20日 上午10:51:42) * @since 0.1 * @see */ public class ImportExeclUtil { private static int totalRows = 0;// 总行数 private static int totalCells = 0;// 总列数 private static String errorInfo;// 错误信息 /** 无参构造方法 */ public ImportExeclUtil() { } public static int getTotalRows() { return totalRows; } public static int getTotalCells() { return totalCells; } public static String getErrorInfo() { return errorInfo; } /** * * 根据流读取Excel文件 * * * @param inputStream * @param isExcel2003 * @return * @see [类、类#方法、类#成员] */ public List<List<String>> read(InputStream inputStream, boolean isExcel2003) throws IOException { List<List<String>> dataLst = null; /** 根据版本选择创建Workbook的方式 */ Workbook wb = null; if (isExcel2003) { wb = new HSSFWorkbook(inputStream); } else { wb = new XSSFWorkbook(inputStream); } dataLst = readDate(wb); return dataLst; } /** * * 读取数据 * * @param wb * @return * @see [类、类#方法、类#成员] */ private List<List<String>> readDate(Workbook wb) { List<List<String>> dataLst = new ArrayList<>(); /** 得到第一个shell */ Sheet sheet = wb.getSheetAt(0); /** 得到Excel的行数 */ totalRows = sheet.getPhysicalNumberOfRows(); /** 得到Excel的列数 */ if (totalRows >= 1 && sheet.getRow(0) != null) { totalCells = sheet.getRow(0).getPhysicalNumberOfCells(); } for (int r = 0; r < totalRows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } List<String> rowLst = new ArrayList<>(); for (int c = 0; c < getTotalCells(); c++) { Cell cell = row.getCell(c); String cellValue = ""; if (null != cell) { // 以下是判断数据的类型 switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // 数字 cellValue = cell.getNumericCellValue() + ""; break; case HSSFCell.CELL_TYPE_STRING: // 字符串 cellValue = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean cellValue = cell.getBooleanCellValue() + ""; break; case HSSFCell.CELL_TYPE_FORMULA: // 公式 cellValue = cell.getCellFormula() + ""; break; case HSSFCell.CELL_TYPE_BLANK: // 空值 cellValue = ""; break; case HSSFCell.CELL_TYPE_ERROR: // 故障 cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } } rowLst.add(cellValue); } /** 保存第r行的第c列 */ dataLst.add(rowLst); } return dataLst; } /** * * 按指定坐标读取实体数据 <按顺序放入带有注解的实体成员变量中> * * @param wb * 工作簿 * @param t * 实体 * @param in * 输入流 * @param integers * 指定需要解析的坐标 * @return T 相应实体 * @throws IOException * @throws Exception * @see [类、类#方法、类#成员] */ @SuppressWarnings("unused") public static <T> T readDateT(Workbook wb, T t, InputStream in, Integer[]... integers) throws Exception { // 获取该工作表中的第一个工作表 Sheet sheet = wb.getSheetAt(0); // 成员变量的值 Object entityMemberValue = ""; // 所有成员变量 Field[] fields = t.getClass().getDeclaredFields(); // 列开始下标 int startCell = 0; /** 循环出需要的成员 */ for (int f = 0; f < fields.length; f++) { fields[f].setAccessible(true); String fieldName = fields[f].getName(); boolean fieldHasAnno = fields[f].isAnnotationPresent(IsNeeded.class); // 有注解 if (fieldHasAnno) { IsNeeded annotation = fields[f].getAnnotation(IsNeeded.class); boolean isNeeded = annotation.isNeeded(); // Excel需要赋值的列 if (isNeeded) { // 获取行和列 int x = integers[startCell][0] - 1; int y = integers[startCell][1] - 1; Row row = sheet.getRow(x); Cell cell = row.getCell(y); if (row == null) { continue; } // Excel中解析的值 String cellValue = getCellValue(cell); // 需要赋给成员变量的值 entityMemberValue = getEntityMemberValue(entityMemberValue, fields, f, cellValue); // 赋值 PropertyUtils.setProperty(t, fieldName, entityMemberValue); // 列的下标加1 startCell++; } } } return t; } /** * * 读取列表数据 <按顺序放入带有注解的实体成员变量中> * * @param wb * 工作簿 * @param t * 实体 * @param beginLine * 开始行数 * @param totalcut * 结束行数减去相应行数 * @return List<T> 实体列表 * @throws Exception * @see [类、类#方法、类#成员] */ @SuppressWarnings("unchecked") public static <T> List<T> readDateListT(Workbook wb, T t, int beginLine, int totalcut) throws Exception { List<T> listt = new ArrayList<>(); /** 得到第一个shell */ Sheet sheet = wb.getSheetAt(0); /** 得到Excel的行数 */ totalRows = sheet.getPhysicalNumberOfRows(); /** 得到Excel的列数 */ if (totalRows >= 1 && sheet.getRow(0) != null) { totalCells = sheet.getRow(0).getPhysicalNumberOfCells(); } /** 循环Excel的行 */ for (int r = beginLine - 1; r < totalRows - totalcut; r++) { Object newInstance = t.getClass().newInstance(); Row row = sheet.getRow(r); if (row == null) { continue; } // 成员变量的值 Object entityMemberValue = ""; // 所有成员变量 Field[] fields = t.getClass().getDeclaredFields();//按照你的成员属性进行排序的 // 列开始下标 int startCell = 0; for (int f = 0; f < fields.length; f++) { fields[f].setAccessible(true); String fieldName = fields[f].getName(); boolean fieldHasAnno = fields[f].isAnnotationPresent(IsNeeded.class); // 有注解 if (fieldHasAnno) { IsNeeded annotation = fields[f].getAnnotation(IsNeeded.class); boolean isNeeded = annotation.isNeeded(); // Excel需要赋值的列 if (isNeeded) { Cell cell = row.getCell(startCell); String cellValue = getCellValue(cell); entityMemberValue = getEntityMemberValue(entityMemberValue, fields, f, cellValue); // 赋值 PropertyUtils.setProperty(newInstance, fieldName, entityMemberValue); // 列的下标加1 startCell++; } } } listt.add((T) newInstance); } return listt; } /** * * 根据Excel表格中的数据判断类型得到值 * * @param cell * @return * @see [类、类#方法、类#成员] */ private static String getCellValue(Cell cell) { String cellValue = ""; if (null != cell) { // 以下是判断数据的类型 switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // 数字 if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { Date theDate = cell.getDateCellValue(); SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd"); cellValue = dff.format(theDate); } else { DecimalFormat df = new DecimalFormat("0"); cellValue = df.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: // 字符串 cellValue = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean cellValue = cell.getBooleanCellValue() + ""; break; case HSSFCell.CELL_TYPE_FORMULA: // 公式 cellValue = cell.getCellFormula() + ""; break; case HSSFCell.CELL_TYPE_BLANK: // 空值 cellValue = ""; break; case HSSFCell.CELL_TYPE_ERROR: // 故障 cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } } return cellValue; } /** * * 根据实体成员变量的类型得到成员变量的值 * * @param realValue * @param fields * @param f * @param cellValue * @return * @see [类、类#方法、类#成员] */ private static Object getEntityMemberValue(Object realValue, Field[] fields, int f, String cellValue) { String type = fields[f].getType().getName(); switch (type) { case "char": case "java.lang.Character": case "java.lang.String": realValue = cellValue; break; case "java.util.Date": realValue = StringUtils.isBlank(cellValue) ? null : DateUtil.strToDate(cellValue, DateUtil.YYYY_MM_DD); break; case "java.lang.Integer": realValue = StringUtils.isBlank(cellValue) ? null : Integer.valueOf(cellValue); break; case "int": case "float": case "double": case "java.lang.Double": case "java.lang.Float": case "java.lang.Long": case "java.lang.Short": case "java.math.BigDecimal": realValue = StringUtils.isBlank(cellValue) ? null : new BigDecimal(cellValue); break; case "java.lang.Object": realValue = cellValue; break; default: break; } return realValue; } /** * * 根据路径或文件名选择Excel版本 * * * @param filePathOrName * @param in * @return * @throws IOException * @see [类、类#方法、类#成员] */ public static Workbook chooseWorkbook(String filePathOrName, InputStream in) throws IOException { Workbook wb = null; boolean isExcel2003 = ExcelVersionUtil.isExcel2003(filePathOrName); if (isExcel2003) { wb = new HSSFWorkbook(in); } else { wb = new XSSFWorkbook(in); } return wb; } static class ExcelVersionUtil { private ExcelVersionUtil() { } /** * * 是否是2003的excel,返回true是2003 * * * @param filePath * @return * @see [类、类#方法、类#成员] */ public static boolean isExcel2003(String filePath) { return filePath.matches("^.+\\.(?i)(xls)$"); } /** * * 是否是2007的excel,返回true是2007 * * * @param filePath * @return * @see [类、类#方法、类#成员] */ public static boolean isExcel2007(String filePath) { return filePath.matches("^.+\\.(?i)(xlsx)$"); } } /** * 日期格式化 * * @author sh-fangz3 * */ public static class DateUtil { public static final String YYYY_MM_DDHHMMSS = "yyyy-MM-dd HH:mm:ss"; public static final String YYYY_MM_DD = "yyyy-MM-dd"; public static final String YYYY_MM = "yyyy-MM"; public static final String YYYY = "yyyy"; public static final String YYYYMMDDHHMMSS = "yyyyMMddHHmmss"; public static final String YYYYMMDD = "yyyyMMdd"; public static final String YYYYMM = "yyyyMM"; public static final String YYYYMMDDHHMMSS_1 = "yyyy/MM/dd HH:mm:ss"; public static final String YYYY_MM_DD_1 = "yyyy/MM/dd"; public static final String YYYY_MM_1 = "yyyy/MM"; /** * * 自定义取值,Date类型转为String类型 * * @param date * 日期 * @param pattern * 格式化常量 * @return * @see [类、类#方法、类#成员] */ public static String dateToStr(Date date, String pattern) { SimpleDateFormat format = null; if (null == date) return null; format = new SimpleDateFormat(pattern, Locale.getDefault()); return format.format(date); } /** * 将字符串转换成Date类型的时间 * <hr> * * @param s * 日期类型的字符串<br> * datePattern :YYYY_MM_DD<br> * @return java.util.Date */ public static Date strToDate(String s, String pattern) { if (s == null) { return null; } Date date = null; SimpleDateFormat sdf = new SimpleDateFormat(pattern); try { date = sdf.parse(s); } catch (ParseException e) { e.printStackTrace(); } return date; } } /** * 对象与对象之间赋值 * @param origin * @param destination */ public static <T> T mergeObject(T origin, T destination) { if (origin == null || destination == null){ return null; } try { Field[] origins = origin.getClass().getDeclaredFields(); Field[] destinations = destination.getClass().getDeclaredFields(); for (int f = 0; f < destinations.length; f++) { origins[f].setAccessible(true); Object value = origins[f].get(origin); boolean fieldHasAnno = destinations[f].isAnnotationPresent(IsNeeded.class); if(fieldHasAnno){ destinations[f].setAccessible(true); destinations[f].set(destination, value); } } } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } return destination; } /** * 判断导入必填字段 */ public static <T> Integer isRequired(T t,int num,String isZhicheng,String devType,String businessType,String isRemind) { // 所有成员变量 Field[] fields = t.getClass().getDeclaredFields();//按照你的成员属性进行排序的 for (int f = 0; f < fields.length; f++) { boolean fieldHasAnno = fields[f].isAnnotationPresent(IsRequired.class); if(fieldHasAnno){ fields[f].setAccessible(true); String name = fields[f].getName(); Object value; try { value = fields[f].get(t); //当是否需要支撑选择否的时候,支撑经理和提醒时间非必填 if("否".equals(isZhicheng) && name.equals("zcManagers") || "否".equals(isRemind) && name.equals("remindDatel")){ IsRequired reuquied = fields[f].getAnnotation(IsRequired.class); if(reuquied.condition()){ continue; } } if(!"2".equals(devType) && name.equals("agentName")){ IsRequired formToken = fields[f].getAnnotation(IsRequired.class); if(!formToken.condition()&&StringUtil.isEmpty(value.toString())){ continue; } } if(businessType != null && !"基础产品-固网".equals(businessType) && name.equals("amount")){ IsRequired reuquied = fields[f].getAnnotation(IsRequired.class); if(!reuquied.condition()){ continue; } } if(StringUtil.isEmpty(value.toString())){ num++; } } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } } } return num; } }
导出注解工具类,也就是在你要读取的对应实体上加上的注解:
package com.unicom.sh.pom.file; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * 自定义注解用于文件导入工具类 * @author fz * @version 0.1 (2018年12月20日 上午10:52:14) * @since 0.1 * @see */ @Retention(value = RetentionPolicy.RUNTIME) @Target(value = {ElementType.FIELD}) public @interface IsNeeded { /** * 是否需要从解析excel赋值 * @return * true:需要 false:不需要 * @see [类、类#方法、类#成员] */ boolean isNeeded() default true; }
然后在实体类中加上注解就ok
完成
差点忘了pomxml 配置文件
<!-- poi操作excel --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.8</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.8</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.8</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> </dependency>