[kendo ui] 엑셀 시트 여러개 만드는 방법, excel muti sheet, 엑셀 루틴

1. 엑셀 버튼 영역

<input type="button" id="excelButton" class="btn btn_b excelDown" value="엑셀" />

 

 

 

2. kendo grid excel 영역

<div id="kendoStatExcelGrid"></div>
<div id="kendoStatExcel2Grid"></div>

 

 

 

3. scirpt안에서 excel 영역 숨기기

<%-- 엑셀용 숨김처리 --%>
$("#kendoStatExcelGrid").hide();
$("#kendoStatExcel2Grid").hide();

 

 

 

 

4. script안에서 엑셀버튼 클릭 시 이벤트 코드

//엑셀버튼 클릭시
$("#excelButton").click(function(){

$("#kendoStatExcelGrid").data("kendoGrid").saveAsExcel();
$("#kendoStatExcel2Grid").data("kendoGrid").saveAsExcel();

$.when.apply(null, promises).then(function(firstSheet, secondSheet) {
	var sheets = [
		firstSheet.sheets[0],
		secondSheet.sheets[0]
	];

	sheets[0].title = "00";
	sheets[1].title = "00";

	var workbook = new kendo.ooxml.Workbook({
		sheets: sheets
	});

	kendo.saveAs({
		dataURI: workbook.toDataURL(),
		fileName: "00.xlsx"
		})
	});

	
});

 

 

 

 

5. 데이터 조회하는 kendo code

var dataSourceExcel = new kendo.data.DataSource({
		      transport: {
		         read: function(options){
		            $.ajax({
		               url: '<c:url value="00"/>',
		               data: {
		            	   type          : "00"
		               },
		               dataType: "json",
		               success: function(result) {
		                  options.success(result);
		               },
		               error: function(result) {
		                  options.error(result);
		               }
		            });
		         },
		         parameterMap: function(options, operation) {
		            return {models: kendo.stringify(options.data)};
		         }
		      },
		      schema: {
		         data: 'resultList',
		         errors: 'errors'          
		      },
		      aggregate: [ 
		               { field: "00", aggregate: "sum" },
		               { field: "00",    aggregate: "sum" }
		            ]
		   });

		   $("#kendoStatExcelGrid").kendoGrid({
		        dataSource: dataSourceExcel,
		        height: 350,
		        sortable:true,
		        noRecords:true,
		        messages:{
		           noRecords: "조회된 내용이 없습니다."
		        },
		        excelExport: function(e) {
		            e.preventDefault();
		            
	    			var workbook = e.workbook;
	    			var sheet = workbook.sheets[0];
	    			var rows = e.workbook.sheets[0].rows;

	    			for (var ri = 0; ri < rows.length; ri++) {
	    				var row = rows[ri];
	    				if (row.type == "header" || row.type == "group-footer" || row.type == "footer") {
	    					for (var ci = 0; ci < row.cells.length; ci++) {
	    						var cell = row.cells[ci];
	    						
	    						cell.bold = true;
	    						cell.color  = "black";
	    						cell.background  = "#DCDCDC";
	                            // 셀 높이 설정
	                            cell.fontSize = 12;
	                            cell.verticalAlign = "middle";
	    						
	    						cell.borderTop = { color: "black", size: 1 };
	    						cell.borderRight = { color: "black", size: 1 };
	    						cell.borderBottom = { color: "black", size: 1 };
	    						cell.borderLeft = { color: "black", size: 1 };
	    						
	    						if(cell.value && cell.value.toString().indexOf("</br>") >= 0){
	    							cell.value = cell.value.replace("</br>", "\n");   
	    							cell.wrap = true;

	    						}
	    					}
	    				}
	    			}
	    			workbook.rtl = true;
	    			for (var i = 0; i < sheet.rows.length; i++) { // 셀 정렬 지정
	    				for (var ci = 0; ci < sheet.rows[i].cells.length; ci++) {
	    					sheet.rows[i].cells[ci].verticalAlign = "center";
	    					if(i>0){
	    						if(ci == '0'){
	    							sheet.rows[i].cells[ci].hAlign = "left";
	    							continue;
	    						}
	    					}
	    					sheet.rows[i].cells[ci].hAlign = "center";
	    				}
	    			}
		            
		            promises[0].resolve(e.workbook);
		          },
		        columns: [
		             { field: '00',   title:'00',    width: 150,sortable: false,footerTemplate: "계" ,headerAttributes:{ style : "text-align : center; background-color:#d9e5ff; font-weight:bold;" },},
		                 { field: '00', title: '00', width: 120,aggregates: ["sum"],footerTemplate: "#= kendo.format('{0:N0}', sum)#",headerAttributes : { style : "text-align : center; background-color:#d9e5ff; font-weight:bold;" }, },
		                 ],
		         dataBound : function() {
		           cmCallback();
		        } 
		     });