Generate dynamic excel from dynamic internal table

former_member140343 · ‎2019 Apr 24

This blog is to create a utility class for generating an excel file dynamically using a dynamic internal table. It can be directly called in any ECC program do download an excel file.
There are already some blogs available on this topic, but those does not support dynamic table.
This utility class can be used in multiple use case i.e. Download program in ECC, download excel via OData service etc.
I will also post my next blog about use of this utility class to download excel via OData service, till then follow my profile for the updates.
So lets start with the utility class creation, here are the steps:

Create following 4 transformations :

Transformation1 : zEXCEL07_SHEET_XML

<?sap.transform simple?>

<tt:transform xmlns:tt="http://www.sap.com/transformation-templates">



<tt:root name="param"/>



<tt:template>

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">



<dimension>

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

</dimension>



<sheetViews>

<sheetView workbookViewId="0">

<!--tabSelected="1" -->

<selection activeCell="A1" sqref="A1"/>

</sheetView>

</sheetViews>



<sheetFormatPr defaultRowHeight="12.75">

<tt:s-cond check="param.outlinelevel &gt; 0">

<tt:attribute name="outlineLevelRow" value-ref="param.outlinelevel"/>

</tt:s-cond>

</sheetFormatPr>



<tt:s-cond check="not-initial(param.t_cols)">

<cols>

<tt:loop name="col" ref="param.t_cols">

<col>

<tt:attribute name="min" value-ref="$col.min"/>

<tt:attribute name="max" value-ref="$col.max"/>

<tt:attribute name="bestfit" value-ref="$col.bestfit"/>

<tt:attribute name="width" value-ref="$col.width"/>

<tt:attribute name="customWidth" value-ref="$col.customWidth"/>

<tt:attribute name="style" value-ref="$col.style"/>

<tt:attribute name="hidden" value-ref="$col.hidden"/>

<tt:attribute name="outlineLevel" value-ref="$col.outline"/>

</col>

</tt:loop>

</cols>

</tt:s-cond>



<sheetData>

<!-- Description -->

<tt:loop name="row" ref="param.t_desc">

<row>

<tt:attribute name="r" value-ref="$row.position"/>

<tt:attribute name="spans" value-ref="$row.spans"/>

<tt:s-cond check="not-initial($row.height)">

<tt:attribute name="ht" value-ref="$row.height"/>

<tt:attribute name="customHeight">1</tt:attribute>

</tt:s-cond>

<tt:s-cond check="$row.outlinelevel &gt; 0">

<tt:attribute name="outlineLevel" value-ref="$row.outlinelevel"/>

</tt:s-cond>

<tt:s-cond check="not-initial($row.hidden)">

<tt:attribute name="hidden" value-ref="$row.hidden"/>

</tt:s-cond>



<tt:loop name="cell" ref="$row.t_cells">

<c>

<tt:attribute name="r" value-ref="$cell.position"/>

<tt:s-cond check="not-initial($cell.style)">

<tt:attribute name="s" value-ref="$cell.style"/>

</tt:s-cond>

<tt:s-cond check="not-initial($cell.sharedstring)">

<tt:attribute name="t" value-ref="$cell.sharedstring"/>

<v>

<tt:value ref="$cell.index"/>

</v>

</tt:s-cond>

<tt:s-cond check="initial($cell.sharedstring) and not-initial($cell.value)">

<v>

<tt:value ref="$cell.value"/>

</v>

</tt:s-cond>

</c>

</tt:loop>

</row>

</tt:loop>

<!--header -->

<tt:loop name="row" ref="param.t_header">

<row>

<tt:attribute name="r" value-ref="$row.position"/>

<tt:attribute name="spans" value-ref="$row.spans"/>

<tt:s-cond check="not-initial($row.height)">

<tt:attribute name="ht" value-ref="$row.height"/>

<tt:attribute name="customHeight">1</tt:attribute>

</tt:s-cond>

<tt:s-cond check="$row.outlinelevel &gt; 0">

<tt:attribute name="outlineLevel" value-ref="$row.outlinelevel"/>

</tt:s-cond>

<tt:s-cond check="not-initial($row.hidden)">

<tt:attribute name="hidden" value-ref="$row.hidden"/>

</tt:s-cond>



<tt:loop name="cell" ref="$row.t_cells">

<c>

<tt:attribute name="r" value-ref="$cell.position"/>

<tt:s-cond check="not-initial($cell.style)">

<tt:attribute name="s" value-ref="$cell.style"/>

</tt:s-cond>

<tt:s-cond check="not-initial($cell.sharedstring)">

<tt:attribute name="t" value-ref="$cell.sharedstring"/>

<v>

<tt:value ref="$cell.index"/>

</v>

</tt:s-cond>

<tt:s-cond check="initial($cell.sharedstring) and not-initial($cell.value)">

<v>

<tt:value ref="$cell.value"/>

</v>

</tt:s-cond>

</c>

</tt:loop>

</row>

</tt:loop>



<!-- data -->

<tt:loop name="row" ref="param.t_rows">

<row>

<tt:attribute name="r" value-ref="$row.position"/>

<tt:attribute name="spans" value-ref="$row.spans"/>

<tt:s-cond check="not-initial($row.height)">

<tt:attribute name="ht" value-ref="$row.height"/>

<tt:attribute name="customHeight">1</tt:attribute>

</tt:s-cond>

<tt:s-cond check="$row.outlinelevel &gt; 0">

<tt:attribute name="outlineLevel" value-ref="$row.outlinelevel"/>

</tt:s-cond>

<tt:s-cond check="not-initial($row.hidden)">

<tt:attribute name="hidden" value-ref="$row.hidden"/>

</tt:s-cond>



<tt:loop name="cell" ref="$row.t_cells">

<c>

<tt:attribute name="r" value-ref="$cell.position"/>

<tt:s-cond check="not-initial($cell.style)">

<tt:attribute name="s" value-ref="$cell.style"/>

</tt:s-cond>

<tt:s-cond check="not-initial($cell.sharedstring)">

<tt:attribute name="t" value-ref="$cell.sharedstring"/>

<v>

<tt:value ref="$cell.index"/>

</v>

</tt:s-cond>

<tt:s-cond check="initial($cell.sharedstring) and not-initial($cell.value)">

<v>

<tt:value ref="$cell.value"/>

</v>

</tt:s-cond>

</c>

</tt:loop>

</row>

</tt:loop>

</sheetData>



<tt:s-cond check="not-initial(param.filter)">

<autoFilter>

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

</autoFilter>

</tt:s-cond>

<tt:s-cond check="not-initial(param.s_merge.t_ref)">

<mergeCells>

<tt:attribute name="count" value-ref="param.s_merge.count"/>

<tt:loop name="mergeCell" ref="param.s_merge.t_ref">

<mergeCell>

<tt:attribute name="ref" value-ref="$mergeCell.span"/>

</mergeCell>

</tt:loop>

</mergeCells>

</tt:s-cond>

<tt:s-cond check="not-initial(param.t_dropdown_formula)">

<dataValidations>

<tt:attribute name="count" value-ref="param.dropdown_count"/>

<tt:loop name="dropDown" ref="param.t_dropdown_formula">

<dataValidation showInputMessage="0" type="list">

<tt:attribute name="sqref" value-ref="$dropDown.cells"/>

<tt:attribute name="showErrorMessage" value-ref="$dropDown.restrict"/>

<tt:attribute name="errorTitle" value-ref="$dropDown.error_text.header"/>

<tt:attribute name="error" value-ref="$dropDown.error_text.text"/>

<formula1>

<tt:value ref="$dropDown.formula"/>

</formula1>

</dataValidation>

</tt:loop>

</dataValidations>

</tt:s-cond>

<tt:s-cond check="not-initial(param.t_hyperlinks)">

<hyperlinks>

<tt:loop name="hyperlink" ref="param.t_hyperlinks">

<hyperlink>

<tt:attribute name="ref" value-ref="$hyperlink.cell_id"/>

<tt:attribute name="location" value-ref="$hyperlink.rel_id"/>

</hyperlink>

</tt:loop>

</hyperlinks>

</tt:s-cond>

<!--&amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;phoneticPr fontId="0" type="noConversion"/&amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;-->

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

<headerFooter alignWithMargins="0"/>

</worksheet>

</tt:template>



</tt:transform>

Transformation 2:ZEXCEL07_STYLESHEET_XML

<?sap.transform simple?>

<tt:transform xmlns:tt="http://www.sap.com/transformation-templates">



<tt:root name="param"/>



<tt:template>



<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<!--&amp;amp;lt;numFmts&amp;amp;gt;

&amp;amp;lt;tt:attribute name="count" value-ref="param.numfmts_count"/&amp;amp;gt;

&amp;amp;lt;tt:loop ref="param.t_numfmts" name="fmt"&amp;amp;gt;

&amp;amp;lt;numFmt&amp;amp;gt;

&amp;amp;lt;tt:attribute name="numFmtId" value-ref="$fmt.id"/&amp;amp;gt;

&amp;amp;lt;tt:attribute name="formatCode" value-ref="$fmt.code"/&amp;amp;gt;

&amp;amp;lt;/numFmt&amp;amp;gt;

&amp;amp;lt;/tt:loop&amp;amp;gt;

&amp;amp;lt;/numFmts&amp;amp;gt;-->



<fonts count="4">

<font>

<sz val="10"/>

<name val="Arial"/>

</font>

<font>

<u/>

<sz val="10"/>

<color indexed="12"/>

<name val="Arial"/>

</font>

<font>

<b/>

<u/>

<sz val="10"/>

<color theme="0"/>

<name val="Arial"/>

<family val="2"/>

</font>



<font>

<b/>

<sz val="10"/>

<name val="Arial"/>

<family val="2"/>

</font>



</fonts>



<fills count="14">

<fill>

<patternFill patternType="none"/>

</fill>

<fill>

<patternFill patternType="gray125"/>

</fill>

<fill>

<patternFill patternType="solid">

<fgColor rgb="FFC5D9F1"/>

<!--<fgColor theme="3" tint="0.59999389629810485"/>-->

<bgColor indexed="64"/>

</patternFill>

</fill>

<fill>

<patternFill patternType="solid">

<fgColor theme="9" tint="0.79998168889431442"/>

<bgColor indexed="64"/>

</patternFill>

</fill>

<fill>

<patternFill patternType="solid">

<fgColor theme="0" tint="-4.9989318521683403E-2"/>

<bgColor indexed="64"/>

</patternFill>

</fill>

<fill>

<patternFill patternType="solid">

<fgColor theme="6" tint="0.79998168889431442"/>

<bgColor indexed="64"/>

</patternFill>

</fill>

<fill>

<patternFill patternType="solid">

<fgColor rgb="FFF2F2F2"/>

<!--<fgColor theme="5" tint="0.59999389629810485" />-->

<bgColor indexed="64"/>

</patternFill>

</fill>

<fill>

<patternFill patternType="solid">

<fgColor theme="2" tint="-9.9978637043366805E-2"/>

<bgColor indexed="64"/>

</patternFill>

</fill>

<fill>

<patternFill patternType="solid">

<fgColor theme="4" tint="0.39997558519241921"/>

<bgColor indexed="64"/>

</patternFill>

</fill>

<fill>

<patternFill patternType="solid">

<fgColor rgb="FF92D050"/>

<bgColor indexed="64"/>

</patternFill>

</fill>

<fill>

<patternFill patternType="solid">

<fgColor theme="4" tint="0.79998168889431442"/>

<bgColor indexed="64"/>

</patternFill>

</fill>

<fill>

<patternFill patternType="solid">

<fgColor rgb="FFEEF3F8"/>

<bgColor indexed="64"/>

</patternFill>

</fill>

<fill>

<patternFill patternType="solid">

<fgColor rgb="FFFFFFFF"/>

<bgColor indexed="64"/>

</patternFill>

</fill>

<fill>

<patternFill patternType="solid">

<fgColor rgb="FFE8F5F8"/>

<bgColor indexed="64"/>

</patternFill>

</fill>

<fill>

<patternFill patternType="solid">

<fgColor rgb="FFC3EBD7"/>

<bgColor indexed="64"/>

</patternFill>

</fill>



</fills>

<borders count="2">

<border>

<left/>

<right/>

<top/>

<bottom/>

<diagonal/>

</border>

<border>

<left style="thin">

<color indexed="64"/>

</left>

<right style="thin">

<color indexed="64"/>

</right>

<top style="thin">

<color indexed="64"/>

</top>

<bottom style="thin">

<color indexed="64"/>

</bottom>

<diagonal/>

</border>

</borders>

<cellStyleXfs count="2">

<xf borderId="0" fillId="0" fontId="0" numFmtId="0"/>

<xf applyAlignment="0" applyBorder="0" applyFill="0" applyNumberFormat="0" borderId="0" fillId="0" fontId="1" numFmtId="0">

<alignment vertical="top"/>

</xf>

</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.xfid"/>

<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.is_string &gt; 0">

<tt:attribute name="applyNumberFormat">1</tt:attribute>

<tt:attribute name="applyAlignment">1</tt:attribute>

</tt:s-cond>

<tt:attribute name="applyProtection">1</tt:attribute>

<tt:s-cond check="$xf.protectionid &gt; 0">

<protection>

<tt:attribute name="locked">0</tt:attribute>

</protection>

</tt:s-cond>

<tt:s-cond check="$xf.is_string &gt; 0">

<alignment>

<tt:s-cond check="$xf.is_string &gt; 0">

<tt:attribute name="horizontal">right</tt:attribute>

</tt:s-cond>

<tt:s-cond check="$xf.indent &gt; 0">

<tt:attribute name="indent" value-ref="$xf.indent"/>

</tt:s-cond>

<tt:s-cond check="$xf.wrap &gt; 0">

<tt:attribute name="wrapText" value-ref="$xf.wrap"/>

</tt:s-cond>

<tt:attribute name="vertical">top</tt:attribute>

</alignment>

</tt:s-cond>

</xf>

</tt:loop>

</cellXfs>

<cellStyles count="2">

<cellStyle builtinId="8" name="Hyperlink" xfId="1"/>

<cellStyle builtinId="0" name="Normal" xfId="0"/>

</cellStyles>

<dxfs count="0"/>

<tableStyles count="0" defaultPivotStyle="PivotStyleLight16" defaultTableStyle="TableStyleMedium9"/>

</styleSheet>



</tt:template>



</tt:transform>

Transformation3 : ZEXCEL07_SHRDSTRING_XML

<?sap.transform simple?>

<tt:transform xmlns:tt="http://www.sap.com/transformation-templates">



<tt:root name="param"/>



<tt:template>

<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">

<tt:attribute name="count" value-ref="param.string_count"/>

<tt:attribute name="uniqueCount" value-ref="param.string_ucount"/>

<tt:loop ref="param.t_strings" name="ss">

<si>

<t>

<tt:value ref="$ss.value"/>

</t>

</si>

</tt:loop>

</sst>

</tt:template>



</tt:transform>

Transformation 4: ZEXCEL07_WORKBOOK_XML

<?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>

<workbook>

<bookViews>

<workbookView windowHeight="14940" windowWidth="21855" xWindow="0" yWindow="0"/>

</bookViews>



<sheets>

<tt:loop name="sheet" ref=".param">

<sheet>

<tt:attribute name="name" value-ref="$sheet.name"/>

<tt:attribute name="sheetId" value-ref="$sheet.sheetid"/>

<tt:s-cond check="not-initial($sheet.state)">

<tt:attribute name="state" value-ref="$sheet.state"/>

</tt:s-cond>

<tt:attribute name="r:id" value-ref="$sheet.relid"/>

</sheet>

</tt:loop>

</sheets>

</workbook>

</tt:template>



</tt:transform>

2. Now create a class by pasting following source code :

class ZTEST_ADPATER definition

public

create public .



public section.



types:

BEGIN OF ts_mergelist,

to   TYPE string,

from TYPE string,

END OF ts_mergelist .

types:

BEGIN OF ts_col_group,

to   TYPE string,

from TYPE string,

END OF ts_col_group .

types:

BEGIN OF ty_drop_down_source,

sheet_name TYPE string,

col        TYPE string,

row_from   TYPE i,

row_to     TYPE i,

END OF ty_drop_down_source .

types:

BEGIN OF ty_drop_down_range,

col_from TYPE i,

col_to   TYPE i,

row_from TYPE i,

row_to   TYPE i,

END OF ty_drop_down_range .

types:

BEGIN OF ts_error_msg,

header TYPE string,

text   TYPE string,

END OF ts_error_msg .

types:

BEGIN OF ty_drop_down_config,

source          TYPE ty_drop_down_source,

range           TYPE ty_drop_down_range,

restrict_values TYPE boolean,

error_text      TYPE ts_error_msg,

END OF ty_drop_down_config .

types:

tt_mergelist TYPE TABLE OF ts_mergelist .

types:

tt_col_group TYPE TABLE OF ts_col_group .

types:

BEGIN OF ts_sheet_name,

sheet_name TYPE string,

END OF ts_sheet_name .

types:

tt_sheet_name TYPE STANDARD TABLE OF ts_sheet_name WITH UNIQUE SORTED KEY sort_key COMPONENTS sheet_name INITIAL SIZE 1 .

types:

BEGIN OF ts_header_struc,

field_name         TYPE string,

row_index          TYPE i,

color_index        TYPE i,

merge_col_cells    TYPE i,

apply_filter       TYPE boole_d,

fix_length         TYPE boole_d,

unlocked           TYPE boolean,

col_index          TYPE i,

s_drop_down_config TYPE ty_drop_down_config,

END OF ts_header_struc .

types:

tt_header_table TYPE STANDARD TABLE OF ts_header_struc INITIAL SIZE 1 .

types:

BEGIN OF ts_col_row,

col_index   TYPE i,

cell_data   TYPE string,

editable    TYPE boole_d,

color_index TYPE i,

END OF ts_col_row .

types:

BEGIN OF ts_desc_row,

row_no TYPE i,

t_col  TYPE STANDARD TABLE OF ts_col_row WITH KEY col_index INITIAL SIZE 1,

END OF ts_desc_row .

types:

tt_descr_table TYPE STANDARD TABLE OF ts_desc_row WITH KEY row_no INITIAL SIZE 1 .

types:

BEGIN OF ts_list_config_for_header ,

source         TYPE ty_drop_down_source,

range          TYPE ty_drop_down_range,

restict_values TYPE boolean,

error_text     TYPE ts_error_msg,

END OF ts_list_config_for_header .

types:

tt_list_config_for_header TYPE TABLE OF  ts_list_config_for_header .

types:

BEGIN OF ts_data_desc,

inttype            TYPE inttype,

lock_col           TYPE boole_d,

num_fmt            TYPE string,

s_drop_down_config TYPE ty_drop_down_config,

style              TYPE i,

alternate_style    TYPE i,

lock_for_sheet     TYPE boolean,

hidden             TYPE boolean,

col_width          TYPE i, "width of column

outline            TYPE i,

exclude            TYPE boolean,

col_pos            TYPE i,

END OF ts_data_desc .

types:

tt_data_desc TYPE STANDARD TABLE OF ts_data_desc .

types:

BEGIN OF ts_hyperlink_struct,

rel_id  TYPE string,

cell_id TYPE string,

END OF ts_hyperlink_struct .

types:

tt_hyperlink TYPE STANDARD TABLE OF ts_hyperlink_struct WITH NON-UNIQUE KEY cell_id INITIAL SIZE 1 .



constants CO_STATE_HIDDEN type STRING value 'hidden' ##NO_TEXT.



methods CREATE_SHEET

importing

!IV_SHEET_NAME type STRING optional

!IV_STATE type STRING optional

!IV_DISP_HEADER type BOOLE_D default ABAP_TRUE

!IT_TABLE_DATA type STANDARD TABLE

!IT_HEADER type TT_HEADER_TABLE optional

!IT_DATA_DESCR type TT_DATA_DESC optional

!IT_DESC type TT_DESCR_TABLE optional

!IT_LIST_CONFIG_FOR_HEADER type TT_LIST_CONFIG_FOR_HEADER optional

!IT_MERGELIST type TT_MERGELIST optional

!IV_ALTERNATE_COUNT type I optional

!IV_LOCK_ALL_CELLS type BOOLEAN default ABAP_FALSE

!IV_DEFAULT_WIDTH type I default 15

!IT_HYPERLINK type TT_HYPERLINK optional

!IV_PROTECTED type BOOLE_D default ABAP_FALSE

exceptions

NAME_ALREADY_EXIST .

methods PREPARE_FOR_DOWNLOAD

returning

value(RV_XLSX_XML) type XSTRING

exceptions

OPENXML_ERROR .

methods DOWNLOAD

importing

!IV_EXCEL_NAME type STRING optional

exceptions

DOWNLOAD_FAILED

DOWNLOAD_CANCELLED .

methods GET_UPLOADED_SHEET_NAMES

exporting

!ET_SHEETS type TT_SHEET_NAME .

methods UPDATE_SHEET_DATA

importing

!IV_NAME type STRING optional

!IV_XML type XSTRING optional

!IT_TABLE_DATA type STANDARD TABLE optional

exporting

!EV_XML type XSTRING .

methods FILL_DECIMAL_FORMAT

importing

!IV_DCPM type USR01-DCPFM .

methods GET_EXCEL_STREAM

exporting

!EV_XML type XSTRING .

PROTECTED SECTION.

PRIVATE SECTION.



TYPES:

BEGIN OF ts_drop_down_formula ,

cells      TYPE string,

formula    TYPE string,

restrict   TYPE boolean,

error_text TYPE ts_error_msg,

END OF ts_drop_down_formula .

TYPES:

tt_drop_down_formula TYPE STANDARD TABLE OF  ts_drop_down_formula WITH KEY cells .

TYPES:

BEGIN OF ts_sharedstring,

value TYPE string,

pos   TYPE i,

END OF ts_sharedstring .

TYPES:

tt_sharedstring TYPE HASHED TABLE OF ts_sharedstring WITH UNIQUE KEY value INITIAL SIZE 1 .

TYPES:

tt_shstr_upload TYPE STANDARD TABLE OF ts_sharedstring WITH KEY pos INITIAL SIZE 1 .

TYPES:

BEGIN OF ts_sharedstring_struc,

t_strings     TYPE tt_sharedstring,

string_count  TYPE i,

string_ucount TYPE i,

END OF ts_sharedstring_struc .

TYPES:

BEGIN OF ts_shstr_upload_struc,

t_strings     TYPE tt_shstr_upload,

string_count  TYPE i,

string_ucount TYPE i,

END OF ts_shstr_upload_struc .

TYPES:

BEGIN OF ts_cell_struc,

position     TYPE string,

cell         TYPE string,

value        TYPE string,

index        TYPE i,

style        TYPE i,

sharedstring TYPE string,

column_index TYPE i,

END OF ts_cell_struc .

TYPES:

BEGIN OF ts_hyperlink_struc,

rel_id  TYPE string,

cell_id TYPE string,

END OF ts_hyperlink_struc .

TYPES:

BEGIN OF ts_ref_struc,

span TYPE string,

END OF ts_ref_struc .

TYPES:

BEGIN OF ts_merge_struc,

count TYPE i,

t_ref TYPE STANDARD TABLE OF ts_ref_struc WITH NON-UNIQUE KEY span INITIAL SIZE 1,

END OF ts_merge_struc .

TYPES:

BEGIN OF ts_col_struc,

min         TYPE i,

max         TYPE i,

bestfit     TYPE i,

width       TYPE i,

customwidth TYPE i,

style       TYPE i,

hidden      TYPE i,

outline     TYPE i,

END OF ts_col_struc .

TYPES:

tt_col_struc TYPE STANDARD TABLE OF ts_col_struc WITH NON-UNIQUE KEY min INITIAL SIZE 1 .

TYPES:

BEGIN OF ts_row_struc,

spans        TYPE string,

position     TYPE i,

outlinelevel TYPE i,

hidden       TYPE char1,

height       TYPE i,

t_cells      TYPE STANDARD TABLE OF ts_cell_struc WITH NON-UNIQUE KEY position INITIAL SIZE 1,

END OF ts_row_struc .

TYPES:

BEGIN OF ts_sheet_struc,

dim                TYPE string,

outlinelevel       TYPE i,

summary_below      TYPE string,

t_header           TYPE STANDARD TABLE OF ts_row_struc WITH NON-UNIQUE KEY position INITIAL SIZE 1,

t_desc             TYPE STANDARD TABLE OF ts_row_struc WITH NON-UNIQUE KEY position INITIAL SIZE 1,

t_rows             TYPE STANDARD TABLE OF ts_row_struc WITH NON-UNIQUE KEY position INITIAL SIZE 1,

t_cols             TYPE STANDARD TABLE OF ts_col_struc WITH NON-UNIQUE KEY min INITIAL SIZE 1,

s_merge            TYPE ts_merge_struc,

filter             TYPE string,

t_hyperlinks       TYPE STANDARD TABLE OF ts_hyperlink_struc WITH NON-UNIQUE KEY cell_id INITIAL SIZE 1,

drawing_id         TYPE string,

dropdown_count     TYPE i,

t_dropdown_formula TYPE tt_drop_down_formula,

protected          TYPE boole_d,

END OF ts_sheet_struc .

TYPES:

BEGIN OF ts_sheets_struc,

name    TYPE string,

sheetid TYPE i,

state   TYPE string,

relid   TYPE string,

sheet   TYPE ts_sheet_struc,

END OF ts_sheets_struc .

TYPES:

tt_sheets TYPE STANDARD TABLE OF ts_sheets_struc WITH KEY name INITIAL SIZE 1 .

TYPES:

BEGIN OF ts_style_numfmt,

id   TYPE i,

code TYPE string,

END OF ts_style_numfmt .

TYPES:

BEGIN OF ts_style_cellxf,

index        TYPE i,

numfmtid     TYPE i,

fillid       TYPE i,

borderid     TYPE i,

is_string    TYPE i,

indent       TYPE i,

xfid         TYPE i,

wrap         TYPE i,

protectionid TYPE i,

fontid       TYPE i,

key          TYPE string,

END OF ts_style_cellxf .

TYPES:

BEGIN OF ts_style_struc,

t_numfmts     TYPE STANDARD TABLE OF ts_style_numfmt WITH KEY id INITIAL SIZE 1,

t_cellxfs     TYPE STANDARD TABLE OF ts_style_cellxf WITH KEY key indent xfid wrap INITIAL SIZE 1,

numfmts_count TYPE i,

cellxfs_count TYPE i,

END OF ts_style_struc .

TYPES:

BEGIN OF ts_meta_data,

sheetno      TYPE i,

dscr_start   TYPE i,

dscr_stop    TYPE i,

header_start TYPE i,

header_stop  TYPE i,

data_start   TYPE i,

data_stop    TYPE i,

END OF ts_meta_data .

TYPES:

tt_meta_data TYPE STANDARD TABLE OF ts_meta_data WITH KEY sheetno INITIAL SIZE 1 .



DATA ms_style TYPE ts_style_struc .

DATA ms_sharedstring TYPE ts_sharedstring_struc .

DATA mt_sheets TYPE tt_sheets .

DATA mt_meta_data TYPE tt_meta_data .

DATA mt_shdrstr_upload TYPE ts_shstr_upload_struc .

CONSTANTS co_char TYPE char26 VALUE 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ##NO_TEXT.

CONSTANTS co_sheet TYPE string VALUE 'Sheet' ##NO_TEXT.

CONSTANTS co_workbook TYPE string VALUE 'sheets' ##NO_TEXT.

CONSTANTS co_sheet_data TYPE string VALUE 'sheetData' ##NO_TEXT.

CONSTANTS co_shared_string TYPE string VALUE 'sst' ##NO_TEXT.

CONSTANTS co_meta_data TYPE string VALUE '_meta_data' ##NO_TEXT.

DATA mv_numfmt TYPE i VALUE 163 ##NO_TEXT.

DATA mv_sheet_no TYPE i .

DATA mo_xlsx_doc TYPE REF TO cl_xlsx_document .

DATA mv_alternate TYPE boolean .

DATA mv_alternate_count TYPE i .

DATA mv_dcpfm TYPE usr01-dcpfm .



METHODS get_cell_index

IMPORTING

!iv_cell TYPE string

EXPORTING

!ev_row  TYPE i

!ev_col  TYPE i .

METHODS get_col_index

IMPORTING

!iv_col_index TYPE string

EXPORTING

!ev_col       TYPE i .

METHODS get_width_for_cols

IMPORTING

!iv_width        TYPE i DEFAULT 15

!it_header_table TYPE tt_header_table

!it_data_descr   TYPE tt_data_desc OPTIONAL

EXPORTING

!et_col          TYPE tt_col_struc .

METHODS upload

EXPORTING

!ev_xlsx_xml TYPE xstring

EXCEPTIONS

file_upload_error

user_cancel .

METHODS get_ss_position

IMPORTING

!iv_value       TYPE data

RETURNING

VALUE(rv_index) TYPE i .

METHODS create_new_cell

IMPORTING

!iv_row       TYPE i

!iv_col       TYPE i

!iv_style     TYPE i

!iv_value     TYPE data OPTIONAL

!iv_is_string TYPE boole_d DEFAULT abap_false

CHANGING

!cs_cell      TYPE ts_cell_struc .

METHODS get_cell_position

IMPORTING

!iv_row            TYPE i

!iv_col            TYPE i

RETURNING

VALUE(rv_position) TYPE string .

METHODS create_row

IMPORTING

!iv_row             TYPE i

!iv_style           TYPE i

!is_data            TYPE data

!it_data_descr      TYPE tt_data_desc OPTIONAL

!it_ddic_fld        TYPE ddfields OPTIONAL

!iv_alternate_count TYPE i OPTIONAL

CHANGING

!ct_row             TYPE STANDARD TABLE .

METHODS create_style_sheet .

METHODS create_header

IMPORTING

!iv_row        TYPE i

!iv_ddic_struc TYPE boole_d DEFAULT abap_true

!it_header     TYPE STANDARD TABLE

EXPORTING

!ev_row        TYPE i

CHANGING

!cs_sheet      TYPE ts_sheet_struc .

METHODS create_description

IMPORTING

!iv_row   TYPE i

!it_decs  TYPE tt_descr_table

EXPORTING

!ev_row   TYPE i

CHANGING

!cs_sheet TYPE ts_sheet_struc .

METHODS get_data_from_xml

IMPORTING

!iv_xml  TYPE xstring

!iv_node TYPE string

EXPORTING

!et_data TYPE data .

METHODS prepare_drop_down_list

IMPORTING

!it_header                 TYPE tt_header_table OPTIONAL

!it_data_descr             TYPE tt_data_desc

!it_list_config_for_header TYPE tt_list_config_for_header OPTIONAL

!iv_data_stop              TYPE i

CHANGING

!cv_dropdown_count         TYPE i

!ct_dropdown_formula       TYPE tt_drop_down_formula .

METHODS _is_num

IMPORTING

!iv_value     TYPE string

RETURNING

VALUE(rv_num) TYPE boolean .

METHODS get_col_label

IMPORTING

!iv_col TYPE i

EXPORTING

!ev_col TYPE string .

ENDCLASS.



CLASS ZTEST_ADPATER IMPLEMENTATION.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->CREATE_DESCRIPTION

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_ROW                         TYPE        I

* | [--->] IT_DECS                        TYPE        TT_DESCR_TABLE

* | [<---] EV_ROW                         TYPE        I

* | [<-->] CS_SHEET                       TYPE        TS_SHEET_STRUC

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD create_description.

DATA:

lv_col    TYPE i,

lv_row    TYPE i,

lv_span_i TYPE i,

lv_style  TYPE i,

lv_span   TYPE string,

ls_cell   TYPE ts_cell_struc.



DATA:

ls_rows  TYPE ts_row_struc,

lt_cells TYPE STANDARD TABLE OF ts_cell_struc WITH NON-UNIQUE KEY position INITIAL SIZE 1.



FIELD-SYMBOLS:

<ls_desc> TYPE ts_desc_row,

<ls_col>  TYPE ts_col_row.



lv_row = iv_row.

LOOP AT it_decs ASSIGNING <ls_desc>.

ADD 1 TO lv_row.

lv_col = 1.

CLEAR: lv_span_i, ls_rows, lt_cells.

LOOP AT <ls_desc>-t_col ASSIGNING <ls_col>. "columns of a row

IF <ls_col>-editable EQ abap_false.

IF <ls_col>-color_index EQ 1.

lv_style = 15.

ELSE.

lv_style = 1.

ENDIF.

ELSE.

IF <ls_col>-color_index EQ 1.

lv_style = 15.

ELSE.

lv_style = 2.

ENDIF.

ENDIF.

*      create new cell

create_new_cell(

EXPORTING

iv_row       = lv_row

iv_col       = lv_col

iv_style     = lv_style

iv_value     = <ls_col>-cell_data

iv_is_string = abap_true

CHANGING

cs_cell      = ls_cell

).

INSERT ls_cell INTO TABLE lt_cells.

ADD 1 TO lv_span_i.

ADD 1 TO lv_col.

ENDLOOP.

*    update created row and calculate span

ls_rows-position  = lv_row.

lv_span = lv_span_i.

CONCATENATE '1:' lv_span INTO ls_rows-spans.

ls_rows-t_cells   = lt_cells.

INSERT ls_rows INTO TABLE cs_sheet-t_rows.

ENDLOOP.

ev_row = lv_row.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->CREATE_HEADER

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_ROW                         TYPE        I

* | [--->] IV_DDIC_STRUC                  TYPE        BOOLE_D (default =ABAP_TRUE)

* | [--->] IT_HEADER                      TYPE        STANDARD TABLE

* | [<---] EV_ROW                         TYPE        I

* | [<-->] CS_SHEET                       TYPE        TS_SHEET_STRUC

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD create_header.

DATA:

lv_col    TYPE i,

lv_row    TYPE i,

lv_row_i  TYPE i,

lv_span_i TYPE i,

lv_style  TYPE i,

lv_span   TYPE string,

lv_comp   TYPE string,

ls_cell   TYPE ts_cell_struc.



DATA:

ls_rows  TYPE ts_row_struc,

*    lt_rows   TYPE TABLE OF ts_row_struc,

lt_cells TYPE STANDARD TABLE OF ts_cell_struc WITH NON-UNIQUE KEY position INITIAL SIZE 1.



FIELD-SYMBOLS:

<ls_field_list> TYPE any,

<lv_locked>     TYPE any,

<lv_value>      TYPE any.



lv_row = iv_row.

*  set style and component for field name

IF iv_ddic_struc EQ abap_true.

lv_comp = 'REPTEXT'.

lv_style = 5.

ELSE.

lv_comp = 'FIELD_NAME'.

lv_style = 22.

ENDIF.



LOOP AT it_header ASSIGNING <ls_field_list>.

ADD 1 TO lv_col.

IF iv_ddic_struc NE abap_true.



*      if row no changes update the row and create the new row index

ASSIGN COMPONENT 'ROW_INDEX' OF STRUCTURE <ls_field_list> TO <lv_value>.

IF <lv_value> IS ASSIGNED AND <lv_value> NE lv_row_i.

ls_rows-position  = lv_row.

lv_span = lv_span_i.

CONCATENATE '1:' lv_span INTO ls_rows-spans.

ls_rows-t_cells   = lt_cells.

INSERT ls_rows INTO TABLE cs_sheet-t_rows.

CLEAR:

lv_span_i, ls_rows, lt_cells.

ADD 1 TO lv_row.

lv_row_i = <lv_value>.

lv_col = 1.

ENDIF.



*      decide style for the header based on the color index

*        ASSIGN COMPONENT 'COLOR_INDEX' OF STRUCTURE <ls_field_list> TO <lv_value>.

*        ASSIGN COMPONENT 'UNLOCKED' OF STRUCTURE <ls_field_list> TO <lv_locked>.

*        CASE <lv_value>.

*          WHEN 1.

*            IF <lv_locked> EQ abap_false.

*              lv_style = 22.

*            ELSE.

*              lv_style = 21.

*            ENDIF.

*          WHEN 2.

*            IF <lv_locked> EQ abap_false.

*              lv_style = 4.

*            ELSE.

*              lv_style = 21.

*            ENDIF.

*          WHEN 3.

*            IF <lv_locked> EQ abap_false.

*              lv_style = 9.

*            ELSE.

*              lv_style = 21.

*            ENDIF..

*          WHEN 4.

*            IF <lv_locked> EQ abap_false.

*              lv_style = 11.

*            ELSE.

*              lv_style = 21 .

*            ENDIF..

*          WHEN 5.

*            IF <lv_locked> EQ abap_false.

*              lv_style = 13.

*            ELSE.

*              lv_style = 21 .

*            ENDIF.

*          WHEN 6.

*            IF <lv_locked> EQ abap_false.

*              lv_style = 7.

*            ELSE.

*              lv_style = 21 .

*            ENDIF.

*          WHEN OTHERS.

*            IF <lv_locked> EQ abap_false.

*              lv_style = 5.

*            ELSE.

*              lv_style = 21.

*            ENDIF.

*        ENDCASE.

ENDIF.

*    get value for the cell and create it

ASSIGN COMPONENT lv_comp OF STRUCTURE <ls_field_list> TO <lv_value>.

IF <lv_value> IS ASSIGNED.

create_new_cell(

EXPORTING

iv_row       = lv_row

iv_col       = lv_col

iv_style     = lv_style

iv_value     = <lv_value>

iv_is_string = abap_true

CHANGING

cs_cell      = ls_cell

).

ENDIF.

INSERT ls_cell INTO TABLE lt_cells.

ADD 1 TO lv_span_i.

ENDLOOP.

ls_rows-position  = lv_row.

lv_span = lv_span_i.

CONCATENATE '1:' lv_span INTO ls_rows-spans.

ls_rows-t_cells   = lt_cells.

INSERT ls_rows INTO TABLE cs_sheet-t_rows.

ev_row = lv_row.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->CREATE_NEW_CELL

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_ROW                         TYPE        I

* | [--->] IV_COL                         TYPE        I

* | [--->] IV_STYLE                       TYPE        I

* | [--->] IV_VALUE                       TYPE        DATA(optional)

* | [--->] IV_IS_STRING                   TYPE        BOOLE_D (default =ABAP_FALSE)

* | [<-->] CS_CELL                        TYPE        TS_CELL_STRUC

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD create_new_cell.

*  get the cell postion

get_cell_position(

EXPORTING

iv_row      = iv_row

iv_col      = iv_col

RECEIVING

rv_position = cs_cell-position

).

IF iv_is_string EQ abap_true.

cs_cell-index = get_ss_position( iv_value = iv_value ).

cs_cell-sharedstring = 's'.

ELSE.

cs_cell-value = iv_value.

ENDIF.

cs_cell-style = iv_style.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->CREATE_ROW

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_ROW                         TYPE        I

* | [--->] IV_STYLE                       TYPE        I

* | [--->] IS_DATA                        TYPE        DATA

* | [--->] IT_DATA_DESCR                  TYPE        TT_DATA_DESC(optional)

* | [--->] IT_DDIC_FLD                    TYPE        DDFIELDS(optional)

* | [--->] IV_ALTERNATE_COUNT             TYPE        I(optional)

* | [<-->] CT_ROW                         TYPE        STANDARD TABLE

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD create_row.

FIELD-SYMBOLS:

<lv_data>      TYPE any,

<ls_header>    TYPE any,

<lv_is_string> TYPE c,

<ls_data_desc> TYPE ts_data_desc.



DATA:  ls_cell   TYPE ts_cell_struc.

DATA:  lv_col    TYPE i.

DATA:  lv_col_pos TYPE i.



DATA lt_rows  TYPE STANDARD TABLE OF ts_cell_struc WITH NON-UNIQUE KEY position INITIAL SIZE 1.



IF mv_alternate = abap_false.

IF mv_alternate_count = 0.

mv_alternate = abap_true.

ENDIF.

ELSE.

IF mv_alternate_count = 0.

mv_alternate = abap_false.

ENDIF.

ENDIF.



IF iv_alternate_count NE 0.

mv_alternate_count = mv_alternate_count + 1.

mv_alternate_count = mv_alternate_count MOD iv_alternate_count .

ENDIF.



lv_col = 1.



DO.

ASSIGN COMPONENT sy-index OF STRUCTURE is_data TO <lv_data>.

IF sy-subrc NE 0.

EXIT.

ENDIF.



CLEAR ls_cell.

ls_cell-style = iv_style.

IF it_ddic_fld IS NOT INITIAL.

READ TABLE it_ddic_fld ASSIGNING <ls_header> INDEX sy-index.

IF <ls_header> IS ASSIGNED.

ASSIGN COMPONENT 'INTTYPE' OF STRUCTURE <ls_header> TO <lv_is_string>.

IF <lv_is_string> IS ASSIGNED AND <lv_is_string> EQ 'C'.

ls_cell-index = get_ss_position( iv_value = <lv_data> ).

ls_cell-sharedstring = 's'.

ENDIF.

ENDIF.

ELSEIF it_data_descr IS NOT INITIAL.

READ TABLE it_data_descr ASSIGNING <ls_data_desc> INDEX sy-index.

IF sy-subrc IS INITIAL AND <ls_data_desc>-inttype EQ 'C'.

ls_cell-index = get_ss_position( iv_value = <lv_data> ).

ls_cell-sharedstring = 's'.



ENDIF.

ENDIF.

CLEAR lv_col_pos.

READ TABLE it_data_descr ASSIGNING <ls_data_desc> INDEX sy-index.

IF <ls_data_desc> IS ASSIGNED.

IF <ls_data_desc>-exclude = abap_true.

UNASSIGN <ls_data_desc>.

CONTINUE.

ENDIF.



IF <ls_data_desc>-lock_col EQ abap_true.



IF <ls_data_desc>-style NE 0.



IF <ls_data_desc>-alternate_style IS NOT INITIAL.

IF mv_alternate = abap_true.

ls_cell-style = <ls_data_desc>-style .

ELSE.

ls_cell-style = <ls_data_desc>-alternate_style .

ENDIF.

ENDIF.

ELSE.

ls_cell-style = 0."4.

ENDIF.

ENDIF.



IF <ls_data_desc>-inttype EQ cl_abap_typedescr=>typekind_int OR

<ls_data_desc>-inttype EQ cl_abap_typedescr=>typekind_int2 OR

<ls_data_desc>-inttype EQ cl_abap_typedescr=>typekind_int8.

*      number format only to be implemented if crucial

ENDIF.

IF <ls_data_desc>-col_pos IS NOT INITIAL.

lv_col_pos = <ls_data_desc>-col_pos.



ENDIF.



UNASSIGN <ls_data_desc>.

ENDIF.

IF ls_cell-index IS INITIAL.

ls_cell-value = <lv_data>.

ENDIF.



IF lv_col_pos IS NOT INITIAL.

ls_cell-position = get_cell_position(

iv_row      = iv_row

iv_col      = lv_col_pos ).

ls_cell-column_index = lv_col_pos.

APPEND ls_cell  TO lt_rows.

ELSE.

ls_cell-position = get_cell_position(

iv_row      = iv_row

iv_col      = lv_col ).

APPEND ls_cell  TO ct_row.

ENDIF.



lv_col = lv_col + 1.

ENDDO.

IF lt_rows IS NOT INITIAL.

SORT lt_rows BY column_index ASCENDING.

APPEND LINES OF lt_rows TO ct_row.

ELSE.

ct_row = ct_row.

ENDIF.



ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Public Method ZTEST_ADPATER->CREATE_SHEET

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_SHEET_NAME                  TYPE        STRING(optional)

* | [--->] IV_STATE                       TYPE        STRING(optional)

* | [--->] IV_DISP_HEADER                 TYPE        BOOLE_D (default =ABAP_TRUE)

* | [--->] IT_TABLE_DATA                  TYPE        STANDARD TABLE

* | [--->] IT_HEADER                      TYPE        TT_HEADER_TABLE(optional)

* | [--->] IT_DATA_DESCR                  TYPE        TT_DATA_DESC(optional)

* | [--->] IT_DESC                        TYPE        TT_DESCR_TABLE(optional)

* | [--->] IT_LIST_CONFIG_FOR_HEADER      TYPE        TT_LIST_CONFIG_FOR_HEADER(optional)

* | [--->] IT_MERGELIST                   TYPE        TT_MERGELIST(optional)

* | [--->] IV_ALTERNATE_COUNT             TYPE        I(optional)

* | [--->] IV_LOCK_ALL_CELLS              TYPE        BOOLEAN (default =ABAP_FALSE)

* | [--->] IV_DEFAULT_WIDTH               TYPE        I (default =15)

* | [--->] IT_HYPERLINK                   TYPE        TT_HYPERLINK(optional)

* | [--->] IV_PROTECTED                   TYPE        BOOLE_D (default =ABAP_FALSE)

* | [EXC!] NAME_ALREADY_EXIST

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD create_sheet.

*----------------------------------------------------------------------*

* TITLE   : Wholesale Contract Management

*----------------------------------------------------------------------*

* Authors : SAP SE

*----------------------------------------------------------------------*

* Program/Method Description :                                         *

* This method will create sheet from provided internal table data and

*    header info                    *

*----------------------------------------------------------------------*

DATA:

lv_row         TYPE i,

lv_ddic        TYPE boole_d,

ls_sheet       TYPE ts_sheets_struc,

lv_mergespan   TYPE ts_ref_struc,

lt_field_list  TYPE ddfields,

lt_header      TYPE tt_header_table,

lr_header      TYPE REF TO ts_header_struc,



lr_typedescr   TYPE REF TO cl_abap_typedescr,

lr_structdescr TYPE REF TO cl_abap_structdescr,

ls_data_descr  LIKE LINE OF it_data_descr,

ls_rows        TYPE ts_row_struc,

ls_mergelist   LIKE LINE OF it_mergelist,

lv_prev_index  TYPE i VALUE -1,

lv_col_index   TYPE i,

lt_cells       TYPE STANDARD TABLE OF ts_cell_struc WITH NON-UNIQUE KEY position INITIAL SIZE 1.



DATA:

lv_span_i   TYPE i,

lv_span     TYPE string,

ls_col_info TYPE ts_col_struc.

DATA:

lv_dimension TYPE i,

ls_meta_data TYPE ts_meta_data.



FIELD-SYMBOLS:

<fs_t_row>      TYPE ts_col_struc,

<lv_temp>       TYPE any,

<ls_table_data> TYPE any,

*         <lt_data_descr>  TYPE ANY TABLE,

<lt_header>     TYPE ANY TABLE.



*check if table data exist

READ TABLE it_table_data ASSIGNING <ls_table_data> INDEX 1.

IF <ls_table_data> IS ASSIGNED.

lr_typedescr = cl_abap_tabledescr=>describe_by_data( p_data = <ls_table_data> ).

MOVE: lr_typedescr ?TO lr_structdescr.

lr_structdescr->get_ddic_field_list( EXPORTING  p_langu                  = syst-langu

p_including_substructres = abap_false

RECEIVING  p_field_list             = lt_field_list

EXCEPTIONS OTHERS                   = 0 ).



IF lt_field_list IS INITIAL.

DATA(lt_comp) = lr_structdescr->components[].

LOOP AT lt_comp ASSIGNING FIELD-SYMBOL(<ls_comp>).

INSERT INITIAL LINE INTO TABLE lt_field_list ASSIGNING FIELD-SYMBOL(<ls_field_list>).

<ls_field_list>-fieldname = <ls_comp>-name.

<ls_field_list>-inttype = <ls_comp>-type_kind.

ENDLOOP.

ENDIF.



ENDIF.



*  assign name to the sheet

ADD 1 TO mv_sheet_no.

ls_sheet-sheetid = mv_sheet_no.

IF iv_sheet_name IS INITIAL.

ls_sheet-name = mv_sheet_no.

CONCATENATE co_sheet ls_sheet-name INTO ls_sheet-name.

ELSE.

READ TABLE mt_sheets TRANSPORTING NO FIELDS WITH KEY name = iv_sheet_name.

IF sy-subrc IS NOT INITIAL.

ls_sheet-name = iv_sheet_name.

ELSE.

*      RAISE exception "Name already exist".

RAISE name_already_exist.

ENDIF.

ENDIF.



ls_meta_data-sheetno = ls_sheet-sheetid.  "assign meta data



*  calculate span

DO.

ASSIGN COMPONENT sy-index OF STRUCTURE <ls_table_data> TO <lv_temp>.

IF sy-subrc IS NOT INITIAL.

EXIT.

ENDIF.

ADD 1 TO lv_span_i.

ENDDO.



CALL METHOD get_width_for_cols

EXPORTING

iv_width        = iv_default_width

it_header_table = it_header

it_data_descr   = it_data_descr

IMPORTING

et_col          = ls_sheet-sheet-t_cols.

.

* get_hyperlinks

ls_sheet-sheet-t_hyperlinks = it_hyperlink.



lv_span = lv_span_i.

CONCATENATE '1:' lv_span INTO lv_span.



*  create the description for the sheet

IF it_desc IS NOT INITIAL.

ls_meta_data-dscr_start = lv_row + 1.

create_description(

EXPORTING

iv_row   = lv_row

it_decs  = it_desc

IMPORTING

ev_row   = lv_row

CHANGING

cs_sheet = ls_sheet-sheet

).

ls_meta_data-dscr_stop = lv_row.



ENDIF.



*  create headers for the table

IF iv_disp_header EQ abap_true.

IF it_header IS INITIAL.

ASSIGN lt_field_list TO <lt_header>.

lv_ddic = abap_true.

ELSE.

lt_header = it_header.

SORT lt_header STABLE BY row_index .

ASSIGN lt_header TO <lt_header>.

lv_ddic = abap_false.

ENDIF.

ADD 1 TO lv_row.

ls_meta_data-header_start = lv_row.

create_header(

EXPORTING

iv_row        = lv_row

iv_ddic_struc = lv_ddic   " Data element for domain BOOLE: TRUE (='X') and FALSE (=' ')

it_header     = <lt_header>

IMPORTING

ev_row        = lv_row

CHANGING

cs_sheet      = ls_sheet-sheet

).

ls_meta_data-header_stop = lv_row.

ENDIF.



*  modify the data to excel format

ls_meta_data-data_start = lv_row + 1.

LOOP AT it_table_data ASSIGNING <ls_table_data>.

ADD 1 TO lv_row.

CLEAR:ls_rows, lt_cells.

create_row(

EXPORTING

iv_row        = lv_row

iv_style      = 0"7

is_data       = <ls_table_data>

it_data_descr = it_data_descr

it_ddic_fld   = lt_field_list

iv_alternate_count = iv_alternate_count

CHANGING

ct_row        = lt_cells

).

ls_rows-position  = lv_row.

ls_rows-spans     = lv_span.

ls_rows-t_cells   = lt_cells.

INSERT ls_rows INTO TABLE ls_sheet-sheet-t_rows.

ENDLOOP.

ls_meta_data-data_stop = lv_row.

INSERT ls_meta_data INTO TABLE mt_meta_data.

*  gen the dimension of the sheet

DESCRIBE TABLE ls_sheet-sheet-t_rows LINES lv_dimension.

ls_sheet-sheet-dim = get_cell_position(

iv_row      = lv_dimension

iv_col      = lv_span_i  ).

CONCATENATE 'A1:' ls_sheet-sheet-dim INTO ls_sheet-sheet-dim.



ls_sheet-state  =  iv_state.



LOOP AT ls_sheet-sheet-t_cols ASSIGNING <fs_t_row> .

IF iv_lock_all_cells  EQ abap_true.

<fs_t_row>-style = 18.

ENDIF.



READ TABLE it_data_descr INTO ls_data_descr INDEX sy-tabix.

IF sy-subrc EQ 0.

IF ls_data_descr-lock_for_sheet EQ abap_true.

<fs_t_row>-style = 22.

ENDIF.

IF ls_data_descr-hidden EQ abap_true.

<fs_t_row>-hidden = 1.

ELSE.

<fs_t_row>-hidden = 0.

ENDIF.

IF ls_data_descr-style IS NOT INITIAL.

<fs_t_row>-style = ls_data_descr-style.

ENDIF.

ENDIF.



IF ls_data_descr-outline EQ 1.

<fs_t_row>-outline = 0."1.

ELSE.

<fs_t_row>-outline = 0.

ENDIF.



ENDLOOP.



" Fill col index

LOOP AT lt_header REFERENCE INTO lr_header.

IF lv_prev_index NE lr_header->row_index.

lv_prev_index = lr_header->row_index .

lv_col_index = 1.

ELSE.

lv_col_index = lv_col_index + 1.

ENDIF.

lr_header->col_index = lv_col_index .

ENDLOOP.



CALL METHOD prepare_drop_down_list

EXPORTING

it_header                 = lt_header

it_data_descr             = it_data_descr

it_list_config_for_header = it_list_config_for_header

iv_data_stop              = ls_meta_data-data_stop

CHANGING

cv_dropdown_count         = ls_sheet-sheet-dropdown_count

ct_dropdown_formula       = ls_sheet-sheet-t_dropdown_formula.



*MERGE CELL

CLEAR     ls_sheet-sheet-s_merge .

DESCRIBE TABLE it_mergelist LINES ls_sheet-sheet-s_merge-count .

LOOP AT it_mergelist INTO ls_mergelist.

lv_mergespan-span = ls_mergelist-from && ':' && ls_mergelist-to .

APPEND lv_mergespan TO ls_sheet-sheet-s_merge-t_ref .

ENDLOOP.



IF iv_protected EQ abap_true.

ls_sheet-sheet-protected = abap_true.

ENDIF.



*  add to the sheets table

INSERT ls_sheet INTO TABLE mt_sheets.



ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->CREATE_STYLE_SHEET

* +-------------------------------------------------------------------------------------------------+

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD create_style_sheet.

*  creating the different styles

DATA:

ls_cell_fx TYPE ts_style_cellxf,

lt_cell_fx TYPE STANDARD TABLE OF ts_style_cellxf WITH KEY key indent xfid wrap INITIAL SIZE 1.



* style for columns unlock all cells---0

CLEAR ls_cell_fx.

ls_cell_fx-index = 0.

ls_cell_fx-wrap  = 1.

ls_cell_fx-protectionid = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style for description, locked----1

CLEAR ls_cell_fx.

ls_cell_fx-index = 1.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 5.

ls_cell_fx-borderid = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style for description, unlocked----2

CLEAR ls_cell_fx.

ls_cell_fx-index = 2.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 5.

ls_cell_fx-borderid = 1.

ls_cell_fx-protectionid = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style for header, color 1 without border----3

CLEAR ls_cell_fx.

ls_cell_fx-index = 3.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 6.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style for header, color 1 with border----4

CLEAR ls_cell_fx.

ls_cell_fx-index = 4.

ls_cell_fx-numfmtid = 49 .

ls_cell_fx-wrap  = 1.

ls_cell_fx-borderid = 1.

ls_cell_fx-fillid = 6.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style for header, color 2----5

CLEAR ls_cell_fx.

ls_cell_fx-index = 5.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 2.

ls_cell_fx-borderid = 1.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style for header, color 2 without boarder----6

CLEAR ls_cell_fx.

ls_cell_fx-index = 6.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 2.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style for data, unlocked cell----7

CLEAR ls_cell_fx.

ls_cell_fx-index = 7.

ls_cell_fx-wrap  = 1.

ls_cell_fx-borderid = 1.

ls_cell_fx-protectionid = 1.

APPEND ls_cell_fx TO lt_cell_fx.



CLEAR ls_cell_fx.

ls_cell_fx-index = 8.

ls_cell_fx-numfmtid = 49 .

ls_cell_fx-wrap  = 0.

ls_cell_fx-protectionid = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style for header, color 3----9

CLEAR ls_cell_fx.

ls_cell_fx-index = 9.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 8.

ls_cell_fx-borderid = 1.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style for header, color 3 without boarder----10

CLEAR ls_cell_fx.

ls_cell_fx-index = 10.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 8.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.

*  style for header, color 4 ------------11

CLEAR ls_cell_fx.

ls_cell_fx-index = 11.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 7.

ls_cell_fx-borderid = 1.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.

*  style for header, color 4 without boarder----12

CLEAR ls_cell_fx.

ls_cell_fx-index = 12.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 7.

ls_cell_fx-borderid = 1.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style for header, color 5 ------------13

CLEAR ls_cell_fx.

ls_cell_fx-index = 13.

ls_cell_fx-xfid = 2 .

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 9.

ls_cell_fx-borderid = 1.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style for header, color 5 without boarder----14

CLEAR ls_cell_fx.

ls_cell_fx-index = 14.

ls_cell_fx-xfid = 2.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 9.

ls_cell_fx-borderid = 1.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style for description   - 15

CLEAR ls_cell_fx.

ls_cell_fx-index = 15.

ls_cell_fx-xfid = 3 .

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 10.

ls_cell_fx-borderid = 1.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.



**  style - 16

CLEAR ls_cell_fx.

ls_cell_fx-index = 16.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 11.

ls_cell_fx-borderid = 1.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.



**  style - 17 :white locked background

CLEAR ls_cell_fx.

ls_cell_fx-index = 17.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 12.

ls_cell_fx-borderid = 1.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.



**  style - 18

CLEAR ls_cell_fx.

ls_cell_fx-index = 18.

ls_cell_fx-wrap  = 1.

ls_cell_fx-protectionid = 0 .

APPEND ls_cell_fx TO lt_cell_fx.



**  style - 19

CLEAR ls_cell_fx.

ls_cell_fx-index = 19.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 13.

ls_cell_fx-borderid = 1.

ls_cell_fx-protectionid = 0 .

APPEND ls_cell_fx TO lt_cell_fx.



**  style - 20

CLEAR ls_cell_fx.

ls_cell_fx-index = 20.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 14.

ls_cell_fx-borderid = 1.

ls_cell_fx-protectionid = 0 .

APPEND ls_cell_fx TO lt_cell_fx.



**  style - 21 :white unlocked background

CLEAR ls_cell_fx.

ls_cell_fx-index = 21.

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 12.

ls_cell_fx-borderid = 1.

ls_cell_fx-protectionid = 1 .

APPEND ls_cell_fx TO lt_cell_fx.



*  style  22 for header, color 1 with border----3

CLEAR ls_cell_fx.

ls_cell_fx-index = 22.

*    ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 0.

ls_cell_fx-borderid = 1.

ls_cell_fx-fontid = 2.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style  23 for header, color 1 with border----3

CLEAR ls_cell_fx.

ls_cell_fx-index = 15.

ls_cell_fx-xfid = 1 .

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 10.

ls_cell_fx-borderid = 1.

ls_cell_fx-is_string = 1.

APPEND ls_cell_fx TO lt_cell_fx.



*  style  24 for header, color 1 with border----3

CLEAR ls_cell_fx.

ls_cell_fx-index = 24.

ls_cell_fx-numfmtid = 49 .

ls_cell_fx-wrap  = 1.

ls_cell_fx-fillid = 13.

ls_cell_fx-borderid = 1.

ls_cell_fx-protectionid = 0 .

APPEND ls_cell_fx TO lt_cell_fx.



ms_style-cellxfs_count  = ls_cell_fx-index + 1.

ms_style-t_cellxfs      = lt_cell_fx.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Public Method ZTEST_ADPATER->DOWNLOAD

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_EXCEL_NAME                  TYPE        STRING(optional)

* | [EXC!] DOWNLOAD_FAILED

* | [EXC!] DOWNLOAD_CANCELLED

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD download.

DATA:

l_length      TYPE i,

l_title       TYPE string,

l_filename    TYPE string,

*       l_appl_para    TYPE string,

l_xml_stream  TYPE xml_rawdata,

*       s_title        TYPE string,

s_loc_fn      TYPE string,

s_loc_dir     TYPE string,

l_user_action TYPE i,

l_xml         TYPE xstring,

lv_excel_name TYPE string.



IF iv_excel_name IS SUPPLIED.

lv_excel_name = iv_excel_name.

ELSE.

lv_excel_name = TEXT-t02.

ENDIF.



l_title = 'Export to Excel 2007'(t01).



CALL METHOD cl_gui_frontend_services=>file_save_dialog

EXPORTING

window_title         = l_title

default_extension    = 'xlsx'

default_file_name    = lv_excel_name                  "#EC NOTEXT

file_filter          = '*.xlsx'

CHANGING

filename             = s_loc_fn

path                 = s_loc_dir

fullpath             = s_loc_dir

user_action          = l_user_action

EXCEPTIONS

cntl_error           = 1

error_no_gui         = 2

not_supported_by_gui = 3

OTHERS               = 4.



IF sy-subrc <> 0.

MESSAGE e162(alvht).

EXIT.

ENDIF.



IF l_user_action = cl_gui_frontend_services=>action_cancel .

MESSAGE s161(alvht).

RAISE download_cancelled .

ENDIF.



CONCATENATE s_loc_dir s_loc_fn INTO l_filename.



l_xml = prepare_for_download( ).



IF NOT l_filename IS INITIAL.

CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'

EXPORTING

buffer        = l_xml

IMPORTING

output_length = l_length

TABLES

binary_tab    = l_xml_stream.



CALL METHOD cl_gui_frontend_services=>gui_download

EXPORTING

bin_filesize = l_length

filetype     = 'BIN'

filename     = l_filename

CHANGING

data_tab     = l_xml_stream

EXCEPTIONS

OTHERS       = 1.

IF sy-subrc <> 0.

RAISE  download_failed.

ENDIF.

ENDIF.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Public Method ZTEST_ADPATER->FILL_DECIMAL_FORMAT

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_DCPM                        TYPE        USR01-DCPFM

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD fill_decimal_format.                                                                                               "start of note 2437206



"Fill the decimal format from user settings.

mv_dcpfm = iv_dcpm.



ENDMETHOD.                                                                                                                "end of note 2437206



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->GET_CELL_INDEX

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_CELL                        TYPE        STRING

* | [<---] EV_ROW                         TYPE        I

* | [<---] EV_COL                         TYPE        I

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD get_cell_index.

DATA:

lv_tabix_cell_index TYPE i,

lv_diff             TYPE i,

lv_char             TYPE char1,

lv_len              TYPE i,

lv_col_index        TYPE string.



*  Step 1: Get the row and coln seperated

lv_len  = strlen( iv_cell ).



WHILE lv_char CO co_char OR lv_char IS INITIAL .

lv_tabix_cell_index = lv_tabix_cell_index + 1.

lv_char = iv_cell+lv_tabix_cell_index(1) .

ENDWHILE .



lv_diff = lv_len - lv_tabix_cell_index .

lv_col_index  = iv_cell(lv_tabix_cell_index) .

ev_row = iv_cell+lv_tabix_cell_index(lv_diff) .



IF ev_col IS REQUESTED .

CALL METHOD get_col_index

EXPORTING

iv_col_index = lv_col_index

IMPORTING

ev_col       = ev_col.

ENDIF.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->GET_CELL_POSITION

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_ROW                         TYPE        I

* | [--->] IV_COL                         TYPE        I

* | [<-()] RV_POSITION                    TYPE        STRING

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD get_cell_position.

DATA: l_part1 TYPE string,

l_part2 TYPE string,

l_part3 TYPE string,

l_mod   TYPE i,

l_div   TYPE i.



l_mod = ( iv_col - 1 ) MOD 26.

l_div = ( iv_col - 1 ) DIV 26.



l_part1 = co_char+l_mod(1).

l_part3 = |{ iv_row }|.



IF l_div > 0.

l_div = l_div - 1.

l_part2 = co_char+l_div(1).

CONCATENATE l_part2 l_part1 l_part3 INTO rv_position.

ELSE.

CONCATENATE l_part1 l_part3 INTO rv_position.

ENDIF.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->GET_COL_INDEX

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_COL_INDEX                   TYPE        STRING

* | [<---] EV_COL                         TYPE        I

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD get_col_index.



DATA:

lv_char             TYPE char1,

lv_len              TYPE i,

lv_len_minus_1      TYPE i,

lv_partial_index1   TYPE i,

lv_partial_index2   TYPE i,

lv_number           TYPE i,

result_tab          TYPE match_result_tab,

lv_col_index_substr TYPE string,

lv_result           TYPE i.



FIELD-SYMBOLS:

<match> LIKE LINE OF result_tab.



lv_len = strlen( iv_col_index ) .

lv_char = iv_col_index(1).



FIND FIRST OCCURRENCE OF lv_char IN co_char RESULTS result_tab.



READ TABLE result_tab ASSIGNING <match> INDEX 1.

lv_number = <match>-offset .

lv_number = lv_number + 1 .



IF lv_len EQ 1.

ev_col = ( ( 26 ** ( lv_len - 1 ) ) * lv_number )  .

ELSE.

lv_len_minus_1 = lv_len - 1.

lv_col_index_substr = iv_col_index+1(lv_len_minus_1) .

CALL METHOD get_col_index

EXPORTING

iv_col_index = lv_col_index_substr

IMPORTING

ev_col       = lv_partial_index2.



lv_partial_index1 = ( ( 26 ** ( lv_len - 1 ) ) * lv_number ) + lv_partial_index2 .

ev_col =  lv_partial_index1 .



ENDIF.



ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->GET_COL_LABEL

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_COL                         TYPE        I

* | [<---] EV_COL                         TYPE        STRING

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD get_col_label.



DATA:

lv_col TYPE i,

lv_max TYPE i,

lv_res TYPE p DECIMALS 5,

lv_rem TYPE i.



CONSTANTS:lv_alp TYPE string VALUE 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.



DATA:

lv_l(1) TYPE c,

lv_str  TYPE string.



lv_col = lv_max = iv_col.



DO.

lv_res = lv_col / 26.

lv_rem = lv_col MOD 26.

lv_res = floor( lv_res ).

IF lv_rem = 0.

CONCATENATE 'Z' lv_str  INTO lv_str.

ELSE.

lv_l = substring( val = lv_alp off = lv_rem - 1 len = 1 ).

CONCATENATE lv_l lv_str INTO lv_str.

ENDIF.

*  NEW-LINE.

IF lv_res = 0 OR lv_max <= 26.

EXIT.

ENDIF.

lv_col = lv_res.

ENDDO.



ev_col = lv_str.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->GET_DATA_FROM_XML

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_XML                         TYPE        XSTRING

* | [--->] IV_NODE                        TYPE        STRING

* | [<---] ET_DATA                        TYPE        DATA

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD get_data_from_xml.

DATA:

lv_test    TYPE string .

DATA:

lo_ixml_factory   TYPE REF TO if_ixml,

lo_stream_factory TYPE REF TO if_ixml_stream_factory,

lo_istream        TYPE REF TO if_ixml_istream,

lo_idocument      TYPE REF TO if_ixml_document,

lo_iparser        TYPE REF TO if_ixml_parser.



DATA: lo_node_collection TYPE REF TO if_ixml_node_collection,

lo_node_iterator   TYPE REF TO if_ixml_node_iterator,

lo_node            TYPE REF TO if_ixml_node.



DATA:

*        lv_name   TYPE string,

*        lv_value  TYPE string,

lo_attr   TYPE REF TO if_ixml_named_node_map.



DATA:

lv_no_of_entries      TYPE        i,

lr_subnode            TYPE REF TO if_ixml_node,

lr_attr_node          TYPE REF TO if_ixml_node,

lr_node_list          TYPE REF TO if_ixml_node_list,

lr_node_list_iterator TYPE REF TO if_ixml_node_iterator.



DATA:

lv_no_of_col         TYPE i,

lr_col_node          TYPE REF TO if_ixml_node,

lr_col_list          TYPE REF TO if_ixml_node_list,

lr_col_list_iterator TYPE REF TO if_ixml_node_iterator.



DATA:

ls_sheet  TYPE ts_sheets_struc,

ls_sh_str TYPE ts_sharedstring,

lt_row    TYPE STANDARD TABLE OF ts_row_struc INITIAL SIZE 1,

lt_cell   TYPE STANDARD TABLE OF ts_cell_struc INITIAL SIZE 1.



FIELD-SYMBOLS:

<ls_row>  TYPE ts_row_struc,

<ls_cell> TYPE ts_cell_struc.

*        <lv_value>  TYPE any.



* Create Main Factory

* creates an instance of the iXML class and returns an interface pointer to the instance.

lo_ixml_factory = cl_ixml=>create( ).

* Next Create Stream Factory

* creates a new StreamFactory instance and returns an interface pointer to this instance

lo_stream_factory = lo_ixml_factory->create_stream_factory( ).

* Create Input Stream

* creates a new XML input stream for the given ABAP xstring

lo_istream = lo_stream_factory->create_istream_xstring( iv_xml ).      "Where 'xml' is the input XML xstring.

* Initialize Input Document

* creates a new Document instance and returns an interface pointer to this instance.

lo_idocument = lo_ixml_factory->create_document( ).

* creates a new Parser instance and returns an interface pointer to this instance.

lo_iparser = lo_ixml_factory->create_parser(

stream_factory = lo_stream_factory

istream        = lo_istream

document       = lo_idocument

).

* implements the DOM-generating interface to the parser

lo_iparser->parse( ).

* Returns an iXMLNodeCollection of all the elements with a given tag name

* in the order in which they would be encountered in a preorder

* traversal of the document tree.

lo_node_collection        = lo_idocument->get_elements_by_tag_name( name = iv_node ).

lo_node_iterator          = lo_node_collection->create_iterator( ).

lo_node                   = lo_node_iterator->get_next( ).



lr_node_list              = lo_node->get_children( ).

lv_no_of_entries          = lr_node_list->get_length( ).

lr_node_list_iterator     = lr_node_list->create_iterator( ).



DO lv_no_of_entries TIMES.

CASE iv_node.

WHEN co_sheet_data.

lr_subnode            = lr_node_list_iterator->get_next( ).

lr_col_list           = lr_subnode->get_children( ).

lv_no_of_col          = lr_col_list->get_length( ).

lr_col_list_iterator  = lr_col_list->create_iterator( ).

INSERT INITIAL LINE INTO TABLE lt_row ASSIGNING <ls_row>.

<ls_row>-position = sy-index.

CLEAR lt_cell.

DO lv_no_of_col TIMES.

lr_col_node   = lr_col_list_iterator->get_next( ).

lo_attr       = lr_col_node->get_attributes( ).

INSERT INITIAL LINE INTO TABLE lt_cell ASSIGNING <ls_cell>.

lr_attr_node  = lo_attr->get_named_item( name = 't' ).

IF lr_attr_node IS INITIAL.                           " shared string doesn`t exist

<ls_cell>-value = lr_col_node->get_value( ).

ELSE.

TRY.

<ls_cell>-index = lr_col_node->get_value( ) + 1.

CLEAR lr_attr_node.

CATCH cx_sy_conversion_no_number.

ENDTRY.

ENDIF.

lr_attr_node  = lo_attr->get_named_item( name = 'r' ).

<ls_cell>-cell = lr_attr_node->get_value( ) .

ENDDO.



<ls_row>-t_cells = lt_cell.

WHEN co_shared_string.

lr_subnode        = lr_node_list_iterator->get_next( ).

ls_sh_str-value = lr_subnode->get_value( ).

ls_sh_str-pos   = sy-index.

INSERT ls_sh_str INTO TABLE mt_shdrstr_upload-t_strings.

WHEN co_workbook.

lr_subnode        = lr_node_list_iterator->get_next( ).

lo_attr           = lr_subnode->get_attributes( ).

lr_attr_node      = lo_attr->get_named_item( name = 'name'  ). "#EC NOTEXT "excel attribute name

ls_sheet-name     = lr_attr_node->get_value( ).

lr_attr_node      = lo_attr->get_named_item( name = 'sheetId'  ). "#EC NOTEXT "execl attribute sheetId

ls_sheet-sheetid  = lr_attr_node->get_value( ).

lr_attr_node      = lo_attr->get_named_item( name = 'id' namespace = 'r'  ). "#EC NOTEXT "excel attribute id namespace r

ls_sheet-relid    = lr_attr_node->get_value( ).

INSERT ls_sheet INTO TABLE mt_sheets.

ENDCASE.

ENDDO.

IF lt_row IS NOT INITIAL.

et_data = lt_row.

ENDIF.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Public Method ZTEST_ADPATER->GET_EXCEL_STREAM

* +-------------------------------------------------------------------------------------------------+

* | [<---] EV_XML                         TYPE        XSTRING

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD get_excel_stream.

TRY.

ev_xml = mo_xlsx_doc->get_package_data( ).

CATCH cx_openxml_format cx_openxml_not_found cx_openxml_not_allowed.

ENDTRY.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->GET_SS_POSITION

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_VALUE                       TYPE        DATA

* | [<-()] RV_INDEX                       TYPE        I

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD get_ss_position.

DATA: ls_sh_str TYPE ts_sharedstring.

READ TABLE ms_sharedstring-t_strings INTO ls_sh_str WITH TABLE KEY value = iv_value.

IF sy-subrc IS NOT INITIAL.

ADD 1 TO ms_sharedstring-string_ucount.

ls_sh_str-value = iv_value.

ls_sh_str-pos   = ms_sharedstring-string_ucount.

INSERT ls_sh_str INTO TABLE ms_sharedstring-t_strings.

ENDIF.

ADD 1 TO ms_sharedstring-string_count.

rv_index = ls_sh_str-pos - 1.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Public Method ZTEST_ADPATER->GET_UPLOADED_SHEET_NAMES

* +-------------------------------------------------------------------------------------------------+

* | [<---] ET_SHEETS                      TYPE        TT_SHEET_NAME

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD get_uploaded_sheet_names.

FIELD-SYMBOLS:

<ls_sheet>      TYPE ts_sheets_struc,

<ls_sheet_name> TYPE ts_sheet_name.



LOOP AT mt_sheets ASSIGNING <ls_sheet> WHERE name NE co_meta_data.

INSERT INITIAL LINE INTO TABLE et_sheets ASSIGNING <ls_sheet_name>.

<ls_sheet_name>-sheet_name = <ls_sheet>-name.

ENDLOOP.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->GET_WIDTH_FOR_COLS

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_WIDTH                       TYPE        I (default =15)

* | [--->] IT_HEADER_TABLE                TYPE        TT_HEADER_TABLE

* | [--->] IT_DATA_DESCR                  TYPE        TT_DATA_DESC(optional)

* | [<---] ET_COL                         TYPE        TT_COL_STRUC

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD get_width_for_cols.



TYPES:

BEGIN OF ts_col_length,

length     TYPE int4,

fix_length TYPE boole_d,

END OF ts_col_length.



TYPES:

tt_col_length TYPE TABLE OF ts_col_length .



DATA: ls_col         LIKE LINE OF et_col,

lv_counter     TYPE i,

lt_col_length  TYPE  tt_col_length,

ls_col_length  TYPE  ts_col_length,

lv_header_len  TYPE i,

lv_lines_prev  TYPE i,

lv_lines_next  TYPE i,

lv_len_prev    TYPE i,

lv_len_next    TYPE i,

lv_len_max     TYPE i,

lv_len_final   TYPE i,

lv_lines_max   TYPE i,

lv_col_index   TYPE i,

lv_last_index  TYPE i,

ls_header      LIKE LINE OF it_header_table,

lt_header_prev TYPE tt_header_table,

ls_header_prev LIKE LINE OF lt_header_prev,

lt_header_next TYPE tt_header_table,

lv_col_len     TYPE i,

ls_header_next LIKE LINE OF lt_header_next,

ls_data_descr  LIKE LINE OF it_data_descr.



FIELD-SYMBOLS:

<fs_col_length>   LIKE LINE OF  lt_col_length .



DESCRIBE TABLE it_header_table LINES lv_header_len.

READ TABLE it_header_table INTO ls_header INDEX 1 .

lv_col_index = ls_header-row_index .



READ TABLE it_header_table INTO ls_header INDEX lv_header_len .

lv_last_index = ls_header-row_index .

"If there is no entry

IF it_header_table IS INITIAL.

ls_col-customwidth = 1.

ls_col-bestfit     = 1.

ls_col-width       = iv_width.

ls_col-min         = 1.

ls_col-max         = 16384.

ls_col-style       = 0.

INSERT ls_col INTO TABLE et_col.

RETURN .

ENDIF.



WHILE lt_header_prev IS INITIAL.



LOOP AT it_header_table INTO ls_header WHERE row_index = lv_col_index.

APPEND ls_header TO lt_header_prev .

ENDLOOP .



ENDWHILE .



"If there is only one row

IF lv_col_index EQ lv_last_index.



DESCRIBE TABLE lt_header_prev LINES lv_lines_prev.

DO lv_lines_prev TIMES.

lv_counter = lv_counter + 1.

READ TABLE lt_header_prev INDEX lv_counter INTO ls_header_prev .

READ TABLE it_header_table INTO ls_header INDEX lv_counter .

READ TABLE it_data_descr INTO ls_data_descr INDEX lv_counter.

IF ls_data_descr-col_width IS NOT  INITIAL.

lv_len_prev = ls_data_descr-col_width.

ELSE.

lv_len_prev = strlen( ls_header_prev-field_name ) .

ENDIF.

ls_col_length-length = lv_len_prev .

ls_col_length-fix_length = ls_header-fix_length .

APPEND ls_col_length TO lt_col_length .

ENDDO.



ENDIF.



WHILE lv_col_index LT lv_last_index .



lv_col_index = lv_col_index + 1.

WHILE lt_header_next IS INITIAL.

LOOP AT it_header_table INTO ls_header WHERE row_index = lv_col_index.

APPEND ls_header TO lt_header_next .

ENDLOOP .



ENDWHILE .



DESCRIBE TABLE lt_header_prev LINES lv_lines_prev.

DESCRIBE TABLE lt_header_next LINES lv_lines_next.



IF lv_lines_prev > lv_lines_next .

lv_lines_max = lv_lines_prev.

ELSE.

lv_lines_max = lv_lines_next.

ENDIF.



DO lv_lines_max TIMES.

lv_counter = lv_counter + 1.

IF lv_lines_prev GT lv_counter AND lv_lines_next GT lv_counter.

READ TABLE lt_header_prev INDEX lv_counter INTO ls_header_prev .

READ TABLE lt_header_next INDEX lv_counter INTO ls_header_next .



lv_len_prev = strlen( ls_header_prev-field_name ) .

lv_len_next = strlen( ls_header_next-field_name ) .



IF lv_len_prev > lv_len_next .

lv_len_max = lv_len_prev.

ELSE.

lv_len_max = lv_len_next.

ENDIF.

ELSEIF lv_lines_prev GT lv_counter AND lv_lines_next LE lv_counter .

lv_len_prev = strlen( ls_header_prev-field_name ) .

lv_len_max = lv_len_prev.

ELSEIF lv_lines_prev LE lv_counter AND lv_lines_next GT lv_counter .

lv_len_next = strlen( ls_header_next-field_name ) .

lv_len_max = lv_len_next.

ENDIF.

READ TABLE lt_col_length ASSIGNING <fs_col_length> INDEX lv_counter .

IF sy-subrc EQ 0.

IF <fs_col_length>-length LT lv_len_max.

<fs_col_length>-length = lv_len_max.

ENDIF.

ELSE.

CLEAR ls_col_length .

ls_col_length-length = lv_len_max .

APPEND ls_col_length TO lt_col_length .

ENDIF.

ENDDO.

CLEAR lv_counter .



CLEAR : lt_header_prev.

lt_header_prev = lt_header_next.

CLEAR : lt_header_next .



ENDWHILE .



LOOP AT lt_col_length INTO ls_col_length .

ls_col-customwidth = 1.

ls_col-bestfit     = 1.

READ TABLE lt_col_length INTO ls_col_length INDEX sy-tabix .

IF ls_col_length-length LT iv_width AND ls_col_length-fix_length EQ abap_false.

ls_col_length-length = iv_width.

ENDIF.

ls_col-width       = ls_col_length-length.

ls_col-min         = sy-tabix.

ls_col-max         = sy-tabix.

ls_col-style       = 0.

APPEND ls_col TO et_col .

ENDLOOP.



ls_col-customwidth = 1.

ls_col-bestfit     = 1.

ls_col-width       = iv_width.

ls_col-min         = sy-tabix + 1 .

ls_col-max         = 16384.

ls_col-style       = 0.

APPEND ls_col TO et_col .



ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->PREPARE_DROP_DOWN_LIST

* +-------------------------------------------------------------------------------------------------+

* | [--->] IT_HEADER                      TYPE        TT_HEADER_TABLE(optional)

* | [--->] IT_DATA_DESCR                  TYPE        TT_DATA_DESC

* | [--->] IT_LIST_CONFIG_FOR_HEADER      TYPE        TT_LIST_CONFIG_FOR_HEADER(optional)

* | [--->] IV_DATA_STOP                   TYPE        I

* | [<-->] CV_DROPDOWN_COUNT              TYPE        I

* | [<-->] CT_DROPDOWN_FORMULA            TYPE        TT_DROP_DOWN_FORMULA

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD prepare_drop_down_list.



DATA:

ls_header                 LIKE LINE OF it_header,

lv_cell_from              TYPE string,

lv_cell_to                TYPE string,

lv_row_from               TYPE string,

lv_row_to                 TYPE string,

lv_col                    TYPE i,

lv_correction             TYPE boolean,

ls_list_config_for_header LIKE LINE OF it_list_config_for_header,

ls_drop_down_formula      LIKE LINE OF ct_dropdown_formula,

ls_data_desc              LIKE LINE OF it_data_descr.



"For desc header

READ TABLE it_header INTO ls_header  INDEX 1.

IF ls_header-row_index = 0.

lv_correction  = abap_true.

ELSE.

lv_correction  = abap_false.

ENDIF.



LOOP AT it_header INTO ls_header WHERE s_drop_down_config IS NOT INITIAL .



lv_row_from = ls_header-s_drop_down_config-source-row_from . "Converting into Char type

lv_cell_from = '$' && ls_header-s_drop_down_config-source-col && '$' && lv_row_from.

CONDENSE lv_cell_from.

lv_row_to = ls_header-s_drop_down_config-source-row_to . "Converting into Char type

lv_cell_to = '$' && ls_header-s_drop_down_config-source-col && '$' && lv_row_to.

CONDENSE lv_cell_to .

CONCATENATE ls_header-s_drop_down_config-source-sheet_name '!' lv_cell_from ':' lv_cell_to

INTO ls_drop_down_formula-formula .

CONDENSE ls_drop_down_formula-formula .



CLEAR : lv_cell_from, lv_cell_to .

IF lv_correction  = abap_true.

CALL METHOD get_cell_position

EXPORTING

iv_row      = ls_header-row_index + 1

iv_col      = ls_header-col_index

RECEIVING

rv_position = lv_cell_from.

ELSE.

CALL METHOD get_cell_position

EXPORTING

iv_row      = ls_header-row_index

iv_col      = ls_header-col_index

RECEIVING

rv_position = lv_cell_from.

ENDIF.



CONCATENATE lv_cell_from ':' lv_cell_from INTO ls_drop_down_formula-cells .

CONDENSE ls_drop_down_formula-cells .



IF  ls_header-s_drop_down_config-restrict_values EQ 'X'.

ls_drop_down_formula-restrict = '1'.

IF ls_header-s_drop_down_config-error_text IS NOT INITIAL.

ls_drop_down_formula-error_text-header = ls_header-s_drop_down_config-error_text-header.

ls_drop_down_formula-error_text-text = ls_header-s_drop_down_config-error_text-text .

ENDIF.

ELSE.

ls_drop_down_formula-restrict = '0'.

ENDIF.



APPEND ls_drop_down_formula TO ct_dropdown_formula .



ENDLOOP.



"For Columns

LOOP AT it_data_descr INTO ls_data_desc .



lv_col = lv_col + 1.



IF ls_data_desc-s_drop_down_config IS NOT INITIAL .



cv_dropdown_count = cv_dropdown_count + 1.

"SOURCE

IF ls_data_desc-s_drop_down_config-source-row_from IS INITIAL OR ls_data_desc-s_drop_down_config-source-row_to IS INITIAL .

CONCATENATE ls_data_desc-s_drop_down_config-source-sheet_name '!$' ls_data_desc-s_drop_down_config-source-col ':$' ls_data_desc-s_drop_down_config-source-col

INTO ls_drop_down_formula-formula.

CONDENSE ls_drop_down_formula-formula .

ELSE.



lv_row_from = ls_data_desc-s_drop_down_config-source-row_from . "Converting into Char type

lv_cell_from = '$' && ls_data_desc-s_drop_down_config-source-col && '$' && lv_row_from.

CONDENSE lv_cell_from.

lv_row_to = ls_data_desc-s_drop_down_config-source-row_to . "Converting into Char type

lv_cell_to = '$' && ls_data_desc-s_drop_down_config-source-col && '$' && lv_row_to.

CONDENSE lv_cell_to .

CONCATENATE ls_data_desc-s_drop_down_config-source-sheet_name '!' lv_cell_from ':' lv_cell_to

INTO ls_drop_down_formula-formula .

CONDENSE ls_drop_down_formula-formula .

ENDIF.

"RANGE

IF ls_data_desc-s_drop_down_config-range-row_from IS INITIAL .

ls_data_desc-s_drop_down_config-range-row_from = iv_data_stop + 1.

ENDIF.



IF ls_data_desc-s_drop_down_config-range-row_to IS INITIAL.

ls_data_desc-s_drop_down_config-range-row_to = 1048576 .

ENDIF.



CLEAR : lv_cell_from, lv_cell_to .

CALL METHOD get_cell_position

EXPORTING

iv_row      = ls_data_desc-s_drop_down_config-range-row_from

iv_col      = lv_col

RECEIVING

rv_position = lv_cell_from.



CALL METHOD get_cell_position

EXPORTING

iv_row      = ls_data_desc-s_drop_down_config-range-row_to

iv_col      = lv_col

RECEIVING

rv_position = lv_cell_to.



CONCATENATE lv_cell_from ':' lv_cell_to INTO ls_drop_down_formula-cells .

CONDENSE ls_drop_down_formula-cells .



IF  ls_data_desc-s_drop_down_config-restrict_values EQ 'X'.

ls_drop_down_formula-restrict = '1'.

IF ls_data_desc-s_drop_down_config-error_text IS NOT INITIAL.

ls_drop_down_formula-error_text-header = ls_data_desc-s_drop_down_config-error_text-header.

ls_drop_down_formula-error_text-text = ls_data_desc-s_drop_down_config-error_text-text .

ENDIF.

ELSE.

ls_drop_down_formula-restrict = '0'.

ENDIF.



APPEND ls_drop_down_formula TO ct_dropdown_formula .

ENDIF.

ENDLOOP.



"For Row (Headers)



LOOP AT it_list_config_for_header INTO ls_list_config_for_header .



cv_dropdown_count = cv_dropdown_count + 1.



"SOURCE

IF ls_list_config_for_header-source-row_from IS INITIAL OR ls_list_config_for_header-source-row_to IS INITIAL .

CONCATENATE ls_list_config_for_header-source-sheet_name '!$' ls_list_config_for_header-source-col ':$' ls_list_config_for_header-source-col

INTO ls_drop_down_formula-formula.

CONDENSE ls_drop_down_formula-formula .

ELSE.



lv_row_from = ls_list_config_for_header-source-row_from . "Converting into Char type

lv_cell_from = '$' && ls_list_config_for_header-source-col && '$' && lv_row_from.



lv_row_to = ls_list_config_for_header-source-row_to . "Converting into Char type

lv_cell_to = '$' && ls_list_config_for_header-source-col && '$' && lv_row_to.

CONDENSE :  lv_cell_from   , lv_cell_to .

CONCATENATE ls_list_config_for_header-source-sheet_name '!' lv_cell_from ':' lv_cell_to

INTO ls_drop_down_formula-formula .

CONDENSE ls_drop_down_formula-formula .

ENDIF.



"RANGE



IF ls_list_config_for_header-range-col_from IS INITIAL.

ls_list_config_for_header-range-col_from = 1 .

ENDIF.



IF ls_list_config_for_header-range-row_from IS INITIAL.

ls_list_config_for_header-range-row_from = 1 .

ENDIF.



IF ls_list_config_for_header-range-row_to IS INITIAL.

ls_list_config_for_header-range-row_to = ls_list_config_for_header-range-row_from .

ENDIF.



CALL METHOD get_cell_position

EXPORTING

iv_row      = ls_list_config_for_header-range-row_from

iv_col      = ls_list_config_for_header-range-col_from

RECEIVING

rv_position = lv_cell_from.



CLEAR lv_row_to.



lv_row_to = ls_list_config_for_header-range-row_to .



IF ls_list_config_for_header-range-col_to IS INITIAL .

CLEAR lv_cell_to.

CONCATENATE 'XFD' lv_row_to INTO lv_cell_to .

ELSE.

CALL METHOD get_cell_position

EXPORTING

iv_row      = ls_data_desc-s_drop_down_config-range-row_to

iv_col      = ls_list_config_for_header-range-col_to

RECEIVING

rv_position = lv_cell_to.

ENDIF.



CONCATENATE lv_cell_from ':' lv_cell_to INTO ls_drop_down_formula-cells .

CONDENSE ls_drop_down_formula-cells .



IF ls_list_config_for_header-restict_values EQ 'X' .

ls_drop_down_formula-restrict = '1'.

IF ls_list_config_for_header-error_text IS NOT INITIAL.

ls_drop_down_formula-error_text-header = ls_data_desc-s_drop_down_config-error_text-header.

ls_drop_down_formula-error_text-text = ls_data_desc-s_drop_down_config-error_text-text .

ELSE.

CLEAR ls_list_config_for_header .

ENDIF.

ELSE.

ls_drop_down_formula-restrict = '0'.

ENDIF.



APPEND ls_drop_down_formula TO ct_dropdown_formula .



ENDLOOP.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Public Method ZTEST_ADPATER->PREPARE_FOR_DOWNLOAD

* +-------------------------------------------------------------------------------------------------+

* | [<-()] RV_XLSX_XML                    TYPE        XSTRING

* | [EXC!] OPENXML_ERROR

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD prepare_for_download.

*----------------------------------------------------------------------*

* TITLE   : Wholesale Contract Management

*----------------------------------------------------------------------*

* Authors : SAP SE

*----------------------------------------------------------------------*

* Program/Method Description :                                         *

* This method will return excel sheet in xstring format                *

*----------------------------------------------------------------------*

DATA:

*      l_xlsx_xml TYPE xstring,

l_xlsx_doc TYPE REF TO cl_xlsx_document.

DATA: l_workbookpart TYPE REF TO cl_xlsx_workbookpart.

DATA: l_worksheetparts TYPE REF TO cl_openxml_partcollection.

*  DATA: l_part TYPE REF TO cl_openxml_part.

DATA: l_worksheetpart TYPE REF TO cl_xlsx_worksheetpart.

DATA: l_stylespart TYPE REF TO cl_xlsx_stylespart.

DATA: l_sharedstringspart TYPE REF TO cl_xlsx_sharedstringspart.



FIELD-SYMBOLS:

<ls_sheet>  TYPE ts_sheets_struc.



DATA:

l_sheetxml     TYPE xstring,

l_shared_xml   TYPE xstring,

l_styles_xml   TYPE xstring,

l_workbook_xml TYPE xstring.



*  prepare sheet with meta data

create_sheet(

EXPORTING

iv_sheet_name  = co_meta_data

iv_state       = co_state_hidden    " 0:visible, 1:Hidden

iv_disp_header = abap_false

it_table_data  = mt_meta_data ).



TRY.

l_xlsx_doc = cl_xlsx_document=>create_document( ).

*      get the workboopart of the document

l_workbookpart = l_xlsx_doc->get_workbookpart( ).

l_worksheetparts = l_workbookpart->get_worksheetparts( ).



*       create all sheets

SORT mt_sheets STABLE BY state ASCENDING .  "Mystery : If a hidden sheet is before a visible sheet, the sheet doesn't get hidden ; although the XML is correct

LOOP AT mt_sheets ASSIGNING <ls_sheet>.

l_worksheetpart ?= l_worksheetparts->get_part( iv_index = sy-tabix - 1 ).

IF l_worksheetpart IS INITIAL.

l_worksheetpart = l_workbookpart->add_worksheetpart( ).

ENDIF.

<ls_sheet>-relid = l_workbookpart->get_id_for_part( l_worksheetpart ).



CALL TRANSFORMATION ZEXCEL07_SHEET_XML

SOURCE param = <ls_sheet>-sheet

RESULT XML l_sheetxml.

l_worksheetpart->feed_data( iv_data = l_sheetxml ).

ENDLOOP.



*      Transformation for the style part

create_style_sheet( ).

CALL TRANSFORMATION zexcel07_stylesheet_xml

SOURCE param = ms_style

RESULT XML l_styles_xml.

*      add style to the woorbook

l_stylespart = l_workbookpart->add_stylespart( ).

l_stylespart->feed_data( iv_data = l_styles_xml ).



*      Transformation for the shared string part

CALL TRANSFORMATION zexcel07_shrdstring_xml

SOURCE param = ms_sharedstring

RESULT XML l_shared_xml.



*      add shared string to the workbook

l_sharedstringspart = l_workbookpart->add_sharedstringspart( ).

l_sharedstringspart->feed_data( iv_data = l_shared_xml ).



*      prepare the workbook

CALL TRANSFORMATION zexcel07_workbook_xml

SOURCE param = mt_sheets

RESULT XML l_workbook_xml.

l_workbookpart->feed_data( iv_data = l_workbook_xml ).



rv_xlsx_xml = l_xlsx_doc->get_package_data( ).

CATCH cx_openxml_format cx_openxml_not_found cx_openxml_not_allowed.

RAISE openxml_error.

ENDTRY.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Public Method ZTEST_ADPATER->UPDATE_SHEET_DATA

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_NAME                        TYPE        STRING(optional)

* | [--->] IV_XML                         TYPE        XSTRING(optional)

* | [--->] IT_TABLE_DATA                  TYPE        STANDARD TABLE(optional)

* | [<---] EV_XML                         TYPE        XSTRING

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD update_sheet_data.

CONSTANTS: lc_n TYPE string VALUE '0123456789. '.

DATA:

l_p TYPE p.

DATA:

lx_root TYPE REF TO cx_root.



DATA:

lv_test    TYPE string .

DATA:

lo_ixml_factory   TYPE REF TO if_ixml,

lo_stream_factory TYPE REF TO if_ixml_stream_factory,

lo_istream        TYPE REF TO if_ixml_istream,

lo_idocument      TYPE REF TO if_ixml_document,

lo_iparser        TYPE REF TO if_ixml_parser.



DATA: lo_node_collection TYPE REF TO if_ixml_node_collection,

lo_node_iterator   TYPE REF TO if_ixml_node_iterator,

lo_node            TYPE REF TO if_ixml_node.



DATA:

lo_attr   TYPE REF TO if_ixml_named_node_map.



DATA:

lv_no_of_entries      TYPE        i,

lr_subnode            TYPE REF TO if_ixml_node,

lr_attr_node          TYPE REF TO if_ixml_node,

lr_node_list          TYPE REF TO if_ixml_node_list,

lr_node_list_iterator TYPE REF TO if_ixml_node_iterator.



DATA:

lv_no_of_col         TYPE i,

lr_col_node          TYPE REF TO if_ixml_node,

lr_val_node          TYPE REF TO if_ixml_node,

lr_col_list          TYPE REF TO if_ixml_node_list,

lr_col_list_iterator TYPE REF TO if_ixml_node_iterator.



DATA:

ls_sheet  TYPE ts_sheets_struc,

ls_sh_str TYPE ts_sharedstring,

lt_row    TYPE STANDARD TABLE OF ts_row_struc INITIAL SIZE 1,

lt_cell   TYPE STANDARD TABLE OF ts_cell_struc INITIAL SIZE 1.



FIELD-SYMBOLS:

<ls_row>  TYPE any,

<lv_cell> TYPE any.

*        <lv_value>  TYPE any.



* Create Main Factory

* creates an instance of the iXML class and returns an interface pointer to the instance.

lo_ixml_factory = cl_ixml=>create( ).

* Next Create Stream Factory

* creates a new StreamFactory instance and returns an interface pointer to this instance

lo_stream_factory = lo_ixml_factory->create_stream_factory( ).

* Create Input Stream

* creates a new XML input stream for the given ABAP xstring

lo_istream = lo_stream_factory->create_istream_xstring( iv_xml ).      "Where 'xml' is the input XML xstring.

* Initialize Input Document

* creates a new Document instance and returns an interface pointer to this instance.

lo_idocument = lo_ixml_factory->create_document( ).

* creates a new Parser instance and returns an interface pointer to this instance.

lo_iparser = lo_ixml_factory->create_parser(

stream_factory = lo_stream_factory

istream        = lo_istream

document       = lo_idocument

).

* implements the DOM-generating interface to the parser

lo_iparser->parse( ).

* Returns an iXMLNodeCollection of all the elements with a given tag name

* in the order in which they would be encountered in a preorder

* traversal of the document tree.

lo_node_collection        = lo_idocument->get_elements_by_tag_name( name = co_sheet_data ).

lo_node_iterator          = lo_node_collection->create_iterator( ).

lo_node                   = lo_node_iterator->get_next( ).



lr_node_list              = lo_node->get_children( ).

lv_no_of_entries          = lr_node_list->get_length( ).

lr_node_list_iterator     = lr_node_list->create_iterator( ).



DATA:

lv_skip      TYPE boolean,

lv_excel_col TYPE i,

lv_row       TYPE string,

lv_col       TYPE string,

lv_temp      TYPE string,

lv_num       TYPE boolean.



TYPES:

BEGIN OF ts_new_node,

node_r TYPE string,

val    TYPE string,

END OF ts_new_node.



DATA:

lt_new_node TYPE STANDARD TABLE OF ts_new_node,

ls_new_node TYPE ts_new_node.



DATA:

lr_attr       TYPE REF TO if_ixml_node,

lr_new_node   TYPE REF TO if_ixml_node,

lr_clone_node TYPE REF TO if_ixml_node.



LOOP AT it_table_data ASSIGNING <ls_row>.

lr_subnode            = lr_node_list_iterator->get_next( ).

lr_col_list           = lr_subnode->get_children( ).

lv_no_of_col          = lr_col_list->get_length( ).

lr_col_list_iterator  = lr_col_list->create_iterator( ).

lo_attr               = lr_subnode->get_attributes( ).

lr_attr_node  = lo_attr->get_named_item( name = 'r' ).

lv_row = lr_attr_node->get_value( ).

DO.

ASSIGN COMPONENT sy-index OF STRUCTURE <ls_row> TO <lv_cell>.

IF sy-subrc IS NOT INITIAL.

EXIT.

ENDIF.



IF lv_skip EQ abap_false.

lr_col_node   = lr_col_list_iterator->get_next( ).

IF lr_col_node IS INITIAL.

CHECK <lv_cell> IS NOT INITIAL.

ls_new_node-val = <lv_cell>.

*            _is_num ls_new_node-val lv_num.

lv_num = _is_num( iv_value = ls_new_node-val ).

CHECK lv_num EQ abap_true.



get_col_label(

EXPORTING

iv_col = sy-index

IMPORTING

ev_col = lv_temp

).

CONCATENATE lv_temp lv_row INTO lv_temp.

CONDENSE lv_temp NO-GAPS.

ls_new_node-node_r = lv_temp.

INSERT ls_new_node INTO TABLE lt_new_node.

CONTINUE. " we need to create a new node here

ENDIF.



lo_attr       = lr_col_node->get_attributes( ).

lr_attr_node  = lo_attr->get_named_item( name = 'r' ).

lv_temp       = lr_attr_node->get_value( ) .



SPLIT lv_temp AT lv_row INTO lv_col lv_temp.



get_col_index(

EXPORTING

iv_col_index = lv_col

IMPORTING

ev_col       = lv_excel_col

).

ENDIF.

lv_temp = <lv_cell>.



IF lv_excel_col > sy-index.

*          new node to be created

lv_skip = abap_true.

IF lv_temp IS NOT INITIAL.

ls_new_node-val = lv_temp.



get_col_label(

EXPORTING

iv_col = sy-index

IMPORTING

ev_col = lv_temp

).

CONCATENATE lv_temp lv_row INTO lv_temp.

CONDENSE lv_temp NO-GAPS.

ls_new_node-node_r = lv_temp.

INSERT ls_new_node INTO TABLE lt_new_node.

ENDIF.

CONTINUE.

ENDIF.

lv_skip = abap_false.



DATA(lv_current_val) = lr_col_node->get_value( ).



IF lr_clone_node IS INITIAL AND lv_current_val IS NOT INITIAL.

lr_clone_node = lr_col_node->clone(  ).

ENDIF.



*        _is_num lv_temp lv_num.

IF lv_temp IS NOT INITIAL.

lv_num = _is_num( iv_value = lv_temp ).

IF lv_num EQ abap_false.

ELSE.

DATA(lv_is_string) = lo_attr->get_named_item_ns(

EXPORTING

name =  't'   " Name

).

IF lv_is_string IS NOT INITIAL.

CONTINUE.

ENDIF.

CHECK lv_current_val NE lv_temp.

*            lo_attr->remove_named_item( name = 't' ).

lr_val_node = lr_col_node->get_first_child( ).

IF lr_val_node IS NOT INITIAL.

lr_val_node->set_value( value = lv_temp ).

ELSE.

lo_idocument->create_simple_element(

name      = 'v'

parent    = lr_col_node

value     = lv_temp

).

ENDIF.

ENDIF.

ENDIF.

ENDDO.

LOOP AT lt_new_node INTO ls_new_node.

lr_new_node = lr_clone_node->clone( ).

lo_attr       = lr_new_node->get_attributes( ).

lo_attr->remove_named_item( name = 't' ).

lr_attr = lo_attr->get_named_item_ns( name = 'r' ).

lr_attr->set_value( value = ls_new_node-node_r ).

lr_val_node = lr_new_node->get_first_child( ).

lr_val_node->set_value( value = ls_new_node-val ).

lr_subnode->append_child( new_child = lr_new_node ).

ENDLOOP.

CLEAR lt_new_node.

ENDLOOP.



*update the file

TRY.



DATA: l_workbookpart TYPE REF TO cl_xlsx_workbookpart.

DATA: l_worksheetpart TYPE REF TO cl_xlsx_worksheetpart.

DATA:

l_sheet_xml TYPE xstring,

l_xml       TYPE REF TO cl_xml_document.



FIELD-SYMBOLS:

<ls_sheet> LIKE LINE OF mt_sheets.



CREATE OBJECT l_xml.

l_xml->create_with_dom( document = lo_idocument ).

l_xml->render_2_xstring(

IMPORTING

stream       = l_sheet_xml    " XString (STREAM)

).



l_workbookpart = mo_xlsx_doc->get_workbookpart( ).

READ TABLE mt_sheets ASSIGNING <ls_sheet> WITH KEY name = iv_name.  "get the shhet to be uploaded

IF sy-subrc IS INITIAL.

l_worksheetpart ?= l_workbookpart->get_part_by_id( <ls_sheet>-relid ).  "get the workbook based on the relationid

l_worksheetpart->feed_data( iv_data = l_sheet_xml ).

IF ev_xml IS REQUESTED.

ev_xml = mo_xlsx_doc->get_package_data( ).

ENDIF.

ENDIF.

CATCH cx_openxml_format cx_openxml_not_found cx_openxml_not_allowed.

ENDTRY.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->UPLOAD

* +-------------------------------------------------------------------------------------------------+

* | [<---] EV_XLSX_XML                    TYPE        XSTRING

* | [EXC!] FILE_UPLOAD_ERROR

* | [EXC!] USER_CANCEL

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD upload.



DATA:

lv_rc           TYPE i,

lv_user_action  TYPE i,

lv_filelength   TYPE i,

lv_title        TYPE string,

lv_filepath     TYPE string,

ls_file         TYPE file_table,

lt_file         TYPE filetable,

lt_file_content TYPE STANDARD TABLE OF solisti1.



lv_title = 'Export to Excel 2007'(t01).



CALL METHOD cl_gui_frontend_services=>file_open_dialog

EXPORTING

window_title            = lv_title   " Title Of File Open Dialog

default_extension       = 'xlsx'     " Default Extension

file_filter             = '*.xlsx'    " File Extension Filter String

CHANGING

file_table              = lt_file    " Table Holding Selected Files

rc                      = lv_rc    " Return Code, Number of Files or -1 If Error Occurred

user_action             = lv_user_action    " User Action (See Class Constants ACTION_OK, ACTION_CANCEL)

EXCEPTIONS

file_open_dialog_failed = 1

cntl_error              = 2

error_no_gui            = 3

not_supported_by_gui    = 4

OTHERS                  = 5.

IF sy-subrc <> 0 OR lv_rc EQ -1.

MESSAGE e162(alvht).

EXIT.

ENDIF.



IF lv_user_action = cl_gui_frontend_services=>action_cancel .

MESSAGE s161(alvht).

RAISE user_cancel .

ENDIF.



READ TABLE lt_file INTO ls_file INDEX 1. "read only the first file

lv_filepath = ls_file-filename.



CALL METHOD cl_gui_frontend_services=>gui_upload

EXPORTING

filename                = lv_filepath    " Name of file

filetype                = 'BIN'    " File Type (ASCII, Binary)

IMPORTING

filelength              = lv_filelength   " File length

CHANGING

data_tab                = lt_file_content   " Transfer table for file contents

EXCEPTIONS

file_open_error         = 1

file_read_error         = 2

no_batch                = 3

gui_refuse_filetransfer = 4

invalid_type            = 5

no_authority            = 6

unknown_error           = 7

bad_data_format         = 8

header_not_allowed      = 9

separator_not_allowed   = 10

header_too_long         = 11

unknown_dp_error        = 12

access_denied           = 13

dp_out_of_memory        = 14

disk_full               = 15

dp_timeout              = 16

not_supported_by_gui    = 17

error_no_gui            = 18

OTHERS                  = 19.

IF sy-subrc <> 0.

RAISE file_upload_error.

ENDIF.



IF lt_file_content IS NOT INITIAL.

CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'

EXPORTING

input_length = lv_filelength

IMPORTING

buffer       = ev_xlsx_xml

TABLES

binary_tab   = lt_file_content.

ENDIF.

ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Private Method ZTEST_ADPATER->_IS_NUM

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_VALUE                       TYPE        STRING

* | [<-()] RV_NUM                         TYPE        BOOLEAN

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD _is_num.

CONSTANTS: lc_n TYPE string VALUE ' 0123456789. '.

DATA:

l_p     TYPE p,

lx_root TYPE REF TO cx_root,

lv_msg  TYPE string.

TRY.

IF iv_value CO lc_n.

l_p = iv_value.

rv_num = abap_true.

*            WRITE: 'numeric'.

ELSE.

rv_num = abap_false.

*            WRITE: 'string'   .

ENDIF.



CATCH cx_sy_conversion_no_number INTO lx_root.

*          write: 'exception:cx_sy_conversion_no_number'.

rv_num = abap_false.

MESSAGE i058(/wctm/document) INTO lv_msg.

WRITE lv_msg.

CATCH cx_sy_conversion_overflow INTO lx_root.

*          write: 'exception:cx_sy_conversion_overflow'.

rv_num = abap_false.

ENDTRY.

ENDMETHOD.

ENDCLASS.

3. Generate excel using this class as below :

DATA lo_excel TYPE REF TO ZTEST_ADPATER .



data lV_SHEET_NAME type string.



DATA lt_excel_header TYPE ZTEST_ADPATER =>tt_header_table.



DATA lv_filecontent type RAWSTRING.



FIELD-SYMBOLS <lt_dyn_data> TYPE STANDARD TABLE.



CREATE OBJECT lo_excel.



**fill <lt_dyn_data> with your data and provide it  to the excel adapter classs



* Create Excel sheet with dynamic data

lo_excel->create_sheet(

EXPORTING

iv_sheet_name = lv_sheet_name

it_header = lt_excel_header

iv_disp_header = abap_true

it_table_data = <lt_dyn_data> ).



* Final excel sheet

CLEAR ls_filecontent.



lo_excel->prepare_for_download(

RECEIVING

rv_xlsx_xml = lv_filecontent ).

4. To create multiple sheets just call "create_sheet" multiple times with related internal table

So we are ready with the utility class now. This can be used in any scenario to generate an excel file.
I will post my next blog about using this utility class to generate excel file via OData entity in a Fiori application.Please wait for the updates till then.

Generate dynamic excel from dynamic internal table

Get Started with the ABAP Development Tools for SAP NetWeaver

Become an ABAP in Eclipse Feature Explorer and earn the Explorer Badge

Six kinds of debugging tips to find the source code where the message is raised