При загрузке данных из таблиц Excel разработчики часто сталкиваются с ситуацией, когда числовые реквизиты, такие как ИНН, считываются с пробелами между разрядами (например, "0 123 456 789"). При попытке найти контрагента по такому значению система 1С возвращает пустой результат, так как в базе ИНН хранится в виде сплошной строки цифр. Выясним причину такого поведения платформы и подробно разберем правильные методы решения этой задачи.
Проанализируем типовой фрагмент кода, в котором возникает ошибка:
КонтрагентИНН = СокрЛП(Лист.Cells(к, КолонкаИНН).Value);
Если ячейка в Excel отформатирована как числовая, свойство Value COM-объекта возвращает в 1С значение с типом Число. Функция СокрЛП() ожидает на входе строку. Когда в нее передается число, платформа выполняет неявное приведение типа по региональным стандартам информационной базы.
По умолчанию для чисел применяется группировка разрядов по три цифры, где в качестве разделителя выступает неразрывный пробел (код символа 160, системная константа Символы.НПП). Обычный строковый пробел (код 32) и неразрывный пробел визуально одинаковы, однако стандартные функции поиска и функции вида СтрЗаменить(Текст, " ", "") обычным пробелом не смогут очистить такую строку.
Кроме того, если ИНН начинается с нуля (характерно для ряда регионов РФ), числовой формат Excel безвозвратно теряет лидирующий ноль, делая 10-значный ИНН организации 9-значным, а 12-значный ИНН предпринимателя — 11-значным.
Наиболее надежный и платформенный путь обработки числового значения — явное форматирование с помощью функции Формат(). Параметр ЧГ=0 отключает группировку триад, а параметр ЧВН= позволяет вернуть ведущие нули при фиксации общей длины числа ЧЦ.
Посмотрим на пример получения очищенной строки без пробелов:
// Простое отключение группировки разрядов
ЗначениеЯчейки = Лист.Cells(к, КолонкаИНН).Value;
КонтрагентИНН = Формат(ЗначениеЯчейки, "ЧГ=0");
Если мы обрабатываем ИНН и требуется гарантировать сохранение ведущего нуля, применим форматную строку с указанием длины (10 знаков для юридических лиц или 12 знаков для физических лиц и ИП):
// Для юридического лица (10 знаков с ведущими нулями без группировки)
КонтрагентИНН = Формат(ЗначениеЯчейки, "ЧЦ=10; ЧВН=; ЧГ=0");
// Для индивидуального предпринимателя (12 знаков)
КонтрагентИНН = Формат(ЗначениеЯчейки, "ЧЦ=12; ЧВН=; ЧГ=0");
Если значение уже получено в виде строки с разделителями триад, удалим неразрывные пробелы напрямую. Для этого обратимся к константе Символы.НПП или функции Символ(160):
// Вариант с использованием системного перечисления
КонтрагентИНН = СтрЗаменить(СокрЛП(Лист.Cells(к, КолонкаИНН).Value), Символы.НПП, "");
// Вариант с явным указанием кода символа
КонтрагентИНН = СтрЗаменить(СокрЛП(Лист.Cells(к, КолонкаИНН).Value), Символ(160), "");
Если в строке могут присутствовать как неразрывные, так и обычные пробелы, выполним последовательную замену обоих символов:
КонтрагентИНН = СтрЗаменить(КонтрагентИНН, Символы.НПП, "");
КонтрагентИНН = СтрЗаменить(КонтрагентИНН, " ", "");
В объектной модели Excel свойство Text возвращает строку в том виде, в каком она отображается на экране монитора, с учетом установленного формата ячейки:
КонтрагентИНН = СокрЛП(Лист.Cells(к, КолонкаИНН).Text);
Важное ограничение: данный способ требует особой осторожности. Если ширина колонки в исходном файле Excel меньше ширины числа, свойство Text вернет строку, состоящую из символов решетки (например, "##########"). Используйте этот вариант только тогда, когда вы уверены в форматировании и ширине колонок исходного документа.
Разберем комплексную обработку, которая очищает строку от пробелов и восстанавливает потерянный лидирующий ноль, ориентируясь на длину ИНН:
ЧисловоеЗначение = Лист.Cells(к, КолонкаИНН).Value;
СтрИНН = Формат(ЧисловоеЗначение, "ЧГ=0");
СтрИНН = СтрЗаменить(СтрИНН, Символы.НПП, "");
СтрИНН = СтрЗаменить(СтрИНН, " ", "");
// Проверяем длину: 9 знаков превращаем в 10, а 11 — в 12
Если СтрДлина(СтрИНН) = 9 Тогда
КонтрагентИНН = "0" + СтрИНН;
ИначеЕсли СтрДлина(СтрИНН) = 11 Тогда
КонтрагентИНН = "0" + СтрИНН;
Иначе
КонтрагентИНН = СтрИНН;
КонецЕсли;
Построчное чтение ячеек через COM-объект на больших файлах выполняется крайне медленно, а процесс EXCEL.EXE может оставаться в оперативной памяти при возникновении исключений. Для повышения надежности и скорости работы учтем следующие правила:
Попытка ... Исключение ... КонецПопытки, обнуляя переменные значениями Неопределено.Cells(Строка, Колонка) прочитайте весь рабочий диапазон за один вызов в массив через Диапазон = Лист.Range(...).Value и обрабатывайте данные во внутреннем массиве 1С..xlsx и .xls без использования внешних библиотек и установленного MS Office на сервере используйте метод ТабличныйДокумент.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Текст). Это решение работает кроссплатформенно, в том числе на серверах под управлением Linux.