SheetJS 테이블 내용 엑셀 다운로드, 스타일 적용 방법

회사에서 엑셀 다운로드 개발 업무를 맡게 됐는데 기존 소스 로직은

페이징 처리된 부분은 1페이지만 다운이 되서 전체 row를 추출하기 위해

SheetJS 라이브러리를 사용하게 됐다.

결론적으론... 이 라이브러리를 사용 안 하기로 했지만 덕분에 스터디를 했기 때문에 기록해 본다.

 

SheetJS 란?

Javascript 클라이언트단에서 엑셀 다운로드 구현 시 사용되는 라이브러리

https://docs.sheetjs.com/

SheetJS Community Edition | SheetJS Community Edition

 

1. SheetJS , FileSaver 스크립트 추가

<!-- SheetJS -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.3/xlsx.full.min.js"></script>
<!-- FileSaver saveAs 이용 -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.8/FileSaver.min.js"></script>
<!-- 엑셀 cell style 적용 -->
<script src="https://cdn.jsdelivr.net/npm/xlsx-js-style@1.2.0/dist/xlsx.min.js"></script>

 

2. 테이블 내용 JSON 으로 가져오기

SheetJS에서 테이블 내용을 가져오는 함수는 json_to_sheet, table_to_sheet 등 다양한데 나처럼 페이징 상관없이 전체 row를 가져올 때엔 JSON으로 가져와야 한다. 

회사에선 DataTable을 사용 중이라 $("#테이블 ID).DataTable(); 이렇게 값을 가져왔다.

function tableToJson(tableId) { 
    var transactions = [];
    
    var table = $('#'+tableId).DataTable(); 
    var data = table.rows().data(); 
    
    for (var i = 0; i < data.length; i++) { 
    	for (var j = 0; j < data[i].length; j++) {
    		/* 회사 코드라 생략 */
    	}
    	
    	transactions.push(data[i]); 
    }
    
    return transactions;
}

 

3. 커스텀 헤더 만들기

JSON은 key, value 형식으로 이루어져 있는데 위의 소스대로 하면 key가 그냥 1, 2, 3 ... 이렇게 들어가서

테이블 상의 제일 첫 번째 줄(<th> 태그)에 해당하는 부분이 key값이 되도록 커스텀 헤더를 만들어주었다.

function tableHeaderToJson(tableId) { 
    var headers = [];
    var arrOfArr = [];
    $("thead").find("th").each(function () {
    	headers.push($(this).text().trim());
    });
    
    $("#"+tableId).find("tr").each(function () {
    	
    	$("#"+tableId).find("td").each(function (i) {
    		var td = $(this);
    		var tdVal = '';
        	tdVal = td.eq(i).html();
    	});
    });
    
    arrOfArr[0]=headers;
    return arrOfArr;
}

SheetJS에서 커스텀 헤더를 만들기 위해선 Array Of Array가 필요해서 위와 같이 구현함.

참고 링크 : https://stackoverflow.com/questions/52215676/sheetjs-json-to-sheet-renaming-headers

SheetJS json_to_sheet renaming headers

 

4. SheetJS 테이블 내용 Excel 다운로드

function downloadExcel(table, headName) {
	// table 데이터를 json 형식으로 받아옴.
	var jsonData = tableToJson(table);
	// json key 값을 컬럼명으로 넣기 위해 table header값을 받아옴.
	var headerArr = tableHeaderToJson(table);
	// workbook 생성
	var work_book = XLSX.utils.book_new(); 
	
	// 시트 만들기 (모든 페이지를 엑셀에 담기 위해 json_to_sheet 사용)
	//var work_sheet = XLSX.utils.table_to_sheet("#"+table);
	var work_sheet = XLSX.utils.json_to_sheet(jsonData);
	// 엑셀 헤더 값을 컬럼명으로 rename
	XLSX.utils.sheet_add_aoa(work_sheet, headerArr);
	
	XLSX.utils.sheet_add_json(work_sheet, jsonData, { origin: 'A2', skipHeader: true });

	// workbook에 새로 만든 워크시트에 이름을 주고 붙인다.
	XLSX.utils.book_append_sheet(work_book, work_sheet, headName);
	// 엑셀 파일 만들기
	var work_book_down = XLSX.write(work_book, {bookType:'xlsx',  type: 'binary'});
	// 엑셀 파일 내보내기 
	saveAs(new Blob([s2ab(work_book_down)],{type:"application/octet-stream"}), headName+'.xls');
}

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;    
}

대략적인 로직은 위와 같다. Workbook을 생성하고 Sheet를 생성하고 이름을 붙이고 다운로드...

설명은 주석 달아두었음.

 

5. 엑셀 헤더 cell style 적용

위와 같이 했을 때 전체 페이지에 해당하는 모든 row 엑셀로 추출하기 성공! 그런데 다음 과제는...

헤더에 색상을 넣는 style을 적용하는 거였다.

// Make first row bold
	for(let i = 0; i < headerArr[0].length; i++) {
	    const cell = work_sheet[XLSX.utils.encode_cell({r: 0, c: i})];
	    // Create new style if cell doesnt have a style yet
	    if(!cell.s) {cell.s = {};}
	    if(!cell.s.font) {cell.s.font = {};}
	    // Set bold
	    //cell.s.font.bold = true;
	    cell.s = {
	    	font: {
	    		bold: true
	    	},
	    	fill: {
	    		patternType: "solid",
	    		fgColor: { rgb: "b1b1b1" }
	    	}
	    }
	}

 

6. cell 길이 자동 적용

글자수가 길 때에는 엑셀 cell 길이가 동적으로 맞춰지도록 적용함.

let objectMaxLength = []; 
	jsonData.map(arr => {
	  Object.keys(arr).map(key => {
	    let value = arr[key] === null ? '' : arr[key]

	    if (typeof value === 'number') {
	      return objectMaxLength[key] = 10
	    }

	    objectMaxLength[key] = objectMaxLength[key] >= value.length ? objectMaxLength[key]  : value.length + 5
	  })
	})
    let worksheetCols = objectMaxLength.map(width => {
	  return {
	    width
	  }
	});
    
    work_sheet["!cols"] = worksheetCols;

 

7. 전체 소스

<table id="resultTbl" class="table">
</table>

<button type="button" class=common-button" id="excelBtn">엑셀 다운로드</button><!-- 엑셀 -->

$('#excelBtn').click(function() {
    var headName = '';
    headName += setFileName(selectedMenuName);	//메뉴명을 파일명으로 지정
    downloadExcel('resultTbl',headName);	
});

function downloadExcel(table, headName) {
	// table 데이터를 json 형식으로 받아옴.
	var jsonData = tableToJson(table);
	// json key 값을 컬럼명으로 넣기 위해 table header값을 받아옴.
	var headerArr = tableHeaderToJson(table);
	// workbook 생성
	var work_book = XLSX.utils.book_new(); 
	
	let objectMaxLength = []; 
	jsonData.map(arr => {
	  Object.keys(arr).map(key => {
	    let value = arr[key] === null ? '' : arr[key]

	    if (typeof value === 'number') {
	      return objectMaxLength[key] = 10
	    }

	    objectMaxLength[key] = objectMaxLength[key] >= value.length ? objectMaxLength[key]  : value.length + 5
	  })
	})
    let worksheetCols = objectMaxLength.map(width => {
	  return {
	    width
	  }
	})
	// 시트 만들기 (모든 페이지를 엑셀에 담기 위해 json_to_sheet 사용)
	//var work_sheet = XLSX.utils.table_to_sheet("#"+table);
	var work_sheet = XLSX.utils.json_to_sheet(jsonData);
	
	sheet2arr(work_sheet);
	
	work_sheet["!cols"] = worksheetCols;
	// 엑셀 헤더 값을 컬럼명으로 rename
	XLSX.utils.sheet_add_aoa(work_sheet, headerArr);
	
	XLSX.utils.sheet_add_json(work_sheet, jsonData, { origin: 'A2', skipHeader: true });
	
	
	
	// Make first row bold
	for(let i = 0; i < headerArr[0].length; i++) {
	    const cell = work_sheet[XLSX.utils.encode_cell({r: 0, c: i})];
	    // Create new style if cell doesnt have a style yet
	    if(!cell.s) {cell.s = {};}
	    if(!cell.s.font) {cell.s.font = {};}
	    // Set bold
	    //cell.s.font.bold = true;
	    cell.s = {
	    	font: {
	    		bold: true
	    	},
	    	fill: {
	    		patternType: "solid",
	    		fgColor: { rgb: "b1b1b1" }
	    	}
	    }
	}
	
	// workbook에 새로 만든 워크시트에 이름을 주고 붙인다.
	XLSX.utils.book_append_sheet(work_book, work_sheet, headName);
	// 엑셀 파일 만들기
	var work_book_down = XLSX.write(work_book, {bookType:'xlsx',  type: 'binary'});
	// 엑셀 파일 내보내기 
	saveAs(new Blob([s2ab(work_book_down)],{type:"application/octet-stream"}), headName+'.xls');
}

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 tableHeaderToJson(tableId) { 
    var headers = [];
    var arrOfArr = [];
    $("thead").find("th").each(function () {
    	headers.push($(this).text().trim());
    });
    
    $("#"+tableId).find("tr").each(function () {
    	
    	$("#"+tableId).find("td").each(function (i) {
    		var td = $(this);
    		var tdVal = '';
        	tdVal = td.eq(i).html();
    	});
    	
    });
    
    arrOfArr[0]=headers;
    return arrOfArr;
} 

function tableToJson(tableId) { 
    var transactions = [];
    
    var table = $('#'+tableId).DataTable(); 
    var data = table.rows().data(); 
    
    for (var i = 0; i < data.length; i++) { 
    	for (var j = 0; j < data[i].length; j++) {
    		/* 회사 소스라 생략 */
    	}
    	
    	transactions.push(data[i]); 
    }
    
    return transactions;
}

 

8. merge cell

맨위에서 언급했던 결론적으로 SheetJS 라이브러리를 사용하지 않기로 한 이유는 merge cell 때문이었다.

테이블에서 rowspan, colspan 처리된 cell은 노가다(?)로 코드를 작성해야 해서 번거롭기 때문...

SheetJS에서 merge cell 처리하는 방법은 아래 링크 참고!

 

https://stackoverflow.com/questions/53516403/sheetjs-xlsx-how-to-write-merged-cells

sheetjs xlsx, How to write merged cells?

https://juni-official.tistory.com/243

sheet.js 셀 스타일 및 병합(merge) 방법