설치
https://downloads.mysql.com/archives/excel/
위의 링크를 타서 설치 전에 Visual Studio 2010 Tools for Office Runtime 를 설치해준다.
사용법
https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=good_ray&logNo=221538398631
'No thanks, just start my download' 라는 문구를 클릭합니다.
그리고 순서대로 내려받으시면 됩니다.
최종적으로 Finish를 누르게 되면 자동으로 엑셀에 버튼이 생깁니다.
분명 비어있던 공간인데 버튼이 생겼군요!!
일단 엑셀에 데이터를 삽입해봅니다.
전부다 삽입했으면 이제 MYSQL에 옮겨야 겠죠??
연결을 시도해봅니다.
Mysql for Excel 이란 단추를 누르면 mysql workbench에 저장된 연결 리스트들이 나옵니다.
원하는 연결로 접속합니다. 접속 후에 스키마를 고르면 다음과 같이 테이블 리스트가 나옵니다.
school을 제외한 나머지 테이블이름은 예민한 정보라서 가립니다.
옮기기 전에, 기본적으로 ctrl + A를 눌러 엑셀의 모든 데이터를 선택해야합니다.
이제 엑셀 데이터를 옮겨볼 준비가 다 되었습니다.
Append Excel Data to Table이라는 위의 형광펜 친 부분을 클릭합니다.
만약 MYSQL에 옮기면서 동시에 새로운 테이블을 누르고 싶으시다면
위의 형광펜 부분을 누르시고 진행하시면 됩니다.
이제 excel의 칼럼부분을 마우스 좌클릭으로 끌어서 mysql 칼럼에 대입시키면 자동으로 대입이 됩니다.
Append 버튼을 누르면 데이터 삽입이 시작이 되는데, 삽입과정에서 예외발생 시 중간에서 멈춥니다.
https://itsarang.tistory.com/456
https://excelyouth.tistory.com/48
이슈
이렇게 해서 업체에서 보낸 회원사 리스트를 member table에 insert하였다. 문제는 이렇게 집어넣은 비밀번호는 BCryptPasswordEncoder 로 해시암호화 되지 않은 상태로 문자열 그대로 넣어진다는 점.. 스프링 시큐리티를 도입했기 때문에 로그인 시 BCryptPasswordEncoder matches - Encoded password does not look like BCrypt 이슈가 터진다.. 그렇다고 시큐리티에서 패스워드 인코딩을 disable 하고 쓰자니, 그것도 뭔가 말이 안 되는 것 같고.. 일일히 노가다로 집어넣자니 그것대로 하기가 싫고..
https://stackoverflow.com/questions/51208425/how-to-use-spring-security-without-password-encoding
해결
https://www.baeldung.com/java-microsoft-excel
https://shinsunyoung.tistory.com/71
https://4urdev.tistory.com/109
결국에는 Excel file을 spring boot 에서 읽어서 암호화를 시킨 다음 DB에 저장하는 방식으로 이슈를 해결했다. excel file을 java 에서 읽기 위해서 아래와 같은 의존성을 추가했다.
<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>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.6</version>
</dependency>commons-io 같은 경우 파일 확장자 가져오기 기능을 사용하려고 넣은 건데, 딱히 필요는 없더라. 안 넣어도 될 듯..
public List<MemberReqDto> readExcelFile() {
String filePath = "C:/Users/songn/Documents/카카오톡 받은 파일/충전기연동프로토콜_custom.xlsx";
List<MemberReqDto> members = new ArrayList<>();
try {
FileInputStream file = new FileInputStream(filePath);
XSSFWorkbook workbook = new XSSFWorkbook(file); //xlsx 확장자만
int rowindex = 0;
int columnindex = 0; //시트 수 (첫번째에만 존재하므로 0을 준다) //만약 각 시트를 읽기위해서는 FOR문을 한번더 돌려준다
XSSFSheet sheet = workbook.getSheetAt(0); //행의 수
int rows = sheet.getPhysicalNumberOfRows();
Sheet worksheet = workbook.getSheetAt(0);
for (rowindex = 2; rowindex < rows; rowindex++) { //행을읽는다
XSSFRow row = sheet.getRow(rowindex);
Row row2 = worksheet.getRow(rowindex);
MemberReqDto reqDto = new MemberReqDto();
log.info("==============================================");
reqDto.setMember_name(row2.getCell(2).getStringCellValue());
reqDto.setMember_userId(row2.getCell(3).getStringCellValue());
reqDto.setMember_password(row2.getCell(3).getStringCellValue());
reqDto.setParking_id(String.valueOf(row2.getCell(4).getNumericCellValue()));
reqDto.setMember_car_number(String.valueOf(row2.getCell(5).getStringCellValue()));
System.out.println(reqDto);
if (row != null) { //셀의 수
int cells = row.getPhysicalNumberOfCells();
for (columnindex = 0; columnindex <= cells; columnindex++) { //셀값을 읽는다
XSSFCell cell = row.getCell(columnindex);
String value = ""; //셀이 빈값일경우를 위한 널체크
if (cell == null) {
continue;
} else { //타입별로 내용 읽기
switch (cell.getCellType()) {
case FORMULA:
value = cell.getCellFormula();
break;
case NUMERIC:
value = cell.getNumericCellValue() + "";
break;
case STRING:
value = cell.getStringCellValue() + "";
break;
case BLANK:
value = cell.getBooleanCellValue() + "";
break;
case ERROR:
value = cell.getErrorCellValue() + "";
break;
}
}
System.out.println(rowindex + "번 행 : " + columnindex + "번 열 값은: " + value);
}
}
members.add(reqDto);
}
} catch (Exception e) {
e.printStackTrace();
}
return members;
}// @Test
// public void excelReadAndInsertMemberListTest(){
//
// List<MemberReqDto> members = excelService.readExcelFile();
//
// for (MemberReqDto reqDto : members) {
// adminService.memberAdd(reqDto);
// }
// }