Skip to Content
Technical Articles
Author's profile photo Manu Handa

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’
‘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’

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
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,


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.




Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Paul Hardy
      Paul Hardy

      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



      Author's profile photo Nabheet Madan
      Nabheet Madan

      +1 could not agree more.

      Author's profile photo Mahesh Palavalli
      Mahesh Palavalli

      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..



      Author's profile photo Paul Hardy
      Paul Hardy

      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


      Author's profile photo Mahesh Palavalli
      Mahesh Palavalli

      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...

      Author's profile photo Michelle Crapo
      Michelle Crapo

      I thought about that too!

      Author's profile photo Carlos Alberto Valentini
      Carlos Alberto Valentini

      Congratulations, excellent post

      Author's profile photo Michelle Crapo
      Michelle Crapo

      Nice blog - that started some nice discussion.  I've always said sometimes the comments are worth writing the blog.

      Author's profile photo Dhanunjay rao
      Dhanunjay rao

      Thanks for the useful formula in case string should display as text format ( in my case 10-05 converting as date format 10-May, below comment useful to display the same string 10-05 )

      CONCATENATE ‘=”‘ ls_emailplanner ‘”‘ INTO lv_planner.

      Author's profile photo Vinayak D
      Vinayak D

      How to convert to 'Number' from String for the same?

      Author's profile photo Vladimir Latyshenko
      Vladimir Latyshenko

      Why not prefix 0001 with ' so that it is not converted to 1? ' is a technical sign to indicate that the value of the cell is a text and not a number.