path: root/components/spread-js/dataBinding.tsx

// @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;
};