gifts2017

Иерархическая загрузка номенклатуры из Excel-файла

Опубликовал Игорь Лисицкий (lisrws) в раздел Обмен - Загрузка и выгрузка в Excel

Столкнулся с задачей загрузки данных из Excel-файла в 1С с учетом представленной в файле иерархией. Сам по себе процесс перекачки данных из экселя в 1с трудностей не представляет. В сети очень много обработок и примеров кода, которые позволяют общаться с Excel. Но облазив много форумов, я нигде не нашел внятного объяснения как произвести загрузку с учетом иерархии, при условии что количество уровней этой самой иерархии произвольно и разные уровни группировок находятся в одной колонке, а не каждый уровень в своей. Хочу поделиться тем, что получилось у меня. Может кому-то пригодится.

Глобальные переменные модуля:

Перем ExcelПоследняяСтрока;
Перем УстановкаЦен;
Перем ТипЦен;
Перем ВалютаТипаЦены;

Итак, обработка состоит из 3 процедур:

Первая процедура открывает окно для выбора файлика из которого будет идти загрузка.  Здесь всё предельно просто:

Процедура ФайлИмпортаНачалоВыбора(Элемент, СтандартнаяОбработка)
    Диалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
    Диалог.Заголовок = НСтр("ru='Укажите имя Excel-файла");
    Диалог.Фильтр = "(*.xls)|*.xls;*.xlsx" ;
    Если Диалог.Выбрать() Тогда
        ФайлИмпорта = Диалог.ПолноеИмяФайла;
    Иначе
        Возврат;
    КонецЕсли;
КонецПроцедуры

После выбора файла, при нажатии на кнопку дергается эта процедура:

Процедура ПрочитатьИзФайла() Экспорт
    
    Дерево = Новый ДеревоЗначений;
    Дерево.Колонки.Добавить("ЭтоГруппа");
    Дерево.Колонки.Добавить("Наименование");
    Дерево.Колонки.Добавить("Ссылка");
    
    ВыбФайл = Новый Файл(ФайлИмпорта);
    Если НЕ ВыбФайл.Существует() Тогда
        Сообщить("Файл не существует!");
        Возврат;
    КонецЕсли;
    
    Попытка
        Excel = Новый COMОбъект("Excel.Application");
        Excel.WorkBooks.Open(ФайлИмпорта);
        Состояние("Обработка файла Microsoft Excel...");
        ExcelЛист = Excel.Sheets(1);
    Исключение
        Сообщить("Ошибка. Возможно неверно указан номер листа книги Excel.");
        Возврат;
    КонецПопытки;
    ТекСтр = 1;
    Работник    = СокрЛп(ExcelЛист.Cells(ТекСтр,2).Value);
    
    xlCellTypeLastCell = 11;
    ExcelПоследняяСтрока = ExcelЛист.Cells.SpecialCells(xlCellTypeLastCell).Row;
    НоваяВетвь = Дерево.Строки.Добавить();
    НоваяВетвь.Наименование = СокрЛп(ExcelЛист.Cells(1,3).Value);
    НоваяВетвь.ЭтоГруппа = Истина;
    Ссылка = Справочники.Номенклатура.НайтиПоНаименованию(НоваяВетвь.Наименование, Истина);
    Если ЗначениеЗаполнено(Ссылка) Тогда
        НоваяВетвь.Ссылка = Ссылка;
    Иначе     
        НоваяГруппа = Справочники.Номенклатура.СоздатьГруппу();
        НоваяГруппа.Наименование = НоваяВетвь.Наименование;
        НоваяГруппа.Записать();
        НоваяВетвь.Ссылка = НоваяГруппа.Ссылка;
    КонецЕсли;
    ДобавитьВеткуВДерево(ExcelЛист, НоваяВетвь, 1, 2);
    
    Excel.WorkBooks.Close();
    Excel = 0;
    
КонецПроцедуры    

Здесь напишу немного пояснений. Сперва создаем дерево. В дерево мы помещаем иерархию из файла. В дереве можно создать необходимые для дальнейшей работы поля. Далее производим чтение из файла и инициализируем первый элемент нашего дерева.

Третья процедура - это рекурсивный обход по дереву. При обходе создаются группы и элементы в справочнике

Процедура ДобавитьВеткуВДерево(ExcelЛист, ТекущаяВетвь, ТекущийУровень, ТекСтр)
    
    Пока ТекСтр    Цикл

       ВетвьУдалили = Ложь;
        
        ПолученныйУровень = ExcelЛист.Rows(ТекСтр).OutlineLevel;
        Если ТекущийУровень < ПолученныйУровень Тогда
            // Попали в дочерний узел
            НоваяВетвь = ТекущаяВетвь.Строки.Добавить();
        ИначеЕсли ТекущийУровень > ПолученныйУровень Тогда
            // Закончили обход элементов старой группировки и перескочили на новую группировку
            НоваяВетвь = ТекущаяВетвь.Родитель.Родитель.Строки.Добавить();
        Иначе
            // Перебираем элементы текущей группировки
            НоваяВетвь = ТекущаяВетвь.Родитель.Строки.Добавить();
        КонецЕсли;
        // Если в 8 колонке этой строки ничего нет, то это группировка. Иначе - элемент
        Если СокрЛп(ExcelЛист.Cells(ТекСтр,8).Value) = "" Тогда
            НоваяВетвь.Наименование = СокрЛп(ExcelЛист.Cells(ТекСтр,3).Value);
            НоваяВетвь.ЭтоГруппа = Истина;
            // Группы ищем по наименованию. Если не нашли, то создаем
            НайденнаяГруппа = Справочники.Номенклатура.НайтиПоНаименованию(НоваяВетвь.Наименование, Истина);
            Если ЗначениеЗаполнено(НайденнаяГруппа) Тогда
                // Нашли. Такая группа уже есть в базе.
                // Проверим, совпадает ли родитель найденной группы с аналогичной группой из файла
                Если НоваяВетвь.Родитель <> Неопределено И НайденнаяГруппа.Родитель.Наименование <> НоваяВетвь.Родитель.Наименование Тогда
                    // Группы различаются. Нужно обновить группу элемента из базы
                    ОбъектНайденнойГруппы = НайденнаяГруппа.ПолучитьОбъект();
                    ОбъектНайденнойГруппы.Родитель = НоваяВетвь.Родитель.Ссылка;
                    Попытка
                        ОбъектНайденнойГруппы.Записать();
                    Исключение
                    КонецПопытки;
                КонецЕсли;
                НоваяВетвь.Ссылка = НайденнаяГруппа;
            Иначе
                // Такой группы нет в базе. Необходимо создать
                НоваяГруппа = Справочники.Номенклатура.СоздатьГруппу();
                НоваяГруппа.Наименование = НоваяВетвь.Наименование;
                НоваяГруппа.Родитель = НоваяВетвь.Родитель.Ссылка;
                НоваяГруппа.Записать();
                НоваяВетвь.Ссылка = НоваяГруппа.Ссылка;
            КонецЕсли;
        Иначе
            // Номенклатуру ищем по артикулу
            НайденныйЭлемент = Справочники.Номенклатура.НайтиПоРеквизиту("Артикул", СокрЛп(ExcelЛист.Cells(ТекСтр,3).Value));
            Если ЗначениеЗаполнено(НайденныйЭлемент) Тогда
                // Нашли. Такой элемент уже есть в базе.
                // Проверим, совпадает ли группа имеющегося товара с группой аналогичного из файла
                Если НайденныйЭлемент.Родитель.Наименование <> НоваяВетвь.Родитель.Наименование Тогда
                    // Группы различаются. Нужно обновить группу элемента из базы
                    ОбъектНайденногоЭлемента = НайденныйЭлемент.ПолучитьОбъект();
                    ОбъектНайденногоЭлемента.Родитель = НоваяВетвь.Родитель.Ссылка;
                    ОбъектНайденногоЭлемента.Записать();
                КонецЕсли;
            Иначе
                // Такого элемента нет в базе. Необходимо создать
                НовыйЭлемент = Справочники.Номенклатура.СоздатьЭлемент();
                НовыйЭлемент.Родитель = НоваяВетвь.Родитель.Ссылка;
                НовыйЭлемент.Наименование = СокрЛп(ExcelЛист.Cells(ТекСтр,4).Value);
                НовыйЭлемент.Артикул = СокрЛп(ExcelЛист.Cells(ТекСтр,3).Value);
                НовыйЭлемент.ВидНоменклатуры = Справочники.ВидыНоменклатуры.НайтиПоНаименованию("Товар", Истина);
                НовыйЭлемент.БазоваяЕдиницаИзмерения = Справочники.КлассификаторЕдиницИзмерения.НайтиПоНаименованию(СокрЛп(ExcelЛист.Cells(ТекСтр,8).Value));
                НовыйЭлемент.СтавкаНДС = Перечисления.СтавкиНДС.НДС0;
                НовыйЭлемент.Записать();
            КонецЕсли;
            
            // Элементы из дерева удаляем
            ТекущаяВетвь.Строки.Удалить(НоваяВетвь);
            ВетвьУдалили = Истина;
            
        КонецЕсли;
        
        ТекСтр = ТекСтр + 1;
        Если Не ВетвьУдалили Тогда
            ДобавитьВеткуВДерево(ExcelЛист, НоваяВетвь, ПолученныйУровень, ТекСтр);
        КонецЕсли;
        
    КонецЦикла;
    
КонецПроцедуры

Здесь самое важное - это свойство OutlineLevel: "ExcelЛист.Rows(ТекСтр).OutlineLevel;". Оно содержит уровень группировки для текущей строки. По нему можно определить входит ли данная строка в какую-то группировку или нет, а так же уровень иерархии, то есть количество группировок.

Ветви дерева, которые соответствуют элементам справочника, будем из дерева удалять, чтобы оно не разросталось до жутких размеров.

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Роман Ложкин (webester) 26.01.14 05:52
Когда вижу
На супероптимальность кода и быстродействие не претендую :)

читаю как
Честно говоря я понятия не имею, как это делается. поэтому сделал уж как получилось


Здесь самое важное - это свойство OutlineLevel: "ExcelЛист.Rows(ТекСтр).OutlineLevel;". Оно содержит уровень группировки для текущей строки. По нему можно определить входит ли данная строка в какую-то группировку или нет, а так же уровень иерархии, то есть количество группировок.

На этом можно было начать и закончить статью, новичок все равно код не разберет, а спец, все равно свой код писать будет.
Chibis; eaa; +2 Ответить 3
2. Olesya Беличенко (OlesyaBelochka) 10.06.14 14:07
Спасибо, статья пригодилась.
3. Анатолий Алексеев (widagon) 23.07.14 22:03
4. mpudy mpudy (mpudy) 23.07.14 23:39
Недавно делал подобное, ориентировался на количество табов)))
5. Ильдар Гиниятуллин (blackilidan) 27.09.14 12:29
Огромное спасибо. Побольше бы таких людей, которые любят делиться тем, что опробовали.
6. Змей Змеевич (Zmey_72) 14.11.14 11:16
статья интересная, но реализовать в свей обработке пришлось немного по-другому. И есть ошибки в коде:

Процедура ДобавитьВеткуВДерево(ExcelЛист, ТекущаяВетвь, ТекущийУровень, ТекСтр)

Пока ТекСтр ЦИКЛ
freeek; kot_dn; +2 Ответить 1
7. Мария Гаврилова (gavrilova_m) 25.11.14 11:22
Интересная обработка! Правда, как молодой и зеленый специалист, пытаюсь разобраться в ней.
8. Игорь Лисицкий (lisrws) 16.12.14 13:49
(6) Zmey_72, спасибо за найденную ошибку. подправил, но там немного не так.
9. Yuri Сергиевич (Yuri1C) 18.03.15 13:32
Ошибка, исправить нужно:

ПОКА ТекСтр < xlCellTypeLastCell ЦИКЛ
10. Алекс Алекс (akostrovdon) 22.03.15 22:25
Вот пример загрузки номенклатуры без деревьев(упр.форма), просто через Таблицу значений
Суть в том что уровни(группы) просто добавляем в массив по мере поступления.
Создаем группы которых нет и присваеваем их вновь созданным элементам, с учетом проверки уже существующих в базе.
Кто знает как OutlineLevel получить через ADODB??
Перебор элементов excel чушь полная если более 10 тыс. строк.


&Наклиенте
Процедура Команда1(Команда)
   
   	
	
	ОбъектЭксель	= Новый COMОбъект("Excel.Application");
	ФайлExcel		= ОбъектЭксель.Workbooks.Open(ФайлЗагрузки);
	ЛистExcel		= ФайлExcel.Sheets(1);
	КоличествоСтрок = ЛистExcel.UsedRange.Rows.Count;
		
	Для ном =НачатьСномераСтроки По КоличествоСтрок Цикл		
	 	
		ЗначениеАртикула = СокрЛП(ЛистExcel.Cells(ном,1).Value);
		Этогруппа=ложь;
		Если ЗначениеАртикула="" Тогда 			
			этоГруппа=истина;
		КонецЕсли;
		Если  СокрЛП(ЛистExcel.Cells(ном,2).Value)=""  Тогда
			Прервать;
		КонецЕсли;
		Наименование=СокрЛП(ЛистExcel.Cells(ном,2).Value);
		
		Цена=0;
		Если не Этогруппа тогда
		 Наименование=СтрЗаменить(Наименование,"-книжка","");
		 Наименование=СтрЗаменить(Наименование,"_"," ");
		 Наименование=СтрЗаменить(Наименование,"_"," ");
		 Наименование=СтрЗаменить(Наименование,"--","");
		 Наименование=СтрЗаменить(Наименование,"No Name","");
		 Наименование=СтрЗаменить(Наименование,"-футляр","");
		 Наименование=СтрЗаменить(Наименование," *","");
		Цена = ЧИСЛО(ЛистExcel.Cells(ном,5).Value); 	
		КонецЕсли;
		Если не Этогруппа тогда
			НайденнаяНоменклатура =найтипореквизиту(ЗначениеАртикула,"Артикул");
			Если ЗначениеЗаполнено(НайденнаяНоменклатура) тогда
				Наименование=НайденнаяНоменклатура;
			КонецЕСли;
		иначе
			НайденнаяГруппа= НайтиПоНаименованию(Наименование);
			
			Если ЗначениеЗаполнено(НайденнаяГруппа) тогда
				Наименование=НайденнаяГруппа;
			КонецЕСли;
		КонецЕСлИ;				
	 	нс= тз.Добавить();					 
	 	Если ЗначениеАртикула="" тогда
	    нс.группа=	Наименование;
		нс.Уровень=ОбъектЭксель.Rows(ном).OutlineLevel-1;
		иначе
		нс.Код=ЗначениеАртикула;
		Нс.Наименование=Наименование;
		нс.вхцена=Цена;
		нс.Цена= нс.вхцена*наценка;
		нс.Уровень=ОбъектЭксель.Rows(ном).OutlineLevel-1; 
		КонецЕслИ;				

КонецЦикла;
ОбъектЭксель.WorkBooks.Close();
ОбъектЭксель.Quit();

КонецПроцедуры

&Насервере

Процедура создатьэлементы()
	Массив=Новый Массив;
	
	
ПоследняяГруппа=Справочники.Номенклатура.ПустаяСсылка();
для каждого стр из тз цикл

		
если  значениезаполнено(стр.Наименование) тогда

Если ТипЗнч(стр.Наименование)=Тип("Строка") Тогда
спр=справочники.Номенклатура.СоздатьЭлемент();
спр.ЕдиницаИзмерения=константы.ЕдиницаИзмеренияОбъема.Получить();
спр.Наименование=стр.наименование;
спр.НаименованиеПолное=стр.наименование;
спр.Артикул=стр.код;
спр.СтавкаНДС=перечисления.СтавкиНДС.БезНДС;
спр.ВидНоменклатуры=справочники.ВидыНоменклатуры.НайтиПоНаименованию("Аксессуары");
спр.ВариантОформленияПродажи=Перечисления.ВариантыОформленияПродажи.РеализацияТоваровУслуг;
спр.ИспользованиеХарактеристик=перечисления.ВариантыИспользованияХарактеристикНоменклатуры.НеИспользовать;
спр.Родитель=ПоследняяГруппа;
спр.Записать();
конецесли;
конецесли;
если  значениезаполнено(стр.Группа) тогда
	Если ТипЗнч(стр.группа)=Тип("Строка") Тогда
     гр=справочники.Номенклатура.СоздатьГруппу();
     гр.Наименование=стр.группа;
	 
     Если стр.Уровень>1 тогда
	 Родитель=Массив[стр.Уровень-2];
	 гр.Родитель=Родитель;
	 КонецЕсли;
	 гр.Записать();	       
	 ПоследняяГруппа=гр.Ссылка;
	 		 
 иначе
	 ПоследняяГруппа=стр.группа;
 КонецЕсли; 
 
		 Если стр.Уровень>Массив.Количество() Тогда
			 Массив.Добавить(ПоследняяГруппа);
		 иначе
			 Массив.Установить(стр.Уровень-1,ПоследняяГруппа);
		 КонецЕслИ;
 
КонецЕсли;

Конеццикла;

конецпроцедуры

&Насервере
Функция   найтипореквизиту(имя,реквизит);
	 возврат Справочники.Номенклатура.НайтиПоРеквизиту(реквизит,имя);	 
 КонецФункции 
 
 Функция   найтипонаименованию(имя);
	 возврат Справочники.Номенклатура.НайтиПоНаименованию(имя,истина);
	 
 КонецФункции
...Показать Скрыть
tinkerbell; Sirgeli; +2 1 Ответить 1
11. Антон Рощин (wolfsoft) 28.10.15 17:36
Этогруппа=ложь; Если ЗначениеАртикула="" Тогда этоГруппа=истина; КонецЕсли;

При таком условии любой дурак код напишет.
13. Анна Медведева (tinkerbell) 29.06.16 08:54
(10) akostrovdon, неправильно работает, когда в группе после вложенной группы есть еще и элементы. Последнюю группу надо вычислять и для элемента тоже. Но за идею спасибо!
14. Антон Косяков (kosyakov_anton) 18.08.16 14:15
Есть возможность поделиться уже готовой обработкой?
15. varius82 18.11.16 22:17
Мой вариант:
Процедура ПрочитатьИзФайла(ПерваяСтрока) Экспорт
	
	ВыбФайл = Новый Файл(ФайлИмпорта);
	Если НЕ ВыбФайл.Существует() Тогда
		Сообщить("Файл не существует!");
		Возврат;
	КонецЕсли;
	
	Попытка
		Excel = Новый COMОбъект("Excel.Application");
		Excel.WorkBooks.Open(ФайлИмпорта);
		//Состояние("Обработка файла Microsoft Excel...");
		ExcelЛист = Excel.Sheets(1);
	Исключение
		Сообщить("Ошибка. Возможно неверно указан номер листа книги Excel.");
		Возврат;
	КонецПопытки;
	//первая значимая строка
	ТекСтр = ПерваяСтрока;
	
	xlCellTypeLastCell = 11;
	ExcelПоследняяСтрока = ExcelЛист.Cells.SpecialCells(xlCellTypeLastCell).Row;
	МассивУровней = Новый Массив;
	ТекущийУровень = 1;	
	
	ЕдИзм = Справочники.УпаковкиЕдиницыИзмерения.НайтиПоНаименованию("шт", Истина);
	ВидНоменклатуры = Справочники.ВидыНоменклатуры.НайтиПоНаименованию("Товар", Истина);
	ПапкаРодитель = Справочники.Номенклатура.ПустаяСсылка();

	Пока ТекСтр < ExcelПоследняяСтрока Цикл
		Наименование = СокрЛп(ExcelЛист.Cells(ТекСтр,3).Value);
		Артикул = СокрЛп(ExcelЛист.Cells(ТекСтр,2).Value);
		Цвет = ExcelЛист.Cells(ТекСтр,3).interior.ColorIndex;
		Если Наименование = "" И Артикул = "" Тогда
			ТекСтр = ТекСтр + 1;
			Продолжить;
		КонецЕсли;
		ЭтоГруппа = Ложь;
		
		Если Артикул = "" И Цвет = 19 Тогда
			ЭтоГруппа = Истина;		
		КонецЕсли;
		ПолученныйУровень = ExcelЛист.Rows(ТекСтр).OutlineLevel;
		Если ЭтоГруппа Тогда
			Родитель = Справочники.Номенклатура.ПустаяСсылка();
			
			Если ПолученныйУровень > 1 Тогда
				Родитель = МассивУровней[ПолученныйУровень-1];
			КонецЕсли;
			НоваяГруппа = Справочники.Номенклатура.СоздатьГруппу();
			НоваяГруппа.Наименование = Наименование;
			НоваяГруппа.Родитель = Родитель;
			НоваяГруппа.Записать();
			Ссылка = НоваяГруппа.Ссылка;
			МассивУровней.Вставить(ПолученныйУровень, Ссылка);
		Иначе
			Родитель = ПапкаРодитель;
			НовыйЭлемент = Справочники.Номенклатура.СоздатьЭлемент();
			НовыйЭлемент.ОбменДанными.Загрузка = Истина;
			НовыйЭлемент.Родитель = Родитель;
			НовыйЭлемент.Наименование = Наименование;
			НовыйЭлемент.Артикул = Артикул;
			НовыйЭлемент.ВидНоменклатуры = ВидНоменклатуры;
			НовыйЭлемент.ТипНоменклатуры = Перечисления.ТипыНоменклатуры.Товар;
			НовыйЭлемент.ВариантОформленияПродажи = Перечисления.ВариантыОформленияПродажи.РеализацияТоваровУслуг;
			НовыйЭлемент.ИспользованиеХарактеристик = Перечисления.ВариантыИспользованияХарактеристикНоменклатуры.НеИспользовать;
			НовыйЭлемент.ЕдиницаДляОтчетов = ЕдИзм;
			НовыйЭлемент.ЕдиницаИзмерения = ЕдИзм;
			НовыйЭлемент.СтавкаНДС = Перечисления.СтавкиНДС.БезНДС;
			НовыйЭлемент.Записать();
			
		КонецЕсли;
		
		ТекСтр = ТекСтр + 1;
		Если ЭтоГруппа Тогда
			ПапкаРодитель = Ссылка;
		КонецЕсли;
		
	КонеЦЦикла;
	
	Excel.WorkBooks.Close();
	Excel = 0;
	
КонецПроцедуры
...Показать Скрыть
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа