Поиск специальных ячеек в диапазоне с помощью API JavaScript для Excel

Используйте API JavaScript для Excel, чтобы быстро находить ячейки с формулами, константами, ошибками или другими характеристиками. Такой подход помогает эффективно выполнять аудит данных или применять форматирование. В этой статье показано, как использовать Range.getSpecialCells и Range.getSpecialCellsOrNullObject, когда выбирать каждый метод, а также как дополнительно сузить результаты по типам значений ячеек. Полный набор свойств и методов, поддерживаемых объектом, см. в Rangeразделе Класс Excel.Range.

Краткий справочник

Поиск диапазонов с помощью специальных ячеек

Методы Range.getSpecialCells и Range.getSpecialCellsOrNullObject находят диапазоны на основе характеристик их ячеек и типов значений в этих ячейках. Оба метода возвращают RangeAreas объекты. Подписи методов из файла типов данных TypeScript:

getSpecialCells(cellType: Excel.SpecialCellType, cellValueType?: Excel.SpecialCellValueType): Excel.RangeAreas;

getSpecialCellsOrNullObject(cellType: Excel.SpecialCellType, cellValueType?: Excel.SpecialCellValueType): Excel.RangeAreas;

В следующем примере кода используется getSpecialCells для поиска всех ячеек с формулами. Обратите внимание на следующие моменты:

• Для повышения производительности код сначала вызываетworksheet.getUsedRange(), чтобы ограничить область поиска.
• getSpecialCells возвращает один RangeAreas объект, чтобы можно было форматировать неконтигентные совпадения в одной операции.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let usedRange = sheet.getUsedRange();
    let formulaRanges = usedRange.getSpecialCells(Excel.SpecialCellType.formulas);
    formulaRanges.format.fill.color = "pink";

    await context.sync();
});

Если в диапазоне нет ячеек с целевыми характеристиками, метод getSpecialCells выдает ошибку ItemNotFound. Эта ошибка перенаправляет поток управления в catch блок, если он есть. Если блока нет catch , ошибка останавливает метод .

Если вы ожидаете, что ячейки с целевой характеристикой всегда существуют, скорее всего, код должен выдавать ошибку, если эти ячейки отсутствуют. Если отсутствие соответствующих ячеек является допустимым сценарием, ваш код должен проверить наличие такой возможности и корректно выполнить действие без выдачи ошибки. Этого можно добиться с помощью getSpecialCellsOrNullObject метода и его возвращаемого isNullObject свойства. В следующем примере кода используется этот шаблон. Обратите внимание на следующие моменты:

• Метод getSpecialCellsOrNullObject всегда возвращает прокси-объект, поэтому он никогда не null используется в обычном смысле JavaScript. Но если соответствующие ячейки не обнаружены, свойству isNullObject объекта присваивается значение true.
• Он вызывает context.syncперед проверкой isNullObject свойства. Этот вызов является обязательным для всех *OrNullObject методов и свойств, так как для его чтения всегда требуется загружать и синхронизировать свойство. Однако явно загружать isNullObject свойство не требуется. Объект context.sync автоматически загружает его, даже если load не вызывается для объекта . Дополнительные сведения см. в разделе Методы и свойства *OrNullObject.
• Этот код можно проверить, выбрав сначала диапазон без ячеек с формулами и запустив его. Затем следует выбрать диапазон, содержащий по крайней мере одну ячейку с формулой, и снова запустить его.

await Excel.run(async (context) => {
    let range = context.workbook.getSelectedRange();
    let formulaRanges = range.getSpecialCellsOrNullObject(Excel.SpecialCellType.formulas);
    await context.sync();
        
    if (formulaRanges.isNullObject) {
        console.log("No cells have formulas");
    }
    else {
        formulaRanges.format.fill.color = "pink";
    }
    
    await context.sync();
});

Для простоты в остальных примерах в этой статье используется getSpecialCells.

Сужение целевых ячеек по типам значений ячеек

Методы Range.getSpecialCells() и Range.getSpecialCellsOrNullObject() принимают необязательный второй параметр, который можно использовать для дальнейшего сужения целевых ячеек. Этот второй параметр используется Excel.SpecialCellValueType для указания того, что нужны только ячейки, содержащие определенные типы значений.

Тестирование для ячеек с одним типом значений

Перечисление Excel.SpecialCellValueType включает в себя эти четыре основных типа, а также другие объединенные значения, описанные далее в этом разделе:

• Excel.SpecialCellValueType.errors
• Excel.SpecialCellValueType.logical (что означает логическое значение)
• Excel.SpecialCellValueType.numbers
• Excel.SpecialCellValueType.text

Следующий пример находит числовые константы и окрасит их в розовый цвет.

Ключевые моменты:

• Целевыми являются только литеральные числовые константы (не формулы, вычисляющие числа, ни логические, текстовые или ячейки ошибок).
• Чтобы проверить, заполните лист значениями числа литерала, другими типами литеральных значений и формулами.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let usedRange = sheet.getUsedRange();
    let constantNumberRanges = usedRange.getSpecialCells(
        Excel.SpecialCellType.constants,
        Excel.SpecialCellValueType.numbers);
    constantNumberRanges.format.fill.color = "pink";

    await context.sync();
});

Тестирование для ячеек с несколькими типами значений

Иногда требуется использовать несколько типов значений ячеек, например все ячейки с текстовым значением и все ячейки с логическим значением (Excel.SpecialCellValueType.logical). Для перечисления Excel.SpecialCellValueType существуют значения с объединенными типами. Например, Excel.SpecialCellValueType.logicalText предназначен для всех логических и всех текстовых ячеек. Excel.SpecialCellValueType.all — значение по умолчанию, которое не ограничивает возвращаемые типы значений ячеек. В следующем примере кода все ячейки окрашены формулами, которые создают число или логическое значение.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let usedRange = sheet.getUsedRange();
    let formulaLogicalNumberRanges = usedRange.getSpecialCells(
        Excel.SpecialCellType.formulas,
        Excel.SpecialCellValueType.logicalNumbers);
    formulaLogicalNumberRanges.format.fill.color = "pink";

    await context.sync();
});

Дальнейшие действия

• Объедините запросы специальных ячеек со строковым поиском для поиска и форматирования ячеек по текстовому содержимому.
• Примените форматирование, комментарии или проверку данных к полученному объекту RangeAreas .

См. также

• Объектная модель JavaScript для Excel в надстройках Office
• Работа с ячейками с помощью API JavaScript для Excel