[Sheet.js]_EXCEL 파일 업로드

환경

Spring

ES6

---

목적

input type = "file" 에 엑셀파일 업로드 하여

해당 엑셀의 데이터 추출 후

api 전송을 위한 json 데이터 화

---

사용 라이브러리

Sheet.js

설치 및 적용은 다음 포스트 참고

https://yn971106.tistory.com/146

[Sheet.js]_Sheet.js 설치 및 적용

 

---

HTML

이런 input 박스에 엑셀파일을 다운로드 하기 위한 script 작성

---

script 

 

1. 데이터를 저장할 전역변수 && FileReader 생성자 만들기

---

2. 해당 input 타입의 데이터가 변할 때 -> onchange 이벤트 부여

input type="file" 의 데이터를 받아서

해당 데이터를 binaryString 으로 변환시킵니다

그리고 reader 객체에 전달합니다.

---

3. reader.onload

reader 에 전달이 되면 laodExcel 함수를 실행시킵니다. 데이터는 binaryString 으로 전환된 값을 전달하게 됩니다.

---

4. loadExcel 소스코드

function loadExcel(excelInputData){

    let _sheet = [];

    const data = excelInputData.result; // binary 데이터

    const workbook = XLSX.read(data,{
        type: 'binary'
    });

    if(workbook.SheetNames != null && workbook.SheetNames.length > 0){

        const SheetName = workbook.SheetNames[0]; // excel 의 시트 이름 index 넘버가 sheet의 순서

        const rows = XLSX.utils.sheet_to_json(workbook.Sheets[SheetName],{
            header :[
                'column1',
                'column2',
                'column3',
            ]
        });

        if(rows[0].column1 !== '컬럼 1 이름' || rows[0].column2 !== '컬럼 2 이름'){
            alert('지정된 양식만 등록할 수 있습니다.');

        }

        _sheet = rows.filter((r,idx) => idx >= 1).map(r => {return { // row의 0번 인덱스는 header 부분 header 가 증가함에 따라 숫자를 늘리면 된다.
            'NO' : ((r.column1??'')+'').trim(), // 공백 제거
            'NAME' : ((r.column2??'')+'').trim(), // 공백 제거
            'PHONE' : ((r.column3??'')+'').trim() // 공백 제거

        };
        });


        if(_sheet.length > 0){
            // 예외처리 가능 부분

            let chkMsg = '';
            for(let i = 0 ; i < _sheet.length ; i++){
                const e = _sheet[i]; // 1row 에 진입

                if(e.PHONE === '' || e.PHONE == null){
                    chkMsg = '전화번호는 필수항목 입니다.'
                    break;
                }


            }

            if( chkMsg !== ''){
                alert(chkMsg);
                return false;
            }

            // 데이터 베이스 컬럼명에 맞게 전역변수에 저장 후 추후 저장 api을 태워서 보내면 된다.
            excelData = _sheet.map((r,idx,arr)=> { return{
                'INDEX' : (arr.length - idx) + '',
                'NUMBER' : r.NO,
                'CUSTOMER_ID' : r.NAME,
                'CUSTOMER_PHNM':r.PHONE

            };
            })


        }else{
            alert('등록할 항목이 없습니다.');

        }



    }




}

---

5. 엑셀 업로드 함수 설명

 

데이터 json 화

데이터를 임시로 저장할 배열을 선언하고

전달받은 binary 데이터를 XLSX <- sheet.js

를 이용하여 변환합니다.

 

sheetjs 가 받을수 있는 데이터 타입은 다음과 같습니다.

Sheet.js doc 참조

 

 

workbook.SheetNames[idx]

엑셀 시트 idx 번호를 넣어주면 원하는 sheet를 접근할 수 있습니다.

 

idx 0번의 sheet에 rows를 가져올 것이기 때문에

idx 0 으로 지정한 뒤

XLSX.utils.sheet_to_json 메소드를 활용하여 해당 시트의 배열 데이터를 가져옵니다.

sheet js 공식문서

여기서 opts 의 값을 이용해서 가져올 데이터의 key 값을 지정할 수 있습니다.

---

지정된 엑셀파일 검증

지정된 엑셀파일 양식만 등록하게 하기위해서는 검증이 필요합니다.

대부분의 엑셀파일은 첫 줄이 header 일 것입니다. ( 예시도 0번째 줄이 header )

 

header의 값을 비교해서 원하는 형식의 컬럼명이 아닐 경우 예외처리를 할 수 있습니다.

---

데이터 row 검증을 위한 배열 생성

엑셀 파일 내부의 row가 비어서는 안될 경우가 있습니다. 이를 검증하기 위해서는 

row 내부의 colunm 각각에 접근해야 합니다. 이를 위해 filtter 를 이용하여 새로운 배열을 만듭니다.

 

함수내부의 지역변수에 저장을 할 것이고,

 

rows -> json 형식의 arrayList

 

이를 idx 번호가 1 이상인 것만 집어서 map 함수를 태웁니다.

1 이상인 이유는 인덱스 0번은 header 이기 때문에 데이터만 검증하기 위해서 1 이상이 됩니다.

만약 헤더가 2줄일 경우 이를 옮겨주면 됩니다.

 

또한 여기서 원하는 이름의 Key 값으로 변경 할 수 있습니다.

 

row의 수 만큼 순회하면서 배열을 만들기 때문에

r -> column 로 접근하며,

데이터 에 공백을 제거하기 위해  ?? 을 이용하여 null ->  ' ' 으로 바꿔줍니다.

---

데이터 row 검증

반환받은 arrayList 를 이용합니다.

우선 chkMsg 를 선언하고

해당 arrayList 의 길이만큼 순회합니다.

e 는 해당 row의 json에 접근을 하였고

e.컬럼명 으로 데이터에 접근이 가능합니다.

따라서 e.PHONE 으로 해당 컬럼이 비었는지 안비었는지 확인후 

비었을 경우 chkMsg 에 각각 예외에 맞는 출력값을 넣어주면 됩니다.

 

for 순회가 끝난 이후 chkMsg 가 공백이 아니라면 해당 함수를 빠져나가고 실패 문구를 띄워주도록 합니다.

---

마지막 데이터 정재

마지막으로 API 를 태우기 위해 map 을 활용하여 전역변수에 배열을 저장합니다.

 

컬럼명과 json 의 KEY 값을 일치 시키거나,

index의 번호를 같이 보내고 싶을때, 데이터를 추가로 넣어주고 싶은경우 데이터를 넣어주고

전역변수에 저장후 추후 api를 태워주면 완성입니다.

 

감사합니다.