const spread = new GC.Spread.Sheets.Workbook(document.getElementById('spreadContainer'));
spread.options.tabStripVisible = false;
// 시트 1: 전표 데이터
const jvSheet = spread.getActiveSheet();
jvSheet.name('전표');
const jvHeaders = ['전표번호', '계정과목', '차변', '대변'];
jvHeaders.forEach((h, c) => {
jvSheet.setValue(0, c, h);
jvSheet.getCell(0, c).backColor('#1B5E20').foreColor('#fff').font('bold 12px sans-serif').hAlign(GC.Spread.Sheets.HorizontalAlign.center);
jvSheet.setColumnWidth(c, c === 1 ? 150 : 100);
});
const jvData = [
['JV-001','급여',12500000,0],['JV-002','급여',0,12500000],
['JV-003','복리후생비',320000,0],['JV-004','복리후생비',0,320000],
['JV-005','여비교통비',85000,0],['JV-006','통신비',55000,0],
['JV-007','소모품비',215000,0],['JV-008','광고선전비',480000,0],
['JV-009','여비교통비',0,85000],['JV-010','통신비',0,55000],
['JV-011','소모품비',0,215000],['JV-012','광고선전비',0,480000],
];
jvData.forEach((row, r) => {
row.forEach((v, c) => {
jvSheet.setValue(r + 1, c, v);
if (c >= 2) jvSheet.getCell(r + 1, c).formatter('#,##0');
});
});
// 시트 2: 합계잔액시산표
const tbSheet = new GC.Spread.Sheets.Worksheet('시산표');
spread.addSheet(1, tbSheet);
const tbHeaders = ['계정과목', '합계차변', '합계대변', '잔액(차)', '잔액(대)'];
tbHeaders.forEach((h, c) => {
tbSheet.setValue(0, c, h);
tbSheet.getCell(0, c).backColor('#0D47A1').foreColor('#fff').font('bold 12px sans-serif').hAlign(GC.Spread.Sheets.HorizontalAlign.center);
tbSheet.setColumnWidth(c, c === 0 ? 140 : 120);
});
tbSheet.setRowHeight(0, 36);
const accounts = ['급여','복리후생비','여비교통비','통신비','소모품비','광고선전비'];
accounts.forEach((acc, r) => {
tbSheet.setValue(r + 1, 0, acc);
// SUMIF로 전표 시트에서 집계
tbSheet.setFormula(r + 1, 1, `=SUMIF(전표!B:B,"${acc}",전표!C:C)`);
tbSheet.setFormula(r + 1, 2, `=SUMIF(전표!B:B,"${acc}",전표!D:D)`);
tbSheet.setFormula(r + 1, 3, `=IF(B${r+2}>C${r+2},B${r+2}-C${r+2},0)`);
tbSheet.setFormula(r + 1, 4, `=IF(C${r+2}>B${r+2},C${r+2}-B${r+2},0)`);
for (let c = 1; c <= 4; c++) tbSheet.getCell(r + 1, c).formatter('#,##0').hAlign(GC.Spread.Sheets.HorizontalAlign.right);
for (let c = 0; c < 5; c++) tbSheet.getCell(r + 1, c).backColor(r % 2 === 0 ? '#E3F2FD' : '#FFFFFF');
});
// 합계 행
const tot = accounts.length + 1;
tbSheet.setValue(tot, 0, '합 계');
for (let c = 1; c <= 4; c++) {
tbSheet.setFormula(tot, c, `=SUM(${String.fromCharCode(65+c)}2:${String.fromCharCode(65+c)}${tot})`);
tbSheet.getCell(tot, c).formatter('#,##0').hAlign(GC.Spread.Sheets.HorizontalAlign.right).font('bold 12px sans-serif').backColor('#BBDEFB');
}
tbSheet.getCell(tot, 0).font('bold 12px sans-serif').backColor('#BBDEFB');
tbSheet.setRowHeight(tot, 36);
spread.setActiveSheetIndex(1);