[JS로 엑셀 파일 쓰기]
https://github.com/kkaok/examples/blob/master/jsExcelExample/src/main/resources/static/html/sheetjs-create-xlsx-aoa.html
https://github.com/kkaok/examples/blob/master/jsExcelExample/src/main/resources/static/html/sheetjs-create-xlsx-json.html
https://github.com/kkaok/examples/blob/master/jsExcelExample/src/main/resources/static/html/sheetjs-create-xlsx-table.html
https://github.com/kkaok/examples/blob/master/jsExcelExample/src/main/resources/static/html/sheetjs-create-xlsx.html
[소스]
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sheetjs create xlsx excel example(json)</title>
<script src="https://code.jquery.com/jquery-3.4.1.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>
<script>
//공통
// 참고 출처 : https://redstapler.co/sheetjs-tutorial-create-xlsx/
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(){
// 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());
}
$(document).ready(function() {
$("#excelFileExport").click(function(){
exportExcel();
});
});
</script>
<script>
var excelHandler = {
getExcelFileName : function(){
return 'json-test.xlsx';
},
getSheetName : function(){
return 'Json Test Sheet';
},
getExcelData : function(){
return [{'상품명':'삼성 갤럭시 s11' , '가격':'200000'}, {'상품명':'삼성 갤럭시 s12' , '가격':'220000'}, {'상품명':'삼성 갤럭시 s13' , '가격':'230000'}];
},
getWorksheet : function(){
return XLSX.utils.json_to_sheet(this.getExcelData());
}
}
</script>
</head>
<body>
파일 내보내기(JSON) : <input type="button" id="excelFileExport" value="엑셀 파일 다운로드(JSON)" />
</body>
</html>
[JS로 엑셀 파일 읽기]
https://github.com/kkaok/examples/blob/master/jsExcelExample/src/main/resources/static/html/js-excel-example01.html
https://github.com/kkaok/examples/blob/master/jsExcelExample/src/main/resources/static/html/js-excel-example02.html
https://github.com/kkaok/examples/blob/master/jsExcelExample/src/main/resources/static/html/js-excel-example03.html
[소스]
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>js excel example 03</title>
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.3/xlsx.full.min.js"></script>
<script>
function excelExport(event){
excelExportCommon(event, handleExcelDataAll);
}
function excelExportCommon(event, callback){
var input = event.target;
var reader = new FileReader();
reader.onload = function(){
var fileData = reader.result;
var wb = XLSX.read(fileData, {type : 'binary'});
var sheetNameList = wb.SheetNames; // 시트 이름 목록 가져오기
var firstSheetName = sheetNameList[0]; // 첫번째 시트명
var firstSheet = wb.Sheets[firstSheetName]; // 첫번째 시트
callback(firstSheet);
};
reader.readAsBinaryString(input.files[0]);
}
function handleExcelDataAll(sheet){
handleExcelDataHeader(sheet); // header 정보
handleExcelDataJson(sheet); // json 형태
handleExcelDataCsv(sheet); // csv 형태
handleExcelDataHtml(sheet); // html 형태
}
function handleExcelDataHeader(sheet){
var headers = get_header_row(sheet);
$("#displayHeaders").html(JSON.stringify(headers));
}
function handleExcelDataJson(sheet){
$("#displayExcelJson").html(JSON.stringify(XLSX.utils.sheet_to_json (sheet)));
}
function handleExcelDataCsv(sheet){
$("#displayExcelCsv").html(XLSX.utils.sheet_to_csv (sheet));
}
function handleExcelDataHtml(sheet){
$("#displayExcelHtml").html(XLSX.utils.sheet_to_html (sheet));
}
// 출처 : https://github.com/SheetJS/js-xlsx/issues/214
function get_header_row(sheet) {
var headers = [];
var range = XLSX.utils.decode_range(sheet['!ref']);
var C, R = range.s.r; /* start in the first row */
/* walk every column in the range */
for(C = range.s.c; C <= range.e.c; ++C) {
var cell = sheet[XLSX.utils.encode_cell({c:C, r:R})] /* find the cell in the first row */
var hdr = "UNKNOWN " + C; // <-- replace with your desired default
if(cell && cell.t) hdr = XLSX.utils.format_cell(cell);
headers.push(hdr);
}
return headers;
}
</script>
</head>
<body>
파일 선택 : <input type="file" id="excelFile" onchange="excelExport(event)"/>
<h1>Header 정보 보기</h1>
<div id="displayHeaders"></div>
<h1>JSON 형태로 보기</h1>
<div id="displayExcelJson"></div>
<h1>CSV 형태로 보기</h1>
<div id="displayExcelCsv"></div>
<h1>HTML 형태로 보기</h1>
<div id="displayExcelHtml"></div>
</body>
</html>