gifts2017

Быстрая функция чтения данных с листа Excel

Опубликовал Дмитрий Калачев (wildhog) в раздел Программирование - Практика программирования

Прочитал статью "Универсальное решение работы с Ексель". И опять чтение происходит путем перебора ячеек листа Ексель. Для больших файлов это очень медленно. Решил просмотреть ВСЕ обработки из рубрики 1С + Excel...

Прочитал статью "Универсальное решение работы с Ексель". И опять чтение происходит путем перебора ячеек листа Ексель. Для больших файлов это очень медленно. Решил просмотреть ВСЕ обработки из рубрики 1С + Excel с целью определить - стоит ли писать о способе чтения Excel файла которым сам пользуюсь.

Итог - почти во всех обработках происходит чтение файла путем перебора каждой ячейки выбранного диапазона листа Ексель. Только в http://infostart.ru/projects/3962/ - использует  вариантный массив, а также чтение с использованием ADO. И то вариантный массив читается поячеечно.

Много обработок с закрытым кодом - о них ничего сказать не могу. 

Но, большинство обработок - законченные решения выполняющие различные функции, а не только ПРОСТОЕ чтение файла. (искренний респект и уважение авторам) Я же предлагаю всего лишь функцию быстрого чтения файла Excel. Готовых обработок в этой области не выкладываю - дублировать существующие нет смысла, или они явно не дотягивают по функционалу до уже выложенных :)

Итак. При чтении файла Excel я использую вариантный массив. Он позволяет быстро получить ВСЮ таблицу листа в память, а также получать данные массива целыми колонками. Тем самым время на чтение области файла Excel в таблицу значений сокращается в десятки раз.

Вот текст функции.

Функция ПрочитатьЛистExcel(ТЗ = Неопределено, ЛистЭксель = Неопределено, НомерПервойСтроки = 1, НомерПервойКолонки = 1, ВсегоСтрок = 0, ВсегоКолонок = 0) Экспорт

Если ЛистЭксель = Неопределено Тогда
   
ЛистЭксель = ПолучитьCOMОбъект(,"Excel.Application");
КонецЕсли;
Если
ВсегоСтрок = 0 Тогда
   
ВсегоСтрок = ЛистЭксель.Cells.SpecialCells(11).Row;
КонецЕсли;
Если
ВсегоКолонок = 0 Тогда
   
ВсегоКолонок = ЛистЭксель.Cells.SpecialCells(11).Column;
КонецЕсли;
Если
ТЗ = Неопределено Тогда
   
ТЗ =  Новый ТаблицаЗначений;
    Для
Счетчик = 1 По ВсегоКолонок Цикл
       
ТЗ.Колонки.Добавить("Колонка"+Счетчик, Новый ОписаниеТипов("Строка"));
    КонецЦикла;
КонецЕсли;
Для
Счетчик = НомерПервойСтроки По ВсегоСтрок Цикл
   
НоваяСтрока = ТЗ.Добавить();
КонецЦикла;

Область = ЛистЭксель.Range(ЛистЭксель.Cells(НомерПервойСтроки,НомерПервойКолонки), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
Данные = Область.Value.Выгрузить();

Для
Счетчик = 0 По ВсегоКолонок-1 Цикл
   
ТЗ.ЗагрузитьКолонку(Данные[Счетчик], Счетчик);
КонецЦикла;
ЛистЭксель = Неопределено;
Возврат
ТЗ;
КонецФункции

 

Комментировать функцию, думаю, нет необходимости. Вот собственно и все, что я хотел сказать. 

 

 

 

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Алексей Константинов (alexk-is) 21.05.09 13:33
Может быть текст функции раскрасить?
2. Дмитрий Калачев (wildhog) 21.05.09 13:44
(1) Хорошая идея, твоя обработка замечательно справиться :)
3. Сергей Кучеров (СергейКа) 22.05.09 12:50
Век живи - век учись... Даже не подозревал о возможности Область.Value.Выгрузить();
4. Дмитрий Калачев (wildhog) 22.05.09 15:34
Область.Value - это ComSafeArray. О нем в конфигураторе довольно подробно написано + методы тоже имеются.
5. artem666 Bogomaz (artem666) 23.05.09 10:58
6. Слава Демченко (A'Huli) 25.05.09 06:57
7. kadr (kadr) 25.05.09 09:27
На днях протестил wildhog vs ADO на листе с 8 колонками и 10000 строками.
Результаты
wildhog:
Выполнение скрипта заняло: 391 мсек.
Обработано строк: 8 колонок: 10000 итого ячеек; 80000

КОД
[code/]
Скрипт = Новый COMОбъект("MSScriptControl.ScriptControl");
Скрипт.language = "javascript";

ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
Эксель = ПолучитьCOMОбъект("c:\delado.xls");
ЛистЭксель = Эксель.WorkSheets(1);
ВсегоСтрок = ЛистЭксель.Cells.SpecialCells(11).Row;
ВсегоКолонок = ЛистЭксель.Cells.SpecialCells(11).Column;

Сообщить("Колонок: "+ Строка(ВсегоКолонок) + " Строк: "+ Строка(ВсегоСтрок));

ТЗ = Новый ТаблицаЗначений;
Для Счетчик = 1 По ВсегоКолонок Цикл
ТЗ.Колонки.Добавить("Колонка"+Счетчик);
КонецЦикла;

Для Счетчик = 1 По ВсегоСтрок Цикл
НоваяСтрока = ТЗ.Добавить();
КонецЦикла;

Область = ЛистЭксель.Range(ЛистЭксель.Cells(1,1), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
Данные = Область.Value.Выгрузить();

Для Счетчик = 0 По ВсегоКолонок-1 Цикл
ТЗ.ЗагрузитьКолонку(Данные[Счетчик], Счетчик);
КонецЦикла;
ЛистЭксель = Неопределено;
Эксель = Неопределено;

ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
ОчиститьСообщения();
Сообщить("Выполнение скрипта заняло: " + Строка(ВремяОкончания - ВремяНачала) + " мсек. " + Символы.ПС +
"Обработано строк: " + Строка(ВсегоКолонок) + " колонок: " + Строка(ВсегоСтрок) + " итого ячеек; " + Строка(ВсегоКолонок * ВсегоСтрок));
[/code]

ADO:
Выполнение скрипта заняло: 438 мсек.
Обработано строк: 8 колонок: 10000 итого ячеек; 80000
Использовалась ВК GameWithFire

КОД
[code/]
Скрипт = Новый COMОбъект("MSScriptControl.ScriptControl");
Скрипт.language = "javascript";
ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

Соединение = Новый COMОбъект("ADODB.Connection");
НаборДанных = Новый COMОбъект("ADODB.RecordSet");

Соединение.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\delado.xls;Extended Properties=Excel 8.0");
НаборДанных.Open("sel ect * from [Лист1$]", Соединение);

Колонок = 0; Строк = 0;
ИспользовалИСО = Ложь;

Попытка
ПодключитьВнешнююКомпоненту("GameWithFire.ADOUtils");
ADOUtils = Новый("AddIn.ADOUtils");
ТЗ = ADOUtils.ADORecordsetToValueTable(НаборДанных);
ИспользовалИСО = Истина;
Исключение
ЧислоКолонокНабора =НаборДанных.Fields.Count;
ТЗ = Новый ТаблицаЗначений;
Для Сч = 1 по ЧислоКолонокНабора Цикл
ТЗ.Колонки.Добавить("Колонка"+Строка(Сч));
Колонок = Колонок + 1;
КонецЦикла;

НаборДанных.MoveFirst();
Пока НЕ НаборДанных.EOF Цикл
СтрокаТЗ = ТЗ.Добавить();
Для Сч = 1 по ЧислоКолонокНабора Цикл
СтрокаТЗ["Колонка"+Строка(Сч)] = НаборДанных.Fields.Item(Сч-1).Value;
КонецЦикла;

Строк = Строк + 1;
НаборДанных.MoveNext();
КонецЦикла;
КонецПопытки;

Если Колонок = 0 Или Строк = 0 тогда
Колонок = ТЗ.Колонки.Количество();
Строк = ТЗ.Количество();
КонецЕсли;

Соединение = Неопределено;
НаборДанных = Неопределено;

ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
ОчиститьСообщения();
Сообщить("Выполнение скрипта заняло: " + Строка(ВремяОкончания - ВремяНачала) + " мсек. " + Символы.ПС +
"Обработано строк: " + Строка(Колонок) + " колонок: " + Строка(Строк) + " итого ячеек; " + Строка(Строк * Колонок)+
?(ИспользовалИСО,Символы.ПС+"Использовалась ВК GameWithFire",""));

ТЗ.ВыбратьСтроку();
Скрипт = Неопределено;
[/code]

Вот так вот
Кстати, писать в Excel тоже лучше массивом
[code/]
Эксель = Новый COMОбъект("Excel.Application");
Книга = Эксель.Workbooks.Add("c:\delado.xls");
Лист = Книга.Sheets(2);

Скрипт = Новый COMОбъект("MSScriptControl.ScriptControl");
Скрипт.language = "javascript";
ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

Строк = 100;
Колонок = 100;

Для Сч = 1 по Строк Цикл
Для Сч1 = 1 по Колонок Цикл
Лист.cells(Сч,Сч1).Value = сч1;
КонецЦикла;
КонецЦикла;

ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
Сообщить("Выполнение скрипта заняло: " + Строка(ВремяОкончания - ВремяНачала) + " мсек. ");

Эксель.Visible = 1;
[/code]
Результат Выполнение скрипта заняло: 94 мсек.

Результат вывода через Лист.Cells(i,j).Value
Выполнение скрипта заняло: 25937 мсек.
kiruha; Istur; Rustig; +3 Ответить 3
8. Анатолий Ситников (acsent) 25.05.09 11:39
ADO не всегда можно. Например нельзя когда разного типа данные в одном столбце. Привоодятся к строке
m_aster; Rustig; +2 Ответить
9. Василий Демидов (Душелов) 25.05.09 11:53
(7) Попробуй еще протестить на скорость http://infostart.ru/projects/3214/
10. kadr (kadr) 25.05.09 12:10
(9) Дык а чего там тестить?
Только оптимальную реализацию алгоритмов работы с Compound объектами?
11. kadr (kadr) 25.05.09 12:11
(9) На правах рекламы :)))
Ваша реализация, ИМХО, самая быстрая, хотя не тестил :)
12. kadr (kadr) 25.05.09 12:57
(9) Болт. не самая быстрая
Код
Скрипт = Новый COMОбъект("MSScriptControl.ScriptControl");
Скрипт.language = "javascript";
ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

ИмяВК="AddIn.ExcelEditor";
Попытка
ПодключитьВнешнююКомпоненту(ИмяВК);
Исключение
Предупреждение("Не удалось подключить компоненту " + ИмяВК);
КонецПопытки;

Попытка
Экзель = Новый(ИмяВК);
Исключение
Предупреждение("Ошибка создания объекта внешней компоненты: " + ИмяВК);
КонецПопытки;

Если Экзель.ОткрытьФайл("c:\delado.xls") тогда
Если Экзель.ОткрытьЛист(1) тогда
КоличествоКолонок = Экзель.ПолучитьКоличествоКолонок();
КоличествоСтрок = Экзель.ПолучитьКоличествоСтрок();

Таблица = Новый ТаблицаЗначений;
Для Сч = 1 по КоличествоКолонок Цикл
Таблица.Колонки.Добавить("Колонка"+Строка(Сч));
КонецЦикла;

Для Сч = 1 по КоличествоСтрок Цикл
СтрокаТЗ = Таблица.Добавить();
Для Сч1 = 1 по КоличествоКолонок Цикл
СтрокаТЗ[Сч1-1] = Экзель.ПолучитьЗначениеЯчейки(Сч, Сч1);
КонецЦикла;
КонецЦикла;

Экзель.Выполнено();

ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
ОчиститьСообщения();
Сообщить("Выполнение скрипта заняло: " + Строка(ВремяОкончания - ВремяНачала) + " мсек. ");
КонецЕсли;
КонецЕсли;

Результат
Выполнение скрипта заняло: 2125 мсек.
13. Василий Демидов (Душелов) 25.05.09 13:06
(12) А где

"Строк = 100;
Колонок = 100;"

?
14. Василий Демидов (Душелов) 25.05.09 13:12
(12) И как бы создание объектов стоит убрать из тестов, а оставить только чтение данных.
15. kadr (kadr) 25.05.09 13:14
Это был тест чтения
Обработано строк: 8 колонок: 10000 итого ячеек; 80000

Тест записи
Выполнение скрипта заняло: 484 мсек.

КОД
Скрипт = Новый COMОбъект("MSScriptControl.ScriptControl");
Скрипт.language = "javascript";
ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

ИмяВК="AddIn.ExcelEditor";
Попытка
ПодключитьВнешнююКомпоненту(ИмяВК);
Исключение
Предупреждение("Не удалось подключить компоненту " + ИмяВК);
КонецПопытки;

Попытка
Экзель = Новый(ИмяВК);
Исключение
Предупреждение("Ошибка создания объекта внешней компоненты: " + ИмяВК);
КонецПопытки;

Если Экзель.ОткрытьФайл("c:\delado.xls") тогда
Если Экзель.ОткрытьЛист(2) тогда

Строк = 100;
Колонок = 100;

Для Сч = 1 по Строк Цикл
Для Сч1 = 1 по Колонок Цикл
Экзель.РедактироватьЗначениеЯчейки(0, 1, Сч*Сч1);
КонецЦикла;
КонецЦикла;


ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
ОчиститьСообщения();
Сообщить("Выполнение скрипта заняло: " + Строка(ВремяОкончания - ВремяНачала) + " мсек. ");
Экзель.Выполнено();
КонецЕсли;
КонецЕсли;

Кстати параметры функции РедактироватьЗначениеЯчейки в документации не указаны :)
16. Василий Демидов (Душелов) 25.05.09 13:16
(15) Попытка/Исключение хорошо кушают милисекунды, кстати.
17. kadr (kadr) 25.05.09 13:17
Создание объектов включил специально, чтобы увидеть скорость выполнения не именно операция ввода-вывода, а скорость импорта-экспорта

Коды тестов есть - дорабатывать и оптимизировать можно всегда :)
18. kadr (kadr) 25.05.09 13:18
19. Василий Демидов (Душелов) 25.05.09 13:19
(18) Пожертвовать скоростью с попыткой/исключение при открытии обработки можно... Важна скорость обработки многомегобайтных файлов.
20. kadr (kadr) 25.05.09 13:19
Ок без Попытка-исключение
Чтение
Выполнение скрипта заняло: 2093 мсек.

Запись
Выполнение скрипта заняло: 469 мсек.
21. Василий Демидов (Душелов) 25.05.09 13:20
И попробуй сделать 80000 строк.
22. Василий Демидов (Душелов) 25.05.09 13:23
И попробуй сделать 3 колонки - число, строка и дата ;)
Просто самому интересны результаты.
23. kadr (kadr) 25.05.09 13:31
Без создания объектов чисто операции I/O

ЧТЕНИЕ
wildhog

КОД
ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
Область = ЛистЭксель.Range(ЛистЭксель.Cells(1,1), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
Данные = Область.Value.Выгрузить();
ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

результат
Выполнение скрипта заняло: 109 мсек.
Обработано строк: 8 колонок: 10000 итого ячеек; 80000

ADO
КОД
ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
ТЗ = ADOUtils.ADORecordsetToValueTable(НаборДанных);
ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

Результат
Выполнение скрипта заняло: 359 мсек.
Обработано строк: 8 колонок: 10000 итого ячеек; 80000
Использовалась ВК GameWithFire

Душелов
КОД
ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
Для Сч = 1 по КоличествоСтрок Цикл
СтрокаТЗ = Таблица.Добавить();
Для Сч1 = 1 по КоличествоКолонок Цикл
СтрокаТЗ[Сч1-1] = Экзель.ПолучитьЗначениеЯчейки(Сч, Сч1);
КонецЦикла;
КонецЦикла;
ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

Результат
Выполнение скрипта заняло: 1843 мсек.

ЗАПИСЬ

COMSAfeArray
КОД
ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
Лист.Range(Лист.Cells(1,1),Лист.cells(Строк,Колонок)).Value = Массив2;
ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

Результат
Выполнение скрипта заняло: 47 мсек.

Построчно
КОД
Строк = 100;
Колонок = 100;
ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
Для Сч = 1 по Строк Цикл
Для Сч1 = 1 по Колонок Цикл
Лист.cells(Сч,Сч1).Value = сч1;
КонецЦикла;
КонецЦикла;
ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

Результат
Выполнение скрипта заняло: 22500 мсек.

Душелов
КОД

Строк = 100;
Колонок = 100;
ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
Для Сч = 1 по Строк Цикл
Для Сч1 = 1 по Колонок Цикл
Экзель.РедактироватьЗначениеЯчейки(0, 1, Сч*Сч1);
КонецЦикла;
КонецЦикла;
ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

Результат
Выполнение скрипта заняло: 219 мсек.
24. Василий Демидов (Душелов) 25.05.09 13:34
Кинь файлик мне ;) Посмотрю, что да как... Что-то долго слишком.
25. Василий Демидов (Душелов) 25.05.09 13:36
(23) На счет чтение... Там везде ТЗ 1С-овское получается?

А то у автора:

"Данные = Область.Value.Выгрузить();

Для Счетчик = 0 По ВсегоКолонок-1 Цикл
ТЗ.ЗагрузитьКолонку(Данные[Счетчик], Счетчик);
КонецЦикла;"

Как бы не объективно получается ;)
26. kadr (kadr) 25.05.09 13:38
(19) согласен
я думал твоя вк будет самой быстрой, но видишь как получилось..
Думаю косяк в многократном вызове метода ВК РедактироватьЗначениеЯчейки, а там по цепочке callAsproc, callasfunc затем у тебя создание объекта типа cell и тд

(22)
Сегодня уже не смогу протестить с разными параметрами

(24) какой файлик? Excel? или тесты? или то и другое? мыло?
27. Василий Демидов (Душелов) 25.05.09 13:39
И на счет записи тоже самое. Приведи запись из 1С-овского ТЗ в 1 варианте.
28. Василий Демидов (Душелов) 25.05.09 13:40
(26) Эти тесты пока не объективны ;)
29. kadr (kadr) 25.05.09 13:47
(27)
Эксель = Новый COMОбъект("Excel.Application");
Книга = Эксель.Workbooks.Add("c:\delado.xls");
Лист = Книга.Sheets(2);

Скрипт = Новый COMОбъект("MSScriptControl.ScriptControl");
Скрипт.language = "javascript";
//ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

Строк = 100;
Колонок = 100;
Массив = Новый Массив(Строк,Колонок);
Для Сч = 0 по Строк - 1 Цикл
Для Сч1 = 0 по Колонок - 1 Цикл
Массив[сч][сч1] = сч1;
КонецЦикла;
КонецЦикла;

Массив2 = Новый COMSafeArray(Массив, "VT_I4",Строк,Колонок);

ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
Лист.Range(Лист.Cells(1,1),Лист.cells(Строк,Колонок)).Value = Массив2;
ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

Сообщить("Выполнение скрипта заняло: " + Строка(ВремяОкончания - ВремяНачала) + " мсек. ");

Эксель.Visible = 1;

Вот из ComSafeArray/ надо из Тз - засунь ТЗ в массив и выведи

Согласен, что не все 1С-совское, но согласись, мы замеряем не скорость работы какого-то определенного интерфейса, а скорость операции импорта-экспорта

(24)
А ты реализцй у с(27)
Эксель = Новый COMОбъект("Excel.Application");
Книга = Эксель.Workbooks.Add("c:\delado.xls");
Лист = Книга.Sheets(2);

Скрипт = Новый COMОбъект("MSScriptControl.ScriptControl");
Скрипт.language = "javascript";
//ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

Строк = 100;
Колонок = 100;
Массив = Новый Массив(Строк,Колонок);
Для Сч = 0 по Строк - 1 Цикл
Для Сч1 = 0 по Колонок - 1 Цикл
Массив[сч][сч1] = сч1;
КонецЦикла;
КонецЦикла;

Массив2 = Новый COMSafeArray(Массив, "VT_I4",Строк,Колонок);

ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
Лист.Range(Лист.Cells(1,1),Лист.cells(Строк,Колонок)).Value = Массив2;
ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

Сообщить("Выполнение скрипта заняло: " + Строка(ВремяОкончания - ВремяНачала) + " мсек. ");

Эксель.Visible = 1;

Вот из ComSafeArray/ надо из Тз - засунь ТЗ в массив и выведи

Согласен, что не все 1С-совское, но согласись, мы замеряем не скорость работы какого-то определенного интерфейса, а скорость операции импорта-экспорта

Ты реализуй в своей вк метод типа ТЗВЭкзель :)
30. Дмитрий Калачев (wildhog) 25.05.09 13:53
Решил и я потестировать оба варианта.
Методика тестирования.
Создал 3 файла, в каждом 6 колонок, строк в 1м файле - 12000, 2м ~ 24000, 3м ~ 49000.

1 тест. запуск последовательно (сначала COMSAFEARRAY, потом ADO) на чтение одного файла. После каждого чтения перезагрузка.
Результат.

Колонок: 6 Строк: 11 999
Выполнение скрипта заняло: 5 323 мсек.
Выполнение скрипта заняло: 2 880 мсек.

Колонок: 6 Строк: 24 661
Выполнение скрипта заняло: 7 061 мсек.
Выполнение скрипта заняло: 5 551 мсек.

Колонок: 6 Строк: 49 321
Выполнение скрипта заняло: 7 890 мсек.
Выполнение скрипта заняло: 10 335 мсек.

Видно, что увеличение количества строк сильно сказывается при чтении через ADO

2 тест. Чтение последовательно 3х файлов разными методами (сначала COMSAFEARRAY, потом ADO). После каждого чтения перезагрузка.
Закешировал создаваемые процедурами Com объекты.

Результат.


Выполнение скрипта заняло: 3 067 мсек.
Обработано строк: 6 колонок: 11 999 итого ячеек; 71 994
Выполнение скрипта заняло: 1 182 мсек.
Обработано строк: 6 колонок: 24 661 итого ячеек; 147 966
Выполнение скрипта заняло: 2 274 мсек.
Обработано строк: 6 колонок: 49 321 итого ячеек; 295 926


Выполнение скрипта заняло: 2 450 мсек.
Обработано строк: 6 колонок: 11 998 итого ячеек; 71 988
Выполнение скрипта заняло: 4 917 мсек.
Обработано строк: 6 колонок: 24 660 итого ячеек; 147 960
Выполнение скрипта заняло: 9 849 мсек.
Обработано строк: 6 колонок: 49 320 итого ячеек; 295 920

Для метода чтения через COMSAFEARRAY замерил время создания Com объекта Екселя. Время создания COM Excel :2 001 мсек.

3 тест. Собственно и не тест а замер производительности средствами 1С.

Результат.

При чтении через ADO больше всего времени занимает выполнение вот этой строки (70%):

СтрокаТЗ["Колонка"+Строка(Сч)] = НаборДанных.Fields.Item(Сч-1).Value;

Обработка всего цикла считывания записей из recordset занимает 80% общего времени.

При чтении через COMSAFEARRAY 27% времени уходит на открытие файла.
27% на Данные = Область.Value.Выгрузить();
10% на ТЗ.Добавить(); :)
31. Дмитрий Калачев (wildhog) 25.05.09 14:00
(25)
Здесь

Данные = Область.Value.Выгрузить();

"Данные" - тип ComSafeArray.
"ТЗ" - Тип таблица значений (самая что нинаеть 1совская :) )
Чуть выше ж есть "ТЗ = Новый ТаблицаЗначений;"

А вот Данные[Счетчик] - уже типа МАССИВ. Он то легко и загружается методом
таблицы значений "ЗагрузитьКолонку".
32. Дмитрий Калачев (wildhog) 25.05.09 14:20
(22) Если нужно прочитать данные сохранив именно те типы которые определены в Экселе, то в сабже нужно сформировать колонки таблицы значений заранее, указав соответствующие типы. В этом случае значения в результирующей ТЗ будут приводиться к типу колонки ТЗ.
33. Василий Демидов (Душелов) 25.05.09 14:25
Попробуйте затестить еще на 70000 строках ;)
34. Василий Демидов (Душелов) 25.05.09 14:29
(29) >Согласен, что не все 1С-совское, но согласись, мы замеряем не скорость работы какого-то определенного интерфейса, а скорость операции импорта-экспорта

>Вот из ComSafeArray/ надо из Тз - засунь ТЗ в массив и выведи

Это надо учитывать в подсчете времени теста!

Условия задачи надо определить жестче:
1) Из экзеля загрузить в ТЗ данные.
2) Из ТЗ записать данные в экзелевский файл.

(31) Да я знаю, я говорю автору теста kadr, о том, что тесты не объективны, т.к. у него в тестах отсутствует заполнение ТЗ, которое так же занимает время.
35. kadr (kadr) 25.05.09 14:34
(34) как же отсутствует?
посмотри начальные тесты (7) там все есть...
затем я просто выложил голое время без создания, инициализации и заполнения объектов
36. Дмитрий Калачев (wildhog) 25.05.09 14:34
(34) а как сделать 70000 строк? у мну получилось только 65536?
37. Василий Демидов (Душелов) 25.05.09 14:38
(35) я про (23), в моем примере ты заполняешь строку ТЗ.
38. Василий Демидов (Душелов) 25.05.09 14:39
(36) Сделай в 8-ке и сохрани в экзель ;)
39. kadr (kadr) 25.05.09 14:54
(37) согласен
исправим первый пример с учетом загрузки ТЗ оптом
ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
Область = ЛистЭксель.Range(ЛистЭксель.Cells(1,1), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
Данные = Область.Value.Выгрузить();

Для Счетчик = 0 По ВсегоКолонок-1 Цикл
ТЗ.ЗагрузитьКолонку(Данные[Счетчик], Счетчик);
КонецЦикла;
ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

Выполнение скрипта заняло: 141 мсек.
Обработано строк: 8 колонок: 10000 итого ячеек; 80000

И построчно
ВремяНачала = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");
Область = ЛистЭксель.Range(ЛистЭксель.Cells(1,1), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
Данные = Область.Value.Выгрузить();

Для Сч = 1 По ВсегоКолонок Цикл
Для Сч1 = 1 По ВсегоСтрок Цикл
ТЗ[Сч1-1][Сч-1] = Данные[Сч-1][Сч1-1];
КонецЦикла;
КонецЦикла;
ВремяОкончания = Формат(Скрипт.eval("new Date().getTime()"),"ЧГ=0");

Выполнение скрипта заняло: 828 мсек.
Обработано строк: 8 колонок: 10000 итого ячеек; 80000
40. Дмитрий Калачев (wildhog) 25.05.09 14:56
(38) попробовал... алгоритм взял из (29).
Что-то мне подсказывало что ничего не получиться :) . Так и вышло...
41. Василий Демидов (Душелов) 25.05.09 14:59
(40) Это один из главных минусол ОЛЕ екзеля, ниже 2007 версии ;)
42. Василий Демидов (Душелов) 25.05.09 15:02
(39) :) Сейчас я поколдую с массивами, ибо компонента должна была работать и в 7-ке, а она (до не давнего времени) с комсэйф-массивами не работала, и можно будет протестировать.
44. Дмитрий Калачев (wildhog) 25.05.09 15:14
(41) млин туплю. Работал с файлами xls а не xlsx... Офис у мну 2007.
Все нормально создалось ну и загрузилось соответственно.
45. Василий Демидов (Душелов) 25.05.09 15:15
(44) Результаты тестов какие?
46. Василий Демидов (Душелов) 25.05.09 15:18
(39) Ради эксперимента, попробуй в рег.бат компоненты моей после строки
regasm.exe ExcelEditor.dll /codebase

добавить строку

ngen.exe ExcelEditor.dll

и перерегить.
47. Василий Демидов (Душелов) 25.05.09 15:19
(44) xlsx уже не объективно ;) формат другой. С ним можно протестить ради эксперимента http://infostart.ru/projects/3495/
48. Дмитрий Калачев (wildhog) 25.05.09 15:26
(47, 45) для xlsx

Выполнение скрипта заняло: 13 240 мсек.
Обработано строк: 6 колонок: 123 301 итого ячеек; 739 806

Тут файл замера производительности http://slil.ru/27691047
49. Дмитрий Калачев (wildhog) 25.05.09 15:29
(47) Если честно. так и не вкурил как создать файл XLS с количеством строк более 65536. Выложи куда нить готовый файл протестирую.
50. Василий Демидов (Душелов) 25.05.09 15:31
(48) А по ссылке в (47) время замера можешь сделать?
(49) Сделай отчет в 1С-ке и сохранить табличный документ в экзель.
51. Дмитрий Калачев (wildhog) 25.05.09 16:21
(50) Попробую.

Поделюсь еще мыслями. Вспомнил что у recorseta есть метод GetRows()
Тут нашел полное описание http://support.microsoft.com/kb/246335

Но, оказалось, что получаемый массив нужно еще транспонировать, что скорее всего очень долго (м.б попозже попробую реализовать). Имхо этот метод (getrows)
не принесет ощутимого прироста в скорости, хотя, все возможно. :)
52. Василий Демидов (Душелов) 25.05.09 16:32
(51) В моем компоненте аналогичное добавил. Обновление залил.
Но, думаю, тоже не сильно прироста скорости будет... Хотя кто знает... Протестить надо. Метод "ПолучитьЗначения".
53. Дмитрий Калачев (wildhog) 25.05.09 16:35
Замер производительности по (47) - http://slil.ru/27691324

Количество строк: 123 301
Количество колонок: 6
54. Василий Демидов (Душелов) 25.05.09 16:40
(53) А времени сколько вышло?

А файл для какого приложения? У меня ничего не стоит с этим расширением ассоциированного...
55. Дмитрий Калачев (wildhog) 25.05.09 16:45
(54) Время в замере. Отдельно замерял. Но счет шел на минуты.
Файл xlsx.
Тестировал обработку чтения файла екселя средствами 1С которая из (47)
http://infostart.ru/projects/3495/
Функция взял из readme.
56. Василий Демидов (Душелов) 25.05.09 16:49
(55) Ну если на минуты, то тогда не надо :)
57. Дмитрий Калачев (wildhog) 25.05.09 16:58
58. kadr (kadr) 25.05.09 17:08
(46) чтение чуть быстрее стало
Выполнение скрипта заняло: 1735 мсек.
59. Дмитрий Калачев (wildhog) 25.05.09 17:09
(56) Итак. Тест твоей компоненты.
Выполнение скрипта заняло: 6 621 мсек.
Обработано колонок : 7
Обработано строк : 64 299

Тот же файл, только по сабжу:
Выполнение скрипта заняло: 7 989 мсек.
Обработано строк: 7 колонок: 64 299 итого ячеек; 450 093

Опять же повторное чтение с помощью сабжа (сразу после первого):
Выполнение скрипта заняло: 3 997 мсек.
Обработано строк: 7 колонок: 64 299 итого ячеек; 450 093

Скажи. а в ВК используешь GetRows?


60. Дмитрий Калачев (wildhog) 25.05.09 17:11
(59) + файл замера производительности. http://slil.ru/27691513
61. Василий Демидов (Душелов) 25.05.09 17:21
(59) Ну, примерно, аналогичное GetRows, но массив я все равно "ручками" заполняю.

И это ты тестил с "ПолучитьЗначения" ?
62. Дмитрий Калачев (wildhog) 25.05.09 17:26
(61) Угу. пробежался по колонкам и загрузил данные

Экзель.ОткрытьФайл(ПутьКФайлу);
Экзель.ОткрытьЛист(1);
ВсегоКолонок = Экзель.ПолучитьКоличествоКолонок();
ВсегоСтрок = Экзель.ПолучитьКоличествоСтрок();

Для Счетчик = 1 По ВсегоСтрок Цикл
ТЗ.Добавить();
КонецЦикла;

Для Счетчик = 0 По ВсегоКолонок - 1 Цикл
Массив = Экзель.ПолучитьЗначения(Счетчик).Выгрузить();
ТЗ.ЗагрузитьКолонку(Массив, Счетчик);
КонецЦикла;
63. Роман Петров (PeRom) 25.05.09 18:33
Пользую метод давно, хотел поделится, но никак не могу справиться с проблеммой, можт кто сталкивался?
Код: Файл = Новый COMОбъект("Excel.Application");
Файл.DisplayAlerts = 0; //не задавать вопросы
Файл.Application.AutomationSecurity = 3; //уровень безопасности
Книга = Файл.Workbooks.Open(ФайлХЛ);
Лист = Книга.Worksheets(2);
Пока (НЕ ПустаяСтрока(Строка(Лист.Cells(строкаXL,1).Value))) или
(НЕ ПустаяСтрока(Строка(Лист.Cells(строкаXL,2).Value))) Цикл
строкаXL = строкаXL+1;
КонецЦикла;
COMSafeArrayМассив = Лист.range(Лист.Cells(1,1),Лист.Cells(строкаXL,249)).Value;
Книга.Close();
Массив = COMSafeArrayМассив.Выгрузить();............
Файл около 50 Мбайт с макросами и паролями(но с этим вроде разобрался), из одного листа файла загружаю 250 колонок * 6-10 000 строк, на строке Книга = Файл.Workbooks.Open(ФайлХЛ); висит 5-6 минут, остальное пролетает за секунды. Как можно это победить?
64. Дмитрий Калачев (wildhog) 26.05.09 08:57
(63) А сколько времени открывается файл в WIndows?
попробуй отключать еще автопересчет формул, кажется свойство Calculation.
М.б еще какие макросы запускаются при открытии?
Если не поможет, м.б попробовать ADO? В этой ветке ado подробно осветили :)
65. rasswet (rasswet) 02.09.09 14:01
сильно вы! респект! так на чем в итоге то останавливаемся?
66. dushelov (Душелов) 02.09.09 16:19
(65) Я пользуюсь своей компонентой. И быстро, и без офиса. Т.к. у нас лицензионный софт везде, а офис не у всех стоит.
67. rasswet (rasswet) 15.09.09 22:53
(66) млин((
написал чтение черед АДО. на работе работает. на домашнем компе тоже SP2 и
MDAC версии 2000.85.1022- не работает.
у того кому делал на висте -тоже не работает. у кого-то читает на висте через ADO
через компоненту твою я не умею..хотелось штатными (относительно) средствами.
68. dushelov (Душелов) 15.09.09 23:27
(67) В чем проблема с компонентой?

АДО не работает как? Что пишет? Может поставщика данных нет в системе?
69. rasswet (rasswet) 16.09.09 07:16
(68) компоненту не хочу, это слать клиенту длл, объяснять по телефону чё с ней делать..не. не хочу.
адо пишет ""BOF или EOF имеют значение True или текущая запись была удалена""

70. rasswet (rasswet) 16.09.09 08:34
(68) проблема локализовалась. в тестовых файлах было по 1му листу, и грузилось нормально, а в реале оказалось три листа в фале. пофиксил.
71. Сергей (Che) Коцюра (CheBurator) 04.11.09 02:24
юзайте йоксель...
а адо как-то попробовал -и бросил ибо на первом же подсунутом файле читала половину файла и тупо финишировала как будто вконец данных - так победить и не смог...
72. Slim33rus (Slim33rus) 27.03.10 10:19
Спасибо wilghog! :D Очень помогла статья. Эксель из 12600 строк загружается за 10 сек. Раньше загружаллось за 8 мин. Большое спасибо. :D
73. Александр Дуганов (sashs1980) 24.06.11 12:51
Этот способ несомненно быстрее, но не совсем корректен и следовательно не универсален. Дело в том, что при выполнение Данные = Область.Value.Выгрузить() текст преобразуется, например у меня был артикул 0000056789 и он преобразуется в 56 789. А при стандартном (универсальном)по ячеестым обходом нет, когда вытаскиваешь из ячейки .text
74. Дмитрий Калачев (wildhog) 20.07.11 16:29
sashs1980 пишет:

Этот способ несомненно быстрее, но не совсем корректен и следовательно не универсален. Дело в том, что при выполнение Данные = Область.Value.Выгрузить() текст преобразуется, например у меня был артикул 0000056789 и он преобразуется в 56 789. А при стандартном (универсальном)по ячеестым обходом нет, когда вытаскиваешь из ячейки .text


Попробуй перед загрузкой создать колонки таблицы значений с указанием нужного типа. В этом случае при загрузке будет корректно выполнено приведение типов.
75. Stepan Shipitsyn (Stepan_1c) 09.12.11 07:34
Спасибо, коротко и ясно=)
76. Александр Кикабидзе (capshow) 07.09.12 09:32
Спасибо за наВодку с методами Excel ) Всегда уважительно относился к емким и эффективным алгоритмам! Респект!
77. mikhailovaew (mikhailovaew) 17.01.13 11:07
Эх, вот такую бы штуку для 7.7! Пробовала переписать - спотыкается о строку
Данные = Область.Value.Выгрузить();
говорит - "Ожидается скалярное выражение (Данные)".
Кто-нибудь знает, как научить семерку жрать массивы?
78. Сергей Шепелев (redgoll) 28.01.13 15:34
Сама загрузка в Таблицу значений действительно проходит махом, за это большое спасибо. Только рано я обрадовался. При дальнейшей обработки данных, для получения ссылочных данных все равно тормозит и обрабатывает долго. Строк около 5 тыс. НЕ подскажите, как убыстрить процесс? Ничего особенного не делаю, в цикле по таблице ищу номенклатуру и единицы измерения к ней.
79. Дмитрий Калачев (wildhog) 28.01.13 16:38
redgoll
Проблема в том, что используешь либо запрос либо НайтиПо... в цикле. Что очень медленно...
Помещай ТЗ во временную таблицу и ищи элементы запросом через левое/внутреннее соединение с ней.
Не забудь, что для временнной таблицы важно чтобы колонки ТЗ были типизированы.
80. Сергей Шепелев (redgoll) 06.02.13 12:32
Спасибо, за совет. Попробую воспользоваться вашим методом. Потом отпишусь о результатах и поблагодарю отдельно. Когда-то читал на данном ресурсе про метод загрузки данных не из Excel, а из табличного документа (mxl). Автор помнится очень хвалил его за быстродействие. Не пробовали на такие грабли наступать?
81. Дмитрий Калачев (wildhog) 06.02.13 13:27
(80)
Не пробовал. Но, думаю, что если и будет прирост в скорости, то не очень большой. + преобразование в mxl занимает время. Так что, смысла не вижу... Попробуйте, вдруг сабжевый способ не самый быстрый )
82. Eugeneer (Eugeneer) 12.04.13 15:44
Самый быстрый способ какой только может быть. при этом даже не нужен никакий эксель и офисы. читает все форматы.
Это написание компонент для 1С на базе существующих библиотек.
Я три года мучался со всеми вариантами. И в табличный документов, и все возможные превозможные АДО, Либре, Ексли с инфостарта и прочие вещи.
В итоге пришли к написанию собственной библиотеки которая читает все что только существует со скоростью звука.
В итоге сейчас листы по 100 000 строк заливаются в 1С за одну минуту.
83. Eugeneer (Eugeneer) 12.04.13 15:47
(78) так в вашем вопросе и ответ. В цикле естественно ВСЕГДА будет тормозить.
Надо делать один запрос всем махом.
84. Дмитрий Калачев (wildhog) 12.04.13 15:55
(82) Если дадите свою компоненту - погоняю, проверим.
А что на выходе 1Совская ТЗ или что-то еще?
У любого способа есть плюсы и минусы. У моего - универсальность, независимость от ВК (права пользователей, регистрация и т.д). У вашего скорость.
85. Eugeneer (Eugeneer) 12.04.13 16:06
(84) это коммерческое решение с защитой. http://infostart.ru/public/21810/ (также для УТ11 работает во всех режимах)
Реализована как Nativ. Вшита в макет и не требует ни регистраций, ни прав, ни возни с файлами. подключили обработку и она пашет.
ПОэтому и универсальность и независимость и скорость)) У меня гениальный друг программист.

Компонента сразу возвращает готовые ТЗ по всем листам.
86. Дмитрий Калачев (wildhog) 12.04.13 17:18
(85) Eugeneer, не стал писать про деньги )) Для многих это существенный минус...
Надеюсь продажи Вашей обработки говорят об обратном ))
87. uri1978 uri1978 (uri1978) 24.05.13 12:36
Отличный метод. Автору большой плюс!
88. Роман Иванов (MusaRB) 14.10.13 12:55
Спасибо автору! Работает действительно очень быстро.
89. Ramz # (ram3) 24.02.14 09:43
Подскажите, как, используя данную функцию, получить значения приведенными к строке, как это бывает при использовании Лист.Cells(i,j).Text.
Область.NumberFormat = "@"; - не помогает.
Проблема в том, что например вот такую строку "0000483159" получаю в виде числа 483 159, не знаю как победить...
90. kiruha Дронов (kiruha) 24.02.14 10:00
(82)
Здорово конечно, но приведенный автором статьи алгоритм + дополнение по записи от kadr полностью покрывает все проблемы - 0.4 сек на 80 000 ячеек.
В дальнейшем Вам стоит указывать и на это альтернативное решение
91. Ramz # (ram3) 24.02.14 10:18
(74) wildhog,
приведение типов происходит еще при выгрузке в массив Данные = Область.Value.Выгрузить(), к моменту заполнения таблицы значений артикул 0000056789 уже принимает следующий вид 56 789.
Какие еще есть идеи? Способ очень хорош, но вышеописанный момент все портит...
92. Дмитрий Калачев (wildhog) 24.02.14 11:22
(91) добрый день. Пришлите файл, посмотрю. + попробуйте перед чтением типизировать колонки тз.
93. Ramz # (ram3) 24.02.14 12:49
(92) wildhog,
в том-то и дело, что до ТЗ не доходит, смотрю отладчиком массив Данные сразу после выгрузки в него области - там уже значения изменены...
Файл для примера: http://yadi.sk/d/21WzBuG5JSKpR
94. kiruha Дронов (kiruha) 24.02.14 16:00
(92)
Смотрю в вашем файле формат ячеек - то числовой (дополнительный), то числовой (все форматы), то общий (текстовой и числовой форматы, еще форматы)
Это ж ....
В принципе можно было бы отформатировать Область.NumberFormat="Text";
Но у Вас в ячейке визуально 00000141 - фактически в ячейке число 141, числовой формат дополнительный, греческий язык , тип "табельный номер" - который отображает 141 как 00000141 и далее аналогично
95. Дмитрий Калачев (wildhog) 25.02.14 09:10
(93) По идее Область.Value берет именно ЗНАЧЕНИЯ ячеек, а не форматированные значения. В (94) все верно kiruha написал - проблемы в данных файла.
Поэтому, приводите в "нормальный" формат файл, а потом читайте.

В вашем случае, м.б рациональнее будет читать поячеечно - тогда сможете брать не значения ячеек, а их отформатированное значение. Проиграете в скорости, но выиграете в удобстве (не придется обрабатывать файл).
96. Ramz # (ram3) 25.02.14 11:36
(94) kiruha,
Но при всем при этом, ЛистЭксель.Cells(СчетчикСтрок,СчетчикКолонок).Text возвращает именно 00000141 а не 141, как вы предполагаете!
97. Ramz # (ram3) 25.02.14 11:42
(95) wildhog,
так и сделал, читаю по старинке перебором ячеек. Жаль, но в данной реализации ваш метод далеко не универсален (предварительная обработка файла убивает все плюсы), хотя по скорости видимо лучший! Пойду на форум любителей экселя, может там что-то подскажут... В любом случае спасибо, идея отличная!
98. Дмитрий Калачев (wildhog) 25.02.14 12:05
(97) Ну уж вы хотите и скорость и чтобы всякое повидло из файлов вытягивало. Удачи)

99. Ramz # (ram3) 25.02.14 12:09
(98) wildhog,
к сожалению боевые условия не всегда приближены к идеальным: как правило файлики для загрузки мы не сами себе стряпаем.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа