data: begin of ls_mara,
matnr type matnr,
maktx type matnr,
end of ls_mara,
lt_mara like standard table of ls_mara,
begin of ls_marc,
matnr type matnr,
werks type marc-werks,
name1 type t001w-name1,
end of ls_marc,
lt_marc like standard table of ls_marc,
begin of ls_mard,
matnr type matnr,
werks type marc-werks,
lgort type mard-lgort,
lgobe type t001l-lgobe,
end of ls_mard,
lt_mard like standard table of ls_mard,
begin of ls_mchb,
matnr type matnr,
werks type marc-werks,
lgort type mard-lgort,
charg type mchb-charg,
clabs type labst,
cumlm type umlmd,
cinsm type insme,
ceinm type einme,
cspem type speme,
cretm type retme,
end of ls_mchb,
lt_mchb like standard table of ls_mchb,
begin of ls_output,
area type string,
clabs type labst,
cumlm type umlmd,
cinsm type insme,
ceinm type einme,
cspem type speme,
cretm type retme,
end of ls_output,
lt_output like standard table of ls_output,
ls_marakey type lvc_nkey,
ls_marckey type lvc_nkey,
ls_mardkey type lvc_nkey,
lr_table type ref to cl_salv_table,
lr_columns type ref to cl_salv_columns_tree,
lr_tree type ref to cl_salv_tree,
lr_node type ref to cl_salv_node,
lt_nodes type salv_t_nodes,
ls_node like line of lt_nodes.
select matnr maktx
up to 50 rows
from makt
into table lt_mara
where spras = sy-langu.
select matnr a~werks name1
from marc as a join t001w as b
on a~werks = b~werks
into table lt_marc
for all entries in lt_mara
where matnr = lt_mara-matnr.
select matnr a~werks a~lgort lgobe
from mard as a join t001l as b
on a~lgort = b~lgort
and a~werks = b~werks
into table lt_mard
for all entries in lt_marc
where matnr = lt_marc-matnr
and a~werks = lt_marc-werks.
select matnr werks lgort charg clabs cumlm cinsm ceinm cspem cretm
from mchb
into table lt_mchb
for all entries in lt_mard
where matnr = lt_mard-matnr
and werks = lt_mard-werks
and lgort = lt_mard-lgort.
cl_salv_tree=>factory(
importing
r_salv_tree = lr_tree
changing
t_table = lt_output ).
lr_columns = lr_tree->get_columns( ).
lr_columns->set_optimize( abap_true ).
loop at lt_mara into ls_mara.
clear ls_output.
concatenate ls_mara-matnr ls_mara-maktx into ls_output-area separated by space.
loop at lt_mchb into ls_mchb where matnr = ls_mara-matnr.
add-corresponding ls_mchb to ls_output.
endloop.
lr_node = lr_tree->get_nodes( )->add_node( related_node = space
data_row = ls_output
relationship = cl_gui_column_tree=>relat_last_child ).
ls_marakey = lr_node->get_key( ).
loop at lt_marc into ls_marc where matnr = ls_mara-matnr.
clear ls_output.
concatenate ls_marc-werks ls_marc-name1 into ls_output-area separated by space.
loop at lt_mchb into ls_mchb where matnr = ls_mara-matnr and werks = ls_marc-werks.
add-corresponding ls_mchb to ls_output.
endloop.
lr_node = lr_tree->get_nodes( )->add_node( related_node = ls_marakey
data_row = ls_output
relationship = cl_gui_column_tree=>relat_last_child ).
ls_marckey = lr_node->get_key( ).
loop at lt_mard into ls_mard where matnr = ls_mara-matnr and werks = ls_marc-werks.
clear ls_output.
concatenate ls_mard-lgort ls_mard-lgobe into ls_output-area separated by space.
loop at lt_mchb into ls_mchb where matnr = ls_mara-matnr and werks = ls_marc-werks and lgort = ls_mard-lgort.
add-corresponding ls_mchb to ls_output.
endloop.
lr_node = lr_tree->get_nodes( )->add_node( related_node = ls_marckey
data_row = ls_output
relationship = cl_gui_column_tree=>relat_last_child ).
ls_mardkey = lr_node->get_key( ).
loop at lt_mchb into ls_mchb where matnr = ls_mara-matnr and werks = ls_marc-werks and lgort = ls_mard-lgort.
clear ls_output.
ls_output-area = ls_mchb-charg.
move-corresponding ls_mchb to ls_output.
lr_node = lr_tree->get_nodes( )->add_node( related_node = ls_mardkey
data_row = ls_output
relationship = cl_gui_column_tree=>relat_last_child ).
endloop.
endloop.
endloop.
endloop.
lr_tree->display( ).
constants:lc_xlspace type c value ' '. "Hexa value for this field should be 0030
data: lv_level type i,
lv_xlsx type xstring,
lt_table type ref to data,
lr_data type ref to data.
field-symbols: <data> type any,
<table> type standard table,
<str> type any.
lt_nodes = lr_tree->get_nodes( )->get_all_nodes( ).
loop at lt_nodes into ls_node.
lr_node = ls_node-node.
clear lv_level.
do.
try.
lr_node = lr_node->get_parent( ).
add 1 to lv_level.
catch cx_salv_msg.
exit.
endtry.
enddo.
lr_data = ls_node-node->get_data_row( ).
assign lr_data->* to <data>.
if <table> is not assigned.
create data lt_table like standard table of <data>.
assign lt_table->* to <table>.
endif.
assign component 1 of structure <data> to <str>.
subtract 1 from lv_level.
do lv_level times.
concatenate lc_xlspace <str> into <str>.
enddo.
append <data> to <table>.
endloop.
cl_salv_table=>factory(
importing
r_salv_table = lr_table
changing
t_table = <table> ).
lr_table->display( ).
lv_xlsx = lr_table->to_xml( if_salv_bs_xml=>c_type_xlsx ).
data: lr_zip type ref to cl_abap_zip,
lr_xlnode type ref to if_ixml_node,
lr_xldimension type ref to if_ixml_node,
lr_xlsheetpr type ref to if_ixml_element,
lr_xloutlinepr type ref to if_ixml_element,
lv_file type xstring,
lr_file type ref to cl_xml_document,
lr_xlrows type ref to if_ixml_node_list,
lr_xlrow type ref to if_ixml_element,
lr_xlformat type ref to if_ixml_element,
lr_xlworksheet type ref to if_ixml_element,
lv_tabix type i,
lv_maxlevel type i,
lv_levels type string.
create object lr_zip.
lr_zip->load( lv_xlsx ).
*Get Worksheet XML file
lr_zip->get( exporting name = 'xl/worksheets/sheet1.xml'
importing content = lv_file ).
create object lr_file.
lr_file->parse_xstring( lv_file ).
*Row elements are under SheetData
lr_xlnode = lr_file->find_node( 'sheetData' ).
lr_xlrows = lr_xlnode->get_children( ).
do lr_xlrows->get_length( ) times.
lv_tabix = sy-index - 1.
lr_xlrow ?= lr_xlrows->get_item( lv_tabix ).
*Find the same node in the SALV Tree object
read table lt_nodes into ls_node index lv_tabix.
if sy-subrc eq 0.
lr_node = ls_node-node.
*Find the level of the node
clear lv_level.
do.
try.
lr_node = lr_node->get_parent( ).
add 1 to lv_level.
catch cx_salv_msg.
exit.
endtry.
enddo.
subtract 1 from lv_level.
if lv_level ne 0.
lv_levels = lv_level.
if lv_level > lv_maxlevel.
lv_maxlevel = lv_level.
endif.
condense lv_levels.
*Assign the level to row
lr_xlrow->set_attribute( name = 'outlineLevel' value = lv_levels ).
lr_xlrow->set_attribute( name = 'hidden' value = 'true' ).
endif.
endif.
enddo.
*Set maximum levels used in the sheet
lv_levels = lv_maxlevel.
condense lv_levels.
lr_xlformat ?= lr_file->find_node( 'sheetFormatPr' ).
lr_xlformat->set_attribute( name = 'outlineLevelRow' value = lv_levels ).
*Create new element in the XML file
lr_xlworksheet ?= lr_file->find_node( 'worksheet' ).
lr_xldimension ?= lr_file->find_node( 'dimension' ).
lr_xlsheetpr = cl_ixml=>create( )->create_document( )->create_element( name = 'sheetPr' ).
lr_xloutlinepr = cl_ixml=>create( )->create_document( )->create_element( name = 'outlinePr' ).
lr_xlsheetpr->if_ixml_node~append_child( lr_xloutlinepr ).
lr_xloutlinepr->set_attribute( name = 'summaryBelow' value = 'false' ).
lr_xlworksheet->if_ixml_node~insert_child( new_child = lr_xlsheetpr ref_child = lr_xldimension ).
*Create Xstring file for the XML, and add it to Excel Zip file
lr_file->render_2_xstring( importing stream = lv_file ).
lr_zip->delete( exporting name = 'xl/worksheets/sheet1.xml' ).
lr_zip->add( exporting name = 'xl/worksheets/sheet1.xml'
content = lv_file ).
lv_xlsx = lr_zip->save( ).
LV_XLSX is the output file. You may download it to your desktop and see how it looks like. Now, with these changes the file Excel file shows groupings and hierarchy.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
25 | |
5 | |
4 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 | |
1 |