1. ExcelJS 소개
exceljs란 javascript에서 데이터를 엑셀로 추출 및 조작할 수 있는 node 환경 라이브러리로, 특정 셀의 스타일을 커스텀하고 합칠 수 있는 장점이 있는 라이브러리이다.
2. 공식문서 링크
npm exceljs 링크
https://www.npmjs.com/package/exceljs
cdn 으로 사용하려면
<script src="https://cdn.jsdelivr.net/npm/exceljs@4.3.0/dist/exceljs.min.js"></script>3. ExcelJS 실사용예
실제 업무에 사용한 코드이다.
- 엑셀파일 구조를 먼저 정의하고
- 해당 구조를 읽어들이고, db 연결, json data load
- 가져온 엑셀구조와 json data 로 엑셀 파일 생성
HTML 파일
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<!-- jquery -->
<script src="./include/jQuery-2.2.4.min.js"></script>
<!-- exceljs 라이브러리 -->
<script src="./include/exceljs/exceljs.min.js"></script>
<script src="./include/exceljs/FileSaver2.js"></script>
</head>
<body>
</body>
</html>
<!-- 엑셀 시트 정의 -->
<script src="./excel_Sheet.js"></script>
<script src="./app.js"></script>
<script type="text/javascript">
<!--
// 엑셀 다운로드 실행
excelDownload(ATTR_1, ATTR_2, ATTR_3, ATTR_4);
//-->
</script>3.1 excel_Sheet.js
엑셀파일 구조를 정의한다.
/**
* excel_Sheet.js
* 엑셀 시트 정의
**/
let excelSheet = (function () {
const obj = {}
obj.getList2016Excel = function getList2016Excel() {
let sheetnm = '1-1 일반현황'; // 시트명
// 컬럼 설정
// header: 엑셀에 표기되는 이름
// key: 컬럼을 접근하기 위한 key
// hidden: 숨김 여부
// width: 컬럼 넓이
let columns = [
{ key:'a', width:5.27 } // A
,{ key:'b', width:19.01 } // B
,{ key:'c', width:6.59 } // C
,{ key:'d', width:11.56, style: { alignment:{horizontal:'right'}, numFmt:'#,##0.00'} } // D
,{ key:'e', width:15.50, style: { alignment:{horizontal:'right'}, numFmt:'#,##0'} } // E
,{ key:'f', width:11.56, style: { alignment:{horizontal:'right'}, numFmt:'#,##0'} } // F
,{ key:'g', width:4.97 , style: { alignment:{horizontal:'right'}, numFmt:'#,##0'} } // G
,{ key:'h', width:4.97 , style: { alignment:{horizontal:'right'}, numFmt:'#,##0'} } // H
,{ key:'i', width:4.97 , style: { alignment:{horizontal:'right'}, numFmt:'#,##0'} } // I
,{ key:'j', width:5.71 , style: { alignment:{horizontal:'right'}, numFmt:'#,##0'} } // J
,{ key:'SUM', width:8.93 , style: { alignment:{horizontal:'right'}, numFmt:'#,##0'} } // K
,{ key:'CNT', width:4.97 , style: { alignment:{horizontal:'right'}, numFmt:'#,##0'} } // L
,{ key:'CNT1', width:4.97 , style: { alignment:{horizontal:'right'}, numFmt:'#,##0'} } // M
,{ key:'PM_CNT', width:4.97 , style: { alignment:{horizontal:'right'}, numFmt:'#,##0'} } // N
,{ key:'PI_CNT', width:5.71 , style: { alignment:{horizontal:'right'}, numFmt:'#,##0'} } // O
,{ key:'DIST_SUM', width:11.27, style: { alignment:{horizontal:'right'}, numFmt:'#,##0'} } // P
,{ key:'DIST_RAT', width:13.16, style: { alignment:{horizontal:'right'}, numFmt:'#,##0.0'} } // Q
];
// header
let headerA0 = [
["Ⅱ. 일반현황"],
["1. 지역별 일반현황"],
[""],
[""]
];
let headerA1 = [
"년도",
"수도사업자",
"자료명",
"행정구역\n면적",
"급수지역수",
"급수\n동·읍·면수\n계",
"급수 동·읍·면수",
"",
"",
"",
"미급수\n동·읍·면수\n계",
"미급수 동·읍·면수",
"",
"",
"",
"행정구역내\n동·읍·면수",
"지표"
];
let headerA2 = [
"", "", "", "", "", "",
"동","읍","면","(도서)","","동","읍","면","(도서)","","동·읍·면 단위\n보급률"
];
let headerA3 = [
"", "",
"단위","(㎢)","(기초자치단체 수)","(개)","(개)","(개)","(개)","(개)","(개)","(개)","(개)","(개)","(개)","(개)","(%)"
];
let headerA4 = [
"", "",
"기호","A1","A2","A3=∑(A4:A6)","A4","A5","A6","A7","A8=∑(A9:A11)","A9","A10","A11","A12","A13=\nA3+A8","A14=A3/A13×100"
];
let header = {
"A0" : headerA0,
"A1" : headerA1,
"A2" : headerA2,
"A3" : headerA3,
"A4" : headerA4,
"cnt" : 4,
"descCnt" : 4
}
// merge 할 header 정보
let merge_list = [
"A5:A8",
"B5:B8",
"C5:C6",
"D5:D6",
"E5:E6",
"F5:F6",
"G5:J5",
"K5:K6",
"L5:O5",
"P5:P6",
];
// 컬럼 기본값
let cellDefault = {
// 조건식 필드
exprField: [],
// 기본값
defaultValue: [
{ range:'4:17',value: 0 }
],
// 상위 column 과 같은 값은 안보이게
rowspan: [
{ range:'1:2' },
],
// 시트 탭 color
tabColor: sum ? '92D050' :'FFC000',
// 시트 틀고정
frozen: {state: 'frozen', xSplit: 3, ySplit: 8}
};
// 시트 정보를 return
let sheetInfo = {
'sheetnm' : sheetnm,
'columns' : columns,
'merge' : merge_list,
'cellDefault': cellDefault,
'header' : header,
'fnNm' : arguments.callee.name
};
return sheetInfo;
}
return {
/** public methods **/
initObj: obj
}
})(); 3.2 app.js
엑셀 파일 만들기
/**
* excelDownload
**/
async function excelDownload () {
// 시트 정보 가져오기(excel_sheet.js 에 정의되어 있는 시트별 정보를 가져와서 변수에 할당)
let sheetInfo_1 = excelSheet.initObj.getList2016Excel();
const excelnm = `통계`; //'통계'; // 엑셀 파일명 정의
const workbook = new ExcelJS.Workbook();
//-----------------------------------------------
// DB에서 데이터를 가져와서 변수에 할당한다. 병렬로 한번에 가져온다.
//-----------------------------------------------
// All
[
data_1
....
] = await Promise.all([
getData (sheetInfo_1.fnNm) // 1
....
]);
//-----------------------------------------------
// 가져온 데이터로 각 시트를 생성하는 함수 호출. 병렬로 한번에 처리한다.
//-----------------------------------------------
await Promise.all([
make_excel(workbook, sheetInfo_1, data_1 )
...
])
const buffer = await workbook.xlsx.writeBuffer();
saveAs(new Blob([buffer]), `${excelnm}.xlsx`);
workbook.removeWorksheet();
}
/**
* make_excel
* 엑셀 파일을 생성한다.
* @param {Object} workbook 워크북 개체
* @param {String} sheetInfo 시트 정보 개체
* @param {Object} data json data
**/
function make_excel (workbook, sheetInfo, data) {
var sheet = workbook.addWorksheet(sheetInfo.sheetnm); // 시트 추가
let columns = sheetInfo.columns; // 컬럼 정보를 가져온다.
let headerDescCnt = sheetInfo.header.descCnt; // 설명컬럼 줄수
//*** header setting ***
// sheetInfo 에서 header 정보를 가져와서 header 를 만든다.
// 설명 헤더 만들기
for (let i=0; i <= 0 ; i++ ) {
let header = eval(`sheetInfo.header.A0`);
for (let j=0; j <= header.length-1; j++) {
sheet.addRow( header[j] );
let headerRow = sheet.getRow(j);
// header cell 스타일 설정
headerRow.eachCell((cell, colNum) => {
setHeaderStyle(cell, true); // header 의 기본 스타일을 설정한다
});
}
}
// 실제 헤더 만들기
for (let i=1; i <= sheetInfo.header.cnt ; i++ ) {
let header = eval(`sheetInfo.header.A${i}`);
sheet.addRow(header);
let headerRow = sheet.getRow(i + headerDescCnt); // 설명컬럼 row 수만큼 더한다.
// header cell 스타일 설정
headerRow.eachCell((cell, colNum) => {
setHeaderStyle(cell, false); // header 의 기본 스타일을 설정한다
});
}
// header merge
for (let merge of sheetInfo.merge) {
sheet.mergeCells(merge);
}
// 시트 설정(시트탭 color, 틀 고정)
setSheetStyle(sheet, sheetInfo.cellDefault);
//-----------------------------------------------
// data cell
//-----------------------------------------------
sheet.columns = columns;
// 컬럼 count
let columnCnt = sheet.columnCount;
let headerCnt = sheetInfo.header.cnt;
// 각 Data cell에 데이터 삽입 및 스타일 지정
data.map((item, index) => {
let row = sheet.addRow(item);
row.height = 21;
let HEAD_GBN = item.HEAD_GBN; // row color 를 설정하기 위해 HEAD_GBN 를 가져온다.
// 컬럼 설정
for (let loop = 1; loop <= columnCnt; loop++) {
let col = sheet.getRow(index + headerCnt +1 + headerDescCnt).getCell(loop);
//console.log('col >>', col);
setCellStyle(col, HEAD_GBN, sheetInfo.cellDefault, sheet, row._number); // cell 의 기본 스타일을 설정한다
}
});
}
/**
* getData
* 쿼리문자열을 입력값으로 받아서 ajax 호출을 실행, json 을 return 한다.
* @param {String} query sql 쿼리 문자열
**/
async function getData (id) {
try {
let url = `/Excel.do?id=${id}`;
let data;
await $.ajax({
type:'post',
url: url,
dataType:'json'
,success : function(res){ //파일 주고받기가 성공했을 경우. data 변수 안에 값을 담아온다.
//console.log('data1 >>', res);
data = res.resDatas;
}
});
return data;
}
catch (err) { console.log('Error >>', err); }
}
/**
* setHeaderStyle
* 헤더 스타일 정의
* @param {Object} cell 셀
* @param {Boolean} bDesc 헤더 중 설명헤더, true 이면 설명헤더 이다
**/
const setHeaderStyle = (cell, bDesc) => {
try {
// 설명 header
if (bDesc) {
cell.fill = {
type : "pattern",
pattern : "solid",
fgColor : { argb: "ffFFFFFF" }
};
cell.font = {
name: "malgun gothic",
size: 12,
bold: true
};
cell.alignment = {
vertical: "left",
horizontal: "left",
wrapText: false
};
// 실제 header
} else {
cell.fill = {
type : "pattern",
pattern : "solid",
fgColor : { argb: "ffE9ECF4" }
};
cell.border = {
top : { style:'thin', color:{argb:'FFA2A2A2'} }
,left : { style:'thin', color:{argb:'FFA2A2A2'} }
,bottom : { style:'thin', color:{argb:'FFA2A2A2'} }
,right : { style:'thin', color:{argb:'FFA2A2A2'} }
};
cell.font = {
name: "malgun gothic",
size: 9,
bold: true
};
cell.alignment = {
vertical: "middle",
horizontal: "center",
wrapText: true
};
}
}
catch (err) { console.log('Error >>', err); }
};
/**
* setCellStyle
* 각 셀의 스타일 및 기본값을 정의
* @param {Object} cell 셀
* @param {String} HEAD_GBN HEAD_GBN 값
* @param {Object} cellDefault cell 기본값정보 및 기타 정보
* @param {Object} sheet 현재 시트
* @param {Int} row_num 현재 row number
**/
const setCellStyle = (cell, HEAD_GBN, cellDefault, sheet, row_num) => {
try {
// 일반 컬럼 스타일
let cell_index = cell._column._number; // 현재 cell index
cell.fill = {
type : "pattern",
pattern : "solid",
fgColor : { argb: "ffFFFFFF" }
};
cell.border = {
top : { style:'thin', color:{argb:'FFA2A2A2'} }
,left : { style:'thin', color:{argb:'FFA2A2A2'} }
,bottom : { style:'thin', color:{argb:'FFA2A2A2'} }
,right : { style:'thin', color:{argb:'FFA2A2A2'} }
};
cell.font = {
name: "malgun gothic",
size: 9
};
// 컬럼설정, 스타일설정에 값이 있는지 확인해서 적용한다.
if (!cell.alignment ) {
cell.alignment = {
vertical: "middle",
horizontal: "center",
wrapText: true
};
} else {
cell.alignment = {
vertical: "middle",
wrapText: true
};
}
// HEAD_GBN 에 따라서 row 의 color 를 설정한다.
if (HEAD_GBN) {
let argb = setRowStyle(cell, HEAD_GBN);
cell.fill = {
type : "pattern",
pattern : "solid",
fgColor : { argb: argb }
};
}
// 기본값 설정, 숫자컬럼에 null 일때 0으로 or 미리 설정해놓은 조건식
if (cellDefault) {
let defVal = cellDefault.defaultValue; // 기본값
let exprField = cellDefault.exprField; // 조건식 필드
// rowspan 값이 있으면 가져오기 없으면 1,2 열을 기본으로 한다.
let rowspan = cellDefault.rowspan ? cellDefault.rowspan : [{ range:'1:2' }] ; // rowspan
// 조건식 필드
for (let i=0; i <= exprField.length-1; i++) {
let defaultValue = exprField[i];
if (defaultValue) {
let startCell_idx = defaultValue.range.split(':')[0]; // cell range 시작 index
let endCell_idx = defaultValue.range.split(':')[1]; // cell range 종료 index
if (cell_index >= startCell_idx && cell_index <= endCell_idx ) {
//console.log(`defaultValue: ${defaultValue}`);
if (cell.value) {
cell.value = eval(defaultValue.expr);
}
}
}
};
// 기본값
for (let i=0; i <= defVal.length-1 ; i++ ) {
let defaultValue = defVal[i];
if (defaultValue) {
let startCell_idx = defaultValue.range.split(':')[0];
let endCell_idx = defaultValue.range.split(':')[1];
if (cell_index >= startCell_idx && cell_index <= endCell_idx ) {
if (cell.value == null || cell.value == '' || cell.value == undefined ) {
cell.value = defaultValue.value
}
}
}
};
// rowspan 필드
for (let i=0; i <= rowspan.length-1 ; i++ ) {
let rowspanInfo = rowspan[i];
if (rowspanInfo) {
let startCell_idx = rowspanInfo.range.split(':')[0];
let endCell_idx = rowspanInfo.range.split(':')[1];
if (cell_index >= startCell_idx && cell_index <= endCell_idx ) {
// 이전 row 가져오기
let _row = sheet.getRow(row_num-1);
// 이전 값과 같으면 안보이게 font color를 배경색과 같게 한다.
if (cell.value == _row.getCell(cell_index).value ) {
let fgcolor = cell.fill.fgColor.argb ? cell.fill.fgColor.argb : 'aaFFFFFF';
cell.font = {color: {argb: fgcolor}};
}
}
}
};
}
}
catch (err) { console.log('Error >>', err); }
};
/**
* setRowStyle
* HEAD_GBN 에 따라서 row background color 설정
* @param {Object} cell 셀
* @param {String} header 헤더여부(true, false)
**/
const setRowStyle = (cell, HEAD_GBN) => {
if (HEAD_GBN == '1') { return 'C6E0B4'; }
else if (HEAD_GBN == '2') { return 'BDD7EE'; }
else if (HEAD_GBN == '3') { return ''; }
else if (HEAD_GBN == '4') { return 'D8BFD8'; }
else if (HEAD_GBN == '5') { return 'DDEBF7'; }
else if (HEAD_GBN == '6') { return 'FFFF00'; }
else if (HEAD_GBN == '7') { return '92D050'; }
else if (HEAD_GBN == '8') { return 'D8BFD8'; }
return '';
}
/**
* setSheetStyle
* sheet 의 frozen 설정 및 시트 전체 스타일 설정
* @param {Object} sheet 시트
* @param {Object} cellDefault 설정값
**/
const setSheetStyle = (sheet, cellDefault) => {
if (cellDefault) {
// 시트탭 color 설정
if (cellDefault.tabColor) {
let tabColor = cellDefault.tabColor;
sheet.properties.tabColor = {argb:'ff'+tabColor};
}
// 시트 틀고정 설정
if (cellDefault.frozen) {
let frozen = cellDefault.frozen;
//sheet.views = [{state: 'frozen', xSplit: 3, ySplit: 4}]
sheet.views = [frozen]
}
}
} 
IanPan