Introduction
There could be multiple scenarios where an excel upload and download using ABAP Report Program or a Odata service with different sheets or tabs in a Excel needs to developed as shown in the below screenshot.
Excel Upload with multiple tabs:
There has been multiple instances where most of the developers tend to use the function module ALSM_EXCEL_TO_INTERNAL_TABLE,TEXT_CONVERT_XLS_TO_SAP. This will work fine when we test the program or Odata from SAP GUI.
Disadvantages of using the ALSM_EXCEL_TO_INTERNAL_TABLE,TEXT_CONVERT_XLS_TO_SAP FM :
- This function module which uses OLE object for Office Integration solution internally will produce a dump and is not supported when used in the SAP ABAP Program Odata when called from Fiori launch pad since this is web based HTTP application with WEBGUI and no OLE is supported similar to the SAP GUI .
Proposed Solution:
The below code can be leveraged to upload and read the excel with multiple excel tabs or worksheets .
- Use the Class CL_FDT_XL_SPREADSHEET in order to over come the above issue by instantiating and passing the XSTRING data in the constructor
- In Odata use the CREATE_STREAM method to write the below code
- IF_FDT_DOC_SPREADSHEET~GET_WORKSHEET_NAMES returns both the tabs as internal tables .
- Use the method IF_FDT_DOC_SPREADSHEET~GET_ITAB_FROM_WORKSHEET to get the corresponding tab into internal table by passing the sheet or tab number.
DATA : lv_filename TYPE string,
lt_records TYPE solix_tab,
lv_headerxstring TYPE xstring,
lv_filelength TYPE i.
lv_filename = p_file.
*-- Read the Input File
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = lv_filename
filetype = 'BIN'
IMPORTING
filelength = lv_filelength
header = lv_headerxstring
TABLES
data_tab = lt_records
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
OTHERS = 17.
IF sy-subrc IS NOT INITIAL.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
*--convert binary data to xstring
CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
EXPORTING
input_length = lv_filelength
IMPORTING
buffer = lv_headerxstring
TABLES
binary_tab = lt_records
EXCEPTIONS
failed = 1
OTHERS = 2.
IF sy-subrc IS NOT INITIAL.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
DATA : lo_excel_ref TYPE REF TO cl_fdt_xl_spreadsheet .
TRY .
lo_excel_ref = NEW cl_fdt_xl_spreadsheet(
document_name = lv_filename
xdocument = lv_headerxstring ) .
CATCH cx_fdt_excel_core.
ENDTRY .
IF lo_excel_ref IS BOUND.
*--Get List of Worksheets
lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING
worksheet_names = DATA(lt_worksheets) ).
IF NOT lt_worksheets IS INITIAL.
*-- Header Tab
READ TABLE lt_worksheets INTO DATA(lv_woksheetname) INDEX 1.
DATA(lo_data_ref) = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet(
lv_woksheetname ).
*-- Excel work sheet data in dyanmic internal table
ASSIGN lo_data_ref->* TO <gt_data_h>.
*-- Item Tab Details
READ TABLE lt_worksheets INTO lv_woksheetname INDEX 2.
lo_data_ref = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet(
lv_woksheetname ).
*-- Excel work sheet data in dyanmic internal table
ASSIGN lo_data_ref->* TO <gt_data_l>.
ENDIF.
ENDIF.Once the Data has been read into two different internal tables use the below methods to the read the data and process it.
In the below example the dynamic internal tables are converted into header and items strucutres.
Similarly dynamic structure names can also be provided for the same.
DATA : lv_numberofcolumns TYPE i,
lv_date_string TYPE string,
lv_target_date_field TYPE datum,
lv_flag TYPE boolean,
l_param1 TYPE syst-msgv1,
l_param2 TYPE syst-msgv1,
lv_convert_date(10) TYPE c,
lv_skip TYPE i,
lv_empty TYPE boole_d,
lv_empty_tmp TYPE boole_d.
DATA: lo_struct TYPE REF TO cl_abap_structdescr,
lo_table TYPE REF TO cl_abap_tabledescr,
lr_type TYPE REF TO cl_abap_typedescr.
FIELD-SYMBOLS: <struc_hdr> TYPE any,
<struc_itm> TYPE any,
<ls_data> TYPE any,
<lv_field> TYPE any,
<fs_excel_data> TYPE STANDARD TABLE,
<fs_data> TYPE any,
<fs_comp> TYPE any.
DATA : lt_field TYPE cl_abap_structdescr=>component_table,
lt_comp TYPE cl_abap_structdescr=>component_table.
*-- find out number of columns dynamically from table
lo_table ?= cl_abap_structdescr=>describe_by_data( gt_tab1 ).
lo_struct ?= lo_table->get_table_line_type( ).
lt_field = lo_struct->get_components( ).
IF <gt_data_h> IS ASSIGNED.
*-- Build the Header details
LOOP AT <gt_data_h> ASSIGNING <ls_data> FROM 3.
*-- First, verify if field exists in dictionary
lv_empty = abap_true.
APPEND INITIAL LINE TO gt_tab1 ASSIGNING FIELD-SYMBOL(<ls_context>).
*-- Initialize flag
lv_flag = abap_true.
l_param1 = sy-tabix.
CONDENSE l_param1 NO-GAPS.
WHILE lv_flag = abap_true.
lv_empty_tmp = abap_false.
*-- Read columnwise entries
ASSIGN COMPONENT sy-index OF STRUCTURE <ls_data> TO <fs_comp> .
IF <fs_comp> IS NOT ASSIGNED.
lv_flag = abap_false.
*-- Exit the loop when a row ends
EXIT.
ELSE.
IF <fs_comp> IS INITIAL.
lv_empty_tmp = abap_true.
ENDIF.
READ TABLE lt_field ASSIGNING FIELD-SYMBOL(<ls_field>) INDEX sy-index .
IF sy-subrc = 0.
*-- Get the Datatype by finding the field name
ASSIGN COMPONENT <ls_field>-name OF STRUCTURE <ls_context> TO FIELD-SYMBOL(<ls_value>).
l_param2 = <ls_field>-name.
CONDENSE l_param2 NO-GAPS.
lr_type ?= <ls_field>-type.
* CASE lr_type->type_kind.
**-- Convert Date to SAP readable Format
* WHEN cl_abap_typedescr=>typekind_date.
* IF <fs_comp> IS NOT INITIAL.
lv_convert_date = <fs_comp> .
if <ls_field>-name = 'BLDAT' or <ls_field>-name = 'BUDAT'.
FIND REGEX '^\d{4}[/|-]\d{1,2}[/|-]\d{1,2}$' IN lv_convert_date.
IF sy-subrc = 0.
CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
EXPORTING
date_in = lv_convert_date
date_format_in = 'DYMD'
to_output_format = ' '
to_internal_format = 'X'
IMPORTING
date_out = lv_convert_date
EXCEPTIONS
illegal_date = 1
illegal_date_format = 2
no_user_date_format = 3
OTHERS = 4.
IF sy-subrc = 0.
<fs_comp> = lv_convert_date .
<ls_value> = <fs_comp>.
ELSE.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ELSE.
" date format DD/MM/YYYY
FIND REGEX '^\d{1,2}[/|-]\d{1,2}[/|-]\d{4}$' IN lv_convert_date.
IF sy-subrc = 0.
CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
EXPORTING
date_in = lv_convert_date
date_format_in = 'DDMY'
to_output_format = ' '
to_internal_format = 'X'
IMPORTING
date_out = lv_convert_date
EXCEPTIONS
illegal_date = 1
illegal_date_format = 2
no_user_date_format = 3
OTHERS = 4.
IF sy-subrc = 0.
<fs_comp> = lv_convert_date .
<ls_value> = <fs_comp>.
* ELSE.
*
* MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
* WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDIF.
ENDIF.
ENDIF.
* WHEN OTHERS.
<ls_value> = <fs_comp>.
* ENDCASE.
ENDIF.
ENDIF.
UNASSIGN <fs_comp>.
ENDWHILE.
ENDLOOP .
*-- Build the line items details
lo_table ?= cl_abap_structdescr=>describe_by_data( gt_tab2 ).
lo_struct ?= lo_table->get_table_line_type( ).
lt_field = lo_struct->get_components( ).
LOOP AT <gt_data_l> ASSIGNING <ls_data> FROM 3 .
*--First, verify if field exists in dictionary
lv_empty = abap_true.
APPEND INITIAL LINE TO gt_tab2 ASSIGNING FIELD-SYMBOL(<ls_context_1>).
*-- Initialize flag
lv_flag = abap_true.
l_param1 = sy-tabix.
CONDENSE l_param1 NO-GAPS.
WHILE lv_flag = abap_true.
lv_empty_tmp = abap_false.
*-- Read columnwise entries
ASSIGN COMPONENT sy-index OF STRUCTURE <ls_data> TO <fs_comp> .
IF <fs_comp> IS NOT ASSIGNED.
lv_flag = abap_false.
*-- Exit the loop when a row ends
EXIT.
ELSE.
IF <fs_comp> IS INITIAL.
lv_empty_tmp = abap_true.
ENDIF.
READ TABLE lt_field ASSIGNING FIELD-SYMBOL(<ls_field1>) INDEX sy-index .
IF sy-subrc = 0.
ASSIGN COMPONENT <ls_field1>-name OF STRUCTURE <ls_context_1> TO FIELD-SYMBOL(<ls_value_1>).
ENDIF.
ENDIF.
<ls_value_1> = <fs_comp>.
*-- Unassign field symbol
UNASSIGN <fs_comp>.
ENDWHILE.
ENDLOOP .
ENDIF.Additional check for the Date format and other data structure can be performed in the loop as shown above.
Generate and Download Excel with multiple tabs or sheets from WEBGUI or Fiori Lauch pad:
There are classes like CL_SALV_EXPORT_TOOL_ATS_XLS which could be used to achieve the above functionality. However we could not use this since it will not work creating multiple tabs or sheets.
Suggested Solution:
The only solution which could solve this problem :
- Install the abapGit using the below link: abapGit documentation – Installation
- Perform the SSL setup using the STRUST transaction
- Import the custom code required for the excel generation using the below link: https://github.com/sapmentors/abap2xlsx
- This custom code should be captured in a transport and the same should be transported along with your report or Odata.
Below is the sample code using the abapGit classes to achieve the same:
*&---------------------------------------------------------------------*
*& Report ZDEMO_EXCEL4
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT zdemo_excel4.
DATA: lo_excel TYPE REF TO zcl_excel,
lo_worksheet TYPE REF TO zcl_excel_worksheet,
lo_hyperlink TYPE REF TO zcl_excel_hyperlink,
lv_tabcolor TYPE zexcel_s_tabcolor,
ls_header TYPE zexcel_s_worksheet_head_foot,
ls_footer TYPE zexcel_s_worksheet_head_foot.
CONSTANTS: gc_save_file_name TYPE string VALUE '04_Sheets.xlsx'.
INCLUDE zdemo_excel_outputopt_incl.
START-OF-SELECTION.
" Creates active sheet
CREATE OBJECT lo_excel.
" Get active sheet
lo_worksheet = lo_excel->get_active_worksheet( ).
lo_worksheet->set_title( ip_title = 'Sheet1' ).
lo_worksheet->zif_excel_sheet_properties~selected = zif_excel_sheet_properties=>c_selected.
lo_worksheet->set_cell( ip_column = 'B' ip_row = 2 ip_value = 'This is the first sheet' ).
* Set color to tab with sheetname - Red
lv_tabcolor-rgb = zcl_excel_style_color=>create_new_argb( ip_red = 'FF'
ip_green = '00'
ip_blu = '00' ).
lo_worksheet->set_tabcolor( lv_tabcolor ).
lo_hyperlink = zcl_excel_hyperlink=>create_internal_link( iv_location = 'Sheet2!B2' ).
lo_worksheet->set_cell( ip_column = 'B' ip_row = 3 ip_value = 'This is link to second sheet' ip_hyperlink = lo_hyperlink ).
" Page printing settings
lo_worksheet->sheet_setup->set_page_margins( ip_header = '1' ip_footer = '1' ip_unit = 'cm' ).
lo_worksheet->sheet_setup->black_and_white = 'X'.
lo_worksheet->sheet_setup->fit_to_page = 'X'. " you should turn this on to activate fit_to_height and fit_to_width
lo_worksheet->sheet_setup->fit_to_height = 0. " used only if ip_fit_to_page = 'X'
lo_worksheet->sheet_setup->fit_to_width = 2. " used only if ip_fit_to_page = 'X'
lo_worksheet->sheet_setup->orientation = zcl_excel_sheet_setup=>c_orientation_landscape.
lo_worksheet->sheet_setup->page_order = zcl_excel_sheet_setup=>c_ord_downthenover.
lo_worksheet->sheet_setup->paper_size = zcl_excel_sheet_setup=>c_papersize_a4.
lo_worksheet->sheet_setup->scale = 80. " used only if ip_fit_to_page = SPACE
" Header and Footer
ls_header-right_value = 'print date &D'.
ls_header-right_font-size = 8.
ls_header-right_font-name = zcl_excel_style_font=>c_name_arial.
ls_footer-left_value = '&Z&F'. "Path / Filename
ls_footer-left_font = ls_header-right_font.
ls_footer-right_value = 'page &P of &N'. "page x of y
ls_footer-right_font = ls_header-right_font.
lo_worksheet->sheet_setup->set_header_footer( ip_odd_header = ls_header
ip_odd_footer = ls_footer ).
lo_worksheet = lo_excel->add_new_worksheet( ).
lo_worksheet->set_title( ip_title = 'Sheet2' ).
* Set color to tab with sheetname - Green
lv_tabcolor-rgb = zcl_excel_style_color=>create_new_argb( ip_red = '00'
ip_green = 'FF'
ip_blu = '00' ).
lo_worksheet->set_tabcolor( lv_tabcolor ).
lo_worksheet->zif_excel_sheet_properties~selected = zif_excel_sheet_properties=>c_selected.
lo_worksheet->set_cell( ip_column = 'B' ip_row = 2 ip_value = 'This is the second sheet' ).
lo_hyperlink = zcl_excel_hyperlink=>create_internal_link( iv_location = 'Sheet1!B2' ).
lo_worksheet->set_cell( ip_column = 'B' ip_row = 3 ip_value = 'This is link to first sheet' ip_hyperlink = lo_hyperlink ).
lo_worksheet->set_cell( ip_column = 'B' ip_row = 4 ip_value = 'Sheet3 is hidden' ).
lo_worksheet->sheet_setup->set_header_footer( ip_odd_header = ls_header
ip_odd_footer = ls_footer ).
lo_worksheet = lo_excel->add_new_worksheet( ).
lo_worksheet->set_title( ip_title = 'Sheet3' ).
* Set color to tab with sheetname - Blue
lv_tabcolor-rgb = zcl_excel_style_color=>create_new_argb( ip_red = '00'
ip_green = '00'
ip_blu = 'FF' ).
lo_worksheet->set_tabcolor( lv_tabcolor ).
lo_worksheet->zif_excel_sheet_properties~hidden = zif_excel_sheet_properties=>c_hidden.
lo_worksheet->sheet_setup->set_header_footer( ip_odd_header = ls_header
ip_odd_footer = ls_footer ).
lo_worksheet = lo_excel->add_new_worksheet( ).
lo_worksheet->set_title( ip_title = 'Sheet4' ).
* Set color to tab with sheetname - other color
lv_tabcolor-rgb = zcl_excel_style_color=>create_new_argb( ip_red = '00'
ip_green = 'FF'
ip_blu = 'FF' ).
lo_worksheet->set_tabcolor( lv_tabcolor ).
lo_worksheet->set_cell( ip_column = 'B' ip_row = 2 ip_value = 'Cell B3 has value 0' ).
lo_worksheet->set_cell( ip_column = 'B' ip_row = 3 ip_value = 0 ).
lo_worksheet->zif_excel_sheet_properties~show_zeros = zif_excel_sheet_properties=>c_hidezero.
lo_worksheet->sheet_setup->set_header_footer( ip_odd_header = ls_header
ip_odd_footer = ls_footer ).
lo_excel->set_active_sheet_index_by_name( 'Sheet1' ).
*** Create output
lcl_output=>output( lo_excel ).
Additionally we can also add hyperlinks and also colors to the excel sheets using the abapGit.
But the only challenge would be to importing additional classes into SAP systems which needsexplicit approvals from system owner.
