xlsx 資料不固定欄位如何讀取
類型1
序號,競賽項目,學校,參賽者,指導教師,帳號,密碼
簡報項目 (多了隊員)
序號,競賽項目,學校, 參賽者,隊員,指導教師,帳號,密碼
繪圖項目(多了繪圖軟體)
序號,競賽項目,學校,參賽者,指導教師,繪圖軟體,帳號,密碼
定義欄位:
競賽項目 contestitem
學校 schoolname
指導教師 instructor
參賽者 username
帳號 account
密碼 passwd
隊員 membername
建立Model Team
public class Team { private String contestitem; private String schoolname; private String username; private String membername; private String instructor; private String account; private String passwd; private String location; private String description; private String comments; }
做法
先讀第一列, 利用map 記錄每個欄位標題名稱
Map<String,Integer> header = new HashMap<>();
Map<String, Integer> header = new HashMap<>(); header.put("contestitem", 99); header.put("username", 99); header.put("instructor", 99); header.put("schoolname", 99); header.put("account", 99); header.put("passwd",99); header.put("membername",99);
每個欄位初始值為99, 如果讀進來的檔案有此欄位, 再設定為欄位值
while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (formatter.formatCellValue(cell).contains("競賽項目")) { header.replace("contestitem", cell.getColumnIndex()); } if (formatter.formatCellValue(cell).contains("參賽者")) { header.replace("username", cell.getColumnIndex()); } if (formatter.formatCellValue(cell).contains("指導教師")) { header.replace("instructor", cell.getColumnIndex()); } if (formatter.formatCellValue(cell).contains("學校")) { header.replace("schoolname", cell.getColumnIndex()); } if (formatter.formatCellValue(cell).contains("帳號")) { header.replace("account", cell.getColumnIndex()); } if (formatter.formatCellValue(cell).contains("密碼")) { header.replace("passwd", cell.getColumnIndex()); } if (formatter.formatCellValue(cell).contains("隊員")) { header.replace("membername", cell.getColumnIndex()); } }
取得index值後, 讀列
//取得每一列的每一欄index中的資料 while (rowIterator.hasNext()) { row = rowIterator.next(); Team team = new Team(); team.setContestitem(row.getCell(header.get("contestitem")).getStringCellValue()); team.setSchoolname(row.getCell(header.get("schoolname")).getStringCellValue()); team.setUsername(row.getCell(header.get("username")).getStringCellValue()); team.setAccount(row.getCell(header.get("account")).getStringCellValue()); team.setPasswd(row.getCell(header.get("passwd")).getStringCellValue()); team.setInstructor(row.getCell(header.get("instructor")).getStringCellValue()); team.setMembers(1); if (header.get("membername")<99) { team.setMembername(row.getCell(header.get("membername")).getStringCellValue()); team.setMembers(2); } ObjectMapper mapper = new ObjectMapper(); logger.info(mapper.writeValueAsString(team)); teamList.add(team); }
簡報項目雖然是兩人一組, 但可一人參賽, 隊員欄位可能沒有值
if (header.get("membername")<99) { team.setMembername(row.getCell(header.get("membername")).getStringCellValue()); team.setMembers(2); }
END
