Technical Articles
Excel file (*.xlsx) Export/Import
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 on ABAP AS 752 SP-Level 0004, Software Component S4CORE Release 102, SP 4.).
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:
- Download any DDIC table’s content into xlsx file
- Change value in the xlsx file
- upload xlsx file into DDIC table
- 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.
Conclusion
Thank you for your interest in XLSX2ABAP and ABAP2XLSX. Example code is tested on SAP_BASIS 755, S4CORE 105.
Interesting. Does SAP plan to propagate and extend the CL_XLSX_DOCUMENT? Currently it is not much used and known...
FIY: There is quite successful project, which is already named "ABAP2XLSX".
Thanks a lot. CL_EHFND_XLSX is build on top of CL_XLSX_DOCUMENT. This is my understanding.
What means "propagate and extend"? CL_XLSX_DOCUMENT is available since Netweaver 7.02 and many people used it for a long time.
CL_EHFND_XLSX is not available on ABAP AS 7.52 SP03.
Hello Mihet,
It can be found on ABAP AS 752 SP-Level 0004.
Software Component S4CORE Release 102, SP 4.
Best regards,
Trevor
I am really confused.
Firstly, as mentioned in the last comment, on what release does CL_EHFND_XLSX become available? Bear in mind that something made available internally within SAP in 2014 may not be available to customers until five or six years later.
Secondly are you saying that the functionality ABAP2XLSX provides is going to be re-invented as standard classes inside standard SAP?
For the last few years once a month on average someone from outside of SAP posts a blog on SCN describing a new way they have come up with to do a limited subset of what ABAP2XLSX can do.
This would be because they have never heard of it, and it is quite possible many within SAP itself have never heard of it either.
Cheersy Cheers
Paul
Hello Paul,
It can be found on ABAP AS 752 SP-Level 0004.
Software Component S4CORE Release 102, SP 4.
Best regards,
Trevor
Hello Trevor,
Do you know if any downporting is planned for CL_EHFND_XLSX (eg via sapnote)?
99% of the developers here don't have access to that release. 🙁
Thanks,
Peter
I'm just as confused as Paul Hardy - every month someone posts a blog on SCN reinventing ABAP2XLSX for some reason. Now that a blog is posted by an SAP employee this sends quite a confusing message...
Availability of classes is not driven by year. It's not like every year each SAP customer gets a box of presents at Christmas with all the new classes and goodies from SAP. Availability is driven by the release and support pack level. We're on ABAP 7.31 and don't have class CL_EHFND_XLSX, for example.
And I also would like to know if this means that SAP will be offering ABAP2XLSX as part of standard?
P.S. I'm getting "you don't have rights" when trying to open the link at the end of the blog. When posting on SCN, please make sure to use only what is open to general population and not just at SAP internally.
Thank you.
Hello Jelena,
I update the info of ABAP version accordingly. Row limits help link is removed also.
Thanks a lot.
Best regards,
Trevor
It's pretty good !!
I have some question for you to use this method :).
Can I set cell color or cell width when file export(ITAB to xls) ??
( I found XLSX_DOCUMNT's internal content table(MS_SHEET_DATA).
But, i don`t know how to change cell style ).
Thank you.
Very good one,
is it possible to add the custom properties for excel\word document using
CL_XLSX_DOCUMENT->ADD_CUSTOMPROPERTIESPART for classifier
Regards
Sriharsha
Hi.
Thank you for sharing nice blog.
Is it possible to change download Excel file font?
I want to avoid setting font "Calibri".
Best Regards.
Miho
Maybe abap2xlsx can help you in this case.
Hi,
CL_EHFND_XLSX seems half implemented and abandoned by SAP...
There is no method to remove sheet and the change_sheet_name method is not implemented. How are you using that?
My system is HANA 2020.
Alex
At least abap2xlsx is "officially" released and supported by the community, and it does what you're looking for. That is not the case of CL_EHFND_XLSX.