Пример построения отчета в XLSX (Excel 2007+)

* Реестр выписок банка о расходе средств     */
 
define input parameter rid-doc as integer.

/*Необходимые для печати в MS Excel определения______________________________*/

{slibooxml/slibxlsx.i}
{slib/slibos.i}
{slib/sliberr.i}

define var cError       as char no-undo.
define var cErrorMsg    as char no-undo.
define var cStackTrace  as char no-undo.
define var template     as character.
define var file         as character.

define temp-table tt_tr NO-UNDO
  field region as char
  field name   as char
  field transl as char
index i0 as primary unique region name .

run src/kernel/file_template.p (rid-doc).
template = RETURN-VALUE.  
run src/kernel/transl_tpl.p(rid-doc, output table tt_tr).
/*___________________________________________________________________________*/
 
DEFINE TEMP-TABLE Result NO-UNDO
  field data      as date      FORMAT "99.99.9999"
  field number    as character
  field client    as character FORMAT "X(30)"
  field sum       as decimal   
  field sum-base  as decimal   
  field osnovanie as character FORMAT "X(200)"
  field curr-cmp  as character
INDEX i0 IS PRIMARY data ASCENDING number ASCENDING.
 
  define variable str   as character.
  define variable str1   as character.
  define variable str2   as character.
  define variable user-lang   as character.
 
  run src/kernel/curlang.p .
  user-lang = return-value.

  run Report (rid-doc).
 
PROCEDURE Report:
  define input  parameter rid-doc  as integer.
 
  define variable date-from  as date FORMAT "99.99.9999".
  define variable date-to      as date FORMAT "99.99.9999".
  define variable i                 as integer.
  define variable j                 as integer.
  define variable k                as integer.
  define variable l                 as integer.
  define variable cr               as integer.
  define variable nomer        as integer.
  define variable client-cod   as integer.
  define variable row   as integer.
  define variable client-name  as character format "X(30)".
  define variable osnovanie    as character format "X(30)".
  define variable summ         as decimal.
  define variable curr         as integer.
  define variable curr-cmp     as character.
 
 
  run src/kernel/get_ffv.p ( "1:1", rid-doc ).
  date-from = DATE ( RETURN-VALUE ).
  run src/kernel/get_ffv.p ( "1:2", rid-doc ).
  date-to   = DATE ( RETURN-VALUE ).
 
  FIND FIRST typedoc WHERE typedoc.id-typedoc = 20 NO-LOCK NO-ERROR.
  IF AVAILABLE typedoc THEN
  DO:
     FOR EACH document  WHERE
         document.date-doc >= date-from AND
         document.date-doc <= date-to   AND
         document.rid-typedoc = typedoc.rid-typedoc NO-LOCK :
 
         create Result.
         run src/kernel/get_ffv.p ("1:1", document.rid-document).
         Result.data = DATE (RETURN-VALUE).
         run src/kernel/get_ffv.p ("1:2", document.rid-document).
         Result.number = RETURN-VALUE.
         run src/kernel/get_ffs.p ("1:4", document.rid-document).
         Result.Client = RETURN-VALUE.
         run src/kernel/get_ffv.p ("1:5", document.rid-document).
         Result.sum = DECIMAL (RETURN-VALUE).

         run src/kernel/get_ffs.p ( "1:21", document.rid-document).
         Result.curr-cmp = RETURN-VALUE.
        
         run src/kernel/get_ffv.p ("1:30", document.rid-document).
         Result.sum-base = DECIMAL (RETURN-VALUE).
         run src/kernel/get_ffv.p ("1:9", document.rid-document).               
         Result.osnovanie = REPLACE (RETURN-VALUE, "\n", "" ).      
     END.
  END.
  /*Создание документа, первоначальное форматирование*/  

  {slib/err_try}:

  run xlsx_copyTemplate(input "stXlsx",input template).

  /* Копирование всех областей в буфер */
  run xlsx_cut( "stXlsx", "header" ).  
  run xlsx_cut( "stXlsx", "table" ).  
  run xlsx_cut( "stXlsx", "footer" ).  

  /* Переводы подписей в шаблоне */
  for each tt_tr no-lock where tt_tr.region<>"":
    run xlsx_setClipboardValue( "stXlsx", tt_tr.region, tt_tr.name, tt_tr.transl).   
  end.
  /* ****************************** */

  /* Вывод данных заголовка отчета */
  run xlsx_setClipboardValue( "stXlsx", "header", "period",
      STRING (date-from) + " - " + STRING (date-to) ).
  run xlsx_paste(input "stXlsx", input "header",  input ? ).

 /* Вывод таблицы данных отчета */

  row = 4.
  FOR EACH result  
      break by result.data by result.sum:
      ACCUMULATE result.sum (TOTAL).
      ACCUMULATE result.sum-base (TOTAL).
      run xlsx_setClipboardValue( "stXlsx", "table", "atab1", result.number ).
      run xlsx_setClipboardValue( "stXlsx", "table", "atab2",
          string(result.data, "99.99.9999") ).
      run xlsx_setClipboardValue( "stXlsx", "table", "atab3", result.client ).
      run xlsx_setClipboardValue( "stXlsx", "table", "atab4", string(result.sum) ).
      run xlsx_setClipboardValue( "stXlsx", "table", "atab5", string(result.sum-base) ).
      run xlsx_setClipboardValue( "stXlsx", "table", "atab6", result.osnovanie ).

      run xlsx_setClipboardValue( "stXlsx", "table", "currency", result.curr-cmp).

      run xlsx_paste(input "stXlsx", input "table",  input ? ).
  END.

/* Вывод итогов данных отчета */

  run xlsx_setClipboardValue( "stXlsx", "footer", "totsum1",
      string(accum total result.sum) ).
  run xlsx_setClipboardValue( "stXlsx", "footer", "totsum2",
      string(accum total result.sum-base) ).
  run xlsx_paste(input "stXlsx", input "footer",  input ? ).
 
  /*Закрытие документа, установка доп. параметров*/
   run src/system/getmpfi2.p("xlsx").
   file = os_getNextFile(return-value).
   run xlsx_save( input "stXlsx",  input file).

  {slib/err_catch cError cErrorMsg cStackTrace}:
    run src/message_alert.p("Ошибка при создании xlsx файла: " + cErrorMsg).
    run log2.p("iOblikError.txt",cErrorMsg + "\n").
  {slib/err_end}.       

  /* Передача отчета на загрузку пользователю */
  run src/prn_dvs.w ( false, "XLSX#" + file,"OUT-ONLY=Да" ).
END.