2023 May 23 7:50 PM
I need to Read Excel file from application server and convert it to internal table
2023 May 23 8:07 PM
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.
2023 May 23 8:31 PM
2023 May 24 9:00 AM
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.
2023 May 24 7:48 AM