JavaScript
const spread = new GC.Spread.Sheets.Workbook(document.getElementById('spreadContainer'));
spread.options.tabStripVisible = true;
// ── 로딩 바 헬퍼 ─────────────────────────────────────────────────────────────
const loadingEl = document.getElementById('loadingBar');
const fillEl = document.getElementById('loadingFill');
const msgEl = document.getElementById('loadingMsg');
const pctEl = document.getElementById('loadingPct');
function setProgress(pct, msg) {
if (fillEl) fillEl.style.width = pct + '%';
if (pctEl) pctEl.textContent = pct + '%';
if (msgEl && msg) msgEl.textContent = msg;
}
// ── 시트 1: 거래 데이터 (Data) ──────────────────────────────────────────────
const dataSheet = spread.getActiveSheet();
dataSheet.name('Data');
const cols = ['거래번호', '거래일자', '계좌번호', '거래금액', '잔액', '유형'];
cols.forEach((h, c) => {
dataSheet.setValue(0, c, h);
dataSheet.getCell(0, c)
.backColor('#0D47A1').foreColor('#ffffff')
.font('bold 12px sans-serif')
.hAlign(GC.Spread.Sheets.HorizontalAlign.center);
dataSheet.setColumnWidth(c, [90, 100, 130, 110, 120, 70][c]);
});
dataSheet.setRowHeight(0, 32);
dataSheet.frozenRowCount(1);
const ROW_COUNT = 100000;
const END_ROW = ROW_COUNT + 1;
// ── Step 1: 데이터 생성 ───────────────────────────────────────────────────────
setProgress(5, '10만 행 데이터 생성 중...');
setTimeout(function() {
const types = ['입금', '출금', '이체'];
const data = [];
let balance = 50000000;
for (let r = 0; r < ROW_COUNT; r++) {
const type = types[r % 3];
const amount = Math.floor(Math.random() * 500000) + 10000;
balance += type === '입금' ? amount : -amount;
const dateObj = new Date(2024, 0, 1);
dateObj.setDate(dateObj.getDate() + Math.floor(r / 10));
const dateStr = dateObj.getFullYear() + '-' +
String(dateObj.getMonth() + 1).padStart(2, '0') + '-' +
String(dateObj.getDate()).padStart(2, '0');
data.push([
'TXN-' + String(r + 1).padStart(7, '0'),
dateStr,
'110-' + String(Math.floor(r / 100)).padStart(3, '0') + '-' + String(r % 100).padStart(6, '0'),
amount,
Math.max(0, balance),
type
]);
}
setProgress(50, 'SpreadJS 가상화 렌더링 중...');
// ── Step 2: SpreadJS 렌더링 ────────────────────────────────────────────────
setTimeout(function() {
const t0 = performance.now();
spread.suspendPaint();
dataSheet.setRowCount(ROW_COUNT + 2);
dataSheet.setArray(1, 0, data);
dataSheet.getRange(1, 3, ROW_COUNT, 2).formatter('#,##0');
for (let r = 0; r < 2000; r++) {
const clr = data[r][5] === '입금' ? '#1B5E20' : data[r][5] === '출금' ? '#B71C1C' : '#0D47A1';
dataSheet.getCell(r + 1, 5).foreColor(clr).font('bold 11px sans-serif')
.hAlign(GC.Spread.Sheets.HorizontalAlign.center);
}
setProgress(80, 'Excel 수식(SUMIF/COUNTIF/AVERAGE) 집계 중...');
// ── 시트 2: Excel 수식 집계 (Summary) ──────────────────────────────────
spread.addSheet(1);
const summSheet = spread.getSheet(1);
summSheet.name('Summary');
summSheet.addSpan(0, 0, 1, 3);
summSheet.setValue(0, 0, '📊 10만 행 Excel 수식 집계 — Data 시트 참조');
summSheet.getCell(0, 0)
.backColor('#0D47A1').foreColor('#fff')
.font('bold 14px sans-serif')
.hAlign(GC.Spread.Sheets.HorizontalAlign.center);
summSheet.setRowHeight(0, 44);
['집계 항목', '결과', '사용 수식'].forEach(function(h, c) {
summSheet.setValue(1, c, h);
summSheet.getCell(1, c)
.backColor('#1565C0').foreColor('#fff')
.font('bold 12px sans-serif')
.hAlign(GC.Spread.Sheets.HorizontalAlign.center);
});
summSheet.setColumnWidth(0, 160);
summSheet.setColumnWidth(1, 180);
summSheet.setColumnWidth(2, 390);
summSheet.setRowHeight(1, 30);
const items = [
['총 거래 건수', '=COUNTA(Data!A2:A' + END_ROW + ')', 'COUNTA(Data!A2:A' + END_ROW + ')', '#,##0"건"'],
['입금 건수', '=COUNTIF(Data!F2:F' + END_ROW + ',"입금")', 'COUNTIF(Data!F2:F' + END_ROW + ',"입금")', '#,##0"건"'],
['출금 건수', '=COUNTIF(Data!F2:F' + END_ROW + ',"출금")', 'COUNTIF(Data!F2:F' + END_ROW + ',"출금")', '#,##0"건"'],
['이체 건수', '=COUNTIF(Data!F2:F' + END_ROW + ',"이체")', 'COUNTIF(Data!F2:F' + END_ROW + ',"이체")', '#,##0"건"'],
['총 입금액', '=SUMIF(Data!F2:F' + END_ROW + ',"입금",Data!D2:D' + END_ROW + ')', 'SUMIF(...,"입금",Data!D2:D' + END_ROW + ')', '#,##0"원"'],
['총 출금액', '=SUMIF(Data!F2:F' + END_ROW + ',"출금",Data!D2:D' + END_ROW + ')', 'SUMIF(...,"출금",Data!D2:D' + END_ROW + ')', '#,##0"원"'],
['평균 거래금액', '=AVERAGE(Data!D2:D' + END_ROW + ')', 'AVERAGE(Data!D2:D' + END_ROW + ')', '#,##0"원"'],
['최대 잔액', '=MAX(Data!E2:E' + END_ROW + ')', 'MAX(Data!E2:E' + END_ROW + ')', '#,##0"원"'],
];
items.forEach(function(item, i) {
const label = item[0], formula = item[1], formulaText = item[2], fmt = item[3];
const row = i + 2;
summSheet.setValue(row, 0, label);
summSheet.setFormula(row, 1, formula);
summSheet.getCell(row, 1).formatter(fmt).font('bold 12px sans-serif')
.foreColor('#0056B3').hAlign(GC.Spread.Sheets.HorizontalAlign.right);
summSheet.setValue(row, 2, formulaText);
summSheet.getCell(row, 2).font('11px monospace').foreColor('#555');
var bg = i % 2 === 0 ? '#F5F9FF' : '#FFFFFF';
[0, 1, 2].forEach(function(c) { summSheet.getCell(row, c).backColor(bg); });
summSheet.setRowHeight(row, 28);
});
spread.resumePaint();
const elapsed = performance.now() - t0;
const perfRow = items.length + 3;
summSheet.addSpan(perfRow, 0, 1, 3);
summSheet.setValue(perfRow, 0,
'⚡ ' + ROW_COUNT.toLocaleString() + '행 로드 + SUMIF/COUNTIF/AVERAGE 즉시 계산 완료 — ' +
elapsed.toFixed(0) + 'ms | SpreadJS는 100만 행도 지원합니다');
summSheet.getCell(perfRow, 0)
.backColor('#1B5E20').foreColor('#fff')
.font('bold 12px sans-serif')
.hAlign(GC.Spread.Sheets.HorizontalAlign.center);
summSheet.setRowHeight(perfRow, 34);
spread.setActiveSheetIndex(1);
// ── 로딩 바 완료 애니메이션 ────────────────────────────────────────────
setProgress(100, '완료! (' + elapsed.toFixed(0) + 'ms)');
setTimeout(function() {
if (loadingEl) {
loadingEl.style.transition = 'opacity 0.5s';
loadingEl.style.opacity = '0';
setTimeout(function() { loadingEl.style.display = 'none'; }, 500);
}
}, 800);
}, 30);
}, 30);