topic Re: Creating Excel 2007 errors when columns exceed 256 in Application Development and Automation Discussions

Creating Excel 2007 errors when columns exceed 256

Former Member — Fri, 13 Apr 2012 11:56:51 GMT

Hi All

I have developed abap program to export data to excel.

Although I have office 2007, excel creation errors when the number of columns created exceeds 256.

Has anyone managed to create excel with multiple sheets and columns exceeding 256 columns?

Thank you.

Raj

Re: Creating Excel 2007 errors when columns exceed 256

Former Member — Fri, 13 Apr 2012 13:50:47 GMT

Hi Raj,

You can try using excel 2010.. In that yu can save N number of columns and rows

Cheers,

Hani

Re: Creating Excel 2007 errors when columns exceed 256

Former Member — Fri, 13 Apr 2012 15:20:56 GMT

Thanks Hani for quick reply. Even excel 2007 allows over 16000 columns. The issues is that when the excel is created with a data sheet containing more than 256 columns using abap program it will come up with error and only the 256 columns are created.

Raj

Re: Creating Excel 2007 errors when columns exceed 256

former_member585060 — Fri, 13 Apr 2012 15:41:36 GMT

Hi,

In which method your are creating the excel?

Try the below method. Pass output table and and its column to the perform. Use the v_xls_iattach to download.

DATA:

v_xls_iattach(2000) OCCURS 0 WITH HEADER LINE.

CONSTANTS:

c_xls_con_tab TYPE c VALUE cl_abap_char_utilities=>horizontal_tab,

c_xls_con_cret TYPE c VALUE cl_abap_char_utilities=>cr_lf.

* Prepare the final excel sheet

PERFORM build_xls_data USING i_fields[]

i_output[].

*********************************************************

FORM build_xls_data USING p_flds TYPE ANY TABLE

p_fs TYPE ANY TABLE.

DATA: lv_text(50) TYPE c.

DATA: lv_line TYPE REF TO data.

FIELD-SYMBOLS: <ft> TYPE ANY TABLE,

<fs> TYPE ANY,

<fl> TYPE ANY.

* For Column Headers

ASSIGN p_flds TO <ft>.

CREATE DATA lv_line LIKE LINE OF p_flds.

ASSIGN lv_line->* TO <fl>.

LOOP AT <ft> INTO <fl>.

CLEAR lv_text.

ASSIGN COMPONENT 'REPTEXT_DDIC' OF STRUCTURE <fl> TO <fs>.

WRITE <fs> TO lv_text.

CONCATENATE v_xls_iattach lv_text

INTO v_xls_iattach.

CONCATENATE v_xls_iattach c_xls_con_tab

INTO v_xls_iattach.

ENDLOOP.

CONCATENATE v_xls_iattach c_xls_con_cret INTO v_xls_iattach.

APPEND v_xls_iattach.

CLEAR v_xls_iattach.

* For Output table

ASSIGN p_fs TO <ft>.

CREATE DATA lv_line LIKE LINE OF p_fs.

ASSIGN lv_line->* TO <fl>.

IF NOT p_fs[] IS INITIAL .

LOOP AT <ft> INTO <fl>.

CLEAR lv_text.

DO.

ASSIGN COMPONENT sy-index OF STRUCTURE <fl> TO <fs>.

IF sy-subrc <> 0.

EXIT.

ENDIF.

WRITE <fs> TO lv_text.

CONCATENATE v_xls_iattach lv_text

INTO v_xls_iattach.

CONCATENATE v_xls_iattach c_xls_con_tab

INTO v_xls_iattach.

ENDDO.

CONCATENATE v_xls_iattach c_xls_con_cret INTO v_xls_iattach.

APPEND v_xls_iattach.

CLEAR v_xls_iattach.

ENDLOOP.

ENDIF.

ENDFORM. " BUILD_XLS_DATA

Thanks & Regards

Bala Krishna

Re: Creating Excel 2007 errors when columns exceed 256

Former Member — Fri, 13 Apr 2012 17:05:50 GMT

Hi Bala

I am building an internal table with "tab" delimited rows and then using following to copy the internal table to clipboard and then using OLE method "PASTE" to paste the EXCEL sheet.

This the quickest way to create excel instead of creating each cell at a time.

CALL METHOD cl_gui_frontend_services=>clipboard_export

IMPORTING

data = l_table[]

CHANGING

Cheers

Raj

Re: Creating Excel 2007 errors when columns exceed 256

former_member585060 — Fri, 13 Apr 2012 17:25:48 GMT

Hi,

Try to download and see the same tab delimited internal table using GUI_DOWNLOAD function module giving the filename 'C:\Temp\I_table.xls'.

CALL METHOD cl_gui_frontend_services=>gui_download
EXPORTING
    filename = 'C:\Temp\i_table.xls'
CHANGING
    data_tab = i_table
EXCEPTIONS
    OTHERS   = 1.

Thanks & Regards

Bala Krishna