adjusting column header length in excel

[Former Member]

Hi,

i'm using MS_EXCEL_OLE_STANDARD_DAT to download internal table contents to a spread sheet. im passing another internal table to write headers. however, i realized that this funtion module doesnt allow the length of the column header to be more than column length .

My question may look weird but if there is any other way or FM to overcome this, please let me know.

Thanks,

ravi.

[Former Member]

Dear Ravi,

one of the first questions is that whether your outout is displayed in an ALV display(List/ Grid) ?

If so then you can

change the the output length of the column for which you want the total column header according to the length of the column header text dyanamically determining it using STRLEN command and giving the output length in your fieldcatlog for that particular equivalent to the column header text.

this will absolutely give you the desired output in your excel sheet which you download.

Hope that this solves your problem.If you have any further queries regadring please do comeback. Please do offer some reward for the same.

Thanks & Regards,

Venugopal

[Former Member]

Hi,

Use this FM . This looks good and also will solve your purpose. I've given it in a FORM ENDFORM. Just copy and paste it in ur code and make changes in the itab names and field names as needed.

DATA: IT_COLS LIKE GXXLT_V OCCURS 0 WITH HEADER LINE.

DATA: IT_ONLINE LIKE GXXLT_O OCCURS 0 WITH HEADER LINE.

DATA: IT_PRINT LIKE GXXLT_P OCCURS 0 WITH HEADER LINE.

1)Load the actual data into the itab it_qual_com.

2)PERFORM EXCEL_GENERATION.

Bingo this will work. Please award points for the same if this was useful.

<b>Cheers,

Sam.</b>

FORM EXCEL_GENERATION.

IND = 0.

IT_COLS-COL_NAME = 'Emp No'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Name'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Date of Joining'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Confirmed'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Role Code'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Role Description'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Job Band'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Skill'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Additional Weightage'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Progeon Experience(months)'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Prev. Relevant Experience(months)'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Total Experience(months)'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'PU/Dept'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'DU'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Location'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Age'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Date of Birth'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Gender'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Birth Place'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'State'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Nationality'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'PB'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'PSB'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

IT_COLS-COL_NAME = 'Stream Code'.

IND = IND + 1.

IT_COLS-COL_NO = IND.

Append it_cols.

<b> CALL FUNCTION 'XXL_SIMPLE_API'</b>

EXPORTING

N_KEY_COLS = 1

TABLES

COL_TEXT = IT_COLS

<b> DATA = IT_QUAL_COM</b> "data itab

ONLINE_TEXT = IT_ONLINE

PRINT_TEXT = IT_PRINT

EXCEPTIONS

DIM_MISMATCH_DATA = 1

FILE_OPEN_ERROR = 2

FILE_WRITE_ERROR = 3

INV_WINSYS = 4

INV_XXL = 5

OTHERS = 6

.

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

ENDFORM. "EXCEL_GENERATION

[Former Member]

Hi,

I'm using the same FM mentioned by you and working on 4.6C. Please see the sample code for usage of the FM. It has also included the method to supply the file name. Here the headers are max 25 chars and colums are max 10 chars long. Hope this helps.

TABLES:vbak.

DATA: BEGIN OF itab1 OCCURS 0,

vbeln LIKE vbak-vbeln,

ernam LIKE vbak-ernam,

vbtyp LIKE vbak-vbtyp,

END OF itab1.

DATA: BEGIN OF heading OCCURS 0,

head(25),

END OF heading.

DATA: itab_k LIKE TABLE OF itab1 WITH HEADER LINE.

DATA: v_fname(20),

v_filename TYPE string,

v_path TYPE string,

v_fullname TYPE STRING,

v_filename2 LIKE rlgrap-filename.

INITIALIZATION.

heading-head = 'SALES ORDER NUMBER'.

APPEND heading.

heading-head = 'USER NAME'.

APPEND heading.

heading-head = 'SALES ORDER TYPE'.

APPEND heading.

START-OF-SELECTION.

• SELECT DATA FROM vbak TABLE UP TO 10 ROWS

SELECT VBELN ERNAM VBTYP UP TO 10 ROWS

FROM VBAK

INTO TABLE itab_k.

END-OF-SELECTION.

*FUNCTION MODULE TO GET THE FILE NAME FROM USER

CALL METHOD cl_gui_frontend_services=>file_save_dialog

EXPORTING

window_title = 'Enter File Name'

default_extension = 'XLS'

initial_directory = 'C:\'

CHANGING

filename = v_filename

path = v_path

fullpath = v_fullname.

*ASSIGN FILE NAME TO PROPER TYPE

v_filename2 = V_FULLNAME.

*CALL FUNCTION TO DOWNLOAD THE FILE TO XLS SHEET

CALL FUNCTION 'MS_EXCEL_OLE_STANDARD_DAT'

EXPORTING

file_name = v_fILEname2

TABLES

data_tab = itab_k

fieldnames = heading.

[Former Member]

Hi,

see this link for sample codes:

Regards,

Anjali