How do I detemine a table column definition from a column index?

How do I detemine a table column definition from a column index?

Posted by: ichioka.yuji on 15 October 2024, 6:30 am EST

• 

  ichioka.yuji

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 15 October 2024, 6:30 am EST - Updated 15 October 2024, 6:35 am EST

  Hello,

  Althogth I wanted a right method to get a table column definition (or its definition id) from a column index, it seems to be unanable as for now. So, as an alternative, I use the table.getColumnName method to determine whether the current table column is a specific column. Please see the codes below.

  The problem is, when the column header name was changed by pasting some value or pushing ‘Delete’ key on the table column header, my approach doesn’t work well. Could you tell me if you have any workaround?

  import * as GC from '@mescius/spread-sheets';
  import '@mescius/spread-sheets/styles/gc.spread.sheets.excel2016black.css';
  import * as React from 'react';
  import './App.css';
  
  import Sheets = GC.Spread.Sheets;
  
  const columns: Sheets.Tables.TableColumn[] = [
    new Sheets.Tables.TableColumn(0, "name", "Name"),
    new Sheets.Tables.TableColumn(1, "address", "Address"),
    new Sheets.Tables.TableColumn(2, "phone", "Phone"),
    new Sheets.Tables.TableColumn(3, "email", "Email"),
  ]; 
  
  export default function App() {
    const host = React.useRef<HTMLDivElement>();
  
    React.useEffect(
      () => {
        const workbook = new Sheets.Workbook(host.current, {sheetCount: 1});
        const sheet = workbook.getSheet(0);
        const table = sheet.tables.add("table1", 1, 1, 4, 4, Sheets.Tables.TableThemes.light1);
        table.bindColumns(columns);
  
        // Prohibit from editing a value on the 'name' column...
        sheet.bind(Sheets.Events.EditStarting, (_: unknown, args: { row: number, col: number, cancel: boolean }) => {
          if (Object.is(table, sheet.tables.find(args.row, args.col))) {
            const tableColumnIndex = args.col - table.range().col;
  
            const name = table.getColumnName(tableColumnIndex);
            if (name === columns[0].name()) {
              args.cancel = true;
            }
          }
        });
  
        return () => workbook.destroy();
      },
      []
    );
  
    return (
      <div ref={host} style={{ height: "90vh", width: "100%" }} />
    );
  }

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 17 October 2024, 2:29 am EST

  Hi,

  As per my understanding, you have a situation, where you don’t want the users to change the table column name. The value can be changed by editing the cell, via drag drop, copy/cut and paste, drag fill, etc.

  It seems like you are trying to handle such cases, and therefore, using the EditStarting Event. You will have to also consider the when the value is cleared via the “Delete” button.

  If you want to restrict the value being cleared using the “Delete” key, you could use the following code snippet:

          // Prohibit from editing a value on the 'name' column...
          sheet.bind(Sheets.Events.EditStarting, (_, args) => {
              if (Object.is(table, sheet.tables.find(args.row, args.col))) {
                  const tableColumnIndex = args.col - table.range().col;
  
                  let colDefinition = table.toJSON().columns;
                  const name = colDefinition[tableColumnIndex].name;
                  // const name = table.getColumnName(tableColumnIndex);
                  console.log(name)
                  if (name === columns[0].name()) {
                      args.cancel = true;
                  }
              }
          });
  
          // Override the "clear" command
          let old = GC.Spread.Sheets.Commands.clear.execute;
          GC.Spread.Sheets.Commands.clear.execute = function (spread, options, isUndo) {
              // Check Custom Condition Here
              let sheet = spread.getSheetFromName(options.sheetName);
              let selection = sheet.getSelections()[0];
              if (selection.row === 1 && selection.col === 1) {
                  return false;
              }
              // Apply the Default Behavior
              return old.apply(this, arguments);
          };
  

  To get the column definition, you could use the following code:

  let colDefinition = table.toJSON().columns;

  A better and suggested approach would be to “lock” the cell so that the value cannot be changed by editing, via delete key, via copy paste, drag fill, etc.

  In the sample, you could use the following code (currently commented out):

          // Unlock all the cells in the worksheet via the styles
          let style = new GC.Spread.Sheets.Style();
          style.locked = false;
          // Set style as default style for all the cells in the sheet
          sheet.setDefaultStyle(style);
          // Separately lock the cell range you want to be read-only
          new GC.Spread.Sheets.CellRange(sheet, 1, 1, 1, 1).locked(true);
          // Set sheet to be protected
          sheet.options.isProtected = true;
  
          sheet.bind(GC.Spread.Sheets.Events.InvalidOperation, (sender, args) => {
              console.log("Invalid Operation");
              console.log(args);
          })

  Sample: https://jscodemine.mescius.io/share/RUU6cgeJGkeqfLpUyx4NjQ/?defaultOpen={"OpenedFileName"%3A["%2Fsrc%2Fapp.jsx"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.jsx"}

  You could refer to the following docs on all the events for drag fill, pasting etc: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Events

  References:

  Protect Worksheet and Lock Cells: https://developer.mescius.com/spreadjs/docs/features/worksheet/celllock#site_main_content-doc-content_title

  InvalidOperation Event: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Events#invalidoperation

  Clipboard Pasting Event: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Events#clipboardpasting

  Let us know if you face any issues. If your use case is not still met, kindly do share the use case with us so that we could have a better understanding of your use case and could help you accordingly.

  Regards,

  Ankit