ABAP to XLSX in less than 10 minutes

                               
      1

 

           1                                                      

Simple transformation ZSCN_XLSX_STYLE

                                                                                                                                1   1   1                

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 * | [<---] E_INDEX TYPE I * +-------------------------------------------------------------------------------------- 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 * | [<-()] ID TYPE I * +-------------------------------------------------------------------------------------- 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 * | [<-()] RESULT TYPE STRING * +-------------------------------------------------------------------------------------- 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 * +-------------------------------------------------------------------------------------------------+ * | [<---] XLSX_ATTACHMENT TYPE SOLIX_TAB * | [<---] ATTACHMENT_LENGHT TYPE SO_OBJ_LEN * +-------------------------------------------------------------------------------------- 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 * +-------------------------------------------------------------------------------------------------+ * | [<-()] XLSX_AS_XSTRING TYPE 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 * | [<-()] ID TYPE I * +-------------------------------------------------------------------------------------- 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 * | [<-()] ID TYPE I * +-------------------------------------------------------------------------------------- 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 * | [<-()] ID TYPE I * +-------------------------------------------------------------------------------------- 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) * | [<-()] P_STYLE_ID TYPE I * +-------------------------------------------------------------------------------------- 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 * | [<-()] P_WIDTH TYPE I * +-------------------------------------------------------------------------------------- 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 * | [<-()] P_ALV_COLOR_NAME TYPE STRING * +-------------------------------------------------------------------------------------- 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