path: root/lib/spread-js/fns.ts

'use client'

import GC from "@mescius/spread-sheets";
import { Spread } from "@/types/spread-js";

//시트 추가
export const addSheet = (spread: Spread | null): void => {
  if (!spread) return;

  const activeIndex = spread.getActiveSheetIndex();

  if (activeIndex >= 0) {
    spread.addSheet(activeIndex + 1);
    spread.setActiveSheetIndex(activeIndex + 1);
  } else {
    spread.addSheet(0);
    spread.setActiveSheetIndex(0);
  }
};

//시트 제거
export const removeSheet = (spread: Spread | null): void => {
  if (!spread) return;

  if (spread.getSheetCount() > 0) {
    spread.removeSheet(spread.getActiveSheetIndex());
  }
};

//전체 시트 제거
export const clearSheet = (spread: Spread | null) => {
  if (!spread) return;

  spread.clearSheets();
};

//Table Sheet 추가
export const createTableSheet = (spread: Spread | null) => {
  if (!spread) return;

  const activeIndex = spread.getActiveSheetIndex();

  if (activeIndex >= 0) {
    spread.addSheet(
      activeIndex + 1,
      new GC.Spread.Sheets.Worksheet(`Table Sheet ${activeIndex + 1}`)
    );
    spread.setActiveSheetIndex(activeIndex + 1);
  } else {
    spread.addSheet(0, new GC.Spread.Sheets.Worksheet(`Table Sheet 0`));
    spread.setActiveSheetIndex(0);
  }

  const sheet = spread.getActiveSheet();
};

/**
 * Sample Report Form를 만드는 함수
 * @param spread
 * @returns
 */
export const createSampleReportForm = (spread: Spread | null) => {
  if (!spread) return;

  const sheet = spread.getActiveSheet();

  sheet.reset();

  sheet.suspendPaint();

  sheet.name("Inspection Report");

  let bindingPathCellType = new BindingPathCellType();

  //기본 테두리 설정
  const defaultLineStyle = GC.Spread.Sheets.LineStyle.thin;
  const defaultLineBorder = new GC.Spread.Sheets.LineBorder(
    "black",
    defaultLineStyle
  );

  //행 높이 설정
  sheet.setRowHeight(1, 50);

  //Cell Data 삽입
  sheet
    .getCell(1, 1)
    .value("TEST")
    .vAlign(GC.Spread.Sheets.VerticalAlign.bottom)
    .hAlign(GC.Spread.Sheets.HorizontalAlign.left);

  //Cell Data Binding

  sheet
    .getCell(1, 2)
    .bindingPath("test")
    .cellType(bindingPathCellType)
    .vAlign(GC.Spread.Sheets.VerticalAlign.bottom);

  sheet
    .getCell(1, 7)
    .bindingPath("test2")
    .cellType(bindingPathCellType)
    .vAlign(GC.Spread.Sheets.VerticalAlign.bottom);

  const bindingData = { test: "testData", test2: "testData2" };
  const bindingDataSource = new GC.Spread.Sheets.Bindings.CellBindingSource(
    bindingData
  );

  sheet.setDataSource(bindingDataSource);

  //Cell Merge
  sheet.addSpan(1, 2, 1, 4);

  //Cell Border Setting
  sheet
    .getRange(1, 1, 1, 5)
    .setBorder(defaultLineBorder, { outline: true, inside: true });

  //Table 생성
  const tableFields = [
    { field: "id", name: "ID" },
    { field: "name", name: "DESCRIPTION" },
    { field: "date", name: "Date", formatter: "dd/mm/yyyy" },
    { field: "qty", name: "QUANTITY" },
    { field: "amount", name: "AMOUNT" },
  ];

  const table = sheet.tables.add("sampleTable", 3, 1, 1, tableFields.length);

  const tableColumns = tableFields.map((c, i) => {
    const { field, name, formatter } = c;
    const tableColumn = new GC.Spread.Sheets.Tables.TableColumn(i);

    tableColumn.name(name);
    tableColumn.dataField(field);

    if (formatter) {
      tableColumn.formatter(formatter);
    }

    return tableColumn;
  });
  table.autoGenerateColumns(false);
  table.bindColumns(tableColumns);

  table.bindingPath("tableData");

  //이미 있는 속성으로 테이블 스타일을 적용 시키는 법
  // table.style(GC.Spread.Sheets.Tables.TableThemes.light1.name());

  //테이블 속성을 임의로 만들어서 적용 시키는 법 1
  const tableBorder = new GC.Spread.Sheets.LineBorder(
    "black",
    defaultLineStyle
  );

  const baseTableStyleInfo = new GC.Spread.Sheets.Tables.TableStyle(
    "#fff",
    "#000000",
    undefined,
    tableBorder,
    tableBorder,
    tableBorder,
    tableBorder,
    tableBorder,
    tableBorder
  );

  const headerStyle = new GC.Spread.Sheets.Tables.TableStyle(
    "#F9FAFB",
    undefined,
    undefined,
    undefined,
    undefined,
    undefined,
    undefined,
    undefined,
    undefined,
    undefined,
    undefined,
    "900",
    "14px"
  );

  var customTableStyle = new GC.Spread.Sheets.Tables.TableTheme();

  // customTableStyle.headerRowStyle(tableStyleInfo);
  customTableStyle.wholeTableStyle(baseTableStyleInfo);

  customTableStyle.headerRowStyle(headerStyle);
  customTableStyle.footerRowStyle(headerStyle);

  // customTableStyle.wholeTableStyle(new GC.Spread.Sheets.Tables.TableStyle('#e0f2f1'));
  // customTableStyle.headerRowStyle(new GC.Spread.Sheets.Tables.TableStyle('#26a69a', '#fff'));
  // customTableStyle.firstRowStripStyle(new GC.Spread.Sheets.Tables.TableStyle('#b2dfdb'));
  // customTableStyle.firstColumnStripStyle(new GC.Spread.Sheets.Tables.TableStyle('#b2dfdb'));
  // customTableStyle.footerRowStyle(new GC.Spread.Sheets.Tables.TableStyle('#26a69a', '#fff'));
  // customTableStyle.highlightFirstColumnStyle(new GC.Spread.Sheets.Tables.TableStyle('#26a69a', '#fff'));
  // customTableStyle.highlightLastColumnStyle(new GC.Spread.Sheets.Tables.TableStyle('#26a69a', '#fff'));

  customTableStyle.name("customTableStyle1");

  spread.customTableThemes.add(customTableStyle);

  table.style("customTableStyle1");

  table.showFooter(true, false /*isFooterInserted*/);
  table.useFooterDropDownList(true);

  sheet.resumePaint();
};

export const setSampleReportData = (
  spread: Spread | null,
  reportData: { [key: string]: any } = {
    test: "test",
    tableData: [
      {
        id: "1",
        name: "2",
        date: new Date(),
        qty: 3,
        amount: 4000,
      },
      {
        id: "1",
        name: "2",
        date: new Date(),
        qty: 3,
        amount: 4000,
      },
    ],
  }
) => {
  if (!spread) return;

  const sheet = spread.getActiveSheet();

  const dataSource = new GC.Spread.Sheets.Bindings.CellBindingSource(
    reportData
  );

  sheet.setDataSource(dataSource);
};

export const exportJSON = (spread: Spread | null) => {
  if (!spread) return;

  const spreadJSON = spread.toJSON()

  const jsonString = JSON.stringify(spreadJSON, null, 2); // 예쁘게 포맷
  const blob = new Blob([jsonString], { type: "application/json" });

  const url = URL.createObjectURL(blob);
  const a = document.createElement("a");

  a.href = url;
  a.download = "spreadjs-form.json"; // 저장될 파일명
  document.body.appendChild(a);
  a.click();
  document.body.removeChild(a);

  URL.revokeObjectURL(url); // 메모리 해제
};

export function handleFileImport(spread: Spread) {
  const input = document.createElement("input");
  input.type = "file";
  input.accept = ".json";

  input.onchange = async (e: any) => {
    const file = e.target.files[0];
    if (!file) return;

    const text = await file.text();
    const json = JSON.parse(text);

    spread.fromJSON(json)

    // const targetSheet = spread.getSheetFromName("Inspection Report");

    const sheets = spread.sheets

    sheets.forEach(sheet => {
      const sheetName = sheet.name()
      const bindingData = { test: "testData", test2: "testData2" };
      const bindingDataSource = new GC.Spread.Sheets.Bindings.CellBindingSource(
      bindingData
    );

    
  
    sheet.setDataSource(bindingDataSource);
    })
    
  };

  input.click();
}


class BindingPathCellType extends GC.Spread.Sheets.CellTypes.Text {
  constructor() {
    super();
  }

  paint(
    ctx: any,
    value: any,
    x: number,
    y: number,
    w: number,
    h: number,
    style: any,
    context: any
  ) {
    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);
  }
}

type TableStyle = {
  backColor?:
    | string
    | GC.Spread.Sheets.IPatternFill
    | GC.Spread.Sheets.IGradientFill
    | GC.Spread.Sheets.IGradientPathFill;
  foreColor?: string;
  font?: string;
  borderLeft?: GC.Spread.Sheets.LineBorder;
  borderTop?: GC.Spread.Sheets.LineBorder;
  borderRight?: GC.Spread.Sheets.LineBorder;
  borderBottom?: GC.Spread.Sheets.LineBorder;
  borderHorizontal?: GC.Spread.Sheets.LineBorder;
  borderVertical?: GC.Spread.Sheets.LineBorder;
  textDecoration?: GC.Spread.Sheets.TextDecorationType;
  fontStyle?: string;
  fontWeight?: string;
  fontSize?: string;
  fontFamily?: string;
};