Skip to Content

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

<?sap.transform simple?>
<tt:transform xmlns:tt="http://www.sap.com/transformation-templates" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">

  <tt:root name="param"/>

  <tt:template>
    <worksheet>

      <dimension>
        <tt:attribute name="ref" value-ref="param.dim"/>
      </dimension>

      <sheetViews>
        <sheetView tabSelected="1" workbookViewId="0">
          <tt:s-cond check="not-initial(param.s_sheet_view.ySplit)">
            <pane>
              <tt:attribute name="ySplit" value-ref="param.s_sheet_view.ySplit"/>
              <tt:attribute name="topLeftCell" value-ref="param.s_sheet_view.topleftcell"/>
              <tt:attribute name="activePane" value-ref="param.s_sheet_view.pane"/>
              <tt:attribute name="state" value-ref="param.s_sheet_view.state"/>
            </pane>

            <selection>

              <tt:attribute name="pane" value-ref="param.s_sheet_view.pane"/>


              <tt:attribute name="activeCell" value-ref="param.s_sheet_view.topleftcell"/>
              <tt:attribute name="sqref" value-ref="param.s_sheet_view.topleftcell"/>


            </selection>
          </tt:s-cond>

        </sheetView>
      </sheetViews>

      <sheetFormatPr>
        <tt:attribute name="defaultRowHeight" value-ref="param.defaultRowHeight"/>
      </sheetFormatPr>


      <cols>
        <tt:loop name="col" ref="param.t_cols">
          <tt:s-cond check="not-initial($col.bestFit)">
            <col>
              <tt:attribute name="min" value-ref="$col.min"/>
              <tt:attribute name="max" value-ref="$col.max"/>
              <tt:attribute name="width" value-ref="$col.width"/>
              <tt:attribute name="style" value-ref="$col.style"/>
              <tt:attribute name="bestFit" value-ref="$col.bestFit"/>
              <tt:attribute name="customWidth">1</tt:attribute>
            </col>
          </tt:s-cond>
        </tt:loop>
      </cols>

      <sheetData>
        <!-- body section -->

        <tt:loop name="body_row" ref="param.t_body_rows">
          <row>
            <tt:attribute name="r" value-ref="$body_row.position"/>
            <tt:attribute name="spans" value-ref="$body_row.spans"/>
            <tt:s-cond check="not-initial($body_row.height)">
              <tt:attribute name="ht" value-ref="$body_row.height"/>
              <tt:attribute name="customHeight">1</tt:attribute>
            </tt:s-cond>
            <tt:s-cond check="$body_row.outlinelevel &gt; 0">
              <tt:attribute name="outlineLevel" value-ref="$body_row.outlinelevel"/>
            </tt:s-cond>
            <tt:s-cond check="not-initial($body_row.hidden)">
              <tt:attribute name="hidden" value-ref="$body_row.hidden"/>
            </tt:s-cond>

            <tt:loop name="body_cell" ref="$body_row.t_cells">
              <c>
                <tt:attribute name="r" value-ref="$body_cell.position"/>
                <tt:s-cond check="not-initial($body_cell.style)">
                  <tt:attribute name="s" value-ref="$body_cell.style"/>
                </tt:s-cond>
                <tt:s-cond check="not-initial($body_cell.sharedstring)">
                  <tt:attribute name="t" value-ref="$body_cell.sharedstring"/>
                  <v>
                    <tt:value ref="$body_cell.index"/>
                  </v>
                </tt:s-cond>
                <tt:s-cond check="initial($body_cell.sharedstring) and not-initial($body_cell.value)">
                  <v>
                    <tt:value ref="$body_cell.value"/>
                  </v>
                </tt:s-cond>
              </c>
            </tt:loop>
          </row>
        </tt:loop>
      </sheetData>
      <tt:s-cond check="not-initial(param.autoFilter)">
        <autoFilter>
          <tt:attribute name="ref" value-ref="param.autoFilter"/>
        </autoFilter>
      </tt:s-cond>

      <tt:s-cond check="not-initial(param.t_merged_cells)">
        <mergeCells>
          <tt:attribute name="count" value-ref="param.merged_cells_count"/>
          <tt:loop name="mergedCells" ref="param.t_merged_cells">
            <mergeCell>
              <tt:attribute name="ref" value-ref="$mergedCells.ref"/>
            </mergeCell>
          </tt:loop>
        </mergeCells>
      </tt:s-cond>

      <pageMargins bottom="1" footer="0.5" header="0.5" left="0.75" right="0.75" top="1"/>
      <headerFooter alignWithMargins="0"/>

      <tt:s-cond check="not-initial(param.drawing_id)">
        <drawing>
          <tt:attribute name="r:id" value-ref="param.drawing_id"/>
        </drawing>
      </tt:s-cond>
    </worksheet>
  </tt:template>

</tt:transform>

 

Simple transformation ZSCN_XLSX_STYLE

<?sap.transform simple?>
<tt:transform xmlns:tt="http://www.sap.com/transformation-templates" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">

  <tt:root name="param"/>

  <tt:template>

    <styleSheet>
      <!-- number formats -->
      <numFmts>
        <tt:attribute name="count" value-ref="param.numfmts_count"/>
        <tt:loop name="fmt" ref="param.t_numfmts">
          <numFmt>
            <tt:attribute name="numFmtId" value-ref="$fmt.id"/>
            <tt:attribute name="formatCode" value-ref="$fmt.code"/>
          </numFmt>
        </tt:loop>
      </numFmts>

      <!-- fonts -->
      <fonts>
        <tt:attribute name="count" value-ref="param.numfonts_count"/>
        <tt:loop name="font" ref="param.t_fonts">
          <font>
            <tt:s-cond check="not-initial($font.bold)">
              <b/>
            </tt:s-cond>
            <tt:s-cond check="not-initial($font.italic)">
              <i/>
            </tt:s-cond>
            <tt:s-cond check="not-initial($font.underline)">
              <u/>
            </tt:s-cond>
            <sz>
              <tt:attribute name="val" value-ref="$font.size"/>
            </sz>
            <tt:s-cond check="not-initial($font.color_rgb)">
              <color>
                <tt:attribute name="rgb" value-ref="$font.color_rgb"/>
              </color>
            </tt:s-cond>
            <name>
              <tt:attribute name="val" value-ref="$font.name"/>
            </name>
          </font>
        </tt:loop>
      </fonts>

      <!-- fills -->
      <fills>
        <tt:attribute name="count" value-ref="param.numfills_count"/>
        <tt:loop name="fill" ref="param.t_fills">
          <fill>
            <tt:cond>
              <patternFill>
                <tt:attribute name="patternType" value-ref="$fill.patterntype"/>

                <tt:s-cond check="not-initial($fill.fgcolor_rgb)">
                  <fgColor>
                    <tt:attribute name="rgb" value-ref="$fill.fgcolor_rgb"/>
                  </fgColor>
                </tt:s-cond>

                <tt:s-cond check="$fill.bgcolor_indexed &gt; 0">
                  <bgColor>
                    <tt:attribute name="indexed" value-ref="$fill.bgcolor_indexed"/>
                  </bgColor>
                </tt:s-cond>

              </patternFill>
            </tt:cond>
          </fill>
        </tt:loop>
      </fills>
      <!-- borders -->

      <borders>
        <tt:attribute name="count" value-ref="param.numborders_count"/>
        <tt:loop name="border" ref="param.t_borders">
          <border>
            <left>
              <tt:s-cond check="not-initial($border.left_style)">
                <tt:attribute name="style" value-ref="$border.left_style"/>
                <tt:s-cond check="not-initial($border.left_color_rgb)">
                  <color>
                    <tt:attribute name="rgb" value-ref="$border.left_color_rgb"/>
                  </color>
                </tt:s-cond>
              </tt:s-cond>
            </left>

            <right>
              <tt:s-cond check="not-initial($border.right_style)">
                <tt:attribute name="style" value-ref="$border.right_style"/>
                <tt:s-cond check="not-initial($border.right_color_rgb)">
                  <color>
                    <tt:attribute name="rgb" value-ref="$border.right_color_rgb"/>
                  </color>
                </tt:s-cond>
              </tt:s-cond>
            </right>

            <top>
              <tt:s-cond check="not-initial($border.top_style)">
                <tt:attribute name="style" value-ref="$border.top_style"/>
                <tt:s-cond check="not-initial($border.top_color_rgb)">
                  <color>
                    <tt:attribute name="rgb" value-ref="$border.top_color_rgb"/>
                  </color>
                </tt:s-cond>
              </tt:s-cond>
            </top>

            <bottom>
              <tt:s-cond check="not-initial($border.bottom_style)">
                <tt:attribute name="style" value-ref="$border.bottom_style"/>
                <tt:s-cond check="not-initial($border.bottom_color_rgb)">
                  <color>
                    <tt:attribute name="rgb" value-ref="$border.bottom_color_rgb"/>
                  </color>
                </tt:s-cond>
              </tt:s-cond>
            </bottom>
           <diagonal/>
          </border>
        </tt:loop>
      </borders>


      <cellStyleXfs count="1">
        <xf borderId="0" fillId="0" fontId="0" numFmtId="0"/>
      </cellStyleXfs>
      <cellXfs>
        <tt:attribute name="count" value-ref="param.cellxfs_count"/>
        <tt:loop name="xf" ref="param.t_cellxfs">
          <xf>
            <tt:attribute name="numFmtId" value-ref="$xf.numfmtid"/>
            <tt:attribute name="fontId" value-ref="$xf.fontid"/>
            <tt:attribute name="fillId" value-ref="$xf.fillid"/>
            <tt:attribute name="borderId" value-ref="$xf.borderid"/>
            <tt:attribute name="xfId" value-ref="$xf.xfid"/>
            <tt:s-cond check="$xf.fillid &gt; 0">
              <tt:attribute name="applyFill">1</tt:attribute>
            </tt:s-cond>
            <tt:s-cond check="$xf.borderid &gt; 0">
              <tt:attribute name="applyBorder">1</tt:attribute>
            </tt:s-cond>
            <tt:s-cond check="$xf.allignment.text_rotation &gt; 0 or not-initial($xf.allignment.wrap_text)">
              <tt:attribute name="applyAlignment">1</tt:attribute>
              <alignment>
                <tt:attribute name="textRotation" value-ref="$xf.allignment.text_rotation"/>
                <tt:attribute name="wrapText" value-ref="$xf.allignment.wrap_text"/>
              </alignment>
            </tt:s-cond>
          </xf>
        </tt:loop>
      </cellXfs>
      <cellStyles count="1">
        <cellStyle builtinId="0" name="Normal" xfId="0"/>
      </cellStyles>
      <dxfs count="0"/>
      <tableStyles count="0" defaultPivotStyle="PivotStyleLight16" defaultTableStyle="TableStyleMedium9"/>
    </styleSheet>
  </tt:template>
</tt:transform>

 

Simple transformation ZSCN_XLSX_CUSTOM_DOC_PROPS

<?sap.transform simple?>
<tt:transform xmlns:tt="http://www.sap.com/transformation-templates"
              xmlns="http://schemas.openxmlformats.org/officeDocument/2006/custom-properties"
              xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
              xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">

  <tt:root name="CUSTOM_DOC_PROPS"/>

  <tt:template>
    <Properties tt:extensible="deep-static">

      <tt:loop name="lines" ref=".CUSTOM_DOC_PROPS">
        <property>
          <tt:attribute name="fmtid" value-ref="$lines.FMTID"/>
          <tt:attribute name="pid" value-ref="$lines.PID"/>
          <tt:attribute name="name" value-ref="$lines.PROP_NAME"/>
          <vt:lpwstr>
            <tt:value ref="$lines.PROP_VALUE"/>
          </vt:lpwstr>
        </property>
      </tt:loop>
    </Properties>
  </tt:template>

</tt:transform>

 

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.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->ADD_CUSTOM_DOC_PROPERTY
* +-------------------------------------------------------------------------------------------------+
* | [--->] PROPERTY_NAME                  TYPE        ANY
* | [--->] PROPERTY_VALUE                 TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->ADD_HEADER_FROM_META
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_DFIES_FIELD                  TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD add_header_from_meta.

  FIELD-SYMBOLS:
    <abap_meta_temp>             LIKE LINE OF abap_meta,
    <header_component>           TYPE any.

  DATA:
   abap_metas_temp            LIKE abap_meta.

  LOOP AT abap_meta ASSIGNING <abap_meta_temp>.
    ASSIGN COMPONENT p_dfies_field OF STRUCTURE <abap_meta_temp> TO <header_component>.
    IF sy-subrc EQ 0.
      IF NOT <header_component> IS INITIAL.
        <abap_meta_temp>-z_header = <header_component>.
*        grow column width if needed
        IF ( strlen( <abap_meta_temp>-z_header ) + 2 ) GE <abap_meta_temp>-z_width.
          <abap_meta_temp>-z_width = strlen( <abap_meta_temp>-z_header ) + 2.
        ENDIF.

      ENDIF.
    ENDIF.

    APPEND <abap_meta_temp> TO abap_metas_temp.
  ENDLOOP.
  feed_data_header( headers = abap_metas_temp ).

ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->ADD_SHAREDSTRING
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_SHAREDSTRING                 TYPE        STRING
* | [<---] E_INDEX                        TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->ADD_STYLE_EXCEPTION
* +-------------------------------------------------------------------------------------------------+
* | [--->] STYLE_ALIAS                    TYPE        STRING
* | [--->] ROW                            TYPE        I(optional)
* | [--->] COL                            TYPE        I(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZSCN_CL_XLSX_WRITER=>BROWSE_CLIENT_FS
* +-------------------------------------------------------------------------------------------------+
* | [<-->] P_FILENAME                     TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZSCN_CL_XLSX_WRITER=>BROWSE_SERVER_FS
* +-------------------------------------------------------------------------------------------------+
* | [<-->] P_FILENAME                     TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | 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)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD build.

    FIELD-SYMBOLS:
     <li_sheet_content>         TYPE ANY TABLE,
     <ls_sheet_content>         TYPE any,
     <lv_cell_value>            TYPE any.

    DATA:
     abap_meta_help             LIKE LINE OF abap_meta,
     abap_metas_help            LIKE abap_meta.

    UNASSIGN <li_sheet_content>.
    ASSIGN abap_sheet_content->* TO <li_sheet_content>.



* 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 = <li_sheet_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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->BUILD_ABAP_META
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | 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)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->CREATE_CELLS
* +-------------------------------------------------------------------------------------------------+
* | [--->] ROW_INDEX                      TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->FEED_DATA_BODY
* +-------------------------------------------------------------------------------------------------+
* | [--->] CONTENT                        TYPE        ANY TABLE
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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:
     <ls_sheet_content>          TYPE any,
     <lv_cell_value>             TYPE any,
     <colors>                    TYPE lvc_t_scol,
     <color>                     TYPE lvc_s_scol,
     <abap_meta>                 LIKE LINE OF abap_meta.

    LOOP AT content ASSIGNING <ls_sheet_content>.

      ADD 1 TO row_index.
      CLEAR ls_row.

*      color manamgent

      IF NOT color_column IS INITIAL.
        ASSIGN COMPONENT color_column OF STRUCTURE <ls_sheet_content> TO <colors>.
      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 <abap_meta>.
        lv_current_col = sy-tabix.
        ASSIGN COMPONENT <abap_meta>-fieldname OF STRUCTURE <ls_sheet_content> TO <lv_cell_value>.

        CLEAR: lv_value, ls_cell, lv_alv_color.

        IF <colors> IS ASSIGNED.
          READ TABLE <colors> ASSIGNING <color>
           WITH KEY fname = <abap_meta>-fieldname.
          IF sy-subrc EQ 0.
            lv_alv_color = get_variant_name_from_color( <color>-color-col ).
          ELSE.
            READ TABLE <colors> ASSIGNING <color>
           WITH KEY fname = ''.
            IF sy-subrc EQ 0.
              lv_alv_color = get_variant_name_from_color( <color>-color-col ).
            ENDIF.

          ENDIF.
        ENDIF.

        ls_cell-style = get_style(   p_alias     = <abap_meta>-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 <abap_meta>-inttype EQ cl_abap_typedescr=>typekind_packed
          OR <abap_meta>-inttype EQ cl_abap_typedescr=>typekind_int.
          IF NOT <lv_cell_value> IS INITIAL.
            lv_add_cell = abap_true.
            IF <lv_cell_value> GE 0.
              ls_cell-value = <lv_cell_value>.
            ELSE.                         "negative number, minus needs to be in the front or excel
              ls_cell-value = abs( <lv_cell_value> ).
              CONCATENATE '-' ls_cell-value INTO ls_cell-value.
            ENDIF.
          ENDIF.

        ELSEIF <abap_meta>-inttype EQ cl_abap_typedescr=>typekind_date.

          IF NOT <lv_cell_value> IS INITIAL.
            lv_add_cell = abap_true.
            CALL METHOD cl_alv_xslt_transform=>get_days_since_1900
              EXPORTING
                i_date = <lv_cell_value>
              RECEIVING
                e_num  = ls_cell-value.
          ENDIF.

        ELSEIF <abap_meta>-inttype EQ cl_abap_typedescr=>typekind_time.
          IF NOT <lv_cell_value> IS INITIAL.
            lv_add_cell = abap_true.

            CALL METHOD cl_alv_xslt_transform=>get_percent_of_act_day
              EXPORTING
                i_time = <lv_cell_value>
              RECEIVING
                e_num  = ls_cell-value.
          ENDIF.
        ELSE.
*        All the rest is string
          IF NOT <lv_cell_value> IS INITIAL.
            IF autofit_column_width EQ abap_true.

              set_max_width(
                EXPORTING
                  p_content   = <lv_cell_value>
                CHANGING
                  p_max_width = <abap_meta>-z_width ).

            ENDIF.

            lv_add_cell = abap_true.

            ls_cell-sharedstring = 's'.
            lv_value = <lv_cell_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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->FEED_DATA_HEADER
* +-------------------------------------------------------------------------------------------------+
* | [--->] HEADERS                        TYPE        YS_ABAP_METAS
* | [--->] STYLE                          TYPE        STRING (default =STYLE_HEADER)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD feed_data_header.

    FIELD-SYMBOLS:
     <abap_meta> 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 <abap_meta> 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 = <abap_meta>-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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->FREEZE_HEADER
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
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.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->GET_BORDER_ID_FROM_ALIAS
* +-------------------------------------------------------------------------------------------------+
* | [--->] ALIAS                          TYPE        STRING
* | [<-()] ID                             TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->GET_CELLPOSITION
* +-------------------------------------------------------------------------------------------------+
* | [--->] ROW                            TYPE        I
* | [--->] COL                            TYPE        I
* | [<-()] RESULT                         TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->GET_FILE_AS_EMAIL_ATTACHMENT
* +-------------------------------------------------------------------------------------------------+
* | [<---] XLSX_ATTACHMENT                TYPE        SOLIX_TAB
* | [<---] ATTACHMENT_LENGHT              TYPE        SO_OBJ_LEN
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->GET_FILE_AS_XSTRING
* +-------------------------------------------------------------------------------------------------+
* | [<-()] XLSX_AS_XSTRING                TYPE        XSTRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD GET_FILE_AS_XSTRING.
    xlsx_as_xstring = me->xlsx_file_raw.
  ENDMETHOD.                    "GET_FILE_AS_XSTRING


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->GET_FILL_ID_FROM_ALIAS
* +-------------------------------------------------------------------------------------------------+
* | [--->] ALIAS                          TYPE        STRING
* | [<-()] ID                             TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->GET_FONT_ID_FROM_ALIAS
* +-------------------------------------------------------------------------------------------------+
* | [--->] ALIAS                          TYPE        STRING
* | [<-()] ID                             TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->GET_NUMFMT_ID_FROM_ALIAS
* +-------------------------------------------------------------------------------------------------+
* | [--->] ALIAS                          TYPE        STRING
* | [<-()] ID                             TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | 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
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->GET_TEXT_WIDTH
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_TEXT                         TYPE        ANY
* | [<-()] P_WIDTH                        TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
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.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->GET_VARIANT_NAME_FROM_COLOR
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_COLOR                        TYPE        LVC_COL
* | [<-()] P_ALV_COLOR_NAME               TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
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.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_ALV_COLOR_FILLS
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | 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)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_DEFAULT_PROPERTIES
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_DEFAULT_STYLES
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | 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)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | 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)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_MANDATORY_BORDERS
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD INSERT_MANDATORY_BORDERS.
    me->insert_border( alias = 'default' ).
  ENDMETHOD.                    "INSERT_MANDATORY_BORDERS


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_MANDATORY_FILLS
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD INSERT_MANDATORY_FILLS.

    insert_fill(
        alias           = 'none'
        patterntype     = 'none' ).

    insert_fill(
        alias           = 'gray125'
        patterntype     = 'gray125' ).


  ENDMETHOD.                    "INSERT_MANDATORY_FILLS


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_MANDATORY_FONTS
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD INSERT_MANDATORY_FONTS.
    me->insert_font(
        alias     = font_mandatory_default_alias
        name      = font_mandatory_default_name
        size      = 11   ).
  ENDMETHOD.                    "INSERT_MANDATORY_FONTS


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_MANDATORY_NUMBER_FORMAT
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD INSERT_MANDATORY_NUMBER_FORMAT.
    insert_number_format(
        alias  = style_time_default
        code   = default_time_style ).
  ENDMETHOD.                    "insert_mandatory_number_format


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_MANDATORY_STYLES
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->INSERT_NUMBER_FORMAT
* +-------------------------------------------------------------------------------------------------+
* | [--->] ALIAS                          TYPE        STRING
* | [--->] CODE                           TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | 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)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  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 = get_font_id_from_alias( alias = font_alias ).
    ENDIF.

    IF  numformat_id IS SUPPLIED.
      ls_cellxf-numfmtid = numformat_id.
    ENDIF.

    IF numformat_alias IS SUPPLIED.
      ls_cellxf-numfmtid = get_numfmt_id_from_alias( alias = numformat_alias ).
    ENDIF.

    IF border_alias IS SUPPLIED.
      ls_cellxf-borderid = get_border_id_from_alias( alias = border_alias ).
    ENDIF.

    IF text_rotation IS SUPPLIED.
      ls_cellxf-allignment-text_rotation = text_rotation.
    ENDIF.

    IF wrap_text IS SUPPLIED.
      IF wrap_text EQ abap_true.
        ls_cellxf-allignment-wrap_text = '1'.
      ELSE.
        ls_cellxf-allignment-wrap_text = '0'.
      ENDIF.
    ENDIF.

    IF fill_alias IS SUPPLIED.
      ls_cellxf-fillid = get_fill_id_from_alias( alias = fill_alias ).
    ENDIF.

    INSERT ls_cellxf INTO TABLE style_struct-t_cellxfs.
    SORT style_struct-t_cellxfs BY id.

    IF p_generate_color_variants EQ abap_true.
      DO 7 TIMES.
        alv_color_index = sy-index.
        alv_color_fill_alias = get_variant_name_from_color( alv_color_index ).
        ADD 1 TO style_struct-cellxfs_count.
        ls_cellxf-id =  style_struct-cellxfs_count - 1.

        ls_cellxf-fillid = get_fill_id_from_alias( alias = alv_color_fill_alias ).
        ls_cellxf-alv_color = alv_color_fill_alias.

        INSERT ls_cellxf INTO TABLE style_struct-t_cellxfs.
        SORT style_struct-t_cellxfs BY id.

      ENDDO.
    ENDIF.

  ENDMETHOD.                    "insert_style


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->MERGE_CELLS
* +-------------------------------------------------------------------------------------------------+
* | [--->] ROW_START                      TYPE        I
* | [--->] COL_START                      TYPE        I
* | [--->] ROW_END                        TYPE        I
* | [--->] COL_END                        TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD MERGE_CELLS.

    DATA:
     ls_merged_cell        LIKE LINE OF sheet_struct-t_merged_cells,
     lv_merged_ref         TYPE string.

    CALL METHOD me->get_cellposition
      EXPORTING
        row    = row_start
        col    = col_start
      RECEIVING
        result = lv_merged_ref.

    ls_merged_cell-ref = lv_merged_ref.

    CLEAR lv_merged_ref.

    CALL METHOD me->get_cellposition
      EXPORTING
        row    = row_end
        col    = col_end
      RECEIVING
        result = lv_merged_ref.

    CONCATENATE ls_merged_cell-ref ':' lv_merged_ref INTO ls_merged_cell-ref.

    APPEND ls_merged_cell TO sheet_struct-t_merged_cells.
    ADD 1 TO sheet_struct-merged_cells_count.

  ENDMETHOD.                    "merge_cells


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->SAVE_FILE_TO_CLIENT
* +-------------------------------------------------------------------------------------------------+
* | [--->] FS_FILENAME                    TYPE        ANY
* | [--->] START_EXCEL                    TYPE        BOOLEAN (default =ABAP_FALSE)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD SAVE_FILE_TO_CLIENT.

    DATA:
      lv_fs_file_name      TYPE string,
      li_binary_tab        TYPE solix_tab,
      lv_file_length       TYPE i,
      lv_subrc             type sysubrc.

    CHECK NOT fs_filename IS INITIAL.

    CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
      EXPORTING
        buffer        = me->xlsx_file_raw
      IMPORTING
        output_length = lv_file_length
      TABLES
        binary_tab    = li_binary_tab.

    lv_fs_file_name = fs_filename.


    cl_gui_frontend_services=>gui_download(
      EXPORTING
        bin_filesize              = lv_file_length
        filename                  = lv_fs_file_name
        filetype                  = 'BIN'
        write_bom                 = abap_true
      CHANGING
        data_tab                  = li_binary_tab
      EXCEPTIONS
        file_write_error          = 1
        no_batch                  = 2
        gui_refuse_filetransfer   = 3
        invalid_type              = 4
        no_authority              = 5
        unknown_error             = 6
        header_not_allowed        = 7
        separator_not_allowed     = 8
        filesize_not_allowed      = 9
        header_too_long           = 10
        dp_error_create           = 11
        dp_error_send             = 12
        dp_error_write            = 13
        unknown_dp_error          = 14
        access_denied             = 15
        dp_out_of_memory          = 16
        disk_full                 = 17
        dp_timeout                = 18
        file_not_found            = 19
        dataprovider_exception    = 20
        control_flush_error       = 21
        not_supported_by_gui      = 22
        error_no_gui              = 23
        OTHERS                    = 24 ).

    IF sy-subrc <> 0.
      lv_subrc = sy-subrc.
*     TODO
    ENDIF.

    IF start_excel EQ abap_true.
      cl_gui_frontend_services=>execute(
        EXPORTING
          document               = lv_fs_file_name
        EXCEPTIONS
          OTHERS                 = 1 ).
    ENDIF.

  ENDMETHOD.                    "save_file_to_client


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->SAVE_FILE_TO_SERVER
* +-------------------------------------------------------------------------------------------------+
* | [--->] FS_FILENAME                    TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD SAVE_FILE_TO_SERVER.

    DATA:
     lv_fs_file_name      TYPE string,
     lv_subrc             type sysubrc.

    CHECK NOT fs_filename IS INITIAL.
    lv_fs_file_name = fs_filename.
    OPEN DATASET lv_fs_file_name FOR OUTPUT IN BINARY MODE.
    IF sy-subrc NE 0.
      lv_subrc = sy-subrc.
* TODO
    ENDIF.
    TRANSFER me->xlsx_file_raw TO lv_fs_file_name.
    CLOSE DATASET lv_fs_file_name.

  ENDMETHOD.                    "SAVE_FILE_TO_SERVER


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->SET_AUTOFILTER
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD set_autofilter.

  DATA:
   no_columns TYPE i.

  DESCRIBE TABLE sheet_struct-t_cols LINES no_columns.

  CALL METHOD me->get_cellposition
    EXPORTING
      row    = 1
      col    = no_columns
    RECEIVING
      result = sheet_struct-autofilter.

  CONCATENATE 'A1:' sheet_struct-autofilter INTO sheet_struct-autofilter.
  sheet_struct-autofilter = |{ sheet_struct-autofilter }|.

ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->SET_AUTOFIT_MULTI_FACTOR
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_AUTOFIT_MULTI_FACTOR         TYPE        P
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD set_autofit_multi_factor.
  autofit_multi_factor = p_autofit_multi_factor.
ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZSCN_CL_XLSX_WRITER->SET_COLOR_COLUMN
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_COLOR_COLUMN                 TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD SET_COLOR_COLUMN.
  color_column = p_color_column.
ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->SET_COLUMN_WIDTH
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_COLUMN_NAME                  TYPE        ANY
* | [--->] P_WIDTH                        TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
method SET_COLUMN_WIDTH.

  FIELD-SYMBOLS:
    <abap_meta> like line of abap_meta.

  READ TABLE abap_meta ASSIGNING <abap_meta>
   with key fieldname = p_column_name.
  if sy-subrc eq 0.
    <abap_meta>-z_width_forced = p_width.
  endif.

endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->SET_CONTENT
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_SHEET_CONTENT                TYPE        ANY TABLE
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD SET_CONTENT.
    FIELD-SYMBOLS:
      <li_sheet_content> TYPE STANDARD TABLE.

    CREATE DATA abap_sheet_content LIKE  p_sheet_content.
    ASSIGN abap_sheet_content->* TO <li_sheet_content>.
    <li_sheet_content> = p_sheet_content.

  ENDMETHOD.                    "SET_CONTENT


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->SET_DEFAULT_COLUMNS
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD set_default_columns.
* sets defaults for header, width and style
    DATA:
      ls_col                     TYPE ys_col_struc,
      ls_abap_meta               LIKE LINE OF abap_meta.

    LOOP AT abap_meta INTO ls_abap_meta.
      CLEAR: ls_col.
      ls_col-min = ls_col-max = sy-tabix.
      IF ls_abap_meta-z_width_forced IS INITIAL.
        ls_col-width = ls_abap_meta-z_width.
      ELSE.
        ls_col-width = ls_abap_meta-z_width_forced.
      ENDIF.
      ls_col-bestfit = 1.
      ls_col-style = get_style( p_alias = ls_abap_meta-z_style p_alv_color = ''  ).
      INSERT ls_col INTO TABLE sheet_struct-t_cols.
    ENDLOOP.

  ENDMETHOD.                    "set_default_columns


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZSCN_CL_XLSX_WRITER->SET_MAX_WIDTH
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_CONTENT                      TYPE        ANY
* | [<-->] P_MAX_WIDTH                    TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD set_max_width.
  DATA:
    width TYPE i.
  width = get_text_width( p_content ).
  IF width GE p_max_width.
    p_max_width = width.
  ENDIF.
ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZSCN_CL_XLSX_WRITER->SET_POSITION_OF_COLOR_COL
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD set_position_of_color_col.
  CHECK NOT color_column IS INITIAL.
  READ TABLE components WITH KEY name = color_column TRANSPORTING NO FIELDS.
  position_of_color_col = sy-tabix.
ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->SET_SPANS
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD SET_SPANS.

    FIELD-SYMBOLS:
     <ls_header_row>            LIKE LINE OF sheet_struct-t_header_rows.

    DATA:
     lv_no_columns              TYPE i,
     lv_no_data_rows            TYPE i,
     lv_no_header_rows          TYPE i,
     lv_dim                     TYPE string.

* Determine positions of start cell and end cell of table content

    DESCRIBE TABLE sheet_struct-t_body_rows LINES lv_no_data_rows.
    DESCRIBE TABLE sheet_struct-t_header_rows LINES lv_no_header_rows.
    DESCRIBE TABLE sheet_struct-t_cols LINES lv_no_columns.

    CALL METHOD me->get_cellposition
      EXPORTING
        row    = ( lv_no_data_rows + lv_no_header_rows )
        col    = lv_no_columns
      RECEIVING
        result = lv_dim.

    CONCATENATE 'A1:' lv_dim INTO sheet_struct-dim.
    spans = |{ lv_no_columns }|.
    CONCATENATE '1:' spans INTO spans.
    sheet_struct-defaultrowheight = c_default_row_height.


*  update the header table with the SPANS

    LOOP AT sheet_struct-t_header_rows ASSIGNING <ls_header_row>.
      <ls_header_row>-spans = spans.
    ENDLOOP.

    LOOP AT sheet_struct-t_body_rows ASSIGNING <ls_header_row>.
      <ls_header_row>-spans = spans.
    ENDLOOP.

  ENDMETHOD.                    "set_spans


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZSCN_CL_XLSX_WRITER->SHOW_SHEET
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_HEADER                       TYPE        ANY(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD show_sheet.

  DATA:
   alv_display_file                 TYPE REF TO cl_salv_table,
   alv_functions                    TYPE REF TO cl_salv_functions,
   alv_display_settings             TYPE REF TO cl_salv_display_settings,
   columns                          TYPE REF TO cl_salv_columns_table,
   alv_layout                       TYPE REF TO cl_salv_layout,
   header                           TYPE lvc_title,
   lines_str                        TYPE string.

  FIELD-SYMBOLS:
   <table>                  TYPE table.

  ASSIGN abap_sheet_content->* TO <table>.

  TRY.
      cl_salv_table=>factory( IMPORTING r_salv_table = alv_display_file CHANGING t_table = <table> ).
    CATCH cx_salv_msg.
  ENDTRY.

  alv_functions = alv_display_file->get_functions( ).
  alv_functions->set_all( abap_true ).

* set optimized columns size
  TRY.
      columns = alv_display_file->get_columns( ).
      columns->set_optimize(  ).
      IF NOT color_column IS INITIAL.
        columns->set_color_column( color_column ).
      ENDIF.
    CATCH cx_salv_not_found.
  ENDTRY.

  alv_display_settings = alv_display_file->get_display_settings( ).
  DESCRIBE TABLE <table>.
  lines_str = sy-tfill.
  IF NOT p_header IS INITIAL.
    header = p_header.
  ELSE.
    CONCATENATE 'Sheet1,'(001) lines_str 'records'(002) INTO header SEPARATED BY space.
  ENDIF.
  alv_display_settings->set_list_header( header ).
  alv_display_file->display( ).


ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZSCN_CL_XLSX_WRITER->TRANSFORM_TO_XSLX
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD transform_to_xslx.

    DATA:
     lr_workbookpart           TYPE REF TO cl_xlsx_workbookpart,
     lr_worksheetparts         TYPE REF TO cl_openxml_partcollection,
     lr_part                   TYPE REF TO cl_openxml_part,
     lr_worksheetpart          TYPE REF TO cl_xlsx_worksheetpart,
     lr_stylespart             TYPE REF TO cl_xlsx_stylespart,
     lr_sharedstringspart      TYPE REF TO cl_xlsx_sharedstringspart,
     lr_drawingpart            TYPE REF TO cl_oxml_drawingspart,
     lr_custom_properties      TYPE REF TO cl_oxml_custompropertiespart,
     lv_sheetxml               TYPE xstring,
     lv_shared_xml             TYPE xstring,
     lv_styles_xml             TYPE xstring,
     lv_custom_doc_props_xml   TYPE xstring,
     lr_xml_string_writer      TYPE REF TO cl_sxml_string_writer.

*   we need to use a string_writer when calling the transformation.
*   Reasons are:
*       1. the parameter "ignore_conversion_errors" which makes the
*          transformation more robust in case of conversion errors
*          especially with Japanese DoubleByte Characters in a non-unicode system
*       2. it keeps the order of the attributes

    TRY.

*   Transformation for the SHEET part
* merge the different content parts:
        APPEND LINES OF sheet_struct-t_header_rows TO sheet_struct-t_body_rows.
        CLEAR sheet_struct-t_header_rows.
        SORT sheet_struct-t_body_rows BY position.
        lr_xml_string_writer = cl_sxml_string_writer=>create( ignore_conversion_errors = abap_true ).
        CALL TRANSFORMATION zscn_xlsx_sheet
           SOURCE param = sheet_struct
           RESULT XML lr_xml_string_writer.
        lv_sheetxml = lr_xml_string_writer->get_output( abap_true ).

*   Transformation for the SHARED STRINGS part
        lr_xml_string_writer = cl_sxml_string_writer=>create( ignore_conversion_errors = abap_true ).
        CALL TRANSFORMATION salv_bs_xml_off2007_shared
           SOURCE param = sharedstring_struct
           RESULT XML lr_xml_string_writer.
        lv_shared_xml = lr_xml_string_writer->get_output( abap_true ).

*   Transformation for the STYLES part
        lr_xml_string_writer = cl_sxml_string_writer=>create( ignore_conversion_errors = abap_true ).
        CALL TRANSFORMATION zscn_xlsx_style
          SOURCE param = style_struct
          RESULT XML lr_xml_string_writer.
        lv_styles_xml = lr_xml_string_writer->get_output( abap_true ).

*   Transformation for the custom doc properties
        lr_xml_string_writer = cl_sxml_string_writer=>create( ignore_conversion_errors = abap_true ).
        CALL TRANSFORMATION zscn_xlsx_custom_doc_props
         SOURCE custom_doc_props = custom_doc_props
         RESULT XML  lr_xml_string_writer .
        lv_custom_doc_props_xml = lr_xml_string_writer->get_output( abap_true ).

      CATCH cx_xslt_abap_call_error.                    "#EC NO_HANDLER
      CATCH cx_xslt_deserialization_error.              "#EC NO_HANDLER
      CATCH cx_xslt_format_error.                       "#EC NO_HANDLER
      CATCH cx_xslt_runtime_error.                      "#EC NO_HANDLER
      CATCH cx_xslt_serialization_error.                "#EC NO_HANDLER
    ENDTRY.

* Get the workbook part of the document
    TRY.
        CALL METHOD xlsx_document->get_workbookpart
          RECEIVING
            rr_part = lr_workbookpart.
*Get the custom properties part
        lr_custom_properties = xlsx_document->add_custompropertiespart( ).
        lr_custom_properties->feed_data( iv_data = lv_custom_doc_props_xml ).

*   Get the first worksheet part
        CALL METHOD lr_workbookpart->get_worksheetparts
          RECEIVING
            rr_parts = lr_worksheetparts.
        CALL METHOD lr_worksheetparts->get_part
          EXPORTING
            iv_index = 0
          RECEIVING
            rr_part  = lr_part.
        lr_worksheetpart ?= lr_part.
*   Fill first worksheet part with the XML data
        CALL METHOD lr_worksheetpart->feed_data
          EXPORTING
            iv_data = lv_sheetxml.
*   Get the style part from the workbook part
        CALL METHOD lr_workbookpart->add_stylespart
          RECEIVING
            rr_part = lr_stylespart.
*   Set the style data into style part
        CALL METHOD lr_stylespart->feed_data
          EXPORTING
            iv_data = lv_styles_xml.
*   Get the shared strings part from the workbook
        CALL METHOD lr_workbookpart->add_sharedstringspart
          RECEIVING
            rr_part = lr_sharedstringspart.
*   Set the sharedstrings XML to the part
        CALL METHOD lr_sharedstringspart->feed_data
          EXPORTING
            iv_data = lv_shared_xml.
*   Package and get the output binary string
        CALL METHOD xlsx_document->get_package_data
          RECEIVING
            rv_data = me->xlsx_file_raw.
      CATCH cx_openxml_format cx_openxml_not_found cx_openxml_not_allowed.
    ENDTRY.
  ENDMETHOD.                    "TRANSFORM_TO_XSLX
ENDCLASS.

 

Program ZSCN_XLSX_WRITER_DEMO_BASIC

*&---------------------------------------------------------------------*
*& Report  ZSCN_XLSX_WRITER_DEMO_BASIC
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT ZSCN_XLSX_WRITER_DEMO_BASIC.



DATA:
 dbtable                          TYPE REF TO data,
 xlsx_writer                      TYPE REF TO ZSCN_CL_XLSX_WRITER.

FIELD-SYMBOLS:
 <dbtable>                     TYPE STANDARD TABLE.
PARAMETERS:
 pdbtab                           TYPE tabname16 DEFAULT 'SFLIGHT',    "DB table to select from
 pmaxrw                           TYPE sy-tabix DEFAULT 500,           "Max number of rows
 pclntfn(255)                     LOWER CASE,                          "Filename on client
 plexcel                          AS CHECKBOX DEFAULT 'X',             "Launch Excel after downloading
 psrvfn(255)                      LOWER CASE.                          "Filename on server

AT SELECTION-SCREEN  ON VALUE-REQUEST FOR pclntfn.
  ZSCN_CL_XLSX_WRITER=>browse_client_fs( CHANGING p_filename = pclntfn  ).

AT SELECTION-SCREEN  ON VALUE-REQUEST FOR psrvfn.
  ZSCN_CL_XLSX_WRITER=>browse_server_fs( CHANGING p_filename = psrvfn  ).

START-OF-SELECTION.


* Authorization check
  AUTHORITY-CHECK OBJECT 'S_TABU_DIS'
           ID 'DICBERCLS' FIELD pdbtab
           ID 'ACTVT' FIELD '03'.
  IF sy-subrc NE 0.
    MESSAGE e034(zz9xa_distr) WITH pdbtab.
*   You are not authorized to display table &1. Program ends.
  ENDIF.

* now validate the input
  TRY.
      cl_abap_dyn_prg=>check_table_name_str(
          val               = to_upper( pdbtab )
          packages          = ''  ).
    CATCH cx_abap_not_a_table .
      MESSAGE e035(zz9xa_distr) WITH pdbtab.
*   Invalid entry, &1 is not a DB table. Program ends.
    CATCH cx_abap_not_in_package. " not intrested
  ENDTRY.

  CREATE DATA dbtable TYPE TABLE OF (pdbtab).
  ASSIGN dbtable->* TO <dbtable>.
  SELECT * FROM (pdbtab) INTO TABLE <dbtable> UP TO pmaxrw ROWS.

  CREATE OBJECT xlsx_writer
    EXPORTING
      sheet_content = <dbtable>.

  xlsx_writer->build(
      add_default_header      = abap_true
      add_techn_names_header  = abap_true
      add_autofilter          = abap_true ).

  xlsx_writer->show_sheet( ).

************************************************************************
* save file to client pc                                               *
************************************************************************
  IF NOT pclntfn IS INITIAL.
        xlsx_writer->save_file_to_client(
          EXPORTING
            fs_filename        = pclntfn
            start_excel        = plexcel ).

    WRITE: 'File created:'(002), pclntfn.
  ENDIF.
************************************************************************
* save file to server                                                  *
************************************************************************
  IF NOT psrvfn IS INITIAL.
        xlsx_writer->save_file_to_server( psrvfn ).
    WRITE: 'File created:'(002), psrvfn.
  ENDIF.
***********

 

Program ZSCN_XLSX_WRITER_DEMO_ADV

*&---------------------------------------------------------------------*
*& Report  ZSCN_XLSX_WRITER_DEMO_ADV
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT zscn_xlsx_writer_demo_adv.

CONSTANTS:
  color_column TYPE lvc_fname VALUE 'ALV_COLOR_COL'.

* for excel color codes, use http://closedxml.codeplex.com/wikipage?title=Excel%20Indexed%20Colors

DATA:
 dbtable                       TYPE REF TO data,
 dbtable_line                  TYPE REF TO data,
 xlsx_writer                   TYPE REF TO zscn_cl_xlsx_writer,
 number_of_records             TYPE i,
 start_time                    TYPE syuzeit,
 end_time                      TYPE syuzeit,
 duration                      TYPE i,
 struc_descr                   TYPE REF TO cl_abap_structdescr,
 components                    TYPE cl_abap_structdescr=>component_table,
 component                     TYPE cl_abap_structdescr=>component,
 table_descr                   TYPE REF TO cl_abap_tabledescr,
 new_struc                     TYPE REF TO data,
 new_table                     TYPE REF TO data,
 alv_color                     TYPE lvc_s_scol,
 alv_colors                    TYPE lvc_t_scol.

FIELD-SYMBOLS:
 <dbtable>                     TYPE STANDARD TABLE,
 <dbtable_line>                TYPE any,
 <new_struc>                   TYPE any,
 <new_table>                   TYPE STANDARD TABLE,
 <color_column>                TYPE any.
PARAMETERS:
 pdbtab                        TYPE tabname16 DEFAULT 'SFLIGHT',    "DB table to select from
 pmaxrw                        TYPE sy-tabix DEFAULT 500,           "Max number of rows
 pshow                         AS CHECKBOX,                         "show alv
 pclntfn(255)                  LOWER CASE,                          "Filename on client
 plexcel                       AS CHECKBOX DEFAULT 'X',             "Launch Excel after downloading
 psrvfn(255)                   LOWER CASE,                          "Filename on server
 p_bgreen                      AS CHECKBOX,                         "green background for header
 p_rotate                      TYPE i,                              "rotate content
 p_border                      AS CHECKBOX,                         "adds a border to the header
 p_font                        AS CHECKBOX,                         "changes the fontID
 p_stylex                      AS CHECKBOX,                         "diffeent style for the column MANDT
 p_hheigh                      TYPE i,                              "header height
 p_color                       AS CHECKBOX.                         "color some cells and rows



AT SELECTION-SCREEN  ON VALUE-REQUEST FOR pclntfn.
  zscn_cl_xlsx_writer=>browse_client_fs( CHANGING p_filename = pclntfn  ).

AT SELECTION-SCREEN  ON VALUE-REQUEST FOR psrvfn.
  zscn_cl_xlsx_writer=>browse_server_fs( CHANGING p_filename = psrvfn  ).

START-OF-SELECTION.

* Authorization check
  AUTHORITY-CHECK OBJECT 'S_TABU_DIS'
           ID 'DICBERCLS' FIELD pdbtab
           ID 'ACTVT' FIELD '03'.
  IF sy-subrc NE 0.
    MESSAGE e034(zz9xa_distr) WITH pdbtab.
*   You are not authorized to display table &1. Program ends.
  ENDIF.

* now validate the input (prevent SQL injections)
  TRY.
      cl_abap_dyn_prg=>check_table_name_str(
          val               = to_upper( pdbtab )
          packages          = ''  ).
    CATCH cx_abap_not_a_table .
      MESSAGE e035(zz9xa_distr) WITH pdbtab.
*   Invalid entry, &1 is not a DB table. Program ends.
    CATCH cx_abap_not_in_package. " not intrested
  ENDTRY.

  CREATE DATA dbtable TYPE TABLE OF (pdbtab).
  ASSIGN dbtable->* TO <dbtable>.

  SELECT * FROM (pdbtab) INTO TABLE <dbtable> UP TO pmaxrw ROWS.
  DESCRIBE TABLE <dbtable> LINES number_of_records.


  GET TIME.
  start_time = sy-uzeit.

  IF p_color EQ abap_true.

    CREATE DATA dbtable_line TYPE  (pdbtab).
    struc_descr ?= cl_abap_structdescr=>describe_by_data_ref( dbtable_line ).
    components = struc_descr->get_components( ).

* Add the component for the Color codeing column
    CLEAR component.
    component-name = color_column.
    component-type ?= cl_abap_tabledescr=>describe_by_name( 'LVC_T_SCOL').

    APPEND component TO components.

    CLEAR struc_descr.
    struc_descr = cl_abap_structdescr=>create( p_components = components ).
    CREATE DATA new_struc TYPE HANDLE struc_descr.
    ASSIGN new_struc->* TO <new_struc>.
    table_descr  = cl_abap_tabledescr=>create( p_line_type  = struc_descr ).
    CREATE DATA new_table TYPE HANDLE table_descr.
    ASSIGN new_table->* TO <new_table>.


    LOOP AT <dbtable> ASSIGNING <dbtable_line> .
      CLEAR: <new_struc>, alv_colors, alv_color.
      MOVE-CORRESPONDING <dbtable_line> TO <new_struc>.
*    coloring
      CASE sy-tabix.
        WHEN 1.
*        color the first row red
          alv_color-color-col = col_negative .
          alv_color-color-int = 0.
          alv_color-color-inv = 0.
          APPEND alv_color TO alv_colors.

        WHEN 2.
*        second line, color first column green.
          READ TABLE components INTO component INDEX 1.
          alv_color-fname = component-name.
          alv_color-color-col = col_positive .
          alv_color-color-int = 0.
          alv_color-color-inv = 0.
          APPEND alv_color TO alv_colors.

        WHEN 3.
*        second line, color first column green.
          READ TABLE components INTO component INDEX 1.
          alv_color-fname = component-name.
          alv_color-color-col = col_total .
          alv_color-color-int = 0.
          alv_color-color-inv = 0.
          APPEND alv_color TO alv_colors.
      ENDCASE.
      ASSIGN COMPONENT color_column OF STRUCTURE <new_struc> TO <color_column>.
      <color_column> = alv_colors.
      APPEND <new_struc> TO <new_table>.

    ENDLOOP.

    CREATE OBJECT xlsx_writer
      EXPORTING
        sheet_content       = <new_table>
        p_color_column      = color_column
        p_row_height_header = p_hheigh.


  ELSE.

  CREATE OBJECT xlsx_writer
    EXPORTING
      sheet_content       = <dbtable>
      p_row_height_header = p_hheigh.

  ENDIF.

  xlsx_writer->add_custom_doc_property(
      property_name  = 'sapSourceSystemID'                  "#EC NOTEXT
      property_value = sy-sysid  ).

  xlsx_writer->add_custom_doc_property(
  property_name  = 'sapSourceClient'                        "#EC NOTEXT
  property_value = sy-mandt  ).

  IF p_bgreen EQ abap_true.
    xlsx_writer->insert_fill(
        alias            = xlsx_writer->style_header
        patterntype     = xlsx_writer->fill_pattern_solid
        fgcolor_rgb     = xlsx_writer->rgb_bright_green
        bgcolor_indexed = xlsx_writer->indexed_col_sys_background ).

  ENDIF.

  IF NOT p_rotate IS INITIAL.
    xlsx_writer->insert_style(
          alias           = xlsx_writer->style_header
          text_rotation   = p_rotate ).
  ENDIF.

  IF p_border EQ abap_true.

    xlsx_writer->insert_border(
        alias            = 'boxed'                          "#EC NOTEXT
        left_style       = 'thick'                          "#EC NOTEXT
        left_color_rgb   = xlsx_writer->rgb_yellow
        right_style      = 'thick'                          "#EC NOTEXT
        right_color_rgb  = xlsx_writer->rgb_yellow
        top_style        = 'thick'                          "#EC NOTEXT
        top_color_rgb    = xlsx_writer->rgb_yellow
        bottom_style     = 'thick'                          "#EC NOTEXT
        bottom_color_rgb = xlsx_writer->rgb_yellow ).

    xlsx_writer->insert_style(
          alias           = xlsx_writer->style_header
          border_alias   = 'boxed' ).                       "#EC NOTEXT
  ENDIF.

  IF p_font EQ abap_true.
    xlsx_writer->insert_font(
        alias     = 'myFont'                                "#EC NOTEXT
        name      = 'Calibri'                               "#EC NOTEXT
        size      = 11
        bold      = abap_true
        italic    = abap_true
        underline = abap_true
        color_rgb = xlsx_writer->rgb_yellow ).
    xlsx_writer->insert_style(
              alias           = xlsx_writer->style_header
              font_alias   = 'myFont' ).                    "#EC NOTEXT
  ENDIF.

  IF p_stylex EQ abap_true.
    xlsx_writer->add_style_exception(
        style_alias = xlsx_writer->style_header
        row         = 0
        col         = 1  ).

  ENDIF.

  xlsx_writer->build( ).


  IF pshow EQ abap_true.
    xlsx_writer->show_sheet( ).
  ENDIF.

************************************************************************
* save file to client pc                                               *
************************************************************************
  IF NOT pclntfn IS INITIAL.
    TRY.
      xlsx_writer->save_file_to_client(
        EXPORTING
          fs_filename        = pclntfn
          start_excel        = plexcel ).
      GET TIME.
      end_time = sy-uzeit.
      duration = end_time - start_time.
      WRITE : / 'seconds it took to create and download the xlsx', duration.
    ENDTRY.
    WRITE: / 'File created on client'(002), pclntfn.
  ENDIF.

************************************************************************
* save file to server                                                  *
************************************************************************
  IF NOT psrvfn IS INITIAL.
    TRY.
      xlsx_writer->save_file_to_server(
        EXPORTING
          fs_filename        = psrvfn ).
    ENDTRY.
    WRITE: / 'File created on server'(003), psrvfn.
  ENDIF.
To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Yuvaraj Shanmugam

    It would have been better if you had given the nugget file of the class. Some of us use a lower version of SAP which doesnt have ‘Source code based editor’ support.

    And it takes a lot of time to copy paste the code for so many methods.

    (0) 
  2. Carlos Constantino

    Hi!

    This looks great, just copied your attachments and It almost worked just fine… One problem: I am using Office 365 and when I open XLSX file I am getting this message:

    “Repaired Records: Cell information from /xl/worksheets/sheet1.xml part”

    XLSXerror.JPG

    Any idea why this is appening?

    Regards,

    Carlos

    (0) 

Leave a Reply