[REFACTOR] 소매 엑셀 업로드 파싱 책임 분리

[JoonKyoLee]

🔁 리팩토링이 필요한 부분

• processRetailExcel 메서드에 엑셀 구조 해석, 셀 파싱, 도메인 입력 검증 로직이 모두 포함되어 있습니다.
• Row 단위 파싱 규칙이 서비스 로직과 강하게 결합되어 있기 때문에 엑셀 양식 변경 시에 수정 범위가 큽니다.
• row index, 원본 값 등 메타 정보가 분리되지 않아 실패 및 스킵 처리 확장이 어렵습니다.

---

🧱 작업 내용

• Excel Row 파싱 전용 컴포넌트 분리
• 업로드 입력용 DTO 도입(RetailExcelRowDto: rowIndex, code, productName, quantity, actualSales)
• 헤더 스킵, 빈 row 처리, 기본 유효성 검증 로직을 파서 레벨로 이동
• Service에서는 파싱된 Row 목록을 받아 도메인 처리만 담당하도록 역할 축소

---

⚠️ 유의 사항 (선택)

• 현재 엑셀 컬럼 순서/의미(No, 코드, 상품명, 수량, 실매출)는 그대로 유지
• 기존 스킵 정책(코드 없음, 수량 0 이하 등)은 동작 변경 없이 유지

📌 참고 자료

관련 PR(#155, #162)

[coderabbitai]

📝 CodeRabbit Plan Mode

Generate an implementation plan and prompts that you can use with your favorite coding agent.

✅ Successfully generated implementation plan.

💡 Use @coderabbitai <feedback> to replan

Examples

• Example 1
• Example 2

---

🔗 Similar Issues

Related Issues

• [FEAT] 엑셀 upload로 소매 재고 관리 기능 구현 #149
• [REFACTOR] 입고 관련 부분 리팩토링 진행 #142
• [REFACTOR] User/Store 조회 로직 공통화 #118
• [REFACTOR] 조회 시 정렬 기준 리팩토링 #178

🔗 Related PRs

#150 - refactor: 입고 및 입고 품목에서 무게나 박스에 관련된 부분 삭제 [merged]
#162 - feat: 엑셀 upload로 소매 재고 관리 기능 구현 [merged]
#182 - refactor: 입고 삭제 기능 수정 [merged]

👤 Suggested Assignees

• JoonKyoLee
• Youngwave1128
• Two-Silver

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord or schedule a call!

[coderabbitai]

Coding Plan

Approach

Ticket Summary

Refactor the processRetailExcel method to separate Excel parsing responsibilities from domain business logic by introducing a dedicated parser component and input DTO, making the codebase more maintainable when Excel format changes occur.

Observations

Codebase Summary

The project is a Spring Boot inventory management system using a feature-based package structure with separate packages for retail, wholesale, order, and product modules. The retail module currently handles Excel uploads through RetailController and RetailService, using Apache POI (version 5.5.1) for Excel file processing. The service layer mixes Excel parsing logic (cell extraction, row iteration, DataFormatter usage) with domain operations (product lookup, inventory updates). The codebase follows standard Spring patterns with @Component services, Java records for result wrappers, and DTOs organized in {module}/dto/request and {module}/dto/response packages. No dedicated parser or transformer components currently exist.

Assumptions

Assumption 1: Parser component type and location

Options Considered:

• Create a utility class with static methods in a util package
• Create a Spring @Component parser class in the retail/service package
• Create an abstract base parser class for future reuse across modules

Chosen Option: Create a Spring @Component parser class in the retail/service package

Rationale: This aligns with the codebase's consistent use of Spring-managed components and dependency injection throughout the service layer. It provides testability without over-engineering with abstractions that aren't currently needed. If wholesale or order modules require Excel parsing later, the component can be refactored into a shared base class following the YAGNI principle.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Create Parser Infrastructure

Introduce the foundational structures for separated Excel parsing without disrupting existing functionality.

Task 1: Create RetailExcelRowDto record

Create a Java record to represent a single parsed Excel row with metadata for tracking and validation.

• Add RetailExcelRowDto record in src/main/java/com/almang/inventory/retail/dto/request/ package
• Include fields: int rowIndex, String code, String productName, Integer quantity, BigDecimal actualSales
• Include original row index for error tracking and skip reporting
• Do not add validation annotations - basic validation will be handled in the parser

Task 2: Create RetailExcelParser component skeleton

Create a Spring-managed parser component that will handle all Excel file processing responsibilities.

• Add RetailExcelParser class annotated with @Component and @Slf4j in src/main/java/com/almang/inventory/retail/service/ package
• Define the main parsing method signature: List<RetailExcelRowDto> parseExcelFile(MultipartFile file)
• Prepare to inject or instantiate DataFormatter for cell value extraction
• Set up try-with-resources structure for Workbook management using WorkbookFactory.create()

🤖 Prompt for AI agents

Create the foundational parser infrastructure to separate Excel parsing from
domain logic.

Create RetailExcelRowDto record:
- Add record in `src/main/java/com/almang/inventory/retail/dto/request/`
- Include fields: rowIndex (int), code (String), productName (String), quantity
(Integer), actualSales (BigDecimal)

Create RetailExcelParser component:
- Add class in `src/main/java/com/almang/inventory/retail/service/`
- Annotate with @Component and @Slf4j
- Add method signature: `List<RetailExcelRowDto> parseExcelFile(MultipartFile
file)`
- Set up DataFormatter for cell value extraction
- Configure try-with-resources for Workbook using WorkbookFactory.create()

Phase 2: Migrate Parsing Logic to Parser

Move all Excel-specific parsing responsibilities from the service to the parser, implementing header skipping, empty row handling, and basic field validation.

Task 1: Move cell extraction helper methods

Transfer the three cell value extraction methods from RetailService to RetailExcelParser.

• Relocate getCellValueAsString(), getCellValueAsBigDecimal(), and getCellValueAsInteger() methods to RetailExcelParser
• Keep the same DataFormatter-based implementation and null-safety handling
• Keep existing warning logs for numeric parsing issues (comma handling, rounding)
• Make these methods private helper methods within the parser

Task 2: Implement row iteration and basic validation

Build the core parsing loop that processes each Excel row and applies format-level validation.

• Iterate through sheet rows starting at index 1 (skip header row at index 0)
• For each row, extract values from cells 1-4: code (cell 1), productName (cell 2), quantity (cell 3), actualSales (cell 4)
• Implement skip conditions at parser level: empty/blank code (skip with warning log), quantity ≤ 0 (skip with warning log)
• Create RetailExcelRowDto instances for valid rows, preserving the rowIndex from Excel file
• Return a List<RetailExcelRowDto> containing only rows that pass basic validation
• Log skipped rows with context: row index and skip reason (e.g., "Skipping row 5: empty product code")

🤖 Prompt for AI agents

Migrate all Excel parsing logic from RetailService to the new RetailExcelParser
component.

Move cell extraction methods:
- Transfer getCellValueAsString(), getCellValueAsBigDecimal(), and
getCellValueAsInteger() from RetailService to RetailExcelParser
- Keep DataFormatter-based implementation and null-safety handling
- Preserve warning logs for numeric parsing issues
- Make methods private helpers

Implement core parsing loop:
- Iterate rows starting at index 1 (skip header at 0)
- Extract from cells 1-4: code, productName, quantity, actualSales
- Skip rows with empty/blank code or quantity ≤ 0
- Log skipped rows with row index and reason
- Create RetailExcelRowDto for valid rows with preserved rowIndex
- Return List<RetailExcelRowDto> of validated rows

Phase 3: Refactor Service Layer

Update RetailService to delegate parsing to the new component and focus exclusively on domain operations.

Task 1: Update processRetailExcel to use parser

Modify the service method to receive parsed DTOs rather than processing raw Excel cells.

• Inject RetailExcelParser into RetailService constructor or field
• Call retailExcelParser.parseExcelFile(file) at the start of processRetailExcel() to obtain List<RetailExcelRowDto>
• Remove all Apache POI imports and cell extraction code from RetailService
• Remove the now-unused helper methods (getCellValueAsString, getCellValueAsBigDecimal, getCellValueAsInteger)

Task 2: Process parsed rows for domain logic

Iterate through the parsed DTO list and apply domain-level validation and business operations.

• Loop through List<RetailExcelRowDto> instead of Excel sheet rows
• Access row data via DTO getters (dto.code(), dto.quantity(), etc.) instead of cell extraction
• Keep existing domain validation: product lookup via productRepository.findByCode(), inventory availability checks, InsufficientInventoryException handling
• Keep existing skip tracking for domain errors: product not found, inventory missing, insufficient stock
• Maintain the same skip product list construction with product codes and reasons
• Preserve the existing RetailUploadResult return structure with processedCount and skippedProducts

Task 3: Update logging and metadata tracking

Enhance error messages using the rowIndex metadata now available from the DTO.

• Update skip warning logs to include the Excel row number from dto.rowIndex() for better traceability
• Format skip messages consistently: "Skipping row {rowIndex}: {reason} (product code: {code})"
• Keep existing log levels: log.info() for successful processing, log.warn() for skips, log.error() for exceptions
• Ensure all skip conditions (both parser-level and domain-level) are logged with appropriate context

🤖 Prompt for AI agents

Refactor RetailService to use the parser component and focus on domain logic
only.

Integrate parser:
- Inject RetailExcelParser into RetailService
- Call parseExcelFile(file) at start of processRetailExcel() to get
List<RetailExcelRowDto>
- Remove Apache POI imports and cell extraction code
- Remove getCellValueAsString, getCellValueAsBigDecimal, getCellValueAsInteger
methods

Process parsed rows:
- Loop through List<RetailExcelRowDto> instead of Excel rows
- Access data via DTO getters (code(), quantity(), etc.)
- Keep domain validation: product lookup, inventory checks, exception handling
- Preserve skip tracking for domain errors
- Maintain RetailUploadResult structure with processedCount and skippedProducts

Enhance logging:
- Include dto.rowIndex() in skip warnings for traceability
- Use consistent format: "Skipping row {rowIndex}: {reason} (product code:
{code})"
- Preserve log levels (info, warn, error)

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Create the foundational parser infrastructure to separate Excel parsing from
domain logic.

Create RetailExcelRowDto record:
- Add record in `src/main/java/com/almang/inventory/retail/dto/request/`
- Include fields: rowIndex (int), code (String), productName (String), quantity
(Integer), actualSales (BigDecimal)

Create RetailExcelParser component:
- Add class in `src/main/java/com/almang/inventory/retail/service/`
- Annotate with @Component and @Slf4j
- Add method signature: `List<RetailExcelRowDto> parseExcelFile(MultipartFile
file)`
- Set up DataFormatter for cell value extraction
- Configure try-with-resources for Workbook using WorkbookFactory.create()
===============================================================================

Task: 2

Migrate all Excel parsing logic from RetailService to the new RetailExcelParser
component.

Move cell extraction methods:
- Transfer getCellValueAsString(), getCellValueAsBigDecimal(), and
getCellValueAsInteger() from RetailService to RetailExcelParser
- Keep DataFormatter-based implementation and null-safety handling
- Preserve warning logs for numeric parsing issues
- Make methods private helpers

Implement core parsing loop:
- Iterate rows starting at index 1 (skip header at 0)
- Extract from cells 1-4: code, productName, quantity, actualSales
- Skip rows with empty/blank code or quantity ≤ 0
- Log skipped rows with row index and reason
- Create RetailExcelRowDto for valid rows with preserved rowIndex
- Return List<RetailExcelRowDto> of validated rows
===============================================================================

Task: 3

Refactor RetailService to use the parser component and focus on domain logic
only.

Integrate parser:
- Inject RetailExcelParser into RetailService
- Call parseExcelFile(file) at start of processRetailExcel() to get
List<RetailExcelRowDto>
- Remove Apache POI imports and cell extraction code
- Remove getCellValueAsString, getCellValueAsBigDecimal, getCellValueAsInteger
methods

Process parsed rows:
- Loop through List<RetailExcelRowDto> instead of Excel rows
- Access data via DTO getters (code(), quantity(), etc.)
- Keep domain validation: product lookup, inventory checks, exception handling
- Preserve skip tracking for domain errors
- Maintain RetailUploadResult structure with processedCount and skippedProducts

Enhance logging:
- Include dto.rowIndex() in skip warnings for traceability
- Use consistent format: "Skipping row {rowIndex}: {reason} (product code:
{code})"
- Preserve log levels (info, warn, error)

💡 Iterate on the plan with: @coderabbitai <feedback>

Example Feedback
- @coderabbitai You can skip phase 3. Add a simple unit test case for phase 2.
- @coderabbitai For assumption 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord or schedule a call!