Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Read Excel file from application server and convert it to internal table

mabace
Explorer
0 Kudos
3,038

I need to Read Excel file from application server and convert it to internal table

4 REPLIES 4

mabace
Explorer
0 Kudos
2,774

I have to 2 options:

The Excel file have this structure:

| Object | ClaCoste | Enero | Febrero | Marzo |

Option 1 Large:

*Constants
  CONSTANTS: c_logical_filename_ftappl_2 LIKE filename-fileintern
   VALUE 'EHS_FTAPPL_2',
             c_codepage                  TYPE abap_encod VALUE '1160',
             c_sheet_xml                 TYPE i VALUE 2,
             c_shared_str_xml            TYPE i VALUE 3,
             c_filefmt_binary            TYPE rlgrap-filetype VALUE 'BIN'.
*Global data
  DATA:g_ex_root TYPE REF TO cx_root,
       g_msg     TYPE string.

*Estructura base para Carga de archivos de Planificación
TYPES: BEGIN OF gty_input,
         objeto    TYPE kstar,
         cla_coste TYPE kstar,
         ENERO     TYPE string,
         FEBRERO   TYPE string,
         MARZO     TYPE string,
       END OF gty_input.

TYPES: gtyd_input TYPE STANDARD TABLE OF gty_input.



FUNCTION zexcel_to_abap.
*"----------------------------------------------------------------------
*"*"Interfase local
*"  IMPORTING
*"     REFERENCE(I_FILE) TYPE  RCGIEDIAL-IEFILE OPTIONAL
*"     REFERENCE(I_LOG_FILENAME) TYPE  FILEINTERN OPTIONAL
*"  EXPORTING
*"     REFERENCE(E_FILE_SIZE) TYPE  DRAO-ORLN
*"     REFERENCE(E_LINES) TYPE  I
*"  CHANGING
*"     REFERENCE(CH_SHEET_DATA) TYPE  XSTRING
*"     REFERENCE(CH_SHARED_DATA) TYPE  XSTRING
*"  EXCEPTIONS
*"      NO_PERMISSION
*"      OPEN_FAILED
*"      READ_ERROR
*"      PATH_ERROR
*"----------------------------------------------------------------------
  DATA: l_log_filename TYPE fileintern,
        l_stack_tab    TYPE sys_callst,
        l_stack_wa     TYPE sys_calls.

  DATA : l_len TYPE sy-tabix.
  DATA : l_filename TYPE authb-filename.
  DATA l_subrc TYPE sy-subrc.
  DATA: l_lines TYPE i.
  DATA: l_xstring TYPE xstring.
  DATA lo_package TYPE REF TO cl_openxml_package.
  DATA lo_parts TYPE REF TO cl_openxml_partcollection.
  DATA l_sheet_data TYPE xstring.
  DATA l_shared_data TYPE xstring.
  DATA: li_e_rcgrepfile_tab TYPE cpt_x255,
        li_xtab             TYPE cpt_x255.
  DATA lst_rcgrepfile TYPE cps_x255.
  DATA: lo_xml_part     TYPE REF TO cl_openxml_part,
        lo_xml_part_uri TYPE REF TO cl_openxml_parturi,
        lx_root         TYPE REF TO cx_root,
        l_uri           TYPE string.
  DATA: l_file_content TYPE xstring.


* function body --------------------------------------------------------

* assign value
  l_filename = i_file.

* check the authority for file
  CALL FUNCTION 'AUTHORITY_CHECK_DATASET'
    EXPORTING
*     PROGRAM          =
      activity         = sabc_act_read
* Authority Check allows right now only 60 Character
      filename         = l_filename(60)
    EXCEPTIONS
      no_authority     = 1
      activity_unknown = 2
      OTHERS           = 3.

  IF sy-subrc <> 0.
    RAISE no_permission.
  ENDIF.

  l_log_filename = c_logical_filename_ftappl_2.

  TRY.

* read the raw-file from the appl.server
      CLEAR l_subrc.

      OPEN DATASET i_file FOR INPUT IN BINARY MODE.
      l_subrc = sy-subrc.
      IF sy-subrc <> 0 OR
      l_subrc <> 0.
        RAISE open_failed.
      ENDIF.
      DO.
        CLEAR l_len.
        CLEAR lst_rcgrepfile.
        READ DATASET i_file INTO lst_rcgrepfile LENGTH l_len.
        IF sy-subrc <> 0.
          IF l_len > 0.
            e_file_size = e_file_size + l_len.
            APPEND lst_rcgrepfile TO li_e_rcgrepfile_tab.
          ENDIF.
          EXIT.
        ENDIF.
        CONCATENATE l_file_content lst_rcgrepfile
        INTO l_file_content IN BYTE MODE.
        e_file_size = e_file_size + l_len.
        APPEND lst_rcgrepfile TO li_e_rcgrepfile_tab.
      ENDDO.

      IF sy-subrc > 10.
        RAISE read_error.
      ENDIF.
      DESCRIBE TABLE li_e_rcgrepfile_tab LINES e_lines.
      CLOSE DATASET i_file.

      IF li_e_rcgrepfile_tab[] IS NOT INITIAL.
        li_xtab[] = li_e_rcgrepfile_tab[].
      ENDIF.

*Convert data to xstring
      cl_scp_change_db=>xtab_to_xstr( EXPORTING im_xtab = li_xtab
      im_size = l_lines
      IMPORTING ex_xstring = l_xstring ).

*load document
      lo_package = cl_xlsx_document=>load_document( iv_data = l_xstring ).

*Get parts
      lo_parts = lo_package->get_parts( ).

*Load XML data
      l_uri = lo_parts->get_part( 2 )->get_parts( )->get_part( c_sheet_xml )->get_uri( )->get_uri( ).
      lo_xml_part_uri = cl_openxml_parturi=>create_from_partname( l_uri ).
      lo_xml_part = lo_package->get_part_by_uri( lo_xml_part_uri ).
      ch_sheet_data = lo_xml_part->get_data( ).

*Load sheet data
      CLEAR l_uri.
      l_uri = lo_parts->get_part( 2 )->get_parts( )->get_part( c_shared_str_xml )->get_uri( )->get_uri( ).
      lo_xml_part_uri = cl_openxml_parturi=>create_from_partname( l_uri ).
      lo_xml_part = lo_package->get_part_by_uri( lo_xml_part_uri ).
      ch_shared_data = lo_xml_part->get_data( ).

    CATCH cx_root INTO g_ex_root.

      CLEAR g_msg.
      g_msg = g_ex_root->get_text( ).
      IF g_msg IS NOT INITIAL.
        sy-subrc = 4.
      ENDIF.

  ENDTRY.

**Transform XML data to internal table with help of XSLT transformation –

*Local data declaration
  DATA lo_shared_str_dom TYPE REF TO if_ixml_document.
  DATA lo_shared_str_nodeset TYPE REF TO if_ixml_node.
  DATA l_shared_str_xml TYPE xstring.

  DATA: XSLT_ERR   TYPE REF TO CX_XSLT_EXCEPTION,
      ERR_STRING TYPE STRING.

*Converting XML into internal table
  TRY.
      CALL TRANSFORMATION z_transform_excel
      SOURCE XML ch_shared_data
*      SOURCE XML g_shared_data
      RESULT XML l_shared_str_xml.

*XML to ABAP
      CALL FUNCTION 'SDIXML_XML_TO_DOM'
        EXPORTING
          xml           = l_shared_str_xml
        IMPORTING
          document      = lo_shared_str_dom
        EXCEPTIONS
          invalid_input = 1
          OTHERS        = 2.

      IF sy-subrc = 0.
        lo_shared_str_nodeset = lo_shared_str_dom->clone( ).
      ENDIF.


*Import data

      DATA: ltd_input TYPE gtyd_input.

      CALL TRANSFORMATION z_trans_import_xls
      PARAMETERS p_shared_string = lo_shared_str_nodeset
      SOURCE XML ch_sheet_data
      RESULT lt_data = ltd_input .

      BREAK-POINT.
      .
* catch any error, very helpful if the XSLT isn't correct
    CATCH cx_xslt_exception INTO xslt_err.
      err_string = xslt_err->get_text( ).
      WRITE: / 'Transformation error: ', err_string.
      EXIT.

  ENDTRY.

Transaction: XSLT_TOOL

Transformation: Z_TRANSFORM_EXCEL

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ss="
http://schemas.openxmlformats.org/spreadsheetml/2006/main"; version="1.1">

  <xsl:strip-space elements="*"/>
  <xsl:output encoding="utf-8" indent="yes" method="xml" omit-xmldeclaration="yes"/>

  <xsl:template match="/">

    <xsl:element name="sst" namespace="">
      <xsl:for-each select="ss:sst/ss:si">
        <xsl:element name="si" namespace="">

          <xsl:element name="t" namespace="">
            <xsl:value-of select="ss:t"/>
          </xsl:element>

        </xsl:element>

      </xsl:for-each>

    </xsl:element>

  </xsl:template>

</xsl:stylesheet>

Transformation: Z_TRANS_IMPORT_XLS

<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ss="h
ttp://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:sap="http:/
/www.sap.com/sapxsl" xmlns:asx="http://www.sap.com/abapxml" exclude-resultprefixes="c"
version="1.0">
  <xsl:param name="P_SHARED_STRING" select=""/>
  <xsl:strip-space elements="*"/>
  <xsl:output encoding="utf-8" indent="yes" omit-xml-declaration="yes"/>

  <xsl:variable name="V_SHARED_STRING">
    <xsl:if test="$P_SHARED_STRING">
      <xsl:copy-of select="$P_SHARED_STRING"/>
    </xsl:if>
  </xsl:variable>

  <xsl:template match="/">

    <asx:abap version="1.0">

      <asx:values>

        <LT_DATA>

          <xsl:for-each select="ss:worksheet/ss:sheetData/ss:row">

            <xsl:if test="position() > 1">

              <item>

                <OBJETO>
                  <xsl:variable name="cell_id" select="concat('A', position())"/>
                  <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
                  <xsl:if test="$v_index">
                    <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
                  </xsl:if>
                  <xsl:if test="not($v_index)">
                    <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
                  </xsl:if>
                </OBJETO>


                <CLA_COSTE>
                  <xsl:variable name="cell_id" select="concat('B', position())"/>
                  <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
                  <xsl:if test="$v_index">
                    <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
                  </xsl:if>
                  <xsl:if test="not($v_index)">
                    <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
                  </xsl:if>
                </CLA_COSTE>

                <ENERO>
                  <xsl:variable name="cell_id" select="concat('C', position())"/>
                  <xsl:variable as="xs:decimal" name="v_index"/>
                  <!--                  <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>-->
                  <xsl:if test="$v_index">
                    <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
                  </xsl:if>
                  <xsl:if test="not($v_index)">
                    <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
                  </xsl:if>
                </ENERO>

                <!--                <ENERO>
                  <xsl:variable name="cell_id" select="concat('C', position())"/>
                  <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
                  <xsl:if test="$v_index">
                    <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
                  </xsl:if>
                  <xsl:if test="not($v_index)">
                    <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
                  </xsl:if>
                </ENERO>-->

                <FEBRERO>
                  <xsl:variable name="cell_id" select="concat('D', position())"/>
                  <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
                  <xsl:if test="$v_index">
                    <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
                  </xsl:if>
                  <xsl:if test="not($v_index)">
                    <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
                  </xsl:if>
                </FEBRERO>

                <MARZO>
                  <xsl:variable name="cell_id" select="concat('E', position())"/>
                  <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
                  <xsl:if test="$v_index">
                    <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
                  </xsl:if>
                  <xsl:if test="not($v_index)">
                    <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
                  </xsl:if>
                </MARZO>

              </item>

            </xsl:if>

          </xsl:for-each>

        </LT_DATA>

      </asx:values>

    </asx:abap>
  </xsl:template>
</xsl:transform>

Option 2 Short:


TYPES: BEGIN OF gty_data,
         objeto    TYPE j_objnr, "CeCo / CeBe / Orden
         cla_coste TYPE kstar,
         enero     TYPE coej-wkg001,
         febrero   TYPE coej-wkg002,
         marzo     TYPE coej-wkg003,
       END OF gty_data.

TYPES: gtyd_data TYPE STANDARD TABLE OF gty_data.
data ltd_data type gtyd_data.

  TYPES: BEGIN OF lty_input,
           objeto    TYPE string,
           cla_coste TYPE string,
           enero     TYPE string,
           febrero   TYPE string,
           marzo     TYPE string,
         END OF lty_input.

  DATA ltd_itab_string TYPE STANDARD TABLE OF lty_input.

  DATA: lv_file     TYPE string,
        lv_xls_xstr TYPE xstring.

  DATA lv_importe TYPE char20.


  lv_file = '/usr/sap/trans/ExcelFile.xlsx'.

* Leer archivo de Servidor de Aplicaciones
  OPEN DATASET lv_file FOR INPUT IN BINARY MODE .
  IF sy-subrc EQ 0.
    READ DATASET lv_file INTO lv_xls_xstr.
    IF sy-subrc NE 0.
*    MESSAGE e002 WITH lv_file.
    ENDIF.
  ELSE.
*  MESSAGE e001 WITH lv_file.
  ENDIF.
  CLOSE DATASET lv_file.


* Crear objeto Spreadsheet document
  DATA(lo_xlsx) = NEW cl_fdt_xl_spreadsheet( document_name = lv_file
                                             xdocument     = lv_xls_xstr ).

* Obtener tabla con Hojas del Libro Excel
  lo_xlsx->if_fdt_doc_spreadsheet~get_worksheet_names(
    IMPORTING
      worksheet_names = DATA(lt_excel)
  ).

* Tomar primera hoja del Libro Excel
  READ TABLE lt_excel INTO DATA(ls_excel) INDEX 1.

* Obtener Tabla de la primera hoja del Libro Excel
  DATA(ir_ref) = lo_xlsx->if_fdt_doc_spreadsheet~get_itab_from_worksheet( ls_excel  ) .
  ASSIGN ir_ref->* TO FIELD-SYMBOL(<lfs_data_tab>).

* Copiar Tabla
  ltd_itab_string = <lfs_data_tab>.

* Eliminar fila de cabecera
  DELETE ltd_itab_string INDEX 1.

  LOOP AT ltd_itab_string ASSIGNING FIELD-SYMBOL(<lwa_itab_string>).

    APPEND INITIAL LINE TO ltd_data ASSIGNING FIELD-SYMBOL(<lwa_input>).

    <lwa_input>-objeto    = <lwa_itab_string>-objeto.
    <lwa_input>-cla_coste = <lwa_itab_string>-cla_coste.

    CLEAR lv_importe.
    WRITE <lwa_itab_string>-enero     TO lv_importe.
    CONDENSE lv_importe NO-GAPS.
    <lwa_input>-enero     = lv_importe.

    CLEAR lv_importe.
    WRITE <lwa_itab_string>-febrero   TO lv_importe.
    CONDENSE lv_importe NO-GAPS.
    <lwa_input>-febrero   = lv_importe.

    CLEAR lv_importe.
    WRITE <lwa_itab_string>-marzo     TO lv_importe.
    CONDENSE lv_importe NO-GAPS.
    <lwa_input>-marzo     = lv_importe.

  ENDLOOP.

Sandra_Rossi
Active Contributor
0 Kudos
2,774

Please add the code in your question (menu Actions > Edit).

NB: you didn't ask a question.

Please use the COMMENT button for comments, asking for complements, adding details, replying to a comment or a proposed solution or to the OP question, etc., ANSWER is only to propose a solution, dixit SAP text at the right of the answer area.