Inspired by interest and comments on my previous article (Best way to generate Microsoft word docx from ABAP), I decided to create similar solution for Microsoft EXCEL.

First of all, I checked these projects on the same topic:

• XLSX Workbench
• Xml template toolkit

It’s powerful tools, but, in my opinion, for regular and common Excel tasks – it’s overhead and require too much manual action.

So, my project is aimed at simplifying the implementation of most common Excel tasks, such:

• replace few variables in template (date, document number, etc.)
• create a table
• create a table of tables

Project based on abap2xlsx. It’s a good package, I just add some automation to remove routine manual work.

I think it cover 90% of work with Excel. So, if your daily tasks look like that – my solution is the best for you.

If you know easier way – please, notify me.

Metrics for simplicity (lower is better):

• mouse click: single click / double click / select = 2 points
• keyboard type: one word / tab / enter = 1 point
• switch application / alt + tab = 3 points
• + cost of change (add 2 fields + remove 2 fields + rename 2 fields)

Watch video instruction (in 1080HD).

Code: https://github.com/AntonSikidin/abap2xlsx

Documentation: https://github.com/AntonSikidin/xlsx_template_filler

Installation

Clone source https://github.com/AntonSikidin/abap2xlsx with abapgit.

Make template

For example, something like this:

Each variable – in square bracket [variable].

Variable name is field name in data structure.
For rows which will be multiplied – create a Named Range:
select whole row or rows, next – go to Formulas, next – go to Define name.

Note!
There are several kind of Range:

• rows
• columns
• cells

In this solution we will use only rows, because, usually, tables are «vertically directed» and our template will «grow» from top to bottom.
Therefore, columns and cells ranges will be ignored. Overlapped non nested rows ranges will be ignored too.

Save and close template.

Generate data types

• Run program ZDEMO_EXCEL_GET_TYPES.
• Navigate path to your template.
• Run.
• Copy all text.

Write program

Past selected text.

Define variable for each sheet in your report.

data
: gs_sheet1 type t_sheet1
, gs_sheet2 type t_sheet2
, lo_data type ref to ZCL_EXCEL_TEMPLATE_DATA
.

Fill it with data.

Create object to hold your data.

create object lo_data.

Add each sheet to object lo_data.

lo_data->add( iv_sheet = 'Sheet1' iv_data = gs_sheet1 ).
lo_data->add( iv_sheet = 'Sheet2' iv_data = gs_sheet2 ).

‘Sheet1’ – name of sheet.

Write other part of program.

create object reader type zcl_excel_reader_2007.
lo_excel = reader->load_file( p_fpath ).

* merge data with template
  lo_excel->fill_template( lo_data ).

*** Create output
  lcl_output=>output( lo_excel ).

Whole program.

*&---------------------------------------------------------------------*
*& Report  Fill Template
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

report zdemo_excel_fill_template.

TYPES
: begin of t_TABLE1
,     PERSON type string
,     SALARY type i
, end of t_TABLE1

, tt_TABLE1 type table of  t_TABLE1 with empty key

, begin of t_LINE1
,     CARRID type string
,     CONNID type string
,     FLDATE type string
,     PRICE type i
, end of t_LINE1

, tt_LINE1 type table of  t_LINE1 with empty key

, begin of t_TABLE2
,     CARRID type string
,     PRICE type i
,     LINE1 type tt_LINE1
, end of t_TABLE2

, tt_TABLE2 type table of  t_TABLE2 with empty key

, begin of t_Sheet1
,     DATE type string
,     TIME type string
,     USER type string
,     TOTAL type i
,     PRICE type i
,     TABLE1 type tt_TABLE1
,     TABLE2 type tt_TABLE2
, end of t_Sheet1


, begin of t_TABLE3
,     PERSON type string
,     SALARY type i
, end of t_TABLE3

, tt_TABLE3 type table of  t_TABLE3 with empty key

, begin of t_Sheet2
,     DATE type string
,     TIME type string
,     USER type string
,     TOTAL type i
,     TABLE3 type tt_TABLE3
, end of t_Sheet2
.



DATA
: lo_data type ref to ZCL_EXCEL_TEMPLATE_DATA
, gs_sheet1 TYPE   t_Sheet1
, gs_sheet2 TYPE   t_Sheet2
.

* define variables
data: lo_excel type ref to zcl_excel,
      reader   type ref to zif_excel_reader.


constants: gc_save_file_name type string value 'fill_template_example.xlsx'.
include zdemo_excel_outputopt_incl.

parameters: p_fpath type string obligatory lower case default 'C:\Users\sadfasdf\Desktop\abap2xlsx\ZABAP2XLSX_EXAMPLE.xlsx'.


parameters: p_file radiobutton group rad1 default 'X'
          , p_smw0 radiobutton group rad1
          .

at selection-screen on value-request for p_fpath.
  perform get_file_path changing p_fpath.


start-of-selection.

  create object lo_data.

* generate data
  gs_sheet1 = value #(
  date = |{ sy-datum date = environment }|
  time = |{ sy-uzeit time = environment }|
  user  = |{ sy-uname }|

  table1 = value #(
                    ( person = 'Lurch Schpellchek' salary = '1200' )
                    ( person = 'Russell Sprout'    salary = '1300' )
                    ( person = 'Fergus Douchebag'  salary = '3000' )
                    ( person = 'Bartholomew Shoe'  salary = '100' )
                  )

  total = '5600'
  table2 = value #(
                      ( line1 = value #(
                                         (  carrid = 'AC' connid = '0820'  fldate = '20.12.2002' price = '1222'  )
                                       )
                        carrid ='AC'
                        price = '1222'
                      )
                      ( line1 = value #(
                                          (  carrid = 'AF' connid = '0820'  fldate = '23.12.2002' price = '2222'  )
                                       )
                        carrid ='AF'
                        price = '2222'
                      )

                      ( line1 = value #(
                                          (  carrid = 'LH' connid = '0400'  fldate = '28.02.1995' price = '899'  )
                                          (  carrid = 'LH' connid = '0454'  fldate = '17.11.1995' price = '1499'  )
                                          (  carrid = 'LH' connid = '0455'  fldate = '06.06.1995' price = '1090'  )
                                          (  carrid = 'LH' connid = '0455'  fldate = '28.04.1995' price = '6000'  )
                                          (  carrid = 'LH' connid = '9981'  fldate = '21.12.2002' price = '222'  )
                                       )
                        carrid ='LH'
                        price = '9488'
                      )
                      ( line1 = value #(
                                          (  carrid = 'SQ' connid = '0026'  fldate = '28.02.1995' price = '849'  )
                                        )
                        carrid ='SQ'
                        price = '849'
                      )

                      ( line1 = value #(
                                          (  carrid = 'LH' connid = '0400'  fldate = '28.02.1995' price = '899'  )
                                          (  carrid = 'LH' connid = '0454'  fldate = '17.11.1995' price = '1499'  )
                                          (  carrid = 'LH' connid = '0455'  fldate = '06.06.1995' price = '1090'  )
                                          (  carrid = 'LH' connid = '0455'  fldate = '28.04.1995' price = '6000'  )
                                          (  carrid = 'LH' connid = '9981'  fldate = '21.12.2002' price = '222'  )
                                       )
                        carrid ='LH'
                        price = '9488'
                      )
                      ( line1 = value #(
                                          (  carrid = 'SQ' connid = '0026'  fldate = '28.02.1995' price = '849'  )
                                        )
                        carrid ='SQ'
                        price = '849'
                      )

                      ( line1 = value #(
                                          (  carrid = 'LH' connid = '0400'  fldate = '28.02.1995' price = '899'  )
                                          (  carrid = 'LH' connid = '0454'  fldate = '17.11.1995' price = '1499'  )
                                          (  carrid = 'LH' connid = '0455'  fldate = '06.06.1995' price = '1090'  )
                                          (  carrid = 'LH' connid = '0455'  fldate = '28.04.1995' price = '6000'  )
                                          (  carrid = 'LH' connid = '9981'  fldate = '21.12.2002' price = '222'  )
                                       )
                        carrid ='LH'
                        price = '9488'
                      )
                      ( line1 = value #(
                                          (  carrid = 'SQ' connid = '0026'  fldate = '28.02.1995' price = '849'  )
                                        )
                        carrid ='SQ'
                        price = '849'
                      )

                      ( line1 = value #(
                                          (  carrid = 'LH' connid = '0400'  fldate = '28.02.1995' price = '899'  )
                                          (  carrid = 'LH' connid = '0454'  fldate = '17.11.1995' price = '1499'  )
                                          (  carrid = 'LH' connid = '0455'  fldate = '06.06.1995' price = '1090'  )
                                          (  carrid = 'LH' connid = '0455'  fldate = '28.04.1995' price = '6000'  )
                                          (  carrid = 'LH' connid = '9981'  fldate = '21.12.2002' price = '222'  )
                                       )
                        carrid ='LH'
                        price = '9488'
                      )
                      ( line1 = value #(
                                          (  carrid = 'SQ' connid = '0026'  fldate = '28.02.1995' price = '849'  )
                                        )
                        carrid ='SQ'
                        price = '849'
                      )

                      ( line1 = value #(
                                          (  carrid = 'LH' connid = '0400'  fldate = '28.02.1995' price = '899'  )
                                          (  carrid = 'LH' connid = '0454'  fldate = '17.11.1995' price = '1499'  )
                                          (  carrid = 'LH' connid = '0455'  fldate = '06.06.1995' price = '1090'  )
                                          (  carrid = 'LH' connid = '0455'  fldate = '28.04.1995' price = '6000'  )
                                          (  carrid = 'LH' connid = '9981'  fldate = '21.12.2002' price = '222'  )
                                       )
                        carrid ='LH'
                        price = '9488'
                      )
                      ( line1 = value #(
                                          (  carrid = 'SQ' connid = '0026'  fldate = '28.02.1995' price = '849'  )
                                        )
                        carrid ='SQ'
                        price = '849'
                      )

  )

  price = '14003'
  ).


  gs_sheet2 = value #(
  date = |{ sy-datum date = environment }|
  time = |{ sy-uzeit time = environment }|
  user  = |{ sy-uname }|

  table3 = value #(
                    ( person = 'Lurch Schpellchek' salary = '1200' )
                    ( person = 'Russell Sprout'    salary = '1300' )
                    ( person = 'Fergus Douchebag'  salary = '3000' )
                    ( person = 'Bartholomew Shoe'  salary = '100' )
                  )

  total = '5600' ).

* add data
  lo_data->add( iv_sheet = 'Sheet1' iv_data = gs_sheet1 ).
  lo_data->add( iv_sheet = 'Sheet2' iv_data = gs_sheet2 ).

* create reader

  create object reader type zcl_excel_reader_2007.
* load template

  IF p_file is NOT INITIAL.
    lo_excel = reader->load_file( p_fpath ).
  else.
    lo_excel = reader->load_smw0( 'ZEXCEL_DEMO_TEMPLATE' ).
  ENDIF.


* merge data with template
  lo_excel->fill_template( lo_data ).

*** Create output
  lcl_output=>output( lo_excel ).


*&---------------------------------------------------------------------*
*&      Form  Get_file_path
*&---------------------------------------------------------------------*
form get_file_path changing cv_path type string.
  clear cv_path.

  data:
    lv_rc          type  i,
    lv_user_action type  i,
    lt_file_table  type  filetable,
    ls_file_table  like line of lt_file_table.

  cl_gui_frontend_services=>file_open_dialog(
  exporting
    window_title        = 'select template  xlsx'
    multiselection      = ''
    default_extension   = '*.xlsx'
    file_filter         = 'Text file (*.xlsx)|*.xlsx|All (*.*)|*.*'
  changing
    file_table          = lt_file_table
    rc                  = lv_rc
    user_action         = lv_user_action
  exceptions
    others              = 1
    ).
  if sy-subrc = 0.
    if lv_user_action = cl_gui_frontend_services=>action_ok.
      if lt_file_table is not initial.
        read table lt_file_table into ls_file_table index 1.
        if sy-subrc = 0.
          cv_path = ls_file_table-filename.
        endif.
      endif.
    endif.
  endif.
endform.                    " Get_file_path

Run program.

Some limitation:

• image, drawing and diagrams add manually as usual

Conclusion:

• Using this development in your daily work you will save your working time.
• Freed up time you can spend on legacy code refactoring.
• After reading this post you can think of a way to generate reports better than this.
• Your boss will notice your progress.
• You can ask for a bigger salary.
• Your hair will become clean and silky.
• Your dog will stop do bad things.
• You can play the piano again even if you didn’t.
• People will populate Mars and there will be apple orchards on Mars.