Оптимизация работы Excel + VB

[MeMBus]

Вот тут все мучаюсь со своими задачами под эксэлем... Накалякал кода несколько килограмм, все по отдельности работает более-менее, когда запустил в «целом» - скорость не очень... Подскажите, кто какими хитростями пользуется для оптимизации и увеличении скорости работы связки Excel и VB?
P.S.
Уже использую...

Код: Выделить всё

.Application.Calculation = xlCalculationManual
.Application.ScreenUpdating = False

Лишний раз по одной ячейке не двигаюсь (правда в ряде случаев все же приходится)...
P.P.S. Понимаю, что не видя код трудно найти узкие места, но дело пока не в коде, а именно в работе Excel (уж больно медленно отрабатывает)...

[Viper]

ну еще .Application.Visible = False

[alibek]

Ну если уж ручная калькуляция и блокировка перерисовки приложения не помогла, остальное зометных результатов не даст, надо менять алгоритм работы программы.
Загоняй часто используемые ячейки в переменные или массивы, сортируй списки и т.п.

[MeMBus]

alibek
Я стараюсь по мере возможного улучшить алгоритм, но очень часто бывают ситуации, когда чтение производится в единичной ячейке, соответственно переходы между ними, как правило, и дают временную нагрузку... Я вообще так понял, что там без разницы, на чем работаешь, хоть на сях пиши, узковатое место все равно будет именно excel... Даже если просто в тупую посмотреть загруженность... Ведь формирование 20 страничного прайса, со всем форматированием, заполнением и т.п. идет 30 минут, конечно, если учесть что там масса манипуляций и т.п. то может это и не так много, но все же... Сейчас решил проработать еще раз алгоритм, там, где это возможно, дабы уменьшить время.
Кто нить знает еще какие нить способы «разгона» экселя кроме выше перечисленных (а так же помимо оптимального направления движения между ячейками)?

[alibek]

Генерация примерно 80 листов у меня занимала минуты две. Полчаса -- это слишком много, где-нибудь в коде скорее всего есть причина этого.

[uhm]

Сразу, чтобы устранить обычно самую главную причину "торможений": переменные типа Variant используешь? Другими словами, все ли переменные у тебя явно обявлены?

[Viper]

Что то у тебя не то с алгоритмом. Excel кончно медленная штука но не настолько... 30 минут на 20 страниц это очень долго!

[Andrey Fedorov]

alibek
Ведь формирование 20 страничного прайса, со всем форматированием, заполнением и т.п. идет 30 минут, конечно, если учесть что там масса манипуляций и т.п. то может это и не так много, но все же... Сейчас решил проработать еще раз алгоритм, там, где это возможно, дабы уменьшить время.

Прайс для того чтобы его отдать пользователю?

Лично я предпочитаю делать прайсы в формате chm - все
получается быстро и просто. Удобное содержание и поиск по прайсу получаются по умолчанию. Для просмотра на компьютере достаточно иметь Windows. Прайс на пару тысяч страниц (с рисунками продукции) создается примерно за две минуты...

Ну а по вопросу - сбрасывать данные в Excel скопом используя массив или Recordset. А уже потом форматировать все так-же скопом (столбцы/колонки)...

[MeMBus]

alibek
Вообще формирование идет в несколько этапов... В начале мы имеем простой прайс, в конце обработки прайс для печати в книжном варианте (на листе 2 страницы), тобишь в итоге получается 20 печатных листов (двух сторонних)= 40 листов * 2 страницы = 80 листов... Уже третий день пытаюсь оптимизировать алгоритм, выйгрыш пока составил около 1 минуты (что в пределах погрешности)...

uhm
Конечно же...

!Viper!
Вот и я о том же...

Andrey Fedorov
Прайс подготавливается для печати... По поводу chm – каждый выбирает то что хочет, по поводу удобства спорить не буду, но как правило те люди которые смотрят прайс отнесутся к экслелевскому виду как к «стандарту де факто», я наверное бы так же выбрал экселевский вариант...

[Andrey Fedorov]

Andrey Fedorov
Прайс подготавливается для печати... По поводу chm – каждый выбирает то что хочет,

Конечно - каждый решает сам. Так печатать я предпочитаю делая нормальный отчет - быстро и проблем нет. Excel для печати даром не нужен.

по поводу удобства спорить не буду, но как правило те люди которые смотрят прайс отнесутся к экслелевскому виду как к «стандарту де факто», я наверное бы так же выбрал экселевский вариант...

Да нет такого "стандарта де факто". Есть прайсы которые в некоторых конторах готовят обычные менеджеры - они просто по другому не умеют!

Excel удобен только там где надо что-то немножко посчитать. В остальных случаях он громоздок и неудобен. Не говоря уж о том что большие объемы он просто в принципе не переварит (у меня тысячи страниц с рисунками) - я уж не говорю о прочих неудобствах...

[alibek]

Лично я предпочитаю делать прайсы в формате chm - все получается быстро и просто.

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

[Andrey Fedorov]

В моем случае CHM не подходил

Ну а чем не подходил обычный генератор отчетов (тот-же ActiveReports)?

[alibek]

Тем, что его не было
Меня попросили сделать утилитку для магазина, делать все это на VB с внешними отчетами мне было просто лень
Так что я все сделал на Excel с небольшой примесью VBA минут за 40. Вот уж воистину, RAD

[MeMBus]

Ребята, я не говорю что удобнее, что лучше или что быстрее... В моем случае были заданы определенные условия, соотв. делать по своему, было бы нелепо... Я согласен с тем, что во многих моих ситуациях эксель – не лучшее решение...

Да нет такого "стандарта де факто". Есть прайсы которые в некоторых конторах готовят...

Но ведь есть так-же пользователи, которые вообще не знают что такое chm, они умеют работать только в Office + WinAmp, и даже представления не имеют о каких-то там еще средствах...

Еще раз повторюсь, спорить о выше сказанном не стоит, нужно отнестись к этому как к задаче, которую нужно решить... Сейчас я пытаюсь допетрить сам где и что узко в проге...

[Andrey Fedorov]

Но ведь есть так-же пользователи, которые вообще не знают что такое chm, они умеют работать только в Office + WinAmp, и даже представления не имеют о каких-то там еще средствах...

О каких-то там средствах навроде Help-a...

chm - это для юзера просто исполняемый файл...

Еще раз повторюсь, спорить о выше сказанном не стоит, нужно отнестись к этому как к задаче, которую нужно решить... Сейчас я пытаюсь допетрить сам где и что узко в проге...

Да все что нужно тебе уже давно сказали - от тебя уже зависит конкретное воплощение.

Кстати, Excel-файлы можно формировать и без использования объектной модели Excel (должно получиться намного быстрей) - если порыться в интернете то можно найти примеры/классы для этого - если интересно, конечно...

[MeMBus]

О каких-то там средствах навроде Help-a...
chm - это для юзера просто исполняемый файл...

Да, я согласен... Но есть «тетки» которые говорят – «я не умею с этим работать, я хочу чтоб было вот так»...

Да все что нужно тебе уже давно сказали - от тебя уже зависит конкретное воплощение.

Да я понял... Самый ценный совет в этом топике – «смотри код»... Хотя этот совет и стандартный, но я таки уже поднял производительность на 25%... Буду дальше смотреть...

Кстати, Excel-файлы можно формировать и без использования объектной модели Excel

Вот это уже интересно, спасибки за идею, попробую посмотреть что есть и как работает...

[brigval]

Надо же. Набрел на тему, мне интересную.

ну еще .Application.Visible = False

Не увеличивает производительность! А даже уменьшает! К сожалению.

Увеличивает на 1/3 скорость считывания из ячеек не Text, а Value. То есть str=Cells(...).Value быстрее, нежели str=Cells(...).Text

[Viper]

Насчет того что невидимость Excel не увеличивает производительность - это весьма странно

[Twister]

MeMBus
Вообще, в идеале, работа с Excel должна выглядеть примерно так: загнали данные в массив --> обработали массив --> выкинули результат в Excel. Т.е. все действа по обработке инфы нужно делать в памяти. Да это, я думаю, ты и сам знаешь прекрасно. Другое дело, что обрабатывать инфу в памяти иной раз не так-то просто - гораздо проще (по многим причинам) работать прямо с экселем. Так что вывод один - тебе стоит в корне пересмотреть алгоритм, а не заморачиваться с Application.ScreenUpdating = False и прочими фишками. Не даром же

Генерация примерно 80 листов у меня занимала минуты две.

[MeMBus]

В общих чертах задача стояла следующая – сбор данных (нэт, мыло, локал и т.п.), анализ данных, формирование сводных данных, формирование прайсов, печать основного прайса, рассылка прайсов, обновление данных на сайте и еще ряд других манипуляций. Общее время работы составляло от 25 до 50 минут, в зависимости от ситуации. Задача по оптимизации встала когда часть данных стало «приходить за 10 минут до начала раб.дня». В общей сложности на оптимизацию было потрачено порядка 1/3 времени разработки. В итоге все те же манипуляции без привлечения доп.инструментов и средств стали выполняться в среднем от 5 до 15 минут.

[Andrey Fedorov]

А я бы всеже не поленился и попробовал нечто вроде:

http://www.carlosag.net/Tools/ExcelXmlW ... fault.aspx

Думаю скорость выросла бы гораздо значительней...

[brigval]

Насчет того что невидимость Excel не увеличивает производительность - это весьма странно

Это у меня было заметно на глаз. И довольно приличная разница. Хотя я сначала тоже думал, что в невидимом режиме будет быстрее. Программа считывает данные из разных файлов (по ссылкам). Я даже обрабабтывал их при включенной видимости Excel одно время. Но пользователям приходилось объяснять, почему у них на панели задач то и дело появляются файлы Excel и пожертвовав временем я "ушел" все-таки в невидимый режим.

Вообще, в идеале, работа с Excel должна выглядеть примерно так: загнали данные в массив --> обработали массив ... а не заморачиваться с Application.ScreenUpdating = False и прочими фишками.

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

MeMBus, что бы Вы смогли посоветовать исходя из накопленного теперь опыта?

[MeMBus]

Вообще, в идеале, работа с Excel должна выглядеть примерно так: загнали данные в массив --> обработали массив ... а не заморачиваться с Application.ScreenUpdating = False и прочими фишками.

Это если работать с данными, а вот когда идет активное форматирование – чем поможет "загнали данные в массив".

А я бы всеже не поленился и попробовал нечто вроде

Проект был сдан

Насчет того что невидимость Excel не увеличивает производительность - это весьма странно

Соглашусь с !Viper!. Очень странно, что при активном обращении к «ячейкам» и т.п. ScreenUpdating вызывает тормоза...

MeMBus, что бы Вы смогли посоветовать исходя из накопленного теперь опыта?

Что конкретно интересует?

[Andrey Fedorov]

А я бы всеже не поленился и попробовал нечто вроде

Проект был сдан

Ну он же, наверное, не последний

[brigval]

Что конкретно интересует?

Вообще-то интересует все по теме Вашей темы. Или на Ваше усмотрение.
Например, известна такая рекомендация. Цикл For Each...Next , быстрее For ... Next c числовым индексом. Пробовали Вы ее использовать ее для перехода по строкам? Сам я не пробовал. Пока только планирую попробовать.

[MeMBus]

Ну он же, наверное, не последний

Я тоже надеюсь Сейчас многие вещи бы сделал по другому...

[Andrey Fedorov]

Что конкретно интересует?

Вообще-то интересует все по теме Вашей темы. Или на Ваше усмотрение.
Например, известна такая рекомендация. Цикл For Each...Next , быстрее For ... Next c числовым индексом. Пробовали Вы ее использовать ее для перехода по строкам? Сам я не пробовал. Пока только планирую попробовать.

Это точно не причем - на фоне общих тормозов объектной модели Excel подобные извраты просто мизер.