DOI输出excel单元格的填充颜色修改

DOI输出excel单元格的时候修改填充颜色。

*定义部分
DATA: GV_ITEM_URL(256), " 存放模板的URL .
      GV_INPLACE       TYPE C VALUE 'X',
      GV_NO_FLUSH(1)   TYPE C VALUE 'X',
      GV_UPDATING      TYPE I VALUE '-1'.

DATA: GO_CONTAINER     TYPE REF TO CL_GUI_CUSTOM_CONTAINER, "容器实例
      GO_CONTROL       TYPE REF TO I_OI_CONTAINER_CONTROL, "控制器实例
      GO_DOCUMENT      TYPE REF TO I_OI_DOCUMENT_PROXY,    "文档操作对象
      GV_DOCUMENT_TYPE TYPE SOI_DOCUMENT_TYPE,
      GO_SPREADSHEET   TYPE REF TO I_OI_SPREADSHEET,       "分隔符对象
      GO_ERROR         TYPE REF TO I_OI_ERROR,             "错误信息
      GV_RETCODE       TYPE SOI_RET_STRING,
      GO_ERRORS        TYPE REF TO I_OI_ERROR OCCURS 0 WITH HEADER LINE. "错误信息

DATA: GT_SHEETS TYPE SOI_SHEETS_TABLE,
      GS_SHEET  TYPE SOI_SHEETS.

*声明
*&---------------------------------------------------------------------*
*& Module DISP_EXCEL OUTPUT
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
MODULE DISP_EXCEL OUTPUT.

  PERFORM FRM_DISP_EXCEL.

ENDMODULE.
*&---------------------------------------------------------------------*
*& Form FRM_DISP_EXCEL
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM FRM_DISP_EXCEL .

  PERFORM FRM_CREATE_BASIC_OBJECT .
*
  PERFORM SELECT_SHEET.
*
  PERFORM FRM_OUTPUT_EXCEL.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_CREATE_BASIC_OBJECT
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM FRM_CREATE_BASIC_OBJECT .

  DATA: L_APP_NAME(200).
  DATA: LO_BDS_INSTANCE     TYPE REF TO CL_BDS_DOCUMENT_SET.
  DATA: LT_DOC_SIGNATURE  TYPE SBDST_SIGNATURE,
        LS_DOC_SIGNATURE  LIKE LINE OF LT_DOC_SIGNATURE,
        LT_DOC_COMPONENTS TYPE SBDST_COMPONENTS,
        LT_DOC_URIS       TYPE SBDST_URI,
        LS_DOC_URIS       LIKE LINE OF LT_DOC_URIS.

  STATICS:L_INITIALIZED TYPE C.
* get the SAP DOI interface references.
* this work has just to be done once !

  CHECK L_INITIALIZED IS INITIAL.
* first get the SAP DOI i_oi_container_control interface
  CALL METHOD C_OI_CONTAINER_CONTROL_CREATOR=>GET_CONTAINER_CONTROL
    IMPORTING
      CONTROL = GO_CONTROL
      ERROR   = GO_ERROR.
* check no errors occured
  CALL METHOD GO_ERROR->RAISE_MESSAGE
    EXPORTING
      TYPE = 'E'.

  L_APP_NAME = SY-REPID.

* initialize the SAP DOI Container, tell it to run in the container
* specified above and tell it to run Excel in-place
  CALL METHOD GO_CONTROL->INIT_CONTROL
    EXPORTING
      R3_APPLICATION_NAME      = L_APP_NAME
      INPLACE_ENABLED          = 'X'
      INPLACE_SCROLL_DOCUMENTS = 'X'
      PARENT                   = CL_GUI_CONTAINER=>SCREEN0 "container
      REGISTER_ON_CLOSE_EVENT  = 'X'
      REGISTER_ON_CUSTOM_EVENT = 'X'
      NO_FLUSH                 = 'X'
    IMPORTING
      ERROR                    = GO_ERRORS.
* save error object in collection
  APPEND GO_ERRORS.

* predefined excel doc. was saved in R/3
* business document service: OAOR
* here we get the URL according to its information.
  CLEAR GV_ITEM_URL.

  GV_DOCUMENT_TYPE = 'EXCEL.SHEET'.
  LS_DOC_SIGNATURE-PROP_NAME = 'BDS_KEYWORD'.
  LS_DOC_SIGNATURE-PROP_VALUE = GC_DOCUMENT_NAME .

  APPEND LS_DOC_SIGNATURE TO LT_DOC_SIGNATURE.

  CREATE OBJECT LO_BDS_INSTANCE.

  CALL METHOD LO_BDS_INSTANCE->GET_INFO
    EXPORTING
      CLASSNAME  = GC_DOC_CLASSNAME
      CLASSTYPE  = GC_DOC_CLASSTYPE
      OBJECT_KEY = GC_DOC_OBJECT_KEY
    CHANGING
      COMPONENTS = LT_DOC_COMPONENTS
      SIGNATURE  = LT_DOC_SIGNATURE.

  CALL METHOD LO_BDS_INSTANCE->GET_WITH_URL
    EXPORTING
      CLASSNAME  = GC_DOC_CLASSNAME
      CLASSTYPE  = GC_DOC_CLASSTYPE
      OBJECT_KEY = GC_DOC_OBJECT_KEY
    CHANGING
      URIS       = LT_DOC_URIS
      SIGNATURE  = LT_DOC_SIGNATURE.
  FREE LO_BDS_INSTANCE.

  READ TABLE LT_DOC_URIS INTO LS_DOC_URIS INDEX GC_URL_IND.

  GV_ITEM_URL = LS_DOC_URIS-URI.

* ask the SAP DOI container for a i_oi_document_proxy for Excel
  CALL METHOD GO_CONTROL->GET_DOCUMENT_PROXY
    EXPORTING
      DOCUMENT_TYPE  = GV_DOCUMENT_TYPE
*     REGISTER_CONTAINER = 'X'
    IMPORTING
      DOCUMENT_PROXY = GO_DOCUMENT
      ERROR          = GO_ERROR.
  APPEND GO_ERRORS.

* open a document saved in business document service.
  CALL METHOD GO_DOCUMENT->OPEN_DOCUMENT
    EXPORTING
      OPEN_INPLACE = GV_INPLACE
      DOCUMENT_URL = GV_ITEM_URL.
* check if our document proxy can serve a spreadsheet interface

  DATA: HAS TYPE I.
  CALL METHOD GO_DOCUMENT->HAS_SPREADSHEET_INTERFACE
    IMPORTING
      IS_AVAILABLE = HAS.
  APPEND GO_ERRORS.

  IF NOT HAS IS INITIAL.
    CALL METHOD GO_DOCUMENT->GET_SPREADSHEET_INTERFACE
*     EXPORTING no_flush        = no_flush
      IMPORTING
        SHEET_INTERFACE = GO_SPREADSHEET.
    APPEND GO_ERRORS.
  ENDIF.

  LOOP AT GO_ERRORS.
    CALL METHOD GO_ERRORS->RAISE_MESSAGE
      EXPORTING
        TYPE = 'E'.
  ENDLOOP.

  FREE GO_ERRORS.

  L_INITIALIZED = 'X'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SELECT_SHEET
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM SELECT_SHEET .
  CALL METHOD GO_SPREADSHEET->GET_SHEETS
    EXPORTING
      NO_FLUSH = ''
      UPDATING = GV_UPDATING
    IMPORTING
      SHEETS   = GT_SHEETS
      ERROR    = GO_ERROR
      RETCODE  = GV_RETCODE.

  CALL METHOD GO_ERROR->RAISE_MESSAGE
    EXPORTING
      TYPE = 'E'.

  READ TABLE GT_SHEETS INTO GS_SHEET INDEX 1.

  CALL METHOD GO_SPREADSHEET->SELECT_SHEET
    EXPORTING
      NAME     = GS_SHEET-SHEET_NAME
      NO_FLUSH = ''
    IMPORTING
      ERROR    = GO_ERROR.

  CALL METHOD GO_ERROR->RAISE_MESSAGE
    EXPORTING
      TYPE = 'E'.

  CALL METHOD GO_SPREADSHEET->GET_ACTIVE_SHEET
    EXPORTING
      NO_FLUSH  = ''
    IMPORTING
      SHEETNAME = GS_SHEET-SHEET_NAME
      ERROR     = GO_ERROR
      RETCODE   = GV_RETCODE.

  CALL METHOD GO_ERROR->RAISE_MESSAGE
    EXPORTING
      TYPE = 'E'.

  LOOP AT GO_ERRORS.
    CALL METHOD GO_ERRORS->RAISE_MESSAGE
      EXPORTING
        TYPE = 'E'.
  ENDLOOP.
ENDFORM.

*循环填充单元格
*&---------------------------------------------------------------------*
*& Form FRM_OUTPUT_EXCEL
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM FRM_OUTPUT_EXCEL .
  DATA: LV_BUTXT TYPE C LENGTH 256,
        LV_VALUE TYPE C LENGTH 256.
  DATA: LV_ROW TYPE I,
        LV_COL TYPE I.

  DATA: LV_LINES TYPE I.
  DATA: LV_FIELD TYPE CHAR30.
  FIELD-SYMBOLS <FS_FIELD>.

  DATA:LS_CONTENT     TYPE SOI_GENERIC_ITEM,
       LT_CONTENT     TYPE SOI_GENERIC_TABLE, "为SET_RANGE_DATA方法提供填充数据
       LS_FORMATTABLE TYPE  SOI_FORMAT_ITEM, "设置范围格式
       LT_FORMATTABLE TYPE TABLE OF SOI_FORMAT_ITEM. "设置范围格式

  FIELD-SYMBOLS:<FS_FILED>,
                <FS_CONTENT_FIELD>.

  SELECT SINGLE BUTXT
    INTO LV_BUTXT
    FROM T001
   WHERE BUKRS IN S_BUKRS .

  LV_BUTXT =  '编制单位：' && LV_BUTXT.
  CONDENSE LV_BUTXT NO-GAPS .

  REFRESH: LT_CONTENT,LT_FORMATTABLE.

  LT_CONTENT = VALUE #( ( ROW = 1 COLUMN = 1 VALUE = LV_BUTXT ) ).
  PERFORM FRM_FILL_RANGE TABLES LT_CONTENT
                                LT_FORMATTABLE
                          USING 1
                                3
                                1
                                1
                                ''.
  REFRESH: LT_CONTENT,LT_FORMATTABLE.
  LV_VALUE = '所属期间：' && P_GJAHR &&
              '年' &&  P_POPER+1(2) && '月'.
  LT_CONTENT = VALUE #( ( ROW = 1 COLUMN = 1 VALUE = LV_VALUE ) ).
  PERFORM FRM_FILL_RANGE TABLES LT_CONTENT
                                LT_FORMATTABLE
                          USING 2
                                3
                                1
                                1
                                ''.
  REFRESH: LT_CONTENT,LT_FORMATTABLE.
  DESCRIBE TABLE GT_ALV LINES LV_LINES."填充数据行数

  DATA: LV_LEFT TYPE I.

  LOOP AT GT_EXCEL INTO GS_EXCEL.
    DATA(LV_COUNT_ROW) = SY-TABIX.
    LV_COUNT_ROW = LV_COUNT_ROW + 4.
    CLEAR LV_LEFT.
    CLEAR LV_COL.
    DO 6 TIMES.
      REFRESH: LT_CONTENT,LT_FORMATTABLE.
      LV_COL = SY-INDEX.
      LV_LEFT = LV_LEFT + 1.
      ASSIGN COMPONENT SY-INDEX OF STRUCTURE GS_EXCEL TO <FS_FILED>.
      LS_CONTENT-ROW = LV_COUNT_ROW.

      LS_CONTENT-COLUMN = LV_COL.

      IF <FS_FILED> IS ASSIGNED.
        MOVE <FS_FILED> TO LS_CONTENT-VALUE.
        CONDENSE LS_CONTENT-VALUE NO-GAPS.
        APPEND LS_CONTENT TO LT_CONTENT.

        IF ( LV_COL = 6 AND LS_CONTENT-VALUE IS NOT INITIAL ) OR
            GS_EXCEL-NAME1 = '银行承兑汇票小计：' OR
            GS_EXCEL-NAME1 = '商业承兑汇票小计：' OR
            GS_EXCEL-NAME1 = '信用证小计：' OR
            GS_EXCEL-NAME1 = '合计：'.
          "填充单元格背景颜色需要用到方法（SET_RANGES_FORMAT），LT_FORMATTABLE这个内表
          "里面是单元格的格式设置，其中BACK参数是背景颜色，数值格式，测试了从1到57有不同的颜色
          LT_FORMATTABLE = VALUE #( ( NAME = 'RANGE1'  BACK = '20' ) ).
        ENDIF.
        PERFORM FRM_FILL_RANGE TABLES LT_CONTENT
                                      LT_FORMATTABLE
                                USING LV_LEFT
                                      LV_COUNT_ROW
                                      1
                                      1
                                      'X'.
      ENDIF.
      CLEAR LS_CONTENT.
    ENDDO.
    CLEAR GS_ALV.
  ENDLOOP.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_FILL_RANGE
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      --> LT_CONTENT
*&      --> LV_LEFT
*&      --> LV_TOP
*&      --> LV_LINES
*&      --> LV_COLUMNS
*&---------------------------------------------------------------------*
FORM FRM_FILL_RANGE  TABLES   PT_CONTENT
                              PT_FORMATTABLE
                      USING   PV_LEFT    TYPE I
                              PV_TOP     TYPE I
                              PV_LINES   TYPE I
                              PV_COLUMNS TYPE I
                              PV_FLAG.
  DATA: LS_RANGE TYPE SOI_RANGE_ITEM,
        LT_RANGE TYPE SOI_RANGE_LIST. "为SET_RANGE_DATA方法标明填充数据的范围名及其行列数

  CALL METHOD GO_SPREADSHEET->INSERT_RANGE_DIM
    EXPORTING
      NO_FLUSH = 'X'
      NAME     = 'RANGE1'
      LEFT     = PV_LEFT
      TOP      = PV_TOP
      ROWS     = PV_LINES
      COLUMNS  = PV_COLUMNS.

  LT_RANGE = VALUE #( ( NAME = 'RANGE1' ROWS = PV_LINES COLUMNS = PV_COLUMNS ) ).

  "填充数据
  CALL METHOD GO_SPREADSHEET->SET_RANGES_DATA
    EXPORTING
      NO_FLUSH = 'X'
      RANGES   = LT_RANGE
      CONTENTS = PT_CONTENT[].
  "设置格式
  IF PV_FLAG = 'X'.
    CALL METHOD GO_SPREADSHEET->SET_RANGES_FORMAT
      EXPORTING
        FORMATTABLE = PT_FORMATTABLE[]
        NO_FLUSH    = 'X'.

    "设置范围边框格式
    CALL METHOD GO_SPREADSHEET->SET_FRAME
      EXPORTING
        RANGENAME = 'RANGE1'
        TYP       = '127'
        COLOR     = 1.
    "设置列自动优化
    CALL METHOD GO_SPREADSHEET->FIT_WIDEST
      EXPORTING
        NO_FLUSH = 'X'
        NAME     = SPACE.
  ENDIF.
ENDFORM.