Skip to Content
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:

  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.

 

Conclusion

Thank you for your interest in XLSX2ABAP and ABAP2XLSX. Example code is tested on SAP_BASIS 755, S4CORE 105.

9 Comments
You must be Logged on to comment or reply to a post.
  • 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“.

    • 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