JavaScript
const spread = new GC.Spread.Sheets.Workbook(document.getElementById('spreadContainer'));
spread.options.tabStripVisible = false;
const sheet = spread.getActiveSheet();
sheet.setRowCount(20);
sheet.setColumnCount(7);
// 타이틀 행 (셀 병합)
sheet.setValue(0, 0, '2024년도 세출예산 집행 현황');
sheet.addSpan(0, 0, 1, 7);
sheet.getCell(0, 0)
.backColor('#0D47A1').foreColor('#FFFFFF')
.fontWeight('bold').hAlign(GC.Spread.Sheets.HorizontalAlign.center);
sheet.setRowHeight(0, 44);
// 헤더 행
const headers = ['부서명', '예산액(천원)', '전월이월(천원)', '집행액(천원)', '잔액(천원)', '집행률(%)', '상태'];
headers.forEach((h, c) => {
sheet.setValue(1, c, h);
sheet.getCell(1, c)
.backColor('#1565C0').foreColor('#FFFFFF')
.fontWeight('bold').hAlign(GC.Spread.Sheets.HorizontalAlign.center);
sheet.setColumnWidth(c, c === 0 ? 110 : c === 6 ? 72 : 118);
});
sheet.setRowHeight(1, 34);
// 부서별 예산 데이터 (부서명, 예산액, 전월이월, 집행액)
const deptData = [
['총무과', 420000, 12000, 318500],
['기획예산과', 280000, 8000, 220800],
['주민복지과', 890000, 25000, 712400],
['건설도시과', 1350000, 48000, 486000],
['환경위생과', 560000, 15000, 398200],
['경제문화과', 340000, 9000, 275600],
['보건의료과', 720000, 22000, 612300],
];
deptData.forEach((row, r) => {
const ri = r + 2;
sheet.setValue(ri, 0, row[0]);
sheet.setValue(ri, 1, row[1]);
sheet.setValue(ri, 2, row[2]);
sheet.setValue(ri, 3, row[3]);
// 잔액 = 예산액 + 전월이월 - 집행액
sheet.setFormula(ri, 4, `=B${ri+1}+C${ri+1}-D${ri+1}`);
// 집행률 = 집행액 / (예산액 + 전월이월) * 100
sheet.setFormula(ri, 5, `=ROUND(D${ri+1}/(B${ri+1}+C${ri+1})*100,1)`);
[1,2,3,4].forEach(c => {
sheet.getCell(ri, c).formatter('#,##0').hAlign(GC.Spread.Sheets.HorizontalAlign.right);
});
sheet.getCell(ri, 5).formatter('0.0').hAlign(GC.Spread.Sheets.HorizontalAlign.center);
const total = row[1] + row[2];
const pct = Math.round(row[3] / total * 100);
const status = pct >= 75 ? '✅ 정상' : pct >= 55 ? '⚠ 주의' : '🔴 부진';
const rowBg = pct >= 75 ? '#F1F8E9' : pct >= 55 ? '#FFFDE7' : '#FFF3E0';
sheet.setValue(ri, 6, status);
sheet.getCell(ri, 6).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
for (let c = 0; c < 7; c++) sheet.getCell(ri, c).backColor(rowBg);
sheet.setRowHeight(ri, 30);
});
// 합계 행
const sumRow = deptData.length + 2;
sheet.setValue(sumRow, 0, '합 계');
['B','C','D','E'].forEach((col, i) => {
sheet.setFormula(sumRow, i + 1, `=SUM(${col}3:${col}${sumRow})`);
sheet.getCell(sumRow, i + 1).formatter('#,##0').hAlign(GC.Spread.Sheets.HorizontalAlign.right);
});
sheet.setFormula(sumRow, 5, `=ROUND(D${sumRow+1}/(B${sumRow+1}+C${sumRow+1})*100,1)`);
sheet.getCell(sumRow, 5).formatter('0.0').hAlign(GC.Spread.Sheets.HorizontalAlign.center);
sheet.setValue(sumRow, 6, '');
for (let c = 0; c < 7; c++) {
sheet.getCell(sumRow, c).backColor('#BBDEFB').fontWeight('bold');
}
sheet.setRowHeight(sumRow, 36);
// 조건부 서식: 집행률 컬럼에 3색 스케일 적용
const cfRanges = [new GC.Spread.Sheets.Range(2, 5, deptData.length, 1)];
sheet.conditionalFormats.add3ScaleRule(
GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number, 30, '#F44336',
GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number, 60, '#FFC107',
GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number, 90, '#4CAF50',
cfRanges
);