При разработке механизмов интеграции 1С с внешними файлами программисты часто сталкиваются с неприятной особенностью: при чтении числовых данных из ячеек Excel в строковые колонки таблицы значений 1С (например, артикулов или штрихкодов) в тексте появляются «лишние» пробелы. Например, вместо ожидаемого значения "147258" мы получаем "147 258". Это происходит из-за того, что Excel или операционная система автоматически форматируют число, добавляя разделитель групп разрядов.
В этой статье мы подробно разберем, почему это происходит, и рассмотрим несколько эффективных способов решения данной проблемы, от изменения свойств COM-объекта до альтернативных методов чтения файлов. Чтобы избежать самостоятельной разработки алгоритмов импорта, можно использовать универсальную обработку загрузки данных из Excel в справочники 1С.
Прежде чем приступать к коду, проанализируем ситуацию. Важно понимать, что символ, который мы видим как пробел, в большинстве случаев не является обычным пробелом (код ASCII 32). Чаще всего это неразрывный пробел (NBSP, код ASCII 160).
Когда мы обращаемся к ячейке Excel через COM-объект, используя свойство Value, Excel передает данные в формате Variant. Если в ячейке находится число, а в настройках операционной системы Windows (в региональных стандартах) в качестве разделителя групп разрядов установлен пробел, то при попытке 1С интерпретировать это значение как строку, платформа автоматически добавит эти разделители. Ситуация усугубляется тем, что визуально в самом Excel число может выглядеть монолитным, но программный интерфейс всё равно передает его с форматированием.
Самый простой и зачастую самый эффективный способ — изменить свойство, по которому мы получаем данные из ячейки. Вместо стандартного Value, которое возвращает типизированное значение, используем Text.
Свойство Text возвращает именно то, что отображается на экране в ячейке Excel, в виде строки. Если в Excel для ячейки задан текстовый формат или формат «Общий» без явных разделителей, данные попадут в 1С в чистом виде.
Рассмотрим пример кода:
// Вместо этого варианта:
// Значение = Лист.Cells(Строка, Колонка).Value;
// Используем этот:
Значение = СокрЛП(Лист.Cells(Строка, Колонка).Text);
Важное замечание: Свойство Text работает медленнее, чем Value, при обработке огромных массивов данных (десятки тысяч строк), так как Excel приходится вычислять визуальное представление каждой ячейки. Однако для большинства задач загрузки справочников это оптимальный выбор.
Если по каким-то причинам использование Text невозможно или не помогает (например, данные уже загружены в таблицу значений через метод ЗагрузитьКолонку), необходимо выполнить постобработку строк.
Как мы уже выяснили, обычная функция СтрЗаменить(Значение, " ", "") может не сработать, так как в строке находится неразрывный пробел. В 1С для этого существует специальная системная константа Символы.НПП.
Разберем пример функции для очистки данных:
Для Каждого СтрокаТЗ Из ТабПоле Цикл
// Удаляем неразрывные пробелы
ЧистоеЗначение = СтрЗаменить(СтрокаТЗ.Артикул, Символы.НПП, "");
// На всякий случай удаляем и обычные пробелы
ЧистоеЗначение = СтрЗаменить(ЧистоеЗначение, " ", "");
СтрокаТЗ.Артикул = ЧистоеЗначение;
КонецЦикла;
Этот метод гарантирует, что любые визуальные разделители будут удалены, и вы получите монолитную цифровую последовательность.
У объекта Range в Excel есть менее известное свойство Value2. В отличие от Value, оно не обрабатывает типы данных «Дата» и «Валюта». При использовании Value2 Excel передает данные в более «сыром» виде, что иногда позволяет избежать автоматического наложения региональных форматов группировки разрядов.
Посмотрим на пример обращения:
// Использование Value2 для минимизации форматирования
Значение = Excel.Cells(1, 1).Value2;
Этот способ рекомендуется использовать, если вы работаете с большими объемами числовых данных и хотите сохранить высокую скорость работы COM-соединения.
Иногда проблема кроется в самом исходном файле. Даже если вы выделите колонку и поменяете формат на «Текстовый», Excel не всегда применяет его немедленно к уже введенным данным. Часто требуется «войти» в каждую ячейку (нажать F2 и Enter), чтобы формат обновился.
Чтобы выполнить массовое преобразование в самом Excel без программирования, воспользуемся следующим алгоритмом:
После этой манипуляции все числа в колонке станут полноценными строками, и 1С считает их корректно даже через обычное свойство Value. Для автоматической смены форматов во всем документе можно применить утилиту изменения формата ячеек книги Excel на текстовый.
В исходном сообщении автор использует метод arr = WS.UsedRange.Value, который выгружает весь диапазон Excel в массив Variant, а затем загружает его в 1С. Это быстро, но именно в этот момент 1С применяет правила преобразования типов ко всему массиву сразу.
Если проблема с пробелами критична, попробуйте переписать алгоритм на построчное чтение. Это позволит контролировать получение значения из каждой конкретной ячейки:
ВсегоСтрок = WS.UsedRange.Rows.Count;
Для НомерСтроки = 1 По ВсегоСтрок Цикл
НоваяСтрока = ТабПоле.Добавить();
// Читаем как текст, чтобы избежать разделителей
НоваяСтрока.Артикул = СокрЛП(WS.Cells(НомерСтроки, 1).Text);
КонецЦикла;
Проанализируем современные альтернативы COM-объекту, которые лишены многих его недостатков:
Построитель.Прочитать() или ТабличныйДокумент.Прочитать(). При загрузке файла .xlsx напрямую в табличный документ 1С, платформа сама занимается парсингом XML-структуры файла, и зачастую делает это более корректно, чем COM-интерфейс Excel.Microsoft.ACE.OLEDB можно указать параметр IMEX=1 в строке подключения. Это заставит драйвер трактовать все данные в колонках как текст, полностью игнорируя числовое форматирование.Для быстрого исправления проблемы в существующем коде лучше всего заменить Value на Text в цикле обхода ячеек. Если же вы используете массовую загрузку через массив, обязательно добавьте этап очистки данных функцией СтрЗаменить(Значение, Символы.НПП, ""). Помните, что визуальный пробел в числах из Excel — это почти всегда результат региональных настроек системы, который легко обходится правильным выбором метода обращения к объекту Excel.