При разработке обработок загрузки данных из внешних файлов (например, Microsoft Excel) в среду 1С программисты часто сталкиваются с задачей заполнения не просто текстовых или числовых полей, а колонок, содержащих ссылочные типы данных (справочники, документы). Типичная ошибка новичков — попытка напрямую записать текстовую строку из ячейки Excel в колонку Таблицы Значений, которая должна содержать ссылку на элемент справочника. В результате данные либо вообще не загружаются, либо отображаются на форме некорректно.
В этой статье мы подробно разберем, как правильно организовать загрузку данных из Excel, выполнить типизацию колонок Таблицы Значений (ТЗ), сопоставить строковые наименования из файла со справочниками базы данных и оптимизировать весь процесс для достижения максимальной скорости работы.
Проанализируем ситуацию, описанную автором темы. В его исходном коде было сделано несколько критических ошибок:
Excel.Cells().Value. Платформа 1С блокирует запись неверного типа данных в типизированную колонку, либо визуально очищает ячейку на форме.Добавить(). Затем он запускал второй цикл для заполнения 2-й и 3-й колонок. Это крайне неэффективно. Все операции по заполнению одной строки (и простые значения, и ссылки) должны выполняться в рамках одного цикла.тпБюджетныеОперации[0][2] вне цикла небезопасно, так как таблица значений может быть пустой, что вызовет ошибку обращения по индексу.Давайте пошагово разберем, как спроектировать правильный и быстрый алгоритм загрузки.
Перед тем как заполнять Таблицу Значений данными из Excel, мы должны гарантировать, что ее колонки имеют строго определенные типы данных. Особенно это касается ссылочных типов. Для этого используется объект ОписаниеТипов.
Рассмотрим пример программного создания структуры Таблицы Значений:
// Создаем объект ТаблицаЗначений
тпБюджетныеОперации = Новый ТаблицаЗначений;
// Определяем типы данных для колонок-справочников
МассивТипаСтатьи = Новый Массив;
МассивТипаСтатьи.Добавить(Тип("СправочникСсылка.СтатьиДвиженияДенежныхСредств"));
ОписаниеТипаСтатьи = Новый ОписаниеТипов(МассивТипаСтатьи);
МассивТипаКонтрагенты = Новый Массив;
МассивТипаКонтрагенты.Добавить(Тип("СправочникСсылка.Контрагенты"));
ОписаниеТипаКонтрагенты = Новый ОписаниеТипов(МассивТипаКонтрагенты);
// Добавляем колонки в ТЗ с явным указанием типов
тпБюджетныеОперации.Колонки.Добавить("Контрагент", ОписаниеТипаКонтрагенты, "Контрагент");
тпБюджетныеОперации.Колонки.Добавить("СтатьяДДС", ОписаниеТипаСтатьи, "Статья движения денежных средств");
тпБюджетныеОперации.Колонки.Добавить("Сумма", Новый ОписаниеТипов("Число"), "Сумма");
Если ваша Таблица Значений уже размещена на форме как элемент управления, убедитесь, что в свойствах ее колонок в конфигураторе (или программно) задан соответствующий тип значения справочника. Это позволит элементам управления на форме корректно отображать ссылки и выбирать их интерактивно.
Теперь напишем единый цикл обхода строк файла Excel. Для сопоставления строкового представления из файла с объектом базы данных мы применим метод НайтиПоНаименованию() менеджера соответствующего справочника.
Проанализируем и переработаем алгоритм загрузки:
// Предположим, что COM-объект Excel уже успешно создан и файл открыт
Версия = Лев(Excel.Version, Найти(Excel.Version, ".") - 1);
Если Версия = "8" Тогда
ФайлСтрок = Excel.Cells.CurrentRegion.Rows.Count;
Иначе
ФайлСтрок = Excel.Cells(1, 1).SpecialCells(11).Row;
КонецЕсли;
// Начинаем обход со 2-й строки (первая обычно содержит заголовки)
Для НС = 2 По ФайлСтрок Цикл
Состояние("Обрабатывается строка " + Строка(НС) + " из " + Строка(ФайлСтрок));
ОбработкаПрерыванияПользователя(); // Возможность прервать процесс по Ctrl+Break
// Создаем ОДНУ новую строку в ТЗ для текущей строки Excel
НоваяСтрока = тпБюджетныеОперации.Добавить();
// --- Заполнение 2-й колонки (например, Контрагент) ---
// Читаем текстовое значение из 1-й колонки Excel
ИмяКонтрагента = СокрЛП(Excel.Cells(НС, 1).Value);
Если ЗначениеЗаполнено(ИмяКонтрагента) Тогда
СсылкаКонтрагент = Справочники.Контрагенты.НайтиПоНаименованию(ИмяКонтрагента, Истина);
Если СсылкаКонтрагент.Пустая() Тогда
// Если не нашли, можно записать сообщение пользователю или создать новый элемент
ОбщегоНазначения.СообщитьПользователю("Не найден контрагент: " + ИмяКонтрагента);
Иначе
НоваяСтрока.Контрагент = СсылкаКонтрагент;
КонецЕсли;
КонецЕсли;
// --- Заполнение 3-й колонки (например, Статья ДДС) ---
// Читаем текстовое значение из 2-й колонки Excel
ИмяСтатьи = СокрЛП(Excel.Cells(НС, 2).Value);
Если ЗначениеЗаполнено(ИмяСтатьи) Тогда
СсылкаСтатья = Справочники.СтатьиДвиженияДенежныхСредств.НайтиПоНаименованию(ИмяСтатьи, Истина);
Если СсылкаСтатья.Пустая() Тогда
ОбщегоНазначения.СообщитьПользователю("Не найдена статья ДДС: " + ИмяСтатьи);
Else
НоваяСтрока.СтатьяДДС = СсылкаСтатья;
КонецЕсли;
КонецЕсли;
// --- Заполнение остальных (числовых/текстовых) колонок, например, начиная с 8-й колонки ТЗ ---
// Допустим, числовые данные в Excel находятся в колонке 3
НоваяСтрока.Сумма = Excel.Cells(НС, 3).Value;
КонецЦикла;
Рассмотренный выше метод с использованием НайтиПоНаименованию() отлично работает на небольших файлах (до 100-200 строк). Однако, если в вашем файле Excel содержится 5 000 строк, обращение к базе данных на каждой итерации цикла приведет к колоссальным задержкам (база данных будет перегружена единичными запросами поиска).
Для оптимизации скорости загрузки мы применим паттерн "Кэширование результатов поиска" с использованием объекта Соответствие. Если в файле Excel названия контрагентов или статей ДДС повторяются, программа выполнит реальный поиск в базе данных только один раз для каждого уникального наименования, а в последующие разы мгновенно достанет ссылку из оперативной памяти.
Посмотрим, как выглядит оптимизированный код с использованием кэша:
// Создаем таблицы соответствия (кэш в оперативной памяти)
КэшКонтрагентов = Новый Соответствие;
КэшСтатейДДС = Новый Соответствие;
Для НС = 2 По ФайлСтрок Цикл
ОбработкаПрерыванияПользователя();
НоваяСтрока = тпБюджетныеОперации.Добавить();
// --- Оптимизированный поиск Контрагента ---
ИмяКонтрагента = СокрЛП(Excel.Cells(НС, 1).Value);
Если ЗначениеЗаполнено(ИмяКонтрагента) Тогда
// Проверяем, есть ли уже этот контрагент в нашем кэше
СсылкаКонтрагент = КэшКонтрагентов[ИмяКонтрагента];
Если СсылкаКонтрагент = Неопределено Тогда
// Элемента нет в кэше, выполняем поиск в базе данных
СсылкаКонтрагент = Справочники.Contragents.НайтиПоНаименованию(ИмяКонтрагента, Истина);
// Запоминаем результат поиска в кэш (даже если ссылка пустая, чтобы не искать повторно)
КэшКонтрагентов.Вставить(ИмяКонтрагента, СсылкаКонтрагент);
КонецЕсли;
НоваяСтрока.Контрагент = СсылкаКонтрагент;
КонецЕсли;
// --- Оптимизированный поиск Статьи ДДС ---
ИмяСтатьи = СокрЛП(Excel.Cells(НС, 2).Value);
Если ЗначениеЗаполнено(ИмяСтатьи) Тогда
СсылкаСтатья = КэшСтатейДДС[ИмяСтатьи];
Если СсылкаСтатья = Неопределено Тогда
СсылкаСтатья = Справочники.СтатьиДвиженияДенежныхСредств.НайтиПоНаименованию(ИмяСтатьи, Истина);
КэшСтатейДДС.Вставить(ИмяСтатьи, СсылкаСтатья);
КонецЕсли;
НоваяСтрока.СтатьяДДС = СсылкаСтатья;
КонецЕсли;
// Заполнение числовых данных
НоваяСтрока.Сумма = Excel.Cells(НС, 3).Value;
КонецЦикла;
Использование этого несложного механизма кэширования позволяет ускорить загрузку больших файлов в десятки раз!
Если перед вами стоит задача регулярной и гибкой загрузки различных файлов Excel силами пользователей, разрабатывать собственный код с нуля не всегда целесообразно. В поставку дисков ИТС и в состав многих типовых конфигураций 1С входит мощная универсальная обработка "Загрузка данных из табличного документа".
Преимущества использования этой готовой обработки:
Для разовых или сложных кастомных задач программирования используйте приведенные выше инструкции по типизации ТЗ и кэшированию поиска. Это сделает ваш код надежным, быстрым и простым в сопровождении.