CODEWITHSUNDEEP
excelabap
AlexanderK
AlexanderK

Reputation: 365

New line symbol when exporting to excel

I need to fill a cell with a data, separated by 'new line' symbol. I've tried: 

 data: l_con_sepa TYPE c VALUE cl_abap_char_utilities=>newline.
...
    CONCATENATE  <gf_aufk>-tplnr " 6000000159 Korchagin AS 02.02.2017
                      <gf_aufk>-pltxt
                      l_con_sepa
                      <gf_aufk>-aufnr
                      INTO lv_str
            SEPARATED BY space.

Tried to use CL_ABAP_CHAR_UTILITIES=>CR_LF. Tried to use "&" and "#" symbols. Tried to wrap lv_str with quotes. Nothing. I either got symbols as is, or just a blank space insted of 'alt+enter' equivalent.

Upvotes: 2

Views: 8127

Answers (2)

Jagger
Jagger

Reputation: 10524

A simple experiment with Excel, namely creating a cell with Alt+Enter characters and saving it as a CSV file, shows that such a new line symbol is LF and not CR_LF. Moreover it is put there in double quotes.

So just use double quotes and CL_ABAP_CHAR_UTILITIES=>NEWLINE.

Excel

Notepad++

It must work with CSV. You did not specify what API you use to export your data to XLS format, so I cannot test it. If you do not mind putting those details in the question, please do so.

Assuming you use FM SAP_CONVERT_TO_XLS_FORMAT, there is even no need for double quotes.

REPORT YYY.

TYPES: BEGIN OF gty_my_type,
    col1 TYPE char255,
    col2 TYPE char255,
  END OF gty_my_type,
  gtty_my_type TYPE STANDARD TABLE OF gty_my_type WITH EMPTY KEY.

START-OF-SELECTION.
  DATA(gt_string_table) = VALUE gtty_my_type(
    (
      col1 = 'aaa'
        && cl_abap_char_utilities=>newline
        && 'bbb'
        && cl_abap_char_utilities=>newline
        && 'ccc'
      col2 = 'ddd'
    )
  ).

  CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
    EXPORTING
      i_filename                 = 'D:\temp\abap.xlsx'
    TABLES
      i_tab_sap_data             = gt_string_table
    EXCEPTIONS
      conversion_failed          = 1
      OTHERS                     = 2.

  ASSERT sy-subrc = 0.

The result looks like follows

Convert to XLS format result

I thought that it might be caused by CONCATENATE .. INTO .. SEPARATED BY space but it is not. Please execute the following program in order to check it out.

REPORT YYY.

TYPES: BEGIN OF gty_my_type,
    col1 TYPE char255,
    col2 TYPE char255,
  END OF gty_my_type,
  gtty_my_type TYPE STANDARD TABLE OF gty_my_type WITH EMPTY KEY.
DATA: gs_string TYPE gty_my_type.
DATA: gt_string_table TYPE gtty_my_type.

START-OF-SELECTION.
  CONCATENATE 'aaa' cl_abap_char_utilities=>newline 'bbb' cl_abap_char_utilities=>newline 'ccc'
    INTO gs_string-col1 SEPARATED BY space.
  gs_string-col2 = 'ddd'.
  APPEND gs_string TO gt_string_table.


  CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
    EXPORTING
      i_filename                 = 'D:\temp\abap.xlsx'
    TABLES
      i_tab_sap_data             = gt_string_table
    EXCEPTIONS
      conversion_failed          = 1
      OTHERS                     = 2.

  ASSERT sy-subrc = 0.

Result Excel with CONCATENATE SEPARATED BY

So the problem must be somewhere else. You are not showing us your whole code. Maybe you use some kind of a third party package to process your Excel files?

Upvotes: 3

Mauricio G Teixeira
Mauricio G Teixeira

Reputation: 11

I don't remember if it's needed to add an "end of line" symbol. Just append each line into a table and download the full table using FM SAP_CONVERT_TO_XLS_FORMAT.

Upvotes: 0

Related Questions