<?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 > 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 > 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 > 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 > 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;lt;phoneticPr fontId="0" type="noConversion"/&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>
<?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;lt;numFmts&amp;gt;
&amp;lt;tt:attribute name="count" value-ref="param.numfmts_count"/&amp;gt;
&amp;lt;tt:loop ref="param.t_numfmts" name="fmt"&amp;gt;
&amp;lt;numFmt&amp;gt;
&amp;lt;tt:attribute name="numFmtId" value-ref="$fmt.id"/&amp;gt;
&amp;lt;tt:attribute name="formatCode" value-ref="$fmt.code"/&amp;gt;
&amp;lt;/numFmt&amp;gt;
&amp;lt;/tt:loop&amp;gt;
&amp;lt;/numFmts&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 > 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>
<?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>
<?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>
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.
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 ).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |