[Apache POI] Apache POI를 이용하여 엑셀파일(.xlsx) 데이터베이스에 저장하기 (2)

DTO와 Entity를 구성하고 이를 저장하는 코드는 모두 구성하였고, 엑셀파일을 데이터베이스에 저장해주는 Service단과 Controller단의 설명을 이어가도록 하겠다.

 

우선 application.properties에

excel.file.path=food.xlsx //엑셀파일이 저장되어 있는 경로지정

spring.servlet.multipart.max-file-size=100MB //처리할 데이터의 용량 100MB로 지정
spring.servlet.multipart.max-request-size=100MB

해당 코드를 추가해준다. 해당 코드는 엑셀파일을 불러와야 하기 때문에 엑셀파일이 저장되어 있는 경로를 지정을 하였고,  기본적으로 데이터를 처리할수 있는 기본 용량은 1MB로 설정이 되어있기 때문에 이를 최대 100MB로 늘려주는 코드이다.

 

Service

@Autowired
    private FoodRepository foodRepository;

    @Value("${excel.file.path}")
    private String excelPath;

    public static List<FoodDto> readFromExcel(String filePath) throws IOException {
        List<FoodDto> foodDtoList = new ArrayList<>();

		// try-with-resources 문을 사용하여 엑셀 워크북을 생성하고, 자동으로 리소스를 닫는다.
        try (Workbook workbook = WorkbookFactory.create(FoodDto.class.getClassLoader().getResourceAsStream(filePath))) {
            // 첫 번째 시트에서 데이터를 읽어온다.
            Sheet sheet = workbook.getSheetAt(0);

            // 헤더 행을 건너뛴다.
            Iterator<Row> rowIterator = sheet.iterator();
            if (rowIterator.hasNext()) {
                rowIterator.next();
            }

			// 각 행을 순회하면서 FoodDto 객체를 생성하여 리스트에 추가한다.
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                FoodDto foodDto = fromExcelRow(row);
                foodDtoList.add(foodDto);
            }
        }
        return foodDtoList;
    }

 

@Value 어노테이션을 사용하여 외부 설정 파일(application.properties) 에서 excel.file.path 속성의 값을 읽어와 저장해준다.

 

 

다음은 엑셀 데이터를 추출하여 FoodDto 객체로 변환하는 메서드로

// 엑셀의 한 행에서 데이터를 추출하여 FoodDto 객체로 변환하는 메서드
private static FoodDto fromExcelRow(Row row) {
    // 새로운 FoodDto 객체 생성
    FoodDto foodDto = new FoodDto();

    // 엑셀의 각 열의 데이터를 FoodDto 객체의 속성에 설정
    foodDto.setNum((long) Double.parseDouble(getStringValue(row.getCell(0))));
    foodDto.setName(getStringValue(row.getCell(1)));
    foodDto.setBrand(getStringValue(row.getCell(2)));
    foodDto.setClass1(getStringValue(row.getCell(3)));
    foodDto.setClass2(getStringValue(row.getCell(4)));
    foodDto.setServingSize(getStringValue(row.getCell(5)));
    foodDto.setServingUnit(getStringValue(row.getCell(6)));
    foodDto.setKcal(getStringValue(row.getCell(7)));
    foodDto.setProtein(getStringValue(row.getCell(8)));
    foodDto.setProvince(getStringValue(row.getCell(9)));
    foodDto.setCarbohydrate(getStringValue(row.getCell(10)));
    foodDto.setSugar(getStringValue(row.getCell(11)));
    foodDto.setDietaryFiber(getStringValue(row.getCell(12)));
    foodDto.setCalcium(getStringValue(row.getCell(13)));
    foodDto.setIron(getStringValue(row.getCell(14)));
    foodDto.setSalt(getStringValue(row.getCell(15)));
    foodDto.setZinc(getStringValue(row.getCell(16)));
    foodDto.setVitaB1(getStringValue(row.getCell(17)));
    foodDto.setVitaB2(getStringValue(row.getCell(18)));
    foodDto.setVitaB12(getStringValue(row.getCell(19)));
    foodDto.setVitaC(getStringValue(row.getCell(20)));
    foodDto.setCholesterol(getStringValue(row.getCell(21)));
    foodDto.setSaturatedFat(getStringValue(row.getCell(22)));
    foodDto.setTransFat(getStringValue(row.getCell(23)));
    foodDto.setIssuer(getStringValue(row.getCell(24)));

    // 완성된 FoodDto 객체 반환
    return foodDto;
}

엑셀의 행에 맞추어 저장하고 싶은 컬럼명에 맞추어 지정해주고 이를 FoodDto 객체로 반환하게 한다.

 

// 주어진 셀로부터 문자열 값을 추출하는 유틸리티 메서드
private static String getStringValue(Cell cell) {
    return cell == null ? null : cell.getStringCellValue();
}

// 트랜잭션 내에서 엑셀에서 읽어온 데이터를 Food 엔티티로 변환하여 데이터베이스에 저장하는 메서드
@Transactional
public void saveFoodData() {
    try {
        // 엑셀에서 데이터를 읽어오는 메서드를 사용하여 FoodDto 객체의 리스트를 획득
        List<FoodDto> foodDtoList = FoodService.readFromExcel(excelPath);
        
        // FoodDto 리스트를 Food 엔티티 리스트로 변환
        List<Food> foodEntities = new ArrayList<>();
        for (FoodDto foodDto : foodDtoList) {
            foodEntities.add(foodDto.toFoodEntity());
        }

        // 변환된 Food 엔티티 리스트를 데이터베이스에 저장
        foodRepository.saveAll(foodEntities);
    } catch (IOException e) {
        // IOException이 발생할 경우 예외 처리
        // 예외 처리 로직을 여기에 추가하거나, 로그에 기록하거나, 사용자에게 메시지를 전달하는 등의 처리를 수행할 수 있다.
        e.printStackTrace();
    }
}

트랜잭션 내에서 엑셀 파일에서 데이터를 읽어와서 각각의 FoodDto를 Food 엔티티로 변환하고, 그 결과를 데이터베이스에 저장하는 메서드다. @Transactional 어노테이션이 사용된 메서드는 트랜잭션으로 묶여 있어, 데이터베이스에 대한 여러 작업이 원자적으로 수행된다. 파일 읽기에서 예외가 발생하면 IOException이 처리되고, 해당 예외에 대한 로그 출력이나 다른 예외 처리 로직이 추가될 수 있다.

 

 

이 코드는 주로 Spring 프레임워크에서 사용되며, 엑셀 파일에서 데이터를 읽어오는 부분은 Apache POI 라이브러리를 사용하고 있다. Apache POI 라이브러리는 마이크로소프트 오피스 파일 포맷을 순수 자바 언어로서 읽고 쓰는 기능을 제공한다. 이를통해 엑셀 파일의 첫 번째 시트에서 데이터를 읽어온 후, 헤더 행을 건너뛰고 각 행을 FoodDto 객체로 변환하여 리스트에 추가하는 역할을 한다.

 

Controller

// FoodService 의존성 주입을 위한 생성자
@Autowired
public FoodController(FoodService foodService) {
    this.foodService = foodService;
}

// 엑셀에서 읽어온 데이터를 처리하여 데이터베이스에 저장하는 엔드포인트
@PostMapping("/upload")
public ResponseEntity<String> uploadFoodData() {
    // FoodService를 통해 엑셀 데이터를 읽어와 데이터베이스에 저장
    foodService.saveFoodData();
    
    // 클라이언트에게 성공적으로 처리되었음을 응답
    return ResponseEntity.ok("Food data uploaded and processed successfully!");
}

이 코드는 엑셀에서 데이터를 읽어와서 데이터베이스에 저장하는 엔드포인트를 제공한다. @Autowired 어노테이션이 사용된 생성자를 통해 FoodService를 주입받아 컨트롤러 내에서 사용할 수 있도록 한다. @PostMapping("/upload") 어노테이션이 사용된 메서드는 HTTP POST 요청이 "/upload" 경로로 들어올 때 실행되며, foodService.saveFoodData()를 호출하여 엑셀 데이터를 처리하고 데이터베이스에 저장한다. 마지막으로, ResponseEntity.ok("Food data uploaded and processed successfully!")를 통해 성공 응답을 반환한다.

 

이를 모두 구성하여 실행을 시키고 Postman 이나 Swagger를 이용하여 upload Controller를 작동시키면 

이런식으로 작동을 하며 작동을 마치면

보는것과 같이 해당 데이터베이스에 정상적으로 데이터들이 저장된것을 확인할 수 있다.

(컬럼의 순서는 따로 지정해줄 수 있는게 아니라는데, DTO나 Entity에서 작성한 순서가 아닌 뒤죽박죽으로 되어있어서 여유가 된다면 컬럼들을 순서대로 저장해주는 방법도 추후 포스팅 하도록 하겠다.)