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 cross–country 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_email–planner ‘”‘ 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.
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
+1 could not agree more.
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...
I thought about that too!
Congratulations, excellent post
Nice blog - that started some nice discussion. I've always said sometimes the comments are worth writing the blog.
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_email–planner ‘”‘ INTO lv_planner.
How to convert to 'Number' from String for the same?
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.