[DateTimePicker] Is there a way to disable some date range in date time picker

Posted by: lucas.nguyen on 16 December 2024, 3:55 am EST

    • Post Options:
    • Link

    Posted 16 December 2024, 3:55 am EST

    Hi,

    Is there a way to disable a specific date range in the date-time picker in a TableSheet, or if the user selects a date outside of a predefined range, the value will automatically clear or revert to the previous value, or have a similar behavior? Currently, I am using a validator, but an invalid date can still be selected.

    My date time picker column:

    {
              value: 'date',
              dataType: 'date',
              caption: t('date'),
              width: 120,
              required: true,
              readonly: openTab == TabName.Confirm,
              style: {
                formatter: 'yyyy/MM/dd',
                hAlign: 'left'
              },
              conditionalFormats: [disabledFormat, disableDateFormat]
            },

    My schema:

    schema: {
                  columns: {
                    date: {
                      dataType: 'date'
                    }
                  }
                }

  • Posted 18 December 2024, 5:00 am EST - Updated 19 December 2024, 4:47 am EST

    Hi,

    Currently, disabling the Calendar dates is not supported in the date-time picker. However, you can use the built-in validators in SpreadJS, which are more suited for this use case than conditional formatting. By attaching a ‘validator’ to a specific column or cell and handling the ‘ValidationError’ event, you can ensure only valid dates are allowed.

    Below is a sample code snippet to demonstrate the implementation:

    1. Defining the Date Validator
    This part defines the validation rules for the date range:
    // Define the date validator
    const dateValidator = {
        comparisonOperator: GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between,
        type: "date",
        value1: new Date(2024, 11, 20), // Start date: Dec 20, 2024
        value2: new Date(2024, 11, 25), // End date: Dec 25, 2024
        inputTitle: "Date Validation",
        inputMessage: "Enter a date between December 20, 2024, and December 25, 2024",
    };
    
    2. Configuring the Date Column
    This part applies the validator to a specific column:
    // Define a column with a date picker and attach the validator
    const dateColumn = {
        value: "dueDate",
        dataType: "date",
        width: 150,
        style: { 
            formatter: "yyyy/mm/dd", 
            hAlign: "left",
        },
        validator: dateValidator, // Attach the validator
    };
    
    3. Handling Validation Errors
    This part ensures invalid values are discarded when the user makes an error:
    // Handle the ValidationError event to discard invalid entries
    spread.bind(GC.Spread.Sheets.Events.ValidationError, (e, args) => {
        // Discard the invalid entry
      args.validationResult = GC.Spread.Sheets.DataValidation.DataValidationResult.discard;
    });

    These sections together implement date validation in SpreadJS while ensuring invalid entries are not accepted.

    Please refer this sample for detailed code we have created for you.

    References:-

    datavalidationresult api :- https://developer.mescius.com/spreadjs/api/enums/GC.Spread.Sheets.DataValidation.DataValidationResult#enumeration-datavalidationresult

    Data Validation Demo :- https://developer.mescius.com/spreadjs/demos/features/table-sheet/customize-view/conditional-format/purejs

    Hope this works for you.

    Best Regards,

    Ankit

    features_table-sheet_define-column_overview_JavaScript (1).zip

  • Posted 18 December 2024, 6:54 am EST

    Hi Ankit,

    But in case my row is disabled, validator will return an error, when I change ignore blank to false, user can delete date value in the enabled cell and submit.

    Best Regards,

    Lucas

  • Posted 19 December 2024, 5:48 am EST - Updated 19 December 2024, 5:53 am EST

    Hi Lucas!

    We have attempted to replicate the behavior you described but were unable to observe the reported issue. Specifically, we disabled the row by setting “args.cancel = true” in the “EditStarting” event. While testing, including using the date picker to set the date in disabled row cell, no error related to the validator was triggered.

    We have attached a sample which is working at our end for you as reference. If your implementation of disabled row is different than our implementation. Please share the exact method you used with us.

    For further investigation, could you kindly share a minimal sample that reproduces the issue? This would help us pinpoint the problem and provide a more precise solution.

    Best Regards,

    Ankit

    Sample: sample.zip

  • Posted 19 December 2024, 6:09 am EST

    Hi Ankit,

    My validator:

    export async function createDateRangeValidation(
      headerText: string,
      minDate: Date,
      maxDate: Date
    ): Promise<any> {
      return {
        type: 'date',
        comparisonOperator: 'between',
        value1: minDate,
        value2: maxDate,
        errorMessage: await getTranslation(
          'Common',
          'validation.invalidDateFormat',
          {
            name: headerText
          }
        ),
        errorStyle: 'stop',
        highlightStyle: {
          type: 'dogEar',
          color: 'red',
          position: 'topLeft'
        },
        ignoreBlank: false,
        inCellDropdown: false,
        errorTitle: '',
        inputMessage: '',
        inputTitle: '',
        showErrorMessage: true,
        showInputMessage: false
      }
    }

    I want to validate a date range from the start to the end of the current month. If the date column is enabled, it is required. This validator works as expected.

    The issue begins when I have some disabled cells. I disable a cell by modifying the openDateTimePicker command to execute as follows:

    let oldOpenDateTimeCmd =
            GC.Spread.Sheets.Commands.openDateTimePicker.execute
          sheet.bind(
            GC.Spread.Sheets.Events.EditStarting,
            function (sender: any, args: any, e: any) {
              if (disabledRows.has(args.row)) {
                args.cancel = true
              }
            }
          )

    Then my object has isDisabled = true and date = undefined. SpreadJS displays 0001/01/01 (why not blank?) => I added a custom style with the backColor and foreColor set to the same color to hide it. The validator is triggered in this disabled cell in my object and displays an error (of course).

    I don’t want to validate empty data cells like this, so I updated my validator with ignoreBlank = true.

    Another issue appears: enabled cells can use the Delete button to clear their value. With ignoreBlank, the validator no longer validates this cell.

    This behavior is generally acceptable, but it is not correct in my case.

    Please advise on how to disable specific date cells in a column, not validate those cells, and validate other date cells in the same column as required and within the range. Any workaround is accepted.

    Best Regards,

    Lucas

  • Posted 20 December 2024, 7:00 am EST - Updated 20 December 2024, 7:05 am EST

    Hi,

    Regarding the query: “Then my object has isDisabled = true and date = undefined. SpreadJS displays 0001/01/01 (why not blank?)”—as per my understanding, when a row is disabled, and the data is undefined, SpreadJS displays some value instead of leaving it blank. I attempted to replicate this behavior based on the provided information but was unable to reproduce it. On my end, SpreadJS displays nothing. Please refer to the attached GIF “Steps.gif” and sample “Sample.zip.”

    Gif:

    Sample: Sample.zip

    To assist you better, could you please share a minimal working sample along with the steps to replicate the observed behavior? Alternatively, you can modify the existing sample to reproduce the issue. This will enable me to investigate further. Additionally, providing a GIF or video illustrating the problem would be very helpful.

    Initially, your requirement was: “Is there a way to disable a specific date range in the date-time picker in a TableSheet, or if the user selects a date outside of a predefined range, the value will automatically clear or revert to the previous value, or have similar behavior?”

    With the recent release of SpreadJS version 18, new functionality has been added to the date-time picker to disable date ranges. For example, if you wish to allow the selection of only the current month date, you can define the selectable range using the minDate and maxDate properties. Refer to the snippet, attached GIF “Steps1.gif,” and sample “Sample1.zip” for more details:

    const defaultDateLimitedStyle = new GC.Spread.Sheets.Style();
    defaultDateLimitedStyle.formatter = "yyyy/mm/dd";
    defaultDateLimitedStyle.hAlign = "left";
    defaultDateLimitedStyle.cellButtons = [
      {
        imageType: GC.Spread.Sheets.ButtonImageType.dropdown,
        command: "openDateTimePicker",
        useButtonStyle: true,
      }
    ];
    defaultDateLimitedStyle.dropDowns = [
      {
        type: GC.Spread.Sheets.DropDownType.dateTimePicker,
        option: {
          showTime: true,
          minDate: new Date("2024/12/1"),
          maxDate: new Date("2024/12/31"),
        },
      },
    ];

    Gif:

    Sample: Sample1.zip

    For more details about the date-time picker dropdown, refer to these resources:

    Date-Time Picker Dropdown Documentation: https://developer.mescius.com/spreadjs/docs/features/cells/cell-dropdowns#date-time-picker-dropdown

    Date-Time Picker Dropdown Demo: https://developer.mescius.com/spreadjs/demos/features/cells/drop-downs/date-time-picker/purejs

    Note that since this feature is part of version 18, you will need to upgrade to this version and obtain a valid license key for it as the previous version key will not work.

    Regards,

    Priyam

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels