Line Feed in Excel Cell

[bjorn-henrik_zink]

Hi,

I'm trying to create line feeds in an excel cell with class attribute

cl_abap_char_utilities=>cr_lf

Unfortunately, this results in multiple rows in excel instead of multiple lines within a cell.

How can I fix the problem?

My code looks something like this:

LOOP AT ta_notes INTO wa_notes.  
  CONCATENATE lv_tmp_str              
              wa_notes-tdline              
              cl_abap_char_utilities=>cr_lf         
         INTO lv_tmp_str.
ENDLOOP.

Thanks in advance.

/ Elvez

[Former Member]

Hi

Did you try NEWLINE instead of cr_lf?

Regards,

Raj

[bjorn-henrik_zink]

Hi Rajasekhar,

I have tried <b>newline</b> and <b>backspace</b> in vain. <b>newline</b> creates new rows in Excel. With <b>backspace</b> no lines are created and some weird symbols are appearing in Excel.

/Elvez

[Former Member]

Hi Elvez

I suspect the cr_lf character will do what Return does on Excel, which as you state is to goto the next cell. In Excel, within a cell itself you need to do Alt + Enter to insert a new line within the cell. I'm not sure how to do this in ABAP but will try and look into it. At least you can be looking into it too!

Kind regards

Andy

[bjorn-henrik_zink]

Thanks Andrew,

do you know the hex code of <b>Alt + Enter</b> ?

/ Elvez

[Former Member]

Hi Elvez.

The hex code should be: A6

/Niklas

[Former Member]

Hi Elvez

From reverse engineering in Excel it looks like it is ascii code 10 which is 0A in hex. However, I have a bad feeling that 0A will be the same as is used in cr_lf because it represents the linefeed control character. Another option is 0D but again this is a carriage return code so will probably have the same effect. Might be worth a go though. I'll carry on thinking about it, I'm not sure how Alt + Enter is treated internally i.e. whether it produces a single hex value or two distinct values that are interpreted by the Excel software.

Kind regards

Andy

[Former Member]

Hi Elvez

I think that you need to use the cr_lf character but what you have to do is enclose the contents of your variable in double quotes. So, using your code you need to do something like this:

CONSTANTS k_dbl_quote(1) TYPE C VALUE '"'.
LOOP AT ta_notes INTO wa_notes.    
  CONCATENATE k_dbl_quote
              lv_tmp_str
              wa_notes-tdline
              cl_abap_char_utilities=>cr_lf
              k_dbl_quote
         INTO lv_tmp_str.
ENDLOOP.

Enclosing the contents in double quotes signifies to Excel an escape sequence so they shouldn't appear in your output but they should cause Excel to mimic Alt + Return rather than just doing a Return to a new cell.

I haven't tried this but I think it is worth trying. I will be interested to know if it works...

Kind regards

Andy

Message was edited by: Andrew Wright

I should add that this is based on the way Excel handles CSV files, if this doesn't work then you might have to think about exportin data as a CSV and then importing it into Excel.

hope this helps!

Andy

[bjorn-henrik_zink]

Hi Andy,

great stuff! It worked very well, however, the coding had to be slightly modified:

CONSTANTS k_dbl_quote(1) TYPE C VALUE '"'.

CONCATENATE k_dbl_quote
            lv_tmp_str
       INTO lv_tmp_str.

LOOP AT ta_notes INTO wa_notes.
  CONCATENATE lv_tmp_str
              wa_notes-tdline
              cl_abap_char_utilities=>cr_lf
         INTO lv_tmp_str.
ENDLOOP.

CONCATENATE lv_tmp_str
            k_dbl_quote
       INTO lv_tmp_str.

A problem occurs when double quotas are part of the text (wa_notes-tdline).

Do you know how I can solve that?

/ Elvez

[Former Member]

Hi Elvez

Excellent!

Erm, it depends what the problem is Could you give me more detail on what happens/doesn't happen please?

Kind regards

Andy