Регулярно возникают задачи по парсингу файлов эксель. Тема хорошо изучена, однако с некоторой периодичностью возникают нюансы и приходится совершенствовать базовый механизм. В моем случае возникла необходимость закачать в регистр сведений много объемных файлов (~ 1 млн. строк). На загрузку одного файла существующим алгоритмом у меня уходило неприлично большое время - несколько часов. Пришла идея распараллелить запись, путем формирования фоновых заданий, каждое из которых сбрасывает свою порцию данных. В итоге, скорость закачки существенно увеличилась - на один файл время загрузки составило 20-30 мин.
(Кстати, в ряде случаев, существенно помогла и программная оптимизация файла - программное удаление пустых столбцов, которые 1С воспринимала как значимые.)
1. Был создан регистр сведений, структура измерений которого совпадает со структурой столбцов исходного файла (чтобы не описывать явным образом типизацию столбцов ТЗ):
ДанныеИзФайла = РегистрыСведений.А.СоздатьНаборЗаписей().Выгрузить();
2. Собственно открытие, оптимизация и чтение данных файла эксель:
Попытка
Excel = Новый COMОбъект("Excel.Application");
Исключение
Excel = Неопределено;
Возврат;
КонецПопытки;
Excel.FileValidation = 1;
Попытка
РабочаяКнига = Excel.WorkBooks;
РабочаяКнига.Open(СокрЛП(ИмяФайла));
Исключение
Excel = Неопределено;
Возврат;
КонецПопытки;
КоличествоЛистов = Excel.WorkSheets.Count();
Лист = Excel.WorkSheets(1);
ИспользуемыйЛист = Лист.UsedRange;
КоличествоЯчеек = Excel.Worksheets(1).Cells(1,1).SpecialCells(11).Row;
ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column;
ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;
Область = Лист.Range(Лист.Cells(1,1), Лист.Cells(ВсегоСтрок,ВсегоКолонок));
//удаление заведомо неиспользуемых столбцов (за НомерМаксимальнойКолонки)
Если НомерМаксимальнойКолонки < ВсегоКолонок Тогда
ОбластьПустых = Лист.Range(Лист.Cells(1,НомерМаксимальнойКолонки+1), Лист.Cells(ВсегоСтрок,ВсегоКолонок));
ОбластьПустых.Delete(-4159);
КонецЕсли;
Попытка
Данные = Область.Value.Выгрузить();
Excel.ActiveWorkbook.Close(Ложь);
Исключение
Excel = Неопределено;
Возврат;
КонецПопытки;
Попытка
Excel.QUIT();
Excel = Неопределено;
Исключение
Excel = Неопределено;
Возврат;
КонецПопытки;
3. Цикл обработки считанных данных:
СчетчикСбросаДанныхВКэш = 0;
ДанныеИзФайлаКоличество = 0;
ВсегоСтрок = Данные[0].Количество();
Для ТекНомерСтрокиФайла = Ексел_НомерСтрокиНачало-1 По ВсегоСтрок-1 Цикл
Если СчетчикСбросаДанныхВКэш = ЧислоЗаписейДляСброса Тогда
ВызватьСопоставитьНаСервере_ДобавитьВРС(ДатаДанных, ДанныеИзФайла, НомерПервойСтрокиПорции,МассивКлючейФоновыхЗаданий);
ДанныеИзФайлаКоличество = ДанныеИзФайлаКоличество + ДанныеИзФайла.Количество();
ДанныеИзФайла.Очистить();
СчетчикСбросаДанныхВКэш = 0;
НомерПервойСтрокиПорции = НомерПервойСтрокиПорции + ЧислоЗаписейДляСброса;
КонецЕсли;
СчетчикСбросаДанныхВКэш = СчетчикСбросаДанныхВКэш + 1;
НовСтр = ДанныеИзФайла.Добавить();
Для j = 0 По ВсегоКолонок - 1 Цикл
Если j = ВсегоКолонок - 1 Тогда //запись ресурса РС, в моем случае Количество
НовСтр.Количество = Данные[j][ТекНомерСтрокиФайла];
Иначе
НовСтр[ДанныеИзФайла.Колонки[j+1].Имя] = Данные[j][ТекНомерСтрокиФайла];
КонецЕсли;
КонецЦикла;
КонецЦикла;
4. Финальный сброс данных
ВызватьСопоставитьНаСервере_ДобавитьВРС(ДатаДанных, ДанныеИзФайла, НомерПервойСтрокиПорции,МассивКлючейФоновыхЗаданий);
ДанныеИзФайлаКоличество = ДанныеИзФайлаКоличество + ДанныеИзФайла.Количество();
//ждем выполнения всех фоновых, с ним связанных
НеВсеВыполнены = Истина;
Пока НеВсеВыполнены Цикл
_ОбщиеФункции.Задержка(20);
МассивФоновыхЗаданий = ФоновыеЗадания.ПолучитьФоновыеЗадания();
Для Каждого Эл Из МассивФоновыхЗаданий Цикл
Индекс_ = МассивКлючейФоновыхЗаданий.Найти(Эл.Ключ);
Если Индекс_ <> Неопределено И Эл.Состояние <> СостояниеФоновогоЗадания.Активно Тогда
МассивКлючейФоновыхЗаданий.Удалить(Индекс_);
КонецЕсли;
НеВсеВыполнены = МассивКлючейФоновыхЗаданий.Количество() > 0;
КонецЦикла;
КонецЦикла;
Jтдельная процедура по формированию и вызову фонового задания для сброса данных:
&НаСервере
Процедура ВызватьСопоставитьНаСервере_ДобавитьВРС(ДатаОтбора,ТЗ_Загруженная,НачальныйНомерСтроки,МассивКлючейФоновыхЗаданий)
Структура = Новый Структура;
Структура.Вставить("ДатаОтбора", ДатаОтбора);
Структура.Вставить("ТЗ_Загруженная", ТЗ_Загруженная);
Структура.Вставить("НачальныйНомерСтроки", НачальныйНомерСтроки);
ПараметрыВыполнения = Новый Массив;
ПараметрыВыполнения.Добавить(Структура);
КлючФоновогоЗадания = "_"+Формат(ДатаОтбора,"ДФ=dd.MM.yy")+"_"+НачальныйНомерСтроки+"_/"+Формат(ТекущаяДата(),"ДФ=с.мс");
МассивКлючейФоновыхЗаданий.Добавить(КлючФоновогоЗадания);
ФоновыеЗадания.Выполнить("_ОбщиеФункции.СопоставитьНаСервере_ДобавитьВРС",ПараметрыВыполнения,КлючФоновогоЗадания,КлючФоновогоЗадания);
КонецПроцедуры
В моем случае _ОбщиеФункции - это общий модуль со свойствами "Сервер", "Вызов сервера".
Процедура СопоставитьНаСервере_ДобавитьВРС(Параметры) Экспорт
ДатаОтбора = Параметры.ДатаОтбора;
ТЗ_Загруженная = Параметры.ТЗ_Загруженная;
НачальныйНомерСтроки = Параметры.НачальныйНомерСтроки;
ЗаписыватьИзменения = Истина; //ЗначениеЗаполнено(ИмяФайла);
НомерСтроки = НачальныйНомерСтроки;
Если ТЗ_Загруженная.Количество() = 0 Тогда
Возврат;
КонецЕсли;
Колонки = ТЗ_Загруженная.Колонки;
Для Каждого Стр Из ТЗ_Загруженная Цикл
НомерСтроки = НомерСтроки + 1;
НовЗапись = РегистрыСведений.А.СоздатьМенеджерЗаписи();
Для Каждого Кол Из Колонки Цикл
НовЗапись[Кол.Имя] = Стр[Кол.Имя];
КонецЦикла;
НовЗапись.Записать(Истина);
КонецЦикла;
КонецПроцедуры
В итоге получается компактное и быстро прикручиваемое решение по считыванию больших файлов. В моменте число фоновых заданий может достигать числа 130-150, каждое из которых работает около 2 минут. Для себя эмпирически установил размер сбрасываемой порции в 3000 строк.
Вступайте в нашу телеграмм-группу Инфостарт