Чистый текст электронной таблицы с многоразовым макросом ONLYOFFICE

Для многократной очистки электронных таблиц создайте небольшой макрос JavaScript в редакторе электронных таблиц, выберите только тот диапазон текста, который вы хотите изменить, и сначала протестируйте его на дубликате рабочей книги. Пример ниже удаляет непечатаемые символы и лишние пробелы из столбца с электронными письмами, сохраняя при этом все строки на своих местах.

Макросы ONLYOFFICE запускаются внутри редактора и используют его API JavaScript Office. В официальной документации рабочий процесс по-прежнему описывается как открытие окна «Вид» > «Макросы» , написание или вставка кода и его запуск в редакторе; никакие недавние изменения, указанные в текущей документации, не меняют этот базовый процесс. Сам макрос представляет собой иллюстративный, многократно используемый скрипт, а не отчет о тестировании программного обеспечения. См. краткое руководство по макросам ONLYOFFICE и справочник по вкладке «Вид» в редакторе электронных таблиц .

Подготовьте резервную копию и определите порядок очистки.

Предположим, рабочая книга содержит список контактов с заголовком в строке 1 и адресами электронной почты в столбце B, от B2 до B500. Некоторые импортированные значения могут содержать переносы строк, табуляцию или начальные и конечные пробелы. В этом примере очищается только этот текстовый столбец. Перед запуском кода отрегулируйте диапазон в соответствии с вашей таблицей.

• Сохраните отдельную копию рабочей книги или скопируйте рабочий лист. Оригинал следует хранить в неизмененном виде до проверки результатов.
• Убедитесь, что выбранный диапазон содержит необработанный текст, а не формулы, идентификаторы, зависящие от пробелов, или данные, для которых необходимо сохранять точное расстояние между символами.
• Не допускайте попадания строки заголовка в заданный диапазон. Макрос не распознает заголовки и не определяет, какие столбцы можно безопасно изменять.

Используйте диапазон, специфичный для столбца, например, B2:B500, вместо всего используемого диапазона. Удаление данных из каждой ячейки без разбора может изменить имена, адреса, коды или результаты формул не так, как вы предполагали.

Откройте редактор макросов и добавьте скрипт.

Откройте рабочую книгу в редакторе электронных таблиц ONLYOFFICE. Выберите «Вид» > «Макросы» , чтобы открыть редактор макросов, затем создайте макрос или замените начальный код в редакторе. Макросы ONLYOFFICE используют синтаксис JavaScript и API Office; это не макросы VBA. Рабочий процесс макроса в редакторе и поддерживаемые методы API описаны в официальном руководстве по началу работы с макросами .

Вставьте этот пример, а затем измените его B2:B500на фактический диапазон, содержащий только текст:

(function () {
    var sheet = Api.GetActiveSheet();
    var range = sheet.GetRange("B2:B500");
    var rows = range.GetValue();
    var functions = Api.WorksheetFunction;

    for (var i = 0; i < rows.length; i++) {
        var value = rows[i][0];
        if (typeof value === "string") {
            value = functions.CLEAN(value);
            rows[i][0] = value.replace(/^\s+|\s+$/g, "")
                              .replace(/\s{2,}/g, " ");
        }
    }

    range.SetValue(rows);
})();

Макрос считывает диапазон в двумерный массив, обрабатывает ячейки строки и записывает массив обратно в тот же диапазон. CLEANУдаляет непечатаемые символы; две замены с помощью регулярных выражений удаляют пробелы по краям и сокращают повторяющиеся пробелы внутри строки до одного пробела. Справочник API подтверждает, что GetValueможет возвращать двумерный массив для диапазона и SetValueпринимает массивы; справочники GetValue , SetValue и CLEAN описывают эти вызовы.

Поскольку скрипт записывает значения обратно, не указывайте ему ячейки, содержащие формулы: ячейки с формулами могут быть заменены их текущими вычисленными значениями. Он также не удаляет повторяющиеся записи, не стандартизирует регистр букв, не проверяет возможность доставки по адресу и не изменяет формат телефонных номеров. Это отдельные правила очистки, которые следует разрабатывать и тестировать независимо друг от друга.

Проведите замеры на контролируемом полигоне.

1. Ещё раз проверьте литерал диапазона. Если список заканчивается, например, на строке 240, используйте диапазон B2:B240 вместо обработки сотен неиспользуемых строк.
2. В редакторе макросов нажмите кнопку «Выполнить» . Если редактор выдаст синтаксическую ошибку, проверьте кавычки, круглые и фигурные скобки, прежде чем изменять диапазон или добавлять дополнительную логику.
3. Вернитесь к листу и проверьте несколько ячеек, включая значение, требующее исправления, значение, уже имеющее правильный формат, и пустую ячейку.

Начиная с узкого диапазона, эффект будет легче понять. После того, как результат будет правильным, вы можете повторно использовать макрос на другом листе, намеренно отредактировав диапазон. Не следует предполагать, что буква столбца или граница строки остаются действительными в другой рабочей книге.

Если команда «Макросы» отсутствует или код не может быть выполнен, проверьте, включены ли макросы в редакторе или отключены в конфигурации хост-приложения. В онлайн-редакторе администратор может управлять доступностью макросов; расширенные настройки редактора электронных таблиц описывают параметры макросов, доступные пользователю. Избегайте включения автоматического выполнения для файлов из неизвестных источников; запускайте только тот код, который вы понимаете.

Перед повторным использованием макроса проверьте результат.

Сравните очищенный столбец с исходным, а не оценивайте успех только по внешнему виду. Убедитесь, что каждый непустой адрес электронной почты по-прежнему находится в той же строке, ни один контакт не исчез, и единственные различия заключаются в символах и пробелах, которые вы хотели нормализовать. Проверьте образец в начале, середине и конце выбранного диапазона. Найдите пробел в начале или конце значения, если ваш редактор отображает его, и проверьте импортированные значения, которые ранее содержали переносы строк или управляющие символы.

Также сравните количество строк и любые зависимые формулы, фильтры или таблицы поиска, использующие этот столбец. Если результат формулы неожиданно изменился, отмените редактирование или восстановите рабочую копию; макрос следует ограничить ячейками с необработанным текстом. Для больших или критически важных для бизнеса рабочих книг сравните очищенный столбец с резервной копией или сначала используйте отдельный выходной столбец, чтобы изменения оставались доступными для просмотра.

Необходимо обеспечить повторяемость процесса очистки.

После успешного первого запуска сохраните рабочую книгу, чтобы макрос оставался доступным в документе в соответствии с рабочим процессом макросов редактора. Не следует предполагать, что макрос, хранящийся в одной рабочей книге, будет автоматически появляться в каждом новом файле. Для повторяющихся импортов используйте чистый шаблон или защищенную копию исходного файла, задокументируйте диапазон, который ожидает макрос, и обновляйте его при изменении макета листа. Макросы ONLYOFFICE полезны для детерминированных правил очистки; они не могут определить, является ли значение фактически правильным или уместно ли изменение для вашего набора данных.

Надежная схема проста: сохранить исходную копию, выбрать известный диапазон значений, выполнить одно определенное преобразование и сравнить результат, прежде чем делиться данными или импортировать их в другое место.