Алгоритмы: улучшаем загрузку из эксель

Деяния юноши являются основой мудрости старика.

Неизвестный автор

Всем привет!

Напишу про алгоритмы и только.

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

Я и сам думал, что загрузка из эксель уже изъезженная тема, но при очередном решении задачи как-то само получилось решить некоторые моменты по-иному. Об этом и пойдет речь. 

Давайте сразу договоримся, что "загрузку из эксель" и "загрузку из макета" - будем считать одним и тем же процессом для заданной задачи. Более подробно я описал такой подход в статье Загрузка номенклатуры из Эксель. Новый взгляд. 

 1. Стандартно загрузка из эксель подразумевает последовательный обход строк и колонок таблицы (эксель или макета) для получения значений полей.

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

текПоле1 = 0; текПоле2 = 0; текПоле3 = 0; текПоле4 = 0;	
Для НомерСтроки = 2 По Макет.ВысотаТаблицы Цикл 
		
	Поле1 = СокрЛП(Макет.Область(НомерСтроки,1,НомерСтроки,1).Текст); 
	Поле2 = СокрЛП(Макет.Область(НомерСтроки,7,НомерСтроки,7).Текст);
	Поле3 = СокрЛП(Макет.Область(НомерСтроки,8,НомерСтроки,8).Текст);
	Поле4 = СокрЛП(Макет.Область(НомерСтроки,9,НомерСтроки,9).Текст);  
		
	Если текПоле1 = Поле1 И текПоле2 = Поле2 И текПоле3 = Поле3 И текПоле4 = Поле4 Тогда Продолжить; КонецЕсли;
		
	//обработка полей
	//...
		
	//в конце цикла обязательно запоминаем текущие значения полей
	текПоле1 = Поле1;
	текПоле2 = Поле2;
	текПоле3 = Поле3;
	текПоле4 = Поле4;

КонецЦикла;

При этом , как мы видим, сверяются всегда две строки, стоящие рядом, то есть 1 и 2 , 2 и 3, 3 и 4. При таком алгоритме не сверяются строки 1 и 3, 2 и 4, 1 и 4. Конечно, дополнительно можно упорядочить таблицу по всем сравниваемым полям. Но об этом нужно помнить.

Этот алгоритм я решил запрограммировать по-другому - создаю для каждой строки таблицы (эксель или макета) КлючСтроки - его же и сверяю при обходе. Смотрите, как теперь выглядит код.

МассивСтрок = Новый Массив;
Для НомерСтроки = 2 По Макет.ВысотаТаблицы Цикл 
		
	//сначала проверим задвоенность 	
	КлючСтроки = СокрЛП(Макет.Область(НомерСтроки,1,НомерСтроки,1).Текст) //номер в реестре
	+ СокрЛП(Макет.Область(НомерСтроки,7,НомерСтроки,7).Текст) //состояние 
	+ СокрЛП(Макет.Область(НомерСтроки,8,НомерСтроки,8).Текст) //номер
	+ СокрЛП(Макет.Область(НомерСтроки,9,НомерСтроки,9).Текст); //дата
		
	//проверим на новую запись или дубль
	Если МассивСтрок.Найти(КлючСтроки) = Неопределено Тогда 
         МассивСтрок.Добавить(КлючСтроки); 
    Иначе 
         Продолжить; 
    КонецЕсли;		

    //обработка строк в цикле
    //...

КонецЦикла;
			

После отладки код и вовсе записывается в пару строк.

МассивСтрок = Новый Массив;
Для НомерСтроки = 2 По Макет.ВысотаТаблицы Цикл 
		
	//проверим на новую запись или дубль по ключу строки 	
	КлючСтроки = СокрЛП(Макет.Область(НомерСтроки,1,НомерСтроки,1).Текст) + СокрЛП(Макет.Область(НомерСтроки,7,НомерСтроки,7).Текст) + СокрЛП(Макет.Область(НомерСтроки,8,НомерСтроки,8).Текст) + СокрЛП(Макет.Область(НомерСтроки,9,НомерСтроки,9).Текст); 
	
	Если МассивСтрок.Найти(КлючСтроки) = Неопределено Тогда МассивСтрок.Добавить(КлючСтроки); Иначе Продолжить; КонецЕсли;		
			
    //обработка строк в цикле
    //...
КонецЦикла;

Зафиксируем такой алгоритм решения задач как искусственное создание индекса (ключа строки) по полям структуры данных. 

 2. По задаче я загружал из эксель 3000 контрагентов, и сама по себе загрузка занимала длительное время. С помощью замера производительности выяснилось, что самая затратная операция при загрузке была на таком коде: 

Если НомерКолонки = 3 Тогда
ЗначениеЯчейки = Справочники.Контрагенты.НайтиПоРеквизиту("НомерВРеестре", Стр.НомерВРеестре);
КонецЕсли;

Здесь я решил опробовать другой способ получения контрагентов. Сначала создаю Соответствие.

СоответствиеКонтрагентов = Новый Соответствие;
Выборка = Справочники.Контрагенты.Выбрать();
Пока Выборка.Следующий() Цикл
    Если Выборка.НомерВРеестре>0 Тогда
        СоответствиеКонтрагентов.Вставить(Выборка.НомерВРеестре, Выборка.Ссылка);
    КонецЕсли;
КонецЦикла;

 Затем получаю контрагента из Соответствия.

Если НомерКолонки = 3 Тогда
    ЗначениеЯчейки = СоответствиеКонтрагентов.Получить(Стр.НомерВРеестре);
КонецЕсли;

После отладки код записываю в одну строку - платформа 1С быстрее формирует Соответствие и быстрее обрабатывает условие Если, когда код записан в одну строку.

//до цикла
СоответствиеКонтрагентов = Новый Соответствие;
Выборка = Справочники.Контрагенты.Выбрать();
Пока Выборка.Следующий() Цикл Если Выборка.НомерВРеестре>0 Тогда СоответствиеКонтрагентов.Вставить(Выборка.НомерВРеестре, Выборка.Ссылка); КонецЕсли; КонецЦикла;

//внутри цикла при обходе строк
Если НомерКолонки = 3 Тогда ЗначениеЯчейки = СоответствиеКонтрагентов.Получить(Стр.НомерВРеестре); КонецЕсли;

Зафиксируем такой алгоритм решения задач как замена поиска справочника по реквизиту на поиск по ключу Соответствия.

 3. Обработка строк в цикле зачастую происходит по однотипному алгоритму.

Стр.НомерВРеестре = СокрЛП(Макет.Область(НомерСтроки, 1, НомерСтроки, 1).Текст);

Для 17 полей прописывать похожие строки мне показалось трудоемкой задачей. Как оказалось в дальнейшем, количество полей увеличилось до 39, поскольку пришлось загружать три эксель в одну обработку для дальнейшего анализа. И следующий способ сильно облегчил написание кода.

//до цикла
СписокПолей = "НомерВРеестре, ИНН, Контрагент, Поле4, Поле5, Поле6, Поле7, Номер, Дата, Поле10, Поле11, Поле12, Поле13, Поле14, Поле15, Поле16, Поле17";
МассивПолей = СтрРазделить(СписокПолей, ","); //задаем массив полей
СоответствиеПолей = Новый Соответствие; //задаем соответствие полей
СоответствиеПолей.Вставить("НомерВРеестре", 1);
СоответствиеПолей.Вставить("ИНН", 2);
СоответствиеПолей.Вставить("Контрагент", 3);
СоответствиеПолей.Вставить("Поле4", 4);
СоответствиеПолей.Вставить("Поле5", 5);
СоответствиеПолей.Вставить("Поле6", 6);
СоответствиеПолей.Вставить("Поле7", 7);
СоответствиеПолей.Вставить("Номер", 8);
СоответствиеПолей.Вставить("Дата", 9);
СоответствиеПолей.Вставить("Поле10", 10);
СоответствиеПолей.Вставить("Поле11", 11);
СоответствиеПолей.Вставить("Поле12", 12);
СоответствиеПолей.Вставить("Поле13", 13);
СоответствиеПолей.Вставить("Поле14", 14);
СоответствиеПолей.Вставить("Поле15", 15);
СоответствиеПолей.Вставить("Поле16", 16);
СоответствиеПолей.Вставить("Поле17", 17);

//внутри цикла
Для НомерСтроки = 1 По Макет.ВысотаТаблицы Цикл

    //проверка на задвоенность ...

    //если не задвоено, то создадим запись
    Стр = СписокКонтрагентов.Добавить(); //табличная часть обработки
    Для Каждого Ключ Из МассивПолей Цикл

        Поле = СокрЛП(Ключ);
        НомерКолонки = СоответствиеПолей[Поле];
        ЗначениеЯчейки = СокрЛП(Макет.Область(НомерСтроки,НомерКолонки,НомерСтроки,НомерКолонки).Текст);
        Если ПустаяСтрока(ЗначениеЯчейки) Тогда Продолжить; КонецЕсли;
        Если НомерКолонки = 3 Тогда ЗначениеЯчейки = СоответствиеКонтрагентов.Получить(Стр.НомерВРеестре);
        ИначеЕсли НомерКолонки = 12 ИЛИ НомерКолонки = 13 ИЛИ НомерКолонки = 14 Тогда ЗначениеЯчейки = Булево(Число(ЗначениеЯчейки));
        ИначеЕсли НомерКолонки = 6 ИЛИ НомерКолонки = 9 ИЛИ НомерКолонки = 10 Тогда ЗначениеЯчейки = ДатаИзСтроки(ЗначениеЯчейки);
        КонецЕсли;
        Стр[Поле] = ЗначениеЯчейки; //число преобразуется автоматом
    КонецЦикла;
КонецЦикла;

Зафиксируем такой способ программирования как компактность кода засчёт заранее заданной структуры хранения полей эксель или макета. Еще такой способ называют методом табличного программирования.

 4. Очень хочется поделиться с вами еще с одним алгоритмом. Но он напрямую не связан с загрузкой данных из эксель или макета. Он связан с обработкой полученных данных. Когда у вас есть табличная часть, по которой вам надо найти определенные строки для дальнейшего анализа, вы используете метод табличной части НайтиСтроки().

Если исходная табличная часть у вас была отсортирована по каким-либо полям, то метод НайтиСтроки() вернет массив найденных строк не обязательно в том же отсортированном порядке.

Зная такой нюанс, вы всегда корректно решаете задачу. Я использую для сортировки найденных строк метод пузырька.  

ПараметрыПоиска = Новый Структура;
ПараметрыПоиска.Вставить("Контрагент", Стр.Контрагент);
ПараметрыПоиска.Вставить("Обработан", Ложь);
МассивНайденныхСтрок = Оплаты.НайтиСтроки(ПараметрыПоиска);

//упорядочим массив по номеру строки исходной табличной части
Колво = МассивНайденныхСтрок.Количество();
Для к=0 По Колво-2 Цикл
    Для р=1 По Колво-1 Цикл
        Если МассивНайденныхСтрок[к].НомерСтроки > МассивНайденныхСтрок[р].НомерСтроки Тогда
            Врем = МассивНайденныхСтрок[к];
            МассивНайденныхСтрок[к] = МассивНайденныхСтрок[р];
            МассивНайденныхСтрок[р] = Врем;
        КонецЕсли;
    КонецЦикла;
КонецЦикла;
 
//обработка строк в цикле
Для Каждого СтрМассива Из МассивНайденныхСтрок Цикл
    //...
    СтрМассива.Обработан = Истина;
    //...
КонецЦикла;

Если исходная табличная часть была отсортирована, например, по Контрагенту и Дате, то массив найденных строк можно сортировать по НомеруСтроки - результат будет одинаковым.

Алгоритм сортировки найденных строк можно записать в одну строку - так платформа быстрее его обработает.

ПараметрыПоиска = Новый Структура;
ПараметрыПоиска.Вставить("Контрагент", Стр.Контрагент); 
ПараметрыПоиска.Вставить("Обработан", Ложь);
МассивНайденныхСтрок = ОплатыКФ.НайтиСтроки(ПараметрыПоиска);
		
//упорядочим массив по номеру строки исходной табличной части
Колво = МассивНайденныхСтрок.Количество();
Для к=0 По Колво-2 Цикл Для р=1 По Колво-1 Цикл Если МассивНайденныхСтрок[к].НомерСтроки>МассивНайденныхСтрок[р].НомерСтроки Тогда Врем = МассивНайденныхСтрок[к]; МассивНайденныхСтрок[к] = МассивНайденныхСтрок[р]; МассивНайденныхСтрок[р] = Врем; КонецЕсли; КонецЦикла; КонецЦикла;
		

 Зафиксируем - метод НайтиСтроки() возвращает неотсортированные строки - упорядочивать надо самим. 

На этом все. Всем добра!

Программируйте алгоритмы разные и прекрасные!