Saturday 12 September 2020

GOS Excel Attachment into Internal Table

Introduction


We have seen how image or PDF file can be retrieved from GOS attachment. But we have challenges when we try to get the data from Excel file of GOS attachment. Herein , we have a scenario where third Party vendor is sending consolidated PO Invoice with Excel File in GOS Attachments. The corresponding excel file contains the invoice line item details like PO Number, Item Number, Amount, Tax Code, Condition type etc. We can check below how we can read the line item data from GOS excel attachment and update the corresponding line item to be passed into Internal Table for further processing.

Solution


We need to read the GOS Excel Attachment from Archive Object, we can read Excel data into XSTRING value after this we need to convert XSTRING data to XML Format and data is read from XML Transformation to Internal Table.

This we can implemented with  series of below steps:

1. GOS Attachment Excel File exist in VIM DP Document, the relevant object type can be fetched depending on the Business Object from Link Table – TOA01, Object Type – /OPT/V1001, Object Key – Docid, Archive Doc Type – /OPT/OTHER.


2. FM ARCHIVOBJECT_GET_URI used to get the Archive Document Attachment Lists


CONSTANTS : c_objecttype(10) TYPE c VALUE '/OPT/V1001',
c_mimetype TYPE string VALUE 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',

c_archivid(2)    TYPE c VALUE 'MB',

c_doctype(10)    TYPE c VALUE '/OPT/OTHER'.

DATA : gt_uri_table       TYPE STANDARD TABLE OF toauri,

gw_uri_table       TYPE toauri,

gw_messages        TYPE /opt/struct_message, "/OPT/STRUCT_MESSAGE_TABLE_T.

lv_error           TYPE flag,

gt_binarchivobject TYPE STANDARD TABLE OF tbl1024,

lt_xls             TYPE solix_tab,

lv_filesize        TYPE i,

lv_xstr            TYPE xstring,

lv_object_id       TYPE saeobjid,

lv_doctype         TYPE saedoktyp,

lv_archivid        TYPE saearchivi.

**Identify if there is any excel attached to particular Document in GOS Attachments List.

lv_object_id = cv_index_header-docid. (Your Document ID which has GOS Attachments)

CALL FUNCTION 'ARCHIVOBJECT_GET_URI'

EXPORTING

objecttype               = c_objecttype

object_id                = lv_object_id

*       LOCATION                 = 'F'

*       HTTP_URL_ONLY            = ' '

TABLES

uri_table                = gt_uri_table

EXCEPTIONS

error_archiv             = 1

error_communicationtable = 2

error_kernel             = 3

error_http               = 4

error_dp                 = 5

OTHERS                   = 6.

IF sy-subrc = 0.

READ TABLE gt_uri_table INTO gw_uri_table WITH KEY mimetype = c_mimetype.

IF sy-subrc = 0.

**Further processing of Excel File as explained in next steps.

Endif.

Endif.

3. FM ARCHIVOBJECT_GET_TABLE to get binary value of the attached excel file.


lv_archivid = c_archivid.

lv_doctype = c_doctype.

CALL FUNCTION 'ARCHIVOBJECT_GET_TABLE'

EXPORTING

archiv_id                = lv_archivid

document_type            = lv_doctype

archiv_doc_id            = gw_uri_table-arc_doc_id  "Excel Attachment Archive DocID from Step2.

TABLES

binarchivobject          = gt_binarchivobject

EXCEPTIONS

error_archiv             = 1

error_communicationtable = 2

error_kernel             = 3

OTHERS                   = 4.

IF sy-subrc = 0.

**Convert to RAW255

CALL METHOD cl_rmps_general_functions=>convert_1024_to_255

EXPORTING

im_tab_1024 = gt_binarchivobject

RECEIVING

re_tab_255  = lt_xls.

Endif.

4. FM SCMS_BINARY_TO_XSTRING , need to convert RAW to XSTRING value to read the Excel file.


**Read length of File.

DESCRIBE TABLE lt_xls[].

lv_filesize = sy-tfill * sy-tleng.

**Convert Binary Content to XSTRING.

CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'

EXPORTING

input_length = lv_filesize

*               FIRST_LINE   = 0

*               LAST_LINE    = 0

IMPORTING

buffer       = lv_xstr

TABLES

binary_tab   = lt_xls

EXCEPTIONS

failed       = 1

OTHERS       = 2.

IF sy-subrc = 0.

**Processing of Excel File as explained in next steps.

Endif.

5. We need to convert the XSTRING value to XML File and then call the XML data into internal table with help of XSLT Transformation.


**Start - Data Declaration for Excel File Class

DATA : lo_package      TYPE REF TO cl_xlsx_document,

lo_parts        TYPE REF TO cl_openxml_partcollection,

l_uri           TYPE string,

lo_xml_part_uri TYPE REF TO cl_openxml_parturi,

lo_xml_part     TYPE REF TO cl_openxml_part,

ch_sheet_data   TYPE xstring,

ch_shared_data  TYPE xstring,

li_xtab         TYPE cpt_x255,

l_xstring       TYPE xstring,

gw_vimitem      TYPE /opt/vim_1item.

CONSTANTS : c_sheet_xml      TYPE i VALUE 2,

c_shared_str_xml TYPE i VALUE 3.

**End - Data Declaration for Excel File Class

**Start - Data Declaration for XML Transformation

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.

**End - Data Declaration for XML Transformation

**Start - Data Declaration for Internal ITAB

TYPES: BEGIN OF t_tab,

po_number   TYPE ebeln,

po_item     TYPE ebelp,

item_amount TYPE wrbtr,

quantity    TYPE menge_d,

po_unit     TYPE meins,

cond_type   TYPE kschl,

tax_code    TYPE /opt/vim_tax_code,

END OF t_tab.

DATA : i_data1 TYPE STANDARD TABLE OF t_tab,

w_data1 TYPE t_tab.

**End - Data Declaration for Internal ITAB

CLEAR : lo_package, lo_parts, l_uri, lo_xml_part_uri, lo_xml_part, ch_sheet_data.

TRY.

**Read Excel File from XSTRING vale.

*Load document

lo_package = cl_xlsx_document=>load_document( iv_data = lv_xstr ).

*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( ).

RAISE EXCEPTION TYPE cx_openxml_format.

CATCH cx_openxml_format.

CATCH cx_openxml_not_found.

ENDTRY.

*Load sheet data

TRY.

CLEAR : l_uri, lo_xml_part_uri, lo_xml_part, ch_shared_data.

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

RAISE EXCEPTION TYPE cx_openxml_format.

CATCH cx_openxml_format.

ch_shared_data = lo_xml_part->get_data( ).

CATCH cx_openxml_not_found.

ENDTRY.

Transform XML data to internal table with help of XSLT transformation

*Process to read the excel content

*Converting XML into internal table

**Read Shared String

TRY.

CALL TRANSFORMATION yvim_transform_excel

SOURCE XML ch_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

CALL TRANSFORMATION yvim_trans_import_xls

PARAMETERS

p_shared_string = lo_shared_str_nodeset

SOURCE XML ch_sheet_data

RESULT lt_data = i_data1.   "i_data1 - Internal table.

CATCH cx_xslt_exception.

ENDTRY.

6. Next we need to create XSLT Transformations which is being called in program with help of Tcode – XSLT_TOOL.


a. Create Transformation YVIM_TRANSFORM_EXCEL, copy-paste below XML style format.

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

b. Create Transformation YVIM_TRANS_IMPORT_XLS (Displays internal table fields mapped to column headers of incoming Excel File and internal table), copy-past below XML data format.

<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ss="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:sap="http://www.sap.com/sapxsl" xmlns:asx="http://www.sap.com/abapxml" exclude-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_DATA>

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

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

<item>

<PO_NUMBER>

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

</PO_NUMBER>

<PO_ITEM>

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

</PO_ITEM>

<ITEM_AMOUNT>

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

</ITEM_AMOUNT>

<QUANTITY>

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

</QUANTITY>

<PO_UNIT>

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

</PO_UNIT>

<COND_TYPE>

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

</COND_TYPE>

<TAX_CODE>

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

</TAX_CODE>

</item>

</xsl:if>

</xsl:for-each>

</LT_DATA>

</asx:values>

</asx:abap>

</xsl:template>

</xsl:transform>

No comments:

Post a Comment