Capture only the date using the Date and Time Picker cell dropdown

Background:

SpreadJS offers built in cell dropdowns which includes a Date Time Picker. This dropdown allows end users to select a date but by default this cell dropdown will capture a time stamp. To avoid capturing the time stamp, SpreadJS users can implement a custom command to only get the date when the end user submits the selected date.

Steps to Complete:

  1. Create a custom command

  2. Register the custom command

  3. Create and applied the cell dropdown

Getting Started:

Step 1: Create a custom command

To capture only the date, we must first create a custom command to get only the date. The command will then set a new value which will contain the capture date:

// Create custom command to submit the date without time
            let submitDateWithoutTime = {
                canUndo: false,
                execute: function (context, options, isUndo) {
                    var sheet = context.getSheetFromName(options.sheetName);

                    var newData = new Date(options.newValue.getFullYear(), options.newValue.getMonth(), options.newValue.getDate());
                    var submitText = new GC.Spread.Formatter.GeneralFormatter(sheet.getFormatter(options.row, options.col)).format(newData);
                    sheet.getParent().commandManager().execute({
                        cmd: "editCell",
                        sheetName: sheet.name(),
                        row: options.row,
                        col: options.col,
                        newValue: submitText,
                        autoFormat: true,
                        isUndo: false
                    });
                }
            }

Step 2: Register the custom command

When creating custom commands, they must be registered to the command manager. We will register the submitDateWithoutTime command like so:

// Register custom command to the command manager
var commandManager = spread.commandManager();
commandManager.register("submitDateWithoutTime", submitDateWithoutTime, null, false, false, false, false);

Step 3: Create and apply the cell dropdown

Next, we will create the style for the Date Time Picker dropdown with the showTime option set to False and the submitCommand will use our custom command, submitDateWithoutTime.

			 // Set styles for Date Time Picker : showTime False & custom command
			var notShowTimestyle = new GC.Spread.Sheets.Style();
			notShowTimestyle.cellButtons = [
				{
					imageType: GC.Spread.Sheets.ButtonImageType.dropdown,
					command: "openDateTimePicker",
					useButtonStyle: true,
				}
			];
			notShowTimestyle.dropDowns = [
				{
					type: GC.Spread.Sheets.DropDownType.dateTimePicker,
					option: {
						showTime: false
					},
					// Set the new custom command as the cell dropdowns submit command
					submitCommand: "submitDateWithoutTime"
				}
			];


Finally, we will set date time picker and apply a date formatter to the same cell to set the format of how the date is to be formatted:

sheet.setStyle(5, 5, notShowTimestyle);
sheet.setFormatter(5, 5, 'yyyy-mm-dd');

Outcome:

With this code logic applied, the cell dropdown will now only capture and display the selected date, the time stamp is not observable:


Here is a link to a live sample showing this: https://codesandbox.io/s/sjs-js-custom-date-time-picker-date-only-vmuio?file=/index.html

Tags:

Mackenzie Albitz