[SPRING] EXCEL 업로드하여 데이터 처리하기 #1

엑셀 데이터를 파싱하여 Database를 업데이트할 수 있도록 해달라는 요청사항에 따라 개발을 수행하게 되었다.

까먹지 않기 위해 포스팅을 작성해본다.

---

시나리오

사이트의 장기 미사용자에 대해 문자메시지를 발송 후 발송된 시점으로 Database 업데이트

1. 웹페이지에서 파일을 등록하고 업데이트 버튼을 클릭

2. 서버에서 엑셀 파일 파싱

3. Dabase에 사용자 존재여부 확인

4. 존재한다면 database update

5. update된 사용자 수 페이지로 전달

 

예제로 아래와 같은 엑셀을 전달받을 경우 처리하는 방법에 대해 작성하겠다.

---

pom.xml

필요한 라이브러리는 아래와 같다.

파일의 type을 체크하여 에러를 발생시키기 위해 apache tika 라이브러리를 추가했다.

	<!-- POI(excel work) -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.monitorjbl</groupId>
            <artifactId>xlsx-streamer</artifactId>
            <version>2.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-collections4</artifactId>
            <version>4.4</version>
        </dependency>
        
	<!-- tika -->
        <dependency>
            <groupId>org.apache.tika</groupId>
            <artifactId>tika-core</artifactId>
            <version>1.24</version>
        </dependency>

---

Controller

서버로 전달된 파일을 service로 전달하도록 작성했다.

파일이 없을 때 error가 전달되도록 작성했다.

방장은 RuntimeException을 상속받는 FailException class를 작성하여 예외나 에러 발생시 HTTPStatus Code 422과 에러메시지를 전달하도록 개발했다. 서버에서 422이 올 경우 프론트에서 요청을 실패로 판단하고 에러메시지를 출력하도록 개발하였다.

 

동작방식

MultipartHttpServletRequest로 객체에 파일과 비밀번호를(없는 경우 ""로 전달) Service instance로 전달한다.

import com.doomole.stockproject.exception.FailException;
import com.doomole.stockproject.service.ExcelUploadService;
import lombok.RequiredArgsConstructor;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import java.util.Iterator;

@RestController
@RequiredArgsConstructor
@CrossOrigin(origins = "http://localhost:3000")
public class ExcelUploadController {

    private ExcelUploadService excelUploadService;

    @RequestMapping(value = "/api/excel/upload", method = RequestMethod.POST)
    public ResponseEntity<Integer> excelUpload(MultipartHttpServletRequest req) {
        int updateCount = 0;
        // 파일 받기
        Iterator<String> iterator = req.getFileNames();
        String password = req.getParameter("password");

        if(iterator.hasNext()) {
            String fileName = iterator.next();
            MultipartFile mFile = req.getFile(fileName);
            updateCount = excelUploadService.excelUpload(mFile, password);
        // 파일이 없는 경우
        } else {
            throw new FailException("파일이 존재하지 않음");
        }

        return new ResponseEntity<>(updateCount, HttpStatus.OK);
    }
}

---

Service

코드가 길어 class 내부에 method별로 나누어 작성하였다.

 

1. excelUpload

controller에서 호출하는 method로 return type은 int이다.(update된 account의 개수)

1) MultipartFile을 File객체로 변환한다.

2) File을 inputStream 객체로 만든다. 비밀번호가 있을 경우 poi의 Decryptor 객체에 파일을 담아 비밀번호 해제 후 만든다.

3) 파일의 type을 검증한다.(Tika라이브러리 사용)

★ inputstream 객체를 전달하는 경우 type확인을 위한 detect 메서드 호출 시 stream이 소멸되어 해당 객체를 사용할 수 없었다.

따라서 BufferedInputStream 객체로 변환하여 전달하니 해당 문제가 해결되었다.(다른 사용방법이 있으면 댓글로 공유 부탁드립니다.)

4) StreamingReader를 통해 Workbook 객체를 생성한다.

★ Workbook 객체를 바로 생성할 경우 데이터가 많은 엑셀 파일의 경우 apache poi(XSSFWorkbook API) 가 갖고 있는 메모리 이슈로 인해 full gc가 지속적으로 발생한다.

방장은 StreamingReader 라이브러리를 사용하여 해결하였다.(다른 해결방법이 있으면 댓글로 공유 부탁드립니다.)

※ library git page(https://github.com/monitorjbl/excel-streaming-reader)

5) workbook의 전체 시트 개수를 구하고, 개수만큼 반복하여 시트별로 데이터를 읽는다.

public int excelUpload(MultipartFile mFile, String password) {
    File file = new File(mFile.getOriginalFilename());
    InputStream dataStream = null;

    // File 객체로 변환
    try {
        mFile.transferTo(file);
    } catch(Exception e) {
        throw new FailException("파일 전송 에러");
    }

    if(password == null || password.equals("")) {
        try {
            dataStream = new FileInputStream(file);
        } catch(Exception e) {
            throw new FailException("dataStream 생성 에러");
        }
    } else {
        try {
            POIFSFileSystem fileSystem = new POIFSFileSystem(file);
            EncryptionInfo info = new EncryptionInfo(fileSystem);
            Decryptor decryptor = Decryptor.getInstance(info);
            // 비밀번호 불일치
            if (!decryptor.verifyPassword(password)) {
                throw new FailException("비밀번호 불일치");
            }
            dataStream = decryptor.getDataStream(fileSystem);
        } catch(Exception e) {
            throw new FailException("엑셀 파싱 실패" + e.getMessage());
        }
    }
    BufferedInputStream bufferedInputStream = new BufferedInputStream(dataStream);

    try {
        if (!FileUtil.validExcelFile(bufferedInputStream)) {
            throw new FailException("파일타입이 엑셀이 아닙니다.");
        }
    } catch(Exception e) {
        throw new FailException("tika error - " + e.getMessage());
    }

    Workbook workbook = StreamingReader.builder().rowCacheSize(100).bufferSize(4096).open(bufferedInputStream);

    int updateCount = 0;
    // sheet별 반복
    for(int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);
        ArrayList<String[]> list = getUpdateAccountList(sheet);
        updateCount += excelUploadService.updateAccountDatetime(list);
    }
    return updateCount;
}

---

2. FileUtil

파일의 확장자를 확인하기 위한 util class이다.

BufferedInputStream 객체를 받아서 Tika 라이브러리의 Detector 객체를 통해 MediaType을 구한다.

방장은 엑셀 파일인지 검증을 하기 위해 사용했으므로 파일의 mediaType이 엑셀 mediaType과 일치할 경우 true를 반환했다.

import java.io.BufferedInputStream;
import org.apache.tika.config.TikaConfig;
import org.apache.tika.detect.Detector;
import org.apache.tika.io.TikaInputStream;
import org.apache.tika.metadata.Metadata;
import org.apache.tika.mime.MediaType;

public class FileUtil {
    public static boolean validExcelFile(BufferedInputStream bufferedInputStream) throws Exception {

        TikaInputStream tikaInputStream = TikaInputStream.get(bufferedInputStream);

        TikaConfig tikaConfig = new TikaConfig();
        Detector tikaDetector = tikaConfig.getDetector();

        MediaType mediaType = tikaDetector.detect(tikaInputStream, new Metadata());
        String[] allowedMIMETypesEquals = {
                //"application/zip",    // .zip
                //"application/pdf",    // .pdf
                //"application/msword", // .doc, .dot
                //"application/x-hwp", "applicaion/haansofthwp", "application/x-tika-msoffice", // .hwp
                "application/x-tika-ooxml"  // .xlsx, .pptx, .docx
        };
        for (int i=0; i<allowedMIMETypesEquals.length; i++) {
            if (mediaType.toString().equals(allowedMIMETypesEquals[i])) {
                return true;
            }
        }
        System.out.println(mediaType);
        String[] allowedMIMETypesStartsWith = {
                //"image",    // .png, .jpg, .jpeg, .gif, .bmp
                //"text",     // .txt, .html 등
                //"application/vnd.ms-word",          // .docx 등 워드 관련
                "application/vnd.ms-excel",         // .xls 등 엑셀 관련
                //"application/vnd.ms-powerpoint",    // .ppt 등 파워포인트 관련
                "application/vnd.openxmlformats-officedocument",    // .docx, .dotx, .xlsx, .xltx, .pptx, .potx, .ppsx
                //"applicaion/vnd.hancom"     // .hwp 관련
        };

        for (int i=0; i<allowedMIMETypesStartsWith.length; i++) {
            if (mediaType.toString().startsWith(allowedMIMETypesStartsWith[i])) {
                return true;
            }
        }
        return false;
    }
}

---

3. getUpdateAccountList

Sheet 객체를 전달 받아 Row를 하나씩 읽는다.

1) 가장 위에 row는 data가 아니므로 패스한다.

2) 셀의 개수를 얻는다. 만약 첫 번째 셀이 빈값이라면 마지막이라고 판단하고 반복을 종료한다.

★ 처음엔 Sheet.getPhysicalNumberOfRows() 메서드로 로우의 마지막행을 얻는 방법을 사용했는데, 끝나는 행이 비정상으로 출력되었다.(데이터는 1000건이었으나 마지막 행을 10만으로 출력했다.) 따라서 빈값이 없다는 전제하에 행에 첫번째 cell이 빈값일 경우 종료되도록 개발하였다.

3) Row에서 cell을 하나씩 읽는다. ExcelUtil을 만들어 데이터 Type에 맞추어 값을 가져오게끔 개발하였다.

★ 위의 엑셀파일에서 4열의 경우 데이터가 시간이어서 년/월/일로 가져오게끔 되어있는 util의 메서드를 사용할 수 없어 시/분/초로 가져오게끔 조건문을 달았다.

4) 객채에 데이터를 담은 후 list에 담아 반환한다.

    public ArrayList getUpdateAccountList(Sheet sheet) {
        ArrayList<String[]> list = new ArrayList<>();
        int count = 0;
        //행을읽는다
        for(Row row : sheet) {
            // [1] 가장 위는 패스
            if(count == 0) {
                count++;
                continue;
            } else {
                count++;
            }
            // [2] 셀의 수
            int cells = row.getLastCellNum();
            String[] arr = new String[cells];
            // 셀이 빈값이라면 마지막행으로 간주하고 for문 종료
            if(row.getCell(0) == null) {
                break;
            }
            for (int columnindex = 0; columnindex <= cells; columnindex++) {
                String value = "";
                try {
                    // [3] 셀값을 읽는다
                    Cell cell = row.getCell(columnindex);
                    // 시/분/초만 있는 cell에 대한 변환
                    if(columnindex == 3) {
                        Date date = cell.getDateCellValue();
                        value = new SimpleDateFormat("HH:mm:ss").format(date);
                    } else {
                        value = ExcelUtil.getValue(cell);
                    }
                    // [4]
                    arr[columnindex] = value;
                } catch(Exception e) {
                    throw new FailException(count + "행 파싱 실패 오류 : " + e.getMessage());
                }
            }
            list.add(arr);
        }

        return list;
    }

 

 

엑셀 데이터를 읽고 처리하는 내용에 대한 설명은 끝이 났다.

다음 포스팅에서는 방장이 해당 데이터를 이용하여 Database를 업데이트한 작업에 대해 작성하였다.

[SPRING] EXCEL 업로드하여 데이터 처리하기 #2

---

* 질문이나 문의사항, 피드백은 댓글로 남겨주세요.