This blog demonstrates how to create an Excel worksheet based on structures of the data dictionary dynamically. The development consists in create an Extensible Markup Language (XML) file using Document Object Model (DOM) API that can be read on Excel program.
The process is executed following below steps:
◈ Create the object and set attributes
◈ Get the data reference from header/item structure of the data dictionary
◈ Create and render a XML document, defined using DOM API, with the necessary structure that is interpreted by Excel
◈ Save a file with XLS extension
Creating a worksheet with the following attributes/functionalities:
◈ Author
◈ Worksheet name
◈ Title
◈ Header
◈ Item table
◈ Sum formula of the numeric fields from the item table
◈ Styles
The main idea was to create a class that could be inherited by others developments and redefined accordingly with the requirements of them.
The class code and the report sample code to execute the process are on the bottom of the page.
Available methods on the local class:
This is the layout of the output files after the sample execution:
The process is executed following below steps:
◈ Create the object and set attributes
◈ Get the data reference from header/item structure of the data dictionary
◈ Create and render a XML document, defined using DOM API, with the necessary structure that is interpreted by Excel
◈ Save a file with XLS extension
Creating a worksheet with the following attributes/functionalities:
◈ Author
◈ Worksheet name
◈ Title
◈ Header
◈ Item table
◈ Sum formula of the numeric fields from the item table
◈ Styles
The main idea was to create a class that could be inherited by others developments and redefined accordingly with the requirements of them.
The class code and the report sample code to execute the process are on the bottom of the page.
Available methods on the local class:
| METHOD | DESCRIPTION |
| CONSTRUCTOR | Create object and set attributes: author, title and worksheet |
| EXECUTE | Execute the process to create a XML document and convert it into a XLS file |
| SAVE_XLS_FILE | Display save dialog window and save the XML file as a binary file with XLS extension |
| RENDER_XML_DOCUMENT | Render the document |
| CREATE_XLS_FILE | Create a binary file from a XML table |
| GET_DATA_REFERENCE | Dynamically get the input data |
| CREATE_XML_DOCUMENT | Create the XML document |
| CREATE_DOCUMENT | Instantiate DOM object and create Workbook node |
| CREATE_STYLE | Create a style |
| CREATE_STYLES | Create styles node |
| DOCUMENT_PROPERTIES | Create document properties node and set author value attribute tag |
| FORMAT_FONT | Set font attributes of a style |
| FORMAT_INTERIOR | Set interior attributes of a style |
| FORMAT_ALIGNMENT | Set alignment attributes of a style |
| ADD_BORDER_TO_STYLE | Add border node to a style |
| FORMAT_BORDER | Set border attributes of border node |
| FORMAT_NUMBER | Set number format attributes of a style |
| ADD_WORKSHEET | Add a new worksheet node on the XML document |
| ADD_TABLE | Add a new table node on the XML document |
| FORMAT_WIDTH_COLUMN | Set columns width of the table node based on the structure data elements length |
| ADD_ROW | Add a new row node on the XML document |
| FORMAT_HEIGHT_ROW | Set row height |
| ADD_CELL | Add a new cell tag on the row node |
| FILL_DATA_CELL | Fill the value of a cell |
| MERGE_CELL | Merge cells |
| DDIF_FIELDINFO_GET | Get data dictionary information |
| FILL_HEADER_ROWS | Fill the header values |
| FILL_ITEM_ROWS | Fill the item values |
| FILL_TOTAL | Create Sum formula for each numeric column of the item table |
| FILL_TITLE | Fill the title value on a cell |
| SET_GRIDLINES | Disable grid layout |
DOM/XML document Interfaces:
| METHOD | DESCRIPTION |
| IF_IXML | Interface of Factory Object |
| IF_IXML_STREAM_FACTORY | Factory for Streams |
| IF_IXML_OSTREAM | Output Streams |
| IF_IXML_RENDERER | Renderer |
| IF_IXML_DOCUMENT | XML Document in DOM Representation |
| IF_IXML_ELEMENT | Element of an XML Document |
This is the layout of the output files after the sample execution:
User Roles (Example using tables: V_USR_NAME/ AGR_USERS)
Purchase Order (Example using tables: EKKO/EKPO)
Delivery (Example using tables: LIKP/LIPS)
Material Document (Example using tables: MKPF/MSEG)
To execute the SAMPLE REPORT:
Create a INCLUDE Z_LOCAL_CLASS_CREATE_XLS.
Create a REPORT Z_TESTE_CREATE_XLS.
Run the Report.
PS.: When you open the XLS file, a warning message is shown as below:
If you want to avoid this message, the extension must be saved as xml instead of the xls extension.
Class code:
*&---------------------------------------------------------------------*
*& Include Z_LOCAL_CLASS_CREATE_XLS
*&---------------------------------------------------------------------*
CLASS zcl_local_create_xls DEFINITION.
PUBLIC SECTION.
DATA t_return TYPE bapiret2_tab .
METHODS constructor
IMPORTING
VALUE(im_title) TYPE string OPTIONAL
VALUE(im_worksheet) TYPE string OPTIONAL
VALUE(im_author) TYPE string OPTIONAL .
METHODS execute
IMPORTING
VALUE(im_header) TYPE any
VALUE(im_item) TYPE any
VALUE(im_item_tab) TYPE ANY TABLE
!im_do_sum TYPE boolean OPTIONAL .
METHODS save_xls_file
IMPORTING
VALUE(im_xls_filename) TYPE string .
PROTECTED SECTION.
PRIVATE SECTION.
TYPES:
ty_xmlline TYPE x LENGTH 255 .
TYPES:
tt_xmlline TYPE STANDARD TABLE OF ty_xmlline .
TYPES ty_dref TYPE REF TO data .
TYPES:
tt_dref TYPE TABLE OF ty_dref .
TYPES:
BEGIN OF ty_total,
fieldname TYPE fieldname,
value TYPE REF TO data,
END OF ty_total .
DATA s_xls_header TYPE ty_dref .
DATA t_dfies_header TYPE dfies_tab .
DATA t_xls_item TYPE ty_dref .
DATA s_xls_item TYPE ty_dref .
DATA t_dfies_item TYPE dfies_tab .
DATA v_workbook TYPE string .
DATA v_worksheet TYPE string .
DATA v_author TYPE string .
DATA v_company TYPE string .
DATA v_title TYPE string .
DATA v_xls_filename TYPE string .
DATA v_current_row TYPE i VALUE 0 ##NO_TEXT.
DATA v_do_sum TYPE boolean .
CONSTANTS c_path TYPE string VALUE 'C:\TEMP\' ##NO_TEXT.
CONSTANTS c_file_extension TYPE string VALUE 'xls' ##NO_TEXT.
CONSTANTS c_file_filter TYPE string VALUE '*.xls' ##NO_TEXT.
DATA s_xls_bin TYPE solix .
DATA t_xls_bin TYPE solix_tab .
DATA xml_size TYPE i .
DATA xml_table TYPE tt_xmlline .
DATA ixml TYPE REF TO if_ixml .
DATA streamfactory TYPE REF TO if_ixml_stream_factory .
DATA ostream TYPE REF TO if_ixml_ostream .
DATA renderer TYPE REF TO if_ixml_renderer .
DATA document TYPE REF TO if_ixml_document .
DATA element_root TYPE REF TO if_ixml_element .
DATA r_worksheet TYPE REF TO if_ixml_element .
DATA r_style TYPE REF TO if_ixml_element .
DATA r_styles TYPE REF TO if_ixml_element .
DATA r_border TYPE REF TO if_ixml_element .
DATA r_table TYPE REF TO if_ixml_element .
DATA r_row TYPE REF TO if_ixml_element .
DATA r_cell TYPE REF TO if_ixml_element .
METHODS render_xml_document .
METHODS create_xls_file .
METHODS get_data_reference
IMPORTING
!im_header TYPE any
!im_item TYPE any
!im_item_tab TYPE ANY TABLE .
METHODS create_xml_document .
METHODS create_document .
METHODS create_style
IMPORTING
!im_style TYPE string .
METHODS create_styles .
METHODS document_properties .
METHODS format_font
IMPORTING
VALUE(im_fontname) TYPE string OPTIONAL
VALUE(im_size) TYPE string OPTIONAL
VALUE(im_bold) TYPE string OPTIONAL
VALUE(im_italic) TYPE string OPTIONAL
VALUE(im_underline) TYPE string OPTIONAL .
METHODS format_interior
IMPORTING
VALUE(im_color) TYPE string OPTIONAL
VALUE(im_pattern) TYPE string OPTIONAL .
METHODS format_alignment
IMPORTING
VALUE(im_h_align) TYPE string OPTIONAL
VALUE(im_v_align) TYPE string OPTIONAL
VALUE(im_wraptext) TYPE string OPTIONAL .
METHODS add_border_to_style .
METHODS format_border
IMPORTING
VALUE(im_position) TYPE string OPTIONAL
VALUE(im_linestyle) TYPE string OPTIONAL
VALUE(im_weight) TYPE string OPTIONAL
VALUE(im_color) TYPE string OPTIONAL .
METHODS format_number
IMPORTING
!im_numberformat TYPE string .
METHODS add_worksheet
IMPORTING
!im_worksheet TYPE string .
METHODS add_table .
METHODS format_width_column .
METHODS add_row .
METHODS format_height_row
IMPORTING
!im_autofitheight TYPE string .
METHODS add_cell
IMPORTING
!im_style TYPE string
VALUE(im_formula) TYPE string OPTIONAL .
METHODS fill_data_cell
IMPORTING
!im_cell_name TYPE string
!im_datatype TYPE string .
METHODS merge_down_cell
IMPORTING
!im_style TYPE string
!im_merge_value TYPE string .
METHODS merge_cell
IMPORTING
!im_style TYPE string
!im_merge_value TYPE string .
METHODS ddif_fieldinfo_get .
METHODS fill_header_rows .
METHODS fill_item_rows .
METHODS fill_total .
METHODS fill_title .
METHODS set_gridlines .
ENDCLASS.
CLASS zcl_local_create_xls IMPLEMENTATION.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->ADD_BORDER_TO_STYLE
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD add_border_to_style.
CLEAR r_border.
r_border = document->create_simple_element( name = 'Borders' parent = r_style ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->ADD_ROW
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD add_row.
CLEAR: r_row.
r_row = document->create_simple_element( name = 'Row' parent = r_table ).
ADD 1 TO v_current_row.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->ADD_TABLE
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD add_table.
CLEAR r_table.
r_table = document->create_simple_element( name = 'Table' parent = r_worksheet ).
r_table->set_attribute_ns( name = 'FullColumns' prefix = 'x' value = '1' ).
r_table->set_attribute_ns( name = 'FullRows' prefix = 'x' value = '1' ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->ADD_WORKSHEET
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_WORKSHEET TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD add_worksheet.
r_worksheet = document->create_simple_element( name = 'Worksheet' parent = element_root ).
r_worksheet->set_attribute_ns( name = 'Name' prefix = 'ss' value = im_worksheet ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method zcl_local_create_xls->CONSTRUCTOR
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_TITLE TYPE STRING(optional)
* | [--->] IM_WORKSHEET TYPE STRING(optional)
* | [--->] IM_AUTHOR TYPE STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD constructor.
v_title = im_title.
v_worksheet = im_worksheet.
v_author = im_author.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->CREATE_DOCUMENT
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD create_document.
CLEAR: ixml, document, element_root.
" Creating a ixml Factory
ixml = cl_ixml=>create( ).
" Creating the DOM Object Model
document = ixml->create_document( ).
" Create Root Node 'Workbook'
element_root = document->create_simple_element( name = 'Workbook' parent = document ).
element_root->set_attribute( name = 'xmlns' value = 'urn:schemas-microsoft-com:office:spreadsheet' ).
DATA(ns_attribute) = document->create_namespace_decl( name = 'ss' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:spreadsheet ' ).
element_root->set_attribute_node( ns_attribute ).
ns_attribute = document->create_namespace_decl( name = 'x' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:excel' ).
element_root->set_attribute_node( ns_attribute ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->CREATE_STYLE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_STYLE TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD create_style.
CHECK im_style IS NOT INITIAL.
CLEAR: r_style.
r_style = document->create_simple_element( name = 'Style' parent = r_styles ).
r_style->set_attribute_ns( name = 'ID' prefix = 'ss' value = im_style ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->CREATE_STYLES
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD create_styles.
" Styles
r_styles = document->create_simple_element( name = 'Styles' parent = element_root ).
" No Style
create_style( im_style = 'NoStyle' ).
" Title
create_style( im_style = 'Title' ).
format_font( im_bold = '1' ).
format_alignment( im_h_align = 'Center' im_v_align = 'Center' im_wraptext = '1' ).
" Style for HeaderTop
create_style( im_style = 'HeaderTop' ).
format_font( im_bold = '1' ).
* format_interior( im_color = '#BFBFBF' im_pattern = 'Solid' ).
format_alignment( im_h_align = 'Center' im_v_align = 'Center' im_wraptext = '1' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
" Style for HeaderLine
create_style( im_style = 'HeaderLine' ).
format_font( im_bold = '1' ).
* format_interior( im_color = '#BFBFBF' im_pattern = 'Solid' ).
format_alignment( im_h_align = 'Center' im_v_align = 'Center' im_wraptext = '1' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).
" Style for HeaderLineDate
create_style( im_style = 'HeaderLineDate' ).
format_number( im_numberformat = 'Short Date' ).
format_font( im_bold = '1' ).
* format_interior( im_color = '#BFBFBF' im_pattern = 'Solid' ).
format_alignment( im_h_align = 'Center' im_v_align = 'Center' im_wraptext = '1' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).
" Style for Item Header
create_style( im_style = 'ItemTop' ).
format_font( im_bold = '1' ).
* format_interior( im_color = '#BFBFBF' im_pattern = 'Solid' ).
format_alignment( im_h_align = 'Center' im_v_align = 'Center' im_wraptext = '1').
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).
" Style for Data
create_style( im_style = 'Data' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Dot' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Dot' im_weight = '1' ).
" Style for DataShortDate
create_style( im_style = 'DataShortDate' ).
format_number( im_numberformat = 'Short Date' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Dot' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Dot' im_weight = '1' ).
" Style for DataNumber
create_style( im_style = 'DataNumber' ).
format_number( im_numberformat = 'Fixed' ).
format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Dot' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Dot' im_weight = '1' ).
" Style for DataNumberAmount
create_style( im_style = 'DataNumberAmount' ).
format_number( im_numberformat = 'Standard' ).
format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Dot' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Dot' im_weight = '1' ).
" Style for DataNumber3Dec
create_style( im_style = 'DataNumber3Dec' ).
format_number( im_numberformat = '# ##0.000' ).
format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Dot' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Dot' im_weight = '1' ).
" Style for DataNumber4Dec
create_style( im_style = 'DataNumber4Dec' ).
format_number( im_numberformat = '# ##0.0000' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).
" Style for Total
create_style( im_style = 'Total' ).
format_font( im_bold = '1' ).
format_alignment( im_h_align = 'Center' im_v_align = 'Center' im_wraptext = '1' ).
format_interior( im_color = '#FADA5E' im_pattern = 'Solid' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).
" Style for TotalNum
create_style( im_style = 'TotalNum' ).
format_font( im_bold = '1' ).
format_interior( im_color = '#FADA5E' im_pattern = 'Solid' ).
format_number( im_numberformat = 'Fixed' ).
format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).
" Style for TotalNumAmount
create_style( im_style = 'TotalNumAmount' ).
format_font( im_bold = '1' ).
format_interior( im_color = '#FADA5E' im_pattern = 'Solid' ).
format_number( im_numberformat = 'Standard' ).
format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).
" Style for TotalNum3Dec
create_style( im_style = 'TotalNum3Dec' ).
format_font( im_bold = '1' ).
format_interior( im_color = '#FADA5E' im_pattern = 'Solid' ).
format_number( im_numberformat = '# ##0.000' ).
format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).
" Style for TotalNum4Dec
create_style( im_style = 'TotalNum4Dec' ).
format_font( im_bold = '1' ).
format_interior( im_color = '#FADA5E' im_pattern = 'Solid' ).
format_number( im_numberformat = '# ##0.0000' ).
format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
add_border_to_style( ).
format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).
" Style for FooterNote
create_style( im_style = 'FooterNote' ).
format_font( im_bold = '1' ).
format_number( im_numberformat = 'Fixed' ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->CREATE_XLS_FILE
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD create_xls_file.
LOOP AT xml_table INTO DATA(wa_xml).
s_xls_bin-line = wa_xml.
APPEND s_xls_bin TO t_xls_bin.
ENDLOOP.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->CREATE_XML_DOCUMENT
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD create_xml_document.
create_document( ).
document_properties( ).
create_styles( ).
add_worksheet( im_worksheet = v_worksheet ).
add_table( ).
" Format columns width based on the data length
format_width_column( ).
" TITLE **************************
fill_title( ).
" HEADER *************************
fill_header_rows( ).
" ITEM ***************************
fill_item_rows( ).
" TOTAL **************************
fill_total( ).
" Disable grid layout
set_gridlines( ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->DDIF_FIELDINFO_GET
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD ddif_fieldinfo_get.
IF s_xls_header IS NOT INITIAL.
FIELD-SYMBOLS: <fs_xls_header> TYPE any.
ASSIGN s_xls_header->* TO <fs_xls_header>.
DATA(lv_header_name) = CONV string( cl_abap_typedescr=>describe_by_data( <fs_xls_header> )->absolute_name+6 ).
CALL FUNCTION 'DDIF_FIELDINFO_GET'
EXPORTING
tabname = CONV tabname( lv_header_name )
TABLES
dfies_tab = t_dfies_header
EXCEPTIONS
not_found = 1
internal_error = 2
OTHERS = 3.
ENDIF.
IF s_xls_item IS NOT INITIAL.
FIELD-SYMBOLS: <fs_xls_item> TYPE any.
ASSIGN s_xls_item->* TO <fs_xls_item>.
DATA(lv_item_name) = CONV string( cl_abap_typedescr=>describe_by_data( <fs_xls_item> )->absolute_name+6 ).
CALL FUNCTION 'DDIF_FIELDINFO_GET'
EXPORTING
tabname = CONV tabname( lv_item_name )
TABLES
dfies_tab = t_dfies_item
EXCEPTIONS
not_found = 1
internal_error = 2
OTHERS = 3.
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->DOCUMENT_PROPERTIES
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD document_properties.
DATA(lr_element_properties) = document->create_simple_element( name = 'DocumentProperties' parent = element_root ).
lr_element_properties->set_attribute( name = 'xmlns' value = 'urn:schemas-microsoft-com:office:office' ).
document->create_simple_element( name = 'Author' value = v_author parent = lr_element_properties ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method zcl_local_create_xls->EXECUTE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_HEADER TYPE ANY
* | [--->] IM_ITEM TYPE ANY
* | [--->] IM_ITEM_TAB TYPE ANY TABLE
* | [--->] IM_DO_SUM TYPE BOOLEAN(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD execute.
TRY.
v_do_sum = im_do_sum.
" Dynamically get the input data
get_data_reference( im_header = im_header
im_item = im_item
im_item_tab = im_item_tab ).
" XML schema
create_xml_document( ).
" Rendering the Document
render_xml_document( ).
" Creation of the Binary file
create_xls_file( ).
CATCH cx_root INTO DATA(lo_root).
APPEND VALUE bapiret2(
type = sy-abcde+4(1)
message = lo_root->get_text( )
) TO t_return.
ENDTRY.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->ADD_CELL
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_STYLE TYPE STRING
* | [--->] IM_FORMULA TYPE STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD add_cell.
CLEAR r_cell.
r_cell = document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = im_style ).
IF im_formula IS NOT INITIAL.
r_cell->set_attribute_ns( name = 'Formula' prefix = 'ss' value = im_formula ).
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FILL_DATA_CELL
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_CELL_NAME TYPE STRING
* | [--->] IM_DATATYPE TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD fill_data_cell.
DATA(lv_aux_str) = im_cell_name.
" Remove sign "-" from the end and put it on the beginning.
IF im_datatype EQ 'Number'
AND im_cell_name CA '-'.
REPLACE ALL OCCURRENCES OF '-' IN lv_aux_str WITH ''.
CONCATENATE '-' lv_aux_str INTO lv_aux_str.
ENDIF.
DATA(lr_data) = document->create_simple_element( name = 'Data' value = lv_aux_str parent = r_cell ).
lr_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = im_datatype ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FILL_HEADER_ROWS
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD fill_header_rows.
CHECK t_dfies_header IS NOT INITIAL.
" Row Header Top
add_row( ).
format_height_row( im_autofitheight = '1' ).
LOOP AT t_dfies_header INTO DATA(ls_dfies_header).
add_cell( im_style = 'NoStyle' ).
" Field label: Short - Medium - long - Heading
fill_data_cell( im_cell_name = CONV #( ls_dfies_header-scrtext_l ) im_datatype = 'String' ).
ENDLOOP.
" Row Header Line
add_row( ).
format_height_row( im_autofitheight = '1' ).
FIELD-SYMBOLS: <fs_xls_header> TYPE any.
ASSIGN s_xls_header->* TO <fs_xls_header>.
LOOP AT t_dfies_header INTO ls_dfies_header.
ASSIGN COMPONENT sy-tabix OF STRUCTURE <fs_xls_header> TO FIELD-SYMBOL(<fs_header_value>).
DATA(descr_ref) = cl_abap_typedescr=>describe_by_data( <fs_header_value> ).
CASE descr_ref->type_kind.
WHEN cl_abap_typedescr=>typekind_date.
DATA: lv_date_out(10).
WRITE <fs_header_value> TO lv_date_out DD/MM/YYYY.
REPLACE ALL OCCURRENCES OF '.' IN lv_date_out WITH '/'.
add_cell( im_style = 'NoStyle' ).
fill_data_cell( im_cell_name = CONV #( lv_date_out ) im_datatype = 'String' ).
WHEN OTHERS.
add_cell( im_style = 'NoStyle' ).
fill_data_cell( im_cell_name = CONV #( <fs_header_value> ) im_datatype = 'String' ).
ENDCASE.
ENDLOOP.
add_row( ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FILL_ITEM_ROWS
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD fill_item_rows.
DATA: lv_date_out(10),
lv_num_out TYPE char20.
CHECK t_dfies_item IS NOT INITIAL.
" Row Item Top
add_row( ).
format_height_row( im_autofitheight = '1' ).
LOOP AT t_dfies_item INTO DATA(ls_dfies_item).
add_cell( im_style = 'ItemTop' ).
" Field label: Short - Medium - long - Heading
fill_data_cell( im_cell_name = CONV #( ls_dfies_item-scrtext_l ) im_datatype = 'String' ).
ENDLOOP.
FIELD-SYMBOLS: <fs_xls_item> TYPE ANY TABLE.
ASSIGN t_xls_item->* TO <fs_xls_item>.
" Row Item Line
LOOP AT <fs_xls_item> ASSIGNING FIELD-SYMBOL(<fs_xls_item_line>).
add_row( ).
format_height_row( im_autofitheight = '1' ).
LOOP AT t_dfies_item INTO ls_dfies_item.
ASSIGN COMPONENT sy-tabix OF STRUCTURE <fs_xls_item_line> TO FIELD-SYMBOL(<fs_item_value>).
DATA(lr_descr_ref) = cl_abap_typedescr=>describe_by_data( <fs_item_value> ).
CASE lr_descr_ref->type_kind.
WHEN cl_abap_typedescr=>typekind_char.
add_cell( im_style = 'Data' ).
fill_data_cell( im_cell_name = CONV #( <fs_item_value> ) im_datatype = 'String' ).
WHEN cl_abap_typedescr=>typekind_date.
WRITE <fs_item_value> TO lv_date_out DD/MM/YYYY.
REPLACE ALL OCCURRENCES OF '.' IN lv_date_out WITH '/'.
add_cell( im_style = 'DataShortDate' ).
fill_data_cell( im_cell_name = CONV #( lv_date_out ) im_datatype = 'String' ).
WHEN cl_abap_typedescr=>typekind_int.
lv_num_out = |{ <fs_item_value> ALPHA = IN }|.
add_cell( im_style = 'DataNumber' ).
fill_data_cell( im_cell_name = CONV #( lv_num_out ) im_datatype = 'Number' ).
WHEN cl_abap_typedescr=>typekind_packed.
lv_num_out = |{ <fs_item_value> ALPHA = IN }|.
CASE lr_descr_ref->decimals.
WHEN 2.
add_cell( im_style = 'DataNumberAmount' ).
WHEN 3.
add_cell( im_style = 'DataNumber3Dec' ).
WHEN 4.
add_cell( im_style = 'DataNumber4Dec' ).
WHEN OTHERS.
add_cell( im_style = 'DataNumber' ).
ENDCASE.
fill_data_cell( im_cell_name = CONV #( lv_num_out ) im_datatype = 'Number' ).
WHEN OTHERS.
add_cell( im_style = 'Data' ).
fill_data_cell( im_cell_name = CONV #( <fs_item_value> ) im_datatype = 'String' ).
ENDCASE.
ENDLOOP.
ENDLOOP.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->merge_CELL
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_STYLE TYPE STRING
* | [--->] IM_MERGE_VALUE TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD merge_cell.
add_cell( im_style = im_style ).
r_cell->set_attribute_ns( name = 'MergeAcross' prefix = 'ss' value = im_merge_value ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->merge_DOWN_CELL
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_STYLE TYPE STRING
* | [--->] IM_MERGE_VALUE TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD merge_down_cell.
add_cell( im_style ).
r_cell->set_attribute_ns( name = 'MergeDown' prefix = 'ss' value = im_merge_value ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FILL_TITLE
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD fill_title.
CHECK v_title IS NOT INITIAL.
add_row( ).
DATA(lv_lines) = lines( t_dfies_item ) - 1.
merge_cell( im_style = 'Title' im_merge_value = CONV #( lv_lines ) ).
fill_data_cell( im_cell_name = v_title im_datatype = 'String' ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FILL_TOTAL
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD fill_total.
CHECK v_do_sum IS NOT INITIAL.
add_row( ).
format_height_row( im_autofitheight = '1' ).
FIELD-SYMBOLS: <fs_xls_item> TYPE ANY TABLE.
ASSIGN t_xls_item->* TO <fs_xls_item>.
DATA(lv_item_lines) = lines( <fs_xls_item> ).
" Create Sum formula for each numeric column of the item table
LOOP AT t_dfies_item INTO DATA(ls_dfies_item).
DATA(lv_tabix) = sy-tabix.
DATA(lv_formula) = '=SUM(R[-' && lv_item_lines && ']' && 'C:R[-1]C)'.
DATA(lr_descr_ref) = cl_abap_typedescr=>describe_by_name( ls_dfies_item-rollname ).
IF lr_descr_ref->type_kind EQ lr_descr_ref->typekind_int.
add_cell( im_style = 'TotalNum' im_formula = lv_formula ).
fill_data_cell( im_cell_name = space im_datatype = 'Number' ).
ELSEIF lr_descr_ref->type_kind EQ lr_descr_ref->typekind_packed.
CASE lr_descr_ref->decimals.
WHEN 2.
add_cell( im_style = 'TotalNumAmount' im_formula = lv_formula ).
WHEN 3.
add_cell( im_style = 'TotalNum3Dec' im_formula = lv_formula ).
WHEN 4.
add_cell( im_style = 'TotalNum4Dec' im_formula = lv_formula ).
WHEN OTHERS.
add_cell( im_style = 'TotalNum' im_formula = lv_formula ).
ENDCASE.
fill_data_cell( im_cell_name = space im_datatype = 'Number' ).
ELSE.
IF lv_tabix = 1.
" Total text
add_cell( im_style = 'Total' ).
fill_data_cell( im_cell_name = 'TOTAIS' im_datatype = 'String' ).
ELSE.
add_cell( im_style = 'Total' ).
fill_data_cell( im_cell_name = '' im_datatype = 'String' ).
ENDIF.
ENDIF.
ENDLOOP.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_ALIGNMENT
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_H_ALIGN TYPE STRING(optional)
* | [--->] IM_V_ALIGN TYPE STRING(optional)
* | [--->] IM_WRAPTEXT TYPE STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD format_alignment.
DATA(lr_format) = document->create_simple_element( name = 'Alignment' parent = r_style ).
" Horizontal Alignment: Center - CenterAcrossSelection - Distributed - Fill - General - Justify - Left - Right
IF im_h_align IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'Horizontal' prefix = 'ss' value = im_h_align ).
ENDIF.
" Vertical Alignment: Bottom - Center - Distributed - Justify - Top
IF im_v_align IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'Vertical' prefix = 'ss' value = im_v_align ).
ENDIF.
" Text Control: WrapText - ShrinkToFit - MergeCells
" 0 - Inactive 1 - Active
IF im_wraptext IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'WrapText' prefix = 'ss' value = im_wraptext ).
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_BORDER
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_POSITION TYPE STRING(optional)
* | [--->] IM_LINESTYLE TYPE STRING(optional)
* | [--->] IM_WEIGHT TYPE STRING(optional)
* | [--->] IM_COLOR TYPE STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD format_border.
DATA(lr_format) = document->create_simple_element( name = 'Border' parent = r_border ).
" Position: Top - Left - Right - Bottom - DiagonalUp
IF im_position IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'Position' prefix = 'ss' value = im_position ).
ENDIF.
" Line style: Continuos - Dot - DashDot - Dash- SlantDashDot - Double
IF im_linestyle IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'LineStyle' prefix = 'ss' value = im_linestyle ).
ENDIF.
" Line weight: Thin - Medium - Thick
IF im_weight IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'Weight' prefix = 'ss' value = im_weight ).
ENDIF.
" Color
IF im_color IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'Color' prefix = 'ss' value = im_color ).
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_FONT
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_FONTNAME TYPE STRING(optional)
* | [--->] IM_SIZE TYPE STRING(optional)
* | [--->] IM_BOLD TYPE STRING(optional)
* | [--->] IM_ITALIC TYPE STRING(optional)
* | [--->] IM_UNDERLINE TYPE STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD format_font.
DATA(lr_format) = document->create_simple_element( name = 'Font' parent = r_style ).
" Name
IF im_fontname IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'FontName' prefix = 'ss' value = im_fontname ).
ENDIF.
" Size
IF im_size IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'Size' prefix = 'ss' value = im_size ).
ENDIF.
" Bold: 0 - Inactive 1 - Active
IF im_bold IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'Bold' prefix = 'ss' value = im_bold ).
ENDIF.
" Italic: 0 - Inactive 1 - Active
IF im_italic IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'Italic' prefix = 'ss' value = im_italic ).
ENDIF.
" Underline: Single - Singular
IF im_underline IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'Underline' prefix = 'ss' value = im_underline ).
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_HEIGHT_ROW
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_AUTOFITHEIGHT TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD format_height_row.
IF im_autofitheight IS NOT INITIAL.
r_row->set_attribute_ns( name = 'AutoFitHeight' prefix = 'ss' value = im_autofitheight ).
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_INTERIOR
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_COLOR TYPE STRING(optional)
* | [--->] IM_PATTERN TYPE STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD format_interior.
DATA(lr_format) = document->create_simple_element( name = 'Interior' parent = r_style ).
IF im_color IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'Color' prefix = 'ss' value = im_color ).
ENDIF.
IF im_pattern IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'Pattern' prefix = 'ss' value = im_pattern ).
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_NUMBER
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_NUMBERFORMAT TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD format_number.
DATA(lr_format) = document->create_simple_element( name = 'NumberFormat' parent = r_style ).
" Number format: General - Number - Currency - Date - Time - Percentage - ...
IF im_numberformat IS NOT INITIAL.
lr_format->set_attribute_ns( name = 'Format' prefix = 'ss' value = im_numberformat ).
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_WIDTH_COLUMN
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD format_width_column.
LOOP AT t_dfies_item INTO DATA(ls_dfies_item).
DATA(lr_column) = document->create_simple_element( name = 'Column' parent = r_table ).
lr_column->set_attribute_ns( name = 'AutoFitWidth' prefix = 'ss' value = '1' ).
ls_dfies_item-outputlen = COND #( WHEN ls_dfies_item-outputlen GE 5
THEN ls_dfies_item-outputlen * 8
ELSE 40 ).
" Maximum limit - 255 characters
ls_dfies_item-outputlen = COND #( WHEN ls_dfies_item-outputlen GT 255
THEN 255
ELSE ls_dfies_item-outputlen ).
lr_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = CONV #( ls_dfies_item-outputlen ) ).
ENDLOOP.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->GET_DATA_REFERENCE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_HEADER TYPE ANY
* | [--->] IM_ITEM TYPE ANY
* | [--->] IM_ITEM_TAB TYPE ANY TABLE
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD get_data_reference.
GET REFERENCE OF im_header INTO s_xls_header.
GET REFERENCE OF im_item INTO s_xls_item.
GET REFERENCE OF im_item_tab INTO t_xls_item.
" Get data dictionary information
ddif_fieldinfo_get( ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->RENDER_XML_DOCUMENT
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD render_xml_document.
" Creating a Stream Factory
DATA(lr_streamfactory) = ixml->create_stream_factory( ).
" Connect Internal XML Table to Stream Factory
DATA(lr_ostream) = lr_streamfactory->create_ostream_itable( table = xml_table ).
" Rendering the Document
DATA(lr_renderer) = ixml->create_renderer( ostream = lr_ostream document = document ).
" Renders the attached XML document into the output stream
DATA(lr_rc) = lr_renderer->render( ).
" Saving the XML Document
xml_size = lr_ostream->get_num_written_raw( ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method zcl_local_create_xls->SAVE_XLS_FILE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_XLS_FILENAME TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD save_xls_file.
DATA: lv_filename TYPE string,
lv_path TYPE string,
lv_fullpath TYPE string,
lv_user_action TYPE i.
" Display save dialog window
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
file_filter = c_file_filter
default_extension = c_file_extension
default_file_name = im_xls_filename
initial_directory = c_path
CHANGING
filename = lv_filename
path = lv_path
fullpath = lv_fullpath
user_action = lv_user_action
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
invalid_default_file_name = 3
not_supported_by_gui = 4
OTHERS = 5.
IF sy-subrc = 0.
IF lv_user_action = 0.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = lv_fullpath
filetype = 'BIN'
TABLES
data_tab = t_xls_bin.
ENDIF.
ELSE.
MESSAGE e001(00) WITH 'Error saving file.' INTO DATA(lv_message).
APPEND VALUE bapiret2(
id = sy-msgid
type = sy-msgty
number = sy-msgno
message = lv_message
message_v1 = sy-msgv1
message_v2 = sy-msgv2
message_v3 = sy-msgv3
message_v4 = sy-msgv4
) TO t_return.
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->SET_GRIDLINES
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD set_gridlines.
DATA(r_worksheetoptions) = document->create_simple_element( name = 'WorksheetOptions' parent = r_worksheet ).
r_worksheetoptions->set_attribute( name = 'xmlns' value = 'urn:schemas-microsoft-com:office:excel' ).
document->create_simple_element( name = 'DoNotDisplayGridlines' parent = r_worksheetoptions ).
ENDMETHOD.
ENDCLASS.
Sample Report code
*&---------------------------------------------------------------------*
*& Report Z_TESTE_CREATE_XLS
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT z_teste_create_xls.
" Local Class ****************************
INCLUDE z_local_class_create_xls.
START-OF-SELECTION.
" Set ramdom offset
DATA: lv_offset TYPE integer2.
CALL FUNCTION 'RANDOM_I2'
EXPORTING
rnd_min = 1
rnd_max = 100
IMPORTING
rnd_value = lv_offset.
" User Roles
DATA: ls_usr_name TYPE v_usr_name,
ls_agr_users TYPE agr_users,
lt_agr_users TYPE TABLE OF agr_users.
" Selects the user name
SELECT SINGLE *
INTO ls_usr_name
FROM v_usr_name
WHERE bname = sy-uname.
" Assignment of roles to users
SELECT *
INTO TABLE lt_agr_users
FROM agr_users
WHERE uname = sy-uname.
DATA(lo_xls_file) = NEW zcl_local_create_xls( im_title = 'User Roles Title'
im_author = CONV string( sy-uname )
im_worksheet = 'User Roles' ).
lo_xls_file->execute( im_header = ls_usr_name
im_item = ls_agr_users
im_item_tab = lt_agr_users
im_do_sum = abap_false ).
lo_xls_file->save_xls_file( 'User_roles' ).
FREE lo_xls_file.
" Purchase Order
DATA: ls_ekko TYPE ekko,
ls_ekpo TYPE ekpo,
lt_ekpo TYPE TABLE OF ekpo.
" Purchasing Document Header
SELECT *
FROM ekko
ORDER BY ebeln
INTO @ls_ekko UP TO 1 ROWS
OFFSET @lv_offset.
ENDSELECT.
" Purchasing Document Item
SELECT *
INTO TABLE lt_ekpo
FROM ekpo
WHERE ebeln = ls_ekko-ebeln.
lo_xls_file = NEW zcl_local_create_xls( im_author = CONV string( sy-uname )
im_worksheet = 'Purchase Order' ).
lo_xls_file->execute( im_header = ls_ekko
im_item = ls_ekpo
im_item_tab = lt_ekpo
im_do_sum = abap_true ).
lo_xls_file->save_xls_file( 'PurchaseOrder_xls_file' ).
FREE lo_xls_file.
" Delivery
DATA: ls_likp TYPE likp,
ls_lips TYPE lips,
lt_lips TYPE TABLE OF lips.
" Delivery Header Data
SELECT *
FROM likp
ORDER BY vbeln
INTO @ls_likp UP TO 1 ROWS
OFFSET @lv_offset.
ENDSELECT.
" Delivery Item data
SELECT *
INTO TABLE lt_lips
FROM lips
WHERE vbeln = ls_likp-vbeln.
lo_xls_file = NEW zcl_local_create_xls( im_author = CONV string( sy-uname )
im_worksheet = 'Delivery' ).
lo_xls_file->execute( im_header = ls_likp
im_item = ls_lips
im_item_tab = lt_lips
im_do_sum = abap_true ).
lo_xls_file->save_xls_file( 'Delivery_xls_file' ).
FREE lo_xls_file.
" Doc material
DATA: ls_mkpf TYPE mkpf,
ls_mseg TYPE mseg,
lt_mseg TYPE TABLE OF mseg.
" Header Material Document
SELECT *
FROM mkpf
ORDER BY mblnr
INTO @ls_mkpf UP TO 1 ROWS
OFFSET @lv_offset.
ENDSELECT.
" Document Segment Material
SELECT *
INTO TABLE lt_mseg
FROM mseg
WHERE mblnr = ls_mkpf-mblnr.
lo_xls_file = NEW zcl_local_create_xls( "im_title = 'TÃtulo'
im_author = CONV string( sy-uname )
im_worksheet = 'Doc. Material' ).
lo_xls_file->execute( im_header = ls_mkpf
im_item = ls_mseg
im_item_tab = lt_mseg
im_do_sum = abap_true ).
lo_xls_file->save_xls_file( 'DocMaterial_xls_file' ).
Hello Sabrina,
ReplyDeleteNice posting, congrats :)
I have couple of issues with formatting excel with this method. If you know and help me, I would be grateful to you :)
I already composed a program with this method and it works fine except 3 things: First one, I have not achieved both to bold font and to make colored in a cell with this method. It makes either bold or colored. Not at the same time. Second issue is to display numbers with thousand separator with dot such as '123.456.789'. It displays only one separator such as '123456,789' And the last one is to freeze first row of the table in excel file.
Thank you and regards.