文章詳情頁

Java使用poi做加自定義注解實現對象與Excel相互轉換

瀏覽：102日期：2022-05-22 08:31:31

引入依賴

maven

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version></dependency>

Gradle

implementation group: ’org.apache.poi’, name: ’poi’, version: ’3.17’代碼展示

1、自定義注解類

@Retention(value = RetentionPolicy.RUNTIME)@Target(value = ElementType.FIELD)public @interface Excel { String name();//列的名字 int width() default 6000;//列的寬度 int index() default -1;//決定生成的順序 boolean isMust() default true; // 是否為必須值,默認是必須的}

2、Java的Excel對象，只展現了field，get與set方法就忽略了

public class GoodsExcelModel { @Excel(name = 'ID_禁止改動', index = 0, width = 0) private Long picId;//picId @Excel(name = '產品ID_禁止改動', index = 1, width = 0) private Long productId; @Excel(name = '型號', index = 3) private String productName;//產品型號 @Excel(name = '系列', index = 2) private String seriesName;//系列名字 @Excel(name = '庫存', index = 5) private Long quantity; @Excel(name = '屬性值', index = 4) private String propValue; @Excel(name = '價格', index = 6) private Double price; @Excel(name = '商品編碼', index = 7, isMust = false) private String outerId; @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long dbId; // 數據庫自增長id private Date createTime; // 記錄創建時間 }

3、Excel表格與對象轉換的工具類，使用時指定泛型參數和泛型的class即可

public class ExcelUtil { private static final String GET = 'get'; private static final String SET = 'set'; private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class); /** * 將對象轉換成Excel * * @param objList 需要轉換的對象 * @return 返回是poi中的對象 */ public static HSSFWorkbook toExcel(List objList) {if (CollectionUtils.isEmpty(objList)) throw new NullPointerException('無效的數據');Class aClass = objList.get(0).getClass();Field[] fields = aClass.getDeclaredFields();HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet();for (int i = 0; i < objList.size(); i++) { HSSFRow row = sheet.createRow(i + 1);//要從第二行開始寫 HSSFRow topRow = null; if (i == 0) topRow = sheet.createRow(0); for (Field field : fields) {Excel excel = field.getAnnotation(Excel.class);//得到字段是否使用了Excel注解if (excel == null) continue;HSSFCell cell = row.createCell(excel.index());//設置當前值放到第幾列String startName = field.getName().substring(0, 1);String endName = field.getName().substring(1, field.getName().length());String methodName = new StringBuffer(GET).append(startName.toUpperCase()).append(endName).toString();try { Method method = aClass.getMethod(methodName);//根據方法名獲取方法,用于調用 Object invoke = method.invoke(objList.get(i)); if (invoke == null) continue; cell.setCellValue(invoke.toString());} catch (NoSuchMethodException e) { e.printStackTrace();} catch (IllegalAccessException e) { e.printStackTrace();} catch (InvocationTargetException e) { e.printStackTrace();}if (topRow == null) continue;HSSFCell topRowCell = topRow.createCell(excel.index());topRowCell.setCellValue(excel.name());sheet.setColumnWidth(excel.index(), excel.width()); }}return workbook; } /** * 將Excel文件轉換為指定對象 * * @param file 傳入的Excel * @param c 需要被指定的class * @return * @throws IOException * @throws IllegalAccessException * @throws InstantiationException */ public static <T> List<T> excelFileToObject(MultipartFile file, Class<T> c) throws IOException, IllegalAccessException, InstantiationException {//key為反射得到的下標,value為對于的set方法Map<Integer, String> methodMap = new HashMap<>();//保存第一列的值與對應的下標,用于驗證用戶是否刪除了該列,key為下標,value為名字Map<Integer, String> startRowNameMap = new HashMap<>();//用來記錄當前參數是否為必須的Map<Integer, Boolean> fieldIsMustMap = new HashMap<>();//得到所有的字段Field[] fields = c.getDeclaredFields();for (Field field : fields) { Excel excel = field.getAnnotation(Excel.class); if (excel == null) continue; String startName = field.getName().substring(0, 1); String endName = field.getName().substring(1, field.getName().length()); String methodName = new StringBuffer(SET).append(startName.toUpperCase()).append(endName).toString(); methodMap.put(excel.index(), methodName); startRowNameMap.put(excel.index(), excel.name()); fieldIsMustMap.put(excel.index(), excel.isMust());}String fileName = file.getOriginalFilename();Workbook wb = fileName.endsWith('.xlsx') ? new XSSFWorkbook(file.getInputStream()) : new HSSFWorkbook(file.getInputStream());Sheet sheet = wb.getSheetAt(0);Row sheetRow = sheet.getRow(0);for (Cell cell : sheetRow) { Integer columnIndex = cell.getColumnIndex(); if (cell.getCellTypeEnum() != CellType.STRING) throw new ExcelException('excel校驗失敗, 請勿刪除文件中第一行數據 !!!'); String value = cell.getStringCellValue(); String name = startRowNameMap.get(columnIndex); if (name == null) throw new ExcelException('excel校驗失敗,請勿移動文件中任何列的順序!!!'); if (!name.equals(value)) throw new ExcelException('excel校驗失敗,【' + name + '】列被刪除,請勿刪除文件中任何列 !!!');}sheet.removeRow(sheetRow);//第一行是不需要被反射賦值的List<T> models = new ArrayList<>();for (Row row : sheet) { if (row == null || !checkRow(row)) continue; T obj = c.newInstance();//創建新的實例化對象 Class excelModelClass = obj.getClass(); startRowNameMap.entrySet().forEach(x -> {Integer index = x.getKey();Cell cell = row.getCell(index);String methodName = methodMap.get(index);if (StringUtils.isEmpty(methodName)) return;List<Method> methods = Lists.newArrayList(excelModelClass.getMethods()).stream().filter(m -> m.getName().startsWith(SET)).collect(Collectors.toList());String rowName = startRowNameMap.get(index);//列的名字for (Method method : methods) { if (!method.getName().startsWith(methodName)) continue; //檢測value屬性 String value = valueCheck(cell, rowName, fieldIsMustMap.get(index)); //開始進行調用方法反射賦值 methodInvokeHandler(obj, method, value);} }); models.add(obj);}return models; } /** * 檢測當前需要賦值的value * * @param cell 當前循環行中的列對象 * @param rowName 列的名字{@link Excel}中的name * @param isMust 是否為必須的 * @return 值 */ private static String valueCheck(Cell cell, String rowName, Boolean isMust) {//有時候刪除單個數據會造成cell為空,也可能是value為空if (cell == null && isMust) { throw new ExcelException('excel校驗失敗,【' + rowName + '】中的數據禁止單個刪除');}if (cell == null) return null;cell.setCellType(CellType.STRING);String value = cell.getStringCellValue();if ((value == null || value.trim().isEmpty()) && isMust) { throw new ExcelException('excel校驗失敗,【' + rowName + '】中的數據禁止單個刪除');}return value; } /** * 反射賦值的處理的方法 * * @param obj 循環創建的需要賦值的對象 * @param method 當前對象期中一個set方法 * @param value 要被賦值的內容 */ private static void methodInvokeHandler(Object obj, Method method, String value) {Class<?> parameterType = method.getParameterTypes()[0];try { if (parameterType == null) {method.invoke(obj);return; } String name = parameterType.getName(); if (name.equals(String.class.getName())) {method.invoke(obj, value);return; } if (name.equals(Long.class.getName())) {method.invoke(obj, Long.valueOf(value));return; } if (name.equals(Double.class.getName())) {method.invoke(obj, Double.valueOf(value)); }} catch (IllegalAccessException e) { e.printStackTrace();} catch (InvocationTargetException e) { e.printStackTrace();} } private static boolean checkRow(Row row) {try { if (row == null) return false; short firstCellNum = row.getFirstCellNum(); short lastCellNum = row.getLastCellNum(); if (firstCellNum < 0 && lastCellNum < 0) return false; if (firstCellNum != 0) {for (short i = firstCellNum; i < lastCellNum; i++) { Cell cell = row.getCell(i); String cellValue = cell.getStringCellValue(); if (!StringUtils.isBlank(cellValue)) return true;}return false; } return true;} catch (Exception e) { return true;} }

4、導出Excel與導入Excel的示例代碼