SheetJS : JS·Î ¿¢¼¿ ÆÄÀÏ ¾²±â ¿¹Á¦
¿¹Àü¿¡ UI¿¡¼ °£´ÜÇÏ°Ô ¿¢¼¿ ÆÄÀÏÀ» ³»º¸³»´Â °ÍÀ» ÇغýÀ´Ï´Ù.
±×¶§´Â ±×°Í¸¸À¸·Îµµ ¼¹öÂÊ È£Ãâ ¾ÈÇÏ°í °£´ÜÇÏ°Ô Ã³¸®ÇÒ ¼ö ÀÖ¾î ÁÁ¾ÆÇߴµ¥ SheetJS´Â ¹è¿, json, html ÇüÅ µî ´Ù¾çÇÑ ÇüÅÂÀÇ µ¥ÀÌÅÍ·Î ¿¢¼¿ ÆÄÀÏÀ» »ý¼ºÇØ ÁÝ´Ï´Ù. Á¤¸» ³Ê¹«µµ ¸ÚÁø ¿ÀÇ ¼Ò½º °°½À´Ï´Ù.
2019/01/24 - [UI(Front-End)/javascript ÀϹÝ] - ¿¢¼¿ ´Ù¿î·Îµå ±¸Çö
1. ¶óÀ̺귯¸® Ãß°¡
<!-- jquery Ãß°¡, Çʼö¾Æ´Ô -->
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<!-- Çʼö, SheetJS -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.3/xlsx.full.min.js"></script>
<!--Çʼö, FileSaver savaAs ÀÌ¿ë -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.8/FileSaver.min.js"></script>
2. ±¸ÇöÇϱâ
Àüü ÇÁ·Î¼¼½º´Â
1. ¿¢¼¿ WorkbookÀ» »ý¼ºÇÏ°í
2. µ¥ÀÌÅÍ(¹è¿/json/html table) °¡Á®¿Í¼ sheet ¸¸µé°í
3. workbook¿¡ ¸¸µç ½ÃÆ®¸¦ Ãß°¡ÇÕ´Ï´Ù.
4. ¿¢¼¿ ÆÄÀÏÀ» ¸¸µé°í
5. ´Ù¿î·Îµå ¹ÞÀ» ¼ö ÀÖ°Ô Ã³¸®ÇÕ´Ï´Ù.
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());
}
var excelHandler = {
getExcelFileName : function(){
return 'aoa-test.xlsx';
},
getSheetName : function(){
return 'AOA Test Sheet';
},
getExcelData : function(){
return [['À̸§' , '³ªÀÌ', 'ºÎ¼'],['µµ»ç¿ø' , '21', 'ÀλçÆÀ'],['±èºÎÀå' , '27', 'ºñ¼½Ç'],['¾öÀü¹«' , '45', '±âȹ½Ç']];
},
getWorksheet : function(){
return XLSX.utils.aoa_to_sheet(this.getExcelData());
}
}
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;
}
XLSX.utils ¿¡¼ Á¦°øÇÏ´Â ±â´É
Importing:
- aoa_to_sheet converts an array of arrays of JS data to a worksheet.
- json_to_sheet converts an array of JS objects to a worksheet.
- table_to_sheet converts a DOM TABLE element to a worksheet.
- sheet_add_aoa adds an array of arrays of JS data to an existing worksheet.
- sheet_add_json adds an array of JS objects to an existing worksheet.
¿¹Á¦ 1. AOA(Array of array ¹è¿ÇüÅÂ) µ¥ÀÌÅÍ·Î ¿¢¼¿ ÆÄÀÏ ´Ù¿î·Îµå
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sheetjs create xlsx excel example(array of array)</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 'aoa-test.xlsx';
},
getSheetName : function(){
return 'AOA Test Sheet';
},
getExcelData : function(){
return [['À̸§' , '³ªÀÌ', 'ºÎ¼'],['µµ»ç¿ø' , '21', 'ÀλçÆÀ'],['±èºÎÀå' , '27', 'ºñ¼½Ç'],['¾öÀü¹«' , '45', '±âȹ½Ç']];
},
getWorksheet : function(){
return XLSX.utils.aoa_to_sheet(this.getExcelData());
}
}
</script>
</head>
<body>
ÆÄÀÏ ³»º¸³»±â(¹è¿) : <input type="button" id="excelFileExport" value="¿¢¼¿ ÆÄÀÏ ´Ù¿î·Îµå(¹è¿)" />
</body>
</html>
¿¹Á¦ 2. JSON µ¥ÀÌÅÍ·Î ¿¢¼¿ ÆÄÀÏ ´Ù¿î·Îµå
<!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>
¿¹Á¦ 3. HTML TABLE·Î ¿¢¼¿ ÆÄÀÏ ´Ù¿î·Îµå
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sheetjs create xlsx excel example(html table)</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 'table-test.xlsx';
},
getSheetName : function(){
return 'Table Test Sheet';
},
getExcelData : function(){
return document.getElementById('tableData');
},
getWorksheet : function(){
return XLSX.utils.table_to_sheet(this.getExcelData());
}
}
</script>
</head>
<body>
<table id="tableData" style="border:1px solid #dddddd">
<thead>
<tr>
<th>À̸§</th>
<th>CP</th>
</tr>
</thead>
<tbody>
<tr>
<td>¸Á³ª´¨</td>
<td>4000</td>
</tr>
<tr>
<td>¸¶±â¶ó½º</td>
<td>3900</td>
</tr>
<tr>
<td>ÇØÇdzʽº</td>
<td>3800</td>
</tr>
</tbody>
</table>
ÆÄÀÏ ³»º¸³»±â(HTML TABLE) : <input type="button" id="excelFileExport" value="¿¢¼¿ ÆÄÀÏ ´Ù¿î·Îµå(TABLE)" />
</body>
</html>
GITHUB
https://github.com/kkaok/examples/tree/master/jsExcelExample/src/main/resources/static/html
¿¢¼¿ ÆÄÀÏ Àб⠿¹Á¦
2019/06/29 - [UI(Front-End)] - SheetJS : JS·Î ¿¢¼¿ ÆÄÀÏ Àб⠿¹Á¦