diff options
Diffstat (limited to 'components/spread-js/dataBinding.tsx')
| -rw-r--r-- | components/spread-js/dataBinding.tsx | 491 |
1 files changed, 491 insertions, 0 deletions
diff --git a/components/spread-js/dataBinding.tsx b/components/spread-js/dataBinding.tsx new file mode 100644 index 00000000..52171dbf --- /dev/null +++ b/components/spread-js/dataBinding.tsx @@ -0,0 +1,491 @@ +// @ts-nocheck +"use client"; + +import * as React from "react"; +import GC from "@mescius/spread-sheets"; +import "@mescius/spread-sheets-resources-ko"; +import { SpreadSheets } from "@mescius/spread-sheets-react"; + +GC.Spread.Common.CultureManager.culture("ko-kr"); +GC.Spread.Sheets.LicenseKey = process.env.NEXT_PUBLIC_SPREADJS_KEY +GC.Spread.Sheets.Designer.LicenseKey = process.env.NEXT_PUBLIC_SPREADJS_KEY + +const DataBinding = () => { + let spread = null; + let dataSource1 = null; + let dataSource2 = null; + + const changeDataSource = () => { + if (!spread) return; + let sheet = spread.getActiveSheet(); + if (sheet.getDataSource() === dataSource1) { + sheet.setDataSource(dataSource2); + } else { + sheet.setDataSource(dataSource1); + } + }; + const initSpread = (currSpread) => { + spread = currSpread; + + if(currSpread){ + let company1 = new Company( + "Baidu", + null, + "We know everything!", + "Beijing 1st road", + "Beijing", + "010-12345678", + "baidu@baidu.com" + ), + company2 = new Company( + "Tecent", + null, + "We have everything!", + "Shenzhen 2st road", + "Shenzhen", + "0755-12345678", + "tecent@qq.com" + ), + company3 = new Company( + "Alibaba", + null, + "We sale everything!", + "Hangzhou 3rd road", + "Hangzhou", + "0571-12345678", + "alibaba@alibaba.com" + ), + customer1 = new Customer("A1", "employee 1", company2), + customer2 = new Customer("A2", "employee 2", company3), + records1 = [ + new Record("Finance charge on overdue balance at 1.5%", 1, 150), + new Record("Invoice #100 for $1000 on 2014/1/1", 1, 150), + ], + records2 = [ + new Record("Purchase server device", 2, 15000), + new Record("Company travel", 100, 1500), + new Record("Company Dinner", 100, 200), + new Record("Company Dinner", 100, 200), + new Record("Company Dinner", 100, 200), + new Record("Company Dinner", 100, 200), + new Record("Company Dinner", 100, 200), + new Record("Company Dinner", 100, 200), + ], + invoice1 = new Invoice( + company1, + "00001", + new Date(2014, 0, 1), + customer1, + customer1, + records1 + ), + invoice2 = new Invoice( + company2, + "00002", + new Date(2014, 6, 6), + customer2, + customer2, + records2 + ); + + dataSource1 = new GC.Spread.Sheets.Bindings.CellBindingSource(invoice1); + dataSource2 = new GC.Spread.Sheets.Bindings.CellBindingSource(invoice2); + + //Get sheet instance + spread.suspendPaint(); + let sheet = spread.sheets[0]; + sheet.name("FINANCE CHARGE"); + + //Set value or bindingPath and style + let bindingPathCellType = new BindingPathCellType(); + sheet + .getCell(1, 2) + .bindingPath("company.slogan") + .cellType(bindingPathCellType) + .vAlign(GC.Spread.Sheets.VerticalAlign.bottom); + sheet + .getCell(1, 4) + .value("INVOICE") + .foreColor("#58B6C0") + .font("33px Arial"); + sheet + .getCell(3, 1) + .bindingPath("company.name") + .cellType(bindingPathCellType) + .foreColor("#58B6C0") + .font("bold 20px Arial"); + sheet + .getCell(5, 1) + .bindingPath("company.address") + .cellType(bindingPathCellType); + sheet.getCell(5, 3).value("INVOICE NO.").font("bold 15px Arial"); + sheet.getCell(5, 4).bindingPath("number").cellType(bindingPathCellType); + sheet + .getCell(6, 1) + .bindingPath("company.city") + .cellType(bindingPathCellType); + sheet.getCell(6, 3).value("DATE").font("bold 15px Arial"); + sheet + .getCell(6, 4) + .bindingPath("date") + .cellType(bindingPathCellType) + .formatter("MM/dd/yyyy") + .hAlign(GC.Spread.Sheets.HorizontalAlign.left); + sheet + .getCell(7, 1) + .bindingPath("company.phone") + .cellType(bindingPathCellType); + sheet.getCell(7, 3).value("CUSTOMER ID").font("bold 15px Arial"); + sheet + .getCell(7, 4) + .bindingPath("customer.id") + .cellType(bindingPathCellType); + sheet + .getCell(8, 1) + .bindingPath("company.email") + .cellType(bindingPathCellType); + sheet.getCell(10, 1).value("TO").font("bold 15px Arial"); + sheet.getCell(10, 3).value("SHIP TO").font("bold 15px Arial"); + sheet + .getCell(11, 1) + .bindingPath("customer.name") + .cellType(bindingPathCellType) + .textIndent(10); + sheet + .getCell(12, 1) + .bindingPath("customer.company.name") + .cellType(bindingPathCellType) + .textIndent(10); + sheet + .getCell(13, 1) + .bindingPath("customer.company.address") + .cellType(bindingPathCellType) + .textIndent(10); + sheet + .getCell(14, 1) + .bindingPath("customer.company.city") + .cellType(bindingPathCellType) + .textIndent(10); + sheet + .getCell(15, 1) + .bindingPath("customer.company.phone") + .cellType(bindingPathCellType) + .textIndent(10); + sheet + .getCell(11, 4) + .bindingPath("receiverCustomer.name") + .cellType(bindingPathCellType); + sheet + .getCell(12, 4) + .bindingPath("receiverCustomer.company.name") + .cellType(bindingPathCellType); + sheet + .getCell(13, 4) + .bindingPath("receiverCustomer.company.address") + .cellType(bindingPathCellType); + sheet + .getCell(14, 4) + .bindingPath("receiverCustomer.company.city") + .cellType(bindingPathCellType); + sheet + .getCell(15, 4) + .bindingPath("receiverCustomer.company.phone") + .cellType(bindingPathCellType); + sheet.addSpan(17, 1, 1, 2); + sheet + .getCell(17, 1) + .value("JOB") + .foreColor("#58B6C0") + .font("bold 12px Arial"); + sheet.addSpan(17, 3, 1, 2); + sheet + .getCell(17, 3) + .value("PAYMENT TERMS") + .foreColor("#58B6C0") + .font("bold 12px Arial"); + sheet.addSpan(18, 1, 1, 2); + sheet.getCell(18, 1).backColor("#DDF0F2"); + sheet.addSpan(18, 3, 1, 2); + sheet + .getCell(18, 3) + .value("Due on receipt") + .backColor("#DDF0F2") + .foreColor("#58B6C0") + .font("12px Arial"); + sheet + .getRange(17, 1, 2, 4) + .setBorder( + new GC.Spread.Sheets.LineBorder( + "#58B6C0", + GC.Spread.Sheets.LineStyle.thin + ), + { top: true, bottom: true, innerHorizontal: true } + ); + let table = sheet.tables.add( + "tableRecordds", + 20, + 1, + 1, + 4, + GC.Spread.Sheets.Tables.TableThemes.light6 + ); + table.autoGenerateColumns(false); + let tableColumn1 = new GC.Spread.Sheets.Tables.TableColumn(0); + tableColumn1.name("DESCRIPTION"); + tableColumn1.dataField("description"); + let tableColumn2 = new GC.Spread.Sheets.Tables.TableColumn(1); + tableColumn2.name("QUANTITY"); + tableColumn2.dataField("quantity"); + let tableColumn3 = new GC.Spread.Sheets.Tables.TableColumn(2); + tableColumn3.name("AMOUNT"); + tableColumn3.dataField("amount"); + table.bindColumns([tableColumn1, tableColumn2, tableColumn3]); + table.bindingPath("records"); + table.showFooter(true); + table.setColumnName(3, "TOTAL"); + table.setColumnValue(2, "TOTAL DUE"); + table.setColumnDataFormula(3, "=[@QUANTITY]*[@AMOUNT]"); + table.setColumnFormula(3, "=SUBTOTAL(109,[TOTAL])"); + sheet + .getCell(26, 1) + .formula( + '="Make all checks payable to "&B4&". THANK YOU FOR YOUR BUSINESS!"' + ) + .foreColor("gray") + .font("italic 14px Arial"); + sheet.options.allowCellOverflow = true; + //Adjust row height and column width + sheet.setColumnWidth(0, 5); + sheet.setColumnWidth(1, 300); + sheet.setColumnWidth(2, 115); + sheet.setColumnWidth(3, 125); + sheet.setColumnWidth(4, 155); + sheet.setRowHeight(0, 5); + sheet.setRowHeight(1, 40); + sheet.setRowHeight(2, 10); + sheet.setRowHeight(3, 28); + sheet.setRowHeight(17, 0); + sheet.setRowHeight(18, 0); + sheet.setRowHeight(19, 0); + sheet.setRowHeight(25, 10); + sheet.options.gridline = { + showHorizontalGridline: false, + showVerticalGridline: false, + }; + + spread.resumePaint(); + } + }; + + return ( + <div className="sample-tutorial"> + <div className="sample-spreadsheets"> + <SpreadSheets workbookInitialized={(spread) => initSpread(spread)} /> + </div> + <div className="options-container"> + <div className="option-row"> + <label style={{ backgroundColor: "#F4F8EB" }}> + Click this button to set the data source for the data-bound table. + </label> + </div> + <input + type="button" + onClick={() => { + changeDataSource(); + }} + id="changeDataSource" + value="Set DataSource" + title="Toggle table binding's data source" + /> + <div className="option-row"> + <label style={{ backgroundColor: "#F4F8EB" }}> + 데이터 바인딩 위치 확인 + </label> + </div> + <input + type="button" + onClick={() => { + extractBindingPaths(spread); + }} + id="changeDataSource" + value="데이터 바인딩 위치 확인" + title="Toggle table binding's data source" + /> + <div className="option-row"> + <label style={{ backgroundColor: "#F4F8EB" }}> + 테이블 데이터 바인딩 위치 확인 + </label> + </div> + <input + type="button" + onClick={() => { + extractTableBindingPaths(spread); + }} + id="changeDataSource" + value="테이블 데이터 바인딩 위치 확인" + title="Toggle table binding's data source" + /> + <div className="option-row"> + <label style={{ backgroundColor: "#F4F8EB" }}>값 가져오기</label> + </div> + <input + type="button" + onClick={() => { + extractAllCellsWithValues(spread); + }} + id="changeDataSource" + value="테이블 데이터 바인딩 위치 확인" + title="Toggle table binding's data source" + /> + </div> + </div> + ); +}; + +export default DataBinding; + +class Company { + constructor( + public name?: string | null, + public logo?: string | null, + public slogan?: string | null, + public address?: string | null, + public city?: string | null, + public phone?: string | null, + public email?: string | null + ) {} +} + +class Customer { + constructor( + public id?: string, + public name?: string, + public company?: Company + ) {} +} + +class Record { + constructor( + public description?: string, + public quantity?: number, + public amount?: number + ) {} +} + +class Invoice { + constructor( + public company: Company, + public number: string, + public date: Date, + public customer: Customer, + public receiverCustomer: Customer, + public records: Record[] | [] + ) {} +} + +class BindingPathCellType extends GC.Spread.Sheets.CellTypes.Text { + constructor() { + super(); + } + + paint(ctx, value, x, y, w, h, style, context) { + if (value === null || value === undefined) { + let sheet = context.sheet, + row = context.row, + col = context.col; + if (sheet && (row === 0 || !!row) && (col === 0 || !!col)) { + let bindingPath = sheet.getBindingPath(context.row, context.col); + if (bindingPath) { + value = "[" + bindingPath + "]"; + } + } + } + super.paint(ctx, value, x, y, w, h, style, context); + } +} + +const extractBindingPaths = (spread) => { + let sheet = spread.getActiveSheet(); + let bindingPaths = []; + + for (let row = 0; row < sheet.getRowCount(); row++) { + for (let col = 0; col < sheet.getColumnCount(); col++) { + let bindingPath = sheet.getBindingPath(row, col); + if (bindingPath) { + bindingPaths.push({ row, col, bindingPath }); + } + } + } + + console.log( + "Extracted Binding Paths:", + JSON.stringify(bindingPaths, null, 2) + ); + return bindingPaths; +}; + +const extractTableBindingPaths = (spread) => { + let sheet = spread.getActiveSheet(); + let tables = sheet.tables.all(); + let tableBindings = []; + + tables.forEach((table) => { + let range = table.range(); // 테이블의 위치 정보 가져오기 + let tableInfo = { + tableName: table.name(), + bindingPath: table.bindingPath(), + tablePosition: { + startRow: range.row, + startCol: range.col, + rowCount: range.rowCount, + colCount: range.colCount, + }, + columns: [], + }; + + // 🔹 컬럼 바인딩 정보 직접 추출 (bindColumns() 사용 안함) + for (let c = range.col; c < range.col + range.colCount; c++) { + let bindingPath = sheet.getBindingPath(range.row, c); + let headerValue = sheet.getValue(range.row, c); + + if (bindingPath) { + tableInfo.columns.push({ + colIndex: c, + columnName: headerValue || `Column ${c - range.col + 1}`, + dataField: bindingPath, + }); + } + } + + tableBindings.push(tableInfo); + }); + + console.log( + "📌 바인딩된 위치 및 컬럼 정보:", + JSON.stringify(tableBindings, null, 2) + ); + return tableBindings; +}; + +const extractAllCellsWithValues = (spread) => { + let sheet = spread.getActiveSheet(); + let rowCount = sheet.getRowCount(); + let colCount = sheet.getColumnCount(); + let cellData = []; + + for (let row = 0; row < rowCount; row++) { + for (let col = 0; col < colCount; col++) { + let value = sheet.getValue(row, col); + if (value !== null && value !== undefined && value !== "") { + cellData.push({ + row: row, + col: col, + value: value, + }); + } + } + } + + console.log("📌 모든 값이 있는 셀 정보:", JSON.stringify(cellData, null, 2)); + return cellData; +}; |