Creating Excel 2007 errors when columns exceed 256

[Former Member]

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

[Former Member]

Hi Raj,

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

Cheers,

Hani

[Former Member]

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

[former_member585060]

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

[Former Member]

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

[former_member585060]

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'.

or

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