JavaScript
try { if (typeof GC.Spread.Sheets.Worksheet.prototype.tabColor !== 'function') { GC.Spread.Sheets.Worksheet.prototype.tabColor = function(c){if(c!==undefined)this._tc=c;return this._tc||''; }; } } catch(e) {}
try { if (typeof GC.Spread.Sheets.Worksheet.prototype.tabColor !== 'function') { GC.Spread.Sheets.Worksheet.prototype.tabColor = function(c){if(c!==undefined)this._tc=c;return this._tc||''; }; } } catch(e) {}
const spread = new GC.Spread.Sheets.Workbook(document.getElementById('spreadContainer'));
const MONTHS = ['1월', '2월', '3월'];
const REGIONS = ['서울', '경기', '부산', '대구', '기타'];
const M_COLORS = ['#1565C0', '#00695C', '#6A1B9A'];
function fillMonthSheet(sheet, name, colorHex) {
sheet.name(name);
sheet.options.sheetTabColor = colorHex;
spread.suspendPaint();
['지역', '매출', '비용', '이익'].forEach((h, c) => {
sheet.setValue(0, c, h);
sheet.getCell(0, c).backColor(colorHex).foreColor('#fff').font('bold 12px sans-serif');
sheet.setColumnWidth(c, c === 0 ? 70 : 110);
});
sheet.setRowHeight(0, 32);
REGIONS.forEach((region, r) => {
const sales = Math.round((Math.random() * 4000 + 1000) * 10000);
const cost = Math.round(sales * (0.35 + Math.random() * 0.2));
const profit = sales - cost;
sheet.setValue(r + 1, 0, region);
sheet.setValue(r + 1, 1, sales);
sheet.setValue(r + 1, 2, cost);
sheet.setValue(r + 1, 3, profit);
const rowBg = r % 2 === 0 ? '#FAFAFA' : '#FFFFFF';
[0, 1, 2, 3].forEach(c => sheet.getCell(r + 1, c).backColor(rowBg));
if (profit < 0) sheet.getCell(r + 1, 3).foreColor('#C62828');
});
const totalRow = REGIONS.length + 1;
sheet.setValue(totalRow, 0, '소계');
sheet.getCell(totalRow, 0).font('bold 12px sans-serif');
['B', 'C', 'D'].forEach((col, c) => {
sheet.setFormula(totalRow, c + 1, `=SUM(${col}2:${col}${totalRow})`);
sheet.getCell(totalRow, c + 1).backColor('#E3F2FD').font('bold 12px sans-serif');
});
spread.resumePaint();
}
function buildSummarySheet(totalSheet) {
totalSheet.name('분기합계');
totalSheet.options.sheetTabColor = '#37474F';
totalSheet.options.isProtected = true; // 수식 보호
spread.suspendPaint();
['지역', '1월', '2월', '3월', '분기합계'].forEach((h, c) => {
totalSheet.setValue(0, c, h);
totalSheet.getCell(0, c).backColor('#37474F').foreColor('#fff').font('bold 12px sans-serif');
totalSheet.setColumnWidth(c, c === 0 ? 70 : 105);
});
totalSheet.setRowHeight(0, 32);
REGIONS.forEach((region, r) => {
totalSheet.setValue(r + 1, 0, region);
MONTHS.forEach((month, m) => {
// 시트 간 수식으로 각 월 매출 참조
totalSheet.setFormula(r + 1, m + 1, `=${month}!B${r + 2}`);
});
// 분기합계 = 3개월 합산
totalSheet.setFormula(r + 1, 4, `=SUM(B${r + 2}:D${r + 2})`);
totalSheet.getCell(r + 1, 4).backColor('#FFF8E1').font('bold 11px sans-serif');
if (r % 2 === 0) [0,1,2,3].forEach(c => totalSheet.getCell(r+1,c).backColor('#FAFAFA'));
});
// 전체합계 행
const totalRow = REGIONS.length + 1;
totalSheet.setValue(totalRow, 0, '전체합계');
totalSheet.getCell(totalRow, 0).font('bold 13px sans-serif');
['B','C','D','E'].forEach((col, c) => {
totalSheet.setFormula(totalRow, c + 1, `=SUM(${col}2:${col}${totalRow})`);
totalSheet.getCell(totalRow, c + 1).backColor('#E8F5E9').font('bold 13px sans-serif');
});
spread.resumePaint();
}
// 초기 구성
MONTHS.forEach((name, idx) => {
const sheet = idx === 0 ? spread.getActiveSheet() : new GC.Spread.Sheets.Worksheet(name);
if (idx > 0) spread.addSheet(idx, sheet);
fillMonthSheet(sheet, name, M_COLORS[idx]);
});
const totalSheet = new GC.Spread.Sheets.Worksheet('분기합계');
spread.addSheet(3, totalSheet);
buildSummarySheet(totalSheet);
spread.setActiveSheetIndex(3);
// 월별 데이터 랜덤 갱신 → 시트 간 수식 자동 반영 확인
document.getElementById('btnUpdate')?.addEventListener('click', () => {
MONTHS.forEach((name, idx) => fillMonthSheet(spread.getSheet(idx), name, M_COLORS[idx]));
spread.setActiveSheetIndex(3);
});