21
Read Excel file from application server and convert it to internal table
source link: https://answers.sap.com/questions/13888687/read-excel-file-from-application-server-and-conver.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Read Excel file from application server and convert it to internal tableSkip to Content
SAP Community Log-in Update
In a few months, SAP Community will switch to SAP Universal ID as the only option to login. Donât wait, create your SAP Universal ID now! If you have multiple accounts, use the Consolidation Tool to merge your content.
1 Answer
Sort by:
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK