При работе с системой 1С:Предприятие 8 часто возникает необходимость загружать данные из внешних источников, таких как файлы Excel. Если ранее вы уже сталкивались с загрузкой данных в справочники, то переход к загрузке в регистры сведений может вызвать некоторые вопросы. В этой статье мы подробно разберем, как корректно выполнить такую загрузку, какие типичные ошибки могут возникнуть и как их избежать, а также рассмотрим альтернативные и более надежные подходы к чтению файлов Excel.
Предположим, у нас есть регистр сведений «ЦеныНаМатериалы» с измерением «Материал» (тип СправочникСсылка.Материалы) и ресурсом «Цена» (тип Число), а также файл Excel с двумя колонками: «Материал» и «Цена». Наша задача — загрузить данные из этого файла в регистр сведений. Давайте проанализируем исходный код, предложенный в сообщении 0, и выявим ключевые моменты, которые требуют исправления.
Исходный код для загрузки данных из Excel выглядит следующим образом:
&НаСервере
Процедура ВыбратьФайлНаСервере(ИмяФайла)
//подключаемся к эксель
Попытка
Excel = Новый COMОбъект("Excel.Application");
Excel.WorkBooks.Open(ИмяФайла);
Исключение
Сообщить("Ошибка при открытии файла с помощью Excel");
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;
//Получаем количество строк и колонок.
Excel.Sheets(1).Select();
ФайлСтрок = Excel.Cells(1,1).SpecialCells(11).Row;
ФайлКолонок = Excel.Cells(1,1).SpecialCells(11).Column;
//считываем первую строку и генерируем колонки на форме с наименованиями из файла
ТЗ = Новый ТаблицаЗначений;
Сч = 1;
Пока ЗначениеЗаполнено(Excel.Cells(1, Сч).Text) Цикл
ИмяКолонки = Excel.Cells(1, Сч).Text;
ИмяБезПробелов = СтрЗаменить(ИмяКолонки,",",""); // убираем из имени колонок пробелы
ТЗ.Колонки.Добавить(ИмяБезПробелов,,ИмяКолонки);
Сч = Сч + 1;
КонецЦикла;
//считываем тело файла эксель и построчно заносим данные в табличное поле
Для НС = 2 по ФайлСтрок Цикл // НС указываем с какой строки начинать обработку
НоваяСтрока = ТЗ.Добавить();
Для НК = 1 по ФайлКолонок Цикл
//заполняем строку значениями
ТекущееЗначение = Excel.Cells(НС, НК).Text;
ИмяКолонки = ТЗ.Колонки[НК-1].Имя;
НоваяСтрока[ИмяКолонки] = ТекущееЗначение;
КонецЦикла;
КонецЦикла;
Excel.DisplayAlerts = 0;
Excel.Quit();
Excel.DisplayAlerts = 1;
Для каждого Об из ТЗ Цикл
Менеджер = РегистрыСведений.ЦеныНаМатериалы.СоздатьМенеджерЗаписи();
Менеджер.Период = ТекущаяДата();
Менеджер.Материал = Справочники.Материалы.НайтиПоНаименованию("Материал");
Менеджер.Цена = Об.Цена;
Менеджер.Записать();
КонецЦикла;
КонецПроцедуры
&НаКлиенте
Процедура ВыбратьФайл(Команда)
Диалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
Если Диалог.Выбрать() Тогда
ИмяФайла = Диалог.ПолноеИмяФайла;
ВыбратьФайлНаСервере(ИмяФайла);
КонецЕсли;
КонецПроцедуры
Проблема, описанная автором: создается только одна строка, в которой поле «Материал» остается пустым, а «Цена» загружается из последней строки Excel. Давайте выясним причину такого поведения.
Ошибка в поиске элемента справочника: Самая очевидная и критичная ошибка кроется в строке:
Менеджер.Материал = Справочники.Материалы.НайтиПоНаименованию("Материал");
Здесь мы видим, что в качестве аргумента метода НайтиПоНаименованию() передается строковая константа "Материал", а не фактическое наименование материала из текущей строки файла Excel. Это означает, что система пытается найти в справочнике «Материалы» элемент, наименование которого строго равно "Материал". Если такого элемента нет (что скорее всего), метод вернет пустую ссылку (Неопределено). В итоге в регистр сведений будет записан материал с пустым значением, что может быть некорректно или привести к ошибкам уникальности измерений.
Как должно быть: Необходимо подставлять значение наименования материала из текущей обрабатываемой строки таблицы значений ТЗ, которая была сформирована из Excel. В нашем случае это Об.Материал, где Об — это текущая строка в цикле Для каждого Об из ТЗ Цикл.
Почему только одна строка: Автор указывает, что загружается только одна строка. В предоставленном коде цикл по ТЗ присутствует и должен был бы обрабатывать все строки. Вероятнее всего, проблема "одной строки" является следствием ошибки с поиском материала. Если измерение «Материал» является ключевым и регистр сведений периодический (по регистратору или просто периодический), то при попытке записи нескольких строк с одинаковым пустым "Материалом" и одинаковым "Периодом" (ТекущаяДата()) будет происходить перезапись одной и той же записи, а не создание новых. В итоге, останется только последняя записанная строка.
Преобразование типов: Обратите внимание, что значения из Excel считываются как текст: Excel.Cells(НС, НК).Text. Колонка «Цена» в Excel, хоть и выглядит как число, будет считана как строка. Перед записью в ресурс регистра сведений типа «Число» необходимо преобразовать строковое значение в числовое. Если этого не сделать, может возникнуть ошибка типа или неявное преобразование, которое не всегда работает корректно, особенно с разделителями дроби (запятая/точка).
Давайте внесем необходимые изменения в код, чтобы корректно загружать данные в регистр сведений «ЦеныНаМатериалы».
Во-первых, исправляем строку поиска материала и добавляем преобразование цены. Также рекомендуется проверять, найден ли материал. Если материал не найден, мы можем либо создать его, либо сообщить об ошибке и пропустить строку.
// ... (начало кода до цикла по ТЗ остается без изменений)
Для каждого Об из ТЗ Цикл
// 1. Поиск материала по наименованию из Excel
// Предполагаем, что колонка в Excel называется "Материал" или была переименована в ТЗ в "Материал".
// Важно: значение Об.Материал должно содержать актуальное наименование материала из текущей строки Excel.
МатериалИзExcel = СокрЛП(Об.Материал); // Обрезаем пробелы с краев
СсылкаНаМатериал = Справочники.Материалы.НайтиПоНаименованию(МатериалИзExcel);
Если СсылкаНаМатериал = Неопределено Тогда
Сообщить("Внимание! Материал '" + МатериалИзExcel + "' не найден в справочнике. Строка пропущена.", СтатусСообщения.Важное);
// Если требуется, можно создать новый элемент справочника:
// НовыйМатериалОбъект = Справочники.Материалы.СоздатьЭлемент();
// НовыйМатериалОбъект.Наименование = МатериалИзExcel;
// Попытка
// НовыйМатериалОбъект.Записать();
// СсылкаНаМатериал = НовыйМатериалОбъект.Ссылка;
// Исключение
// Сообщить("Ошибка при создании материала '" + МатериалИзExcel + "': " + ОписаниеОшибки(), СтатусСообщения.Важное);
// Продолжить; // Пропускаем текущую строку, если не удалось создать материал
// КонецПопытки;
Продолжить; // Пропускаем текущую строку, если материал не найден
КонецЕсли;
// 2. Создаем менеджер записи регистра сведений
Менеджер = РегистрыСведений.ЦеныНаМатериалы.СоздатьМенеджерЗаписи();
// 3. Заполняем измерения и ресурсы
// Если регистр периодический, период обязателен. Если не нужен конкретный период, можно использовать ТекущаяДата().
// Если период должен быть из Excel, то нужно добавить колонку "Период" в Excel и считывать ее.
Менеджер.Период = ТекущаяДата();
Менеджер.Материал = СсылкаНаМатериал;
// Преобразуем строковое значение цены в числовое.
// Используем СтрЧисло(), чтобы корректно обработать число из строки.
// Важно: в Excel разделителем дробной части может быть запятая или точка,
// СтрЧисло() обычно корректно обрабатывает обе ситуации в зависимости от региональных настроек 1С.
Попытка
Менеджер.Цена = СтрЧисло(Об.Цена);
Исключение
Сообщить("Ошибка преобразования цены '" + Об.Цена + "' для материала '" + МатериалИзExcel + "'. Строка пропущена.", СтатусСообщения.Важное);
Продолжить;
КонецПопытки;
// 4. Записываем запись регистра сведений
Попытка
Менеджер.Записать();
Исключение
Сообщить("Ошибка записи в регистр сведений для материала '" + МатериалИзExcel + "': " + ОписаниеОшибки(), СтатусСообщения.Важное);
Продолжить;
КонецПопытки;
КонецЦикла;
Сообщить("Загрузка данных завершена.");
// ... (конец кода остается без изменений)
Дополнительные рекомендации по работе с регистром сведений:
Уникальность записей: Для периодического регистра сведений уникальность записи определяется комбинацией значений измерений и периода. Если вы загружаете несколько цен на один и тот же материал в одну и ту же дату, то каждая последующая запись будет перезаписывать предыдущую. Если это нежелательно, необходимо либо обеспечить уникальность периода (например, использовать время вплоть до секунд), либо убедиться, что измерения регистра уникальны для каждой строки данных, которую мы хотим сохранить.
Массовая запись: Для больших объемов данных создание и запись каждого МенеджераЗаписи по отдельности может быть неэффективным. В таких случаях мы можем использовать объект НаборЗаписей. Мы можем заполнить всю таблицу значений ТЗ, а затем создать НаборЗаписей и загрузить в него данные из этой ТЗ.
// Пример использования НабораЗаписей для массовой записи
НаборЗаписей = РегистрыСведений.ЦеныНаМатериалы.СоздатьНаборЗаписей();
НаборЗаписей.Отбор.Период.Установить(ТекущаяДата()); // Очищаем записи за текущую дату, если нужно
// Или отбор по другим измерениям, если нужно удалить записи перед загрузкой
// Здесь нам понадобится сформировать ТЗ, структура которой соответствует регистру сведений
// (колонки: Период, Материал, Цена).
// Допустим, мы уже преобразовали ТЗ из Excel в ТЗРегистра, где есть СсылкиНаМатериалы и числовые Цены.
НаборЗаписей.Загрузить(ТЗРегистра);
НаборЗаписей.Записать();
Для использования НаборЗаписей.Загрузить(), ваша таблица значений должна иметь колонки, имена которых соответствуют именам измерений и ресурсов регистра сведений, а типы данных колонок должны быть совместимы с типами измерений и ресурсов.
Использование COM-объекта Excel.Application на сервере 1С:Предприятия имеет ряд ограничений и потенциальных проблем:
Гораздо более предпочтительным способом является использование встроенного в 1С объекта ТабличныйДокумент, который умеет читать файлы Excel (как .xls, так и .xlsx) и ODS-файлы без необходимости установки Microsoft Excel на сервере. Этот метод является более кроссплатформенным и надежным.
Давайте рассмотрим пример универсальной функции для загрузки таблицы значений из файла с помощью ТабличныйДокумент, предложенный в сообщении 8.
Функция ЗагрузитьТаблицуЗначенийИзФайла(ИмяФайла, Параметры = Неопределено) Экспорт
ТД = Новый ТабличныйДокумент();
ТД.Прочитать(ИмяФайла, СпособЧтенияЗначенийТабличногоДокумента.Текст);
ПараметрСтрокаШапки = 1; // По умолчанию шапка находится в первой строке
ПараметрОбязательныеПоля = Новый Массив(); // По умолчанию нет обязательных полей
Если Параметры <> Неопределено Тогда
Если Параметры.Свойство("СтрокаШапки") Тогда
ПараметрСтрокаШапки = Параметры.СтрокаШапки;
КонецЕсли;
Если Параметры.Свойство("ОбязательныеПоля") Тогда
ПараметрОбязательныеПоля = Параметры.ОбязательныеПоля;
КонецЕсли;
КонецЕсли;
МассивКолонок = Новый Массив();
МассивОбязательныхКолонок = Новый Массив();
ТЗ = Новый ТаблицаЗначений();
// Формируем колонки ТЗ на основе шапки файла Excel
Для ИндКол = 1 ПО ТД.ШиринаТаблицы Цикл
ТекстШапки = СокрЛП(ТД.Область(ПараметрСтрокаШапки, ИндКол).Текст);
Если ТекстШапки = "" Тогда
Продолжить;
КонецЕсли;
МассивКолонок.Добавить(ИндКол); // Индекс колонки в ТабличномДокументе
// Преобразуем имя колонки для использования в ТЗ (убираем недопустимые символы)
ИмяКолонки = ОставитьДопустимыеДляКлючаСтруктурыСимволы(ТекстШапки, "_");
ТЗ.Колонки.Добавить(ИмяКолонки);
// Отмечаем обязательные колонки
Для Каждого Эл ИЗ ПараметрОбязательныеПоля Цикл
Если Эл = ИмяКолонки Тогда
МассивОбязательныхКолонок.Добавить(ИндКол);
КонецЕсли;
КонецЦикла;
КонецЦикла;
// Считываем данные из тела файла Excel
Для ИндСтр = ПараметрСтрокаШапки + 1 По ТД.ВысотаТаблицы Цикл
ЕстьЗаполненныеОбязательные = Ложь;
// Проверяем, есть ли заполненные данные в обязательных полях текущей строки
Если МассивОбязательныхКолонок.Количество() > 0 Тогда
Для Каждого ИндКолОбяз ИЗ МассивОбязательныхКолонок Цикл
ТекстЯчейки = СокрЛП(ТД.Область(ИндСтр, ИндКолОбяз).Текст);
Если ТекстЯчейки <> "" Тогда
ЕстьЗаполненныеОбязательные = Истина;
Прервать;
КонецЕсли;
КонецЦикла;
Иначе // Если обязательных полей нет, считаем строку заполненной, если хоть что-то есть
Для Каждого ИндКолВсе ИЗ МассивКолонок Цикл
ТекстЯчейки = СокрЛП(ТД.Область(ИндСтр, ИндКолВсе).Текст);
Если ТекстЯчейки <> "" Тогда
ЕстьЗаполненныеОбязательные = Истина;
Прервать;
КонецЕсли;
КонецЦикла;
КонецЕсли;
Если НЕ ЕстьЗаполненныеОбязательные Тогда // Пропускаем пустые или невалидные строки
Продолжить;
КонецЕсли;
НСтр = ТЗ.Добавить();
Для СчКолонки = 0 По МассивКолонок.Количество() - 1 Цикл
ИндКол = МассивКолонок[СчКолонки];
ТекстЯчейки = СокрЛП(ТД.Область(ИндСтр, ИндКол).Текст);
НСтр[СчКолонки] = ТекстЯчейки;
КонецЦикла;
КонецЦикла;
Возврат ТЗ;
КонецФункции
Функция ОставитьДопустимыеДляКлючаСтруктурыСимволы(Ключ, ЗаменятьНедопустимыеНа = "")
ДопустимаяСтрока = "АБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890_";
Рез = "";
Для Инд = 1 По СтрДлина(Ключ) Цикл
Симв = Сред(Ключ, Инд, 1);
Если Найти(ДопустимаяСтрока, ВРег(Симв)) = 0 Тогда
Рез = Рез + ЗаменятьНедопустимыеНа;
Иначе
Рез = Рез + Симв;
КонецЕсли;
КонецЦикла;
Возврат Рез;
КонецФункции
Применение новой функции для загрузки:
Чтобы использовать эту функцию, нам нужно изменить клиент-серверную часть обработки. На клиенте мы выбираем файл, затем передаем его двоичные данные на сервер, где уже вызываем функцию ЗагрузитьТаблицуЗначенийИзФайла.
&НаКлиенте
Перем ФайлЗагрузки; // Поле формы, где хранится путь к выбранному файлу
Процедура Загрузить(Команда)
Диалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
Диалог.Заголовок = "Выберите файл для загрузки";
Диалог.Фильтр = "Файлы Excel (*.xlsx;*.xls)|*.xlsx;*.xls|Все файлы (*.*)|*.*";
Если Диалог.Выбрать() Тогда
ФайлЗагрузки = Диалог.ПолноеИмяФайла;
// Получаем двоичные данные файла, так как на сервере нет прямого доступа к файловой системе клиента
ДД = Новый ДвоичныеДанные(ФайлЗагрузки);
РасширениеФайла = ОбщегоНазначенияКлиентСервер.ПолучитьРасширениеФайла(ФайлЗагрузки);
// Вызываем серверную функцию для загрузки
ТЗЗагруженныхДанных = ЗагрузитьДанныеНаСервере(ДД, РасширениеФайла);
Если ТЗЗагруженныхДанных <> Неопределено Тогда
Сообщить("Данные успешно загружены из файла в таблицу значений. Начинаем запись в регистр.");
// Теперь используем ТЗЗагруженныхДанных для записи в регистр сведений
Для каждого Об из ТЗЗагруженныхДанных Цикл
// 1. Поиск материала
МатериалИзExcel = СокрЛП(Об.Материал); // Имя колонки "Материал" должно соответствовать шапке Excel
СсылкаНаМатериал = Справочники.Материалы.НайтиПоНаименованию(МатериалИзExcel);
Если СсылкаНаМатериал = Неопределено Тогда
Сообщить("Внимание! Материал '" + МатериалИзExcel + "' не найден. Строка пропущена.", СтатусСообщения.Важное);
Продолжить;
КонецЕсли;
Менеджер = РегистрыСведений.ЦеныНаМатериалы.СоздатьМенеджерЗаписи();
Менеджер.Период = ТекущаяДата();
Менеджер.Материал = СсылкаНаМатериал;
Попытка
Менеджер.Цена = СтрЧисло(Об.Цена); // Имя колонки "Цена" должно соответствовать шапке Excel
Исключение
Сообщить("Ошибка преобразования цены '" + Об.Цена + "' для материала '" + МатериалИзExcel + "'. Строка пропущена.", СтатусСообщения.Важное);
Продолжить;
КонецПопытки;
Попытка
Менеджер.Записать();
Исключение
Сообщить("Ошибка записи в регистр сведений для материала '" + МатериалИзExcel + "': " + ОписаниеОшибки(), СтатусСообщения.Важное);
Продолжить;
КонецПопытки;
КонецЦикла;
Сообщить("Запись в регистр сведений завершена.");
Иначе
Сообщить("Не удалось загрузить данные из файла.");
КонецЕсли;
КонецЕсли;
КонецПроцедуры
&НаСервере
Функция ЗагрузитьДанныеНаСервере(ДвоичныеДанныеФайла, РасширениеФайла)
// Сохраняем двоичные данные во временный файл на сервере
ИмяВременногоФайла = ПолучитьИмяВременногоФайла(РасширениеФайла);
ДвоичныеДанныеФайла.Записать(ИмяВременногоФайла);
ПараметрыТЗ = Новый Структура();
// Здесь мы можем указать обязательные поля, если это необходимо
// Например, если колонка "Материал" должна быть обязательно заполнена
// ОбязательныеПоля = Новый Массив();
// ОбязательныеПоля.Добавить("Материал"); // Имя колонки после преобразования
// ПараметрыТЗ.Вставить("ОбязательныеПоля", ОбязательныеПоля);
ТЗ = ЗагрузитьТаблицуЗначенийИзФайла(ИмяВременногоФайла, ПараметрыТЗ);
// Удаляем временный файл
УдалитьФайлы(ИмяВременногоФайла);
Возврат ТЗ;
КонецФункции
// ... (Функции ЗагрузитьТаблицуЗначенийИзФайла и ОставитьДопустимыеДляКлючаСтруктурыСимволы из предыдущего примера)
Таким образом, мы сначала загружаем данные из Excel в промежуточную таблицу значений на сервере, а затем, уже внутри клиентской процедуры (или другой серверной), обрабатываем эту таблицу значений для записи в регистр сведений. Этот подход является более гибким, масштабируемым и соответствует лучшим практикам разработки в 1С:Предприятие.
Используйте отладчик: Как правильно было отмечено в сообщении 1, отладчик — ваш лучший друг. Он позволяет пошагово отследить выполнение кода, проверять значения переменных и быстро находить ошибки.
Подготовка файла Excel: Всегда проверяйте формат данных в Excel. Убедитесь, что числовые значения (цены) действительно представлены как числа, а не как текст с некорректными символами. Наименования материалов должны быть точными, без лишних пробелов или опечаток, чтобы метод НайтиПоНаименованию() мог их обнаружить.
Обработка ненайденных материалов: Решите, что делать, если материал из Excel не найден в справочнике 1С. Вы можете либо создавать новые элементы справочника, либо пропускать такие строки и информировать пользователя о пропущенных позициях.
Типовые обработки: Не забывайте о существовании типовой обработки «ЗагрузкаДанныхИзТабличногоДокумента.epf» (доступна на ИТС). Она является универсальным инструментом для загрузки данных в различные объекты конфигурации и может сэкономить много времени, если она подходит для ваших задач.
Имена колонок: Убедитесь, что имена колонок в файле Excel точно соответствуют тем именам, которые вы используете в коде для доступа к данным таблицы значений (например, Об.Материал и Об.Цена). Если в Excel названия колонок содержат пробелы или специальные символы, функция ОставитьДопустимыеДляКлючаСтруктурыСимволы поможет привести их к валидному виду для имен колонок таблицы значений.
Следуя этим рекомендациям и исправлениям, мы сможем успешно загружать данные из файлов Excel в регистры сведений 1С, делая наши обработки более надежными и функциональными.