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.