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:
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:
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):
Watch video instruction (in 1080HD).
Code: https://github.com/AntonSikidin/abap2xlsx
Documentation: https://github.com/AntonSikidin/xlsx_template_filler
Clone source https://github.com/AntonSikidin/abap2xlsx with abapgit.
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:
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.
Past selected text.
Define variable for each sheet in your report.
Fill it with data.
Create object to hold your data.
Add each sheet to object lo_data.
'Sheet1' - name of sheet.
Write other part of program.
Whole program.
Run program.
Some limitation:
Conclusion:
First of all, I checked these projects on the same topic:
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_pathRun 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.
- SAP Managed Tags:
13 Comments
| User | Count |
|---|---|
| 6 | |
| 5 | |
| 3 | |
| 3 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 1 | |
| 1 |
