基于注解形式Excel导入功能

/** * @ClassName: Excel * @Description: * @Author: 88578 * @Date: 2022/3/17 14:16 */ @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.FIELD) public @interface Excel { String value() default ""; } 1234567891011

@Data public class CbestSiteInfoBody implements Serializable { private static final long serialVersionUID = -4721795934875251002L; /** * 考点名称 */ @Excel("考点名称") private String siteName; } 12345678910

import com.techhero.project.emergency.annotation.Excel; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.util.Assert; import org.springframework.util.StringUtils; import org.springframework.web.multipart.MultipartFile; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.lang.reflect.Type; import java.text.SimpleDateFormat; import java.util.*; import java.util.concurrent.ConcurrentHashMap; /** * @ClassName: AnnExcelUtils * @Description: Excel基于注解形式解析工具类 * @Author: 88578 * @Date: 2022/3/17 16:10 */ public class AnnExcelUtils<T> { /** * 2003版本的excel */ private final static String excel2003L = "xls"; /** * 2007版本的excel */ private final static String excel2007U = "xlsx"; Class<T> clazz; public AnnExcelUtils(Class<T> clazz) { this.clazz = clazz; } public Collection<T> importExcel(MultipartFile file, String... pattern) { Assert.hasText(file.getOriginalFilename(), "未选择选择文件"); String type = StringUtils.getFilenameExtension(file.getOriginalFilename()); Collection<T> out = new ArrayList<T>(); try { Field filed[] = clazz.getDeclaredFields(); Map<String, Method> conMap = new ConcurrentHashMap<String, Method>(); for (int i = 0; i < filed.length; i++) { Field f = filed[i]; Excel ex = f.getAnnotation(Excel.class); if (ex != null) { String fieldname = f.getName(); String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1); Method setMethod = clazz.getMethod(setMethodName, new Class[]{f.getType()}); conMap.put(ex.value(), setMethod); } } Workbook book = null; if (excel2003L.equals(type)) { book = new HSSFWorkbook(file.getInputStream()); } else if (excel2007U.equals(type)) { book = new XSSFWorkbook(file.getInputStream()); } else { throw new Exception("解析的文件格式有误！"); } Sheet sheet = book.getSheetAt(0); Iterator<Row> row = sheet.rowIterator(); Row title = row.next(); Iterator<Cell> cellTitle = title.cellIterator(); Map<Integer, String> conTitle = new ConcurrentHashMap<Integer, String>(); int i = 0; while (cellTitle.hasNext()) { Cell cell = cellTitle.next(); String value = cell.getStringCellValue(); conTitle.put(i, value); i = i + 1; } //用来格式化日期 SimpleDateFormat sf; if (pattern.length < 1) { sf = new SimpleDateFormat("yyyy-MM-dd"); } else { sf = new SimpleDateFormat(pattern[0]); } while (row.hasNext()) { Row rown = row.next(); Iterator<Cell> cellbody = rown.cellIterator(); // 得到传入类的实例 T tObject = clazz.newInstance(); int k = 0; while (cellbody.hasNext()) { Cell cell = cellbody.next(); String titleString = (String) conTitle.get(k); if (conMap.containsKey(titleString)) { Method setMethod = (Method) conMap.get(titleString); Type[] ts = setMethod.getGenericParameterTypes(); String xclass = ts[0].toString(); cell.setCellType(CellType.STRING); if (xclass.equals("class java.lang.String")) { setMethod.invoke(tObject, cell.getStringCellValue()); } else if (xclass.equals("class java.util.Date")) { setMethod.invoke(tObject, sf.parse(cell.getStringCellValue())); } else if (xclass.equals("class java.lang.Boolean")) { Boolean boolname = true; if (cell.getStringCellValue().equals("否")) { boolname = false; } setMethod.invoke(tObject, boolname); } else if (xclass.equals("class java.lang.Integer")) { setMethod.invoke(tObject, new Integer(cell.getStringCellValue())); } else if (xclass.equals("class java.lang.Long")) { setMethod.invoke(tObject, new Long(cell.getStringCellValue())); } } k = k + 1; } out.add(tObject); } } catch (Exception e) { e.printStackTrace(); return null; } return out; } }