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() > 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>
Conclusion:
You can get your Excel File data of GOS Attachment into internal table(i_data1) which can be used for further processing as needed.
