*调用Excel（包括合并单元格、改变字体、调整列宽）
REPORT Z_XGFI_R_QTYWLR1.
TABLES : VBRP, VBRK, CKMLHD, CKMLCR, TSPAT.
TYPE-POOLS: SLIS.
INCLUDE OLE2INCL.
DATA: G_FIELDCAT_ALV TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE,
G_LAYOUT TYPE SLIS_LAYOUT_ALV, "alv???
G_FIELDCAT TYPE SLIS_FIELDCAT_ALV,
G_REPID LIKE SY-REPID.
DATA: IT_EVENT TYPE SLIS_T_EVENT,
IT_HEADER TYPE SLIS_T_LISTHEADER .
CONSTANTS: C_FN_TOP_PAGE TYPE SLIS_FORMNAME VALUE 'ALV_TOP_OF_PAGE'.
DATA: EXCEL_OBJ TYPE OLE2_OBJECT,
BOOK_OBJ TYPE OLE2_OBJECT,
SHEET_OBJ TYPE OLE2_OBJECT,
CELL_OBJ TYPE OLE2_OBJECT,
ROW_OBJ TYPE OLE2_OBJECT,
COLUMN_OBJ TYPE OLE2_OBJECT,
RANGE_OBJ TYPE OLE2_OBJECT,
BORDERS_OBJ TYPE OLE2_OBJECT,
INT_OBJ TYPE OLE2_OBJECT,
FONT_OBJ TYPE OLE2_OBJECT.
TYPES: BEGIN OF T_OUT,
SPART TYPE SPART, "产品组
ARKTX TYPE ARKTX, "销售订单项目短文本
MATNR TYPE MATNR, "物料号
FKIMG TYPE FKIMG, "实际已开票数量
DWSR TYPE CK_PVPRS_1, "单位售价
XSSR TYPE NETWR_FP, "销售收入
DWCB TYPE CK_PVPRS_1, "单位成本
XSCB TYPE NETWR_FP, "销售成本
DWSJ TYPE CK_PVPRS_1, "单位税金
XSSJ TYPE NETWR_FP, "销售税金
DWLR TYPE CK_PVPRS_1, "单位利润
XSLR TYPE NETWR_FP, "销售利润
END OF T_OUT.
DATA: IT_OUT TYPE STANDARD TABLE OF T_OUT,
WA_OUT TYPE T_OUT.
TYPES: BEGIN OF T_OUT2,
YWMS TYPE ARKTX, "业务描述
QTSR TYPE NETWR_FP, "业务收入
QTCB TYPE NETWR_FP, "业务成本
END OF T_OUT2.
DATA: IT_OUT2 TYPE STANDARD TABLE OF T_OUT2,
WA_OUT2 TYPE T_OUT2.
DATA: G_TITLE(20),
G_NAME(30),
G_DATE(20).
DATA: G_LINES TYPE I,
G_LINES2 TYPE I.
SELECTION-SCREEN BEGIN OF BLOCK BLK1 WITH FRAME TITLE TEXT-001.
PARAMETERS: P_BUKRS TYPE BUKRS OBLIGATORY,
P_YEAR(4) TYPE N OBLIGATORY DEFAULT SY-DATUM+0(4),
P_MONTH(2) TYPE N OBLIGATORY DEFAULT SY-DATUM+4(2).
PARAMETERS: PRC_LJ AS CHECKBOX.
SELECTION-SCREEN SKIP 1.
PARAMETERS: PRB_ALV RADIOBUTTON GROUP G1 DEFAULT 'X',
PRB_XLS RADIOBUTTON GROUP G1 .
SELECTION-SCREEN END OF BLOCK BLK1.
AT SELECTION-SCREEN.
PERFORM FRM_CHECK_INPUT .
INITIALIZATION.
CLEAR: WA_OUT, WA_OUT2,G_TITLE, G_NAME, G_DATE, G_LINES, G_LINES2.
REFRESH: IT_OUT, IT_OUT2.
START-OF-SELECTION.
PERFORM FRM_EDIT_DATA.
IF G_LINES = 0 AND G_LINES2 = 0.
MESSAGE '没有找到数据' TYPE 'S' DISPLAY LIKE 'E'.
ELSE.
IF PRB_ALV = 'X'.
CLEAR WA_OUT.
LOOP AT IT_OUT2 INTO WA_OUT2.
WA_OUT-ARKTX = WA_OUT2-YWMS.
WA_OUT-XSSR = ABS( WA_OUT2-QTSR ).
WA_OUT-XSCB = ABS( WA_OUT2-QTCB ).
WA_OUT-XSLR = WA_OUT-XSSR - WA_OUT-XSCB.
APPEND WA_OUT TO IT_OUT.
CLEAR WA_OUT.
ENDLOOP.
PERFORM LAYOUT_BUILD. "用于定义ALV表单的相关格式、属性
PERFORM FIELDS. "用来定义表单中的各个列的相关信息，比如列名等
PERFORM BUILD_EVENTTAB USING IT_EVENT.
PERFORM DISPLAY_DATA. "用来显示ALV表单
ELSE.
PERFORM CREAT_EXCEL."创建EXCEL表单
PERFORM FORMAT_EXCEL."设置EXCEL格式
PERFORM PRM_OUTPUT_EXCEL. "用来显示EXCEL表单
ENDIF.
ENDIF.
*&---------------------------------------------------------------------*
*& Form FRM_EDIT_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM FRM_EDIT_DATA.
TYPES: BEGIN OF T_VBRP,
ARKTX TYPE ARKTX, "销售订单项目短文本
MATNR TYPE MATNR, "物料号
BWTAR TYPE BWTAR_D, "评估类型
SPART TYPE SPART, "产品组
FKART TYPE FKART, "开票类型
FKIMG TYPE FKIMG, "实际已开票数量
NETWR TYPE NETWR_FP, "出具发票项目的凭证货币净值
PVPRS TYPE CK_PVPRS_1, "周期单位价格
END OF T_VBRP.
DATA: IT_VBRP TYPE STANDARD TABLE OF T_VBRP,
IT_VBRP2 TYPE STANDARD TABLE OF T_VBRP,
WA_VBRP TYPE T_VBRP.
TYPES: BEGIN OF T_CKML,
MATNR TYPE MATNR, "物料号
BWTAR TYPE BWTAR_D, "评估类型
PVPRS TYPE CK_PVPRS_1, "周期单位价格
END OF T_CKML.
DATA: IT_CKML TYPE STANDARD TABLE OF T_CKML,
WA_CKML TYPE T_CKML.
TYPES: BEGIN OF T_FAG,
RACCT TYPE RACCT, "科目编号
HSL TYPE VLCUR12, "用本地货币表示的值
TXT50 TYPE TXT50_SKAT,"总帐科目成文本
END OF T_FAG.
DATA: IT_FAG TYPE STANDARD TABLE OF T_FAG,
IT_FAG2 TYPE STANDARD TABLE OF T_FAG,
WA_FAG TYPE T_FAG.
DATA: I_FDATE TYPE D,
I_LDATE TYPE D.
DATA I_LENTH TYPE I.
RANGES R_MONTH FOR FAGLFLEXA-POPER.
DATA I_MONTH TYPE I.
IF PRC_LJ = 'X'.
IF P_MONTH = '01'.
G_TITLE = '其他业务销售利润明细表（本月）'.
CONCATENATE P_YEAR '年' P_MONTH '月' INTO G_DATE.
ELSE.
G_TITLE = '其他业务销售利润明细表（累计）'.
CONCATENATE P_YEAR '年01月到' P_MONTH '月' INTO G_DATE.
ENDIF.
CONCATENATE P_YEAR '01' '01' INTO I_FDATE.
I_MONTH = 1.
DO P_MONTH TIMES.
R_MONTH-SIGN = 'I'.
R_MONTH-OPTION = 'EQ'.
R_MONTH-LOW = I_MONTH.
APPEND R_MONTH.
ADD 1 TO I_MONTH.
ENDDO.
ELSE.
G_TITLE = '其他业务销售利润明细表（本月）'.
CONCATENATE P_YEAR '年' P_MONTH '月' INTO G_DATE.
CONCATENATE P_YEAR P_MONTH '01' INTO I_FDATE.
R_MONTH-SIGN = 'I'.
R_MONTH-OPTION = 'EQ'.
R_MONTH-LOW = P_MONTH.
APPEND R_MONTH.
ENDIF.
PERFORM FRM_COUNT_DATE USING P_YEAR P_MONTH I_LDATE.
SELECT VBRP~ARKTX
VBRP~MATNR
VBRP~BWTAR
VBRP~SPART
VBRK~FKART
VBRP~FKIMG
VBRP~NETWR
INTO TABLE IT_VBRP
FROM VBRP
INNER JOIN VBRK ON VBRK~VBELN = VBRP~VBELN
WHERE VBRP~WERKS = P_BUKRS
AND VBRP~KTGRM = '02'
AND VBRK~FKDAT >= I_FDATE
AND VBRK~FKDAT <= I_LDATE.
LOOP AT IT_VBRP INTO WA_VBRP.
CASE WA_VBRP-FKART.
WHEN 'YF02' OR 'YF03' OR 'YF06' OR 'YF08'.
WA_VBRP-FKIMG = -1 * WA_VBRP-FKIMG.
WA_VBRP-NETWR = -1 * WA_VBRP-NETWR.
ENDCASE.
IF WA_VBRP-FKART = 'YF05' OR WA_VBRP-FKART = 'YF06'.
WA_VBRP-FKIMG = 0.
ENDIF.
WA_VBRP-FKART = ''.
COLLECT WA_VBRP INTO IT_VBRP2.
ENDLOOP.
SELECT CKMLHD~MATNR
CKMLHD~BWTAR
CKMLCR~PVPRS
INTO TABLE IT_CKML
FROM CKMLHD
INNER JOIN CKMLCR ON CKMLCR~KALNR = CKMLHD~KALNR
FOR ALL ENTRIES IN IT_VBRP2
WHERE CKMLHD~MATNR = IT_VBRP2-MATNR
AND CKMLHD~BWTAR = IT_VBRP2-BWTAR
AND CKMLHD~BWKEY = P_BUKRS
AND CKMLCR~BDATJ = P_YEAR
AND CKMLCR~POPER IN R_MONTH.
LOOP AT IT_VBRP2 INTO WA_VBRP.
READ TABLE IT_CKML INTO WA_CKML WITH KEY MATNR = WA_VBRP-MATNR
BWTAR = WA_VBRP-BWTAR.
IF SY-SUBRC = 0.
WA_VBRP-PVPRS = WA_CKML-PVPRS.
ENDIF.
MODIFY IT_VBRP2 FROM WA_VBRP.
ENDLOOP.
LOOP AT IT_VBRP2 INTO WA_VBRP.
WA_OUT-SPART = WA_VBRP-SPART."产品组
WA_OUT-ARKTX = WA_VBRP-ARKTX."产品名称
WA_OUT-MATNR = WA_VBRP-MATNR."物料号
WA_OUT-FKIMG = WA_VBRP-FKIMG."销售量
WA_OUT-XSSR = WA_VBRP-NETWR."销售收入
WA_OUT-DWCB = WA_VBRP-PVPRS."单位成本
WA_OUT-XSCB = WA_OUT-DWCB * WA_OUT-FKIMG. "销售成本
WA_OUT-XSLR = WA_OUT-XSSR - WA_OUT-XSCB - WA_OUT-XSSJ."销售利润
IF WA_OUT-FKIMG = 0.
WA_OUT-DWSR = 0.
WA_OUT-DWLR = 0.
ELSE.
WA_OUT-DWSR = WA_OUT-XSSR / WA_OUT-FKIMG. "单位售价
WA_OUT-DWLR = WA_OUT-XSLR / WA_OUT-FKIMG. "单位利润
ENDIF.
COLLECT WA_OUT INTO IT_OUT.
CLEAR WA_OUT.
ENDLOOP.
SORT IT_OUT BY SPART.
SELECT FAGLFLEXA~RACCT
FAGLFLEXA~HSL
SKAT~TXT50
INTO TABLE IT_FAG
FROM FAGLFLEXA
INNER JOIN SKAT ON SKAT~SAKNR = FAGLFLEXA~RACCT
WHERE FAGLFLEXA~RBUKRS = P_BUKRS
AND ( FAGLFLEXA~RACCT BETWEEN '6051010300' AND '6051999999'
OR FAGLFLEXA~RACCT BETWEEN '6402010200' AND '6402999999' )
AND FAGLFLEXA~RYEAR = P_YEAR
AND FAGLFLEXA~POPER IN R_MONTH
AND SKAT~SPRAS = '1'
AND SKAT~KTOPL = '8888'.
LOOP AT IT_FAG INTO WA_FAG.
COLLECT WA_FAG INTO IT_FAG2.
ENDLOOP.
LOOP AT IT_FAG2 INTO WA_FAG.
IF WA_FAG-RACCT+0(4) = '6051'.
WA_OUT2-QTSR = WA_FAG-HSL.
ELSEIF WA_FAG-RACCT+0(4) = '6402'.
WA_OUT2-QTCB = WA_FAG-HSL.
ENDIF.
IF WA_FAG-RACCT+0(6) = '605102'
OR WA_FAG-RACCT = '6402010200'.
WA_OUT2-YWMS = '宾馆'.
ELSEIF WA_FAG-RACCT+0(6) = '640290'.
WA_OUT2-YWMS = '差异'.
ELSE.
I_LENTH = STRLEN( WA_FAG-TXT50 ) - 7 - 2.
WA_OUT2-YWMS = WA_FAG-TXT50+7(I_LENTH).
ENDIF.
COLLECT WA_OUT2 INTO IT_OUT2.
CLEAR WA_OUT2.
ENDLOOP.
DESCRIBE TABLE IT_OUT LINES G_LINES.
DESCRIBE TABLE IT_OUT2 LINES G_LINES2.
ENDFORM. "FRM_EDIT_DATA
*&---------------------------------------------------------------------*
*& Form FRM_CHECK_INPUT
*&---------------------------------------------------------------------*
* 对屏幕参数的检查
*----------------------------------------------------------------------*
FORM FRM_CHECK_INPUT .
DATA I_BUKRS LIKE T001-BUKRS.
IF P_BUKRS IS NOT INITIAL.
SELECT SINGLE BUTXT INTO G_NAME FROM T001
WHERE BUKRS = P_BUKRS.
IF SY-SUBRC <> 0.
MESSAGE '公司代码错误' TYPE 'S' DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING .
STOP.
ENDIF.
ENDIF.
ENDFORM. " FRM_CHECK_INPUT
*&---------------------------------------------------------------------*
*& Form layout_build
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM LAYOUT_BUILD.
G_LAYOUT-ZEBRA = 'X'.
G_LAYOUT-DETAIL_POPUP = 'X'. "是否弹出详细信息窗口
G_REPID = SY-REPID. "程序为当前程序
G_LAYOUT-F2CODE = '&ETA'. "设置触发弹出详细信息窗口的功能码，这里是双击
G_LAYOUT-NO_VLINE = ''. "这个用来设置列间隔线
G_LAYOUT-COLWIDTH_OPTIMIZE = 'X'. "优化列宽选项是否设置
G_LAYOUT-DETAIL_INITIAL_LINES = 'X'.
G_LAYOUT-DETAIL_TITLEBAR = '详细内容'. "设置出窗口的标题栏
ENDFORM. "layout_build
*&---------------------------------------------------------------------*
*& Form fields
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM FIELDS.
REFRESH G_FIELDCAT_ALV.
CLEAR G_FIELDCAT.
G_FIELDCAT-COL_POS = 1. "第几列
G_FIELDCAT-FIELDNAME = 'ARKTX'.
G_FIELDCAT-SELTEXT_L = '产品名称'. "列名
APPEND G_FIELDCAT TO G_FIELDCAT_ALV.
CLEAR G_FIELDCAT.
G_FIELDCAT-COL_POS = 2.
G_FIELDCAT-FIELDNAME = 'MATNR'.
G_FIELDCAT-SELTEXT_L = '物料编号'.
APPEND G_FIELDCAT TO G_FIELDCAT_ALV.
CLEAR G_FIELDCAT.
G_FIELDCAT-COL_POS = 3.
G_FIELDCAT-FIELDNAME = 'SPART'.
G_FIELDCAT-SELTEXT_L = '产品组'.
APPEND G_FIELDCAT TO G_FIELDCAT_ALV.
CLEAR G_FIELDCAT.
G_FIELDCAT-COL_POS = 4.
G_FIELDCAT-FIELDNAME = 'FKIMG'.
G_FIELDCAT-SELTEXT_L = '销售量'.
APPEND G_FIELDCAT TO G_FIELDCAT_ALV.
CLEAR G_FIELDCAT.
G_FIELDCAT-COL_POS = 5.
G_FIELDCAT-FIELDNAME = 'DWSR'.
G_FIELDCAT-SELTEXT_L = '单位售价'.
APPEND G_FIELDCAT TO G_FIELDCAT_ALV.
CLEAR G_FIELDCAT.
G_FIELDCAT-COL_POS = 6.
G_FIELDCAT-FIELDNAME = 'XSSR'.
G_FIELDCAT-SELTEXT_L = '销售收入'.
APPEND G_FIELDCAT TO G_FIELDCAT_ALV.
CLEAR G_FIELDCAT.
G_FIELDCAT-COL_POS = 7.
G_FIELDCAT-FIELDNAME = 'DWCB'.
G_FIELDCAT-SELTEXT_L = '单位成本'.
APPEND G_FIELDCAT TO G_FIELDCAT_ALV.
CLEAR G_FIELDCAT.
G_FIELDCAT-COL_POS = 8.
G_FIELDCAT-FIELDNAME = 'XSCB'.
G_FIELDCAT-SELTEXT_L = '销售成本'.
APPEND G_FIELDCAT TO G_FIELDCAT_ALV.
CLEAR G_FIELDCAT.
G_FIELDCAT-COL_POS = 9.
G_FIELDCAT-FIELDNAME = 'DWSJ'.
G_FIELDCAT-SELTEXT_L = '单位税金'.
APPEND G_FIELDCAT TO G_FIELDCAT_ALV.
CLEAR G_FIELDCAT.
G_FIELDCAT-COL_POS = 10.
G_FIELDCAT-FIELDNAME = 'XSSJ'.
G_FIELDCAT-SELTEXT_L = '销售税金及附加'.
APPEND G_FIELDCAT TO G_FIELDCAT_ALV.
CLEAR G_FIELDCAT.
G_FIELDCAT-COL_POS = 11.
G_FIELDCAT-FIELDNAME = 'DWLR'.
G_FIELDCAT-SELTEXT_L = '单位利润'.
APPEND G_FIELDCAT TO G_FIELDCAT_ALV.
CLEAR G_FIELDCAT.
G_FIELDCAT-COL_POS = 12.
G_FIELDCAT-FIELDNAME = 'XSLR'.
G_FIELDCAT-SELTEXT_L = '销售利润'.
APPEND G_FIELDCAT TO G_FIELDCAT_ALV.
CLEAR G_FIELDCAT.
ENDFORM. "fields
*&---------------------------------------------------------------------*
*& Form display_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM DISPLAY_DATA.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
I_CALLBACK_PROGRAM = G_REPID "当前程序
I_SAVE = ''
IS_LAYOUT = G_LAYOUT "子函数layout_build填充的格式定义
IT_FIELDCAT = G_FIELDCAT_ALV[] "子函数fields填充的各列
IT_EVENTS = IT_EVENT " 标题
TABLES
T_OUTTAB = IT_OUT. "假设数据都在head1内表中
ENDFORM. "display_data
*&---------------------------------------------------------------------*
*& Form ALV_TOP_OF_PAGE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM ALV_TOP_OF_PAGE.
DATA: LH_LINE TYPE SLIS_LISTHEADER.
REFRESH IT_HEADER.
LH_LINE-TYP = 'H'.
LH_LINE-INFO = G_TITLE.
APPEND LH_LINE TO IT_HEADER.
LH_LINE-TYP = 'S'.
LH_LINE-INFO = G_NAME.
APPEND LH_LINE TO IT_HEADER.
LH_LINE-TYP = 'S'.
LH_LINE-INFO = G_DATE.
APPEND LH_LINE TO IT_HEADER.
CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'
EXPORTING
IT_LIST_COMMENTARY = IT_HEADER.
ENDFORM. " ALV_TOP_OF_PAGE
*&---------------------------------------------------------------------*
*& Form BUILD_EVENTTAB
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->UT_EVENT text
*----------------------------------------------------------------------*
FORM BUILD_EVENTTAB USING UT_EVENT TYPE SLIS_T_EVENT.
DATA: LH_EVENT TYPE SLIS_ALV_EVENT.
CALL FUNCTION 'REUSE_ALV_EVENTS_GET'
EXPORTING
I_LIST_TYPE = 0
IMPORTING
ET_EVENTS = UT_EVENT
EXCEPTIONS
OTHERS = 1.
READ TABLE UT_EVENT INTO LH_EVENT WITH KEY NAME = SLIS_EV_TOP_OF_PAGE.
IF SY-SUBRC = 0.
MOVE C_FN_TOP_PAGE TO LH_EVENT-FORM.
MODIFY UT_EVENT FROM LH_EVENT INDEX SY-TABIX.
ENDIF.
ENDFORM. "BUILD_EVENTTAB
*&---------------------------------------------------------------------*
*& Form CREAT_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM CREAT_EXCEL.
CREATE OBJECT EXCEL_OBJ 'excel.APPLICATION'.
IF SY-SUBRC NE 0.
MESSAGE 'EXCEL创建错误' TYPE 'S' DISPLAY LIKE 'E'.
STOP.
ENDIF.
CALL METHOD OF EXCEL_OBJ 'WORKBOOKS' = BOOK_OBJ .
SET PROPERTY OF EXCEL_OBJ 'VISIBLE' = 1.
SET PROPERTY OF EXCEL_OBJ 'SheetsInNewWorkbook' = 1.
CALL METHOD OF BOOK_OBJ 'ADD' = SHEET_OBJ.
CALL METHOD OF SHEET_OBJ 'ACTIVATE'.
FREE OBJECT SHEET_OBJ. "OK
ENDFORM. "CREAT_EXCEL
*&---------------------------------------------------------------------*
*& Form FORMAT_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM FORMAT_EXCEL.
PERFORM ROWHEIGHT USING '1' '40'.
PERFORM MERGERANGE USING 'A1:K1'.
PERFORM MERGERANGE USING 'G2:H2'.
PERFORM MERGERANGE USING 'A3:A4'.
PERFORM MERGERANGE USING 'B3:B4'.
PERFORM MERGERANGE USING 'C3:C4'.
PERFORM MERGERANGE USING 'D3:E3'.
PERFORM MERGERANGE USING 'F3:G3'.
PERFORM MERGERANGE USING 'H3:I3'.
PERFORM MERGERANGE USING 'J3:K3'.
PERFORM COLUMNWIDTH USING 'C' '12'.
PERFORM COLUMNWIDTH USING 'D' '11'.
PERFORM COLUMNWIDTH USING 'E' '13'.
PERFORM COLUMNWIDTH USING 'F' '11'.
PERFORM COLUMNWIDTH USING 'G' '13'.
PERFORM COLUMNWIDTH USING 'H' '11'.
PERFORM COLUMNWIDTH USING 'I' '13'.
PERFORM COLUMNWIDTH USING 'J' '11'.
PERFORM COLUMNWIDTH USING 'K' '13'.
PERFORM CELLTYPE USING 'A:B' '@'.
PERFORM CELLTYPE USING 'C:K' '0.00'.
PERFORM CELLTYPE USING '1:4' '@'.
PERFORM FONT USING 'A:K' 0 10 '宋体'.
PERFORM FONT USING 'A1' 0 '22' '隶书'.
PERFORM FONT USING 'A2:K3' 0 12 '宋体'.
PERFORM CENTERCELL USING 'A1'.
PERFORM CENTERCELL USING 'F2'.
PERFORM CENTERCELL USING 'A3:K4'.
PERFORM WRITECELL USING 2 6 '日期：'.
PERFORM WRITECELL USING 3 1 '产品名称'.
PERFORM WRITECELL USING 3 2 '物料编号'.
PERFORM WRITECELL USING 3 3 '销售量'.
PERFORM WRITECELL USING 3 4 '销售收入'.
PERFORM WRITECELL USING 3 6 '销售成本'.
PERFORM WRITECELL USING 3 8 '销售税金及附加'.
PERFORM WRITECELL USING 3 10 '销售利润'.
PERFORM WRITECELL USING 4 4 '单位售价'.
PERFORM WRITECELL USING 4 5 '金额'.
PERFORM WRITECELL USING 4 6 '单位成本'.
PERFORM WRITECELL USING 4 7 '金额'.
PERFORM WRITECELL USING 4 8 '单位税金'.
PERFORM WRITECELL USING 4 9 '金额'.
PERFORM WRITECELL USING 4 10 '单位利润'.
PERFORM WRITECELL USING 4 11 '金额'.
ENDFORM. "FORMAT_EXCEL
*&---------------------------------------------------------------------*
*& Form PRM_OUTPUT_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM PRM_OUTPUT_EXCEL.
DATA I_VTEXT TYPE VTXTK.
DATA I_LEN TYPE I.
DATA: I_JS(4) TYPE N,
I_SJS(4) TYPE N,
I_EJS(4) TYPE N,
I_JS1(4) TYPE N,
I_JS2(4) TYPE N,
I_DWSR(12),
I_XSCB(12),
I_DWSJ(12),
I_DWLR(12),
I_XSLR(18),
I_E(17),
I_G(17),
I_I(17),
I_K(17),
I_HE TYPE STRING,
I_HG TYPE STRING,
I_HI TYPE STRING,
I_HK TYPE STRING,
I_ZE(12),
I_ZG(12),
I_ZI(12),
I_ZK(12).
DATA I_END(11) TYPE N.
I_HE = '='.
I_HG = '='.
I_HI = '='.
I_HK = '='.
PERFORM WRITECELL USING 1 1 G_TITLE.
PERFORM WRITECELL USING 2 1 G_NAME.
PERFORM WRITECELL USING 2 7 G_DATE.
I_JS = 5.
IF G_LINES <> 0.
PERFORM WRITECELL USING I_JS 1 '销售'.
I_JS = I_JS + 1.
LOOP AT IT_OUT INTO WA_OUT.
AT NEW SPART.
SELECT SINGLE VTEXT FROM TSPAT INTO I_VTEXT
WHERE SPART = WA_OUT-SPART AND SPRAS = '1'.
CONCATENATE '' '' I_VTEXT INTO I_VTEXT SEPARATED BY SPACE.
PERFORM WRITECELL USING I_JS 1 I_VTEXT.
I_JS = I_JS + 1.
I_SJS = I_JS.
ENDAT.
CONCATENATE '=E' I_JS '/C' I_JS INTO I_DWSR.
CONCATENATE '=F' I_JS '*C' I_JS INTO I_XSCB.
CONCATENATE '=I' I_JS '/C' I_JS INTO I_DWSJ.
CONCATENATE '=K' I_JS '/C' I_JS INTO I_DWLR.
CONCATENATE '=E' I_JS '-G' I_JS '-I' I_JS INTO I_XSLR.
CONCATENATE '' '' '' '' WA_OUT-ARKTX INTO WA_OUT-ARKTX SEPARATED BY SPACE.
WA_OUT-MATNR = WA_OUT-MATNR+8.
PERFORM WRITECELL USING I_JS 1 WA_OUT-ARKTX.
PERFORM WRITECELL USING I_JS 2 WA_OUT-MATNR.
PERFORM WRITECELL USING I_JS 3 WA_OUT-FKIMG.
PERFORM WRITECELL USING I_JS 5 WA_OUT-XSSR.
PERFORM WRITECELL USING I_JS 6 WA_OUT-DWCB.
PERFORM WRITECELL USING I_JS 4 I_DWSR.
PERFORM WRITECELL USING I_JS 7 I_XSCB.
PERFORM WRITECELL USING I_JS 8 I_DWSJ.
PERFORM WRITECELL USING I_JS 10 I_DWLR.
PERFORM WRITECELL USING I_JS 11 I_XSLR.
I_JS = I_JS + 1.
AT END OF SPART.
I_EJS = I_JS - 1.
CONCATENATE '=SUM(E' I_SJS ':E' I_EJS ')' INTO I_E.
CONCATENATE '=SUM(G' I_SJS ':G' I_EJS ')' INTO I_G.
CONCATENATE '=SUM(I' I_SJS ':I' I_EJS ')' INTO I_I.
CONCATENATE '=SUM(K' I_SJS ':K' I_EJS ')' INTO I_K.
PERFORM WRITECELL USING I_JS 1 ' 小计'.
PERFORM WRITECELL USING I_JS 5 I_E.
PERFORM WRITECELL USING I_JS 7 I_G.
PERFORM WRITECELL USING I_JS 9 I_I.
PERFORM WRITECELL USING I_JS 11 I_K.
CONCATENATE I_HE 'E' I_JS '+' INTO I_HE.
CONCATENATE I_HG 'G' I_JS '+' INTO I_HG.
CONCATENATE I_HI 'I' I_JS '+' INTO I_HI.
CONCATENATE I_HK 'K' I_JS '+' INTO I_HK.
I_JS = I_JS + 1.
ENDAT.
AT LAST.
I_LEN = STRLEN( I_HE ) - 1.
I_HE = I_HE+0(I_LEN).
I_LEN = STRLEN( I_HG ) - 1.
I_HG = I_HG+0(I_LEN).
I_LEN = STRLEN( I_HI ) - 1.
I_HI = I_HI+0(I_LEN).
I_LEN = STRLEN( I_HK ) - 1.
I_HK = I_HK+0(I_LEN).
PERFORM WRITECELL USING I_JS 1 ' 合计'.
PERFORM WRITECELL USING I_JS 5 I_HE.
PERFORM WRITECELL USING I_JS 7 I_HG.
PERFORM WRITECELL USING I_JS 9 I_HI.
PERFORM WRITECELL USING I_JS 11 I_HK.
CONCATENATE '=E' I_JS INTO I_ZE.
CONCATENATE '=G' I_JS INTO I_ZG.
CONCATENATE '=I' I_JS INTO I_ZI.
CONCATENATE '=K' I_JS INTO I_ZK.
I_JS = I_JS + 1.
ENDAT.
ENDLOOP.
ENDIF.
IF G_LINES2 <> 0.
PERFORM WRITECELL USING I_JS 1 '其他业务'.
I_JS = I_JS + 1.
I_SJS = I_JS.
LOOP AT IT_OUT2 INTO WA_OUT2.
CONCATENATE '' '' WA_OUT2-YWMS INTO WA_OUT2-YWMS SEPARATED BY SPACE.
WA_OUT2-QTSR = ABS( WA_OUT2-QTSR ).
WA_OUT2-QTCB = ABS( WA_OUT2-QTCB ).
CONCATENATE '=E' I_JS '-G' I_JS '-I' I_JS INTO I_XSLR.
PERFORM WRITECELL USING I_JS 1 WA_OUT2-YWMS.
PERFORM WRITECELL USING I_JS 5 WA_OUT2-QTSR.
PERFORM WRITECELL USING I_JS 7 WA_OUT2-QTCB.
PERFORM WRITECELL USING I_JS 11 I_XSLR.
I_JS = I_JS + 1.
AT LAST.
I_EJS = I_JS - 1.
CONCATENATE '=SUM(E' I_SJS ':E' I_EJS ')' INTO I_E.
CONCATENATE '=SUM(G' I_SJS ':G' I_EJS ')' INTO I_G.
CONCATENATE '=SUM(I' I_SJS ':I' I_EJS ')' INTO I_I.
CONCATENATE '=SUM(K' I_SJS ':K' I_EJS ')' INTO I_K.
PERFORM WRITECELL USING I_JS 1 ' 合计'.
PERFORM WRITECELL USING I_JS 5 I_E.
PERFORM WRITECELL USING I_JS 7 I_G.
PERFORM WRITECELL USING I_JS 9 I_I.
PERFORM WRITECELL USING I_JS 11 I_K.
CONCATENATE I_ZE '+E' I_JS INTO I_ZE.
CONCATENATE I_ZG '+G' I_JS INTO I_ZG.
CONCATENATE I_ZI '+I' I_JS INTO I_ZI.
CONCATENATE I_ZK '+K' I_JS INTO I_ZK.
I_JS = I_JS + 1.
ENDAT.
ENDLOOP.
ENDIF.
IF G_LINES <> 0 AND G_LINES2 <> 0 .
PERFORM WRITECELL USING I_JS 1 '总计'.
PERFORM WRITECELL USING I_JS 5 I_ZE.
PERFORM WRITECELL USING I_JS 7 I_ZG.
PERFORM WRITECELL USING I_JS 9 I_ZI.
PERFORM WRITECELL USING I_JS 11 I_ZK.
I_JS = I_JS + 1.
ENDIF.
I_JS = I_JS - 1.
CONCATENATE 'A3:K' I_JS INTO I_END.
PERFORM BORDERRANGE USING I_END.
PERFORM COLUMNWIDTH USING 'A:B' ''.
ENDFORM. "PRM_OUTPUT_EXCEL
*&---------------------------------------------------------------------*
*& Form writecell
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->X text
* -->Y text
* -->V text
*----------------------------------------------------------------------*
FORM WRITECELL USING ROW COL VAL.
CALL METHOD OF EXCEL_OBJ 'CELLS' = CELL_OBJ
EXPORTING
#1 = ROW
#2 = COL.
SET PROPERTY OF CELL_OBJ 'VALUE' = VAL.
FREE OBJECT CELL_OBJ.
ENDFORM. "writecell
*&---------------------------------------------------------------------*
*& Form mergerange
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->RANGE text
*----------------------------------------------------------------------*
FORM MERGERANGE USING RANGE.
CALL METHOD OF EXCEL_OBJ 'RANGE' = CELL_OBJ
EXPORTING
#1 = RANGE.
CALL METHOD OF CELL_OBJ 'MERGE' .
FREE OBJECT CELL_OBJ.
ENDFORM. "mergerange
*&---------------------------------------------------------------------*
*& Form celltype
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->RANGE text
*----------------------------------------------------------------------*
FORM CELLTYPE USING RANGE TYPE.
CALL METHOD OF EXCEL_OBJ 'RANGE' = CELL_OBJ
EXPORTING
#1 = RANGE.
SET PROPERTY OF CELL_OBJ 'NumberFormatLocal' = TYPE.
FREE OBJECT CELL_OBJ.
ENDFORM. "celltype
*&--------------------------------------------------------------------*
*& Form rowheight
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
* -->HEIGHT text
*---------------------------------------------------------------------*
FORM ROWHEIGHT USING ROW HEIGHT .
CALL METHOD OF EXCEL_OBJ 'ROWS' = ROW_OBJ
EXPORTING
#1 = ROW.
SET PROPERTY OF ROW_OBJ 'RowHeight' = HEIGHT .
FREE OBJECT ROW_OBJ.
ENDFORM . "rowheight
*&--------------------------------------------------------------------*
*& Form ColumnWidth
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
* -->WIDTH text
*---------------------------------------------------------------------*
FORM COLUMNWIDTH USING COLUMN WIDTH .
CALL METHOD OF EXCEL_OBJ 'COLUMNS' = COLUMN_OBJ
EXPORTING
#1 = COLUMN.
IF WIDTH = ''.
CALL METHOD OF COLUMN_OBJ 'AutoFit'.
ELSE.
SET PROPERTY OF COLUMN_OBJ 'columnwidth' = WIDTH .
ENDIF.
FREE OBJECT COLUMN_OBJ.
ENDFORM . "columnwidth
*&--------------------------------------------------------------------*
*& Form borderrange
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
* -->WE text
*---------------------------------------------------------------------*
FORM BORDERRANGE USING RANGE.
CALL METHOD OF EXCEL_OBJ 'RANGE' = CELL_OBJ
EXPORTING
#1 = RANGE.
DO 4 TIMES .
CALL METHOD OF CELL_OBJ 'BORDERS' = BORDERS_OBJ
EXPORTING
#1 = SY-INDEX.
SET PROPERTY OF BORDERS_OBJ 'LineStyle' = '1'.
SET PROPERTY OF BORDERS_OBJ 'WEIGHT' = '2'.
SET PROPERTY OF BORDERS_OBJ 'ColorIndex' = '1'.
FREE OBJECT BORDERS_OBJ.
ENDDO.
FREE OBJECT BORDERS_OBJ.
FREE OBJECT CELL_OBJ.
ENDFORM. "borderrange
*---------------------------------------------------------------------*
* FORM font *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> bold *
* --> size *
*---------------------------------------------------------------------*
FORM FONT USING RANGE BOLD SIZE NAME.
CALL METHOD OF EXCEL_OBJ 'RANGE' = CELL_OBJ
EXPORTING
#1 = RANGE.
CALL METHOD OF CELL_OBJ 'FONT' = FONT_OBJ.
SET PROPERTY OF FONT_OBJ 'BOLD' = BOLD.
SET PROPERTY OF FONT_OBJ 'SIZE' = SIZE.
SET PROPERTY OF FONT_OBJ 'NAME' = NAME.
FREE OBJECT FONT_OBJ.
FREE OBJECT CELL_OBJ.
ENDFORM. "font
*&---------------------------------------------------------------------*
*& Form CENTERCELL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->RANGE text
*----------------------------------------------------------------------*
FORM CENTERCELL USING RANGE.
CALL METHOD OF EXCEL_OBJ 'RANGE' = CELL_OBJ
EXPORTING
#1 = RANGE.
SET PROPERTY OF CELL_OBJ 'HorizontalAlignment' = 3.
FREE OBJECT CELL_OBJ.
ENDFORM. "CENTERCELL
*&---------------------------------------------------------------------*
*& Form FRM_COUNT_DATE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->PR_YEAR text
* -->PR_MONTH text
* -->PR_DATE text
*----------------------------------------------------------------------*
FORM FRM_COUNT_DATE USING PR_YEAR TYPE N
PR_MONTH TYPE N
PR_DATE TYPE D.
DATA I_DAY(2) TYPE N.
CASE PR_MONTH.
WHEN '01' OR '03' OR '05' OR '07' OR '08' OR '10' OR '12'.
I_DAY = '31'.
WHEN '04' OR '06' OR '09' OR '11'.
I_DAY = '30'.
WHEN '02'.
DATA: I_1 TYPE I, I_2 TYPE I, I_3 TYPE I.
I_1 = PR_YEAR MOD 4.
I_2 = PR_YEAR MOD 100.
I_3 = PR_YEAR MOD 400.
IF ( I_1 = 0 AND I_2 <> 0 ) OR I_3 = 0.
I_DAY = '29'.
ELSE.
I_DAY = '28'.
ENDIF.
ENDCASE.
CONCATENATE PR_YEAR PR_MONTH I_DAY INTO PR_DATE.
ENDFORM. "FRM_COUNT_DATE