Excel, быстрая группировка

[Pandasama]

Имеется отчет в Excel, при некоторых условиях достигающий 100 тысяч строк.
В нем необходимо сделать группировки по строкам.
При 100 тысячах строк для группировки есть 20 тысяч рэнджей.
Группировка идет через обычный COM, метод .group(), что при указанных объемах занимает чуть больше часа времени.

Есть ли более быстрые способы для осуществления группировок такого масштаба?
Вывод в Excel сейчас осуществляется также через COM, через вставку одного здоровенного строки-буфера (или разбития её на несколько кусков, если она сильно большая). Скорость вывода вполне нормальная, но при необходимости ускорения группировки способ вывода можно и поменять.

Используется в Ax2009

[Weez]

Рекомендую работать с Excel используя DotNet библиотеку, на форуме множество примеров вывода данных не используя textbuffer.

[AndyD]

.Net не поможет
Это надстройка на COM'ом и использование его будет, по сути, эквивалентно использованию в Аксапте. Проблема-то не в выводе массива данных, а в необходимости сделать вызов большого числа методов Экселя, что в любом случае будет не быстро

По-моему, в данному случае, надо двигаться в строну сводных таблиц
Или OLAP-кубов, в более широком смысле

[Weez]

Про сводные таблицы хорошая идея, а перевод на DotNet все равно желателен, хотя бы для отказа вывода через текстбуфер, который элементарно крешится при параллельных операциях с буфером обмена. Вот такое ИМХО.

[AlexeyS]

Самый простой вариант - сделать шаблон Excel со сводной таблицей.
На втором листе - строки с данными, на первом - сводная таблица.
Последовательность создания шаблона такая:
вставляем данные в таблицу,
формируем сводную таблицу в нужном виде (выделение данных по колонкам)
удаляем данные
сохраняем шаблон
Далее из аксапты заполняем данными второй лист и все.

[Владимир Максимов]

Основная стратегия ускорения формирования Excel - это замена "штучных" команд на групповые. Т.е. вместо 20 тысяч отдельных команд на группировку надо попытаться сделать одну (несколько десятков или сотен) общих команд на группировку.

Судя по документации, команда на группировку имеет вид Range.group(). Но Range - это не обязательно непрерывный диапазон. В общем случае, это может быть вообще набор отдельных ячеек.

Может быть, Вам имеет смысл попробовать группировать Range "пакетами" и накладывать группировку на эти "пакеты"? Я не пробовал так делать, просто как идея

Для справки:

Есть такой метод Excel.Union(), который позволяет объединять несколько Range в один. Правда, в этом методе может быть не более 30 параметров в одной команде (не более 30 Range могут быть объединены одной командой), но можно сделать цикл и присоединять по одному Range за раз

В псевдо-коде как-то так

rangeAll = Excel.Range("A1:B1")
rangeAll = Excel.Union(rangeAll, Excel.Range("A2:B2"))
rangeAll = Excel.Union(rangeAll, Excel.Range("A3:B3"))
...
rangeAll.group()

[Pandasama]

Цитата:

Сообщение от Владимир Максимов

rangeAll.group()

Увы

Метод "group" в COM-объекте класса "Range" возвратил код ошибки 0x800A03EC (<неизвестно>), который означает: Данная команда неприменима для несвязных диапазонов.

[Владимир Максимов]

Цитата:

Сообщение от Pandasama

Имеется отчет в Excel, при некоторых условиях достигающий 100 тысяч строк.
В нем необходимо сделать группировки по строкам.
При 100 тысячах строк для группировки есть 20 тысяч рэнджей.
Группировка идет через обычный COM, метод .group(), что при указанных объемах занимает чуть больше часа времени.

Проверяю

static void Job_test(Args _args)
{
    ComExcelDocument_RU ComExcelDocument_RU;
    Com                 comRange;
    MSOfficeBookMark_RU bookMark;
    int                 nextI;
    int                 timeNowBegin = timeNow();
    ;

    ComExcelDocument_RU = new ComExcelDocument_RU();
    ComExcelDocument_RU.newFile('',false);


    for (nextI = 1; nextI <= 20000; nextI++)
    {
        bookMark = int2str((nextI-1)*10 + 1)+':'+int2str(nextI*10 - 2);
        comRange = ComExcelDocument_RU.findRange(bookMark);
        comRange.group();
    }

    ComExcelDocument_RU.visible(true);

    info(time2str(timeNow()-timeNowBegin,1,1));
    info('end');
}

20 тысяч групп было создано за 00:07:46. Т.е. примерно за 8 минут. При этом последняя используемая строка Excel имеет номер 199`999. Т.е. 200 тысяч строк

Тест выполнял на Ax4.0. Почему у Вас получилось время более 1 часа ищите сами

PS: Надеюсь, вы метод select() не используете? Для программного формирования Excel обычно он не нужен и лишь является лишним тормозом. Используется только как завершающая команда непосредственно перед открытием.

[Pandasama]

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

[Владимир Максимов]

Да. Вопрос оптимизации процесс творческий

А не пробовали заполнять не чистый лист Excel, а предварительно созданный шаблон с преднастроенными группировками?

Насколько я понимаю, у Вас много мелких групп по 5..10 строк. Ну, и создать шаблон, например, с сотней группировок по 10 строк, а при подготовке буфера обмена добавлять пустые (фиктивные) строки в буфер, чтобы "вписаться" в группы шаблона.

Да, будут группы с пустыми строками, зато сами группировки при формировании отчета создавать не надо.

Можно еще поиграться добавлением/удалением пустых строк в предварительно настроенные группы шаблона. Может быть, это будет быстрее, чем собственно создание групп