Excel Fast Massive Load

Какие бы ни были большие по объему импортируемые данные, всегда хочется, чтобы они грузились как можно быстрее. Иначе зачем нам тогда эти хх-ядерные компьютеры с уу-гигабайтами оперативы. Для Excel в качестве альтернативы классическому медленному последовательному обходу по ячейкам листа можно использовать считывание данных в память с последующей обработкой.
Сначала мы так же подключаемся через COM-соединение к необходимому файлу и получаем нужный лист с данными. Далее, обращаемся к свойству листа .UsedRange.Value. Это свойство содержит значение типа COMSafeArray, с котором 1С умеет работать и в котором как раз и находятся все необходимые нам данные. Через метод Выгрузить() получаем наши данные и обрабатываем их. В качестве примера приведу обработку и замеры по быстродействию.

&НаКлиенте
Процедура ПрочитатьExcel(Команда)

 ВремяНачала = ТекущаяДата();
 
 КОМ = ОткрытьФайлExcel(Файл);
 
 Если КОМ = Неопределено Тогда
  Возврат;
 КонецЕсли;
 
 Если ОбходПоЯчейкам Тогда
  СоздатьКолонкиТЗ(КОМ.ПоследняяКолонка);
  ПолучитьДанныеExcel(КОМ, Данные);
 Иначе
  СоздатьТЗ(КОМ);
 КонецЕсли;
 
 ПоказатьЗначение(, "Время выполнения " + (ТекущаяДата() - ВремяНачала) + " сек., " + Данные.Количество() + " строк.");
 
КонецПроцедуры

&НаКлиенте
Функция ОткрытьФайлExcel(ИмяФайлаExcel, НомерЛиста = 1, ПарольФайла = "") Экспорт
 
 Попытка
  ExcelПриложение = Новый COMОбъект("Excel.Application");
 Исключение
  Возврат Неопределено;
 КонецПопытки;
 
 Попытка
  
  ExcelФайл = ExcelПриложение.WorkBooks.Open(ИмяФайлаExcel, , Истина);
  ExcelЛист = ExcelФайл.Sheets(НомерЛиста);
  ExcelЛист.UnProtect(ПарольФайла);
  xlCellTypeLastCell = 11;
  ExcelПоследняяСтрока = ExcelЛист.Cells.SpecialCells(xlCellTypeLastCell).Row;
  ExcelПоследняяКолонка = ExcelЛист.Cells.SpecialCells(xlCellTypeLastCell).Column;
  
  Если ОбходПоЯчейкам Тогда
   Возврат Новый Структура("Приложение, Файл, Лист, ПоследняяСтрока, ПоследняяКолонка", 
     ExcelПриложение, 
     ExcelФайл, 
     ExcelЛист, 
     ExcelПоследняяСтрока, 
     ExcelПоследняяКолонка);
  Иначе
   //ExcelЛист.UsedRange.Value содержит COMSafeArray, методы можно посмотреть в синтаксис-помощнике
   //Метод Выгрузить выгружает данные этого объекта в массив по колонкам, 
   //который в свою очередь содержит массивы с данными каждой колонки.
   ExcelДанные = ExcelЛист.UsedRange.Value.Выгрузить();
   ExcelПоследняяСтрока = ExcelЛист.UsedRange.Rows.Count;
   ExcelПоследняяКолонка = ExcelЛист.UsedRange.Columns.Count;
   //Завершаем работу с Excel, все необходимые данные выгружены в память.
   ExcelПриложение.Quit();
   Возврат Новый Структура("Данные, ПоследняяСтрока, ПоследняяКолонка", 
     ExcelДанные,
     ExcelПоследняяСтрока, 
     ExcelПоследняяКолонка);
  КонецЕсли;
    
 Исключение
  ExcelПриложение.Quit();
  Возврат Неопределено;
 КонецПопытки;
 
КонецФункции

&НаКлиенте
Процедура ПолучитьДанныеExcel(ДанныеExcel, ТаблицаДанных) Экспорт
 
 Для Строка = 1 По ДанныеExcel.ПоследняяСтрока Цикл
  СтрокаТЧ = ТаблицаДанных.Добавить();
  Для Колонка = 1 По ДанныеExcel.ПоследняяКолонка Цикл
   Ячейка = ДанныеExcel.Лист.Cells(Строка, Колонка);
   ЯчейкаЗначение = Ячейка.Value;
   СтрокаТЧ["Колонка_" + XMLСтрока(Колонка)] = ЯчейкаЗначение;
  КонецЦикла;
 КонецЦикла;
 ДанныеExcel.Приложение.Quit();
 
КонецПроцедуры

&НаСервере
Процедура СоздатьКолонкиТЗ(КоличествоКолонок)

 Данные.Очистить();
 
 мУдалить = Новый Массив;
 мКолонки = ПолучитьРеквизиты("Данные");
 Для Каждого Колонка ИЗ мКолонки Цикл
  мУдалить.Добавить(Колонка.Путь + "." + Колонка.Имя);
  Элементы.Удалить(Элементы[Колонка.Имя]);
 КонецЦикла;
 
 мДобавить = Новый Массив;
 
 Для Кол = 1 По КоличествоКолонок Цикл
  ИмяРеквизита = "Колонка_" + XMLСтрока(Кол);
  рКолонка = Новый РеквизитФормы(ИмяРеквизита, Новый ОписаниеТипов("Строка"), "Данные"); 
  мДобавить.Добавить(рКолонка);
 КонецЦикла;
 
 ИзменитьРеквизиты(мДобавить, мУдалить);
 
 Для Кол = 1 По КоличествоКолонок Цикл
  ИмяРеквизита = "Колонка_" + XMLСтрока(Кол);
  НовыйЭлемент = Элементы.Добавить(ИмяРеквизита, Тип("ПолеФормы"), Элементы.Данные);
  НовыйЭлемент.Вид = ВидПоляФормы.ПолеВвода;
  НовыйЭлемент.ПутьКДанным = "Данные." + ИмяРеквизита;
 КонецЦикла;

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

&НаСервере
Процедура СоздатьТЗ(КОМ)

 мУдалить = Новый Массив;
 мКолонки = ПолучитьРеквизиты("Данные");
 Для Каждого Колонка ИЗ мКолонки Цикл
  мУдалить.Добавить(Колонка.Путь + "." + Колонка.Имя);
  Элементы.Удалить(Элементы[Колонка.Имя]);
 КонецЦикла;
 
 мДобавить = Новый Массив;
 ТЧ = Новый ТаблицаЗначений;
 //Стурктура понадобится для получения пустых строк из результирующей таблицы
 //для их последующего удаления из результата.
 //Пустые строки в таблице будут содержать Неопределено во всех колонках.
 ОтборНеопределено = Новый Структура; 
 Для Кол = 1 По КОМ.ПоследняяКолонка Цикл
  ИмяРеквизита = "Колонка_" + XMLСтрока(Кол);
  ОтборНеопределено.Вставить(ИмяРеквизита);
  рКолонка = Новый РеквизитФормы(ИмяРеквизита, Новый ОписаниеТипов("Строка"), "Данные"); 
  мДобавить.Добавить(рКолонка);
  ТЧ.Колонки.Добавить(ИмяРеквизита);
 КонецЦикла;
 
 ИзменитьРеквизиты(мДобавить, мУдалить);
 
 Для Кол = 1 По КОМ.ПоследняяКолонка Цикл
  ИмяРеквизита = "Колонка_" + XMLСтрока(Кол);
  НовыйЭлемент = Элементы.Добавить(ИмяРеквизита, Тип("ПолеФормы"), Элементы.Данные);
  НовыйЭлемент.Вид = ВидПоляФормы.ПолеВвода;
  НовыйЭлемент.ПутьКДанным = "Данные." + ИмяРеквизита;
 КонецЦикла;
 
 //Можно было обойти массив данных поячеечно вложенным циклом,
 //но по мне два простых лучше. Метод ЗагрузитьКолонку, к сожалению,
 //не добавляет необходимые строки для данных. Поэтому сначала создаем пустые строки,
 //потом загружаем данные колонок.
 Для Сч = 1 По КОМ.ПоследняяСтрока Цикл
  ТЧ.Добавить();
 КонецЦикла;
 Для Сч = 0 По КОМ.Данные.ВГраница() Цикл
  ТЧ.ЗагрузитьКолонку(КОМ.Данные[Сч], Сч);
 КонецЦикла;

 //Удалем пустые строки из таблицы значений
 СтрокиНеопределено = ТЧ.НайтиСтроки(ОтборНеопределено);
 Для Каждого нСтрока Из СтрокиНеопределено Цикл
  ТЧ.Удалить(нСтрока);
 КонецЦикла;
 
 Данные.Загрузить(ТЧ);
 
КонецПроцедуры

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

В качестве теста скорости обработки загрузим большой объем данных (8612 строк) из прайс-листа 1С. При обходе по ячейкам загрузка заняла порядка получаса.

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