Про выгрузку отчетов в MS Excel

[apanko]

Утомившись от отдыха, решил порадовать общественность, чем нибудь полезным. Так как концептуальная работа еще в процессе подготовки, то выкладываю крайне познавательный демо-пример. С концептуальным трудом, он ничего общего не имеет, но ознакомившись с ним, можно легко будет встраивать во всяческие отчеты (как стандартные, так и разработанные отдельно) функцию «Выгрузка в MS Excel».

Для этого требуется создать следующие глобальные переменные:
TempExcelBuffer: Record: Excel Buffer (Temporary=Yes)
Export2Excel: Boolean (флажок, которые требуется установить на форму запроса. true - выгрузка производиться, false - выгрузка не производиться)
CurrentRowNo: Integer

Также требуется создать текстовые константы. Если Вы не собираетесь продавать разрабатываемый функционал за границу, то значения можно указать прямо в C/AL коде.
ExcelCapt1 Пример выгрузки отчета в MS Excel
ExcelCapt2 Число
ExcelCapt4 Число * 200 / 3
ExcelCapt5 Демо-пример

А также добавим функцию:

 
EnterCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[1024];Bold : Boolean;Italic : Boolean;UnderLine : Boolean)
TempExcelBuffer.INIT;
TempExcelBuffer.VALIDATE("Row No.",RowNo);
TempExcelBuffer.VALIDATE("Column No.",ColumnNo);
TempExcelBuffer."Cell Value as Text" := CellValue;
TempExcelBuffer.Formula := '';
TempExcelBuffer.Bold := Bold;
TempExcelBuffer.Italic := Italic;
TempExcelBuffer.Underline := UnderLine;
TempExcelBuffer.INSERT;

Для примера, построим отчет на таблице Integer. Добавим соответствующий dataitem и к нему две секции: Header и Body

В секции Header укажем следующий C/AL код

 
Integer, Header (1) - OnPreSection()
IF Export2Excel AND (CurrReport.PAGENO = 1) THEN BEGIN //условие CurrReport.PAGENO = 1 Используется для
  //многостраничных отчетов, в которых шапка выводится на каждой странице. Это позволяет избежать дублирования шапки в MS Excel
  CurrentRowNo += 1;
  EnterCell(CurrentRowNo, 1, ExcelCapt1, TRUE, FALSE, FALSE);
  CurrentRowNo += 1;
  EnterCell(CurrentRowNo, 1, ExcelCapt2, TRUE, FALSE, FALSE);
  EnterCell(CurrentRowNo, 2, ExcelCapt3, TRUE, FALSE, FALSE);
  EnterCell(CurrentRowNo, 3, ExcelCapt4, TRUE, FALSE, FALSE);
END;

А этот C/AL код добавим в секцию Body

 
Integer, Body (2) - OnPreSection()
IF Export2Excel AND CurrReport.SHOWOUTPUT THEN BEGIN
  CurrentRowNo += 1;
  EnterCell(CurrentRowNo, 1, FORMAT(Integer.Number,0,'<Standard Format,1>'), FALSE, FALSE, FALSE);  //Standart Format используется
    //для того, чтобы при выводе в Excel не было проблем с символом разделителем разрядов.
  EnterCell(CurrentRowNo, 2, FORMAT(Integer.Number*200/3,0,'<Precision,2><Standard Format,1>'), FALSE, FALSE, FALSE); //Precision,2
    //указывает, что для дробных величин должно быть два знака после запятой.
END;

Сформировав таблицу со значениями ячеек и данными о форматировании, создадим книгу MS Excel и заполним ее согласно подготовленным данным. Для этого добавим следующий C/AL код в триггер отчета OnPostReport.

 
Report - OnPostReport()
IF (Export2Excel)AND(TempExcelBuffer.ISEMPTY()=FALSE) THEN BEGIN
  TempExcelBuffer.CreateBook;
  TempExcelBuffer.CreateSheet(ExcelCapt5, ExcelCapt5, COMPANYNAME, USERID);
  TempExcelBuffer.GiveUserControl;
END;

Примечание

В колонтитуле созданного листа MS Excel должны быть указаны:
* Слева – Название листа, Название отчета.
* Справа – Дата формирования отчета, Пользователь, Номер страницы.
Однако в правой части колонтитула могут возникнуть проблемы – ни даты, ни номера страницы указано не будет, только слово Страница. Связано это с тем, что в русифицированном MS Excel используются иные командные символы, чем в англоязычной версии.
Для корректного формирования колонтитулов следует изменить в таблице 370 текстовые константы:

Техt013 &B &Ж (влияет на выделение жирным название листа, в правой части колонтитула)
Text014 &D &Д
Text015 &P &C («С» на русском)

Стоит отметить, что после внесения указанных изменений в таблицу 370, перестанут работать колонтитулы у пользователей, использующих англоязычную версию MS Excel.


См. также
Отчет 12469 Item Net Change Sheet (пример стандартного отчета с функцией экспорта)
http://apanko.ru/showthread.php?t=50 (выгрузка/загрузка таблиц в стандартном функционале)
http://apanko.ru/showthread.php?t=34 (пример загрузки прогнозов производства)

[apanko]

Печатные формы, довольно нудно рисовать.
Рисовать приятнее в Excel'е.

Выкладываю печатную форму ТОРГ-13 с шаблоном MS Excel.

[apanko]

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

Пользуемся формами. Форму можно отфильтровать и сохранить в Excel, где и проводить над ней дальнейшие манипуляции типа «сводная таблица».

Форма более дружественна, т.к. в ней полей изначально меньше, да и дополнительный функционал присутствует.

Единственная проблема это сохранение в Excel числовых значений. Разделитель триад используемый Navision для Excelне пригоден. Далеко не все могут его победить.

Тут на помощь пришло свойство format.

Если в него записать <Standard Format,1> (с угловыми скобками), то на форме разделитель триад не отображается, что приводит к легкому взаимодействию с Excel через copy/paste.

Отлично, но как быть если разделитель все же очень хочется видеть? Тоже используем свойство формат, только напишем в него следующее: <Sign><Integer Thousand><1000Character, ><Decimals>, где после запятой указан обычный пробел.

[apanko]

При использовании стандартной таблицы 370 Excel Buffer проблема с разделителями триад остается.

Чтобы победить нужно применять функцию Format, не забыв указать правильный параметр:

 
EnterCell(CurrentRowNo, 5, FORMAT("Invoiced Quantity",0,1), FALSE, FALSE, FALSE);

[apanko]

См. также Таблица 370 Excel Buffer. Выгрузка как она есть.

[apanko]

Захотел поработать с Excel напрямую, в смысле без участия таблицы 370.

Создал переменную Automation. 'Microsoft Excel 11.0 Object Library'.Application

И получил ошибку:

Переменная XLAPPLICATION::WORKBOOKPIVOTTA определена более одного раза.
Например: 'File' существует как тип данных и как глобальная переменная.

Безуспешно боролся минут семь.

Оказалось не стоило называть переменную xlApplication.

[apanko]

Юра выложил крайне полезный объект с описанием.
Подробнее в статье на nav4u
Расширенный Excel Buffer

[apanko]

Тема с xlApplication получила продолжение и теоретическую базу http://forum.mazzy.ru/index.php?showtopic=12240.

Коллега столкнулся с такой же проблемой в результате борьбы было выяснено:
1. Независимо от того активизирован ли флаг WithEvents для переменной automation, соотвествующие функции все равно создаются.
2. Navision не воспринимает длинные имена функций, а обрезает их до 30 символов.

Отсюда проблема:
В Excel.Application есть два события начинающихся с одинаковых символов:
xlApplication::WorkbookPivotTableCloseConnection
xlApplication::WorkbookPivotTableOpenConnection

После того как я уменьшил имя переменной с xlApplication до xlApp функции стали выглядеть как:
xlApp::WorkbookPivotTableCloseConnection
xlApp::WorkbookPivotTableOpenConnection
Теперь Navision понял, что это разные события и разрешил скомпилировать объект.

Т.е. переменная могла называться ABC1234567890 и при этом вызывать проблему, т.е. xlApplication не является каким-либо зарезервированным словом кам можно было подумать.

[apanko]

Однако, что делать коллеге у которого имя переменной 5 символов, а для однозначной идентификации события тоже нужно 5 символов
wdapp::MailMergeDataSourceValidate
wdapp::MailMergeDataSourceValidate2
Уменьшить длину переменной он не может.

Тут пригодится следующий совет от AndreBlack
1. сохранить объект без компиляции.
2. выгрузить объект в текстовый файл (да, не каждая лицензия это осилит)
3. отредактировать текстовый файл (удалив повторяющиеся события)
4. сохранить текстовый файл и импортировать объект назад.

Система должна заработать.

Однако, как система будет обрабатывать данные триггеры пока остается загадкой. Требуется эксперимент.

[apanko]

Возник вопрос: как добавить лист в конец книги. Т.е. по умолчанию создается книга с Лист1, Лист2, Лист3. Нужно чтобы Лист4 был самым последним.

Вот ответ:

 
IF NOT CREATE(XlApp,TRUE) THEN
  ERROR('Excel не установлен');
XlApp.Visible(FALSE);
XlWrkBk := XlApp.Workbooks.Add;
//Это стандартный код по инициализации automation и созданию новой книги (с тремя листами: 1,2,3).
 
XlWrkSht2:= XlWrkBk.Worksheets.Item(XlWrkBk.Worksheets.Count);
//Переменная XlWrkSht2 теперь ссылается на самый последний лист в книге 
//(количество листов определено с помощью функции Count)
 
XlWrkSht := XlWrkBk.Worksheets.Add(XlWrkSht2);
//Создан новый лист4 перед листом3, первый параметр функции Add - это лист перед которым нужно вставить новый лист.
//Т.е. теперь порядок листов в книге следующий: 1,2,4,3
 
XlWrkSht2.Move(XlWrkSht);
//Перемещаем лист3 перед листом4, первый параметр функции Move - это лист перед которым нужно поместить текущий лист. 
//Т.е. теперь порядок листов в книге следующий: 1,2,3,4
 
//Далее идет стандартный код
XlApp.Visible(TRUE);
XlApp.UserControl(TRUE);
CLEAR(XlApp);

[apanko]

Возник вопрос: как програмно удалить лист с данными.

Проблема заключается в том, что Эксель спрашивает у пользователя - "На данном листе могут существовать данные ...". Причем делает это независимо от того есть ли там данные или нет. Точнее если данных никогда не было, то не спрашивает. А если были, то несмотря на то, что их потом удалили - вопрос все равно будет.

Чтоб отключить вопросы нужно использовать свойство DisplayAlerts объекта Application

 
XlApp.DisplayAlerts(FALSE); //отключили вопросы.
XlWrkSht2.Delete; //удалили лист.
XLapp.DisplayAlerts(TRUE); //включили вопросы назад.

Изначально ответил здесь: http://forum.mazzy.ru/index.php?showtopic=13123

[Васыо]

Выкладываю печатную форму ТОРГ-13 с шаблоном MS Excel.

А ТОРГ-12 и счета-фактуры не завалялось?