[Spring] 엑셀 업로드 DB Insert 예제

환경: JDK 1.8, Spring Boot 2.7.10

build.gradle

implementation 'commons-io:commons-io:2.7'
implementation group: 'org.apache.poi', name: 'poi', version: '4.1.2'
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.2'

UserExcelDto.java

@Data
public class UserExcelDto {
    private String userUid;
    private String encPassword;
    private String userName; // 이름
    private String phone; // 연락처
    private String birthDay; // 생년월일
    private String gender; // 성별

    public UserExcelDto mapping(DataFormatter formatter, XSSFRow row){
        this.userName = formatter.formatCellValue(row.getCell(0));
        this.phone = formatter.formatCellValue(row.getCell(1));
        this.birthDay = formatter.formatCellValue(row.getCell(2));
        this.gender = formatter.formatCellValue(row.getCell(3));
        this.userUid = UUID.randomUUID().toString();
        this.encPassword = new PasswordEncoderUtil().encode(birthDay);

        return this;
    }

    public UserExcelDto validCheck(int i, StringBuilder message, UserExcelMapper mapper){
        int rowCnt = i;
        if(userName == null || userName.isEmpty()){
            message.append(rowCnt + "행의 사용자 이름이 유효하지않습니다.").append("\n");
        }

        final String PHONE_REGEX = "^\\d{3}-\\d{3,4}-\\d{4}|\\d{11,12}$";
        if(phone.length() == 10 ){
            phone = "0" + phone;
        }
        Pattern pattern = Pattern.compile(PHONE_REGEX);
        Matcher matcher = pattern.matcher(phone);
        if(phone == null || phone.isEmpty() || !matcher.matches()){
            message.append(rowCnt + "행의 연락처가 유효하지않습니다.").append("\n");
        }

        int dupCount = mapper.selectDuplicateUserPhone(this);
        if(dupCount > 0){
            message.append(rowCnt + "행의 연락처가 이미 등록된 사용자의 연락처와 중복됩니다.").append("\n");
        }

        final String DAY_REGEX = "^\\d{4}-\\d{2}-\\d{2}$";
        pattern = Pattern.compile(DAY_REGEX);
        matcher = pattern.matcher(birthDay);
        if(birthDay == null || birthDay.isEmpty() || !matcher.matches()){
            message.append(rowCnt + "행의 생년월일이 유효하지않습니다.").append("\n");
        }

        if(gender == null || gender.isEmpty()){
            message.append(rowCnt + "행의 성별이 유효하지 않습니다.").append("\n");
        }

        return this;
    }
}

Controller

@Transactional
@RequestMapping(value="/userExcel/uploadTemplate", method= RequestMethod.POST)
public @ResponseBody Map<String, Object> uploadTemplate(@RequestPart(value = "file") MultipartFile file) throws IOException {
    Map<String, Object> result = new HashMap<>();
    result.put("success", false);
    StringBuilder message = new StringBuilder();

    if(file.isEmpty()) {
        result.put("message", "file is null");
        return result;
    }
    
    XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
    XSSFSheet worksheet = workbook.getSheetAt(0);
    List<UserExcelDto> list = new ArrayList<>();
    for(int i=1;i<worksheet.getPhysicalNumberOfRows() ;i++) {
        DataFormatter formatter = new DataFormatter();
        XSSFRow row = worksheet.getRow(i);

        UserExcelDto dto = new UserExcelDto().mapping(formatter, row);

        dto.validCheck(i, message, mapper);

        list.add(dto);
    }

    result.put("totalCnt", list.size());

    if(!message.toString().equals("")) {
        result.put("message", message.toString());
        return result;
    }

    for(UserExcelDto dto : list) {
        mapper.insertUsers(dto);
    }

    result.put("success", true);
    return result;
}