spool to excel

siongchao_ng · ‎2021 Jul 13

Hi all,

I need to convert spool to excel in background job only to send excel attachment to users through email.

Spool is like image. I will be getting 1 whole piece of spool into single excel column. Anyone have any other working codes?

My codes below fail to separate the spool into excel nicely.

DATA it_spool_xls       TYPE  TABLE OF soli.
  DATA it_xls_spool       TYPE TABLE OF soli.


  CALL FUNCTION 'RSPO_RETURN_ABAP_SPOOLJOB'
    EXPORTING
      rqident              = spool_number                     "Spool Request Number
      first_line           = 1
    TABLES
      buffer               = it_spool_xls                            "Internal table that will have the Spool Request No data
    EXCEPTIONS
      no_such_job          = 1
      not_abap_list        = 2
      job_contains_no_data = 3
      selection_empty      = 4
      no_permission        = 5
      can_not_access       = 6
      read_error           = 7
      OTHERS               = 8.

*To convert the spool data into excel format
  CALL FUNCTION 'SO_RAW_TO_RTF'
    TABLES
      objcont_old = it_spool_xls               "Internal table having spool data
      objcont_new = it_xls_spool.           "Int table having Excel format data converted from Spool data


  IF NOT it_xls_spool IS INITIAL.
    IF app_serv_path IS NOT INITIAL.
      FIELD-SYMBOLS <hex_container> TYPE any.
      OPEN DATASET app_serv_path FOR OUTPUT IN TEXT MODE ENCODING DEFAULT WITH SMART LINEFEED.
      IF sy-subrc = 0.
        LOOP AT it_xls_spool ASSIGNING <hex_container> .
          TRANSFER <hex_container> TO app_serv_path.
          CLEAR <hex_container>.
        ENDLOOP.
        CLOSE DATASET app_serv_path.
        MESSAGE 'File succesfully transferred' TYPE 'S' .
      ELSE.
        MESSAGE 'File cannot be opened' TYPE 'E' .
      ENDIF.
    ELSEIF local_serv_path IS NOT INITIAL.
* *      Download in presentation server
      CALL FUNCTION 'GUI_DOWNLOAD'
        EXPORTING
          filename                = local_serv_path
          append                  = 'X'
        TABLES
          data_tab                = it_xls_spool
        EXCEPTIONS
          file_write_error        = 1
          no_batch                = 2
          gui_refuse_filetransfer = 3
          invalid_type            = 4
          no_authority            = 5
          unknown_error           = 6
          header_not_allowed      = 7
          separator_not_allowed   = 8
          filesize_not_allowed    = 9
          header_too_long         = 10
          dp_error_create         = 11
          dp_error_send           = 12
          dp_error_write          = 13
          unknown_dp_error        = 14
          access_denied           = 15
          dp_out_of_memory        = 16
          disk_full               = 17
          dp_timeout              = 18
          file_not_found          = 19
          dataprovider_exception  = 20
          control_flush_error     = 21
          OTHERS                  = 22.
      IF sy-subrc = 0.
        MESSAGE 'File succesfully transferred' TYPE 'S' .
      ENDIF.
    ELSE.
      MESSAGE 'Please specify either Application/presentation server path' TYPE 'E' .
    ENDIF.
  ENDIF.

Sandra_Rossi · ‎2021 Jul 13

What Excel file do you expect? The one you show?

siongchao_ng · ‎2021 Jul 13

sandra.rossi

1 excel column per 1 column, not as image all crammed into 1 excel column

Sandra_Rossi · ‎2021 Jul 13

According to me, it's very complex and unsafe to decode a spool file to generate an Excel file. You should generate the Excel file at the same time when the spool file is generated, from the original data (which maybe is contained in an internal table). Maybe you have a standard option in the original report to generate the Excel file?

siongchao_ng · ‎2021 Jul 14

Problem solved. can cut the spool output into individual fields

LOOP AT lt_soli INTO lw_soli.
*        IF lw_soli-line CS 'FS Item'.
        IF lw_soli-line CS '---'.
          CONTINUE.
        ELSEIF lw_soli-line CS '|---'.
          CONTINUE.
        ELSEIF lw_soli-line CS '|'.
          SPLIT lw_soli-line AT '|' INTO gv_string1 gv_string2.
          CONCATENATE gv_string_zfi054 gv_string1 cl_bcs_convert=>gc_tab INTO gv_string_zfi054.
        ENDIF.

        WHILE gv_string2 CS '|'.
          CLEAR gv_string1.
          SPLIT gv_string2 AT '|' INTO gv_string1 gv_string2.
          CONDENSE gv_string1.

*       check whether it is the last field (need to enter new line)
          IF gv_string2 IS INITIAL.
            CONCATENATE gv_string_zfi054 gv_string1 cl_bcs_convert=>gc_crlf "to go to next line
            INTO gv_string_zfi054.
          ELSE.
            CONCATENATE gv_string_zfi054 gv_string1 cl_bcs_convert=>gc_tab INTO gv_string_zfi054.
          ENDIF.

        ENDWHILE.


      ENDLOOP.

      CONDENSE lv_xls_size.
      cl_bcs_convert=>string_to_solix(
     EXPORTING
          iv_string = gv_string_zfi054     " your delimited string
        iv_codepage = '4103' " for MS Excel
      iv_add_bom = 'X'
       IMPORTING
        et_solix = gt_xls_zfi054       " the binary, XLS file
            ev_size = lv_xls_size ).

rajdhakal · ‎2024 Oct 24

Hello,

Thanks for the solution but the column names are repeating, have you found a way to eliminate the column names repeating?

SAPSAI21 · ‎2024 Nov 23

Hi,

can you share the complete code that you wrote for this requirement?

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

spool to excel