Make ABAP Launch Excel and Populate Worksheet with Internal Table

The ability to instantly view the contents of an internal table in Excel without having to pass it through the clipboard is a great time saver and a big hit with clients. Not only does it obviate the need to perform a cumbersome System→List→Save→Local File → Clipboard routine, but also the Columns to Text step in Excel with its own complex set of individual steps.

The XXL_FULL_API function has a demanding interface with eighteen parameters including six unique internal tables. All of these values must be aligned exactly or the function will throw an error.

For ease-of-use, this snippet will be a form in the User-Defined Routines accepting only three parameters. The three parameters are:

• The internal table to be displayed in Excel
• The number of columns to be colored as "indexed"
• The language for column header descriptions

The form starts by declaring the incoming parameters and defining the local variables. The lvSPRAS parameter can be left blank if field names and not descriptions are desired.

Plagiarism Alert!

It should be obvious that a great deal of this code was stolen out of the source code for the SQ00 transaction. A lot of debugging during execution was required to understand how ABAP calls this function; viewing the code in SE37 provides little useful information and this was ultimately copy-pasted right out of SAPMS38R along with the German comments.

form InternalTableToExcel
 using it_Table type standard table
       nvkeys type i
       lvSPRAS like DD04T-DDLANGUAGE.
    "*** Data for Excel Spreadsheet function
  data: data_starting_at type i,         " erste Zeile der Datentabelle
        data_ending_at   type i,         " letzte Zeile der Datentabelle
        filename like gxxlt_f-file,      " Dateiname für Listobjekt
        header_1 like gxxlt_p-text,      " Titelzeile
        header_2 like gxxlt_p-text,      " Untertitel
        n_vrt_keys type i,               " Anzahl vertikale Merkmale
                                         " (= Anzahl Merkmalsspalten)
        n_hrz_keys type i,               " Anzahl horizontale Merkmale
                                         " (= Anzahl der Spaltenmerkmale
                                         "    für Kennzahlspalten)
        n_att_cols type i,               " Anzahl der Kennzahlspalten
        rcode      type i,               " Return-Code für XXL
        sotitle    like gxxlt_f-so_title," Titel für SAPoffice-Dokument
        nostart(1),                      " X = EXCEL nicht starten
        nodialog(1),                     " X = kein XXL-Dialog
        nSEMA type i.
  data: begin of vkey occurs 10.         " Ausprägung vertikale Merkm.
          include structure gxxlt_v.
  data: end of vkey.
  data: begin of hkey occurs 10.         " Ausprägung horizontale Merkm.
          include structure gxxlt_h.
  data: end of hkey.
  data: begin of sema occurs 10.         " Spaltenbeschreibung
          include structure gxxlt_s.
  data: end of sema.
  data: begin of print_text occurs 10.   " Drucktexte
          include structure gxxlt_p.
  data: end of print_text.
  data: begin of online_text occurs 10.  " online-Texte
          include structure gxxlt_o.
  data: end of online_text.

  DATA: t_descr TYPE REF TO CL_ABAP_TABLEDESCR,
        wa_keys type ABAP_KEYDESCR.
  DATA : lv_struc TYPE REF TO cl_abap_structdescr.
  DATA : lv_typedesc TYPE REF TO cl_abap_typedescr.
  DATA : lv_comp TYPE abap_compdescr_tab,
         wv_comp LIKE LINE OF lv_comp.
  data: lv_dy_line type ref to data.
  field-symbols: <lv_dyn_wa>.

Next is determining the field names that comprise the rows of the internal table. Much easier than requiring a developer to pass a list of field names (along with the number of columns) is ripping the fields right out of the internal table using the CL_ABAP_TABLEDESCR class.

The fields in an internal table can be looped through and populated into the header row of the worksheet while counting of the number of each type of column. The indexed columns are populated first and are highlighted in the final worksheet while the regular rows are on a white background.

  "*** Added this code to more accurately count the number of
  "*** columns in the incoming table 09APR2019. -Jimbo
  CREATE DATA lv_dy_line LIKE LINE OF it_Table.
  assign lv_dy_line->* to <lv_dyn_wa>.
  CALL METHOD cl_abap_structdescr=>describe_by_data
    EXPORTING
      p_data      = <lv_dyn_wa>
    RECEIVING
      p_descr_ref = lv_typedesc.
  lv_struc ?= lv_typedesc.
  lv_comp = lv_struc->components.

  t_descr ?= CL_ABAP_TABLEDESCR=>describe_by_data( it_Table ).
  n_vrt_keys = 0. n_att_cols = 0. n_hrz_keys = 1.
*  loop at t_descr->key into wa_keys.
  loop at lv_comp into wv_comp.
    wa_keys-Name = wv_Comp-NAME. " New way.
    if n_vrt_keys lt nvkeys. "Populate these first.
      add 1 to n_vrt_keys.
      vkey-COL_NO = n_vrt_keys.
      vkey-COL_NAME = wa_keys-NAME.
      select DDTEXT from DD04T into vkey-COL_NAME
       where ROLLNAME eq wa_keys-NAME and DDLANGUAGE eq lvSPRAS.
      endselect.
      append vkey.
    else.
      add 1 to n_att_cols.
      hkey-COL_NO = n_att_cols. hkey-ROW_NO = 1.
      hkey-COL_NAME = wa_keys-NAME.
      select DDTEXT from DD04T into hkey-COL_NAME
       where ROLLNAME eq wa_keys-NAME and DDLANGUAGE eq lvSPRAS.
      endselect.
      append hkey.
    endif.
  endloop.

At this point, six of the parameters to pass into the function have been prepared. Next is the SEMA internal table which must have exactly the same number of rows as the source data has columns.

The SEMA internal table will format the cells in each column as number or string. For simplicity, each field will be handled as a string.

  nSEMA = N_VRT_KEYS + N_ATT_COLS.
  do nSEMA times.
    sema-COL_NO = sy-index.
    sema-COL_TYP = 'STR'.  "Just make them all text.
    sema-COL_OPS = 'DFT'.
    append sema.
  enddo.

The hardest part is done. Describing the length of the source data and giving it a name (that will never be seen) is the easy part.

The nodialog parameter appears to do nothing. SAP presents two popup windows regardless of whether this parameter is flagged.

  data_starting_at = 1.
  describe table it_Table lines data_ending_at.
  filename = 'Output'.
  header_1 = 'Output'.
  nodialog = space.                    " mit Dialog

The XXL_CHECK_API checks to see if ABAP can launch Excel. If Excel is not installed on the user's computer or there is some permission or compatibility issue then this function will return a non-zero code.

 
  call function 'XXL_CHECK_API'        " Test, ob XXL gestartet werden
       exporting question    = 'STARTABLE'   " kann
       importing return_code = rcode.
  if rcode = 0.                        " XXL kann gestartet werden
    nostart = space.
    sotitle = space.
  else.                                " XXL kann nicht gestartet werden
    nostart = 'X'.                     " nur Ablage als SAPoffice-
    sotitle = 'Output'.                " Dokument sinnvoll
  endif.

Finally, the source data is checked to ensure that it is not empty before calling the function; an empty spreadsheet will cause the function to throw an error. The function handles several exceptions, but only cancelled_by_user is handled here; all other issues are obviated by meticulously populating all the parameters before passing them to the function.

There is a DATA_TOO_BIG exception handler, but that is likely obsoleted by newer versions of Excel. The DIM_MISMATCH_DATA exception handler hasn't presented in the past despite passing in integers and floating point values to be handled as string.

  if data_ending_at ne 0.
    call function 'XXL_FULL_API'
       exporting
            n_hrz_keys        = n_hrz_keys
            n_vrt_keys        = n_vrt_keys
            n_att_cols        = n_att_cols
            data_starting_at  = data_starting_at
            data_ending_at    = data_ending_at
            filename          = filename
            header_1          = header_1
            header_2          = header_2
            no_dialog         = nodialog
            so_title          = sotitle
            no_start          = nostart
       tables
            vkey              = vkey
            hkey              = hkey
            data              = it_Table
            sema              = sema
            online_text       = online_text
            print_text        = print_text
       exceptions
            cancelled_by_user = 1.
  else.
    write: / 'No records to output.'.
  endif.
endform.

A complex structure was passed into the form for the purpose of testing and documentation. Just one record with one value was populated.

wa_r-First = 'Test'.
append wa_R to it_R.

When the function is called, SAP will popup these two windows. After clicking through, Excel will be launched and then populated with the contents of the internal table.

When the lvSPRAS parameter is populated with a language, the header is populated with language-specific descriptions of the field where available. In this example, the "2" passed as the second parameter causes two columns to be highlighted as indexed.

perform InternalTableToExcel using it_R 2 'E'.

When the lvSPRAS parameter is left blank as in this example, the header is populated with the field names from the source data. In this example, three columns are highlighted as indexed.

perform InternalTableToExcel using it_R 3 ''.

SAP will wait while the spreadsheet is open. Clicking the checkbox on the dialog box that appears will cause the instance of Excel with the spreadsheet to close automatically and the ABAP program will continue.

Download this tool
This tool, along with all of the other useful tools and User-Defined Routines, are available as part of Jimbo's LSMW Toolbox. Importing this snippet as a User-Defined Routine is much easier that copy-pasting it into LSMW.