공지사항
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday

티스토리 뷰

■ 프론트엔드 ■/JavaScript

Excel multi sheet export by Vanilla

serpiko 2020. 8. 13. 14:14

 

엑셀 다중 시트 저장(내보내기)

 

 

XML로 스타일을 컨트롤하여 직접 엑셀 내보내는 바닐라 스크립트를 찾았는데 몇가지 기능을 추가하였다

 

 

소소한 타입 기능 추가

 

1) attribute에 data-style이 Number 이면 1000 단위마다 콤마를 표기한다.

'<Style ss:ID="Number"><NumberFormat ss:Format="#,##0_ "></NumberFormat></Style>'

 

2) XML 의 ss:DafaultColumnWidth 로 열의 기본 너비를 적용했다(일일이 정하기 귀찮아서 그냥 100px로 넣었다..)

'<Worksheet ss:Name="{nameWS}"><Table ss:DefaultColumnWidth="100">{rows}</Table><</Worksheet>'

 

3) 위의 1) 번 항목에 있는 ss:ID가 Number로 인식하려면 data-style에 Number형식을 컨텍스트로 추가.

ctx = {  attributeStyleID: (dataStyle=='Currency' || dataStyle=='Date' || dataStyle=='Number')?' ss:StyleID="'+dataStyle+'"':''
								 , nameType: (dataType=='Number' || dataType=='DateTime' || dataType=='Boolean' || dataType=='Error')?dataType:'String'
								 , data: (dataFormula)?'':dataValue
								 , attributeFormula: (dataFormula)?' ss:Formula="'+dataFormula+'"':''
								};

 

 

사용할 수 있는 형식과 결과 알아보기

HTML TTD에서 지정할 Excel Column의 형식 Excel 데이터 타입 결과
<td>5,918,064</td> String                 
<td data-type="Number">5,944,728</td> Number
<td data-type="Number" data-style='Currency'>5,944,728</td> Number
( currency )
<td data-type="Number" data-style='Number'>5,944,728</td> Number
( Thousand comma )
     

 

예제 다운로드)

excel_export.html
0.01MB

 

소스코드 보기)

<!DOCTYPE html>
<html lang="en">
<head>
    <meta name="Author" content="serpiko@hanmail.net" />
    <meta name="description" content="http://serpiko.tistory.com" />
 
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge, chrome=1" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0" />
    <!--<meta name="viewport" content="width=device-width, initial-scale=1">-->
    <meta name="format-detection" content="telephone=no" />
    <title>Document</title>
    <style>
        * {
            box-sizing: border-box;
            -moz-box-sizing: border-box;
            -webkit-box-sizing: border-box;
        }
				table, table td{
					border:1px solid #000;
				}
    </style>
</head>
<body>

<table class="" border="0" cellspacing="0" id='tbl1'>
<colgroup>
	<col width="3%">
	<col width="130">
	<col width="130">
	<col width="130">
</colgroup>
<thead>
	<tr>
		<th rowspan="2">no</th>
		<th rowspan="2">title</th>
		<th rowspan="2">count1</th>
		<th rowspan="2">count2</th>
		<th rowspan="2">total amount</th>
		<th rowspan="2">percent</th>
	</tr>
</thead>
<tbody> 
	<tr>
		<td><button class="btn_txt_green" type="button">1</button></td>
		<td>book</td>
		<td data-type="Number" data-style='Number'>5,944,728</td>
		<td data-type="Number">32</td>
		<td data-type="Number">5,944,760</td>
		<td data-type="Number">0.99</td>
		</tr>

		<tr>
		<td><button class="btn_txt_green" type="button">2</button></td>
		<td>coffee</td>
		<td>5,918,064</td>
		<td>45</td>
		<td>5,918,109</td>
		<td>0.99</td>
	</tr>
	</tbody>
	</table>
<hr>

<table class="" border="0" cellspacing="0" id='tbl2'>
	<colgroup>
		<col width="3%">
		<col width="130">
		<col width="130">
		<col width="130">
	</colgroup>
	<thead>
		<tr>
			<th rowspan="2">no</th>
			<th rowspan="2">국가명</th>
			<th rowspan="2">number</th>
			<th rowspan="2">percent</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td><button class="btn_txt_green" type="button">1</button></td>
			<td>오스트레일리아</td>
			<td>686,283</td>
			<td>0.99</td>
		</tr>

		<tr>
			<td><button class="btn_txt_green" type="button">2</button></td>
			<td>과들루프</td>
			<td>388,991</td>
			<td>0.99</td>
		</tr>

		<tr>
			<td><button class="btn_txt_green" type="button">3</button></td>
			<td>탄자니아</td>
			<td>378,396</td>
			<td>0.99</td>
		</tr>

		<tr>
			<td><button class="btn_txt_green" type="button">4</button></td>
			<td>기니</td>
			<td>378,393</td>
			<td>0.99</td>
		</tr>

		<tr>
			<td><button class="btn_txt_green" type="button">5</button></td>
			<td>소말리아</td>
			<td>377,008</td>
			<td>0.99</td>
		</tr>

		<tr>
			<td><button class="btn_txt_green" type="button">6</button></td>
			<td>니제르</td>
			<td>375,502</td>
			<td>0.99</td>
		</tr>

		<tr>
			<td><button class="btn_txt_green" type="button">7</button></td>
			<td>아프가니스탄</td>
			<td>375,193</td>
			<td>0.99</td>
		</tr>

		<tr>
			<td><button class="btn_txt_green" type="button">8</button></td>
			<td>볼리비아</td>
			<td>370,804</td>
			<td>0.99</td>
		</tr>

		<tr>
			<td><button class="btn_txt_green" type="button">9</button></td>
			<td>쿡 제도</td>
			<td>368,392</td>
			<td>0.99</td>
		</tr>

		<tr>
			<td><button class="btn_txt_green" type="button">10</button></td>
			<td>토켈라우 토켈라우</td>
			<td>368,197</td>
			<td>0.99</td>
		</tr>

		<tr>
			<td><button class="btn_txt_green" type="button">11</button></td>
			<td>피지</td>
			<td>366,884</td>
			<td>0.99</td>
		</tr>

		<tr>
			<td><button class="btn_txt_green" type="button">12</button></td>
			<td>중화인민공화국</td>
			<td>365,197</td>
			<td>0.99</td>
		</tr>

		<tr>
			<td><button class="btn_txt_green" type="button">13</button></td>
			<td>생피에르 미클롱</td>
			<td>364,256</td>
			<td>0.99</td>
		</tr>

		<tr>
			<td><button class="btn_txt_green" type="button">14</button></td>
			<td>체코</td>
			<td>363,200</td>
			<td>0.99</td>
		</tr>
	</tbody>
</table>
			
<hr />

<table class="" border="0" cellspacing="0" id='tbl3'>
	<colgroup>
		<col width="10%">
		<col width="">
		<col width="">
		<col width="">
		<col width="">
		<col width="">
		<col width="">
		<col width="">
		<col width="">
		<col width="">
		<col width="">
		<col width="">
		<col width="">
	</colgroup>
	<thead>
		<tr>
			<th rowspan="2">국가명</th>
			<th rowspan="2">A</th>
			<th rowspan="2">B</th>
			<th rowspan="2">C</th>
			<th rowspan="2">D</th>
			<th rowspan="2">E</th>
			<th rowspan="2">F</th>
			<th rowspan="2">G</th>
			<th rowspan="2">H</th>
			<th rowspan="2">I</th>
			<th rowspan="2">J</th>
			<th rowspan="2">K</th>
			<th rowspan="2">L</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td><button class="btn_txt_green" type="button">가나</button></td>
			<td>25,800  (0.99 %)</td>
			<td>32,399  (0.99 %)</td>
			<td>31,799  (0.99 %)</td>
			<td>29,599  (0.99 %)</td>
			<td>32,364  (0.99 %)</td>
			<td>26,999  (0.99 %)</td>
			<td>28,366  (0.99 %)</td>
			<td>32,200  (0.99 %)</td>
			<td>28,599  (0.99 %)</td>
			<td>32,200 (0.99%)</td>
			<td>29,364 (0.99%)</td>
			<td>33,400 (0.99%)</td>
		</tr>
		
		<tr>
			<td><button class="btn_txt_green" type="button">가봉</button></td>
			<td>27,599  (0.99 %)</td>
			<td>30,364  (0.99 %)</td>
			<td>30,165  (0.99 %)</td>
			<td>30,164  (0.99 %)</td>
			<td>24,766  (0.99 %)</td>
			<td>29,199  (0.99 %)</td>
			<td>28,196  (0.99 %)</td>
			<td>26,998  (0.99 %)</td>
			<td>33,000  (0.99 %)</td>
			<td>27,600 (0.99%)</td>
			<td>28,999 (0.99%)</td>
			<td>29,200 (0.99%)</td>
		</tr>
		
		<tr>
			<td><button class="btn_txt_green" type="button">가이아나</button></td>
			<td>31,200  (0.99 %)</td>
			<td>30,166  (0.99 %)</td>
			<td>28,400  (0.99 %)</td>
			<td>27,198  (0.99 %)</td>
			<td>27,566  (0.99 %)</td>
			<td>30,599  (0.99 %)</td>
			<td>27,400  (0.99 %)</td>
			<td>31,166  (0.99 %)</td>
			<td>28,800  (0.99 %)</td>
			<td>31,998 (0.99%)</td>
			<td>28,998 (0.99%)</td>
			<td>28,598 (0.99%)</td>
		</tr>
	</tbody>
</table>

<button  onclick="tablesToExcel(['tbl1','tbl2', 'tbl3'], ['시트명1','시트명2', '시트명3'], '통계.xls', 'Excel')">Export to Excel</button>
<script type="text/javascript">
	var tablesToExcel = (function() {
	var uri = 'data:application/vnd.ms-excel;base64,'
	, tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
		+ '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>serpiko</Author><Created>{created}</Created></DocumentProperties>'
		+ '<Styles>'
		+ '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'
    + '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'
		+ '<Style ss:ID="Number"><NumberFormat ss:Format="#,##0_ "></NumberFormat></Style>'
		+ '</Styles>' 
		+ '{worksheets}</Workbook>'
	, tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table ss:DefaultColumnWidth="100">{rows}</Table><</Worksheet>'
	, tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'
	, base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
	, format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }
	return function(tables, wsnames, wbname, appname) {
		var ctx = "";
		var workbookXML = "";
		var worksheetsXML = "";
		var rowsXML = "";

		for (var i = 0; i < tables.length; i++) {
			if (!tables[i].nodeType) tables[i] = document.getElementById(tables[i]);
			for (var j = 0; j < tables[i].rows.length; j++) {
				rowsXML += '<Row>'
				for (var k = 0; k < tables[i].rows[j].cells.length; k++) {
					var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");
					var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");
					var dataValue = tables[i].rows[j].cells[k].getAttribute("<em></em>data-value");
					dataValue = (dataValue)?dataValue:tables[i].rows[j].cells[k].innerHTML;
					var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");
					dataFormula = (dataFormula)?dataFormula:(appname=='Calc' && dataType=='DateTime')?dataValue:null;
					ctx = {  attributeStyleID: (dataStyle=='Currency' || dataStyle=='Date' || dataStyle=='Number')?' ss:StyleID="'+dataStyle+'"':''
								 , nameType: (dataType=='Number' || dataType=='DateTime' || dataType=='Boolean' || dataType=='Error')?dataType:'String'
								 , data: (dataFormula)?'':dataValue
								 , attributeFormula: (dataFormula)?' ss:Formula="'+dataFormula+'"':''
								};
					rowsXML += format(tmplCellXML, ctx);
				}
				rowsXML += '</Row>'
			}
			ctx = {rows: rowsXML, nameWS: wsnames[i] || 'Sheet' + i};
			worksheetsXML += format(tmplWorksheetXML, ctx);
			rowsXML = "";
		}

		ctx = {created: (new Date()).getTime(), worksheets: worksheetsXML};
		workbookXML = format(tmplWorkbookXML, ctx);

		var link = document.createElement("A");
		link.href = uri + base64(workbookXML);
		link.download = wbname || 'Workbook.xls';
		link.target = '_blank';
		document.body.appendChild(link);
		link.click();
		document.body.removeChild(link);
	}
})();
// source: https://stackoverflow.com/questions/29698796/how-to-convert-html-table-to-excel-with-multiple-sheet/29717451
</script>
</body>
</html>

XML 속성 좀 더 알아보기

<?xml version='1.0' encoding='UTF-8'?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles> // 스타일을 정의하는 부분. 태그내에 삽입이 불가능하기에 일일이 스타일을 지정해줘야함.
<Style ss:ID="Default" ss:Name="Normal"> // 전체적으로 적용되는 스타일
<Alignment  ss:Horizontal="Center" ss:Vertical="Center" /> // 정렬
<Borders> // 셀 테두리
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" />
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" />
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" />
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" />
</Borders>
<Font ss:FontName="고딕" ss:Size="11" ss:Color="Blue" ss:Underline="Single" /> // 글씨 크기와 색상, 모양
<Interior ss:Color="Black" ss:Pattern="Solid" /> // 셀의 색
<NumberFormat /> //  숫자 표현 방식
<Protection />
</Style>
<Style ss:ID="Headline"> // Headline 태그에만 적용되는 스타일
<Alignment />
<Font />
<Interior />
</Style>
<Style ss:ID="Charge">
<NumberFormat ss:Format="#,##0_ " /> // 천단위마다 콤마
</Style>
</Styles>
<Worksheet ss:Name="Test1"> // 첫번째 시트의 이름
<Table>
<Column ss:Width="50" /> // 셀의 너비를 정함
<Column ss:Width="300" />
<Column ss:Width="150" />
<Row ss:Index="2"> // Row의 시작 위치
<Cell ss:StyleID="Headline"><Data ss:Type="String">Excel Test</Data></Cell>
<Cell ss:StyleID="Charge"><Data ss:Type="Number">1000000</Data></Cell>
<Cell ss:Formula="=SUM(R[-2]C, R[-1]C)"><Data ss:Type="Number">10</Data></Cell> // 연산
</Row>
</Table>
</Worksheet>
<Worksheet ss:Name="Test2"> // 두번째 시트의 이름
<Table ss:DefaultColumnWidth="100" ss:DefaultRowHeight="200"> // 셀 1개당 기본 길이
<Row>
<Cell ss:HRef="mailto:test@test.com"><Data ss:Type="String">test@test.com</Data></Cell> // 링크
<Cell ss:MergeAcross="2" ss:MergeDown="3"><Data ss:Type="String"></Data></Cell> // 셀 합치기
</Row>
</Table>
</Worksheet>
</Workbook>

 

 

 

출처

https://blog.naver.com/cozymiru/54913008

https://stackoverflow.com/questions/29698796/how-to-convert-html-table-to-excel-with-multiple-sheet/29717451

저작자표시 비영리 동일조건

'■ 프론트엔드 ■ > JavaScript' 카테고리의 다른 글

ES6 구조 분해 할당 - Expressions Destructuring assignment  (0) 2020.11.06
두개의 배열의 값을 비교할때, 조금 더 나은방법 Array.filter (in) Array.indexOf  (0) 2020.09.13
compare two JSON have the same properties  (0) 2020.07.24
Template Literals 에서 조건문 쉽게 사용하기  (0) 2020.07.14
ES6. 리듀서 함수를 이용한 객체 누산기  (0) 2020.07.08
댓글