Skip to Content

prerequisite : NW 7.02

The first thing is to get a xstring (hexadecimal string) of the datafile.

From dataserver is easy with open dataset in binary mode

in BSP  is mode difficult :

data: fu  type ref to cl_htmlb_fileupload.

         fu ?= cl_htmlb_manager=>get_data( request = request id = ‘FILE_UP1’ name = ‘fileUpload’ ).

         if  fu->file_name  NE ”  AND fu->file_length GT 0 .

           if fu->file_content_type = ‘application/vnd.ms-excel’ or

              fu->file_content_type = ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’ or

              fu->file_content_type = ‘application/vnd.ms-excel.sheet.macroEnabled.12’.

             CONCATENATE ‘\\’ syHOST ‘\DATAFTP\Budget\IN\Budget.csv’ into l_file .

*            Get the data into internal table GT_RESULTAT
             CALL METHOD application->IMPORTATION

               EXPORTING

                 P_IN_XRESULTAT  = fu->file_content

                 P_IN_FILE       = l_file

               CHANGING

                 PT_OUT_RESULTAT = GT_RESULTAT

                 PT_OUT_ERROR    = GT_ERREUR

               EXCEPTIONS

                 PB_DOC          = 1

                 PB_PARTIES_DOC  = 2

                 PB_TRANSFO1     = 3

                 PB_TRANSFO2     = 4

                 others          = 5.

             IF SYSUBRC <> 0.

               GS_ERREUR = ‘ERREUR : Les données de l’‘onglet n°1 sont incorrectes.’.

            else.

               GS_ERREUR = ‘Les données ont été correctement transférées à BW.’.

             endif.

           else.

             if  fu->file_content_type = ‘application/octet-stream’.

               GS_ERREUR = ‘ERREUR : Le fichier est toujours ouvert.’.

             else.

               GS_ERREUR = ‘ERREUR : Le fichier doit être de type Excel.’.

             endif.

           endif.

         else.

           GS_ERREUR = ‘ERREUR : Le fichier doit être valide.’.

         endif.

method IMPORTATION.

/wp-content/uploads/2012/12/importation_166916.png

refresh PT_OUT_RESULTAT.

* Xlsx file is a collection of zip’s xml files

* first => unzip the package

  data: o_pack_doc type REF TO cl_xlsx_document.

  TRY .

      o_pack_doc = cl_xlsx_document=>load_document( iv_data = P_IN_XRESULTAT ).

    CATCH cx_openxml_format cx_openxml_not_found.

      RAISE PB_DOC.

  ENDTRY.

* gets all the various parts of the document


  data: o_parties_doc type REF TO CL_OPENXML_PARTCOLLECTION.

  TRY.

      o_parties_doc = o_pack_doc->get_parts( ).

    CATCH cx_openxml_format cx_openxml_not_found.

      RAISE PB_PARTIES_DOC.

  ENDTRY.

* I want to retrieve the first tab always in second position


  data: my_sheet_xml       type i ,

        my_shared_str_xml  type i .

  data: ls_sheet_data type xstring.

  CALL METHOD me->LOAD_XML_DATA

    EXPORTING

      P_IN_PARTS     = o_parties_doc

      P_IN_PACK_DOC  = o_pack_doc

      P_IN_SHEET_LIB = ‘sheet2.xml’

    CHANGING

      P_OUT_DATA     = ls_sheet_data

      P_OUT_SHEET    = my_sheet_xml.

* The strings are in a separate xml file

  data: ls_shared_str_xml type xstring.

  CALL METHOD me->LOAD_XML_DATA

    EXPORTING

      P_IN_PARTS     = o_parties_doc

      P_IN_PACK_DOC  = o_pack_doc

      P_IN_SHEET_LIB = ‘sharedstrings.xml’

    CHANGING

      P_OUT_DATA     = ls_shared_str_xml

      P_OUT_SHEET    = my_shared_str_xml.

* get the data with Transformation (source at the end of this post)

data: lo_shared_str_dom      type REF TO if_ixml_document,

        lo_shared_str_nodeset  type REF TO if_ixml_node,

        lv_shared_str_xml      type xstring.

  TRY.

      call TRANSFORMATION  zcos_shared_string_del_namesp2

             source  XML ls_shared_str_xml

             result  XML lv_shared_str_xml.

    CATCH cx_xslt_exception.

      RAISE PB_TRANSFO1.

  ENDTRY.

  CALL FUNCTION ‘SDIXML_XML_TO_DOM’

    EXPORTING

      XML           = lv_shared_str_xml

    IMPORTING

      DOCUMENT      = lo_shared_str_dom

    EXCEPTIONS

      INVALID_INPUT = 1

      OTHERS        = 2.

  if sysubrc = 0.

    lo_shared_str_nodeset = lo_shared_str_dom->clone( ).

  endif.

  TRY.

      call TRANSFORMATION  zcos_xlsx_sheet_import2

           PARAMETERS      p_shared_string = lo_shared_str_nodeset

           source          xml       ls_sheet_data

           result lt_result = PT_OUT_RESULTAT.

    CATCH cx_xslt_exception.

      RAISE PB_TRANSFO2.

  ENDTRY.

endmethod.

method LOAD_XML_DATA.

/wp-content/uploads/2012/12/load_xml_data_166918.png

  data: lo_xml_part      type REF TO cl_openxml_part,

        lo_xml_part_uri  type REF TO cl_openxml_parturi,

        lv_uri           type string,

        l_part1          type REF TO cl_openxml_part,

        l_part2          type REF TO cl_openxml_part,

        l_parts1         type REF TO cl_openxml_partcollection,

        l_part_uri1      type REF TO cl_openxml_parturi,

        li_parts         type i.

  try.

      l_part1        = P_IN_PARTS->GET_PART( 2 ).

      l_parts1       = l_part1->get_parts( ).

      li_parts       = l_parts1->get_count( ).

      P_OUT_SHEET    = 0.

      while  P_OUT_SHEET <= li_parts.

        l_part2      = l_parts1->GET_PART( P_OUT_SHEET ).

        l_part_uri1  = l_part2->get_uri( ).

        lv_uri       = l_part_uri1->get_uri( ).

        if lv_uri cs P_IN_SHEET_LIB.

          exit.

        endif.

        P_OUT_SHEET  = P_OUT_SHEET + 1.

      endwhile.

      lo_xml_part_uri = cl_openxml_parturi=>create_from_partname( lv_uri ).

      lo_xml_part     = P_IN_PACK_DOC->get_part_by_uri( lo_xml_part_uri ).

      P_OUT_DATA      = lo_xml_part->get_data( ).

    CATCH cx_openxml_format cx_openxml_not_found.

  ENDTRY.

endmethod.

Transformation :  zcos_shared_string_del_namesp2

<xsl:stylesheet xmlns:xsl=http://www.w3.org/1999/XSL/Transform” xmlns:ss=http://schemas.openxmlformats.org/spreadsheetml/2006/mainversion=“1.1”>

  <xsl:strip-space elements=“*”/>

  <xsl:output encoding=“utf-8” indent=“yes” method=“xml” omit-xml-declaration=“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 :  zcos_xlsx_sheet_import2

This transformation load the PT_OUT_RESULTAT internal table

structure :

ANNEE                Types CHAR4

SOCIETE             Types CHAR4

VERSION             Types CHAR2

ID_OPERA           Types CHAR1

ID_CP                   Types CHAR15

CP_LIB                Types CHAR50

ID_CC           Types CHAR15

CC_LIB           Types CHAR50

ID_EOTP           Types CHAR24

EOTP_LIB           Types CHAR50

ID_IO2           Types CHAR15

IO2_LIB           Types CHAR50

UNITE           Types CHAR3

JANVIER           Types CHAR20

FEVRIER           Types CHAR20

MARS           Types CHAR20

AVRIL                Types CHAR20

MAI                Types CHAR20

JUIN T               ypes CHAR20

JUILLET                Types CHAR20

AOUT                Types CHAR20

SEPTEMBRE      Types CHAR20

OCTOBRE           Types CHAR20

NOVEMBRE           Types CHAR20

DECEMBRE           Types CHAR20

<xsl:transform xmlns:xsl=http://www.w3.org/1999/XSL/Transform” xmlns:ss=http://schemas.openxmlformats.org/spreadsheetml/2006/mainxmlns:sap=http://www.sap.com/sapxslxmlns:asx=http://www.sap.com/abapxmlexclude-result-prefixes=“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_RESULT>

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

            <xsl:if test=“position() &gt; 1”>

              <item>

                <ANNEE>

                  <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>

                </ANNEE>

                <SOCIETE>

                  <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>

                </SOCIETE>

                <VERSION>

                  <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>

                </VERSION>

                <ID_OPERA>

                  <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>

                </ID_OPERA>

                <ID_CP>

                  <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>

                </ID_CP>

                <CP_LIB>

                  <xsl:variable name=“cell_id” select=“concat(‘F’, 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>

                </CP_LIB>

                <ID_CC>

                  <xsl:variable name=“cell_id” select=“concat(‘G’, 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>

                </ID_CC>

                <CC_LIB>

                  <xsl:variable name=“cell_id” select=“concat(‘H’, 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>

                </CC_LIB>

                <ID_EOTP>

                  <xsl:variable name=“cell_id” select=“concat(‘I’, 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>

                </ID_EOTP>

                <EOTP_LIB>

                  <xsl:variable name=“cell_id” select=“concat(‘J’, 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>

                </EOTP_LIB>

                <ID_IO2>

                  <xsl:variable name=“cell_id” select=“concat(‘K’, 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>

                </ID_IO2>

                <IO2_LIB>

                  <xsl:variable name=“cell_id” select=“concat(‘L’, 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>

                </IO2_LIB>

                <UNITE>

                  <xsl:variable name=“cell_id” select=“concat(‘M’, 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>

                </UNITE>

                <JANVIER>

                  <xsl:variable name=“cell_id” select=“concat(‘N’, 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>

                </JANVIER>

                <FEVRIER>

                  <xsl:variable name=“cell_id” select=“concat(‘O’, 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>

                </FEVRIER>

                <MARS>

                  <xsl:variable name=“cell_id” select=“concat(‘P’, 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>

                </MARS>

                <AVRIL>

                  <xsl:variable name=“cell_id” select=“concat(‘Q’, 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>

                </AVRIL>

                <MAI>

                  <xsl:variable name=“cell_id” select=“concat(‘R’, 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>

                </MAI>

                <JUIN>

                  <xsl:variable name=“cell_id” select=“concat(‘S’, 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>

                </JUIN>

                <JUILLET>

                  <xsl:variable name=“cell_id” select=“concat(‘T’, 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>

                </JUILLET>

                <AOUT>

                  <xsl:variable name=“cell_id” select=“concat(‘U’, 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>

                </AOUT>

                <SEPTEMBRE>

                  <xsl:variable name=“cell_id” select=“concat(‘V’, 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>

                </SEPTEMBRE>

                <OCTOBRE>

                  <xsl:variable name=“cell_id” select=“concat(‘W’, 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>

                </OCTOBRE>

                <NOVEMBRE>

                  <xsl:variable name=“cell_id” select=“concat(‘X’, 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>

                </NOVEMBRE>

                <DECEMBRE>

                  <xsl:variable name=“cell_id” select=“concat(‘Y’, 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>

                </DECEMBRE>

              </item>

            </xsl:if>

          </xsl:for-each>

        </LT_RESULT>

      </asx:values>

    </asx:abap>

  </xsl:template>

</xsl:transform>

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply