const spread = new GC.Spread.Sheets.Workbook(document.getElementById('spreadContainer'), { sheetCount: 2 });
const kpiSheet = spread.sheets[0];
const dataSheet = spread.sheets[1];
kpiSheet.name('KPI');
dataSheet.name('Data');
spread.options.tabStripVisible = false;
kpiSheet.options.colHeaderVisible = false;
kpiSheet.options.rowHeaderVisible = false;
kpiSheet.options.gridline = { showVerticalGridline: false, showHorizontalGridline: false };
// 타이틀 행
kpiSheet.addSpan(0, 0, 1, 3);
kpiSheet.setValue(0, 0, 'FEMS 에너지 KPI 현황');
const titleStyle = new GC.Spread.Sheets.Style();
titleStyle.font = 'bold 16px sans-serif';
titleStyle.foreColor = '#ffffff';
titleStyle.backColor = '#1565C0';
titleStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
titleStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
kpiSheet.setStyle(0, 0, titleStyle);
kpiSheet.setRowHeight(0, 38);
// 데이터 시트에 KPI 기준값 입력
dataSheet.setArray(0, 0, [
['지표', '목표', '현재', '최소', '최대'],
['kWh 소비', 350, 312, 0, 500],
['CO₂(kg)', 150, 178, 0, 250],
['전력피크(kW)', 80, 92, 0, 120],
]);
// 게이지 행 크기 설정
kpiSheet.setRowHeight(1, 220);
for (let c = 0; c < 3; c++) kpiSheet.setColumnWidth(c, 240);
// GAUGEKPISPARKLINE 수식 삽입
const fonts = '{"normal normal 9pt Calibri","normal bold 14pt Calibri","normal normal 8pt Calibri","normal normal 8pt Calibri"}';
kpiSheet.setFormula(1, 0,
'=GAUGEKPISPARKLINE(Data!B2,Data!C2,Data!D2,Data!E2,TRUE,TEXT(Data!C2,"0 kWh"),Data!A2,TEXT(Data!D2,"0"),TEXT(Data!E2,"0"),' + fonts + ',-90,90,0.4,0,{0,300,"#BCEAB4"},{300,450,"#FFDFB0"},{450,500,"#FFB2BD"})');
kpiSheet.setFormula(1, 1,
'=GAUGEKPISPARKLINE(Data!B3,Data!C3,Data!D3,Data!E3,TRUE,TEXT(Data!C3,"0 kg"),Data!A3,TEXT(Data!D3,"0"),TEXT(Data!E3,"0"),' + fonts + ',-90,90,0.4,0,{0,100,"#BCEAB4"},{100,200,"#FFDFB0"},{200,250,"#FFB2BD"})');
kpiSheet.setFormula(1, 2,
'=GAUGEKPISPARKLINE(Data!B4,Data!C4,Data!D4,Data!E4,TRUE,TEXT(Data!C4,"0 kW"),Data!A4,TEXT(Data!D4,"0"),TEXT(Data!E4,"0"),' + fonts + ',-90,90,0.4,0,{0,60,"#BCEAB4"},{60,95,"#FFDFB0"},{95,120,"#FFB2BD"})');
// 게이지 셀 배경
const gaugeStyle = new GC.Spread.Sheets.Style();
gaugeStyle.backColor = '#E3F2FD';
for (let c = 0; c < 3; c++) kpiSheet.setStyle(1, c, gaugeStyle);