JavaScript
// 분기별 영업 성과 대시보드 — SpreadJS 수식·고정·조건부서식 데모
const spread = new GC.Spread.Sheets.Workbook(document.getElementById('spreadContainer'));
const sheet = spread.getActiveSheet();
spread.options.tabStripVisible = false;
// 헤더 설정
const headers = ['영업사원', 'Q1(만원)', 'Q2(만원)', 'Q3(만원)', 'Q4(만원)', '연간합계', '달성률'];
headers.forEach((h, c) => {
sheet.setValue(0, c, h);
sheet.getCell(0, c).backColor('#1565C0').foreColor('#ffffff').font('bold 12px sans-serif');
sheet.setColumnWidth(c, c === 0 ? 90 : 100);
});
// 샘플 데이터 (연간 목표: 1.5억원)
const target = 15000;
const data = [
['김민준', 2800, 3200, 2900, 3500],
['이서연', 4200, 3900, 4500, 4800],
['박지호', 1500, 1800, 2100, 1900],
['최유진', 5100, 5300, 4900, 5600],
['정수민', 3300, 3100, 3400, 3700],
['강태양', 2200, 2600, 2400, 2800],
['윤지원', 4800, 4500, 5000, 5200],
['한소희', 1800, 2000, 1700, 2100],
];
data.forEach((row, r) => {
const dr = r + 1; // 0-indexed 시트 행
sheet.setValue(dr, 0, row[0]);
for (let q = 0; q < 4; q++) {
sheet.setValue(dr, q + 1, row[q + 1]);
sheet.getCell(dr, q + 1).formatter('#,##0');
}
// ★ SpreadJS 수식 엔진 — TanStack Table에서는 JS로 직접 계산 필요
sheet.setFormula(dr, 5, `=SUM(B${dr + 1}:E${dr + 1})`);
sheet.getCell(dr, 5).font('bold 11px sans-serif').formatter('#,##0');
sheet.setFormula(dr, 6, `=F${dr + 1}/${target}`);
sheet.getCell(dr, 6).formatter('0.0%');
// 달성률에 따른 조건부 배경색
const sum = row.slice(1).reduce((a, b) => a + b, 0);
const rate = sum / target;
const bg = rate >= 1.0 ? '#E8F5E9' : rate >= 0.8 ? '#FFF9C4' : '#FFEBEE';
for (let c = 0; c <= 6; c++) sheet.getCell(dr, c).backColor(bg);
sheet.setRowHeight(dr, 28);
});
// 팀 합계 행 (수식으로 자동 집계)
const total = data.length + 1;
sheet.setValue(total, 0, '팀 합계');
sheet.getCell(total, 0).font('bold 12px sans-serif').backColor('#E3F2FD');
['B', 'C', 'D', 'E', 'F'].forEach((col, i) => {
sheet.setFormula(total, i + 1, `=SUM(${col}2:${col}${total})`);
sheet.getCell(total, i + 1).font('bold 11px sans-serif').formatter('#,##0').backColor('#E3F2FD');
});
sheet.setFormula(total, 6, `=F${total + 1}/${target * data.length}`);
sheet.getCell(total, 6).font('bold 11px sans-serif').formatter('0.0%').backColor('#E3F2FD');
sheet.setRowHeight(total, 32);
// ★ 헤더 행 고정 — TanStack Table은 CSS sticky 직접 구현 필요
sheet.frozenRowCount(1);
// ★ 영업사원 열 고정
sheet.frozenColumnCount(1);
sheet.setRowHeight(0, 36);
sheet.options.gridlineColor = '#E0E0E0';