При импорте данных из файлов Microsoft Excel в информационные базы «1С:Предприятие» разработчики часто сталкиваются с неприятной особенностью: коды номенклатуры, штрихкоды или артикулы, содержащие лидирующие (ведущие) нули (например, 000555 или 00001234), при загрузке превращаются в обычные числа (555 или 1234). В результате нарушается уникальность кодирования, и сопоставить загружаемые объекты с существующей базой становится невозможно.
В этой статье мы подробно разберем, почему так происходит, проанализируем различные подходы к решению этой проблемы и рассмотрим пять практических способов загрузки данных с гарантированным сохранением лидирующих нулей.
Проблема кроется в механизме определения типов данных. По умолчанию программа Microsoft Excel пытается облегчить жизнь пользователю и автоматически преобразует любые последовательности цифр в числовой формат. Когда 1С обращается к файлу Excel, она считывает типизированное значение ячейки. Если ячейка распознана как число, то лидирующие нули физически отсутствуют в значении переменной, и последующее преобразование в тип Строка в коде 1С уже не вернет утерянные нули обратно.
Давайте пошагово разберем, как решить эту задачу на стороне 1С с использованием различных технологий.
---
Если в вашей обработке загрузка реализована классическим способом через COM-соединение (Excel.Application), то наиболее простым и быстрым решением будет изменение свойства, по которому считывается значение ячейки.
Чаще всего разработчики используют свойство Value (или Value2), которое возвращает внутреннее вычисленное значение ячейки в виде числа. Чтобы получить данные ровно в том виде, в котором они отображаются на экране (со всеми нулями и форматированием), необходимо использовать свойство Text.
Рассмотрим пример кода, демонстрирующий это различие:
// Создаем подключение к COM-объекту Excel
Попытка
ExcelПриложение = Новый COMОбъект("Excel.Application");
ExcelКнига = ExcelПриложение.Workbooks.Open(ПутьКФайлу);
ExcelЛист = ExcelКнига.Sheets(1);
Исключение
Сообщить("Не удалось запустить MS Excel или открыть файл: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
// Неправильный подход: вернет число 555
АртикулНеверно = ExcelЛист.Cells(Строка, Колонка).Value;
// Правильный подход: вернет строку "000555"
АртикулВерно = СокрЛП(ExcelЛист.Cells(Строка, Колонка).Text);
Преимущества: Простота реализации. Достаточно заменить одно свойство в коде существующей обработки.
Недостатки: COM-соединение требует установленного Microsoft Excel на рабочем месте пользователя или сервере (что невозможно на серверах под управлением Linux) и работает относительно медленно на больших объемах данных.
---
Современным стандартом разработки в среде «1С:Предприятие 8.3» является использование встроенных механизмов платформы. Мы можем прочитать файл Excel напрямую в объект ТабличныйДокумент без привлечения внешних библиотек. Это работает быстро, стабильно и полностью кроссплатформенно.
Чтобы платформа не пыталась преобразовать ячейки к числам, при чтении файла необходимо явно указать способ получения значений ячеек с помощью системного перечисления СпособЧтенияЗначенийТабличногоДокумента.Текст.
Проанализируем пример кода:
ТабДок = Новый ТабличныйДокумент;
// Указываем способ чтения именно как Текст
Попытка
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Текст);
Исключение
ВызватьИсключение "Ошибка чтения файла: " + ОписаниеОшибки();
КонецПопытки;
КоличествоСтрок = ТабДок.ВысотаТаблицы;
// Обходим таблицу и считываем данные
Для НомерСтроки = 1 По КоличествоСтрок Цикл
// Свойство Текст гарантированно вернет строку с сохранением лидирующих нулей
Артикул = ТабДок.Область(НомерСтроки, НомерКолонкиАртикула).Текст;
Артикул = СтрЗаменить(Артикул, Символы.НПП, ""); // Удаляем неразрывные пробелы, если они есть
// Дальнейшая обработка полученного артикула
КонецЦикла;
Преимущества: Высокая скорость работы, отсутствие зависимости от MS Excel, корректная работа на серверах Linux, простота отладки.
---
Для загрузки файлов очень большого объема часто используют технологию ADODB, которая позволяет работать с листом Excel как с базой данных, выполняя к нему SQL-запросы. По умолчанию драйвер сканирует первые несколько строк таблицы для автоматического определения типов колонок. Если первые строки содержат цифры, драйвер посчитает колонку числовой и усечет лидирующие нули в последующих строках.
Чтобы принудительно заставить драйвер считывать все смешанные типы данных как текст, мы должны использовать специальный параметр IMEX=1 в строке подключения.
Рассмотрим пример создания такого подключения:
СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;""";
Connection = Новый COMОбъект("ADODB.Connection");
Recordset = Новый COMОбъект("ADODB.Recordset");
Попытка
Connection.Open(СтрокаПодключения);
// Выбираем данные с Листа1
Recordset.Open("SELECT * FROM [Лист1$]", Connection);
Исключение
Сообщить("Ошибка подключения через ADO: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
Пока Не Recordset.EOF() Цикл
// Благодаря IMEX=1 значение вернется как строка с нулями
Артикул = Строка(Recordset.Fields("Артикул").Value);
Recordset.MoveNext();
КонецЦикла;
Recordset.Close();
Connection.Close();
Преимущества: Идеально подходит для работы с таблицами, содержащими сотни тысяч строк, обеспечивая максимальную скорость выполнения запросов.
---
Файлы современного формата .xlsx представляют собой обычные ZIP-архивы, внутри которых находятся XML-файлы со структурой книги и страниц. Все уникальные текстовые строки книги хранятся в одном общем файле sharedStrings.xml в их исходном текстовом виде (именно как "000555"), а на листах хранятся лишь ссылки на индексы этих строк.
Для сверхбыстрой загрузки гигантских файлов без потери структуры данных можно программно разархивировать файл и прочесть XML-файлы напрямую.
Разберем логику процесса по шагам:
ЧтениеZipФайла для распаковки файла во временный каталог.ЧтениеXML для последовательного чтения файла sharedStrings.xml, где хранятся исходные тексты ячеек.sheet1.xml).Поскольку к данным не применяются механизмы неявного приведения типов MS Excel, артикулы гарантированно сохранят все свои ведущие нули.
---
Если пользователи перед загрузкой открывают и редактируют файлы вручную, Excel может «молча» срезать нули еще до того, как файл попадет в 1С. Чтобы предотвратить такое поведение программы на рабочих местах пользователей, в современных версиях MS Excel (Microsoft 365, Excel 2024 и новее) можно отключить эту опцию.
Выполним следующие настройки в Excel:
После этого Excel перестанет автоматически портить текстовые артикулы при открытии CSV и текстовых файлов пользователями.
---
Для выбора оптимального решения проанализируем архитектуру вашей системы: