728x90
반응형
직전 프로젝트 두 번째 복기를 해볼까 한다.
우선 간단하게 설명하자면, 나는 사용자에게 파일을 받아 이를 HTTPFS를 통해 HDFS에 저장하고, 내용을 파싱해서 DB에 저장한느 프로그램을 개발했다.
이 때 받은 파일은 총 두 가지 종류로, CSV 파일 혹은 엑셀 파일 (xls, xlsx) 이다.
그래서 이번에는 POI 라이브러리를 통해 엑셀 파일을 파싱하는 코드를 공유해볼까 한다.
POI 라이브러리
POI ( Poor Obfuscation Implementation)는 Apache에서 개발한 Java용 라이브러리이로 Microsoft Office 문서 형식(예: Excel, Word, PowerPoint 등)을 생성, 수정 및 읽기 위한 라이브러리이다. 해당 라이브러리를 통해 Java 애플리케이션에서 Microsoft Office 파일을 다룰 수 있다.
Spring에서 사용하기 위해서는 아래 Dependency를 추가해주면 된다.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.5</version>
</dependency>
프로젝트 생성
프로젝트는 간단하게 MVC로 만들까 한다.
- Spring Boot 3.2.7
- JAVA 17
또한 별다른 디펜던시는 필요가 없고, poi는 위에서 작성한대로 수기로 작성할 것이기에 아래와 같이 Web과 Lombok 정도만 추가한다.
코드 작성
간단하게 아래의 로직대로 개발할까 한다.
- 사용자에게 파일을 전달받는다.
- 임시 파일로 저장한다
- 데이터를 파싱해서 콘솔에 출력한다.
정말 간단한 프로그램이므로, 아래와 같이 구성해서 작성한다.
- CommonCode : 원래대로라면 컨트롤러와 서비스가 주고받기 위한 시그널 집합
- UploadService : 컨트롤러에서 파일을 받아 비즈니스 로직 수행
우선 CommonCode 클래스는 아래와 같이 간단하게 작성했다.
CommonCode.java
package com.mwlee.test.common;
public class CommonCode {
public static final Integer SUCCESS=1;
public static final Integer FAIL=2;
public static final Integer ERR_INTERNAL=3;
public static final Integer EMPTY_FILE=4;
public static final Integer FILE_EXISTS=5;
public static final Integer INVALID_FILE=6;
}
다음은 Service이다. 코드 공개에 앞서 사설을 붙이자면 엑셀 파일을 읽어 workbook을 여는 방법은 아래처럼 다양하게 존재할 수 있다.
Workbook workbook = WorkbookFactory.create(file)
하지만 나는 위의 방식보다는 아래의 방식으로 파일을 여는 방법을 채택했다.
OPCPackage pkg = OPCPackage.open(tmpFile);
Workbook workbook = new XSSFWorkbook(pkg);
우선 이유라면 간단한데, 개발한 프로그램은 윈도우 서버가 아니라 리눅스 서버에서 돌아가야하는 프로그램이었다.
하지만 윈도우에서는 잘 버티던 프로그램이 사양이 더 짱짱함에도 불구하고 리눅스 서버에서만 돌리면 속도가 너무 느리고, 또 파일내 데이터가 너무 커지면 해당 라이브러리 자체가 먹통이 되어 더 이상 로직이 진행되지 않는 현상이 발생했다. (윈도우 기준 1,000,000개 row 까지 버티던 게 리눅스에서는 못버팀. 500,000개가 최대였음.)
그래서 챗 지피티에게 문의한 결과 XSSFWorkbook을 사용하면 대용량 엑셀 파일을 처리하는데 최적화되더 있으며 메모리 사용량을 줄이고 처리 속도를 향상시킬 수 있다는 답변을 받았기 때문에 채택하게 되었다.
솔직히 WorkbookFactory.create 함수를 사용했다고 하더라도 어차피 xlsx 파일은 XSSFWorkbook으로 만들어진다. 게다가 위 방법은 XSSF가 아니라 SXSSF를 사용해야 한다. 고로 위 답변은 틀린 답변으로 생각이 된다.
어쨌든 xls를 사용할 일은 없고, 100만개의 데이터까지 굳이 버텨야 할 이유가 없으므로 xlsx만 받도록 개발을 진행했다.
바로 윗 줄에서 갑자기 xls가 나왔는데, 기본적으로 엑셀은 xls나 xlsx가 사용된다. 대충 MS Office 97-2003은 .xls, MS Office .xlsx라고 생각하면 쉽다.
그리고 .xls는 HSSFWorkbook 라이브러리를 사용해야하고, .xlsx는 XSSFWorkbook 라이브러리를 사용해야 한다. workbookFactory 사용 시 해당 라이브러리 중 하나를 자동으로 매핑해주지만, 위 코드는 XSSFWorkbook만을 사용하기에 .xls는 받을 수 없기에 한 말이다.
사설은 여기까지하고, 코드는 아래와 같이 작성했다.
별도의 설명은 주석으로 갈음한다.
UploadService.java
package com.mwlee.test.service;
import java.io.File;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import com.mwlee.test.common.CommonCode;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@Service
public class UploadService {
private static final String tmpDir = "C:\\files\\";
private DecimalFormat decimalFormat = new DecimalFormat("0");
/**
* MultiPartFile을 File로 임시저장
*
* @param file
* @return
* @throws IOException
*/
public File saveAndGetTmpFile(MultipartFile file) throws IOException {
File tmpFile = new File(tmpDir + file.getOriginalFilename());
file.transferTo(tmpFile);
return tmpFile;
}
/**
* 임시파일 삭제
*
* @param file
* @return
*/
public boolean deleteTmpFile(File file) {
file.delete();
if(file.exists()) {
return false;
}
return true;
}
/**
* 엑셀 파일을 업로드받아 파싱
*
* @param file
* @return
*/
public Integer uploadExcelFile(MultipartFile file) {
File tmpFile = null;
try {
tmpFile = saveAndGetTmpFile(file);
}
catch(IOException e) {
log.error("@@ Error Occured While Save TmpFile : {}", e.getMessage());
return CommonCode.ERR_INTERNAL;
}
try {
// 파일을 열어서 Workbook 생성
OPCPackage pkg = OPCPackage.open(tmpFile);
Workbook workbook = new XSSFWorkbook(pkg);
// 첫 번째 시트만 연다.
Sheet sheet = workbook.getSheetAt(0);
// 한 줄씩 파싱 (keys : 맨 윗줄로 컬럼들 / values : 둘째줄부터 입력된 values들)
List<String> keys = new ArrayList<>();
List<List<String>> values = new ArrayList<>();
for(Row row : sheet) {
// 테스트용이므로 전부 String으로 받기
List<String> arr = new ArrayList<>();
// 뒷글에서 설명하겠지만 아래와 같이 하면 안됨.
for(Cell cell : row) {
switch(cell.getCellType()) {
// POI 라이브러리는 CELL 내용의 타입에 따라 분기되어야 함.
case STRING:
arr.add(cell.getStringCellValue().trim());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
// 날짜도 숫자로 입력되기에 별도 처리
arr.add(cell.getDateCellValue().toString());
}
else {
double numericValue = cell.getNumericCellValue();
// 숫자를 일반적인 형식으로 표현 : 이렇게 안하면 3E+03 같은 게 나옴.
arr.add(decimalFormat.format(numericValue));
}
break;
default :
arr.add("");
}
}
if(row.getRowNum() == 0) {
// 첫째 줄이면 keys에 입력
keys = arr;
}
else {
values.add(arr);
}
}
// 잘 입력됐나 출력해보기
for(String key : keys) {
System.out.print(key + "\t");
}
System.out.println();
for(List<String> value : values) {
for(String val : value) {
System.out.print(val + "\t");
}
System.out.println();
}
} catch (InvalidFormatException e) {
log.error("@@ Error Occured While Open OPCPackage : {}", e.getMessage());
return CommonCode.INVALID_FILE;
} catch (IOException e) {
log.error("@@ Error Occured While Open Workbook : {}", e.getMessage());
return CommonCode.INVALID_FILE;
} finally {
// 임시 파일 삭제
if(!deleteTmpFile(tmpFile)) {
log.warn("@@ Can't Delete TmpFile {}. Delete Manually.", tmpFile.getName());
}
}
return CommonCode.SUCCESS;
}
}
테스트
이제 테스트를 해보자 아래와 같은 엑셀 파일을 생성했다.
그리고 테스트 자바 파일을 작성한다.
package com.mwlee.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;
import com.mwlee.test.common.CommonCode;
import com.mwlee.test.service.UploadService;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@SpringBootTest
class FileparserTestApplicationTests {
@Autowired UploadService service;
@Test
void contextLoads() {
}
@Test
void testUploadExcel() {
try {
// 사용자에게 업로드되었다고 가정하기 위해 MultipartFile 전송
File file = ResourceUtils.getFile("C://test-excel.xlsx");
InputStream is = new FileInputStream(file);
MultipartFile mFile = new MockMultipartFile("file", file.getName(), "application/octet-stream", is);
Integer result = service.uploadExcelFile(mFile);
if(result == CommonCode.SUCCESS) {
log.info("@@ Test Scuuess.");
}
else if(result == CommonCode.INVALID_FILE) {
log.info("@@ Test Fail. Invalid File");
}
else if(result == CommonCode.ERR_INTERNAL) {
log.info("@@ Test Fail. Error Internal");
}
}
catch(Exception e) {
e.printStackTrace();
}
}
}
테스트
이제 테스트를 돌려보자.
정상적으로 돌아감을 확인할 수 있다.
그런데 한 가지 의문이 생길 것이다. 서비스 코드를 자세히 읽어보면 아래와 같은 주석을 달아놓았다.
개발을 하던 중 중간중간 데이터가 빠진 엑셀 파일들에 대한 테스트를 진행했었다.
예시로 들면 아래와 같다.
만약 이렇게 데이터가 주어지면 어떻게 처리가 될까?
한 번 돌려보자.
보이는 것과 같이 1st_col, 2nd_col이 null이 아닌 다음 값이 삽입되어있다.
문제가 된 코드는 아래 코드이다.
for(Cell cell : row) {
switch(cell.getCellType()) {
// POI 라이브러리는 CELL 내용의 타입에 따라 분기되어야 함.
case STRING:
arr.add(cell.getStringCellValue().trim());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
// 날짜도 숫자로 입력되기에 별도 처리
arr.add(cell.getDateCellValue().toString());
}
else {
double numericValue = cell.getNumericCellValue();
// 숫자를 일반적인 형식으로 표현 : 이렇게 안하면 3E+03 같은 게 나옴.
arr.add(decimalFormat.format(numericValue));
}
break;
default :
arr.add("");
}
}
보다싶이 row의 각 cell을 순회하는데, 만약 빈 셀이 있을 경우 이를 null로 취급하지 않고 아예 무시하고 그 다음 셀로 넘어가는 것이 문제이다.
이를 해결하기 위해서는 for each보다는 for 문으로 셀의 숫자를 지정하고, null일 경우에 대한 처리를 MissingCellPolicy로 지정하면 된다.
코드는 아래와 같이 변경되면 된다.
for(int cn=0; cn<row.getLastCellNum(); cn++) {
// 빈 값 체크
Cell cell = row.getCell(cn, MissingCellPolicy.CREATE_NULL_AS_BLANK);
if(cell == null) {
arr.add(null);
continue;
}
// 빈 값이 아닐 경우 원래 로직
switch(cell.getCellType()) {
// POI 라이브러리는 CELL 내용의 타입에 따라 분기되어야 함.
case STRING:
arr.add(cell.getStringCellValue().trim());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
// 날짜도 숫자로 입력되기에 별도 처리
arr.add(cell.getDateCellValue().toString());
}
else {
double numericValue = cell.getNumericCellValue();
// 숫자를 일반적인 형식으로 표현 : 이렇게 안하면 3E+03 같은 게 나옴.
arr.add(decimalFormat.format(numericValue));
}
break;
default :
arr.add("");
}
}
추가로 아래와 같은 데이터가 들어올 때, col_3같이 뒤에 있는 데이터를 null로 처리할 수 있도록 예외를 하나 더 생성한다.
| col_1 | col_2 | col_3 |
| 1 | 2 |
if(row.getRowNum() == 0) {
// 첫째 줄이면 keys에 입력
keys = arr;
}
else {
while(arr.size() != keys.size()) {
/**
* 중간 값이 비어있는건 문제가 없지만 마지막 값이 비어있는 건 문제가될 수 있음
* 예를 들어
*
* col_1 col_2 col_3
* 1 3
*
* 이면 (1, null, 3)이지만
*
* col_1 col_2 col_3
* 1 2
* 이면 (1, 2)가 됨.
* 고로 size()가 맞지 않으면 뒤에 null들을 추가해줘야 함.
*/
arr.add(null);
}
values.add(arr);
}
이를 변경해서 다시 돌려보면 아래와 같이 빈 값에 대한 예외가 잘 처리된다.
그리고 이를 적용한 Service 코드는 아래와 같다.
UploadService.java
package com.mwlee.test.service;
import java.io.File;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import com.mwlee.test.common.CommonCode;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@Service
public class UploadService {
private static final String tmpDir = "C:\\files\\";
private DecimalFormat decimalFormat = new DecimalFormat("0");
/**
* MultiPartFile을 File로 임시저장
*
* @param file
* @return
* @throws IOException
*/
public File saveAndGetTmpFile(MultipartFile file) throws IOException {
File tmpFile = new File(tmpDir + file.getOriginalFilename());
file.transferTo(tmpFile);
return tmpFile;
}
/**
* 임시파일 삭제
*
* @param file
* @return
*/
public boolean deleteTmpFile(File file) {
file.delete();
if(file.exists()) {
return false;
}
return true;
}
/**
* 엑셀 파일을 업로드받아 파싱
*
* @param file
* @return
*/
public Integer uploadExcelFile(MultipartFile file) {
File tmpFile = null;
try {
tmpFile = saveAndGetTmpFile(file);
}
catch(IOException e) {
log.error("@@ Error Occured While Save TmpFile : {}", e.getMessage());
return CommonCode.ERR_INTERNAL;
}
try {
// 파일을 열어서 Workbook 생성
OPCPackage pkg = OPCPackage.open(tmpFile);
Workbook workbook = new XSSFWorkbook(pkg);
// 첫 번째 시트만 연다.
Sheet sheet = workbook.getSheetAt(0);
// 한 줄씩 파싱 (keys : 맨 윗줄로 컬럼들 / values : 둘째줄부터 입력된 values들)
List<String> keys = new ArrayList<>();
List<List<String>> values = new ArrayList<>();
for(Row row : sheet) {
// 테스트용이므로 전부 String으로 받기
List<String> arr = new ArrayList<>();
for(int cn=0; cn<row.getLastCellNum(); cn++) {
// 빈 값 체크
Cell cell = row.getCell(cn, MissingCellPolicy.CREATE_NULL_AS_BLANK);
if(cell == null) {
arr.add(null);
continue;
}
// 빈 값이 아닐 경우 원래 로직
switch(cell.getCellType()) {
// POI 라이브러리는 CELL 내용의 타입에 따라 분기되어야 함.
case STRING:
arr.add(cell.getStringCellValue().trim());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
// 날짜도 숫자로 입력되기에 별도 처리
arr.add(cell.getDateCellValue().toString());
}
else {
double numericValue = cell.getNumericCellValue();
// 숫자를 일반적인 형식으로 표현 : 이렇게 안하면 3E+03 같은 게 나옴.
arr.add(decimalFormat.format(numericValue));
}
break;
default :
arr.add(null);
}
}
if(row.getRowNum() == 0) {
// 첫째 줄이면 keys에 입력
keys = arr;
}
else {
while(arr.size() != keys.size()) {
/**
* 중간 값이 비어있는건 문제가 없지만 마지막 값이 비어있는 건 문제가될 수 있음
* 예를 들어
*
* col_1 col_2 col_3
* 1 3
*
* 이면 (1, null, 3)이지만
*
* col_1 col_2 col_3
* 1 2
* 이면 (1, 2)가 됨.
* 고로 size()가 맞지 않으면 뒤에 null들을 추가해줘야 함.
*/
arr.add(null);
}
values.add(arr);
}
}
// 잘 입력됐나 출력해보기
for(String key : keys) {
System.out.print(key + "\t");
}
System.out.println();
for(List<String> value : values) {
for(String val : value) {
System.out.print(val + "\t");
}
System.out.println();
}
} catch (InvalidFormatException e) {
log.error("@@ Error Occured While Open OPCPackage : {}", e.getMessage());
return CommonCode.INVALID_FILE;
} catch (IOException e) {
log.error("@@ Error Occured While Open Workbook : {}", e.getMessage());
return CommonCode.INVALID_FILE;
} finally {
// 임시 파일 삭제
if(!deleteTmpFile(tmpFile)) {
log.warn("@@ Can't Delete TmpFile {}. Delete Manually.", tmpFile.getName());
}
}
return CommonCode.SUCCESS;
}
}728x90
반응형
'실습 > 리눅스 서버 + 스프링 부트' 카테고리의 다른 글
| [JavaScript/HTML] 새 창에서 열기 시 response에 따른 분기 (0) | 2024.03.31 |
|---|---|
| [JAVA] POI 라이브러리를 사용한 엑셀 파일 만들기 (2) | 2024.03.31 |
| [Webflux] Hadoop HTTPFS에 파일 업로드 (0) | 2024.03.30 |
| [Spring JPA] Dynamic Insert/Update (0) | 2023.12.19 |
| [Spring JPA] JPA에서도 PK가 변경될까? (1) | 2023.12.18 |
