Uploading Excel from application server to internal table in background mode

[Zakir_hussain]

I have a requirement that I hav mentioned. I read dataset with binary mode as Text mode doesnt taking for excel.From binary data, i have to convert into readable one. I browsed to many blogs I couldnt understand, Could U pleaseprovide a document or solution on How to convert binary data to internal table data.

I used FM 'SCMS_BINARY_TO_XSTRING'.

[FredericGirod]

Check ABAP2XLSx

[Zakir_hussain]

ABAP2XLSx for foreground process. I want it for background , used open dataset, closedataset. This process on background.

I have shared my logics. Please look into it.

In lo_package , Im getting runtime error. X_string length is 255, but needs more than 520 to process.

REPORT ZEXCEL_TO_ABAP.

TYPES :BEGIN OF ty_data,
        Tid type string,
        Tr type string,
        Tad type string,
  END OF ty_data,
  ty_datum type table of ty_data WITH EMPTY KEY.

TYPES: BEGIN OF bin_file,
         name TYPE string,
         size TYPE i,
         data TYPE solix_tab,
       END OF bin_file.

       data:lv_file TYPE string,
             directory(30).

data:lv_length type i,
      lst_data type solix, "cpt_x255.
      lst_tab type solix_tab,
      li_xtab type solix_tab,
      ix_string type xstring,
      file_size type drao-orln.

DATA lo_package TYPE REF TO cl_openxml_package.

PARAMETERS : p_app_s type rlgrap-filename.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_app_s.

*// DIRECTORY NAME
  directory = 'C:\usr\sap\CSFL'.
*
*
**// Fm for Application Server F4 help
  CALL FUNCTION '/SAPDMC/LSM_F4_SERVER_FILE'
    EXPORTING
      directory        = directory
    IMPORTING
      serverfile       = p_app_s
    EXCEPTIONS
      canceled_by_user = 1
      OTHERS           = 2.

lv_file = p_app_s.


START-OF-SELECTION.


open DATASET LV_file for INPUT in BINARY MODE .
IF sy-subrc <> 0.
message 'unable to open' type 'I' DISPLAY LIKE 'E'.
endif.


do.
READ DATASET lv_file into lst_data LENGTH lv_length." LENGTH lv_length.
if sy-subrc = 0.
file_size = file_size + lv_length.

APPEND lst_data to lst_tab.
endif.
exit.
enddo.

close DATASET lv_file.

IF lst_tab is not INITIAL.
li_xtab[] = lst_tab[].
ENDIF.


call function'SCMS_BINARY_TO_XSTRING'
  EXPORTING
    input_length = lv_length
*    first_line   = 0
*    last_line    = 0
  IMPORTING
    buffer       = ix_string
  TABLES
    binary_tab   = li_xtab[]
*  EXCEPTIONS
*    failed       = 1
*    others       = 2
  .
IF sy-subrc <> 0.
* MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
*            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

lo_package = cl_xlsx_document=>load_document( iv_data = ix_string ). "Here is where Im getting dump

data(flag) = 'X'.

[FredericGirod]

ABAP2xlsx is the simpliest method to build Xls in background : https://abapblog.com/articles/tricks/33-create-xlsx-mhtml-file-from-internal-table-in-background

[abo]

Adding to frdric.girod answer disguised as a comment 😉

You're looking for ZDEMO_EXCEL15, which showcases the reader functionality.

[FredericGirod]

c5e08e0478aa4727abc4482f5be390b2 you give the solution with ZDEMO_EXCEL15, I haven't find it (quick search)

[abo]

Hahah, I've copied it straight from my previous answer to this same question 🙂

[Sandra_Rossi]

Maybe you could not understand my answer, so instead of answering, maybe you can ask what you didn't understand?

[Zakir_hussain]

I used the procedure mentioned below

https://praveensg8.files.wordpress.com/2015/04/reading-excel-file-from-application-server-into-abap-... .after final Transformation I am getting blank Internal table,

One doubt I have is in the tranformation mentioned in the above link in place of <EXCEL_DATA-FIELD1> what I need to use is this internal table field name or Excel heading name.

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

 <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>
 </EXCEL_DATA-FIELD1>
 <EXCEL_DATA-FIELD2>
 <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>
 </EXCEL_DATA-FIELD2>
 <EXCEL_DATA-FIELD3>

 <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>
 </EXCEL_DATA-FIELD3>
 <EXCEL_DATA-FIELD4>
 <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>
 </EXCEL_DATA-FIELD4>
 </item>
 </xsl:if>
 </xsl:for-each>
 </LT_DATA>
 </asx:values>
 </asx:abap>
 </xsl:template>
</xsl:transform>