Excel Data Validation - Whole Numbers have restricted integer values

[ChrisWRWX]

Provide required information needed to triage your issue

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: PC Desktop
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: Version 2211 (Build 15831.20208)
• Operating System: Windows 10
• Browser (if using Office on the web): N/A

Expected behavior

Setting data validation for whole numbers using JS should allow larger positive and negative integers, as is possible by the Excel UI.

Current behavior

When setting 'wholeNumber' data validation in JS, only values in the range 0-65535 can be set.

It appears this is using a 16 bit unsigned integer, where the Excel UI itself is not restricted by the same limitation.

65,535 works as expected but 65,536 does not.

Steps to reproduce

This code does work as expected:

const range = context.workbook.getSelectedRange();

range.dataValidation.rule = {
  wholeNumber: {
    formula1: 65535,
    operator: Excel.DataValidationOperator.lessThan
  }
};

await context.sync();

But not if the integer is outside the range 0-65535

const range = context.workbook.getSelectedRange();

range.dataValidation.rule = {
  wholeNumber: {
    formula1: 65536,
    operator: Excel.DataValidationOperator.lessThan
  }
};

await context.sync();

[AlexJerabek]

Thanks for reporting this @ChrisWRWX.

@yaweizhu-henson, could you please investigate?

[donlvMSFT]

Hi, yes currently the number have limitation, we have internal backlog to track this: #8009234

We will start the investigation ASAP when we have the bandwidth. Or you can provide the scale of affected users, it will help us re-prioritize this bug. Thanks for your patience!

[thomasjungblut]

We're also running into this right now, where we want to ensure different integer ranges (16 bit, 32 bit, 64 bit - signed/unsigned). I can see this works within Excel, is this a limitation of officejs?

edit: we can workaround by using "decimal" instead.