[Java] ExcelUpload - 파일 읽고 DB에 insert 하기

• Java 17
• Maven
• Spring Boot 3.0.3
• Oracle 11g
• IntelliJ Ultimate
• DBeaver

 

SpringBoot 환경에서 메서드만 이용하여 Excel Upload 기능 할 일이 있어서 테스트 Class 에서 진행하였다 

main 메서드를 이용해서 하려면 Junit 을 이용하도록 하자

main 메서드에서는 mapper를 이 방법 처럼 사용 할 수 없다 따로 연결을 해주어야 하니 간편하게

Junit 혹은 Test Class 를 적극 활용하자

 

Maven - dependency // xls , xlsx 확장자를 사용하기 위함

<!-- POI -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.1.2</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.2</version>
</dependency>

 

 

 

excel Upload 

package com.example.practice;

import com.example.practice.mapper.user.UserMapper;
import com.example.practice.vo.UserVo;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

@SpringBootTest
class PracticeApplicationTests {

    // DB에 값을 넣기 위한 mapper
    @Autowired
    private UserMapper mapper;

    @Test
    void contextLoads() {
    }

    @Test
    void excelUpload(){
        String path = "C:\\Excel\\test.xlsx";

        // 엑셀 데이터를 담기 위한 vo
        UserVo vo = null;

        File file = new File(path);

        try {
            FileInputStream fileInputStream = new FileInputStream(file);

            // .xlsx 확장자
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);

            // .xls 확장자
//            HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);

            int seetNum = workbook.getNumberOfSheets();

            for (int i = 0; i < seetNum; i++) {
                // .xlsx 확장자
                XSSFSheet sheet = workbook.getSheetAt(i);

                // .xls 확장자
//                HSSFSheet sheet = workbook.getSheetAt(i);

                int rows = sheet.getPhysicalNumberOfRows();

                // 엑셀 데이터를 읽어서 담기 위한 map
                Map<Integer, String> map = new HashMap();
                
                // 0번째 행은 이름인 경우가 대부분이기에 1부터 시작함
                // 필요에 따라 0부터 시작하면 됨
                for (int j = 1; j < rows; j++) {

                    // .xlsx 확장자
                    XSSFRow row = sheet.getRow(j);

                    // .xls 확장자
//                    HSSFRow row = sheet.getRow(j);

                    int cells = row.getPhysicalNumberOfCells();

                    for (int k = 0; k < cells; k++) {
                        // .xlsx 확장자
                        XSSFCell cell = row.getCell(k);

                        // .xls 확장자
//                        HSSFCell cell = row.getCell(k);

                        String value = "";

                        if (cell == null) {
                            continue;
                        } else {
                            switch (cell.getCellType()) {
                                case FORMULA:
                                    value = cell.getCellFormula();
                                    break;
                                case NUMERIC:
                                    // 숫자를 읽어올때 예) 123456789 가 1.23455678+123 이런식으로 출력이 돼서 String 으로 변환해서 받는다
//                                    value = cell.getNumericCellValue() + "";
                                    value = String.valueOf((int) cell.getNumericCellValue()) + "";
                                    break;
                                case STRING:
                                    value = cell.getStringCellValue() + "";
                                    break;
                                case BLANK:
                                    value = cell.getBooleanCellValue() + "";
                                    break;
                                case ERROR:
                                    value = cell.getErrorCellValue() + "";
                                    break;
                            }
                        }
                        System.out.println(j +"번 행, " + k +" 번 열 값 : "+ value);
                        map.put(k, value);
                    }
                    System.err.println("test : " + map.toString());

                    /**
                     * 엑셀 데이터를 vo에 담기
                     */
                    vo = new UserVo();

                    vo.setId(map.get(0));
                    vo.setName(map.get(1));
                    vo.setAge(Integer.parseInt(map.get(2)));


                    /**
                     * 중복체크
                     * 테이블에 데이터를 넣을때 pk가 지정되어 있다면 중복된 값이 들어가지 않도록 체크
                     * 중복과 pk 상관없이 그대로 넣는 상황이라면 해당 구문이 필요 없다
                     */
//                    int check = mapper.checkId(vo.getId());
//                    if(check == 0){
//                        mapper.excelUpload(vo);
//                    }

                    mapper.excelUpload(vo);  // 데이터 insert
                }

            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

}

String path 에서 확장자만 잘라내어 확장자에 따라  XSSFWorkBook 또는 HSSFWorkbook 으로 변환해주는

로직을 구현해서 사용하면 좀 더 간편하게 사용 할 수 있다

 

혹시나 .csv 확장자 엑셀 파일이라면 어렵게 생각 할 거 없이

해당 파일을 다른 이름으로 저장 해서 확장자를 xls , xlsx로 변환하여 돌리면 된다

 

 

 

userVo

public class UserVo {
    private String id;
    private String name;
    private String password;

    private int age;
    
    ... // getter setter
    ...
    ...
    }

 

 

UserMapper.java

@Mapper
public interface UserMapper {

    /**
     * 중복 값 확인
     * @param id
     * @return
     */
    int checkId(String id);


    /**
     * db에 insert
     * @param vo
     */
    void excelUpload(UserVo vo);
    
    }

 

 

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.practice.mapper.user.UserMapper">

    <!-- 중복되는 id 찾기 -->
    <select id="checkId" resultType="int">
        SELECT
            COUNT(ID)
        FROM USERMEMBER
        WHERE ID = #{id}
    </select>

    <!-- 엑셀 데이터 insert -->
    <insert id="excelUpload" parameterType="com.example.practice.vo.UserVo">
        INSERT INTO USERMEMBER
            (id, name, age)
        VALUES
            (#{id},#{name}, #{age})
    </insert>

</mapper>

 

 

application.properties

# Database
#spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
#spring.datasource.url=jdbc:oracle:thin:@localhost:1521/XE
spring.datasource.driverClassName=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.url=jdbc:log4jdbc:oracle:thin:@localhost:1521/XE
spring.datasource.username=system
spring.datasource.password=0000


# VO location
mybatis.type-aliases-package=com.example.practice.vo

# xml location
mybatis.mapper-locations=classpath:mappers/**/*.xml

 

본인 환경에 따라 vo , mapper, application.properties 를 수정해서 사용하면 되겠다

 

 

Test

엑셀 데이터

 

결과