Классы для генерации отчетов в Excel

[Eldar9x]

Привет всем.
Часто приходится создавать экселевские отчеты. Вначале пользовался джобом AndyD Исследование скорости экспорта данных из Axapta в Excel (коллективный эксперимент)
Вывод в xml формате, как мне кажется, наиболее привлекателен и возможностями форматирования, и скоростью. Но джоб нужно каждый раз править под свои нужды, поэтому возникла мысль несколько это автоматизировать. Выкладываю классы позволяющие создавать такие экселевские отчеты. Основные цели, которых мне хотелось достигнуть при проектировании этих классов:
- сохранить скорость вывода отчетов;
- упростить создание отчетов;
- получить возможность гибко настраивать и расширять функциональность отчетов;
- убрать ограничение на количество выводимых данных;

При тестировании скорость вывода 50000 строк в простом цикле (без запроса в бд) около 30 секунд, на серваке отработало за 10 сек.

Простейший пример отчета:

    VendTable       vendTable;
    IXMLExcelReport rep = new CXMLExcelReport();
    ;

    while select vendTable
        rep.send([vendTable.AccountNum, vendTable.Name, today(), 100.0012]);

    rep.show();

О том, как задавать стили, ширину, фиксировать ячейки и.т.д. в примерах в проекте .

[Ivanhoe]

Вопрос по форматированию в Excel:

1. решена ли проблема с высотой строк и объединенными ячейкам? Если в строке есть объединенные ячейки, то автоподбор высоты по строке перестает работать (ограничение Excel).
2. решена ли проблема с выводом "перевода строки" в ячейке? Чтобы в одной ячейке можно было сделать несколько абзацев, как при ручном вводе в Excel по ALT+Enter (ограничение Axapta-вского генератора xml)/

[Eldar9x]

Цитата:

Вопрос по форматированию в Excel:

попробую решить

[Eldar9x]

2-е решается легко, достаточно форматировать текст в виде:
str s = strfmt("Строка 1%1%2%3%4%5Строка 2%1%2%3%4%5Строка 3", '&','#','1','0',';');
и в стиле задать " <Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>"

Насчет первого - пытаюсь вкурить, поясните. Как можно это обойти, если это ограничение самого excel...

[Ivanhoe]

Ограничение Excel, но некоторые отчеты, особенно табличного вида, без этого не построить красиво.

Видел разные решения - например, в некой простой строке вставить текст, узнать высоту строки (которая автоматически подобралась) и выставить высоту явно. Правда, такое решение видел при работе через COM. Как решить через xml - надо думать.

По поводу перевода строки в ячейке, у меня не получалось так сделать, буду смотреть Ваш проект

[Eldar9x]

Цитата:

буду смотреть Ваш проект

думаю, искать в строке символ \n это загубить всю скорость, лучше форматировать на месте, но указвать доп. стиль.

[andrewK]

"Переменная GRD_InventTreeGroup не объявлена."
А где можно найти эту переменную?

[Eldar9x]

Нигде - это из наших доработок. В классе Tester код в методе test() табличную переменную можно чем-нибудь другим заменить

[Daiver]

Привет.
Попробовал использовать эти классы для вывода относительно большого массива данных (например 113 столбцов, 970 строк), получаю ошибку:

"Сообщение (17:07:23)
Ошибка времени выполнения: Недостаточно памяти для исполнения кода.

Трассировка стека

(S)\Classes\CXMLRowBuilder\buf - line 4
(S)\Classes\CXMLExcelReport\swapBuf - line 14
(S)\Classes\CXMLExcelReport\show - line 8"

При меньшем объеме данных такой проблемы не возникает.
Также её можно обойти, если например через каждые 100 строк делать swap.

Не подскажешь, в чем может быть проблема?

ЗЫ: версия Акс 4 SP2EE FP1. Версия Excel 2003 (11.5612.5606)
ЗЫЫ: Для 4-ки в классе CXMLExcelReport метод new, система требует ExecutePermission.assert(); ...

[Eldar9x]

классы делал на 3-ку. Скоро выложу и для четверки. Насчет проблемы, щас 4-ки под рукой нет, посмотрю позже. Можешь проверить для меньшего количества столбцов (10-20) как в четверке отработает, или при запуске с клиента?

[Daiver]

Цитата:

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

классы делал на 3-ку. Скоро выложу и для четверки. Насчет проблемы, щас 4-ки под рукой нет, посмотрю позже. Можешь проверить для меньшего количества столбцов (10-20) как в четверке отработает, или при запуске с клиента?

При swapQty 1000: Строк 970, если столбцов более 66 то вылетает ошибка. Если оставить 66, но увеличить строки, например до 980, то снова ошибка. Снижаем swapQty, ошибка пропадает. Я у себя оставил swapQty 100 ...
Если кол-во строк не большое, например взял 100, то и при swapQty 1000 все нормально.

[Eldar9x]

Сделал небольшое наблюдение.

При swapQty = 10000, код

    IXMLExcelReport report = new CXMLExcelReportEx();
    ;

    for (i = 1; i <= 10970; i++)
    {

        con = connull();
        for (j = 1; j <= 213; j++)
        {
            con += "Некая строка для вывода";
        }
        con += i;

        report.send(con);
    }

    report.show();

валится в момент передачи данных в excel (метод swapBuf)
this.range(curRow,1,curRow+curLines-1,xmlRowBuilder.cols()).value(#xlRangeValueXMLSpreadsheet, xmlRowBuilder.buf());

При этом длина строки buf составила 145705719, что, если я не ошибаюсь, сотсавляет примерно 145 MB !!!
Уменьшение swapQty до 5000 распечатало отчет нормально. Но даже в этом случае на вызов метода range.value ушло несколько секунд.
Есть подозрение, что передача большого объема данных в excel чревато нехорошими последствиями. Буду проверять в 4ке

ps/ знакомая ошибка, кстати Обратная разработка в AX4

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

[Eldar9x]

Выкладываю обновленные классы:
1) Добавлена возможность контролировать (без перегрузки метода) предельный размер буфера до выгрузки (swapQty). по умолчанию выставлено в 1000;

report.swapQty(1000);

2) Добавлена возможность контролировать размеры буфера во избежание его переполнения:
задается командой

report.autoSwap(true);

по умолчанию выключено
макимальный размер буфера (по умолчанию выставлен 1000000) задается командой

report.maxStr(1000000);

Это гарантирует работу при любом размере отчета, но несколько замедляет его вывод, поэтому при большом числе колонок лучше регулировать swapQty.
3) Добавлена возможность выводить многострочные ячейки. Для этого ячейке указывается стиль WRAP_STR. Можно добавлять свои стили с перенесом строки, но их нужно включить в метод CXMLStyles::wrapTextStyles()

report.send([1, today(), "Строка 1\nСтрока 2\nСтрока 3"], ["", "EX5_NUM", "", "WRAP_STR"]);

4) Формат ячейки теперь определяется автоматически, если не задан конкретный стиль.

[tolstjak]

Подскажите пожалуйста как вывести в первой строке отчета его заглавие " ОСВ по ........"?
Использую класс CXMLInventReport т.к в нем есть диалог..

[Eldar9x]

Простой вариант:
в методе Run до цикла по таблице

    xmlExcelReport.write("ОСВ по", 1, 1, 1, 3, 1, "CLR_CNR_STR"); // выводим текст
    xmlExcelReport.curRow(2); // сдвигаем текущую позицию вниз

Параметры метода:
- текст
- стартовая строка
- стартовая колонка
- конечная строка
- конечная колонка
- tabsheet
- стиль для ячейки.
Недостаток:
При переходе на новую страницу заголовок не будет печататься

Более правильный вариант, лишенный указанного недостатка:
делаем наследника от CXMLExcelReportEx (в качестве примера можно взять Tutorial_CXMLExcelJobReport).
делаем наследника от CXMLExcelRunBase, перегружаем в нем xmlExcelReportClass (вернуть надо classnum созданного наследника от CXMLExcelReportEx) и используем его так же, как вы использовали CXMLInventReport.

стили можно задавать так, хотя это и длиньше, но более правильно.
staticmethodstr(CXMLStyles, CLR_CNR_STR)

[tolstjak]

Цитата:

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

xmlExcelReport.curRow(2); // сдвигаем текущую позицию вниз

Спасибо за подсказку.
Ругается на функцию: Задано неправильное число аргументов функции.
Буду разбираться.

[Eldar9x]

Цитата:

Задано неправильное число аргументов функции.

инкрементную компиляцию после импорта делали?
Надо для CXMLExcelReport, CXMLExcelRunBase
а версия аксапты какая?

[tolstjak]

Цитата:

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

инкрементную компиляцию после импорта делали?
Надо для CXMLExcelReport, CXMLExcelRunBase
а версия аксапты какая?

Делал.

Вместо xmlExcelReport.curRow(2);
сделал xmlExcelReport.setcurRow(2);
заглавие отчета появилось.

Большое спасибо еще раз.

[Eldar9x]

Нее. setCurRow() лучше не использовать. От методов get set я избавляюсь, видимо забыл удалить в этих проектах. Они нигде не должны использоваться. Метода curRow() достаточно, чтобы и установить и получить значение. Видимо у вас до импорта старая версия висела в приложении (еще из первого поста, который я почему-то не могу редактировать...).
Сделайте следующее, откройте classDeclaration CXMLExcelRunBase, внесите в него какие-нибудь изменения (пробел поставьте где-нибудь) и перекомпилируйте целиком весь класс, потом снова инкрементную компиляцию. То же самое можно проделать с другими классами. Я бы вообще снес все классы под корень и импортировал заново.

[tolstjak]

Цитата:

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

Нее. setCurRow() лучше не использовать. От методов get set я избавляюсь, видимо забыл удалить в этих проектах. Они нигде не должны использоваться. Метода curRow() достаточно, чтобы и установить и получить значение. Видимо у вас до импорта старая версия висела в приложении (еще из первого поста, который я почему-то не могу редактировать...).
Сделайте следующее, откройте classDeclaration CXMLExcelRunBase, внесите в него какие-нибудь изменения (пробел поставьте где-нибудь) и перекомпилируйте целиком весь класс, потом снова инкрементную компиляцию. То же самое можно проделать с другими классами. Я бы вообще снес все классы под корень и импортировал заново.

ОК.
так и сделаю.