xlsx 資料不固定欄位如何讀取並儲存

URL Link //n.sfs.tw/16505

2025-03-28 13:52:18 By igogo

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);

}