Какие бы ни были большие по объему импортируемые данные, всегда хочется, чтобы они грузились как можно быстрее. Иначе зачем нам тогда эти хх-ядерные компьютеры с уу-гигабайтами оперативы. Для 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С. При обходе по ячейкам загрузка заняла порядка получаса.
Статью не мешало бы назвать "Excel - экстра медленная загрузка".
ОтветитьУдалитьПолезный код, пригодился. Спасибо
ОтветитьУдалить[im]https://lh3.googleusercontent.com/-Oc2Sc3eKFX0/U32JVX0gchI/AAAAAAAABEQ/5bn6L4qfQmA/w100-h99-no/thumbs_up.png[/im]
УдалитьСпасибо, как раз "бъюсь" сейчас в этом направлении ))
ОтветитьУдалитьПривет профессионалам! Спасибо за материал! Очень помогло! Валерий
ОтветитьУдалить