Spring 대용량 엑셀 업로드

어드민을 관리 할 경우 대용량 엑셀을 업로드 할 경우가 생깁니다.

단순하게 엑셀만 업로드 할 경우 OOM, 타임아웃, 파일 관련, 네트워크 관련 에러 등 여러 에러가 발생할 수 있습니다.

 

아래 5가지를 주의해서 만들고, DB 모니터링으로 최적화가 가능합니다.

 

1. 스트리밍 방식 파일 읽기

• 파일 전체를 메모리에 로드하지 않고, 스트리밍 방식으로 한 행씩 순차적으로 읽음
• Apache POI의 SXSSF(Streaming XML SAX Parser) 또는 xlsx-streamer 라이브러리 활용
• 메모리 사용량을 최소화하여 OOM(Out of Memory) 방지

public class ExcelStreamReader {

    public void readExcelStream(String filePath) {
        try (InputStream is = new FileInputStream(filePath);
             Workbook workbook = StreamingReader.builder()
                     .rowCacheSize(100)   // 메모리에 캐싱할 행의 수
                     .bufferSize(4096)    // 버퍼 크기
                     .open(is)) {
            
            // 첫 번째 시트 선택
            Sheet sheet = workbook.getSheetAt(0);
            
            for (Row row : sheet) {
                // 각 행을 처리
                // 실제 구현에서는 이 부분에서 데이터 변환 및 처리
                if (row.getRowNum() == 0) {
                    // 헤더 행 처리 (필요시)
                    System.out.println("Processing header row");
                    continue;
                }
                
                // 실제 데이터 행 처리 (예시)
                String cellValue = row.getCell(0) != null ? row.getCell(0).getStringCellValue() : "";
                System.out.println("Row " + row.getRowNum() + ", First cell: " + cellValue);
            }
            
        } catch (IOException e) {
            throw new RuntimeException("Failed to read excel file: " + filePath, e);
        }
    }
}

 

2. 비동기 처리

• 사용자 요청(업로드)과 실제 처리를 분리
• 사용자에게는 "작업 ID"를 즉시 반환하고 백그라운드에서 처리
• Spring의 @Async 어노테이션 및 ThreadPoolTaskExecutor 활용

public class ExcelProcessingService {
    
    private final JobService jobService;
    private final ExcelDataRepository excelDataRepository;
    private static final int BATCH_SIZE = 5000;
    private static final int PROGRESS_UPDATE_FREQUENCY = 5000; // 5000행마다 상태 업데이트
    
    @Async("excelTaskExecutor")
    public CompletableFuture<Void> processExcelAsync(Long jobId, String filePath) {
        try {
            // 작업 시작 상태로 업데이트
            jobService.startJob(jobId);
            
            // 엑셀 처리
            processExcelFile(jobId, filePath);
            
            // 작업 완료 처리
            jobService.completeJob(jobId);
            
            return CompletableFuture.completedFuture(null);
        } catch (Exception e) {
            log.error("엑셀 처리 중 오류 발생: {}", e.getMessage(), e);
            jobService.failJob(jobId, e.getMessage());
            throw new RuntimeException("엑셀 처리 실패", e);
        }
    }
    
    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    public void processExcelFile(Long jobId, String filePath) throws Exception {
        try (InputStream is = new FileInputStream(filePath);
             Workbook workbook = StreamingReader.builder()
                     .rowCacheSize(100)
                     .bufferSize(4096)
                     .open(is)) {
            
            Sheet sheet = workbook.getSheetAt(0);
            
            // 1단계: 총 행 수 계산 (선택적)
            long totalRows = countRows(sheet); 
            jobService.setTotalRows(jobId, totalRows);
            
            // 2단계: 데이터 처리
            processSheetData(jobId, sheet);
        }
    }
    
    private long countRows(Sheet sheet) {
        long count = 0;
        for (Row row : sheet) {
            count++; 
        }
        return count - 1; // 헤더 제외
    }
    
    private void processSheetData(Long jobId, Sheet sheet) {
        List<ExcelData> batch = new ArrayList<>(BATCH_SIZE);
        long processedRows = 0;
        boolean isHeader = true;
        
        for (Row row : sheet) {
            // 헤더 행 스킵
            if (isHeader) {
                isHeader = false;
                continue;
            }
            
            // 행 데이터 변환 및 배치에 추가
            ExcelData data = convertRowToData(row, jobId);
            batch.add(data);
            processedRows++;
            
            // 배치 크기에 도달하면 저장
            if (batch.size() >= BATCH_SIZE) {
                saveDataBatch(batch);
                batch.clear();
                
                // 진행 상황 업데이트 (주기적으로)
                if (processedRows % PROGRESS_UPDATE_FREQUENCY == 0) {
                    jobService.updateProgress(jobId, processedRows);
                }
            }
        }
        
        // 남은 데이터 처리
        if (!batch.isEmpty()) {
            saveDataBatch(batch);
        }
        
        // 최종 진행 상황 업데이트
        jobService.updateProgress(jobId, processedRows);
    }
    
    @Transactional
    public void saveDataBatch(List<ExcelData> batch) {
        excelDataRepository.saveAll(batch);
    }
    
    private ExcelData convertRowToData(Row row, Long jobId) {
        // 실제 데이터 변환 로직 (엑셀 열에 따라 조정 필요)
        String col1 = getStringCellValue(row, 0);
        String col2 = getStringCellValue(row, 1);
        String col3 = getStringCellValue(row, 2);
        
        return ExcelData.builder()
                .jobId(jobId)
                .column1(col1)
                .column2(col2)
                .column3(col3)
                .build();
    }
    
    private String getStringCellValue(Row row, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        if (cell == null) return "";
        
        switch (cell.getCellType()) {
            case STRING: return cell.getStringCellValue();
            case NUMERIC: 
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                }
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN: return String.valueOf(cell.getBooleanCellValue());
            case FORMULA: return cell.getCellFormula();
            default: return "";
        }
    }
}

 

3. 청크 단위 처리 및 배치 삽입

• 대량의 데이터를 작은 "청크(chunk)" 단위로 나누어 처리
• 각 청크(예: 5,000건)마다 DB 배치 삽입(batch insert) 수행
• 메모리 효율성과 DB 성능 최적화 동시 달성

@Service
public class ExcelDataProcessor {

    private final JdbcTemplate jdbcTemplate;
    private final JobRepository jobRepository;
    
    private static final int CHUNK_SIZE = 5000;

    public ExcelDataProcessor(JdbcTemplate jdbcTemplate, JobRepository jobRepository) {
        this.jdbcTemplate = jdbcTemplate;
        this.jobRepository = jobRepository;
    }

    public void processExcelFile(Job job, String filePath) {
        try (InputStream is = new FileInputStream(filePath);
             Workbook workbook = StreamingReader.builder()
                     .rowCacheSize(100)
                     .bufferSize(4096)
                     .open(is)) {
            
            Sheet sheet = workbook.getSheetAt(0);
            
            // 총 행 수 계산 (선택적, 필요시)
            long estimatedRows = countRows(sheet);
            job.setTotalRows(estimatedRows);
            jobRepository.save(job);
            
            // 실제 데이터 처리
            processSheetInChunks(job, sheet);
        } catch (Exception e) {
            throw new RuntimeException("Error processing excel file", e);
        }
    }
    
    private long countRows(Sheet sheet) {
        long count = 0;
        for (Row row : sheet) {
            count++;
        }
        return count - 1; // 헤더 제외
    }
    
    private void processSheetInChunks(Job job, Sheet sheet) {
        List<ExcelDataEntity> chunk = new ArrayList<>(CHUNK_SIZE);
        long processedRows = 0;
        boolean isHeader = true;
        
        for (Row row : sheet) {
            // 헤더 행 스킵
            if (isHeader) {
                isHeader = false;
                continue;
            }
            
            // 행 데이터 변환
            ExcelDataEntity entity = convertRowToEntity(row, job);
            chunk.add(entity);
            
            // 청크 크기에 도달하면 배치 처리
            if (chunk.size() >= CHUNK_SIZE) {
                saveChunk(chunk);
                
                // 진행 상태 업데이트
                processedRows += chunk.size();
                updateJobProgress(job, processedRows);
                
                // 청크 초기화
                chunk.clear();
            }
        }
        
        // 마지막 남은 데이터 처리
        if (!chunk.isEmpty()) {
            saveChunk(chunk);
            processedRows += chunk.size();
            updateJobProgress(job, processedRows);
        }
    }
    
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void saveChunk(List<ExcelDataEntity> chunk) {
        String sql = "INSERT INTO excel_data (job_id, column1, column2, column3) VALUES (?, ?, ?, ?)";
        
        jdbcTemplate.batchUpdate(sql, chunk, CHUNK_SIZE, (PreparedStatement ps, ExcelDataEntity entity) -> {
            ps.setLong(1, entity.getJobId());
            ps.setString(2, entity.getColumn1());
            ps.setString(3, entity.getColumn2());
            ps.setString(4, entity.getColumn3());
        });
    }
    
    private ExcelDataEntity convertRowToEntity(Row row, Job job) {
        // 실제 구현에서는 엑셀 열의 데이터 타입에 따라 적절히 변환
        String col1 = row.getCell(0) != null ? row.getCell(0).getStringCellValue() : "";
        String col2 = row.getCell(1) != null ? row.getCell(1).getStringCellValue() : "";
        String col3 = row.getCell(2) != null ? row.getCell(2).getStringCellValue() : "";
        
        return ExcelDataEntity.builder()
                .jobId(job.getId())
                .column1(col1)
                .column2(col2)
                .column3(col3)
                .build();
    }
    
    private void updateJobProgress(Job job, long processedRows) {
        job.setProcessedRows(processedRows);
        jobRepository.save(job);
    }
}

 

 

4. 트랜잭션 관리

• 트랜잭션을 최소한의 DB 작업에만 적용
• 각 청크마다 짧은 트랜잭션 사용으로 DB 락 시간 최소화
• 오랜 시간 동안 트랜잭션 유지로 인한 문제 방지

@Transactional(propagation = Propagation.NOT_SUPPORTED)
public void processLargeExcel(String filePath) {
    // 트랜잭션 외부에서 전체 프로세스 실행
    try (InputStream is = new FileInputStream(filePath)) {
        int batchCount = 0;
        List<ExcelData> batch = new ArrayList<>(BATCH_SIZE);
        
        // 파일 스트리밍 읽기
        for (Row row : getRows(is)) {
            ExcelData data = convertToData(row);
            batch.add(data);
            
            if (batch.size() >= BATCH_SIZE) {
                // 배치 크기에 도달할 때마다 트랜잭션으로 처리
                processBatchWithTransaction(batch);
                batch.clear();
                batchCount++;
            }
        }
        
        // 남은 배치 처리
        if (!batch.isEmpty()) {
            processBatchWithTransaction(batch);
        }
    }
}

@Transactional
public void processBatchWithTransaction(List<ExcelData> batch) {
    repository.saveAll(batch);
}

 

 

5. 작업 상태 추적

• 진행 상황을 DB에 주기적으로 업데이트
• 사용자는 작업 ID로 진행률 조회 가능
• 오류 발생 시에도 어디까지 처리되었는지 확인 가능

public class JobService {
    
    private final JobRepository jobRepository;
    
    public Job createJob(String filename, String tempFilePath) {
        Job job = Job.builder()
                .filename(filename)
                .status("PENDING")
                .tempFilePath(tempFilePath)
                .totalRows(0)
                .processedRows(0)
                .createdAt(LocalDateTime.now())
                .build();
        
        return jobRepository.save(job);
    }
    
    public Optional<Job> getJob(Long jobId) {
        return jobRepository.findById(jobId);
    }
    
    public List<Job> getRecentJobs() {
        return jobRepository.findTop10ByOrderByCreatedAtDesc();
    }
    
    // 작업 시작 상태로 업데이트
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void startJob(Long jobId) {
        Job job = jobRepository.findById(jobId).orElseThrow();
        job.setStatus("PROCESSING");
        jobRepository.save(job);
    }
    
    // 총 행 수 설정
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void setTotalRows(Long jobId, long totalRows) {
        Job job = jobRepository.findById(jobId).orElseThrow();
        job.setTotalRows(totalRows);
        jobRepository.save(job);
        log.info("작업 ID {}: 총 {}행 처리 예정", jobId, totalRows);
    }
    
    // 진행 상황 업데이트 (별도 트랜잭션으로 처리)
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void updateProgress(Long jobId, long processedRows) {
        // 별도 메서드 호출로 성능 최적화
        jobRepository.updateProcessedRows(jobId, processedRows);
        
        // 로깅 (1만 행마다)
        if (processedRows % 10000 == 0) {
            log.info("작업 ID {}: {}행 처리 완료", jobId, processedRows);
        }
    }
    
    // 작업 완료 처리
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void completeJob(Long jobId) {
        Job job = jobRepository.findById(jobId).orElseThrow();
        job.setStatus("COMPLETED");
        job.setCompletedAt(LocalDateTime.now());
        jobRepository.save(job);
        log.info("작업 ID {}: 처리 완료. 총 {}행 처리됨", jobId, job.getProcessedRows());
    }
    
    // 작업 실패 처리
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void failJob(Long jobId, String errorMessage) {
        jobRepository.updateJobError(jobId, "FAILED", errorMessage);
        log.error("작업 ID {}: 처리 실패. 사유: {}", jobId, errorMessage);
    }
}