How to obtain a specified range and configure the range properties in a table file - Intelligent Media Management

This topic describes how to obtain a specified range, configure the range properties, and add data validation rules to a specified range in a table file.

Range object

A range object represents a cell, a row, a column, or a selected range that contains a single cell or several contiguous cells.

Syntax
Expression.Range
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');
}

Range item

A range item is a range object that represents a specified location in the range.

Syntax
Expression.Range.Item({ RowIndex, ColumnIndex })
Expression: the document type application object.

Parameters

Parameter

Data type

Required

Description

RowIndex

Number

Yes

The row number or index.

If you specify the relative row number of the cell, you must also specify the ColumnIndex parameter.
If you specify the index of the subrange, you can skip the ColumnIndex parameter.

ColumnIndex

Number

The relative column number of the cell.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1:D2');

  // Obtain the range item B2.
  const item1 = await range.Item(2, 2);
  await item1.Select();
}

Properties

Obtain the width of a range

Syntax:
Expression.Range.ColumnWidth
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1:B2');

  // Obtain the width of the range.
  range.ColumnWidth = 30;
}

Obtain the height of a range

Syntax
Expression.Range.RowHeight
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1:B2');

  // Obtain the height of the range.
  range.RowHeight = 100;
}

Obtain the number of ranges

Syntax
Expression.Range.Count
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1:B2');

  // Obtain the number of ranges.
  const count = await range.Count;
  console.log(count);
}

Obtain the left margin

The left margin is the distance from the left edge of column A to the left edge of the range.

Syntax
Expression.Range.Left
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('B2');

  // Obtain the left margin.
  const left = await range.Left;
  console.log(left);
}

Obtain the top margin

The top margin is the distance from the top edge of row 1 to the top edge of the range.

Syntax:
Expression.Range.Top
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('B2');

  // Obtain the top margin.
  const top = await range.Top;
  console.log(top);
}

Hide rows or columns

Syntax
Expression.Range.Hidden
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain the entire column.
  const entireColumn = await range.Rows.EntireColumn;
  console.log(entireColumn);

  // Hide the column.
  entireColumn.Hidden = true;
}

Specify the horizontal alignment for a range

Syntax:
Expression.Range.HorizontalAlignment
Expression: the document type application object.

Fields

Field	Value	Alignment type
xlHAlignRight	-4152	Right.
xlHAlignLeft	-4131	Left.
xlHAlignJustify	-4130	Justified.
xlHAlignDistributed	-4117	Distributed.
xlHAlignCenter	-4108	Center.
xlHAlignGeneral	1	According to a data type.
xlHAlignFill	5	Fill.
xlHAlignCenterAcrossSelection	7	Center across selection.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1');

  // Specify center alignment for the range.
  range.HorizontalAlignment = await app.Enum.XlHAlign.xlHAlignCenter;

  setTimeout( async () => {
    // Obtain the alignment.
    const horizontalAlignment = await range.HorizontalAlignment;
    console.log(horizontalAlignment);
  }, 3000);
}

Specify the vertical alignment for a range

Syntax:
Expression.Range.VerticalAlignment
Expression: the document type application object.
Fields
Field
Value
Alignment type
xlVAlignTop
-4160
Top.
xlVAlignJustify
-4130
Justified.
xlVAlignDistributed
-4117
Distributed.
xlVAlignCenter
-4108
Center.
xlVAlignBottom
-4107
Bottom.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1');

  // Specify bottom alignment for the range.
  range.VerticalAlignment = await app.Enum.XlVAlign.xlVAlignBottom;

  setTimeout( async () => {
    // Obtain the alignment.
    const horizontalAlignment = await range.VerticalAlignment;
    console.log(horizontalAlignment);
  }, 3000);
}

Interior property

Obtain the interior property object of a range

Syntax
Expression.Range.Interior
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain the interior property object.
  const interior = await range.Interior;
}

Obtain or set the interior color of a range

Syntax
Expression.Range.Interior.Color
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain the interior property object.
  const interior = await range.Interior;

  // Specify the interior color.
  interior.Color = '#e1ff02';

  setTimeout( async () => {
    // Obtain the interior color.
    const color = await interior.Color;
    console.log(color);
  }, 3000);
}

Methods

Activate a range

Syntax
Expression.Range.Activate()
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Activate range A1.
  range.Activate();
}

Select a range

Syntax
Expression.Range.Select()
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Select range A1.
  range.Select();

  // Select range B1:D2 after 5,000 milliseconds.
  setTimeout(async () => {
    const newRange = await app.Range('B1:D2');
    newRange.Select();
  }, 5000);
}

Delete the content of a range

Syntax
Expression. Range.ClearContents()
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1:B2');

  // Delete the content of the range.
  await range.ClearContents();
}

Determine whether ranges overlap

Syntax
Expression.Range.Contain({ Range })
Expression: the document type application object.
Parameters
Parameter
Data type
Required
Description
Range
Range
Yes
Another range object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1:D2');

  // The second range object.
  const newRange = await app.Range('A1:B4');

  // Determine whether the ranges overlap.
  const contain = await range.Contain(newRange);
  console.log(contain);
}

Region reference

Obtain the String values that are referenced by a range using a macro language.

Syntax
Expression.Range.Address({ RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo })
Expression: the document type application object.

Parameters

Parameter	Data type	Required	Description
RowAbsolute	Boolean	No	Default value: true. Specify true for this parameter to return the row as an absolute reference.
ColumnAbsolute	Boolean	No	Default value: true. Specify true for this parameter to return the column as an absolute reference.
ReferenceStyle	Enum	No	The reference style. Valid values of `Enum.XlReferenceStyle`: -4150 or xlR1C1: returns the reference in the R1C1 style. 1 or xlA1: returns the reference in the A1 style. This is the default value.
External	Boolean	No	Default value: false. true: returns an external reference. false: returns a local reference.
RelativeTo	Range	No	A range object that defines the starting point. If `RowAbsolute` and `ColumnAbsolute` are false and `ReferenceStyle` is xlR1C1, you must include a starting point for the relative reference.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain the String values that are referenced by the range using a macro language.
  const address1 = await range.Address();
  console.log('address1:', address1);

  const address2 = await range.Address(false, false);
  console.log('address2:', address2);

  const address3 = await range.Address(true, true, -4150);
  console.log('address3:', address3);
}

Autofill

Perform autofill on the cells in the specified range.

Syntax
Expression. Range.AutoFill({ Destination, Type })
Expression: the document type application object.

Parameters

Parameter

Data type

Required

Description

Destination

Range

The cells to be filled. The destination range must include the source range.

Type

Enum

The fill type. Valid values of Enum.XlAutoFillType:

0 or xlFillDefault (default): The system determines the value and format used to fill the specified range.
1 or xlFillCopy: The values and formats are copied from the source range to the destination range. The process is repeated if necessary.
2 or xlFillSeries: The values in the source range are extended into the destination range as a series. For example, '1, 2' is extended as '3, 4, 5'. Formats are copied from the source range to the destination range. The process is repeated if necessary.
3 or xlFillFormats: Only the formats are copied from the source range to the destination range. The process is repeated if necessary.
4 or xlFillValues: Only the values are copied from the source range to the destination range. The process is repeated if necessary.
5 or xlFillDays: The names of the days of the week in the source range are extended into the destination range. Formats are copied from the source range to the destination range. The process is repeated if necessary.
6 or xlFillWeekdays: The names of the days of the workweek in the source range are extended into the destination range. Formats are copied from the source range to the destination range. The process is repeated if necessary.
7 or xlFillMonths: The names of the months in the source range are extended into the destination range. Formats are copied from the source range to the destination range. The process is repeated if necessary.
8 or xlFillYears: The years in the source range are extended into the destination range. Formats are copied from the source range to the destination range. The process is repeated if necessary.
9 or xlLinearTrend: The numeric values in the source range are extended into the destination range, assuming that the relationships between the numbers is additive. For example, '1, 2,' is extended as '3, 4, 5', assuming that each number is a result of adding some value to the previous number. Formats are copied from the source range to the destination range. The process is repeated if necessary.
10 or xlGrowthTrend: The numeric values in the source range are extended into the destination range, assuming that the relationships between the numbers in the source range are multiplicative. For example, '1, 2,' is extended as '4, 8, 16', assuming that each number is a result of multiplying the previous number by some value. Formats are copied from the source range to the destination range. The process is repeated if necessary.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1:A2');

  // Obtain the cells to fill.
  const fillRange = await app.Range('A1:A20');

  // Perform autofill on the cells in the specified range.
  await range.AutoFill(fillRange);
}

Offset a range

Offset a specified range.

Syntax
Expression.Range.Offset({ RowOffset, ColumnOffset })
Expression: the document type application object.

Parameters

Parameter

Data type

Required

Description

RowOffset

Number

The number of rows by which the range is to be offset. The number can be positive, negative, or zero. Default value: 0.

A positive number indicates a downward offset.
A negative number indicates an upward offset.

ColumnOffset

Number

The number of columns by which the range is to be offset. The number can be positive, negative, or zero. Default value: 0.

A positive number indicates an offset to the right.
A negative number indicates an offset to the left.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1:D2');
  await range.Select();

  // Offset the specified range.
  const newRange = await range.Offset(2, 2);
  await newRange.Select();
}

Export a range as an image

Export a range as an image.

Syntax
Expression.Range.ToImageDataURL()
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1:D2');

  // Export the range as an image.
  const img = await range.ToImageDataURL();
  console.log(img);
}

Borders

Obtain border objects

Syntax
Expression.Range.Borders
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain border objects.
  const borders = await range.Borders;
}

A single border

Obtain a single border object

A single border object represents one of the borders of either a range of cells or styles.

Syntax
Expression.Range.Borders.Item(Index)
Expression: the document type application object.

Parameters

Parameter

Data type

Required

Description

Index

Enum

Yes

The border to retrieve. Valid values of Enum.XlBordersIndex:

5 or xlDiagonalDown: the border running from the upper-left corner to the lower-right of each cell in the range.
6 or xlDiagonalUp: the border running from the lower-left corner to the upper-right of each cell in the range.
7 or xlEdgeLeft: the border at the left edge of the range.
8 or xlEdgeTop: the border at the top of the range.
9 or xlEdgeBottom: the border at the bottom of the range.
10 or xlEdgeRight: the border at the right edge of the range.
11 or xlInsideVertical: the vertical borders of all the cells in the range except for borders outside the range.
12 or xlInsideHorizontal: the horizontal borders of all cells in the range except for borders outside the range.
13 or xlOutside: the upper, lower, left, and right borders in the range.
14 or xlInside: the borders in the middle of the range.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain border objects.
  const borders = await range.Borders;

  // Obtain the single border object.
  const border = await borders.Item(5);
}

Obtain the color of a border

Syntax
Expression.Range.Borders.Item(Index).Color
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain border objects.
  const borders = await range.Borders;

  // Obtain the single border object.
  const border = await borders.Item(app.Enum.XlBordersIndex.xlDiagonalDown);

  // Obtain the border color.
  const color = await border.Color;
  console.log(color);
}

Specify the line style of a border

Syntax
Expression.Range.Borders.Item(Index).LineStyle
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A3');

  // Obtain border objects.
  const borders = await range.Borders;

  // Obtain the single border object.
  const border = await borders.Item(app.Enum.XlBordersIndex.xlOutside);

  // Specify the line style of the border.
  border.LineStyle = app.Enum.XlLineStyle.xlDash;
}

Specify the weight of a border

Syntax
Expression.Range.Borders.Item(Index).Weight
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A3');

  // Obtain border objects.
  const borders = await range.Borders;

  // Obtain the single border object.
  const border = await borders.Item(app.Enum.XlBordersIndex.xlOutside);

  // Specify the weight of the border.
  border.Weight = app.Enum.XlBorderWeight.xlThick;
}

Rows

Obtain specified rows

Obtain a range object that represents the rows in the specified range. You can use the properties and methods that are related to the range object.

Syntax
Expression.Range.Rows
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1:B2');

  // Obtain row objects.
  const rows = await range.Rows;

  // Specify the second row.
  const row = await rows.Item(2);

  // Select the second row.
  await row.Select();
}

Obtain the number of the first row in a range

Obtain the number of the first row in the range.

Syntax
Expression.Range.Row
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1:B2');

  // Obtain the number of the first row in the range.
  const row = await range.Row;
  console.log(row);
}

Obtain the number of the last row in a range

Obtain the number of the last row in the range.

Syntax
Expression.Range.RowEnd
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1:B2');

  // Obtain the number of the last row in the range.
  const rowEnd = await range.RowEnd;
  console.log(rowEnd);
}

Obtain an entire row

Obtain an entire row that contains a specified range.

Syntax
Expression.Range.EntireRow
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain the entire row that contains the range.
  const entireRow = range.EntireRow;

  // Select this row.
  await entireRow.Select();
}

Columns

Obtain specified columns

Obtain a range object hat represents the columns in the specified range. You can use the properties and methods that are related to the range object.

Syntax
Expression.Range.Columns
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1:B2');

  // Obtain column objects.
  const columns = await range.Columns;

  // Specify the second column.
  const column = await columns.Item(2);

  // Select the second column.
  await column.Select();
}

Obtain the number of the first column in a range

Obtain the number of the first column in a range.

Syntax
Expression.Range.Column
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1:B2');

  // Obtain the number of the first column in the range.
  const column = await range.Column;
  console.log(column);
}

Obtain the number of the last column in a range

Obtain the number of the last column in a range.

Syntax
Expression.Range.ColumnEnd
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1:B2');

  // Obtain the number of the last column in the range.
  const columnEnd = await range.ColumnEnd;
  console.log(columnEnd);
}

Obtain an entire column

Obtain an entire column that contains a specified range.

Syntax
Expression.Range.EntireColumn
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain the entire column that contains the range.
  const entireColumn = range.EntireColumn;

  // Select this column.
  await entireColumn.Select();
}

Cells

Obtains specified cells

Obtain a range object that represents the cells in a specified range. You can use the properties and methods that are related to the range object.

Syntax
Expression.Range.Cells
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1:B2');

  // Obtain cell objects.
  const cells = await range.Cells;

  // Specify the first cell.
  const cell = await cells.Item(1);
  window.cells = cells;

  // Select the first cell.
  await cell.Select();
}

Obtain the text of cells

Syntax
Expression.Range.Text
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1:B2');

  // Obtain the text of cells in the specified range.
  const text = await range.Text;
  console.log(text);
}

Merge cells

Syntax
Expression.Range.Merge({ Across })
Expression: the document type application object.
Parameters
Parameter
Data type
Required
Description
Across
Boolean
Yes
Default value: false. If you specify true for this parameter, the cells in each row of the specified range are merged as separate merged cells.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1:D2');

  // Merge cells.
  await range.Merge();
}

Obtain a merged range that contains a specified cell

Syntax
Expression.Range.MergeArea
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1:D2');

  // Merge cells.
  await range.Merge();

  // Click the blank area of a merged range within 5,000 milliseconds after the cells are merged, and the range is automatically selected after 5,000 milliseconds.
  setTimeout( async () => {
    // Obtain the merged range of the cell.
    const mergeArea = await range.MergeArea;
    mergeArea.Select();
  }, 5000);
}

Query merged cells

Query whether merged cells exist in a range.

Syntax
Expression.Range.MergeCells
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1:D2');

  // Merge cells.
  await range.Merge();

  // Query whether merged cells exist in the range.
  const mergeCells = await range.MergeCells;
  console.log(mergeCells);
}

Set the value for cells

Set the value for the cells in a specified range.

Syntax
Expression.Range.Value
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1');

  // Set the value for the cells in the range.
  range.Value = 'WebOffice';
}

Font

Obtain a font object

Syntax
Expression.Range.Font
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain the font object.
  const font = range.Font;
}

Set or obtain the font size

Syntax
Expression.Range.Font.Size
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain the font object.
  const font = range.Font.Size;

  // Set the font size.
  font.Size = 30;

  // Obtain the font size.
  const size = await font.Size;
  console.log(size);
}

Set or obtain bold formatting of the font

Syntax
Expression.Range.Font.Bold
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain the font object.
  const font = range.Font;

  // Obtain bold formatting of the font.
  const isBold = await font.Bold;
  console.log ('Whether the font is bold:', isBold);

  // Specify that the font is formatted as bold.
  font.Bold = true;
}

Comments

Add a comment

Add a comment to a specified range.

Syntax
Expression.Range.AddComment({ Text })
Expression: the document type application object.
Parameters
Parameter
Data type
Required
Description
Text
String
No
The text of the comment.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Add a comment to range A1.
  range.AddComment('WebOffice');

Clear comments

Clear all comments from a specified range.

Syntax
Expression.Range.ClearComments()
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A1:B2');

  // Clear all comments from the range.
  await range.ClearComments();
}

Formulas

Set the formula of an object

Set the implicitly intersecting formula of an object in A1-style notation.

Syntax
Expression.Range.Formula
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A2');

  // Set the following formula: A2=A1+B1.
  range.Formula = '=A1+B1';
}

Set or obtain the array formula of a range

Set or obtain the array formula of a specified range.

Syntax
Expression.Range.FormulaArray
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;

  // Obtain the range object.
  const range = await app.Range('A2:B2');

  // Set the following formula: the values of A2 to B2 are the sum of A1 and B1.
  range.FormulaArray = '=Sum(A1:B1)';
  
  // Obtain the formula.
  const formulaArray = await range.FormulaArray;
  console.log(formulaArray);
}

Conditional formats

Obtain conditional format objects

Obtain all the conditional formats for a specified range.

Syntax
Expression.Range.FormatConditions
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain the conditional format objects.
  const formatConditions = await range.FormatConditions;
}

Add a conditional format

Add a new conditional format.

Syntax
Expression.Range.FormatConditions.Add({ Type, Operator, Formula1, Formula2 })
Expression: the document type application object.

Parameters

Parameter	Data type	Required	Description
Type	Enum	Yes	Specifies whether the conditional format is based on a cell value or an expression. Valid values of `Enum.XlFormatConditionType`: 1 or xlCellValue: cell value. 2 or xlExpression: expression. 3 or xlColorScale: color scale. 5 or xlTop10: top 10 values. 8 or xlUniqueValues: unique values. 9 or xlTextString: text string. 10 or xlBlanksCondition: blanks condition. 11 or xlTimePeriod: time period. 12 or xlAboveAverageCondition: above average condition. 13 or xlNoBlanksCondition: no blanks condition. 16 or xlErrorsCondition: errors condition. 17 or xlNoErrorsCondition: no errors condition.
Operator	Number	No	The operator of a conditional format. Valid values of `Enum.XlFormatConditionOperator`: 1 or xlBetween: between. This argument can be used only if two formulas are provided. 2 or xlNotBetween: not between. This argument can be used only if two formulas are provided. 3 or xlEqual: equal. 4 or xlNotEqual: not equal. 5 or xlGreater: greater than. 6 or xlLess: less than. 7 or xlGreaterEqual: greater than or equal to. 8 or xlLessEqual: less than or equal to.
Formula1	Number	No	The value or expression associated with the conditional format. This argument can be a constant value, a string value, a cell reference, or a formula.
Formula2	Number	No	The value or expression associated with the second part of the conditional format when the operator is xlBetween or xlNotBetween. If the operator is any other value, this argument is ignored. This argument can be a constant value, a string value, a cell reference, or a formula.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A4:D5');

  // Select a range.
  await range.Select();

  // Set a formula.
  range.Formula = 'WebOffice';

  // Obtain conditional format objects.
  const formatConditions = await range.FormatConditions;

  // Add a conditional format.
  formatConditions.Add(
    app.Enum.XlFormatConditionType.xlExpression,
    undefined,
    '=D1=1',
  );
}

Data validation

Obtain data validation

Syntax
Expression.Range.Validation
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain data validation.
  const validation = await range.Validation;
}

Add data validation

Add data validation to a specified range.

Syntax
Expression.Range.Validation.Add({ Type, Operator, Formula1, Formula2 })
Expression: the document type application object.

Parameters

Parameter	Data type	Required	Description
Type	Enum	Yes	The validation type. Valid values of `Enum.XlDVType`: 1 or xlValidateWholeNumber: whole numeric values. 2 or xlValidateDecimal: numeric values. 3 or xlValidateList: value in a specified list. 4 or xlValidateDate: date values. 5 or xlValidateTime: time values. 6 or xlValidateTextLength: text length. 7 or xlValidateCustom: validated by using an arbitrary formula.
Operator	Enum	No	The data validation operator. Valid values of `Enum.XlFormatConditionOperator`: 1 or xlBetween: between. This argument can be used only if two formulas are provided. 2 or xlNotBetween: not between. This argument can be used only if two formulas are provided. 3 or xlEqual: equal. 4 or xlNotEqual: not equal. 5 or xlGreater: greater than. 6 or xlLess: less than. 7 or xlGreaterEqual: greater than or equal to. 8 or xlLessEqual: less than or equal to.
Formula1	String	No	The first part of the data validation equation, which can be up to 255 characters in length.
Formula2	String	No	The second part of the data validation equation when the data validation operator is xlBetween or xlNotBetween. If the operator is any other value, this parameter is ignored.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain the data validation object.
  const validation = await range.Validation;

  // Add data validation.
  await validation.Add({
    Type: app.Enum.XlDVType.xlValidateWholeNumber,
    Operator: app.Enum.XlFormatConditionOperator.xlBetween,
    Formula1: '1',
    Formula2: '5',
  });
}

Modify data validation

Modify data validation for a range.

Syntax
Expression.Range.Validation.Modify({ Type, Operator, Formula1, Formula2 })
Expression: the document type application object.

Parameters

Parameter	Data type	Required	Description
Type	Enum	Yes	The validation type. Valid values of `Enum.XlDVType`: 1 or xlValidateWholeNumber: whole numeric values. 2 or xlValidateDecimal: numeric values. 3 or xlValidateList: value in a specified list. 4 or xlValidateDate: date values. 5 or xlValidateTime: time values. 6 or xlValidateTextLength: text length. 7 or xlValidateCustom: validated by using an arbitrary formula.
Operator	Enum	No	The data validation operator. Valid values of `Enum.XlFormatConditionOperator`: 1 or xlBetween: between. This argument can be used only if two formulas are provided. 2 or xlNotBetween: not between. This argument can be used only if two formulas are provided. 3 or xlEqual: equal. 4 or xlNotEqual: not equal. 5 or xlGreater: greater than. 6 or xlLess: less than. 7 or xlGreaterEqual: greater than or equal to. 8 or xlLessEqual: less than or equal to.
Formula1	String	No	The first part of the data validation equation, which can be up to 255 characters in length.
Formula2	String	No	The second part of the data validation equation when the data validation operator is xlBetween or xlNotBetween. If the operator is any other value, this parameter is ignored.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain the data validation object.
  const validation = await range.Validation;

  // Add data validation.
  await validation.Add({
    Type: app.Enum.XlDVType.xlValidateWholeNumber,
    Operator: app.Enum.XlFormatConditionOperator.xlBetween,
    Formula1: '1',
    Formula2: '5',
  });

  // Modify data validation after 10,000 milliseconds.
  setTimeout( async () => {
    await validation.Modify({
      Type: app.Enum.XlDVType.xlValidateWholeNumber,
      Operator: app.Enum.XlFormatConditionOperator.xlNotBetween,
      Formula1: '1',
      Formula2: '5',
    });
  }, 10000);
}

Delete data validation

Delete data validation of a range.

Syntax
Expression.Range.Validation.Delete()
Expression: the document type application object.

Example

async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // Obtain the range object.
  const range = await app.Range('A1');

  // Obtain the data validation object.
  const validation = await range.Validation;

  // Add data validation.
  await validation.Add({
    Type: app.Enum.XlDVType.xlValidateWholeNumber,
    Operator: app.Enum.XlFormatConditionOperator.xlBetween,
    Formula1: '1',
    Formula2: '5',
  });

  // Delete data validation after 10,000 milliseconds.
  setTimeout( async () => {
    await validation.Delete();
  }, 10000);
}