Data validation on table columns

Data validation on table columns

Posted by: ichioka.yuji on 30 October 2024, 3:40 am EST

• 

  ichioka.yuji

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 30 October 2024, 3:40 am EST

  As we can set a specific cell type on specific table columns, can we set a specfic data validation on table columns?

  Here is your official demo: Table With Conditional Formats and Data Validation
  
  https://developer.mescius.com/spreadjs/demos/features/tables/table-layout/purejs

  Using this demo, I tried the following operations:

  1. Type “5” at the [A1] cell.
  2. Copy the value (“5”) on the [A1] cell, and then past it onto the [B4] cell.

  I had expected the [B4] cell in the table to be highlited as an invalid value, but it didn’t turn highlited.

  Could tell me the way how a data validation set on table columns works well with a value pasted from another cell?

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 30 October 2024, 4:54 am EST - Updated 30 October 2024, 4:59 am EST

  Hi,

  The observed behaviour is by design and is expected. When you copy/paste from the Cell A1 to Cell B4, by default the data validator of Cell A1 (which is undefined) will also be copied to the Cell B4 which will make the Cell B4 data validator as undefined and therefore, the data validator doesn’t circle the Cell Value on Cell B4.

  This behaviour is same as that of Microsoft Excel. If you copy paste using the context menu, and paste only the values, you will see that it correctly highlights the invalid value on Cell B4. Refer to the following gif on using the context menu.

  

  If you don’t want the data validator to be replaced, you can use any of the approach.

  1. Set the Clipboard Option to paste only the values: You could set the clipboard option to paste only the values. This way only the values will be pasted for each of the cells, and not conditional formatting, styles, data validators, etc. Note that it will work for each and every cells.

  //This example uses the ClipboardPasteOptions enumeration.
  activeSheet.options.clipBoardOptions = GC.Spread.Sheets.ClipboardPasteOptions.values;

  Refer to the following clipboardOptions Docs: https://developer.mescius.com/spreadjs/docs/features/workbook/clipboard#site_main_content-doc-content_title

  ClipboardPasteOptions Enumeration: https://developer.mescius.com/spreadjs/api/enums/GC.Spread.Sheets.ClipboardPasteOptions#values

  2. If you don’t want the behaviour for every cells, and only want that the validators should not get overridden when pasting the table cells, you could use the “ClipboardPasting” and “ClipboardPasted” events, and restore the data validators when the pasting is completed.

  Refer to the following code snippet and the sample that demonstrates the same:

      let oldTableValidator = null;
      spread.bind(GC.Spread.Sheets.Events.ClipboardPasting, (sender, args) => {
          console.log("Clipboard Pasting Event");
          console.log(args);
          let pastingCellRange = args.cellRange;
          let isRangeInTable = checkCellRangeInTable(args.sheet, args.cellRange);
          if (isRangeInTable) {
              oldTableValidator = args.sheet.getRange(pastingCellRange.row, pastingCellRange.col, pastingCellRange.rowCount, pastingCellRange.colCount).validator();
          } else {
              oldTableValidator = null;
          }
      });
  
      spread.bind(GC.Spread.Sheets.Events.ClipboardPasted, (sender, args) => {
          console.log("Clipboard Pasted Event");
          console.log(args);
          if (oldTableValidator) {
              let pastingCellRange = args.cellRange;
              args.sheet.getRange(pastingCellRange.row, pastingCellRange.col, pastingCellRange.rowCount, pastingCellRange.colCount).validator(oldTableValidator)
          }
      });

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

  References:

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

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

  validator method: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.CellRange#validator

  Let me know if you face any issues.

  Regards,

  Ankit

• 

  ichioka.yuji

  • Post Options:
  • Link
    Copy

  Posted 1 November 2024, 3:14 am EST

  Great!! Thank you.