ABAP ABAP2XLSX 开源项目 excel 发送邮件附件demo,设置列宽 颜色控制

*&---------------------------------------------------------------------*
*& Report  ZDEMO_EXCEL50
*&
*&---------------------------------------------------------------------*
*& Like ZDEMO_EXCEL3 but with ZCL_EXCEL_WRITER_HUGE_FILE writer class
*&
*&---------------------------------------------------------------------*

REPORT zdemo_ven_inact02.
TYPES:BEGIN OF ts_test,
        name1 TYPE string,
        name2 TYPE string,
        name3 TYPE string,
        name4 TYPE string,
        name5 TYPE string,
      END OF ts_test.

TYPES: ty_sflight_lines TYPE TABLE OF ts_test,
       ty_scarr_lines   TYPE TABLE OF scarr.

DATA: lo_excel     TYPE REF TO zcl_excel,
      lo_worksheet TYPE REF TO zcl_excel_worksheet,
      lo_column    TYPE REF TO zcl_excel_column.

DATA: ls_table_settings       TYPE zexcel_s_table_settings.


DATA: lv_title TYPE zexcel_sheet_title,
      lt_carr  TYPE ty_scarr_lines,
      row      TYPE zexcel_cell_row VALUE 2,
      ls_error TYPE zcl_excel_worksheet=>mty_s_ignored_errors,
      lt_error TYPE zcl_excel_worksheet=>mty_th_ignored_errors,
      lo_range TYPE REF TO zcl_excel_range.
DATA: lo_data_validation TYPE REF TO zcl_excel_data_validation.
FIELD-SYMBOLS: <carr> LIKE LINE OF lt_carr.

CONSTANTS: c_airlines TYPE string VALUE 'Airlines'.


CONSTANTS: gc_save_file_name TYPE string VALUE '50_iTab.xlsx'.
*&---------------------------------------------------------------------*
*&  Include           ZDEMO_EXCEL_OUTPUTOPT_INCL
*&---------------------------------------------------------------------*
CLASS lcl_output DEFINITION CREATE PRIVATE.
  PUBLIC SECTION.
    CLASS-METHODS:
      output         IMPORTING cl_excel            TYPE REF TO zcl_excel
                               iv_writerclass_name TYPE clike OPTIONAL
                               iv_info_message     TYPE abap_bool DEFAULT abap_true
                     RAISING   zcx_excel.

  PRIVATE SECTION.
    METHODS:
      send_email.

    DATA: xdata     TYPE xstring,             " Will be used for sending as email
          t_rawdata TYPE solix_tab,           " Will be used for downloading or open directly
          bytecount TYPE i.                   " Will be used for downloading or open directly
ENDCLASS.                    "lcl_output DEFINITION


*----------------------------------------------------------------------*
*       CLASS lcl_output IMPLEMENTATION
*----------------------------------------------------------------------*
CLASS lcl_output IMPLEMENTATION.

  METHOD output.

    DATA: cl_output TYPE REF TO lcl_output,
          cl_writer TYPE REF TO zif_excel_writer,
          cl_error  TYPE REF TO zcx_excel.

    TRY.

        IF iv_writerclass_name IS INITIAL.
          CREATE OBJECT cl_output.
          CREATE OBJECT cl_writer TYPE zcl_excel_writer_2007.
        ELSE.
          CREATE OBJECT cl_output.
          CREATE OBJECT cl_writer TYPE (iv_writerclass_name).
        ENDIF.
        cl_output->xdata = cl_writer->write_file( cl_excel ).

        cl_output->t_rawdata = cl_bcs_convert=>xstring_to_solix( iv_xstring  = cl_output->xdata ).
        cl_output->bytecount = xstrlen( cl_output->xdata ).

        cl_output->send_email( ).

      CATCH zcx_excel INTO cl_error.
        IF iv_info_message = abap_true.
          MESSAGE cl_error TYPE 'I' DISPLAY LIKE 'E'.
        ELSE.
          RAISE EXCEPTION cl_error.
        ENDIF.
    ENDTRY.

  ENDMETHOD.                    "output

  METHOD send_email.
* Needed to send emails
    DATA: bcs_exception        TYPE REF TO cx_bcs,
          errortext            TYPE string,
          cl_send_request      TYPE REF TO cl_bcs,
          cl_document          TYPE REF TO cl_document_bcs,
          cl_recipient         TYPE REF TO if_recipient_bcs,
          cl_sender            TYPE REF TO cl_cam_address_bcs,
          t_attachment_header  TYPE soli_tab,
          wa_attachment_header LIKE LINE OF t_attachment_header,
          attachment_subject   TYPE sood-objdes,

          sood_bytecount       TYPE sood-objlen,
          mail_title           TYPE so_obj_des,
          t_mailtext           TYPE soli_tab,
          wa_mailtext          LIKE LINE OF t_mailtext,
          send_to              TYPE adr6-smtp_addr,
          sent                 TYPE abap_bool.


    mail_title     = 'Mail title'.
    wa_mailtext    = 'Mailtext'.
    APPEND wa_mailtext TO t_mailtext.

    TRY.
* Create send request
        cl_send_request = cl_bcs=>create_persistent( ).
* Create new document with mailtitle and mailtextg
        cl_document = cl_document_bcs=>create_document( i_type    = 'RAW' "#EC NOTEXT
                                                        i_text    = t_mailtext
                                                        i_subject = mail_title ).
* Add attachment to document
* since the new excelfiles have an 4-character extension .xlsx but the attachment-type only holds 3 charactes .xls,
* we have to specify the real filename via attachment header
* Use attachment_type xls to have SAP display attachment with the excel-icon
        attachment_subject  = gc_save_file_name.
        CONCATENATE '&SO_FILENAME=' attachment_subject INTO wa_attachment_header.
        APPEND wa_attachment_header TO t_attachment_header.
* Attachment
        sood_bytecount = bytecount.  " next method expects sood_bytecount instead of any positive integer *sigh*
        cl_document->add_attachment(  i_attachment_type    = 'XLS' "#EC NOTEXT
                                      i_attachment_subject = attachment_subject
                                      i_attachment_size    = sood_bytecount
                                      i_att_content_hex    = t_rawdata
                                      i_attachment_header  = t_attachment_header ).

* add document to send request
        cl_send_request->set_document( cl_document ).

* add recipient(s) - here only 1 will be needed
        DATA(lv_mail) = 'ztest@163.com'.
        send_to = lv_mail.
        IF send_to IS INITIAL.
          send_to = 'no_email@no_email.no_email'.  " Place into SOST in any case for demonstration purposes
        ENDIF.
        cl_recipient = cl_cam_address_bcs=>create_internet_address( send_to ).
        cl_send_request->add_recipient( cl_recipient ).

* Und abschicken
        sent = cl_send_request->send( i_with_error_screen = 'X' ).

        COMMIT WORK.

        IF sent = abap_true.
          MESSAGE s805(zabap2xlsx).
          MESSAGE 'Document ready to be sent - Check SOST or SCOT' TYPE 'I'.
        ELSE.
          MESSAGE i804(zabap2xlsx) WITH lv_mail.
        ENDIF.

      CATCH cx_bcs INTO bcs_exception.
        errortext = bcs_exception->if_message~get_text( ).
        MESSAGE errortext TYPE 'I'.

    ENDTRY.
  ENDMETHOD.                    "send_email


ENDCLASS.                    "lcl_output IMPLEMENTATION



START-OF-SELECTION.
  " Creates active sheet
  CREATE OBJECT lo_excel.

  DATA  lo_style_filled_turquoise TYPE REF TO zcl_excel_style.
  " Create filled style turquoise using legacy excel ver <= 2003 palette. (https://github.com/abap2xlsx/abap2xlsx/issues/92)

  lo_style_filled_turquoise                 = lo_excel->add_new_style( ).
  lo_excel->legacy_palette->set_color( "replace built-in color from palette with out custom RGB turquoise
      ip_index =     1
      ip_color =   '2' ).

  lo_style_filled_turquoise->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
  lo_style_filled_turquoise->fill->bgcolor-rgb  = zcl_excel_style_color=>c_white.
  lo_style_filled_turquoise->fill->fgcolor-indexed  = 1.

  " Get active sheet
  lo_worksheet = lo_excel->get_active_worksheet( ).
  lo_worksheet->set_title( ip_title = 'Internal table' ).

  DATA lt_test TYPE ty_sflight_lines.

*  IF p_empty <> abap_true.
*    IF p_checkr = abap_true.
*      PERFORM load_fixed_data_for_checker CHANGING lt_test.

*      SELECT * FROM sflight INTO TABLE lt_test.         "#EC CI_NOWHERE
  lt_test = VALUE #(
               ( name1 = 'Ann'  name2 = 'AnnAnnAnnAnnAnnAnnAnnAnnAnnAnn' name3 = 'Ann' name4 = 'Ann' name5 = 'Ann' )
               ).

*  ls_table_settings-table_style      = zcl_excel_table=>builtinstyle_medium2.
*  ls_table_settings-table_style      = zcl_excel_table=>builtinstyle_medium15.
*  ls_table_settings-show_row_stripes = abap_true.
  ls_table_settings-show_row_stripes    = abap_false.
  ls_table_settings-show_column_stripes = abap_false.
  ls_table_settings-nofilters           = abap_true.

  lo_worksheet->bind_table( ip_table          = lt_test
                            is_table_settings = ls_table_settings
                             ).

  PERFORM set_column_headers USING lo_worksheet
    'Vendor Code;EN name;CN name;Purchasing Organization;Inactive'
    lo_style_filled_turquoise .


*** Create output
  lcl_output=>output( cl_excel = lo_excel iv_writerclass_name = 'ZCL_EXCEL_WRITER_HUGE_FILE' ).

FORM set_column_headers
    USING io_worksheet TYPE REF TO zcl_excel_worksheet
          iv_headers   TYPE csequence
          lo_style_filled_turquoise TYPE REF TO zcl_excel_style
    RAISING zcx_excel.

  DATA: lt_headers TYPE TABLE OF string,
        lv_header  TYPE string,
        lv_tabix   TYPE i.
  DATA :lo_style_filled           TYPE REF TO zcl_excel_style.



  SPLIT iv_headers AT ';' INTO TABLE lt_headers.
  LOOP AT lt_headers INTO lv_header.
    lv_tabix = sy-tabix.
    io_worksheet->set_cell( ip_row = 1 ip_column = lv_tabix ip_value = lv_header ip_style = lo_style_filled_turquoise ).
    CASE lv_tabix.
      WHEN 1.
        DATA(lv_width_fix)  = 10.
      WHEN 2.
        lv_width_fix  = 100.
      WHEN 3.
        lv_width_fix  = 100.
      WHEN OTHERS.
        CLEAR sy-subrc.
    ENDCASE.
    io_worksheet->set_column_width( ip_column = lv_tabix  ip_width_fix =  lv_width_fix  ).
  ENDLOOP.

ENDFORM.