티스토리 뷰
Javascript 로 엑셀 처리할 때 사용했던 방식들을 공유합니다.
테스트 소스를 작성하고, 사소하지만 각 기능 별 추가된 소스를 차례대로 기술하겠습니다.
0. 엑셀 기능 제공 SheetJS 사용하기
SheetJS : Javascript로 엑셀 기능을 간단하게 구현할 수 있는 라이브러리 입니다. 유료 버전도 있으나,
무료 버전으로도 기본적인 기능은 충분히 가능합니다.
Shee.js 에 대한 설명 및 예제 소스는 아래 두 블로그에 너무 잘 기술되어 있습니다.
해당 예제 소스를 기반으로, 엑셀 예제 소스와, 추가적으로 사용한 기능들만 간략하게 설명하도록 하겠습니다.
- Export 참고 : https://velog.io/@gsuchoi/Javascript-SheetJS-Excel-%EB%8B%A4%EC%9A%B4%EB%A1%9C%EB%93%9C
- Import 참고 : https://eblo.tistory.com/83
0 - 1 ) 예제 화면
회사원 정보를 추출/입력 하는 예시 화면입니다.
SheetJS 에서 html으로 변환하는 기본 펑션도 제공되긴 하나, 성별을 select box 로 입력 해야하므로,
엑셀 데이터를 우선 JSON 형식으로 받고, HTML로 append 해주는 소스를 추가하였습니다.
0 - 2 ) 예제 소스 전문 보기
전체 예제 소스
0-1. header (Imported file)
<script src="https://cdn.jsdelivr.net/jquery/1.12.4/jquery.min.js"></script>
<script src="https://ajax.aspnetcdn.com/ajax/jquery.ui/1.12.1/jquery-ui.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.3/xlsx.full.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.8/FileSaver.min.js"></script>
0-1 . HtmlPage1.html
<body>
<div>
<!-- 테이블 -->
<table id="testTable" style="border:1px solid black">
<thead>
<tr>
<th>부서</th>
<th>사번</th>
<th>이름</th>
<th>성별</th>
<th>직급</th>
<th>입사일</th>
</tr>
</thead>
<tbody>
<tr>
<td>A부서</td>
<td>S11</td>
<td>회사원A</td>
<td>
<select>
<option value="남">남</option>
<option value="여" selected>여</option>
</select>
</td>
<td>사원</td>
<td>2020-01-05</td>
</tr>
<tr>
<td>A부서</td>
<td>S12</td>
<td>회사원B</td>
<td>
<select>
<option value="남" selected>남</option>
<option value="여">여</option>
</select>
</td>
<td>대리</td>
<td>2020-07-16</td>
</tr>
<tr>
<td>B부서</td>
<td>S13</td>
<td>회사원C</td>
<td>
<select>
<option value="남" selected>남</option>
<option value="여">여</option>
</select>
</td>
<td>과장</td>
<td>2021-03-20</td>
</tr>
</tbody>
</table>
</div>
<!-- 엑셀 버튼 -->
<div style="padding-top:30px;">
<button type="button" id="btnExport">EXPORT</button>
<button type="button" id="btnImport">IMPORT</button>
<input type="file" id="excelImport" onclick="this.value=null" style="display:none;" />
</div>
</body>
0-2. script
<script>
// excel
function s2ab(s) {
var buf = new ArrayBuffer(s.length); //convert s to arrayBuffer
var view = new Uint8Array(buf); //create uint8array as viewer
for (var i = 0; i < s.length; i++) view[i] = s.charCodeAt(i) & 0xFF; //convert to octet
return buf;
}
function exportExcel(id) {
// step 1. workbook 생성
var wb = XLSX.utils.book_new();
// step 2. 시트 만들기
var newWorksheet = excelHandler.getWorksheet();
// step 3. workbook에 새로만든 워크시트에 이름을 주고 붙인다.
XLSX.utils.book_append_sheet(wb, newWorksheet, excelHandler.getSheetName());
// step 4. 엑셀 파일 만들기
var wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'binary' });
// step 5. 엑셀 파일 내보내기
saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), excelHandler.getExcelFileName());
}
var excelHandler = {
getExcelFileName: function () {
return getLocalDate().substring(0, 10) + '.xlsx';
},
getSheetName: function () {
return 'Sheet1';
},
getExcelData: function () {
var table = $('#testTable').clone();
table.find('select').each(function (i, d) {
d.outerHTML = d.selectedOptions[0].text;
});
return table[0];
},
getWorksheet: function () {
return XLSX.utils.table_to_sheet(this.getExcelData());
},
setExcelToJson: function (e, callback) {
var input = e.target;
var reader = new FileReader();
reader.onload = function () {
var fileData = reader.result;
var wb = XLSX.read(fileData, { type: 'binary' });
wb.SheetNames.forEach(function (sheetName) {
var rowObj = XLSX.utils.sheet_to_json(wb.Sheets[sheetName]);
console.info(rowObj);
if (callback && typeof (callback) == 'function')
callback(rowObj);
})
};
reader.readAsBinaryString(input.files[0]);
}
}
// button event
$('#btnExport').on('click', function (e) {
exportExcel('testTable');
})
$('#btnImport').on('click', function (e) {
$("#excelImport").trigger('click');
});
$('#excelImport').on('change', function (e) {
excelHandler.getExcelToJson(e, insertRow);
});
function insertRow(datas) {
var rowForm = `<tr>
<td>{0}</td>
<td>{1}</td>
<td>{2}</td>
<td>
<select>
<option value="남" {3}>남</option>
<option value="여" {4}>여</option>
</select>
</td>
<td>{5}</td>
<td>{6}</td>
</tr>`;
datas.forEach(function (d, i) {
//console.log(d);
var isMan = (d.성별 === '남' ? 'selected' : '');
var isWoman = (d.성별 === '여' ? 'selected' : '');
var row = rowForm.format(d.부서, d.사번, d.이름, isMan, isWoman, d.직급, exToDateString(d.입사일));
$('tbody').append(row);
});
}
// Util
String.prototype.format = function () {
var formatted = this;
for (var arg in arguments) {
formatted = formatted.replace("{" + arg + "}", arguments[arg]);
}
return formatted;
};
function exToDateString(exdate) {
var e0date = new Date(0); // epoch "zero" date
var offset = e0date.getTimezoneOffset(); // tz offset in min
// calculate Excel xxx days later, with local tz offset
var jsdate = new Date(0, 0, exdate - 1, 0, -offset, 0);
return jsdate.toJSON().substring(0, 10);
}
function getLocalDate() {
var d = new Date();
let offset = d.getTimezoneOffset() * 60000;
return new Date(d.getTime() - offset).toISOString();
}
</script>
1. 엑셀 파일 추출 (Export)
1 - 1. 셀렉트 박스에서 '선택한 텍스트' (selected option) 만 추출하기
: 엑셀로 추출하려는 폼에 선택지(select box)가 있는 경우, 전체 옵션이 출력되기 때문에, selected 한 값만 출력할 수 있도록 필터링 소스를 추가하였다.
getExcelData: function () {
// 가상 돔 복사
var table = $('#testTable').clone();
/* 선택된 옵션의 text 만 HTML 에 남긴다
AS-IS : <select><option value="...>...</option></select>
TO-BE : 님 or 여
*/
table.find('select').each(function (i, d) {
d.outerHTML = d.selectedOptions[0].text;
});
return table[0];
}
2. 엑셀 파일 입력 (Import)
2 - 1. Input type="file" 객체를 '버튼'으로 대체하기
: 엑셀 파일을 입력받는 Input="file" 객체를 숨기고, export 와 같은 버튼으로 스타일을 통일 시켜 줄 때 참고하면 된다.
=> 버튼을 하나 생성 후, 내부에서 trigger click event를 호출한다
HtmlPage1.html
<button type="button" id="btnImport">IMPORT</button>
<input type="file" id="excelImport" style="display:none;" />
script.js
$('#btnImport').on('click', function (e) {
$("#excelImport").trigger('click');
});
2 - 2. 같은 이름의 파일도 다시 입력 받을 수 있도록 변경
: 같은 이름의 엑셀 파일을 다시 import하는 경우,파일 내용이 변경되었더라도 change 펑션이 실행되지 않아서 결과 값이 달라지지 않는 현상 발견
=> 간단한 해결 방법 : input 객체에 onclick="this.value=null" 이벤트 삽입
해주면 같은 이름의 파일도 계속 import 가능
<input type="file" id="excelImport" onclick="this.value=null" style="display:none;" />
2 - 3. 엑셀 파일의 날짜 형식 데이터를 정상적으로 변환 후 받기
=> 엑셀 파일의 데이터를 JSON 으로 받을 떄, 날짜 데이터가 다르게 입력되므로, 한번 변환해주는 펑션 추가
function exToDateString(exdate) {
var e0date = new Date(0); // epoch "zero" date
var offset = e0date.getTimezoneOffset(); // tz offset in min
// calculate Excel xxx days later, with local tz offset
var jsdate = new Date(0, 0, exdate - 1, 0, -offset, 0);
return jsdate.toJSON().substring(0, 10);
}
3. 엑셀 스타일
엑셀 스타일 관련 내용은 이 글에 추가할까 하다가, 다른 라이브러리가 추가되기 때문에, 별도로 파생글을 작성하였습니다.
참고로 봐주시면 좋겠습니다. :)
[JS][Excel] 엑셀 스타일 기능 (feat. xlsx-js-style, SheetJS)
이전에 SheetJS 기반으로 엑셀 기본 기능 개발에 관한 글을 작성하였습니다. 그런데 개발 후, 엑셀에 스타일을 포함해달라는 추가 요구사항이 있었고, 저와 같은 분들이 계실 것 같아서 관련 글을
ryusae.tistory.com
참고 :
https://velog.io/@sumin3280/%EC%97%91%EC%85%80%ED%8C%8C%EC%9D%BC%EC%9D%84-JSON%EC%9C%BC%EB%A1%9C-%EB%B3%80%ED%99%98%ED%95%98%EA%B8%B0
https://stackoverflow.com/questions/16229494/converting-excel-date-serial-number-to-date-using-javascript
'개발 > Script' 카테고리의 다른 글
| [IE] Polyfill 사용하기 (+ slice, readAsBinaryString) (0) | 2022.10.19 |
|---|---|
| [JS][Excel] 엑셀 스타일 기능 (feat. xlsx-js-style, SheetJS) (0) | 2022.10.06 |
| [JSON] 자주 쓰는 사이트들 (0) | 2022.07.11 |
| [IE] 버전 이슈 대응 방법 여러가지 (script 대체, 레지스트리, JSON 등) (0) | 2021.08.27 |
| [IE] div Scroll Bar 나타나지 않는 현상 (0) | 2021.08.09 |
- Total
- Today
- Yesterday