Ir al contenido principal

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.

 Source:

https://blogs.sap.com/2019/02/05/format-excel-column-as-text-via-abap-code/


Comentarios

Publicar un comentario

Entradas más populares de este blog

A to Z of OLE Excel in ABAP 7.4

  SAP users, both business and end users always need to download the output of a report to spreadsheet and do their analytics.   The standard excel output from a report is very simple process but it is old fashioned and the spreadsheet looks quite boring.  There is no default formatting and the users have to do all the hard work of changing the fonts, coloring the texts, marking the borders etc. Updated 16th Aug 2019  –  If you are working in non-ABAP 7.4 (below), there is complete reference program for you too. Go to the end of this article.  Thank you  Legxis  ( LeonievonK ) for the share. I acknowledge, whatever I mentioned above can be achieved in many ways programmatically. We can do it in the old traditional ABAP way but  providing multiple tabs in the spreadsheet and formatting is quite tricky with non  OLE  method. OLE = Object Linking and Embedding The high level agenda of this article is to be the  G.O.A.T.  (please google it if you do not know the full form)  of OLE Excel Gu
Publicar un comentario
Leer más
How to add custom field in Additional B Tab for SAP Sales Order In this article we want to explain step by step how to add custom field in SAP Sales Order transaction VA01/VA02/VA03. This time Mr ABAPGurus will give the tutorial of enhancement to add custom field in SAP Sales order. In this sample we will add new custom field for comments and customer satisfaction, this data will save into separate table from SAP Standard table ( VBAK / VBAP ). SAP provided us with ADDITIONAL TAB in the sales order transaction ( VA01/VA02/VA03 ) which allow customer to add custom fields.  T he different between ADDITIONAL A and ADDITIONAL B is the ADDITIONAL A for field which already predefine in Sales Order Header ( VBAK ) fields and ADDITIONAL B is for field that freely define. This sample we will use ADDITIONAL B because we will store data from customer satisfaction into Z database table. 1. Create one Z table using SE11 SAP Transaction code. 2.Using SE38 Transaction code and ope
Publicar un comentario
Leer más

VOFM

Creating VOFM custom routine, functionality same as SAP standard routine 103 with Additional Functionality 1.    Introduction 1.1   Document Purpose 1.2    This is the document helps you to create custom routine for sales order for below specific requirement: Functional description: In case of sale order with the SD document category ‘K’ or ‘L’ or ‘H’ related to an invoice, we need to fill the fixed value date (VBKD_VALDT) with the billing reference date (VBRK_FKDAT). For example: Invoice 90001111, the billing date is 22.07.2013 If we create a sale order related to this invoice, we want the fixed value date equal to the billing reference date. As per the requirement: Sap standard routine ‘103’ triggers whenever sales order is being created with reference. Sap standard routine is configured in VOFM transaction as: Sap standard routine ‘103’ has the functionality of copying the billing document header data and billing document line item data into the
Publicar un comentario
Leer más