안녕하세요~!! ㅎㅎㅎ
저번 포스팅으로는 Jsoup 라이브러리 설명과 크롤링을 통한 응용방법에 대해서 알아보았습니다!
ㅎㅎㅎ
오늘은 엑셀파일을 DB에 저장하는 방법중 하나인
POI 라이브러리 이용해서 엑셀파일을 DB에 저장시키기 입니다!!!
엑셀파일을 그대로 DB로 옮겨?
네! 그렇습니다. ㅎㅎㅎ 그리고 제 프로젝트 중 하나인
주식 종목 엑셀로 최신화 하기
주식 종목 엑셀로 업데이트 하기 등등
최신종목으로 바꿀때 사용했었던 라이브러리 입니다!
https://uno-kim.tistory.com/212
네이버 금융 크롤링 : 주식 종목 엑셀로 최신화 하기
안녕하세요 ~! ㅎㅎㅎㅎ 오늘은 주식 종목으로 엑셀로 최신화 하는 방법에 대해서 알아보겠습니다.!!ㅎㅎㅎ https://uno-kim.tistory.com/211 네이버 금융 크롤링 : 주식 종목 DB 가져오기 안녕하세요~! ㅎ
uno-kim.tistory.com
이글에서 설명을 얼렁 뚱땅하고 지나가서 이번 포스팅에서는 조금더 부연설명을 드리고자 작성하게 되었습니다.
우선 해당 라이브러리를 이용해서 DB에 담는 단계는 대략 4단계로 되어있습니다.
- POI 라이브러리 설치 / 의존성 주입
- 엑셀파일을 자바 서버에 올리기
- 엑셀파일을 라이브러리를 통해서 읽기
- 읽은 데이터를 DB 또는 자료구조에 저장하기
이렇게 진행하겠습니다.! 그럼
엑셀파일을 자바 서버에 올리기 부터 설명드리겠습니다!
1. POI 라이브러리 설치 / 의존성 주입
우선 POI 라이브러리를 설치 또는 의존성 주입을 하겠습니다.
pom.xml에
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.0</version>
</dependency>를 추가해 줍니다.
그리고 해당 프로젝트를 재 빌드 / 메이븐 업데이트를 하시면
해당 모습처럼 라이브러리가 주입된것을 확인 하실 수 있습니다!!
2. 엑셀파일을 자바 서버에 올리기
이번엔 빠르게 엑셀파일을 자바 서버에 올려보겠습니다!
우선 저는 지난 포스팅에서 사용했던 엑셀파일을 이용하겠습니다.
*출처 : http://data.krx.co.kr/contents/MDC/MDI/mdiLoader/index.cmd?menuId=MDC0201
KRX 정보데이터시스템
증권·파생상품의 시장정보(Marketdata), 공매도정보, 투자분석정보(SMILE) 등 한국거래소의 정보데이터를 통합하여 제공 서비스
data.krx.co.kr
네 여기서 다운받았습니다. 엑셀파일로 다운받았었습니다.
이 파일을
리파지토리에 담아 내보겠습니다.,
그리고 소스상에서 해당 파일의 위치를 얻어내고자 소스를 작성해보겠습니다.
우선 제가 저기 올린 파일의 경로를 확인해보겠습니다.
파일을 오른쪽 마우스버튼으로 클릭하시고
맨하단의 프로퍼티즈를 눌러줍니다.
그럼 해당 파일의 경로를 알 수 있습니다.
해당 위치는 제 기준으로
- [유저 디렉터리 주소] : 이건 해당 위치는 기본값으로, 소스가 저장되어있는 디렉토리 주소이다..
- [폴더경로] (src) : 이건 기본적인 소스루트(?) 로서 여기서부터 경로 검색시 참고할수 있는 값이 된다.
- [파일명] (엑셀파일명): 해당 파일 이름
이렇게 이루어져 있습니다.
이 경로를 통해서 파일을 열 수 있습니다.
해당 경로를 통해서 파일을 읽는 소스를 작성해 보겠습니다.
package ㅎJavaTests;
import java.io.File;
public class excelParseTest
{
/* 현재 테스트 클래스의 사용자 디렉터리 물리적 경로 */
static final String USER_DIR_PATH = System.getProperty("user.dir");
public static void main(String[] args) throws Exception
{
String filename = "\\src\\data_2032_20221027.xlsx"; // 파일명 설정
File excelFile = new File(USER_DIR_PATH + filename);
System.out.println("파일명 : " + excelFile.getName());
System.out.println("용량 : " + excelFile.length());
System.out.println("위치 : " + excelFile.getPath());
}
}현재 리파지토리의 디렉토리 경로는 변함이 없으니 상수화를 하였습니다.
해당 소스를 실행해 보겠습니다.
와우!!! 파일명, 용량, 위치 모두 잘나왔습니다.
이제 제가 아까 올린 엑셀파일을 자바에서 File 클래스를 이용해서 엑셀파일을 올린것을 확인 했습니다.
3. 엑셀파일을 라이브러리를 통해서 읽어내기
앞서서 업로드한 엑셀파일을 자바에 올릴수 있는 것을 확인했습니다.
이제 이 파일을 FileInputStream 클래스와 POI 라이브러리를 통해서 엑셀을 읽어내보겠습니다!!
이떄 필요한것은
XSSFWorkbook 클래스입니다. 해당 클래스는 엑셀을 위주로 진행할 수 있는 클래스입니다.
이번에 공부하면서 알아낸건데 PPT, WORD 파일도 해당 라이브러리를 통해서 다룰 수 있더라구요!
나중에 또 공부해보고 포스팅 해보겠습니다!!!
공부하는 재미가있는 라이브러리였군요 ㅎㅎㅎ
우선! 라이브러리를 진행하고 데이터를 읽어내기전에
엑셀의 구조를 알아야합니다.
엑셀은 은근 3D 구조?? 로 되어있다고 저는 쉽게 생각하고있어요
얼핏보면 열과 행으로 만 이루어진 2차원 데이터 모델이라고 생각하는데 그것이
상하로 다시 쌓이는 Sheep 개념으로 저는 3차원이라고 생각합니다. ㅎㅎㅎ
따라서!!! 이 라이브러리도 따짜고짜 엑셀파일을 서버 위 메모리에 올렸다고 해도
해당 파일을 바로 열과 행으로 나누는 것이 아니라
시트라는 한 차원을 또 올리고 그 위에서 행과 열을 다뤄야 한다는 얘기입니다.
우선 저희 데이터를 보겠습니다.
다행히 시트가 한개에 행과 열로 깔끔하게 나누어지는 단순한 엑셀이군요
정말 간단하게 실습하거나 예시로 들기엔 데이터 구조적으로는 주식만한건 없다고 혼자 생각이 듭니다.ㅎ..ㅎㅎㅎ
따라서 엑셀을 읽는 순서는 시트-> 행열 이순서로 다가가야한다는 것을 알 수 있습니다.
package ㅎJavaTests;
import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class excelParseTest
{
/* 현재 테스트 클래스의 사용자 디렉터리 물리적 경로 */
static final String USER_DIR_PATH = System.getProperty("user.dir");
public static void main(String[] args) throws Exception
{
String filename = "\\src\\data_2032_20221027.xlsx"; // 파일명 설정
File excelFile = new File(USER_DIR_PATH + filename);
System.out.println("파일명 : " + excelFile.getName());
System.out.println("용량 : " + excelFile.length());
System.out.println("위치 : " + excelFile.getPath());
FileInputStream excelFileInputStream = new FileInputStream(excelFile);
XSSFWorkbook excelWorkBook = new XSSFWorkbook(excelFileInputStream);
/* 시트개수를 구하고 해당 순번의 시트의 정보를 읽어내기 위한 for문 */
for (int sheetIdx = 0; sheetIdx < excelWorkBook.getNumberOfSheets(); sheetIdx++)
{
/* for 문을 돌리고 해당 인덱스에 해당하는 시트를 인스턴스화 한다. */
XSSFSheet excelSheet = excelWorkBook.getSheetAt(sheetIdx);
/* 다시 for문을 돌려서 시트내의 행(row)으로 나누어 지기에 for문을 돌려서 행(row)으로 읽어내는 for문 */
for (int rowIdx = 0; rowIdx < excelSheet.getPhysicalNumberOfRows(); rowIdx++)
{
/* for문을 돌리고 해당 인덱스에 해당하는 행(row)을 인스턴스화 한다. */
XSSFRow excelRow = excelSheet.getRow(rowIdx);
/* 다시 for문을 돌려서 행내의 열(셀)로 나누어 지기에, for문을 돌려서 열(셀)로 읽어내는 for문 */
for (int cellIdx = 0; cellIdx < excelRow.getPhysicalNumberOfCells(); cellIdx++)
{
/* for 문을 돌리고 해당 인덱스에 해당하는 열를 인스턴스화 한다. */
XSSFCell excelCell = excelRow.getCell(cellIdx);
System.out.print(excelCell + " ");
}
System.out.println();
}
}
}
}우선 제가 작성한 소스입니다.
앞서 설명드린것 과같이
- 엑셀을 메모리에 올리고
- 올린 엑셀의 시트개수를 구하고
- 해당 인덱싱된 시트를 인스턴스화하고
- 또 행 개수를 구하고
- 해당 인덱싱된 행을 인스턴스화하고
- 또 열 개수를 구하고
- 해당 인덱싱된 열을 인스턴스화하
하는 과정을 통해서
총 for문이 3가지로 되어있으며
아까 말씀드린것과같이 3차원 같다... 라고 설명드렸습니다.
한번 결과를 보겠습니다.
위와 같이 콘솔로 결과가 나왔습니다!!!
맨첫번째 표준코드 단축코드 등등 컬럼들도 나왔구요
데이터들도 모두 잘나온것 같습니다. 한번 원본과 대조해보겠습니다.!!
와우!!! 거의 똑같이 나왔고 순서도 똑같이 나왔습니다!!!
맞습니다.
ㅎㅎㅎ해당 정보로 모두 가져왔습니다!!
그럼 조금 심화해서 진행해 보겠습니다...
우리가 필요한건
이 모든 것들이 아니고
이렇게 딱
- 단축코드
- 한글 종목약명
- 시장구분
정도만 필요합니다.이 정보들만 출력하게 하고 다음단계로 넘어가겠습니다.
우리가 필요한 컬럼의 정보는
이렇게 B, D, G입니다.
A가 첫번째 라고한다면
B는 2번째이고
D는 4번째, G는 6번째입니다.
따라서 소스에서도 해당 2번,4번, 6번 cell 열만 출력시키게 작성한다면
셀, 열 부분을 출력하는 부분을 이렇게 작성해서 실행시켜보니
이렇게 필요한 값들이 모두 나온것을 확인 할 수 있습니다!!
이제 엑셀들을 다 읽었으므로 해당 데이터를 DB에 넣을 수 있게 자료구조 및 데이터형태로 넣는방법에 대해 설명드리겠습니다.
4. 엑셀정보를 자료구조나 데이터클래스형태로 저장하고 DB에 저장하기
이렇게 원하는 엑셀정보를 정제를 했습니다.
현재 단계에서는 출력화면이나 별다른 설명은 줄이고
소스위주로 3가지 상황에 맞는 소스를 올려드리겠습니다.
이제 이 값을 자료구조로 다른 클래스로 가져가거나 ,
간단한 자료구조에 담에 바로 DB에 Insert 를 하는 방법!!
총 2가지 방법에 대해서 설명드리겠습니다.
[1] 자료구조로 만들어 다른 곳에 값을 넘기는 형식(리턴하는 형식)
package ㅎJavaTests;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReturnExcelDataStruct
{
/* 현재 테스트 클래스의 사용자 디렉터리 물리적 경로 */
static final String USER_DIR_PATH = System.getProperty("user.dir");
public static void main(String[] args) throws Exception
{
List<List<String>> execlArrDataStruct = new ArrayList<List<String>>();
String filename = "\\src\\data_2032_20221027.xlsx"; // 파일명 설정
execlArrDataStruct = excelFileToDataStruct(filename);
}
private static List<List<String>> excelFileToDataStruct(String filename) throws IOException
{
List<List<String>> outArr = new ArrayList<List<String>>();
File excelFile = new File(USER_DIR_PATH + filename);
System.out.println("파일명 : " + excelFile.getName());
System.out.println("용량 : " + excelFile.length());
System.out.println("위치 : " + excelFile.getPath());
FileInputStream excelFileInputStream = new FileInputStream(excelFile);
XSSFWorkbook excelWorkBook = new XSSFWorkbook(excelFileInputStream);
/* 시트개수를 구하고 해당 순번의 시트의 정보를 읽어내기 위한 for문 */
for (int sheetIdx = 0; sheetIdx < excelWorkBook.getNumberOfSheets(); sheetIdx++)
{
/* for 문을 돌리고 해당 인덱스에 해당하는 시트를 인스턴스화 한다. */
XSSFSheet excelSheet = excelWorkBook.getSheetAt(sheetIdx);
/* 다시 for문을 돌려서 시트내의 행(row)으로 나누어 지기에 for문을 돌려서 행(row)으로 읽어내는 for문 */
for (int rowIdx = 0; rowIdx < excelSheet.getPhysicalNumberOfRows(); rowIdx++)
{
/* 열(셀)들을 저장할 리스트형태 */
List<String> inArr = new ArrayList<String>();
/* for문을 돌리고 해당 인덱스에 해당하는 행(row)을 인스턴스화 한다. */
XSSFRow excelRow = excelSheet.getRow(rowIdx);
/* 다시 for문을 돌려서 행내의 열(셀)로 나누어 지기에, for문을 돌려서 열(셀)로 읽어내는 for문 */
for (int cellIdx = 0; cellIdx < excelRow.getPhysicalNumberOfCells(); cellIdx++)
{
/* for 문을 돌리고 해당 인덱스에 해당하는 열를 인스턴스화 한다. */
XSSFCell excelCell = excelRow.getCell(cellIdx);
switch (excelCell.getColumnIndex()) {
case 2 : /* 종목코드 */
inArr.add(excelCell.getStringCellValue());
break;
case 4 : /* 종목이름 한글명 */
inArr.add(excelCell.getStringCellValue());
break;
case 6 : /* 시장구분 */
inArr.add(excelCell.getStringCellValue());
break;
}
}
outArr.add(inArr);
System.out.println();
}
}
excelWorkBook.close();
excelFileInputStream.close();
return outArr;
}
}[2] 행값을 얻을 때마다 간단한 자료구조로 Insert
package ㅎJavaTests;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
public class excelParseTest
{
@Autowired
private SharesDataMapper sharesDataMapper;
/* 현재 테스트 클래스의 사용자 디렉터리 물리적 경로 */
static final String USER_DIR_PATH = System.getProperty("user.dir");
public static void main(String[] args) throws Exception
{
String filename = "\\src\\data_2032_20221027.xlsx";
FileInputStream excelFileInputStream = new FileInputStream(new File(USER_DIR_PATH + filename));
XSSFWorkbook excelWorkBook = new XSSFWorkbook(excelFileInputStream);
for (int sheetIdx = 0; sheetIdx < excelWorkBook.getNumberOfSheets(); sheetIdx++)
{
XSSFSheet excelSheet = excelWorkBook.getSheetAt(sheetIdx);
for (int rowIdx = 0; rowIdx < excelSheet.getPhysicalNumberOfRows(); rowIdx++)
{
List<String> rowData = new ArrayList<String>();
XSSFRow excelRow = excelSheet.getRow(rowIdx);
for (int cellIdx = 0; cellIdx < excelRow.getPhysicalNumberOfCells(); cellIdx++)
{
XSSFCell excelCell = excelRow.getCell(cellIdx);
switch (excelCell.getColumnIndex()) {
case 2 : /* 종목코드 */
rowData.add(excelCell.getStringCellValue());
break;
case 4 : /* 종목이름 한글명 */
rowData.add(excelCell.getStringCellValue());
break;
case 6 : /* 시장구분 */
rowData.add(excelCell.getStringCellValue());
break;
}
}
this.sharesDataMapper.insertExcelData(rowData);
}
}
excelWorkBook.close();
excelFileInputStream.close();
}
}원하는 셀의 값 3개를 하나의 List에 담아 얻어낸 행을
바로 insert하는 방법이다.
<insert id="insertExcelData"
parameterType="list">
<![CDATA[
INSERT INTO
SHARES_FIN_INFO
(
CODE
, NAME
, EXCHANGE
)
VALUES
(
#{finCode}
,#{finName}
,#{exchange}
)
]]>
</insert>해당 insert문은 mybatis로 해당 쿼리로 작성하였습니다.
아마 자료구조를 만들거나 해당 for문내에서 바로 인서트를 하는 방법이든
시간이 많이 걸릴 수 있고 조금 서버단이 힘들수 있는 작업이 될 수 있습니다.
따라서 해당 작업을 진행하게 된다면 트랜잭션을 설정하시고 중간에
예외라던가 오류가 생겨 작업이 중단될지라도 빠져나와 데이터에 문제가 생기지 않도록
잘 대비해야합니다.
이것만 주의하면 강력하고 아주 자유로운 라이브러리이며
이 라이브러리를 통해서 엑셀도 작성하고, PPT보기, WORD 작성 등등을 할 수 있습니다.
저도 조금더 공부해서 해당 엑셀의 값을 살짝 바꿔주어서 업무자동화? 처럼 해주는 토이 프로젝트를
만들어서 올려보겠습니다 ㅎㅎㅎ
지금까지 긴들 읽어주셔서 감사합니다.
다음 포스팅은 현재 제가 진행하고 있는 프로젝트관련된 글을 작성하려고합니다.
감사합니다.
'JAVA공부 > 99-기타 라이브러리' 카테고리의 다른 글
| [JAVA]Jsoup 라이브러리를 이용한 크롤링 - Jsoup 사용법 (2) | 2022.11.02 |
|---|---|
| [JAVA]Jsoup 라이브러리를 이용한 크롤링 - Jsoup 사용법 + 라이브러리 저장하는 법 (0) | 2022.11.01 |
댓글