ABAP code to Retain leading zeros within XLS file (excel)

A potential problem with the example code for sending an .xls attachment via email is that if a field value contains leading zeros, excel will automatically remove these from display i.e. (00444 with be displayed as 444). One solution I have found to get around this problem is to take advantage of excels 'REPLACE' function. This is designed to replace a string section with another value, for example you could replace the first 2 chars with todays date. I have found that when building the .xls table within SAP that if you insert tha code for the 'REPLACE' command to replace the value with its self excel will then treat it as a text value and display the zeros.

=REPLACE("<value>",<start position>,<num of chars to replace>,"<value>")
=REPLACE("00100",1,5,"00100")

The code below demonstrates how this solution would be implemented using ABAP. The full source code for the send email attachment including this modification can be found here.


*&-----------------------------------------------------------*
*&      Form  BUILD_XLS_DATA_TABLE
*&-----------------------------------------------------------*
*       Build data table for .xls document
*&      Produced by www.SAP Development                         *
*------------------------------------------------------------*

FORM build_xls_data_table.
  data: ld_store(50) type c.  "Leading zeros
  CONSTANTS: con_cret TYPE x VALUE '0D',
             con_tab TYPE x VALUE '09'.
  CONCATENATE 'EBELN' 'EBELP' 'AEDAT' 'MATNR'
         INTO it_attach SEPARATED BY con_tab.
  CONCATENATE con_cret it_attach  INTO it_attach.
  APPEND  it_attach.
  LOOP AT it_ekpo INTO wa_charekpo.
*Modification to retain leading zeros
*   inserts code for excell REPLACE command into ld_store
*   =REPLACE("00100",1,5,"00100")
    concatenate '=REPLACE("' wa_charekpo-ebelp '",1,5,"'
                             wa_charekpo-ebelp '")' into ld_store .
*   concatenate ld_store into .xls file instead of actual value(ebelp)
    CONCATENATE wa_charekpo-ebeln ld_store.
*End of modification    
                wa_charekpo-aedat wa_charekpo-matnr
           INTO it_attach SEPARATED BY con_tab.
    CONCATENATE con_cret it_attach  INTO it_attach.
    APPEND  it_attach.
  ENDLOOP.
ENDFORM.                    " BUILD_XLS_DATA_TABLE

<--SAP Email processing Home

Send email from SAP using ABAP code on sending data as an email attachment
Send external email from within ABAP program and include attachment
Email attachment from SAP and retain leading zeros mod in attached excel document
cl_bcs ABAP class interface to send emails from SAP
Sending Emails to external addresses from within a program using SAPconnect
Send external email from within ABAP program
Restrict access to sent emails in SOST SAP transaction
SAPconnect Administration
Send SAP mail to specific users workplace inbox
Start send process from SAPconnect (Send mail to external address)
Setup PO external send to partner / vendor functionality for SAP purchase orders
SOST SAPConnect send requests transaction

ABAP code to Retain leading zeros within XLS file (excel)

Related Articles