In this Blog-post I’d like to give a few insights on how we process XLSX file by using latest ABAP, which might be quite different than in most other implementations, for example:ABAP and OLE or Excel with SAP – An Overview
By using CL_XLSX_DOCUMENT (available from 2008), it is easy to load file, workbook, sheets, rows and columns. A good example code can be found within class CL_EHFND_XLSX(available from 2014).
On the other hand, you can set document format by using IF_IXML_NODE, below example code show how …
lo_xlsx_doc = cl_xlsx_document=>load_document( <XSTRING of xlsx file> )
lo_workbookpart = lo_xlsx_doc->get_workbookpart( ).
lo_wordsheetparts = lo_workbookpart->get_worksheetparts( ).
lo_wordsheetpart = lo_wordsheetparts->get_part( 0 ).
lo_sheet_content = lo_wordsheetpart->get_data( ).
CREATE OBJECT lo_xml_document.
lo_xml_document->parse_xstring( lo_sheet_content ).
DATA(lo_node_datavalidation_init) = lo_node_datavalidations->clone( ).
....
DATA(lo_node_datavalidation) = lo_node_datavalidation_init->clone( ).
lo_node_datavalidation->set_name( 'dataValidation' ).
CONCATENATE ls_validation-column lv_begin_str ':' ls_validation-column lv_end_str INTO
DATA(lv_sqref).
DATA(lo_attrs_map_datavalidation) = lo_node_datavalidation->get_attributes( ).
DATA(lo_attr_sqref) = lo_attrs_map_datavalidation->get_named_item_ns( 'count' )->clone( ).
lo_attr_sqref->set_name( 'sqref' ).
lo_attr_sqref->set_value( lv_sqref ).
lo_attrs_map_datavalidation->set_named_item_ns( lo_attr_sqref ).
lo_attrs_map_datavalidation->remove_named_item_ns( 'count' ).
DATA(lo_attr_showerrormsg) = lo_attr_sqref->clone( ).
lo_attr_showerrormsg->set_name( 'showErrorMessage' ).
lo_attr_showerrormsg->set_value( '1' ).
lo_attrs_map_datavalidation->set_named_item_ns( lo_attr_showerrormsg ).
.....
Main functionalities of this report are:
1. Download any DDIC table’s content into xlsx file
2. Change value in the xlsx file
3. upload xlsx file into DDIC table
4. check changed value in DDIC table.
And the dynamic structure is used in this report. The code style is what I would like to present also.
By using CL_XLSX_DOCUMENT (available from 2008), it is easy to load file, workbook, sheets, rows and columns. A good example code can be found within class CL_EHFND_XLSX(available from 2014).
On the other hand, you can set document format by using IF_IXML_NODE, below example code show how …
lo_xlsx_doc = cl_xlsx_document=>load_document( <XSTRING of xlsx file> )
lo_workbookpart = lo_xlsx_doc->get_workbookpart( ).
lo_wordsheetparts = lo_workbookpart->get_worksheetparts( ).
lo_wordsheetpart = lo_wordsheetparts->get_part( 0 ).
lo_sheet_content = lo_wordsheetpart->get_data( ).
CREATE OBJECT lo_xml_document.
lo_xml_document->parse_xstring( lo_sheet_content ).
DATA(lo_node_datavalidation_init) = lo_node_datavalidations->clone( ).
....
DATA(lo_node_datavalidation) = lo_node_datavalidation_init->clone( ).
lo_node_datavalidation->set_name( 'dataValidation' ).
CONCATENATE ls_validation-column lv_begin_str ':' ls_validation-column lv_end_str INTO
DATA(lv_sqref).
DATA(lo_attrs_map_datavalidation) = lo_node_datavalidation->get_attributes( ).
DATA(lo_attr_sqref) = lo_attrs_map_datavalidation->get_named_item_ns( 'count' )->clone( ).
lo_attr_sqref->set_name( 'sqref' ).
lo_attr_sqref->set_value( lv_sqref ).
lo_attrs_map_datavalidation->set_named_item_ns( lo_attr_sqref ).
lo_attrs_map_datavalidation->remove_named_item_ns( 'count' ).
DATA(lo_attr_showerrormsg) = lo_attr_sqref->clone( ).
lo_attr_showerrormsg->set_name( 'showErrorMessage' ).
lo_attr_showerrormsg->set_value( '1' ).
lo_attrs_map_datavalidation->set_named_item_ns( lo_attr_showerrormsg ).
.....
Executable SE38 report.
Main functionalities of this report are:
1. Download any DDIC table’s content into xlsx file
2. Change value in the xlsx file
3. upload xlsx file into DDIC table
4. check changed value in DDIC table.
And the dynamic structure is used in this report. The code style is what I would like to present also.
User Interface
selection-screen begin of block b04 with frame title text-b04.
parameters p_exp radiobutton group radi user-command action default 'X'.
parameters p_imp radiobutton group radi.
selection-screen end of block b04.
selection-screen begin of block b01 with frame title text-b01.
parameters p_table type dd02l-tabname modif id gp1 obligatory memory id ht.
parameters p_file type localfile modif id gp2 obligatory memory id hf.
parameters p_sql type string modif id gp3.
selection-screen comment /1(75) comm.
selection-screen end of block b01.
initialization.
comm = `e.g. RLDNR = 'Y1' AND RRCTY = 'U'`.
at selection-screen on value-request for p_file.
* call function 'F4_FILENAME'
* exporting
* field_name = 'P_FILE'
* importing
* file_name = p_file.
data(title) = |Select Excel File, e.g. *.xlsx|.
data(defaultextension) = |.xlsx|.
data(filefilter) = `Excel Files (*.xlsx)|*.xlsx`.
data it_tab type filetable.
data returncode type i.
call method cl_gui_frontend_services=>file_open_dialog
exporting
window_title = title
default_extension = defaultextension
* default_filename =
* file_filter = filefilter
* with_encoding =
* initial_directory =
* multiselection =
changing
file_table = it_tab
rc = returncode
* user_action =
* file_encoding =
* exceptions
* file_open_dialog_failed = 1
* cntl_error = 2
* error_no_gui = 3
* not_supported_by_gui = 4
* others = 5
.
if sy-subrc <> 0.
* Implement suitable error handling here
endif.
read table it_tab assigning field-symbol(<selectedfilename>) index 1.
if sy-subrc = 0.
p_file = <selectedfilename>-filename.
endif.
Main Functionality
Download table content into local excel file (xlsx)
Select action “Export”
Input existing table , e.g. Table Name T000, File Full Path: c:\demo\t000.xlsx
Change the value of non-key column in xlsx file.
start-of-selection.
try.
data(configurationhandler) = new lcl_configuration( filefullpath = conv #( p_file )
tablename = conv #( p_table )
sqlscript = p_sql ).
if p_exp = abap_true.
configurationhandler->export( ).
else.
configurationhandler->import( ).
endif.
catch lcx_configuration into data(configurationexception).
write: / configurationexception->local_text.
endtry.
Upload local excel file (*.xlsx) into table
Select action “Import”
Input existing table , e.g. Table Name T000, File Full Path: c:\demo\t000.xlsx
Empty row will be skip, and table entries will be modified (Insert or Update). It is not possible to delete existing table entry.
start-of-selection.
try.
data(configurationhandler) = new lcl_configuration( filefullpath = conv #( p_file )
tablename = conv #( p_table )
sqlscript = p_sql ).
if p_exp = abap_true.
configurationhandler->export( ).
else.
configurationhandler->import( ).
endif.
catch lcx_configuration into data(configurationexception).
write: / configurationexception->local_text.
endtry.
Exception Handling
You will get exception of structure infliction if xlsx file is not for that table.
"check file structure, first line of excel file
data(columncount) = firstsheet->get_last_column_number_in_row( 1 ).
data column type i value 1.
"data tablecomponents type cl_abap_structdescr=>component_table.
data(tablecomponents) = me->tablestructure->get_components( ).
data invalidcolumn type string.
types: begin of columninfo,
column type i,
columnname type string,
end of columninfo.
types columnsinfo type standard table of columninfo with empty key.
data columnfromfile type columnsinfo.
do columncount times.
data(cellvalue) = firstsheet->get_cell_content(
exporting
iv_row = 1
iv_column = column ).
append initial line to columnfromfile assigning field-symbol(<columnfromfile>).
<columnfromfile>-column = column.
<columnfromfile>-columnname = cellvalue.
if line_exists( tablecomponents[ name = cellvalue ] ).
delete tablecomponents where name = cellvalue.
else.
invalidcolumn = invalidcolumn && |,{ cellvalue }|.
endif.
column = column + 1.
enddo.
data missingcolumns type string.
loop at tablecomponents reference into data(currentcomponent).
missingcolumns = missingcolumns && |, { currentcomponent->*-name }|.
endloop.
if not invalidcolumn is initial.
raise exception type lcx_configuration
exporting
text = |Find invalid columns: { invalidcolumn } |.
endif.
First row will be the table columns’ name
Local Class Definition
*&---------------------------------------------------------------------*
*& Include ZZZZ_HOME_CFG_FINE_TUNING_LCL
*&---------------------------------------------------------------------*
class lcx_configuration definition
inheriting from cx_static_check.
public section.
data local_text type string.
methods constructor importing text type string.
endclass.
class lcx_configuration implementation.
method constructor.
super->constructor( ).
local_text = text.
endmethod.
endclass.
*&---------------------------------------------------------------------*
*& Class lcl_configuration
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
class lcl_configuration definition.
public section.
data filefullpath type string.
data tablename type string.
data sqlscript type string.
data tableinfo type tadir.
data tablestructure type ref to cl_abap_structdescr.
data tabletype type ref to cl_abap_tabledescr.
data tabledata type ref to data.
class-methods validate_sql_script
changing sqlscript type string.
class-methods validate_table
changing checkedtablename type string
raising lcx_configuration.
methods constructor "Constructore method
importing filefullpath type string
tablename type string
sqlscript type string
raising lcx_configuration.
methods import raising lcx_configuration.
methods export raising lcx_configuration.
protected section.
methods get_filecontent
returning value(filecontent) type xstring
raising lcx_configuration.
methods extract_data_from_table
raising lcx_configuration.
methods check_file
raising lcx_configuration.
methods extract_data_from_excel
raising lcx_configuration.
methods get_tablecontent
exporting tablecontent type any table
raising lcx_configuration.
private section.
endclass.
class lcl_configuration implementation.
method constructor.
if filefullpath is initial or tablename is initial.
raise exception type lcx_configuration
exporting
text = |File Name { filefullpath } and Table Name { tablename } should be provided|.
endif.
me->filefullpath = filefullpath.
me->tablename = tablename.
me->sqlscript = sqlscript.
lcl_configuration=>validate_table( changing checkedtablename = me->tablename ).
me->tablestructure ?= cl_abap_typedescr=>describe_by_name( me->tablename ).
if not me->tablestructure is bound.
raise exception type lcx_configuration
exporting
text = |Exception occurs when parsing Table Structure for { tablename } |.
endif.
try.
me->tabletype = cl_abap_tabledescr=>create( p_line_type = me->tablestructure ).
catch cx_sy_table_creation into data(tabletypeexception).
raise exception type lcx_configuration
exporting
text = |Exception occurs when parsing Table Type for { tablename } |.
endtry.
create data tabledata type handle me->tabletype.
endmethod.
method import.
"Update DDIC table content from (client PC) excel file
me->extract_data_from_excel( ).
field-symbols <finaltabledata> type standard table.
data finaltabledata type ref to data.
create data finaltabledata type handle me->tabletype.
assign finaltabledata->* to <finaltabledata>.
field-symbols <tabledata> type standard table.
assign me->tabledata->* to <tabledata>.
loop at <tabledata> assigning field-symbol(<currenttabledata>).
assign component 'MANDT' of structure <currenttabledata> to field-symbol(<lv_client>).
if sy-subrc = 0.
<lv_client> = ''.
if not <currenttabledata> is initial.
<lv_client> = sy-mandt.
append <currenttabledata> to <finaltabledata>.
else.
"delete <tabledata> from <currenttabledata>.
endif.
else.
if <currenttabledata> is initial.
"delete <tabledata> from <currenttabledata>.
endif.
endif.
endloop.
data(checkedtablename) = me->tablename.
lcl_configuration=>validate_table( changing checkedtablename = checkedtablename ).
if not <finaltabledata> is initial.
modify (checkedtablename) from table <finaltabledata>.
"break-point.
if sy-subrc <> 0.
rollback work.
raise exception type lcx_configuration
exporting
text = |Exception occurs when modifying table: { tablename } |.
else.
message s001(00) with |Table: { tablename } is modified successfully.|.
endif.
endif.
endmethod.
method export.
"Create client PC excel file from DDIC table
data(filecontent) = me->get_filecontent( ).
cl_scp_change_db=>xstr_to_xtab( exporting im_xstring = filecontent
importing ex_xtab = data(filecontenttab) ).
cl_gui_frontend_services=>gui_download(
exporting
bin_filesize = xstrlen( filecontent )
filename = |{ me->filefullpath }|
filetype = 'BIN'
confirm_overwrite = abap_true
importing
filelength = data(bytestransferred)
changing
data_tab = filecontenttab
exceptions
file_write_error = 1
no_batch = 2
gui_refuse_filetransfer = 3
invalid_type = 4
no_authority = 5
unknown_error = 6
header_not_allowed = 7
separator_not_allowed = 8
filesize_not_allowed = 9
header_too_long = 10
dp_error_create = 11
dp_error_send = 12
dp_error_write = 13
unknown_dp_error = 14
access_denied = 15
dp_out_of_memory = 16
disk_full = 17
dp_timeout = 18
file_not_found = 19
dataprovider_exception = 20
control_flush_error = 21
not_supported_by_gui = 22
error_no_gui = 23
others = 24
).
if sy-subrc <> 0.
message id sy-msgid type sy-msgty number sy-msgno
with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
else.
message s001(00) with bytestransferred ' bytes transferred'.
endif.
endmethod.
method get_filecontent.
me->extract_data_from_table( ).
if me->tabledata is initial.
raise exception type lcx_configuration
exporting
text = |Table { tablename } has no entry.|.
endif.
"Get file content from table
clear filecontent.
try.
data(xlsx_handling) = cl_ehfnd_xlsx=>get_instance( ).
data(xlsx_document) = xlsx_handling->create_doc( ).
data(xlsx_sheets) = xlsx_document->get_sheets( ).
data(first_xlsx_sheet) = xlsx_document->get_sheet_by_id( xlsx_sheets[ 1 ]-sheet_id ).
first_xlsx_sheet->change_sheet_name( 'Data' ).
data(lv_column) = 1.
loop at me->tablestructure->components reference into data(component).
first_xlsx_sheet->set_cell_content( iv_row = 1 iv_column = lv_column iv_value = component->name ).
lv_column = lv_column + 1.
endloop.
data(lv_row) = 2.
field-symbols <tabledata> type standard table.
assign me->tabledata->* to <tabledata>.
loop at <tabledata> assigning field-symbol(<currenttabledata>).
lv_column = 1.
loop at me->tablestructure->components reference into component.
assign component component->name of structure <currenttabledata> to field-symbol(<columnvalue>).
first_xlsx_sheet->set_cell_content( iv_row = lv_row iv_column = lv_column iv_value = <columnvalue> ).
lv_column = lv_column + 1.
endloop.
lv_row = lv_row + 1.
endloop.
filecontent = xlsx_document->save( ).
catch cx_openxml_format into data(openxml_format_exception).
raise exception type lcx_configuration
exporting
text = |Error occurs when constructing excel file instance. cx_openxml_format|.
catch cx_openxml_not_found into data(openxml_not_found_exception).
raise exception type lcx_configuration
exporting
text = |Error occurs when constructing excel file instance. CX_OPENXML_NOT_FOUND |.
catch cx_openxml_not_allowed into data(openxml_not_allowed_exception).
raise exception type lcx_configuration
exporting
text = |Error occurs when constructing excel file instance. CX_OPENXML_NOT_ALLOWED |.
endtry.
endmethod.
method get_tablecontent.
"Get table content from file
endmethod.
method validate_table.
"raise exception if table does not exist
select single * from tadir into @data(tableinfo) where obj_name = @checkedtablename and object = 'TABL'. "#EC CI_GENBUFF.
if sy-subrc <> 0.
raise exception type lcx_configuration
exporting
text = |Table { checkedtablename } does not exist.|.
endif.
try.
checkedtablename =
cl_abap_dyn_prg=>check_table_or_view_name_str(
val = checkedtablename
packages = conv #( tableinfo-devclass )
incl_sub_packages = abap_true
).
catch cx_abap_not_a_table
cx_abap_not_in_package.
return.
endtry.
endmethod.
method extract_data_from_table.
data sql_script type string.
data checkedtablename type string.
sql_script = me->sqlscript.
checkedtablename = me->tablename.
lcl_configuration=>validate_sql_script( changing sqlscript = sql_script ).
lcl_configuration=>validate_table( changing checkedtablename = checkedtablename ).
field-symbols <tabledata> type standard table.
assign tabledata->* to <tabledata>.
if me->sqlscript is initial.
select * from (checkedtablename) into table <tabledata>.
else.
select * from (checkedtablename) into table <tabledata> where (sql_script).
endif.
endmethod.
method validate_sql_script.
if sqlscript is initial.
return.
endif.
sqlscript = replace( val = sqlscript
sub = `'`
with = `''`
occ = 0 ).
concatenate `'` sqlscript `'` into sqlscript separated by space.
try.
sqlscript =
cl_abap_dyn_prg=>check_char_literal( sqlscript ).
data(lv_len) = strlen( sqlscript ) - 2.
sqlscript = sqlscript+1(lv_len).
sqlscript = replace( val = sqlscript
sub = `''`
with = `'`
occ = 0 ).
catch cx_abap_invalid_value into data(lo_exception).
clear sqlscript.
endtry.
endmethod.
method check_file.
endmethod.
method extract_data_from_excel.
field-symbols <exceldata> type standard table.
assign me->tabledata->* to <exceldata>.
data(xlsxhandler) = cl_ehfnd_xlsx=>get_instance( ).
check not xlsxhandler is initial.
try.
data(xstring_excel) = cl_openxml_helper=>load_local_file( me->filefullpath ).
catch cx_openxml_not_found into data(openxml_not_found).
return.
endtry.
try.
data(xlsxdocument) = xlsxhandler->load_doc( iv_file_data = xstring_excel ).
catch cx_openxml_format into data(openxml_format).
return.
catch cx_openxml_not_allowed into data(openxml_not_allowed).
return.
catch cx_dynamic_check into data(dynamic_check).
return.
endtry.
"extract data from first sheet
try.
data(firstsheet) = xlsxdocument->get_sheet_by_id( iv_sheet_id = 1 ).
catch cx_openxml_format into openxml_format.
raise exception type lcx_configuration
exporting
text = |Error occurs when extract data from first sheet: CX_OPENXML_FORMAT |.
catch cx_openxml_not_found into openxml_not_found.
raise exception type lcx_configuration
exporting
text = |Error occurs when extract data from first sheet: OPENXML_NOT_FOUND |.
catch cx_dynamic_check into dynamic_check.
raise exception type lcx_configuration
exporting
text = |Error occurs when extract data from first sheet: CX_DYNAMIC_CHECK |.
endtry.
"return if no sheet in xlsx file
check not firstsheet is initial.
"check file structure, first line of excel file
data(columncount) = firstsheet->get_last_column_number_in_row( 1 ).
data column type i value 1.
"data tablecomponents type cl_abap_structdescr=>component_table.
data(tablecomponents) = me->tablestructure->get_components( ).
data invalidcolumn type string.
types: begin of columninfo,
column type i,
columnname type string,
end of columninfo.
types columnsinfo type standard table of columninfo with empty key.
data columnfromfile type columnsinfo.
do columncount times.
data(cellvalue) = firstsheet->get_cell_content(
exporting
iv_row = 1
iv_column = column ).
append initial line to columnfromfile assigning field-symbol(<columnfromfile>).
<columnfromfile>-column = column.
<columnfromfile>-columnname = cellvalue.
if line_exists( tablecomponents[ name = cellvalue ] ).
delete tablecomponents where name = cellvalue.
else.
invalidcolumn = invalidcolumn && |,{ cellvalue }|.
endif.
column = column + 1.
enddo.
data missingcolumns type string.
loop at tablecomponents reference into data(currentcomponent).
missingcolumns = missingcolumns && |, { currentcomponent->*-name }|.
endloop.
if not invalidcolumn is initial.
raise exception type lcx_configuration
exporting
text = |Find invalid columns: { invalidcolumn } |.
endif.
if not missingcolumns is initial.
raise exception type lcx_configuration
exporting
text = |Columns do not exist in excel file: { missingcolumns } |.
endif.
tablecomponents = me->tablestructure->get_components( ).
data(rowcount) = firstsheet->get_last_row_number( ).
data currentrow type i value 2.
while currentrow <= rowcount.
append initial line to <exceldata> assigning field-symbol(<currentrow>).
loop at columnfromfile reference into data(currentcolumn).
cellvalue = firstsheet->get_cell_content(
exporting
iv_row = currentrow
iv_column = currentcolumn->*-column ).
assign component currentcolumn->*-columnname of structure <currentrow> to field-symbol(<cellvalue>).
<cellvalue> = cellvalue.
endloop.
currentrow = currentrow + 1.
endwhile.
endmethod.
endclass.
CL_EHFND_XLSX not available in AS ABAP 7.52 sp03
ReplyDelete