Skip to Content
Technical Articles

Format excel column as ‘Text’ via Abap Code

Objective: Prepare excel from code and format excel column as ‘TEXT’. Example, 0001 will be saved as 0001 and not 1.

The code mentioned in this document  is crosscountry with no language dependency and independent of excel properties across country.

Manual step for text formatting of a column in excel.

 

 

 

Automation of excel column formatting as text via code.

When we create excel via code and want to have value in any of the column as “TEXT” format.

Below code will help to format cell as text.

DATA: ls_attach     TYPE solisti1,
ls_email         TYPE /dpm/a_pls_email,
lt_email         TYPE /dpm/tt_pls_email,
ls_final_email   TYPE /dpm/a_pls_email,
lv_tab           TYPE c,
lv_cret          TYPE c,
lv_planner     TYPE string,
lv_status        TYPE string,
lv_datum_txt(10) TYPE c,
lv_time_txt(8)  TYPE c.

lv_tab = cl_abap_char_utilities=>horizontal_tab. “”Horizontal Tab Stop” Character
lv_cret = cl_abap_char_utilities=>cr_lf. “”Carriage Return and Line Feed”

* Appending header in the excel
CONCATENATE   ‘Product/part number’
‘Planner’
‘Date of change (date of CIF)’
‘Time of change (time of CIF)’
‘Current BOD-change Status’
‘Current value of final consumption plant’
‘New value of final consumption plant’
‘System’

INTO ls_attach SEPARATED BY lv_tab.

CONCATENATE lv_cret ls_attach  INTO ls_attach.
APPEND  ls_attach TO et_attach.

* Appending item data in the excel
CLEAR ls_email .
LOOP AT /dpm/cl_pls_email_grouping=>gt_email INTO ls_email.

CLEAR: lv_planner, lv_status.
ls_final_email-mandt              = sy-mandt.
ls_final_email-matnr              = ls_email-matnr.

Here, planner and status are the fields where cell format should be text. If value is 070, then it should come in excel as 070 only.

Below way of applying formula is cross country with no language dependency or excel properties across country.

CONCATENATE ‘=”‘ ls_emailplanner ‘”‘ INTO lv_planner.
CONCATENATE ‘=”‘ ls_email/dpm/status_bod ‘”‘ INTO lv_status.

ls_final_email-/dpm/cnspl_fin_old = ls_email-/dpm/cnspl_fin_old.
ls_final_email-/dpm/cnspl_fin_new = ls_email-/dpm/cnspl_fin_new.
ls_final_email-sysid              = ls_email-sysid.

 

Writing date and time as per user choice of date/time format.

ls_final_email-crdat              = ls_email-crdat.
ls_final_email-crtim              = ls_email-crtim.

WRITE  ls_final_email-crdat TO   lv_datum_txt USING EDIT MASK ‘__.__.____’.
WRITE  ls_final_email-crtim TO   lv_time_txt USING EDIT MASK ‘__:__:__’.

CONCATENATE ls_final_email-matnr
lv_planner
lv_datum_txt
lv_time_txt
lv_status
ls_final_email-/dpm/cnspl_fin_old
ls_final_email-/dpm/cnspl_fin_new
ls_final_email-sysid
INTO ls_attach SEPARATED BY lv_tab.

CONCATENATE lv_cret ls_attach  INTO ls_attach.

APPEND  ls_attach TO et_attach.  ” attachment for mail

CLEAR: ls_email,
ls_final_email,
ls_attach.
ENDLOOP.

 

We would be receiving below excel as attachment prepared in the code above.

 

 

This is used in project DAIMLER (German Project) where the requirement is to send excel as attachment via mail.

It took 2 days of effort to format the column with number input as text.

There are other formulas also available in google search , but they are language dependent and the excel when opened by German colleagues results in wrong output.

But with the help of above code, it will format the column as text irrespective of any dependency.

 

 

 

8 Comments
You must be Logged on to comment or reply to a post.
  • I am going to have to say it.

    If the requirement is to send a formatted spreadsheet via email, generated programatically out of ABAP, what is wrong with the approach used by ABAP2XLSX?

    Then you could use ABAP to set the format of the cell to text. Anything you can do manually in Excel you can do programatically, with very little effort.

    Cheersy Cheers

    Paul

     

    • It would really make things very simple..

      But when we look at his profile, he works at SAP and again if we look at his code, he is using some custom namespace probably a CD or Labs project.. So is he really allowed to use ABAP2XLSX? Even if he uses it, all customers might not really accept the opensource(if that product is delivered to multiple customers).

      Now comes the question, why SAP didn’t made it as the part of the system? due to opensource licencing policy? I wonder why.., that would make it easy for all the customers..

      BR,

      Mahesh.

      • When ABAP2XLSX started it was hosted on the “code exchange”. At that point SAP said in the small print that they reserved the right to use anything on the code exchange in the core SAP product.

        Some other code on the code exchange made it into standard SAP e.g. some sort of JSON transformation class, I recall a blog where the guy who wrote it did not know it had been put in the SAP standard system, and when he found out he was quite flattered.

        abapGit is an open source ABAP project as well, and that is going to be used in ABAP in the Cloud, and possibly in S/4 HANA on premise as well (based on what Bjorn G said two years ago at TECHED)

        So SAP can use any open source ABAP thing they feel like if they so desire, and in this case they seem to prefer to re-invent the wheel….

        Cheersy Cheers

        Paul

        • Thanks Paul, that cleared my question with the opensource..

          But my other question was why SAP has not merged the ABPA2XLSX code in their standard products, which could have made it easier for the OP and many other customers.. I feel like SAP is not doing it deliberately for some unknown reason…