[Spring] poi 라이브러리를 이용한 엑셀 일괄 등록

Spring MVC + Maven 구조로 poi 라이브러리를 통해 엑셀 데이터를 읽고 DB에 저장하는 코드를 작성해봄.

스프링 강의는 많이 들었지만 전부 따라 하기만 했지 직접 구현해보는 건 처음이다 ...
그래도 완강 몇 번 하니까 어노테이션에도 익숙해지고, 구조가 잡히는 느낌이라 한 번 시도해봄!

poi 라이브러리로 엑셀 데이터 읽는 부분은 다른 사람의 코드를 참고했다.


~ 구성 ~
1. jsp에서 ajax를 통해 /excel/upload 주소로 업로드한 엑셀 파일 정보를 보냄

2. 해당 주소로 매핑된 contoller에서 정보를 받고, 적절한 service를 호출해준다

• .xls 형식일 경우 xlsExcelReader() 메서드 호출
• .xlsx 형식일 경우 xlsxExcelReader() 메서드 호출

3. service에서 엑셀 데이터를 읽고 List 객체에 저장한다

4. dao excelInsert() 메서드에 정보가 담긴 List 객체를 전달

5. JdbcTemplate을 이용해 insert 쿼리 실행 -> DB 저장



ExcelData.java
- private 변수와 getter/setter로 이루어진 DTO 파일

ExcelContoller.java

@Controller
@RequestMapping("/excel")
public class ExcelController {

	private final Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	ExcelService excelService;

	@ModelAttribute("cp")
	public String getContextPath(HttpServletRequest request) {
		return request.getContextPath();	//프로젝트 path 불러옴
	}

	@ResponseBody
	@RequestMapping(value = "/upload", method = RequestMethod.POST)
	public void excelUpload(MultipartHttpServletRequest request) {
		String excelType = request.getParameter("excelType");

		// 엑셀 파일이 xls, xlsx일 때 서비스 라우팅
		if (excelType.equals("xlsx")) {
			excelService.xlsxExcelReader(request);
		} else if (excelType.equals("xls")) {
			excelService.xlsExcelReader(request);
		}
		
	}

}

@Controller로 컨트롤러라고 명시해주고, @RequestMapping으로 주소 매핑을 해주었다.
원래 의존성 주입은 생성자에다 해주는 게 좋지만, 간단하게 테스트하는 목적이므로 대충 @Autowired 시켜줬다.

slf4j를 이용한 logger도 하나 선언해주었다. 막상 구현하면서 쓸 만한 상황은 안 나오긴 했는데
일단 배운건 다 써먹어야지.. 강의에 매번 빠짐없이 나와서 이젠 눈 감고도 생성한단 말이다.

@ModelAttribute 어노테이션이 붙은 객체는 자동으로 model 객체에 추가되고 해당 데이터가 jsp 뷰 단까지 전달된다.
jsp에서 submit 했을 경우 보내줄 주소에 들어갈 프로젝트 path 정보를 담고 있다.

$("#excelUpForm").attr("action", "${cp}/excel/upload");

이렇게 사용해주었다.

@ResponseBody 는 반환 값을 그대로 클라이언트에게 return 하고 싶을 때 사용한다.
엑셀 데이터 저장에 성공했을 때 페이지 변경이 일어나지 않고 같은 페이지에서 alert가 뜨게 되므로 따로 view 이름을 반환해주지 않았다.

MultipartHttpServletRequest 타입으로 엑셀 정보를 받아오고, getParameter()를 통해 ajax로 넘긴 excelType을 받아왔다.
받아온 엑셀 타입으로 xls, xlsx 형식에 따라 적절한 서비스를 찾아가게 만들었다.

ExcelService.java

@Service
public class ExcelService {

	@Autowired
	ExcelDao excelDao;

	public void xlsxExcelReader(MultipartHttpServletRequest request) {
		List<ExcelData> list = new ArrayList<>();

		MultipartFile file = request.getFile("excel");
		XSSFWorkbook workbook = null;

		try {
			// XSSFWorkbook은 엑셀파일 전체 내용을 담고 있는 객체
			workbook = new XSSFWorkbook(file.getInputStream());

			// 탐색에 사용할 Sheet, Row, Cell 객체
			XSSFSheet curSheet;
			XSSFRow curRow;
			XSSFCell curCell;
			ExcelData excelData;

			// Sheet 탐색 for문
			for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {

				// 현재 sheet 반환
				curSheet = workbook.getSheetAt(sheetIndex);

				// row 탐색 for문
				for (int rowIndex = 0; rowIndex < curSheet.getPhysicalNumberOfRows(); rowIndex++) {

					// row 0은 헤더정보이기 때문에 무시
					if (rowIndex != 0) {
						curRow = curSheet.getRow(rowIndex);
						excelData = new ExcelData();
						String value;

						// row의 첫번째 cell값이 비어있지 않는 경우만 cell탐색
						if (curRow.getCell(0) != null) {
							if (!"".equals(curRow.getCell(0).getStringCellValue())) {

								// cell 탐색 for문
								for (int cellIndex = 0; cellIndex < curRow.getPhysicalNumberOfCells(); cellIndex++) {
									curCell = curRow.getCell(cellIndex);

									if (true) {
										value = "";

										// cell 스타일이 다르더라도 String으로 반환 받음
										switch (curCell.getCellType()) {
										case XSSFCell.CELL_TYPE_FORMULA:
											value = curCell.getCellFormula();
											break;
										case XSSFCell.CELL_TYPE_NUMERIC:
											value = curCell.getNumericCellValue() + "";
											break;
										case XSSFCell.CELL_TYPE_STRING:
											value = curCell.getStringCellValue() + "";
											break;
										case XSSFCell.CELL_TYPE_BLANK:
											value = curCell.getBooleanCellValue() + "";
											break;
										case XSSFCell.CELL_TYPE_ERROR:
											value = curCell.getErrorCellValue() + "";
											break;
										default:
											value = new String();
											break;
										} // end switch

										// 현재 column index에 따라서 data입력
										switch (cellIndex) {
										case 0: // 이름
											excelData.setName(value);
											break;
										case 1: // 전화번호
											excelData.setPhone(value);
											break;
										case 2: // 이메일
											excelData.setEmail(value);
											break;
										default:
											break;
										}

									} // end if

								} // end for

								// cell 탐색 이후 vo 추가
								list.add(excelData);

							} // end

						} // end if

					}

				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}

		// 디비에 insert
		excelDao.excelInsert(list);

	}

	public void xlsExcelReader(MultipartHttpServletRequest request) {
		List<ExcelData> list = new ArrayList<>();

		MultipartFile file = request.getFile("excel");
		HSSFWorkbook workbook = null;
		
		try {
			// HSSFWorkbook은 엑셀파일 전체 내용을 담고 있는 객체
			workbook = new HSSFWorkbook(file.getInputStream());
			
			// 탐색에 사용할 Sheet, Row, Cell 객체
			HSSFSheet curSheet;
			HSSFRow curRow;
			HSSFCell curCell;
			ExcelData excelData;
	
			// Sheet 탐색
			for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
				//현재 sheet 반환
				curSheet = workbook.getSheetAt(sheetIndex);
				
				// row 탐색
				for (int rowIndex = 0; rowIndex < curSheet.getPhysicalNumberOfRows(); rowIndex++) {
					// row = 0은 보통 헤더 정보이기 때문에 넘김
					if(rowIndex != 0) {
						curRow = curSheet.getRow(rowIndex);
						excelData = new ExcelData();
						String value;

						// row의 첫번째 cell 값이 비어있지 않은 경우만 cell 탐색
						if (curRow.getCell(0) != null) {
							if (!"".equals(curRow.getCell(0).getStringCellValue())) {
								// cell 탐색
								for (int cellIndex = 0; cellIndex < curRow.getPhysicalNumberOfCells(); cellIndex++) {
									curCell = curRow.getCell(cellIndex);

									if (true) {
										value = "";

										// cell 스타일이 다르더라도 String으로 반환 받음
										switch (curCell.getCellType()) {
										case HSSFCell.CELL_TYPE_FORMULA:
											value = curCell.getCellFormula();
											break;
										case HSSFCell.CELL_TYPE_NUMERIC:
											value = curCell.getNumericCellValue() + "";
											break;
										case HSSFCell.CELL_TYPE_STRING:
											value = curCell.getStringCellValue() + "";
											break;
										case HSSFCell.CELL_TYPE_BLANK:
											value = curCell.getBooleanCellValue() + "";
											break;
										case HSSFCell.CELL_TYPE_ERROR:
											value = curCell.getErrorCellValue() + "";
											break;
										default:
											value = new String();
											break;
										} // end switch

										// 현재 column index에 따라서 data 입력
										switch (cellIndex) {
										case 0: // 이름
											excelData.setName(value);
											break;
										case 1: // 전화번호
											excelData.setPhone(value);
											break;
										case 2: // 이메일
											excelData.setEmail(value);
											break;
										default:
											break;
										} // end switch

									} // end if

								} // end for

								// cell 탐색 이후 data 추가
								list.add(excelData);

							} // end if
						} // end if
					}
				}
			}
			
		} catch (IOException e) {
			e.printStackTrace();
		}

		// DB에 insert
		excelDao.excelInsert(list);

	}
}

엑셀 데이터를 읽는 핵심 로직이 들어있는 서비스. @Service 어노테이션으로 명시해둔다.
길어서 복잡해 보이지만 침착하게 한줄한줄 읽어보면 그냥 for문 돌려서 데이터 읽어오는 수준이라 생각보다 어렵지는 않다.
그렇다고 쉽다는 건 아님 ..

xlsx는 XSSFWorkbook, xls는 HSSFWorkbook을 통해 엑셀 내용을 담는다.
보통 0번째 row는 헤더 정보(이름, 전화번호, 이메일)를 담고 있으므로 1번째부터 데이터를 담는다.
생성해둔 excelData 객체에 setter를 통해 한 줄 읽어온 값을 저장하고,
그걸 다시 List 객체에 담아 저장하는 방식으로 엑셀 전체 내용을 담는다.

데이터가 저장된 list를 excelDao.excelInsert()에 담아 DAO 객체로 전달해준다.

ExcelDao.java

@Repository
public class ExcelDao {

	private JdbcTemplate template;

	@Autowired
	public ExcelDao(ComboPooledDataSource dataSource) {
		this.template = new JdbcTemplate(dataSource);
	}

	public void excelInsert(List<ExcelData> list) {
		final String sql = "INSERT INTO user_table(user_seq, name, phone, mail, id, pw) "
				+ "values(user_seq.nextval, ?, ?, ?, ?, ?)";

		for (ExcelData excelData : list) {
			template.update(sql, new PreparedStatementSetter() {

				@Override
				public void setValues(PreparedStatement pstmt) throws SQLException {
					pstmt.setString(1, excelData.getName());
					pstmt.setString(2, excelData.getPhone());
					pstmt.setString(3, excelData.getEmail());
					pstmt.setString(4, excelData.getId());
					pstmt.setString(5, "1234");

				}
			});
		}
	}

}

JdbcTemplate와 ComboPooledDataSource 관련한 내용은 아래 게시글에 정리해놨다.

https://hyoj-j.tistory.com/18?category=1265604

[Spring] JdbcTemplate & 커넥션풀

~ 간단 설명 ~
1. @Configuration 어노테이션을 이용해 해당 클래스에 Bean을 등록하겠다는 것을 명시
2. c3p0 모듈의 ComboPooledDataSource으로 구현한 데이터베이스 커넥션을 @Bean으로 등록해둠

생성자 @Autowired를 통해 JdbcTemplate에 넣어서 사용해주면 ok


List에 담긴 excelData를 전부 insert 해줘야 하므로 foreach문으로 돌려줬다.
비밀번호는 난수발생기를 이용해 랜덤 비밀번호 알고리즘을 구현할 예정이라 임시로 더미 데이터를 넣어줬다.

아참 @Repository 어노테이션을 안 써줘서 고생 좀 했다.
역시 직접 구현하려니까 수십 번 접했던 기본적인 부분도 잘 까먹는다.




---
처음 구현해본 거라 예외처리 부분이라던가 다방면에서 많이 부족한 점이 보인다 ...
살짝 변명 같지만 사실 가볍게 테스트해본 부분이라 실전! 완벽! 이라는 마음가짐이 없기도 했다.
이미 저것만으로도 수많은 에러와 생각지도 못한 부분에서 막히는 경험을 했다 ... 스프링은 정말 복잡하구나 ~
부족한 점이 보인다는 건 그만큼 알고 있다는 말이기도 하니까
그래도 뭘 알긴 아는구나 머리에 들어있긴 하구나 예쁘게 말하면 성장했구나를 깨닫는다.
뿌듯해 ... 성공했을 땐 책상 내리칠 뻔했음 (기뻐서)

생각보다 jquery부분에서 많이 애를 먹었다 ... 아직도 확실하게 이해가지는 않음. 코드 읽는 연습을 더 해야겠다.
공부를 할수록 뭔가.. 무언가 뚝딱뚝딱 몇십 줄 만들어 낸 부분보다 문제를 해결하는 코드 한 줄이 더 중요하구나를 느낀다.
아 당연한가? 어휘력 부족으로 글로 풀어내기 힘드네
아무튼 나는 새로운 걸 구현해내는 것에 매력을 느끼며 개발자의 길을 뚜벅뚜벅 가고 있었는데
사실 그 부분은 정말 잠깐이다.
대부분은 사소한 설정 문제로 에러가 나고 ... 그걸 5시간 동안 찾고 ... 5초 만에 고치고의 반복인 거 같다.
코딩을 잘한다 != 구현을 잘 한다
코딩을 잘 한다 = 문제의 핵심을 잘 파악하고, 해결 능력이 뛰어나다
이렇게 생각이 점점 바뀌고 있음 근데 이러나 저러나 어찌 됐든 난 간지 나는 시니어 될 거니깐 홧팅


에러 해결 부분은 용량과다로 다음 포스팅에 계속 ~


- 참고 글
https://blog.naver.com/PostView.nhn?isHttpsRedirect=true&blogId=cutesboy3&logNo=220785274026

spring + poi 이용한 엑셀upload DB insert~