Загрузка из Excel в 1С при помощи VBA. Ускорение 200%.

Как-то возникла у меня необходимость анализа большого отчета, который выгружался из 1С в Excel. Это был такой монструозный отчет, что при выводе терялись данные, куча условий на выводе, которые вносили ошибку. Конечно, скажете вы, проще переделать отчет, чем его проверять, но на тот момент времени не было на переделку.

Сражу же пришел на ум вариант загрузить результаты отчет из Excel в 1С и произвести сверку данных путем простой выборки.

Взялся я загружать данные, а данные это около 5000 строк и 27 столбцов. Не помню сколько у меня заняло времени на загрузку, используя вызов Excel из 1С и загрузка данных с преобразованием. Но факт в том, что в то время пока загружались данные и отлаживалась загрузка, я задумался, почему же так долго происходит загрузка.

Начал копать Excel при помощи VBA и заметил что в VBA код выполняется быстрее, чем в 1С (обход ячеек, преобразование данных) и тут меня осенило… Ну а в чем заключался мой метод, я опишу ниже. Сразу оговорюсь, что подобного метода я не встречал, если кто-то встречал подобные статьи, прошу скинуть мне ссылочку.

Для демонстрации моего метода, разработана простейшая конфигурация, ее состав один справочник и обработка, создан файл Excel c 5000 строк.

Вид тестового файла

Строка1

Строка2

Строка3

Строка4

Строка5

Строка6

Строка7

Строка8

Строка9

Для указания файла сделана процедура

&НаКлиенте
Процедура ИмяФайлаНачалоВыбора(Элемент, ДанныеВыбора, СтандартнаяОбработка)
            Режим = РежимДиалогаВыбораФайла.Открытие;
            Диалог = Новый ДиалогВыбораФайла(Режим);
            Диалог.Фильтр="(*.xls)|*.xls";
            Диалог.МножественныйВыбор = Ложь;
            Если Диалог.Выбрать() ТОгда
                        ВыбранныеФайлы =            Диалог.ВыбранныеФайлы;
                        Если  ВыбранныеФайлы.Количество()>0 ТОгда
                                   ИмяФайла = ВыбранныеФайлы[0];          
                        КонецЕсли;
            КонецЕсли;  
КонецПроцедуры

Сначала я сделал стандартную загрузку из Excel.

&НаКлиенте
Процедура ВыполнитьПроцедуру(Команда)
            Excel = Новый COMОбъект("Excel.Application");
            Excel.Workbooks.Open(ИмяФайла);
            Sheet = Excel.ActiveWorkbook.ActiveSheet;
            СписокЗначение = Новый СписокЗначений;
            Для каждого стр из Sheet.UsedRange Цикл
                        СписокЗначение.Добавить(стр.Value);
            КонецЦикла;
            Excel.Application.Quit();
            ЗаписатьЭлементы(СписокЗначение);
КонецПроцедуры

Поскольку это тонкий клиент, запись элементов справочника происходит на сервере

&НаСервере
Процедура ЗаписатьЭлементы(СЗ)
            Для каждого стр из СЗ Цикл
                        Нов = Справочники.ПростойСправочник.СоздатьЭлемент();
                        нов.Наименование = стр.Значение;
                        нов.Записать();        
           КонецЦикла;
КонецПроцедуры

А вот теперь другая загрузка. Для нее нам понадобиться:

            1. Написать скрипт для выгрузки данных в текстовый файл;

            2. Написать код для загрузки данных из текстового файла.

Казалось бы все просто, но нет, мы не будем загружать данные из текстового файла, загрузку сделает сама  1С.

Текст скрипта на VBA

Sub Load(Filename As String)

Set fs = CreateObject("Scripting.FileSystemObject")

Set a = fs.CreateTextFile(Filename, True)

a.WriteLine ("СписокЗначений = Новый СписокЗначений;")

Set whs = ThisWorkbook.Worksheets(1)

For Each rw In ThisWorkbook.Worksheets(1).UsedRange.Rows

a.WriteLine ("СписокЗначение.Добавить("" + whs.Cells(rw.Row, 1).Value + "");")

Next

End Sub

Что он делает, создаем текстовый файл, имя его мы будем передавать из 1С.

И уже в текстовый файл пишем инструкции для 1С

СписокЗначение.Добавить("" + whs.Cells(rw.Row, 1).Value + "");

В этом скрипте можно не только записывать текстовые данные, но и, например, такие инструкции:

Справочники.Номенклатура.НайтиПоКоду("" + whs.Cells(rw.Row, 1).Value + "");

Т.е все зависит от целей.

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

&НаКлиенте
Процедура ЗагрузитьСкриптом(Команда)
            Excel = Новый COMОбъект("Excel.Application");
            Excel.Workbooks.Open(СокрЛП(ИмяФайла));
            Excel.DisplayAlerts = False ;
            Попытка
                        Pr   =Excel.Workbooks(1).VBProject;
            Исключение
                    Предупреждение("Включите ""Доверять доступ к Visual Basic Project"" (Меню [Сервис] - [Макрос>] - [Безопасность...], закладка ""Надежные источники""");
                        Возврат;        
            КонецПопытки;
                        Модуль=Pr.VBComponents.Add(1);
                        МакросСтрока =       ПолучитьТекстСкрипта();
                        Модуль=Pr.VBComponents.Add(1);
                        Модуль.CodeModule.AddFromString(МакросСтрока);
                        ИмяФайла = КаталогВременныхФайлов()+"Загрузка.txt";
                        Excel.Run("Load",ИмяФайла);
                        Pr.VBComponents.Remove(Модуль);
                        Excel.Application.Quit();
                        тд = Новый ТекстовыйДокумент;
                        тд.Прочитать(ИмяФайла);
                       СписокЗначение = Новый СписокЗначений;
                        Выполнить(тд.ПолучитьТекст());
                        ЗаписатьЭлементы(СписокЗначение);     


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

Функция ПолучитьТекстСкрипта()
Возврат ПолучитьОбщийМакет("Макет").ПолучитьТекст();      
КонецФункции

Что здесь происходит:

Создаем объект Excel, без этого ни куда )).

Для того что бы иметь возможность добавлять модуль в проект VBAProject необходимо установить «Доверять доступ к Visual Basic Project». В подсказке написано где это сделать.

Ну и все, устанавливаем скрипт, выполняем его и получаем файл с результатом.

Далее этот результат загружаем в текстовый документ и выполняем командой Выполнить. В результате выполнения кода из файла у нас создастся СписокЗначений с именем СписокЗначение. А поскольку в коде из файла объект называется так же, 1С поместит в этот объект результат выполнения кода.

Ну вот и все , а теперь все ради чего это делалось: сравнение производительности:

Тестирование производительности первым методом загрузки.

Тестирование производительности вторым методом загрузки.

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

 Удачи в Ваших начинаниях.

© neusro, 2013. При копировании и републикации статьи ссылка на первоисточник обязательна.