Spring Boot로 엑셀 파일 파싱 및 주소 기반 데이터 조회 처리하기

엑셀 파일을 통한 대량의 데이터를 처리하는 기능은 다양한 서비스에서 매우 유용하게 사용됩니다. 이번 포스트에서는 Spring Boot로 엑셀 파일을 업로드하고, 그 데이터를 데이터베이스에 저장하는 과정을 함께 살펴보겠습니다. 이 기능은 주로 관리자가 다수의 건물 정보를 한 번에 업데이트해야 할 때 활용됩니다.

엑셀 파일 업로드 흐름

엑셀 파일 업로드의 흐름은 다음과 같이 진행됩니다:

1. 엑셀 파일 업로드 요청: 클라이언트에서 엑셀 파일을 업로드합니다.
2. 엑셀 파일 파싱: 업로드된 엑셀 파일의 내용을 읽어들여 각 셀의 값을 처리합니다.
3. 주소 정보 매핑 및 데이터베이스 조회: 엑셀 파일에 포함된 주소 데이터를 기반으로 추가 정보를 조회하여 건물 번호를 확인합니다.
4. 데이터 저장: 파싱된 데이터를 데이터베이스에 삽입 또는 업데이트합니다.

이 모든 과정을 하나씩 자세히 살펴보겠습니다.

1. 엑셀 업로드 컨트롤러

우선, 클라이언트에서 엑셀 파일을 업로드하는 HTTP POST 요청을 처리하는 컨트롤러를 만들어야 합니다. ExcelUploadController는 이 역할을 담당하며, 파일을 ExcelUploadService로 전달합니다.

@RestController
public class ExcelUploadController {

    @Autowired
    private ExcelUploadService excelUploadService;

    @PostMapping("/upload-excel")
    public ResponseEntity<Map<String, Object>> uploadExcelFile(@RequestParam("file") MultipartFile file) {
        Map<String, Object> response = excelUploadService.uploadExcelFile(file);
        return ResponseEntity.ok(response);
    }
}

여기서는 /upload-excel 경로로 POST 요청이 들어오면, 첨부된 엑셀 파일을 받아 uploadExcelFile 메서드로 처리합니다.

2. 엑셀 파일 파싱 로직

엑셀 파일의 내용을 파싱하는 로직은 ExcelUploadService 클래스에서 이루어집니다. 엑셀의 첫 번째 시트에서 데이터를 읽어와 각 행을 하나씩 처리합니다.

@Service
@Slf4j
public class ExcelUploadService {

    @Autowired
    private AddressMapper addressMapper;

    public Map<String, Object> uploadExcelFile(MultipartFile file) {
        Map<String, Object> response = new HashMap<>();

        try {
            Workbook workbook = new XSSFWorkbook(file.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);  // 첫 번째 시트
            List<BuildingBLCMInfoVo> data = new ArrayList<>();

            // 첫 번째 행에서 컬럼 이름 가져오기
            Row headerRow = sheet.getRow(0);
            Map<Integer, String> headerMap = extractHeaderMap(headerRow);

            // 나머지 행 데이터 처리
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                BuildingBLCMInfoVo rowData = new BuildingBLCMInfoVo();
                for (Cell cell : row) {
                    String columnName = headerMap.get(cell.getColumnIndex());
                    setFieldValue(rowData, columnName, cell);
                }

                // 주소 매핑 및 데이터 추가
                processAddressAndAdditionalData(rowData);

                data.add(rowData);
            }

            // 데이터베이스 삽입 또는 업데이트
            for (BuildingBLCMInfoVo rowData : data) {
                addressMapper.upsertData(rowData);
            }

            response.put("data", data);
        } catch (IOException e) {
            log.error("File processing error", e);
            response.put("error", "File processing error");
        }

        return response;
    }

    // 헤더 행에서 컬럼 이름 추출
    private Map<Integer, String> extractHeaderMap(Row headerRow) {
        Map<Integer, String> headerMap = new HashMap<>();
        for (Cell cell : headerRow) {
            headerMap.put(cell.getColumnIndex(), cell.getStringCellValue());
        }
        return headerMap;
    }

    // 데이터베이스 삽입/업데이트용 메서드 생략...
}

이 서비스 클래스는 업로드된 엑셀 파일을 파싱하여 각 셀의 값을 BuildingBLCMInfoVo 객체에 매핑한 후, 데이터베이스에 삽입 또는 업데이트하는 작업을 수행합니다.

컬럼 매핑 및 데이터 처리

setFieldValue 메서드는 Excel 파일의 각 셀 데이터를 BuildingBLCMInfoVo 객체의 필드에 매핑하는 기능을 수행합니다. 컬럼 이름과 일치하는 셀의 값을 확인하여 적절한 데이터 형식으로 변환한 후, 해당 필드에 저장합니다.

private void setFieldValue(BuildingBLCMInfoVo buildingInfo, String columnName, Cell cell) {
	switch (columnName) {
		case "주소": buildingInfo.setAddress(cell.getStringCellValue()); break; 
        // 기타 컬럼 매핑 생략... 
    	} 
 }

 

3. 주소 매핑 및 데이터 조회

엑셀 파일에서 파싱된 주소 정보를 활용해, 데이터베이스에서 추가 정보를 가져와야 합니다. AddressMapper를 이용해 주소 정보에 맞는 건물 번호(bul_man_no)를 조회한 후 해당 값을 BuildingBLCMInfoVo 객체에 설정합니다.

AddressMapper XML 쿼리

아래는 AddressMapper에서 사용되는 쿼리입니다. 이 쿼리는 특정 시군구 코드와 읍면동 코드, 지번을 기준으로 데이터를 조회하는 역할을 합니다.

<mapper namespace="mapper.AddressMapper">
    <select id="getAddressData" parameterType="map" resultType="map">
        SELECT DISTINCT bul_man_no
        FROM tl_spbd_buld_all
        WHERE sig_cd = #{sigCd}
        AND RIGHT(emd_cd, 3) = #{emdCd}
        AND lnbr_mnnm = #{lnbrMnnm}
        AND lnbr_slno = #{lnbrSlno}
    </select>

    <insert id="upsertData" parameterType="vo.BuildingBLCMInfoVo">
        INSERT INTO building_info
        (bul_man_no, building_name, address, dong_name, approval_date, inspection_deadline, ...)
        VALUES (#{bul_man_no}, #{building_name}, #{address}, #{dong_name}, #{approval_date}, #{inspection_deadline}, ...)
        ON DUPLICATE KEY UPDATE
        building_name = VALUES(building_name),
        address = VALUES(address),
        dong_name = VALUES(dong_name),
        approval_date = VALUES(approval_date),
        inspection_deadline = VALUES(inspection_deadline), ...
    </insert>
</mapper>

이 쿼리에서는 sig_cd, emd_cd, lnbr_mnnm, lnbr_slno 값을 이용해 데이터베이스에서 건물 번호(bul_man_no)를 가져옵니다. 또한, upsertData 쿼리를 통해 새로운 데이터를 삽입하거나 기존 데이터를 업데이트할 수 있습니다.

4. 자동 경과년수 계산

엑셀 파일을 업로드할 때, 건물의 승인일과 통보일을 기반으로 자동으로 경과년수를 계산하여 설정할 수 있습니다.

private String calculateElapsedYears(BuildingBLCMInfoVo buildingInfo) {
    if (buildingInfo.getNotification_date() != null && buildingInfo.getApproval_date() != null) {
        LocalDate notificationDate = LocalDate.parse(buildingInfo.getNotification_date(), DateTimeFormatter.ofPattern("yyyyMMdd"));
        LocalDate approvalDate = LocalDate.parse(buildingInfo.getApproval_date(), DateTimeFormatter.ofPattern("yyyyMMdd"));
        long years = ChronoUnit.YEARS.between(approvalDate, notificationDate);
        long months = ChronoUnit.MONTHS.between(approvalDate, notificationDate) % 12;
        return years + "년 " + months + "개월";
    }
    return null;
}

마무리

이 포스트에서는 Spring Boot에서 엑셀 파일을 업로드하고, 해당 데이터를 데이터베이스에 저장하는 기능을 살펴보았습니다. 데이터 파싱과 저장뿐만 아니라, 주소 정보를 통해 추가 데이터를 조회하고, 자동으로 경과년수를 계산하는 기능을 구현할 수 있습니다.

이를 통해, 대량의 데이터를 손쉽게 처리하고 관리하는 시스템을 효율적으로 구축할 수 있습니다.