JavaScript
const spread = new GC.Spread.Sheets.Workbook(document.getElementById('spreadContainer'));
const sheet = spread.getActiveSheet();
spread.options.tabStripVisible = false;
sheet.suspendPaint();
const headers = ['공종', '예산금액(원)', '실집행금액(원)', '차이(원)', '집행률(%)'];
const widths = [165, 130, 135, 120, 100];
headers.forEach((h, c) => {
sheet.setValue(0, c, h);
sheet.setColumnWidth(c, widths[c]);
sheet.getCell(0, c).backColor('#263238').foreColor('#fff').font('bold 12px sans-serif').hAlign(1);
});
sheet.setRowHeight(0, 38);
const rows = [
['토공사', 15500000, 14200000],
['철근콘크리트공사', 34500000, 38200000],
['조적공사', 8000000, 7800000],
['미장공사', 7000000, 9500000],
['타일공사', 12000000, 11500000],
['도장공사', 6000000, 6850000],
['창호공사', 11000000, 10200000],
['전기·설비공사', 18000000, 17500000],
];
rows.forEach(([name, budget, actual], r) => {
const dr = r + 1;
sheet.setValue(dr, 0, name);
sheet.setValue(dr, 1, budget);
sheet.setValue(dr, 2, actual);
sheet.setFormula(dr, 3, '=C' + (dr + 1) + '-B' + (dr + 1));
sheet.setFormula(dr, 4, '=ROUND(C' + (dr + 1) + '/B' + (dr + 1) + '*100,1)');
sheet.getCell(dr, 1).formatter('#,##0');
sheet.getCell(dr, 2).formatter('#,##0');
sheet.getCell(dr, 3).formatter('#,##0');
sheet.getCell(dr, 4).formatter('0.0');
sheet.setRowHeight(dr, 34);
});
// 합계 행
const totalRow = rows.length + 1;
sheet.setValue(totalRow, 0, '총 합 계');
sheet.setFormula(totalRow, 1, '=SUM(B2:B' + (rows.length + 1) + ')');
sheet.setFormula(totalRow, 2, '=SUM(C2:C' + (rows.length + 1) + ')');
sheet.setFormula(totalRow, 3, '=C' + (totalRow + 1) + '-B' + (totalRow + 1));
sheet.setFormula(totalRow, 4, '=ROUND(C' + (totalRow + 1) + '/B' + (totalRow + 1) + '*100,1)');
[1,2,3].forEach(c => sheet.getCell(totalRow, c).formatter('#,##0').font('bold 12px sans-serif'));
sheet.getCell(totalRow, 4).formatter('0.0').font('bold 12px sans-serif');
for (let c = 0; c < 5; c++) sheet.getCell(totalRow, c).backColor('#ECEFF1').font('bold 12px sans-serif');
sheet.setRowHeight(totalRow, 38);
// 조건부 서식: 차이(D열) > 0 이면 빨강 강조
const CF = GC.Spread.Sheets.ConditionalFormatting;
const overRule = new CF.NormalConditionRule();
overRule.ruleType(CF.RuleType.cellValueRule);
overRule.operator(CF.ComparisonOperators.greaterThan);
overRule.value1(0);
overRule.ranges([new GC.Spread.Sheets.Range(1, 3, rows.length, 1)]);
const redStyle = new GC.Spread.Sheets.Style();
redStyle.backColor = '#FFCDD2';
redStyle.foreColor = '#B71C1C';
overRule.style(redStyle);
sheet.conditionalFormats.addRule(overRule);
// 조건부 서식: 집행률(E열) > 100 이면 굵은 빨강 텍스트
const over100 = new CF.NormalConditionRule();
over100.ruleType(CF.RuleType.cellValueRule);
over100.operator(CF.ComparisonOperators.greaterThan);
over100.value1(100);
over100.ranges([new GC.Spread.Sheets.Range(1, 4, rows.length, 1)]);
const boldRed = new GC.Spread.Sheets.Style();
boldRed.foreColor = '#B71C1C';
boldRed.font = 'bold 12px sans-serif';
over100.style(boldRed);
sheet.conditionalFormats.addRule(over100);
// 데이터 바: 집행률(E열) 시각화 (0~130% 범위)
sheet.conditionalFormats.addDataBarRule(
CF.ScaleValueType.number, 0,
CF.ScaleValueType.number, 130,
'#42A5F5',
[new GC.Spread.Sheets.Range(1, 4, rows.length, 1)]
);
sheet.resumePaint();