SAP ABAP Central: Generate dynamic excel from dynamic internal table

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.

SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Learning, SAP ABAP Study Materials

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:

1. 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">

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

<sheetFormatPr defaultRowHeight="12.75">
<tt:s-cond check="param.outlinelevel > 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>

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

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


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

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


<fonts count="4">

<sz val="10"/>
<name val="Arial"/>



<sz val="10"/>
<color indexed="12"/>
<name val="Arial"/>




<sz val="10"/>
<color theme="0"/>
<name val="Arial"/>
<family val="2"/>




<sz val="10"/>
<name val="Arial"/>
<family val="2"/>


</fonts>

<fills count="14">
<fill>
<patternFill patternType="none"/>
</fill>
<fill>
<patternFill patternType="gray125"/>
</fill>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFC5D9F1"/>

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

<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 > 0">
<tt:attribute name="applyFill">1</tt:attribute>
</tt:s-cond>
<tt:s-cond check="$xf.borderid > 0">
<tt:attribute name="applyBorder">1</tt:attribute>
</tt:s-cond>
<tt:s-cond check="$xf.is_string > 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 > 0">
<protection>
<tt:attribute name="locked">0</tt:attribute>
</protection>
</tt:s-cond>
<tt:s-cond check="$xf.is_string > 0">
<alignment>
<tt:s-cond check="$xf.is_string > 0">
<tt:attribute name="horizontal">right</tt:attribute>
</tt:s-cond>
<tt:s-cond check="$xf.indent > 0">
<tt:attribute name="indent" value-ref="$xf.indent"/>
</tt:s-cond>
<tt:s-cond check="$xf.wrap > 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.

SAP ABAP Central

Pages

Thursday 25 April 2019

Generate dynamic excel from dynamic internal table

1. Create following 4 transformations:

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

3. Generate excel using this class as below :

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

No comments:

Post a Comment