Java中Easypoi實現excel多sheet表導入導出功能

 <!-- 導出文件工具 EasyPoi實現Excel讀寫管理測試用例 -->
  <dependency>
   <groupId>cn.afterturn</groupId>
   <artifactId>easypoi-spring-boot-starter</artifactId>
   <version>4.2.0</version>
  </dependency>

@Setter
@Getter
@ToString
public class LoginCaseDto {
 @Excel(name = "flag(0是反向，1是正向)",orderNum = "1",width = 20)
 private String flag;
 @Excel(name = "urlid(訪問id)",orderNum = "2",width = 20)
 private String urlid;
 @Excel(name = "name(登錄賬號)",orderNum = "3",width = 20)
 private String name;
 @Excel(name = "pwd(登錄密碼)",orderNum = "4",width = 20)
 private String pwd;
 @Excel(name = "desc(期望提示語)",orderNum = "5",width = 40)
 private String desc;
 @Excel(name = "actual(實際測試結果)",orderNum = "6",width = 40 )
 private String actual;
 @Excel(name = "urlpath(被測路徑)",orderNum = "7",width = 40 )
 private String urlpath;
}

public class LoginUrlDto {
 @Excel(name = "id（訪問測試類型）",orderNum = "1",width = 20)
 private String id;
 @Excel(name = "type(請求類型)",orderNum = "2",width = 20)
 private String type;
 @Excel(name = "url(訪問地址)",orderNum = "3",width = 40)
 private String url;
}

 /**
  * 功能描述：根據接收的Excel文件來導入多個sheet,根據索引可返回一個集合
  * @param filePath 導入文件路徑
  * @param sheetIndex 導入sheet索引
  * @param titleRows 表標題的行數
  * @param headerRows 表頭行數
  * @param pojoClass Excel實體類
  * @return
  */
 public static <T> List<T> importExcel(String filePath,int sheetIndex,Integer titleRows, Integer headerRows, Class<T> pojoClass) {
  // 根據file得到Workbook,主要是要根據這個對象獲取,傳過來的excel有幾個sheet頁
  ImportParams params = new ImportParams();
  // 第幾個sheet頁
  params.setStartSheetIndex(sheetIndex);
  params.setTitleRows(titleRows);
  params.setHeadRows(headerRows);
  List<T> list = null;
  try {
   list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
  } catch (NoSuchElementException e) {
   throw new RuntimeException("模板不能為空");
  } catch (Exception e) {
   e.printStackTrace();
  }
  return list;
 }

 /**
  * 功能描述：把同一個表格多個sheet測試結果重新輸出，如果後續增加多個List<Map<String, Object>>對象，需要後面繼續追加
  * @ExcelEntiry sheet表格映射的實體對象
  * @return
  */
 public static String exportSheet( Object...objects){
  Workbook workBook = null;
  try {
   // 創建參數對象（用來設定excel得sheet得內容等信息）
   ExportParams deptExportParams = new ExportParams();
   // 設置sheet得名稱
   deptExportParams.setSheetName("登錄用例");
   // 設置sheet表頭名稱
   deptExportParams.setTitle("測試用例");
   // 創建sheet1使用得map
   Map<String, Object> deptExportMap = new HashMap<>();
   // title的參數為ExportParams類型，目前僅僅在ExportParams中設置瞭sheetName
   deptExportMap.put("title", deptExportParams);
   // 模版導出對應得實體類型
   deptExportMap.put("entity", LoginCaseDto.class);
   // sheet中要填充得數據
   deptExportMap.put("data", objects[0]);
   ExportParams empExportParams = new ExportParams();
   empExportParams.setTitle("被測RUL路徑");
   empExportParams.setSheetName("被測url");
   // 創建sheet2使用得map
   Map<String, Object> empExportMap = new HashMap<>();
   empExportMap.put("title", empExportParams);
   empExportMap.put("entity", LoginUrlDto.class);
   empExportMap.put("data", objects[1]);
   // 將sheet1、sheet2使用得map進行包裝
   List<Map<String, Object>> sheetsList = new ArrayList<>();
   sheetsList.add(deptExportMap);
   sheetsList.add(empExportMap);
   // 執行方法
   workBook = EasyPoiUtil.exportExcel(sheetsList, ExcelType.HSSF);
   //String fileName = URLEncoder.encode("test", "UTF-8");
   String filepath = (String) LoadStaticConfigUtil.getCommonYml( "testcaseexcel.cases");
   FileOutputStream fos = new FileOutputStream(filepath);
   workBook.write(fos);
   fos.close();
  }catch (Exception e){
   e.printStackTrace();
  }finally {
   if(workBook != null) {
    try {
     workBook.close();
    } catch (IOException e) {
     e.printStackTrace();
    }
   }
  }
  return "success";
 }