2021-04-23

DataTables에서 excel을 다운로드 할때, excel의 각 cell에 기울기나 굵기, 색상 등을 입히고 싶을 때는 아래와 같이 하면 된다.
참고링크1: https://datatables.net/reference/button/excelHtml5
참고링크2: https://datatables.net/extensions/buttons/examples/html5/excelCellShading.html

<table id="example" class="display" style="width:100%">
	<thead>
		<tr>
			<th>이름</th>
			<th>직위</th>
			<th>지역</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td>홍길동</td>
			<td>부장</td>
			<td>인천</td>
		</tr>
		<tr>
			<td>김원희</td>
			<td>과장</td>
			<td>서울</td>
		</tr>
		<tr>
			<td>김길자</td>
			<td>대리</td>
			<td>대전</td>
		</tr>
		<tr>
			<td>김길동</td>
			<td>대리</td>
			<td>인천</td>
		</tr>
	</tbody>
</table>

<link rel="stylesheet" href="/post_inc/datatables/jquery.dataTables.min.css">
<link rel="stylesheet" href="/post_inc/datatables/buttons.dataTables.min.css">

<script src="/post_inc/datatables/jquery.dataTables.1.10.20.min.js"></script>
<script src="/post_inc/datatables/dataTables.buttons.min.js"></script>
<script src="/post_inc/datatables/jszip.min.js"></script>
<script src="/post_inc/datatables/buttons.html5.min.js"></script>

<script type="text/javascript">
$(function(){
	$('#example').DataTable( {
		dom: 'Bfrtip',
		buttons: [
			{
				extend: 'excel'
				,text: '엑셀출력'
				,filename: '엑셀파일명'
				,title: '엑셀파일 안에 쓰일 제목'
				// customize 옵션으로 excel를 수정하는게가능하다.
				,customize: function(xlsx) {
					var sheet = xlsx.xl.worksheets['sheet1.xml'];

					$('row', sheet).each( function (k,v) {
						// A열중에서 text가 '김원희'일 경우 style 20이, 해당 열의 B,C에 적용됨
						if ( $('c[r^="A"]', this).text() == '김원희' ) {
							$('c[r^="B"], c[r^="C"]', this).attr( 's', '20' );
						}
					});

					$('row c[r^="C"]', sheet).each( function () {
						// C열 중에서 인천은 style 39 를 적용
						if ( $('is t', this).text() == '인천' ) {
							$(this).attr( 's', '39' );
						}
					});

					// A열의 1번째행의 제목을 수정하는 부분
					$('c[r=A1] t', sheet).text( '제목 바꿔 치기' );

					// A열의 2행, B열의 2행, C열의 2행을 수정하는 방법
					$('c[r=A2]', sheet).attr( 's', '17' );
					$('c[r=B2]', sheet).attr( 's', '7' );
					$('c[r=C2]', sheet).attr( 's', '36' );

				}
			},
		]
	});
})
</script>

위의 엑셀을 다운로드 해보면, 위에서 적용한 것들이 적용된 채로 엑셀이 나올 것이다.
style에 관해서는 아래를 참조하면 될것이다.
직접 적용하는게 아니라, 아래 지정된 것들로만 지정이 가능하다.

Styles:

• 0 - Normal text
• 1 - White text
• 2 - Bold
• 3 - Italic
• 4 - Underline
• 5 - Normal text, grey background
• 6 - White text, grey background
• 7 - Bold, grey background
• 8 - Italic, grey background
• 9 - Underline, grey background
• 10 - Normal text, red background
• 11 - White text, red background
• 12 - Bold, red background
• 13 - Italic, red background
• 14 - Underline, red background
• 15 - Normal text, green background
• 16 - White text, green background
• 17 - Bold, green background
• 18 - Italic, green background
• 19 - Underline, green background
• 20 - Normal text, blue background
• 21 - White text, blue background
• 22 - Bold, blue background
• 23 - Italic, blue background
• 24 - Underline, blue background
• 25 - Normal text, thin black border
• 26 - White text, thin black border
• 27 - Bold, thin black border
• 28 - Italic, thin black border
• 29 - Underline, thin black border
• 30 - Normal text, grey background, thin black border
• 31 - White text, grey background, thin black border
• 32 - Bold, grey background, thin black border
• 33 - Italic, grey background, thin black border
• 34 - Underline, grey background, thin black border
• 35 - Normal text, red background, thin black border
• 36 - White text, red background, thin black border
• 37 - Bold, red background, thin black border
• 38 - Italic, red background, thin black border
• 39 - Underline, red background, thin black border
• 40 - Normal text, green background, thin black border
• 41 - White text, green background, thin black border
• 42 - Bold, green background, thin black border
• 43 - Italic, green background, thin black border
• 44 - Underline, green background, thin black border
• 45 - Normal text, blue background, thin black border
• 46 - White text, blue background, thin black border
• 47 - Bold, blue background, thin black border
• 48 - Italic, blue background, thin black border
• 49 - Underline, blue background, thin black border
• 50 - Left aligned text (since 1.2.2)
• 51 - Centred text (since 1.2.2)
• 52 - Right aligned text (since 1.2.2)
• 53 - Justified text (since 1.2.2)
• 54 - Text rotated 90° (since 1.2.2)
• 55 - Wrapped text (since 1.2.2)
• 56 - Percentage integer value (automatically detected and used by buttons - since 1.2.3)
• 57 - Dollar currency values (automatically detected and used by buttons - since 1.2.3)
• 58 - Pound currency values (automatically detected and used by buttons - since 1.2.3)
• 59 - Euro currency values (automatically detected and used by buttons - since 1.2.3)
• 60 - Percentage with 1 decimal place (automatically detected and used by buttons - since 1.2.3)
• 61 - Negative numbers indicated by brackets (automatically detected and used by buttons - since 1.2.3)
• 62 - Negative numbers indicated by brackets - 2 decimal places (automatically detected and used by buttons - since 1.2.3)
• 63 - Numbers with thousand separators (automatically detected and used by buttons - since 1.2.3)
• 64 - Numbers with thousand separators - 2 decimal places (automatically detected and used by buttons - since 1.2.3)
• 65 - Numbers without thousand separators (automatically detected and used by buttons - since 1.2.4)
• 66 - Numbers without thousand separators - 2 decimal places (automatically detected and used by buttons - since 1.2.4)

Notes:

• Grey is #d9d9d9
• Red is #d99795
• Green is #6efce
• Blue is #c6cfef