Intro
이번달도 계속되는 걍진커미션...
액셀의 내용을 DB에 저장해보고 싶다.
이번 포스팅에서는 Gradle 라이브러리 설치부터 엑셀 내용을 출력하는 테스트를 해보겠다.
Apache POI
apache POI는 언제 사용하는가?
아파치 POI(Apache POI)는 아파치 소프트웨어 재단에서 만든 라이브러리로서 마이크로소프트 오피스 파일 포맷을 순수 자바 언어로서 읽고 쓰는 기능을 제공한다.
프로젝트는 기존의 cms 프로젝트를 사용해서 진행하겠다.
pymthstr에서 구매 내역을 엑셀로부터 DB에 저장하고
statistics에서 월별로 그룹핑한 결과를 api를 통해서 보여줄 것이다.
1. Gradle 설정
implementation ("org.apache.poi:poi:5.2.3")
implementation ("org.apache.poi:poi-ooxml:5.2.3")2. excelUpload()
PymtHstrService 클래스를 만들어서 uploadExcel() 메서드를 만든다.
단계적으로 하기 위해 먼저 값이 출력되는지 여부부터 확인하려고 한다.
import com.cms.world.pymthstr.domain.PymtHstrEntity;
import com.cms.world.pymthstr.repository.PymtHstrRepository;
import lombok.AllArgsConstructor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.util.ArrayList;
@Service
@AllArgsConstructor
public class PymtHstrService {
private final PymtHstrRepository pymtHstrRepository;
public int uploadExcel (MultipartFile file) throws Exception {
ArrayList<PymtHstrEntity> list = new ArrayList<>();
Workbook workbook = new XSSFWorkbook(file.getInputStream());
Sheet worksheet = workbook.getSheetAt(1);
for (int i = 1; i < worksheet.getPhysicalNumberOfRows(); i++) {
Row row = worksheet.getRow(i);
if (row != null) {
for (int j = 0; j <= 5; j++) {
Cell cell = row.getCell(j);
if (cell != null) {
cell.setCellType(CellType.STRING);
System.out.println("let's get cell: " +cell.getStringCellValue() + "\t");
}
}
}
}
return 0;
}
}j는 excel의 column의 범위다. 위의 엑셀 사진을 보면
나는 최대 column이 6이어서 0부터 5까지를 범위로 설정했다.
soutv를 통해 cell을 출력하면 [i][j]에 속하는 cell 한 칸의 정보가 출력된다.
참고한 블로그를 보니 엑셀의 버전에 따라서 다른 타입으로 아래 workbook을 초기화한 것을 보았다.
if (fileExtsn.equals("xls")) {
workbook = new HSSFWorkbook(file.getInputStream());
} else {
workbook = new XSSFWorkbook(file.getInputStream());
}위 코드는 .xslx, 액셀 기준임을 짚고 넘어가고 싶다.
코드 출처
https://moongproject.tistory.com/5
3. Controller & Postman
import com.cms.world.http.response.ResponseResult;
import com.cms.world.pymthstr.service.PymtHstrService;
import lombok.AllArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
@RestController
@RequestMapping("/pymt/hstr")
@AllArgsConstructor
public class PymtHstrController {
private final PymtHstrService service;
@PostMapping("/upload/excel")
public ResponseEntity<ResponseResult> excelUpload (@RequestParam("file") MultipartFile file) throws Exception {
service.uploadExcel(file);
//
return ResponseEntity.ok(ResponseResult.data("success"));
}
}
ResponseResult는 커스텀 클래스인데 그냥 빼고 해도 된다.
출력 결과만 테스트하자.
postman
출력을 하면 아래와 같이 나온다.
그런데 406에러가 떨어져서 이 또한 수정해야 한다;;
컬럼의 범위는 맞는데 날짜 부분이 이상하다;;
2023-11이 아니라 45231.0 같은 기괴한 값이 나온다.
Date 형식으로 출력하지 않고 숫자 형식으로 출력했기 때문이다.
아래처럼 코드를 변경한다.
public int uploadExcel (MultipartFile file) throws Exception {
ArrayList<PymtHstrEntity> list = new ArrayList<>();
Workbook workbook = new XSSFWorkbook(file.getInputStream());
Sheet worksheet = workbook.getSheetAt(1);
for (int i = 1; i < worksheet.getPhysicalNumberOfRows(); i++) {
Row row = worksheet.getRow(i);
if (row != null) {
for (int j = 0; j <= 5; j++) {
Cell cell = row.getCell(j);
if (cell != null) {
String[] values = new String[6]; // 데이터 저장을 위한 배열
switch (cell.getCellType()) {
case STRING:
values[j] = cell.getStringCellValue();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
// 숫자가 날짜 형식인 경우 날짜로 변환하여 문자열로 저장
values[j] = cell.getDateCellValue().toString();
} else {
// 그 외의 숫자는 일반적인 숫자로 저장
values[j] = String.valueOf(cell.getNumericCellValue());
}
break;
default:
// 다른 타입의 경우 빈 문자열로 처리
values[j] = "";
break;
}
System.out.print(values[j] + "\t");
}
}
}
}
return 0;
}숫자 형식인 경우에 Date 형식에 부합하면 변경하도록 하는 코드 및 기타 형식 처리가 추가되었다.
이제 날짜도 제대로 출력된다.
(다만 나는 월까지만 명시를 했는데 일자가 다르게 나온것을 보면 액셀 작성 시 날짜가 들어간 것이 아닐까? 생각된다.)
406 이슈
@RequestParam()을 없앴더니 해결되었다.
can not parse string null
삽질을 여기서 참 많이 했다.
난 액셀 데이터의 개수를 가져오는 getPhysicalRows() 메서드가 데이터가 있는 row만큼만 돌아가는 줄 알았는데
말 그대로 시트의 최대 row만큼 실행을 했다. (998행;;)
그러니 데이터를 다 insert하고도 null값을 만나면 기존 결과들까지 @Transactional로 롤백되어 insert에 실패했다.
그래서 처리를 했는데 왜 소량은 제대로 되면서 94건을 insert하면 마지막에 꼭 null을 못 거르고 can not parse null string 에러를 내는 것이냐....
어쩔수 없이 난 노가다를 뛰었다...
일일이 다 찍어보고
boolean 타입의 flag값을 선언해서 true로 설정해서 break;도 해보고
결론은 Row안의 cell이 null인 경우 그냥 나가버리자고 정했다.
import com.cms.world.pymthstr.domain.PymtHstrEntity;
import com.cms.world.pymthstr.repository.PymtHstrRepository;
import lombok.AllArgsConstructor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import org.apache.poi.ss.usermodel.DateUtil;
@Service
@AllArgsConstructor
public class PymtHstrService {
private final PymtHstrRepository pymtHstrRepository;
@Transactional
public void uploadExcel(MultipartFile file) throws Exception {
Workbook workbook = new XSSFWorkbook(file.getInputStream());
Sheet worksheet = workbook.getSheetAt(1);
for (int i = 1; i < worksheet.getPhysicalNumberOfRows(); i++) { // 말 그대로 시트의 998개의 행이 있으면 그걸 다 돈다
Row row = worksheet.getRow(i);
if (row != null) {
String[] values = new String[6];
for (int j = 0; j <= 5; j++) {
Cell cell = row.getCell(j);
if (!isValidCell(cell)) {
System.out.println("cell != null = " + cell != null);
return;
}
values[j] = formatCell(cell);
}
PymtHstrEntity pymtHstrEntity = PymtHstrEntity.builder()
.paymentDate(values[0])
.id(Long.parseLong(values[1]))
.username(values[2])
.amount(Long.parseLong(values[3]))
.paymentMethod(values[4])
.paymentMethodName(values[5])
.build();
pymtHstrRepository.save(pymtHstrEntity);
}
}
}
private boolean isValidCell (Cell cell) {
return cell != null && formatCell(cell) != null && !formatCell(cell).isEmpty();
}
private String formatCell (Cell cell) {
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue();
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue().toString();
} else {
return String.valueOf((int) cell.getNumericCellValue()); // 소수점 이하가 없는 경우 정수로 처리
}
default:
return "";
}
}
}
결과적으로 위와 같이 짰다.
isValidCell() 메서드 가지고 그 고생을 했다.
Outro
return할 때 데이터가 없어서인지, 행의 값이 특정 길이에 맞지 않아서인지 알려주면 더 좋지 않을까 라는 생각이 들었다.
