Sometimes you just want to have a simple internal table from your ABAP program into a simple Excel xlsx format. You might want to store this one on the client pc, on the application server or even send it as an email attachment. And you might want to do it in the coming 10 Minutes or less before your coffee gets cold.

 

Prerequisite: it’s all based on the fine functionality in these standard packages: CL_SXML_WRITER, SALV_BS_EXPORT, CL_OPENXML_PACKAGE.

 

You’ve got those packages? Let’s get started. Download the attached files, create 3 Simple Transformations and 1 Class. That’s it. You can start using it with the 2 demo programs.

 

 

  1. From the attachment simpleTransformations.txt, create these Simple Transformations in transaction STRANS:
    • ZSCN_XLSX_SHEET from the code below.
    • ZSCN_XLSX_STYLE from the code below.
    • ZSCN_XLSX_CUSTOM_DOC_PROPS from the code below.
  2. Create and activate the class ZSCN_CL_XLSX_WRITER in Transaction SE24
    • Make sure your class builder editor is in “Source Code-Based Class Builder” mode. This makes the copy / paste much easier. You can set this from the menu in SE24, Utilities –> Settings, then in the tab “Class Builder”, check the option “Source Code-Based Class Builder”, or switch it as shown in the Video.
    • Create the class ZSCN_CL_XLSX_WRITER from the code below.
  3. Celebrate, you’re done!
  4. Create a first test program in SE38 ZSCN_XLSX_WRITER_DEMO_BASIC from the source code part ZSCN_XLSX_WRITER_DEMO_BASICbelow.
  5. Create a second test program ZSCN_XLSX_WRITER_DEMO_ADV with some more functionality in SE38 from the source code part ZSCN_XLSX_WRITER_DEMO_ADV below.

Simple transformation ZSCN_XLSX_SHEET

    

 

Simple transformation ZSCN_XLSX_STYLE

    

 

Simple transformation ZSCN_XLSX_CUSTOM_DOC_PROPS

    

 

Class ZSCN_CL_XLSX_WRITER

class ZSCN_CL_XLSX_WRITER definition public create public . public section. *"* public components of class ZSCN_CL_XLSX_WRITER *"* do not include other source files here!!! type-pools ABAP . types: BEGIN OF ys_sheet_view, ysplit TYPE i, topleftcell TYPE string, pane TYPE string, state TYPE string, END OF ys_sheet_view . types: BEGIN OF ys_allignment, text_rotation TYPE i, wrap_text TYPE i, "cht1746 - 16.05.2014 END OF ys_allignment . types: BEGIN OF ys_abap_meta. INCLUDE TYPE dfies. TYPES: z_style TYPE string, z_header TYPE string, z_width TYPE i, z_width_forced type i, END OF ys_abap_meta . types: ys_abap_metas TYPE TABLE OF ys_abap_meta . types: BEGIN OF ys_doc_prop, fmtid TYPE string, pid TYPE string, prop_name TYPE string, prop_value TYPE string, END OF ys_doc_prop . types: ys_doc_props TYPE TABLE OF ys_doc_prop . types: BEGIN OF ys_columninfo, columnid TYPE string, field TYPE string, width TYPE i, properties TYPE if_salv_bs_model_column=>s_type_uie_properties, attribute TYPE if_salv_bs_t_data=>s_type_attribute, END OF ys_columninfo . types: yt_columninfo TYPE HASHED TABLE OF ys_columninfo WITH UNIQUE KEY columnid . types: BEGIN OF ys_style_numfmt, id TYPE i, alias TYPE string, code TYPE string, END OF ys_style_numfmt . types: BEGIN OF ys_style_cellxf, id TYPE i, alias TYPE string, alv_color type string, numfmtid TYPE i, fontid TYPE i, fillid TYPE i, borderid TYPE i, is_string TYPE i, indent TYPE i, xfid TYPE i, wrap TYPE i, key TYPE string, allignment TYPE ys_allignment, END OF ys_style_cellxf . types: BEGIN OF ys_font, id TYPE i, alias TYPE string, name TYPE string, size TYPE i, bold TYPE boolean, italic TYPE boolean, underline TYPE boolean, color_rgb TYPE string, END OF ys_font . types: BEGIN OF ys_fill, id TYPE i, alias TYPE string, patterntype TYPE string, fgcolor_rgb TYPE string, bgcolor_indexed TYPE i, END OF ys_fill . types: BEGIN OF ys_border, id TYPE i, alias TYPE string, left_style TYPE string, left_color_rgb TYPE string, right_style TYPE string, right_color_rgb TYPE string, top_style TYPE string, top_color_rgb TYPE string, bottom_style TYPE string, bottom_color_rgb TYPE string, END OF ys_border . types: BEGIN OF ys_style_struc, t_fonts TYPE HASHED TABLE OF ys_font WITH UNIQUE KEY alias , t_fills TYPE HASHED TABLE OF ys_fill WITH UNIQUE KEY alias , t_borders TYPE HASHED TABLE OF ys_border WITH UNIQUE KEY alias , t_numfmts TYPE HASHED TABLE OF ys_style_numfmt WITH UNIQUE KEY alias , t_cellxfs TYPE HASHED TABLE OF ys_style_cellxf WITH UNIQUE KEY alias alv_color, numfonts_count TYPE i, numfills_count TYPE i, numborders_count TYPE i, numfmts_count TYPE i, cellxfs_count TYPE i, END OF ys_style_struc . types: BEGIN OF ys_sharedstring, value TYPE string, pos TYPE i, END OF ys_sharedstring . types: yth_sharedstring TYPE STANDARD TABLE OF ys_sharedstring WITH NON-UNIQUE KEY value . types: BEGIN OF ys_sharedstring_struc, t_strings TYPE yth_sharedstring, string_count TYPE i, string_ucount TYPE i, END OF ys_sharedstring_struc . types: BEGIN OF ys_cell_struc, position TYPE string, spans TYPE string, value TYPE string, index TYPE i, style TYPE i, sharedstring TYPE string, END OF ys_cell_struc . types: BEGIN OF ys_col_struc, min TYPE i, max TYPE i, width TYPE i, style TYPE i, bestfit TYPE i, END OF ys_col_struc . types: BEGIN OF ys_hyperlink_struc, rel_id TYPE string, cell_id TYPE string, END OF ys_hyperlink_struc . types: BEGIN OF ys_row_struc, spans TYPE string, position TYPE i, outlinelevel TYPE i, hidden TYPE char1, height TYPE i, t_cells TYPE STANDARD TABLE OF ys_cell_struc WITH NON-UNIQUE KEY position, END OF ys_row_struc . types: BEGIN OF ys_merged_cell, ref TYPE string, END OF ys_merged_cell . types: BEGIN OF ys_sheet_struc, dim TYPE string, autofilter type string, s_sheet_view type ys_sheet_view, defaultrowheight TYPE i, merged_cells_count TYPE i, t_merged_cells TYPE STANDARD TABLE OF ys_merged_cell WITH NON-UNIQUE KEY ref, s_header_row TYPE ys_row_struc, t_header_rows TYPE STANDARD TABLE OF ys_row_struc WITH NON-UNIQUE KEY position , t_body_rows TYPE STANDARD TABLE OF ys_row_struc WITH NON-UNIQUE KEY position , t_footer_rows TYPE STANDARD TABLE OF ys_row_struc WITH NON-UNIQUE KEY position , t_cols TYPE STANDARD TABLE OF ys_col_struc WITH NON-UNIQUE KEY min , t_hyperlinks TYPE STANDARD TABLE OF ys_hyperlink_struc WITH NON-UNIQUE KEY cell_id , drawing_id TYPE string, END OF ys_sheet_struc . types: BEGIN OF ys_format_exception, row TYPE i, col TYPE i, style_alias TYPE string, END OF ys_format_exception . types: ys_format_exceptions TYPE HASHED TABLE OF ys_format_exception WITH UNIQUE KEY row col . constants C_TYPE_STRING type C value 'C'. "#EC NOTEXT constants C_DOCPROP_ABAP_STRUC type STRING value 'zicSapStruc'. "#EC NOTEXT constants C_DOCPROP_NUM_HEAD_LINES type STRING value 'zicNumberOfHeaderLines'. "#EC NOTEXT constants LOW_DATE type DATUM value '19000101'. "#EC NOTEXT constants C_DOCPROP_SOURCE_SYSTEM type STRING value 'zicSapSourceSystemID'. "#EC NOTEXT constants C_DOCPROP_SOURCE_CLIENT type STRING value 'zicSapSourceClient'. "#EC NOTEXT constants C_DOCPROP_GENERATED_BY type STRING value 'zicGeneratedBy'. "#EC NOTEXT constants C_DOCPROP_GENERATED_ON type STRING value 'zicGeneratedOn'. "#EC NOTEXT constants C_XLXS_FILE_FILTER_CLIENT type STRING value 'Excel 2010 Files (*.XLSX)|*.XLSX|'. "#EC NOTEXT constants C_XLXS_FILE_FILTER_SERVER type STRING value '*.xlsx'. "#EC NOTEXT constants FILE_EXTENSION type STRING value 'xlsx'. "#EC NOTEXT constants MIME_TYPE type STRING value 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'. "#EC NOTEXT constants C_DOCPROP_NON_PERSISTENT_STRUC type STRING value 'zicSapAbsoluteType'. "#EC NOTEXT constants DEFAULT_TIME_STYLE type STRING value '[$-F400]h:mm:ss AM/PM'. "#EC NOTEXT constants FONT_MANDATORY_DEFAULT_NAME type STRING value 'Calibri'. "#EC NOTEXT constants FONT_MANDATORY_DEFAULT_ALIAS type STRING value 'default'. "#EC NOTEXT constants C_DOCPROP_COLOR_COLUMN type STRING value 'zicColorColumn'. "#EC NOTEXT constants STYLE_HEADER type STRING value 'header'. "#EC NOTEXT constants STYLE_TEXT_DEFAULT type STRING value 'normalText'. "#EC NOTEXT constants STYLE_DATE_DEFAULT type STRING value 'date'. "#EC NOTEXT constants STYLE_TEXT_FORCED type STRING value 'textForced'. "#EC NOTEXT constants STYLE_TIME_DEFAULT type STRING value 'timeNormal'. "#EC NOTEXT constants STYLE_GRAY125 type STRING value 'gray125'. "#EC NOTEXT constants STYLE_DEFAULT type STRING value 'default'. "#EC NOTEXT constants STYLE_NUMBER_DEFAULT type STRING value 'normalNumber'. "#EC NOTEXT constants RGB_BLUE type STRING value 'FF00CCFF'. "#EC NOTEXT constants RGB_GRAY_25 type STRING value 'FFC0C0C0'. "#EC NOTEXT constants RGB_BRIGHT_GREEN type STRING value 'FF00FF00'. "#EC NOTEXT constants INDEXED_COL_SYS_BACKGROUND type I value 64. "#EC NOTEXT constants FILL_PATTERN_SOLID type STRING value 'solid'. "#EC NOTEXT constants RGB_YELLOW type STRING value 'FFFFFF00'. "#EC NOTEXT constants C_DEFAULT_ROW_HEIGHT type I value 13. "#EC NOTEXT constants ALV_RGB_COL_HEADING type STRING value 'FFDDDDDD'. "#EC NOTEXT constants ALV_COL_HEADING type STRING value 'ALV_COL_HEADING'. "#EC NOTEXT constants ALV_RGB_COL_NORMAL type STRING value 'FFCCFFFF'. "#EC NOTEXT constants ALV_RGB_COL_TOTAL type STRING value 'FFFFFFCC'. "#EC NOTEXT constants ALV_RGB_COL_KEY type STRING value 'FF66FFFF'. "#EC NOTEXT constants ALV_RGB_COL_POSITIVE type STRING value 'FF66FF99'. "#EC NOTEXT constants ALV_RGB_COL_NEGATIVE type STRING value 'FFFF7C80'. "#EC NOTEXT constants ALV_RGB_COL_GROUP type STRING value 'FFFFCC66'. "#EC NOTEXT constants ALV_COL_NORMAL type STRING value 'ALV_COL_NORMAL'. "#EC NOTEXT constants ALV_COL_TOTAL type STRING value 'ALV_COL_TOTAL'. "#EC NOTEXT constants ALV_COL_KEY type STRING value 'ALV_COL_KEY'. "#EC NOTEXT constants ALV_COL_POSITIVE type STRING value 'ALV_COL_POSITIVE'. "#EC NOTEXT constants ALV_COL_NEGATIVE type STRING value 'ALV_COL_NEGATIVE'. "#EC NOTEXT constants ALV_COL_GROUP type STRING value 'ALV_COL_GROUP'. "#EC NOTEXT data RGB_BLACK type STRING value '#000000'. "#EC NOTEXT methods CONSTRUCTOR importing !SHEET_CONTENT type ANY TABLE !P_ROW_HEIGHT_HEADER type I default C_DEFAULT_ROW_HEIGHT !P_ROW_HEIGHT_BODY type I default C_DEFAULT_ROW_HEIGHT !P_COLOR_COLUMN type ANY optional !P_AUTOFIT_COLUMN_WIDTH type BOOLEAN default ABAP_TRUE . class-methods BROWSE_SERVER_FS changing !P_FILENAME type ANY . methods SAVE_FILE_TO_SERVER importing !FS_FILENAME type ANY . class-methods BROWSE_CLIENT_FS changing !P_FILENAME type ANY . methods SAVE_FILE_TO_CLIENT importing !FS_FILENAME type ANY !START_EXCEL type BOOLEAN default ABAP_FALSE . methods GET_FILE_AS_EMAIL_ATTACHMENT exporting !XLSX_ATTACHMENT type SOLIX_TAB !ATTACHMENT_LENGHT type SO_OBJ_LEN . methods GET_FILE_AS_XSTRING returning value(XLSX_AS_XSTRING) type XSTRING . methods ADD_CUSTOM_DOC_PROPERTY importing !PROPERTY_NAME type ANY !PROPERTY_VALUE type ANY . methods GET_CELLPOSITION importing !ROW type I !COL type I returning value(RESULT) type STRING . methods INSERT_STYLE importing !ALIAS type STRING !FONT_ALIAS type STRING default 'default' !FILL_ALIAS type STRING default 'none' !BORDER_ALIAS type STRING default 'default' !NUMFORMAT_ALIAS type STRING default 'default' !NUMFORMAT_ID type I optional !TEXT_ROTATION type I optional !WRAP_TEXT type ABAP_BOOL optional !P_GENERATE_COLOR_VARIANTS type ABAP_BOOL default ABAP_TRUE . methods INSERT_FONT importing !ALIAS type STRING !NAME type STRING !SIZE type I !BOLD type BOOLEAN default ABAP_FALSE !ITALIC type BOOLEAN default ABAP_FALSE !UNDERLINE type BOOLEAN default ABAP_FALSE !COLOR_RGB type STRING optional . methods INSERT_FILL importing !ALIAS type STRING !PATTERNTYPE type STRING optional !FGCOLOR_RGB type STRING optional !BGCOLOR_INDEXED type I optional . methods INSERT_BORDER importing !ALIAS type STRING !LEFT_STYLE type STRING optional !LEFT_COLOR_RGB type STRING optional !RIGHT_STYLE type STRING optional !RIGHT_COLOR_RGB type STRING optional !TOP_STYLE type STRING optional !TOP_COLOR_RGB type STRING optional !BOTTOM_STYLE type STRING optional !BOTTOM_COLOR_RGB type STRING optional . methods BUILD importing !ADD_DEFAULT_HEADER type BOOLEAN default ABAP_TRUE !ADD_TECHN_NAMES_HEADER type BOOLEAN default ABAP_FALSE !ADD_AUTOFILTER type BOOLEAN default ABAP_FALSE !P_FREEZE_HEADER type BOOLEAN default ABAP_TRUE . methods FEED_DATA_HEADER importing !HEADERS type YS_ABAP_METAS !STYLE type STRING default STYLE_HEADER . methods FEED_DATA_BODY importing !CONTENT type ANY TABLE . methods ADD_STYLE_EXCEPTION importing !STYLE_ALIAS type STRING !ROW type I optional !COL type I optional . methods MERGE_CELLS importing !ROW_START type I !COL_START type I !ROW_END type I !COL_END type I . methods SET_AUTOFILTER . methods ADD_HEADER_FROM_META importing !P_DFIES_FIELD type ANY preferred parameter P_DFIES_FIELD . methods GET_TEXT_WIDTH importing !P_TEXT type ANY returning value(P_WIDTH) type I . methods SET_COLUMN_WIDTH importing !P_COLUMN_NAME type ANY !P_WIDTH type I . methods SET_AUTOFIT_MULTI_FACTOR importing !P_AUTOFIT_MULTI_FACTOR type P . methods SHOW_SHEET importing !P_HEADER type ANY optional . protected section. *"* protected components of class ZSCN_CL_XLSX_WRITER *"* do not include other source files here!!! constants DEFAULT_AUTOFIT_MULTI_FACTOR type F value '1.14'. "#EC NOTEXT data ABAP_SHEET_CONTENT type ref to DATA . data ABAP_META type YS_ABAP_METAS . data SHEET_STRUCT type YS_SHEET_STRUC . data SHAREDSTRING_STRUCT type YS_SHAREDSTRING_STRUC . data STYLE_STRUCT type YS_STYLE_STRUC . constants C_DECIMALS_FORMAT type CHAR26 value '0.000000000'. "#EC NOTEXT data XLSX_DOCUMENT type ref to CL_XLSX_DOCUMENT . data XLSX_FILE_RAW type XSTRING . data CUSTOM_DOC_PROPS type YS_DOC_PROPS . data NUM_OF_HEADER_LINES type I . data COLOR_COLUMN type LVC_FNAME . data POSITION_OF_COLOR_COL type I . class CL_ABAP_STRUCTDESCR definition load . data COMPONENTS type CL_ABAP_STRUCTDESCR=>COMPONENT_TABLE . methods SET_MAX_WIDTH importing !P_CONTENT type ANY changing !P_MAX_WIDTH type I . methods SET_COLOR_COLUMN importing !P_COLOR_COLUMN type ANY . methods SET_POSITION_OF_COLOR_COL . private section. *"* private components of class ZSCN_CL_XLSX_WRITER *"* do not include other source files here!!! data ROW_HEIGHT_HEADER type I . data ROW_HEIGHT_BODY type I . data ROW_INDEX type I . data SPANS type STRING . data STYLE_EXCEPTIONS type YS_FORMAT_EXCEPTIONS . data AUTOFIT_COLUMN_WIDTH type BOOLEAN . data AUTOFIT_MULTI_FACTOR type F value DEFAULT_AUTOFIT_MULTI_FACTOR. "#EC NOTEXT . methods BUILD_ABAP_META . methods SET_DEFAULT_COLUMNS . methods SET_CONTENT importing !P_SHEET_CONTENT type ANY TABLE . methods INSERT_MANDATORY_STYLES . methods INSERT_DEFAULT_STYLES . methods INSERT_MANDATORY_FONTS . methods INSERT_MANDATORY_FILLS . methods GET_FILL_ID_FROM_ALIAS importing !ALIAS type STRING returning value(ID) type I . methods INSERT_MANDATORY_BORDERS . methods GET_BORDER_ID_FROM_ALIAS importing !ALIAS type STRING returning value(ID) type I . methods INSERT_NUMBER_FORMAT importing !ALIAS type STRING !CODE type STRING . methods INSERT_MANDATORY_NUMBER_FORMAT . methods GET_NUMFMT_ID_FROM_ALIAS importing !ALIAS type STRING returning value(ID) type I . methods CREATE_CELLS importing !ROW_INDEX type I . methods ADD_SHAREDSTRING importing !I_SHAREDSTRING type STRING exporting !E_INDEX type I . methods TRANSFORM_TO_XSLX . methods GET_STYLE importing !P_ALIAS type STRING !P_ALV_COLOR type STRING !P_ROW type I optional !P_COL type I optional returning value(P_STYLE_ID) type I . methods SET_SPANS . methods GET_FONT_ID_FROM_ALIAS importing !ALIAS type STRING returning value(ID) type I . methods INSERT_DEFAULT_PROPERTIES . methods FREEZE_HEADER . methods INSERT_ALV_COLOR_FILLS . methods GET_VARIANT_NAME_FROM_COLOR importing !P_COLOR type LVC_COL preferred parameter P_COLOR returning value(P_ALV_COLOR_NAME) type STRING . ENDCLASS. CLASS ZSCN_CL_XLSX_WRITER IMPLEMENTATION. * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->ADD_CUSTOM_DOC_PROPERTY * +-------------------------------------------------------------------------------------------------+ * | [--->] PROPERTY_NAME TYPE ANY * | [--->] PROPERTY_VALUE TYPE ANY * +-------------------------------------------------------------------------------------- METHOD ADD_CUSTOM_DOC_PROPERTY. DATA: ls_doc_prop LIKE LINE OF custom_doc_props, lv_pid TYPE sytabix. ls_doc_prop-fmtid = '{d5cdd505-2e9c-101b-9397-08002b2cf9ae}'. DESCRIBE TABLE custom_doc_props LINES lv_pid . ADD 2 TO lv_pid. ls_doc_prop-pid = lv_pid. CONDENSE ls_doc_prop-pid NO-GAPS. ls_doc_prop-prop_name = property_name. ls_doc_prop-prop_value = property_value. APPEND ls_doc_prop TO custom_doc_props. ENDMETHOD. "ADD_CUSTOM_DOC_PROPERTY * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->ADD_HEADER_FROM_META * +-------------------------------------------------------------------------------------------------+ * | [--->] P_DFIES_FIELD TYPE ANY * +-------------------------------------------------------------------------------------- METHOD add_header_from_meta. FIELD-SYMBOLS:  LIKE LINE OF abap_meta,  TYPE any. DATA: abap_metas_temp LIKE abap_meta. LOOP AT abap_meta ASSIGNING . ASSIGN COMPONENT p_dfies_field OF STRUCTURE  TO . IF sy-subrc EQ 0. IF NOT  IS INITIAL. -z_header = . * grow column width if needed IF ( strlen( -z_header ) + 2 ) GE -z_width. -z_width = strlen( -z_header ) + 2. ENDIF. ENDIF. ENDIF. APPEND  TO abap_metas_temp. ENDLOOP. feed_data_header( headers = abap_metas_temp ). ENDMETHOD. * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->ADD_SHAREDSTRING * +-------------------------------------------------------------------------------------------------+ * | [--->] I_SHAREDSTRING TYPE STRING * | [ METHOD ADD_SHAREDSTRING. DATA: l_index TYPE i, ls_sharedstring TYPE ys_sharedstring. * Check: is the string already in our shared string table? * -> If yes, give me the position/index * -> In no, insert the string and give me then the position/index READ TABLE sharedstring_struct-t_strings WITH KEY value = i_sharedstring INTO ls_sharedstring. IF sy-subrc EQ 0. * nothing to do here ELSE. ADD 1 TO sharedstring_struct-string_ucount. CLEAR ls_sharedstring. ls_sharedstring-value = i_sharedstring. ls_sharedstring-pos = sharedstring_struct-string_ucount. APPEND ls_sharedstring TO sharedstring_struct-t_strings. ENDIF. ADD 1 TO sharedstring_struct-string_count. e_index = ls_sharedstring-pos - 1. ENDMETHOD. "ADD_SHAREDSTRING * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->ADD_STYLE_EXCEPTION * +-------------------------------------------------------------------------------------------------+ * | [--->] STYLE_ALIAS TYPE STRING * | [--->] ROW TYPE I(optional) * | [--->] COL TYPE I(optional) * +-------------------------------------------------------------------------------------- METHOD ADD_STYLE_EXCEPTION. DATA: ls_style_exception LIKE LINE OF style_exceptions. IF row IS INITIAL AND col IS INITIAL. EXIT. ENDIF. ls_style_exception-row = row. ls_style_exception-col = col. ls_style_exception-style_alias = style_alias. INSERT ls_style_exception INTO TABLE style_exceptions. ENDMETHOD. "ADD_STYLE_EXCEPTION * ---------------------------------------------------------------------------------------+ * | Static Public Method ZSCN_CL_XLSX_WRITER=>BROWSE_CLIENT_FS * +-------------------------------------------------------------------------------------------------+ * | [] P_FILENAME TYPE ANY * +-------------------------------------------------------------------------------------- METHOD browse_client_fs. zz9xa_cl_screen_generics=>client_file_save_dialogue( EXPORTING p_filter = c_xlxs_file_filter_client p_default_extention = file_extension CHANGING p_name = p_filename ). ENDMETHOD. "BROWSE_CLIENT_FS * ---------------------------------------------------------------------------------------+ * | Static Public Method ZSCN_CL_XLSX_WRITER=>BROWSE_SERVER_FS * +-------------------------------------------------------------------------------------------------+ * | [] P_FILENAME TYPE ANY * +-------------------------------------------------------------------------------------- METHOD browse_server_fs. zz9xa_cl_screen_generics=>server_file_save_dialogue( EXPORTING p_filter = c_xlxs_file_filter_server CHANGING p_name = p_filename ). ENDMETHOD. "BROWSE_SERVER_FS * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->BUILD * +-------------------------------------------------------------------------------------------------+ * | [--->] ADD_DEFAULT_HEADER TYPE BOOLEAN (default =ABAP_TRUE) * | [--->] ADD_TECHN_NAMES_HEADER TYPE BOOLEAN (default =ABAP_FALSE) * | [--->] ADD_AUTOFILTER TYPE BOOLEAN (default =ABAP_FALSE) * | [--->] P_FREEZE_HEADER TYPE BOOLEAN (default =ABAP_TRUE) * +-------------------------------------------------------------------------------------- METHOD build. FIELD-SYMBOLS:  TYPE ANY TABLE,  TYPE any,  TYPE any. DATA: abap_meta_help LIKE LINE OF abap_meta, abap_metas_help LIKE abap_meta. UNASSIGN . ASSIGN abap_sheet_content->* TO . * Header IF add_default_header EQ abap_true. feed_data_header( headers = abap_meta ). ENDIF. IF add_techn_names_header EQ abap_true. add_header_from_meta('FIELDNAME'). ENDIF. * Body feed_data_body( content =  ). *Columns set_default_columns( ). set_spans( ). IF add_autofilter EQ abap_true. set_autofilter( ). ENDIF. add_custom_doc_property( property_name = c_docprop_num_head_lines property_value = num_of_header_lines ). IF NOT color_column IS INITIAL. add_custom_doc_property( property_name = c_docprop_color_column property_value = color_column ). ENDIF. * Freeze the header rows IF p_freeze_header EQ abap_true. freeze_header( ). ENDIF. * create the xml streams transform_to_xslx( ). ENDMETHOD. "build * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->BUILD_ABAP_META * +-------------------------------------------------------------------------------------------------+ * +-------------------------------------------------------------------------------------- METHOD build_abap_meta. DATA: lr_sheet_line TYPE REF TO data, lr_type_descr TYPE REF TO cl_abap_typedescr, lr_struc_descr TYPE REF TO cl_abap_structdescr, ls_abap_meta LIKE LINE OF abap_meta, ls_abap_compdescr TYPE abap_compdescr, lr_table_descr TYPE REF TO cl_abap_tabledescr, li_ddfields TYPE ddfields, ls_ddfield TYPE LINE OF ddfields, lv_tabix TYPE sytabix, lv_num_format_part TYPE i, lv_number_format_alias TYPE string, lv_number_format TYPE string. lr_table_descr ?= cl_abap_tabledescr=>describe_by_data_ref( abap_sheet_content ). lr_struc_descr ?= lr_table_descr->get_table_line_type( ). components = lr_struc_descr->get_components( ). IF NOT abap_meta IS INITIAL. CLEAR abap_meta. ENDIF. CALL METHOD lr_struc_descr->get_ddic_field_list EXPORTING p_langu = sy-langu p_including_substructres = abap_true RECEIVING p_field_list = li_ddfields EXCEPTIONS not_found = 1 no_ddic_type = 2 OTHERS = 3. IF sy-subrc EQ 0. LOOP AT li_ddfields INTO ls_ddfield. CLEAR ls_abap_meta. MOVE-CORRESPONDING ls_ddfield TO ls_abap_meta. APPEND ls_abap_meta TO abap_meta. ENDLOOP. ELSE. * no DDIC type - so retrieve the technical names LOOP AT lr_struc_descr->components INTO ls_abap_compdescr. CLEAR ls_abap_meta. ls_abap_meta-fieldname = ls_abap_compdescr-name. ls_abap_meta-inttype = ls_abap_compdescr-type_kind. ls_abap_meta-leng = ls_abap_meta-outputlen = ls_abap_compdescr-length. ls_abap_meta-decimals = ls_abap_compdescr-decimals. APPEND ls_abap_meta TO abap_meta. ENDLOOP. ENDIF. * set the default styles and default texts DELETE abap_meta WHERE fieldname EQ color_column. LOOP AT abap_meta INTO ls_abap_meta. lv_tabix = sy-tabix. CASE ls_abap_meta-inttype. WHEN cl_abap_typedescr=>typekind_packed OR cl_abap_typedescr=>typekind_int. * create a number format with the matching decimals CLEAR: lv_number_format_alias, lv_number_format. lv_number_format_alias = ls_abap_meta-fieldname. IF ls_abap_meta-decimals GT 0. lv_num_format_part = ls_abap_meta-decimals + 2. lv_number_format = c_decimals_format(lv_num_format_part). ENDIF. CONCATENATE '#,##' lv_number_format INTO lv_number_format. me->insert_number_format( alias = lv_number_format_alias code = lv_number_format ). me->insert_style( alias = lv_number_format_alias numformat_alias = lv_number_format_alias ). ls_abap_meta-z_style = lv_number_format_alias. WHEN cl_abap_typedescr=>typekind_date. ls_abap_meta-z_style = style_date_default. WHEN cl_abap_typedescr=>typekind_time. ls_abap_meta-z_style = style_time_default. WHEN cl_abap_typedescr=>typekind_num. * num - force as being text ls_abap_meta-z_style = style_text_forced. WHEN cl_abap_typedescr=>typekind_char. ls_abap_meta-z_style = style_text_forced. WHEN OTHERS. ls_abap_meta-z_style = style_text_default. ENDCASE. ls_abap_meta-z_width = ls_abap_meta-outputlen + 2. * First try with DDIC fieldname IF NOT ls_abap_meta-fieldtext IS INITIAL AND strlen( ls_abap_meta-fieldtext ) LE ls_abap_meta-z_width. ls_abap_meta-z_header = ls_abap_meta-fieldtext. ELSEIF NOT ls_abap_meta-scrtext_l IS INITIAL AND strlen( ls_abap_meta-scrtext_l ) LE ls_abap_meta-z_width. ls_abap_meta-z_header = ls_abap_meta-scrtext_l. ELSEIF NOT ls_abap_meta-scrtext_m IS INITIAL AND strlen( ls_abap_meta-scrtext_m ) LE ls_abap_meta-z_width. ls_abap_meta-z_header = ls_abap_meta-scrtext_m. ELSEIF NOT ls_abap_meta-scrtext_s IS INITIAL AND strlen( ls_abap_meta-scrtext_s ) LE ls_abap_meta-z_width. ls_abap_meta-z_header = ls_abap_meta-scrtext_s. ELSEIF NOT ls_abap_meta-scrtext_s IS INITIAL. ls_abap_meta-z_header = ls_abap_meta-scrtext_s. ELSE. ls_abap_meta-z_header = ls_abap_meta-fieldname. ENDIF. IF ( strlen( ls_abap_meta-z_header ) + 2 ) GE ls_abap_meta-z_width. ls_abap_meta-z_width = strlen( ls_abap_meta-z_header ) + 2. ENDIF. IF autofit_column_width EQ abap_true. CLEAR ls_abap_meta-z_width. ENDIF. MODIFY abap_meta FROM ls_abap_meta INDEX lv_tabix. ENDLOOP. IF lr_struc_descr->is_ddic_type( ) EQ abap_true. add_custom_doc_property( property_name = c_docprop_abap_struc property_value = lr_struc_descr->get_relative_name( ) ). ELSE. add_custom_doc_property( property_name = c_docprop_abap_struc property_value = '' ). ENDIF. add_custom_doc_property( property_name = c_docprop_non_persistent_struc property_value = lr_struc_descr->absolute_name ). ENDMETHOD. "build_abap_meta * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->CONSTRUCTOR * +-------------------------------------------------------------------------------------------------+ * | [--->] SHEET_CONTENT TYPE ANY TABLE * | [--->] P_ROW_HEIGHT_HEADER TYPE I (default =C_DEFAULT_ROW_HEIGHT) * | [--->] P_ROW_HEIGHT_BODY TYPE I (default =C_DEFAULT_ROW_HEIGHT) * | [--->] P_COLOR_COLUMN TYPE ANY(optional) * | [--->] P_AUTOFIT_COLUMN_WIDTH TYPE BOOLEAN (default =ABAP_TRUE) * +-------------------------------------------------------------------------------------- METHOD constructor. xlsx_document = cl_xlsx_document=>create_document( ). autofit_column_width = p_autofit_column_width. row_height_header = p_row_height_header. row_height_body = p_row_height_body. insert_default_properties( ). set_content( p_sheet_content = sheet_content ). set_color_column( p_color_column = p_color_column ). insert_mandatory_styles( ). build_abap_meta( ). insert_default_styles( ). ENDMETHOD. "constructor * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->CREATE_CELLS * +-------------------------------------------------------------------------------------------------+ * | [--->] ROW_INDEX TYPE I * +-------------------------------------------------------------------------------------- METHOD CREATE_CELLS. DATA: ls_cell TYPE ys_cell_struc, l_col_index TYPE i. CALL METHOD me->get_cellposition EXPORTING row = row_index col = l_col_index RECEIVING result = ls_cell-position. ENDMETHOD. "CREATE_CELLS * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->FEED_DATA_BODY * +-------------------------------------------------------------------------------------------------+ * | [--->] CONTENT TYPE ANY TABLE * +-------------------------------------------------------------------------------------- METHOD feed_data_body. DATA: ls_row TYPE ys_row_struc, lv_current_col TYPE sytabix, * ls_abap_meta LIKE LINE OF abap_meta, ls_cell TYPE ys_cell_struc, lv_value TYPE string, lv_date_xlsx_format TYPE string, lv_add_cell TYPE boolean, lv_alv_color TYPE string. FIELD-SYMBOLS:  TYPE any,  TYPE any,  TYPE lvc_t_scol,  TYPE lvc_s_scol,  LIKE LINE OF abap_meta. LOOP AT content ASSIGNING . ADD 1 TO row_index. CLEAR ls_row. * color manamgent IF NOT color_column IS INITIAL. ASSIGN COMPONENT color_column OF STRUCTURE  TO . ENDIF. lv_add_cell = abap_false. ls_row-spans = spans. IF row_height_body NE c_default_row_height. ls_row-height = row_height_body. ENDIF. ls_row-position = row_index. * now go column by column LOOP AT abap_meta ASSIGNING . lv_current_col = sy-tabix. ASSIGN COMPONENT -fieldname OF STRUCTURE  TO . CLEAR: lv_value, ls_cell, lv_alv_color. IF  IS ASSIGNED. READ TABLE  ASSIGNING  WITH KEY fname = -fieldname. IF sy-subrc EQ 0. lv_alv_color = get_variant_name_from_color( -color-col ). ELSE. READ TABLE  ASSIGNING  WITH KEY fname = ''. IF sy-subrc EQ 0. lv_alv_color = get_variant_name_from_color( -color-col ). ENDIF. ENDIF. ENDIF. ls_cell-style = get_style( p_alias = -z_style p_alv_color = lv_alv_color p_row = row_index p_col = lv_current_col ). IF ls_cell-style NE 0. lv_add_cell = abap_true. ENDIF. CALL METHOD me->get_cellposition EXPORTING row = row_index col = lv_current_col RECEIVING result = ls_cell-position. IF -inttype EQ cl_abap_typedescr=>typekind_packed OR -inttype EQ cl_abap_typedescr=>typekind_int. IF NOT  IS INITIAL. lv_add_cell = abap_true. IF  GE 0. ls_cell-value = . ELSE. "negative number, minus needs to be in the front or excel ls_cell-value = abs(  ). CONCATENATE '-' ls_cell-value INTO ls_cell-value. ENDIF. ENDIF. ELSEIF -inttype EQ cl_abap_typedescr=>typekind_date. IF NOT  IS INITIAL. lv_add_cell = abap_true. CALL METHOD cl_alv_xslt_transform=>get_days_since_1900 EXPORTING i_date =  RECEIVING e_num = ls_cell-value. ENDIF. ELSEIF -inttype EQ cl_abap_typedescr=>typekind_time. IF NOT  IS INITIAL. lv_add_cell = abap_true. CALL METHOD cl_alv_xslt_transform=>get_percent_of_act_day EXPORTING i_time =  RECEIVING e_num = ls_cell-value. ENDIF. ELSE. * All the rest is string IF NOT  IS INITIAL. IF autofit_column_width EQ abap_true. set_max_width( EXPORTING p_content =  CHANGING p_max_width = -z_width ). ENDIF. lv_add_cell = abap_true. ls_cell-sharedstring = 's'. lv_value = . CALL METHOD me->add_sharedstring EXPORTING i_sharedstring = lv_value IMPORTING e_index = ls_cell-index. ENDIF. ENDIF. IF lv_add_cell = abap_true. INSERT ls_cell INTO TABLE ls_row-t_cells. ENDIF. ENDLOOP. INSERT ls_row INTO TABLE sheet_struct-t_body_rows. ENDLOOP. ENDMETHOD. "feed_data_body * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->FEED_DATA_HEADER * +-------------------------------------------------------------------------------------------------+ * | [--->] HEADERS TYPE YS_ABAP_METAS * | [--->] STYLE TYPE STRING (default =STYLE_HEADER) * +-------------------------------------------------------------------------------------- METHOD feed_data_header. FIELD-SYMBOLS:  LIKE LINE OF abap_meta. DATA: ls_row TYPE ys_row_struc, ls_header LIKE LINE OF headers, ls_cell TYPE ys_cell_struc, lv_current_col TYPE sy-tabix, lv_add_cell TYPE boolean. ADD 1 TO row_index. ADD 1 TO num_of_header_lines. LOOP AT headers INTO ls_header. lv_current_col = sy-tabix. CLEAR: ls_cell. * ls_row-spans = spans. ls_row-position = row_index. IF row_height_header NE c_default_row_height. ls_row-height = row_height_body. ENDIF. * now go column by column CLEAR: ls_cell. CALL METHOD me->get_cellposition EXPORTING row = row_index col = lv_current_col RECEIVING result = ls_cell-position. ls_cell-style = me->get_style( p_alias = style p_alv_color = '' p_row = row_index p_col = lv_current_col ). IF ls_cell-style NE 0. lv_add_cell = abap_true. ENDIF. IF NOT ls_header-z_header IS INITIAL. * headers are always string lv_add_cell = abap_true. ls_cell-sharedstring = 's'. CALL METHOD me->add_sharedstring EXPORTING i_sharedstring = ls_header-z_header IMPORTING e_index = ls_cell-index. IF autofit_column_width EQ abap_true. READ TABLE abap_meta ASSIGNING  WITH KEY tabname = ls_header-tabname fieldname = ls_header-fieldname. IF sy-subrc EQ 0. set_max_width( EXPORTING p_content = ls_header-z_header CHANGING p_max_width = -z_width ). ENDIF. ENDIF. ENDIF. IF lv_add_cell EQ abap_true. INSERT ls_cell INTO TABLE ls_row-t_cells. ENDIF. ENDLOOP. INSERT ls_row INTO TABLE sheet_struct-t_header_rows. ENDMETHOD. "feed_data_header * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->FREEZE_HEADER * +-------------------------------------------------------------------------------------------------+ * +-------------------------------------------------------------------------------------- METHOD freeze_header. DATA: reference_row TYPE i. sheet_struct-s_sheet_view-ysplit = num_of_header_lines. reference_row = num_of_header_lines + 1. sheet_struct-s_sheet_view-topleftcell = |{ reference_row }|. CONCATENATE 'A' sheet_struct-s_sheet_view-topleftcell INTO sheet_struct-s_sheet_view-topleftcell. sheet_struct-s_sheet_view-pane = 'bottomLeft'. sheet_struct-s_sheet_view-state = 'frozen'. ENDMETHOD. * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->GET_BORDER_ID_FROM_ALIAS * +-------------------------------------------------------------------------------------------------+ * | [--->] ALIAS TYPE STRING * | [ METHOD GET_BORDER_ID_FROM_ALIAS. DATA: ls_border LIKE LINE OF style_struct-t_borders. READ TABLE style_struct-t_borders INTO ls_border WITH KEY alias = alias. IF sy-subrc EQ 0. id = ls_border-id. ELSE. CLEAR id. "set to default ENDIF. ENDMETHOD. "GET_BORDER_ID_FROM_ALIAS * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->GET_CELLPOSITION * +-------------------------------------------------------------------------------------------------+ * | [--->] ROW TYPE I * | [--->] COL TYPE I * | [ METHOD GET_CELLPOSITION. DATA: l_part1 TYPE string, l_part2 TYPE string, l_part3 TYPE string, l_part4 TYPE string, l_mod TYPE i, l_div TYPE i, l_div_aux TYPE i. l_mod = ( col - 1 ) MOD 26. l_div = ( col - 1 ) DIV 26. IF l_div GT 26. l_div_aux = l_div DIV 26. ENDIF. l_part1 = sy-abcde+l_mod(1). l_part3 = |{ row }|. IF l_div > 0. l_div = l_div - 1. IF l_div_aux IS NOT INITIAL. l_div_aux = l_div_aux - 1. l_div = l_div MOD 26. l_part4 = sy-abcde+l_div_aux(1). l_part2 = sy-abcde+l_div(1). CONCATENATE l_part4 l_part2 l_part1 l_part3 INTO result. ELSE. l_part2 = sy-abcde+l_div(1). CONCATENATE l_part2 l_part1 l_part3 INTO result. ENDIF. ELSE. CONCATENATE l_part1 l_part3 INTO result. ENDIF. ENDMETHOD. "get_cellposition * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->GET_FILE_AS_EMAIL_ATTACHMENT * +-------------------------------------------------------------------------------------------------+ * | [ METHOD GET_FILE_AS_EMAIL_ATTACHMENT. attachment_lenght = xstrlen( me->xlsx_file_raw ). CALL METHOD cl_bcs_convert=>xstring_to_solix EXPORTING iv_xstring = me->xlsx_file_raw RECEIVING et_solix = xlsx_attachment. ENDMETHOD. "GET_FILE_AS_EMAIL_ATTACHMENT * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->GET_FILE_AS_XSTRING * +-------------------------------------------------------------------------------------------------+ * | [ METHOD GET_FILE_AS_XSTRING. xlsx_as_xstring = me->xlsx_file_raw. ENDMETHOD. "GET_FILE_AS_XSTRING * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->GET_FILL_ID_FROM_ALIAS * +-------------------------------------------------------------------------------------------------+ * | [--->] ALIAS TYPE STRING * | [ METHOD GET_FILL_ID_FROM_ALIAS. DATA: ls_fill LIKE LINE OF style_struct-t_fills. READ TABLE style_struct-t_fills INTO ls_fill WITH KEY alias = alias. IF sy-subrc EQ 0. id = ls_fill-id. ELSE. CLEAR id. "set to default ENDIF. ENDMETHOD. "GET_FILL_ID_FROM_ALIAS * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->GET_FONT_ID_FROM_ALIAS * +-------------------------------------------------------------------------------------------------+ * | [--->] ALIAS TYPE STRING * | [ METHOD GET_FONT_ID_FROM_ALIAS. DATA: ls_font LIKE LINE OF style_struct-t_fonts. READ TABLE style_struct-t_fonts INTO ls_font WITH KEY alias = alias. IF sy-subrc EQ 0. id = ls_font-id. ELSE. CLEAR id. "set to default ENDIF. ENDMETHOD. "GET_FONT_ID_FROM_ALIS * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->GET_NUMFMT_ID_FROM_ALIAS * +-------------------------------------------------------------------------------------------------+ * | [--->] ALIAS TYPE STRING * | [ METHOD GET_NUMFMT_ID_FROM_ALIAS. DATA: ls_numfmt LIKE LINE OF style_struct-t_numfmts. READ TABLE style_struct-t_numfmts INTO ls_numfmt WITH KEY alias = alias. IF sy-subrc EQ 0. id = ls_numfmt-id. ELSE. CLEAR id. "set to default ENDIF. ENDMETHOD. "GET_NUMFMT_ID_FROM_ALIAS * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->GET_STYLE * +-------------------------------------------------------------------------------------------------+ * | [--->] P_ALIAS TYPE STRING * | [--->] P_ALV_COLOR TYPE STRING * | [--->] P_ROW TYPE I(optional) * | [--->] P_COL TYPE I(optional) * | [ METHOD get_style. DATA: ls_cellxf TYPE ys_style_cellxf, lv_style_alias TYPE string, ls_style_exception LIKE LINE OF style_exceptions. * set the default lv_style_alias = p_alias. * check if we do not have an exception READ TABLE style_exceptions INTO ls_style_exception WITH KEY row = p_row col = p_col. IF sy-subrc EQ 0. lv_style_alias = ls_style_exception-style_alias. ELSE. * is there an override for the whole row READ TABLE style_exceptions INTO ls_style_exception WITH KEY row = p_row col = 0. IF sy-subrc EQ 0 . lv_style_alias = ls_style_exception-style_alias. ELSE. READ TABLE style_exceptions INTO ls_style_exception WITH KEY row = 0 col = p_col. IF sy-subrc EQ 0. lv_style_alias = ls_style_exception-style_alias. ENDIF. ENDIF. ENDIF. READ TABLE style_struct-t_cellxfs WITH TABLE KEY alias = lv_style_alias alv_color = p_alv_color TRANSPORTING id INTO ls_cellxf. IF sy-subrc EQ 0. p_style_id = ls_cellxf-id. ELSE. p_style_id = 0. ENDIF. ENDMETHOD. "get_style * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->GET_TEXT_WIDTH * +-------------------------------------------------------------------------------------------------+ * | [--->] P_TEXT TYPE ANY * | [ METHOD get_text_width. DATA: content TYPE string. content = p_text. p_width = round( val = ( strlen( content ) * autofit_multi_factor ) dec = 0 mode = cl_abap_math=>round_up ). ENDMETHOD. * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->GET_VARIANT_NAME_FROM_COLOR * +-------------------------------------------------------------------------------------------------+ * | [--->] P_COLOR TYPE LVC_COL * | [ METHOD get_variant_name_from_color. CASE p_color. WHEN col_heading. p_alv_color_name = alv_col_heading. WHEN col_normal. p_alv_color_name = alv_col_normal. WHEN col_total. p_alv_color_name = alv_col_total. WHEN col_key. p_alv_color_name = alv_col_key. WHEN col_positive. p_alv_color_name = alv_col_positive. WHEN col_negative. p_alv_color_name = alv_col_negative. WHEN col_group. p_alv_color_name = alv_col_group. WHEN others. p_alv_color_name = ''. ENDCASE. ENDMETHOD. * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_ALV_COLOR_FILLS * +-------------------------------------------------------------------------------------------------+ * +-------------------------------------------------------------------------------------- METHOD insert_alv_color_fills. insert_fill( alias = alv_col_heading patterntype = fill_pattern_solid fgcolor_rgb = alv_rgb_col_heading bgcolor_indexed = indexed_col_sys_background ). insert_fill( alias = alv_col_normal patterntype = fill_pattern_solid fgcolor_rgb = alv_rgb_col_normal bgcolor_indexed = indexed_col_sys_background ). insert_fill( alias = alv_col_total patterntype = fill_pattern_solid fgcolor_rgb = alv_rgb_col_total bgcolor_indexed = indexed_col_sys_background ). insert_fill( alias = alv_col_key patterntype = fill_pattern_solid fgcolor_rgb = alv_rgb_col_key bgcolor_indexed = indexed_col_sys_background ). insert_fill( alias = alv_col_positive patterntype = fill_pattern_solid fgcolor_rgb = alv_rgb_col_positive bgcolor_indexed = indexed_col_sys_background ). insert_fill( alias = alv_col_negative patterntype = fill_pattern_solid fgcolor_rgb = alv_rgb_col_negative bgcolor_indexed = indexed_col_sys_background ). insert_fill( alias = alv_col_group patterntype = fill_pattern_solid fgcolor_rgb = alv_rgb_col_group bgcolor_indexed = indexed_col_sys_background ). ENDMETHOD. "INSERT_MANDATORY_FILLS * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->INSERT_BORDER * +-------------------------------------------------------------------------------------------------+ * | [--->] ALIAS TYPE STRING * | [--->] LEFT_STYLE TYPE STRING(optional) * | [--->] LEFT_COLOR_RGB TYPE STRING(optional) * | [--->] RIGHT_STYLE TYPE STRING(optional) * | [--->] RIGHT_COLOR_RGB TYPE STRING(optional) * | [--->] TOP_STYLE TYPE STRING(optional) * | [--->] TOP_COLOR_RGB TYPE STRING(optional) * | [--->] BOTTOM_STYLE TYPE STRING(optional) * | [--->] BOTTOM_COLOR_RGB TYPE STRING(optional) * +-------------------------------------------------------------------------------------- METHOD INSERT_BORDER. DATA: ls_border LIKE LINE OF style_struct-t_borders. READ TABLE style_struct-t_borders INTO ls_border WITH KEY alias = alias. IF sy-subrc EQ 0. * delete the existing one DELETE TABLE style_struct-t_borders WITH TABLE KEY alias = alias. ELSE. * new font ENDIF. CLEAR ls_border. DESCRIBE TABLE style_struct-t_borders LINES style_struct-numborders_count. ls_border-id = style_struct-numborders_count. ls_border-alias = alias. ls_border-left_style = left_style. ls_border-left_color_rgb = left_color_rgb. ls_border-right_style = right_style. ls_border-right_color_rgb = right_color_rgb. ls_border-top_style = top_style. ls_border-top_color_rgb = top_color_rgb. ls_border-bottom_style = bottom_style. ls_border-bottom_color_rgb = bottom_color_rgb. INSERT ls_border INTO TABLE style_struct-t_borders. SORT style_struct-t_borders BY id. ADD 1 TO style_struct-numborders_count. ENDMETHOD. "insert_border * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_DEFAULT_PROPERTIES * +-------------------------------------------------------------------------------------------------+ * +-------------------------------------------------------------------------------------- METHOD INSERT_DEFAULT_PROPERTIES. add_custom_doc_property( property_name = c_docprop_source_system property_value = sy-sysid ). add_custom_doc_property( property_name = c_docprop_source_client property_value = sy-mandt ). add_custom_doc_property( property_name = c_docprop_generated_by property_value = sy-uname ). add_custom_doc_property( property_name = c_docprop_generated_on property_value = sy-datum ). ENDMETHOD. "INSERT_DEFAULT_PROPERTIES * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_DEFAULT_STYLES * +-------------------------------------------------------------------------------------------------+ * +-------------------------------------------------------------------------------------- METHOD insert_default_styles. insert_fill( alias = style_header patterntype = fill_pattern_solid fgcolor_rgb = rgb_gray_25 bgcolor_indexed = indexed_col_sys_background ). insert_style( alias = style_header fill_alias = style_header ). insert_style( alias = style_date_default numformat_id = 14 ). insert_style( alias = style_text_forced numformat_id = 49 ). ENDMETHOD. "insert_default_styles * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->INSERT_FILL * +-------------------------------------------------------------------------------------------------+ * | [--->] ALIAS TYPE STRING * | [--->] PATTERNTYPE TYPE STRING(optional) * | [--->] FGCOLOR_RGB TYPE STRING(optional) * | [--->] BGCOLOR_INDEXED TYPE I(optional) * +-------------------------------------------------------------------------------------- METHOD INSERT_FILL. DATA: ls_fill LIKE LINE OF style_struct-t_fills. READ TABLE style_struct-t_fills INTO ls_fill WITH KEY alias = alias. IF sy-subrc EQ 0. DELETE TABLE style_struct-t_fills WITH TABLE KEY alias = alias. ELSE. * new font ENDIF. CLEAR ls_fill. DESCRIBE TABLE style_struct-t_fills LINES style_struct-numfills_count. ls_fill-id = style_struct-numfills_count. ls_fill-alias = alias. ls_fill-patterntype = patterntype. ls_fill-fgcolor_rgb = fgcolor_rgb. ls_fill-bgcolor_indexed = bgcolor_indexed. INSERT ls_fill INTO TABLE style_struct-t_fills. SORT style_struct-t_fills BY id. ADD 1 TO style_struct-numfills_count. ENDMETHOD. "INSERT_FILL * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->INSERT_FONT * +-------------------------------------------------------------------------------------------------+ * | [--->] ALIAS TYPE STRING * | [--->] NAME TYPE STRING * | [--->] SIZE TYPE I * | [--->] BOLD TYPE BOOLEAN (default =ABAP_FALSE) * | [--->] ITALIC TYPE BOOLEAN (default =ABAP_FALSE) * | [--->] UNDERLINE TYPE BOOLEAN (default =ABAP_FALSE) * | [--->] COLOR_RGB TYPE STRING(optional) * +-------------------------------------------------------------------------------------- METHOD INSERT_FONT. DATA: ls_font LIKE LINE OF style_struct-t_fonts. READ TABLE style_struct-t_fonts INTO ls_font WITH KEY alias = alias. IF sy-subrc EQ 0. DELETE TABLE style_struct-t_fonts WITH TABLE KEY alias = alias. ENDIF. DESCRIBE TABLE style_struct-t_fonts LINES style_struct-numfonts_count. ls_font-id = style_struct-numfonts_count. ls_font-alias = alias. ls_font-name = name. ls_font-size = size. IF bold IS SUPPLIED. ls_font-bold = bold. ENDIF. IF italic IS SUPPLIED. ls_font-italic = italic. ENDIF. IF underline IS SUPPLIED. ls_font-underline = underline. ENDIF. IF color_rgb IS SUPPLIED. ls_font-color_rgb = color_rgb. ENDIF. INSERT ls_font INTO TABLE style_struct-t_fonts. SORT style_struct-t_fonts BY id. ADD 1 TO style_struct-numfonts_count. ENDMETHOD. "insert_font * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_MANDATORY_BORDERS * +-------------------------------------------------------------------------------------------------+ * +-------------------------------------------------------------------------------------- METHOD INSERT_MANDATORY_BORDERS. me->insert_border( alias = 'default' ). ENDMETHOD. "INSERT_MANDATORY_BORDERS * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_MANDATORY_FILLS * +-------------------------------------------------------------------------------------------------+ * +-------------------------------------------------------------------------------------- METHOD INSERT_MANDATORY_FILLS. insert_fill( alias = 'none' patterntype = 'none' ). insert_fill( alias = 'gray125' patterntype = 'gray125' ). ENDMETHOD. "INSERT_MANDATORY_FILLS * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_MANDATORY_FONTS * +-------------------------------------------------------------------------------------------------+ * +-------------------------------------------------------------------------------------- METHOD INSERT_MANDATORY_FONTS. me->insert_font( alias = font_mandatory_default_alias name = font_mandatory_default_name size = 11 ). ENDMETHOD. "INSERT_MANDATORY_FONTS * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_MANDATORY_NUMBER_FORMAT * +-------------------------------------------------------------------------------------------------+ * +-------------------------------------------------------------------------------------- METHOD INSERT_MANDATORY_NUMBER_FORMAT. insert_number_format( alias = style_time_default code = default_time_style ). ENDMETHOD. "insert_mandatory_number_format * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_MANDATORY_STYLES * +-------------------------------------------------------------------------------------------------+ * +-------------------------------------------------------------------------------------- METHOD insert_mandatory_styles. insert_mandatory_fonts( ). insert_mandatory_fills( ). insert_alv_color_fills( ). insert_mandatory_borders( ). insert_mandatory_number_format( ). insert_style( alias = 'default' fill_alias = 'none' p_generate_color_variants = abap_false ). insert_style( alias = 'gray125' fill_alias = 'gray125' p_generate_color_variants = abap_false ). insert_style( alias = style_time_default numformat_alias = style_time_default ). ENDMETHOD. "INSERT_MANDATORY_STYLES * ---------------------------------------------------------------------------------------+ * | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_NUMBER_FORMAT * +-------------------------------------------------------------------------------------------------+ * | [--->] ALIAS TYPE STRING * | [--->] CODE TYPE STRING * +-------------------------------------------------------------------------------------- METHOD INSERT_NUMBER_FORMAT. DATA: ls_numfmt LIKE LINE OF style_struct-t_numfmts. READ TABLE style_struct-t_numfmts INTO ls_numfmt WITH KEY alias = alias. IF sy-subrc EQ 0. * TODO existing font --> replace EXIT. ELSE. * new font ENDIF. CLEAR ls_numfmt. DESCRIBE TABLE style_struct-t_numfmts LINES style_struct-numfmts_count. ls_numfmt-id = 168 + style_struct-numfmts_count. ls_numfmt-alias = alias. ls_numfmt-code = code. INSERT ls_numfmt INTO TABLE style_struct-t_numfmts. ADD 1 TO style_struct-numfmts_count. ENDMETHOD. "INSERT_NUMBER_FORMAT * ---------------------------------------------------------------------------------------+ * | Instance Public Method ZSCN_CL_XLSX_WRITER->INSERT_STYLE * +-------------------------------------------------------------------------------------------------+ * | [--->] ALIAS TYPE STRING * | [--->] FONT_ALIAS TYPE STRING (default ='default') * | [--->] FILL_ALIAS TYPE STRING (default ='none') * | [--->] BORDER_ALIAS TYPE STRING (default ='default') * | [--->] NUMFORMAT_ALIAS TYPE STRING (default ='default') * | [--->] NUMFORMAT_ID TYPE I(optional) * | [--->] TEXT_ROTATION TYPE I(optional) * | [--->] WRAP_TEXT TYPE ABAP_BOOL(optional) * | [--->] P_GENERATE_COLOR_VARIANTS TYPE ABAP_BOOL (default =ABAP_TRUE) * +-------------------------------------------------------------------------------------- METHOD insert_style. DATA: ls_cellxf TYPE ys_style_cellxf, ls_fill LIKE LINE OF style_struct-t_fills, lv_current_index TYPE ys_style_cellxf-id, alv_color_fill_alias TYPE string, alv_color_index TYPE lvc_col. READ TABLE style_struct-t_cellxfs WITH TABLE KEY alias = alias alv_color = '' INTO ls_cellxf. IF sy-subrc EQ 0. * style exists --> override of style. * keep everything, just override what is supplied * Delete the current style DELETE style_struct-t_cellxfs WHERE alias = alias. ELSE. * We have a new style ADD 1 TO style_struct-cellxfs_count. ls_cellxf-id = style_struct-cellxfs_count - 1. ENDIF. * ls_cellxf-alias = alias. ls_cellxf-is_string = 1. IF font_alias IS SUPPLIED. ls_cellxf-fontid = g