該函數與前面的函數不同,為更新版,可以生成檔案的同時,自動按照ID區分生成多個sheet,并且每個sheet可以按照KEY_FIELD生成多個不同的表格,内表參數結構在後面的貼圖中。
一、函數:
FUNCTION ZF_EXP_EXCEL_FILE_MULTI.
*"----------------------------------------------------------------------
*"*"Local interface:
*" IMPORTING
*" REFERENCE(SPECIFY_FORMAT) TYPE CHAR1 OPTIONAL
*" REFERENCE(SAVE_AS) TYPE CHAR1 OPTIONAL
*" REFERENCE(QUICK_FORMAT) TYPE CHAR1 OPTIONAL
*" REFERENCE(ROW_PITCH) TYPE ANZ_BLK DEFAULT 2
*" REFERENCE(MODEL) TYPE CHAR1 OPTIONAL
*" REFERENCE(FILENAME) TYPE C OPTIONAL
*" TABLES
*" IT_TITLE STRUCTURE ZST_EXP_EXCEL_TITLE OPTIONAL
*" IT_HEAD STRUCTURE ZST_EXP_EXCEL_HEAD OPTIONAL
*" IT_COND STRUCTURE ZST_EXP_EXCEL_FILE_COND OPTIONAL
*" IT_TAB_FIELD STRUCTURE ZST_EXP_EXCEL_TAB_FD_TITLE OPTIONAL
*" IT_FOOTER STRUCTURE ZST_EXP_EXCEL_FILE_COND OPTIONAL
*" IT_DATA1
*" IT_DATA2 OPTIONAL
*" IT_DATA3 OPTIONAL
*"----------------------------------------------------------------------
DATA:F_SELECTLIST LIKE SPOPLI OCCURS 5 WITH HEADER LINE.
DATA:F_ANTWORT TYPE C.
DATA:F_SELECT_MOD.
DATA:F_CLEAR.
DATA:F_ROWCNT TYPE P DECIMALS 0.
CLEAR F_SELECT_MOD.
IF IT_TAB_FIELD[] IS NOT INITIAL.
F_SELECT_MOD = 'X'.
ENDIF.
DATA: F_TABNAME(30),
F_ID LIKE IT_HEAD-ID.
DATA:IT_FUPARAREF LIKE FUPARAREF OCCURS 0 WITH HEADER LINE.
FIELD-SYMBOLS <FRM_TABLE> TYPE STANDARD TABLE.
"清空内表
PERFORM FRM_REFRESH.
"取函數的IT_DATA參數
SELECT PARAMETER PPOSITION FROM FUPARAREF
INTO CORRESPONDING FIELDS OF TABLE IT_FUPARAREF
WHERE FUNCNAME = 'ZF_EXP_EXCEL_FILE_MULTI' AND
R3STATE = 'A' AND
PARAMETER LIKE 'IT_DATA%' AND
PARAMTYPE = 'T'.
SORT IT_FUPARAREF BY PPOSITION.
IF IT_HEAD[] IS INITIAL.
CLEAR IT_HEAD.
READ TABLE IT_TITLE INDEX 1.
MOVE-CORRESPONDING IT_TITLE TO IT_HEAD.
APPEND IT_HEAD.
ENDIF.
IF MODEL IS NOT INITIAL.
IF IT_DATA1[] IS INITIAL.
F_CLEAR = 'X'.
CLEAR IT_DATA1. APPEND IT_DATA1.
ENDIF.
ENDIF.
SORT IT_HEAD BY ID.
LOOP AT IT_FUPARAREF.
F_ID = SY-TABIX.
READ TABLE IT_HEAD WITH KEY ID = F_ID.
IF SY-SUBRC NE 0.
READ TABLE IT_HEAD WITH KEY ID = ''.
IF SY-SUBRC NE 0 .
READ TABLE IT_HEAD WITH KEY ID = '0000'.
IF SY-SUBRC NE 0.
READ TABLE IT_HEAD INDEX 1.
IF SY-SUBRC NE 0.
CONTINUE.
ENDIF.
ENDIF.
ENDIF.
ENDIF.
CONCATENATE IT_FUPARAREF-PARAMETER '[]' INTO F_TABNAME.
CONDENSE F_TABNAME NO-GAPS.
ASSIGN (F_TABNAME) TO <FRM_TABLE>.
IF MODEL IS NOT INITIAL.
IF F_TABNAME NE 'IT_DATA1[]'.
CONTINUE.
ENDIF.
ENDIF.
PERFORM FRM_SET_DATA TABLES IT_HEAD <FRM_TABLE> USING MODEL.
ENDLOOP.
"檢查資料記錄數
PERFORM FRM_GET_EXCEL_DATA_ROWCNTS CHANGING F_ROWCNT.
IF F_ROWCNT = 0.
RETURN.
ENDIF.
PERFORM FRM_SET_SPECIFY_SAVE_AS USING SAVE_AS. "是否預覽标志
PERFORM FRM_SET_ROW_PITCH USING ROW_PITCH. "報表間的空白行數
PERFORM FRM_SET_EXCEL_FILE_TITLE TABLES IT_TITLE. "
PERFORM FRM_SET_SAVE_FILENAME USING FILENAME. "儲存的檔案
IF SPECIFY_FORMAT IS NOT INITIAL. "指定格式導出
PERFORM FRM_DC_EXCEL TABLES IT_COND IT_TAB_FIELD IT_FOOTER.
IF F_CLEAR IS NOT INITIAL.
CLEAR IT_DATA1[].
ENDIF.
RETURN.
ENDIF.
IF QUICK_FORMAT IS NOT INITIAL OR F_SELECT_MOD IS INITIAL. "快速格式導出
PERFORM FRM_KSDC_EXCEL TABLES IT_COND IT_TAB_FIELD IT_FOOTER.
IF F_CLEAR IS NOT INITIAL.
CLEAR IT_DATA1[].
ENDIF.
RETURN.
ENDIF.
CLEAR:F_SELECTLIST[],F_SELECTLIST.
F_SELECTLIST-VAROPTION = '按指定格式導出'.
F_SELECTLIST-SELFLAG = 'X'. APPEND F_SELECTLIST.
CLEAR F_SELECTLIST.
F_SELECTLIST-VAROPTION = '快速導出'. APPEND F_SELECTLIST.
CALL FUNCTION 'POPUP_TO_DECIDE_LIST'
EXPORTING
MARK_MAX = 1
START_COL = 15
START_ROW = 3
TEXTLINE1 = '檔案的格式:'
TITEL = '選擇導出'
IMPORTING
ANSWER = F_ANTWORT
TABLES
T_SPOPLI = F_SELECTLIST
EXCEPTIONS
NOT_ENOUGH_ANSWERS = 1
TOO_MUCH_ANSWERS = 2
TOO_MUCH_MARKS = 3
OTHERS = 4.
CASE F_ANTWORT.
WHEN '1'.
PERFORM FRM_DC_EXCEL TABLES IT_COND IT_TAB_FIELD IT_FOOTER.
WHEN '2'.
PERFORM FRM_KSDC_EXCEL TABLES IT_COND IT_TAB_FIELD IT_FOOTER.
ENDCASE.
IF F_CLEAR IS NOT INITIAL.
CLEAR IT_DATA1[].
ENDIF.
ENDFUNCTION.
二、函數TOP:
TYPE-POOLS: OLE2."EXCEL輸出中所需要的一些預定義
DATA: L_EXCEL TYPE OLE2_OBJECT, " Excel object
L_WINDOW TYPE OLE2_OBJECT, " list of workbooks
L_BOOKS TYPE OLE2_OBJECT, " list of workbooks
L_BOOK TYPE OLE2_OBJECT, " workbook
L_CELL TYPE OLE2_OBJECT, " cell object
L_FONT TYPE OLE2_OBJECT, " font object
L_COLUMN TYPE OLE2_OBJECT, " COLUMN OBJECT
L_ROW TYPE OLE2_OBJECT, " COLUMN OBJECT
L_RANGE TYPE OLE2_OBJECT, " RANGE OBJECT
L_BORDER TYPE OLE2_OBJECT, " BORDER OBJECT
L_SHEET TYPE OLE2_OBJECT. " cell object
TYPE-POOLS ABAP.
DATA:BEGIN OF IT_EXCLE_TABLE_FIELD OCCURS 0,
LENGTH TYPE I,
DECIMALS TYPE I,
TYPE_KIND TYPE ABAP_TYPEKIND,
NAME TYPE ABAP_COMPNAME,
END OF IT_EXCLE_TABLE_FIELD.
* EXCEL 輸出準備
DATA: EXCEL_TITLE1(6) VALUE 'TITLE1',
EXCEL_TITLE2(6) VALUE 'TITLE2',
EXCEL_COND(4) VALUE 'COND',
EXCEL_CONDBD(4) VALUE 'CDBD',
EXCEL_TBTIL(6) VALUE 'TBTIL',
EXCEL_TDATA(6) VALUE 'TDATA',
EXCEL_FOOTBD(4) VALUE 'FTBD',
EXCEL_FOOT(4) VALUE 'FOOT'.
DATA: BEGIN OF IT_EXCEL_TITLE OCCURS 0, "檔案擡頭
ID LIKE ZST_EXP_EXCEL_HEAD-ID,
TYP,"類型 M-主标題,否則為子标題
MS(200),
END OF IT_EXCEL_TITLE.
DATA: EXCEL_TITLE1_SIZE TYPE I VALUE 18,
EXCEL_TITLE1_ROWHIGH TYPE I VALUE 30,
EXCEL_TITLE2_SIZE TYPE I VALUE 15,
EXCEL_TITLE2_ROWHIGH TYPE I VALUE 20..
DATA: DYN_DATA1 TYPE REF TO DATA,
DYN_DATA2 TYPE REF TO DATA,
DYN_DATA3 TYPE REF TO DATA.
DATA: SAVE_AS, "指定格式的另存檔案标志
SAVE_FILENAME TYPE STRING. "指定格式導出時,不預覽,儲存将要導出的檔案名
DATA: FONT_NAME(20) VALUE '宋體',
FONT_SIZE TYPE I VALUE 10.
DATA: FORM_ROW_PITCH TYPE I VALUE 2. "FORM的行距
DATA: EXCEL_TB_CELL_TITLE_FONT_BOLD TYPE I VALUE 1.
DATA: EXCEL_ITAB_DATA_MAX_CNT TYPE P DECIMALS 0,
EXCEL_DATA_ROWCNTS TYPE P DECIMALS 0, "所有的資料記錄數總數
EXCEL_OTHER_ROWCNTS TYPE I. "标題,條件,表頭描述,表腳的記錄數
DATA: EXCEL_CUR_SHEETID TYPE I VALUE 1,
EXCEL_SHEET_MAX_ROW TYPE I VALUE 64000. "允許每個SHEET的最多記錄數
DATA: BEGIN OF IT_KEY_FD OCCURS 0,
ID(20),
FIELD(20),
END OF IT_KEY_FD,
ITAB_DATA_FJ_FDNAME(20) VALUE 'ITAB_DATA_BIANHAO'. "資料内表附加字段名
DATA:IT_EXCEL_FILE_TITLE LIKE ZST_EXP_EXCEL_TITLE OCCURS 0 WITH HEADER LINE,
IT_EXCEL_TAB_DATA_TITLE LIKE ZST_EXP_EXCEL_TAB_FD_TITLE OCCURS 0 WITH HEADER LINE,
IT_EXCEL_QUERY_COND LIKE ZST_EXP_EXCEL_FILE_COND OCCURS 0 WITH HEADER LINE,
BEGIN OF IT_EXCEL_ROWS OCCURS 0,
ID TYPE I,
KEY_FDVALUE LIKE ZST_EXP_EXCEL_TAB_FD_TITLE-KEY_FDVALUE,
ROWTYP LIKE EXCEL_TITLE1, "取值:TITLE,COND,TBTIL,TDATA,FOOT
BG_ROWID TYPE I,
ED_ROWID TYPE I,
END OF IT_EXCEL_ROWS,
IT_EXCEL_FOOTER LIKE ZST_EXP_EXCEL_FILE_COND OCCURS 0 WITH HEADER LINE,
BEGIN OF IT_TB_CELL_HALIGNMENT OCCURS 0,
NAME(20),
ALIGHT TYPE I,
END OF IT_TB_CELL_HALIGNMENT,
BEGIN OF IT_MERGECELLS OCCURS 0,
BG_ROWID TYPE I,
ED_ROWID TYPE I,
BG_COLNAME(5),
ED_COLNAME(5),
END OF IT_MERGECELLS.
DATA:BEGIN OF IT_EXP_SHEETNAME OCCURS 0,
ID(4),
MS(200),
END OF IT_EXP_SHEETNAME.
DATA:BEGIN OF IT_EXCEL_DATA OCCURS 0.
INCLUDE STRUCTURE ZST_EXP_EXCEL_HEAD.
DATA: DYN_ITAB TYPE REF TO DATA,
DYN_ITAB_HJ TYPE REF TO DATA,
END OF IT_EXCEL_DATA.
*&---------------------------------------------------------------------*
*& Form FRM_GET_EXCEL_DATA_ROWCNTS
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_ROWCNT text
*----------------------------------------------------------------------*
FORM FRM_GET_EXCEL_DATA_ROWCNTS CHANGING P_ROWCNT.
P_ROWCNT = EXCEL_DATA_ROWCNTS.
ENDFORM. "FRM_GET_EXCEL_DATA_ROWCNTS
*&---------------------------------------------------------------------*
*& Form frm_set_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_HEAD text
* -->P_ITABNAME text
*----------------------------------------------------------------------*
FORM FRM_SET_DATA TABLES P_IT_HEAD STRUCTURE ZST_EXP_EXCEL_HEAD P_IT_DATA USING P_MODEL.
DATA: FRM_IT_EXCEL_DATA_ROWCNT TYPE I.
CLEAR:IT_EXCEL_DATA.
MOVE-CORRESPONDING P_IT_HEAD TO IT_EXCEL_DATA.
PERFORM FRM_SET_IT_DATA_KEY_FIELD USING P_IT_HEAD-ID P_IT_HEAD-KEY_FIELD. "内表參數關鍵字段
IF IT_EXCEL_DATA-ID IS INITIAL.
IT_EXCEL_DATA-ID = LINES( IT_EXCEL_DATA ) + 1.
ENDIF.
IF P_MODEL IS INITIAL.
IF P_IT_DATA[] IS INITIAL.
RETURN.
ENDIF.
ENDIF.
PERFORM FRM_MOVE_DATA TABLES P_IT_DATA
USING P_IT_HEAD-ID P_IT_HEAD-AUTO_TOTAL P_MODEL
CHANGING IT_EXCEL_DATA-DYN_ITAB IT_EXCEL_DATA-DYN_ITAB_HJ .
APPEND IT_EXCEL_DATA.
PERFORM FRM_SET_TITLE USING IT_EXCEL_DATA.
ENDFORM. "frm_set_data
*&---------------------------------------------------------------------*
*& Form FRM_SET_TITLE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_WA_HEAD text
*----------------------------------------------------------------------*
FORM FRM_SET_TITLE USING P_WA_HEAD STRUCTURE ZST_EXP_EXCEL_HEAD.
FIELD-SYMBOLS:<FRM_WA>,<FRM_FD>,<FRM_VALUE>.
IF P_WA_HEAD IS INITIAL.
RETURN.
ENDIF.
DATA:FRM_ITAB_COMPONET TYPE REF TO CL_ABAP_STRUCTDESCR.
FRM_ITAB_COMPONET ?= CL_ABAP_TYPEDESCR=>DESCRIBE_BY_DATA( P_WA_HEAD ).
CLEAR: IT_EXCEL_TITLE.
IT_EXCEL_TITLE-ID = P_WA_HEAD-ID.
LOOP AT FRM_ITAB_COMPONET->COMPONENTS ASSIGNING <FRM_WA> WHERE NAME(5) = 'TITLE'.
ASSIGN COMPONENT 'NAME' OF STRUCTURE <FRM_WA> TO <FRM_FD>.
ASSIGN COMPONENT <FRM_FD> OF STRUCTURE P_WA_HEAD TO <FRM_VALUE>.
IF <FRM_FD>+5 = '1'.
IT_EXCEL_TITLE-TYP = 'M'.
ELSE.
CLEAR IT_EXCEL_TITLE-TYP.
ENDIF.
IF <FRM_VALUE> IS NOT INITIAL.
IT_EXCEL_TITLE-MS = <FRM_VALUE>.
APPEND IT_EXCEL_TITLE.
ENDIF.
ENDLOOP.
ENDFORM. " FRM_SET_TITLE
*&---------------------------------------------------------------------*
*& Form FRM_SET_SPECIFY_SAVE_AS
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SAVE_AS text
*----------------------------------------------------------------------*
FORM FRM_SET_SPECIFY_SAVE_AS USING P_SAVE_AS.
SAVE_AS = P_SAVE_AS.
ENDFORM. " FRM_SET_SPECIFY_SAVE_AS
*&---------------------------------------------------------------------*
*& Form frm_set_ROW_PITCH
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_ROW_PITCH text
*----------------------------------------------------------------------*
FORM FRM_SET_ROW_PITCH USING P_ROW_PITCH.
FORM_ROW_PITCH = P_ROW_PITCH.
ENDFORM. "frm_set_ROW_PITCH
*&---------------------------------------------------------------------*
*& Form frm_set_file_title
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_TITLE text
*----------------------------------------------------------------------*
FORM FRM_SET_EXCEL_FILE_TITLE TABLES P_IT_TITLE.
IT_EXCEL_FILE_TITLE[] = P_IT_TITLE[].
ENDFORM. "frm_set_file_title
*&---------------------------------------------------------------------*
*& Form FRM_SET_SAVE_FILENAME
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_FILENAME text
*----------------------------------------------------------------------*
FORM FRM_SET_SAVE_FILENAME USING P_FILENAME.
SAVE_FILENAME = P_FILENAME.
ENDFORM. "FRM_SET_SAVE_FILENAME
*&---------------------------------------------------------------------*
*& Form FRM_SET_IT_DATA_KEY_FIELD
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_ID text
* -->P_KEY_FIELD text
*----------------------------------------------------------------------*
FORM FRM_SET_IT_DATA_KEY_FIELD USING P_ID P_KEY_FIELD.
IF P_KEY_FIELD IS INITIAL.
RETURN.
ENDIF.
IT_KEY_FD-ID = P_ID.
IT_KEY_FD-FIELD = P_KEY_FIELD.
COLLECT IT_KEY_FD.
ENDFORM. "frm_set_IT_DATA_KEY_FIELD
*&---------------------------------------------------------------------*
*& Form FRM_DC_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_COND text
* -->P_IT_ITEM text
* -->P_IT_FOOTER text
*----------------------------------------------------------------------*
FORM FRM_DC_EXCEL TABLES P_IT_COND STRUCTURE ZST_EXP_EXCEL_FILE_COND
P_IT_ITEM STRUCTURE ZST_EXP_EXCEL_TAB_FD_TITLE
P_IT_FOOTER STRUCTURE ZST_EXP_EXCEL_FILE_COND.
DATA:FRM_ERRCODE.
"資料處理
IF P_IT_ITEM[] IS INITIAL.
MESSAGE S001(00) WITH '尚未為導出EXCEL函數指定參數:IT_TAB_FIELD'.
RETURN.
ENDIF.
PERFORM FRM_MOD_DATA_SET TABLES P_IT_COND P_IT_ITEM P_IT_FOOTER.
"打開EXCEL
PERFORM FRM_EXCLE_INIT USING FRM_ERRCODE.
IF FRM_ERRCODE IS NOT INITIAL.
RETURN.
ENDIF.
"導出資料
DATA:FRM_TITLEID TYPE I.
CLEAR EXCEL_CUR_SHEETID.
LOOP AT IT_EXCEL_DATA.
FRM_TITLEID = IT_EXCEL_DATA-ID.
ADD 1 TO EXCEL_CUR_SHEETID.
PERFORM FRM_EXP_DC_DATA USING EXCEL_CUR_SHEETID FRM_TITLEID IT_EXCEL_DATA-DYN_ITAB IT_EXCEL_DATA-DYN_ITAB_HJ.
ENDLOOP.
PERFORM FRM_DEL_NULL_SHEETS. "删除空白的SHEET
PERFORM FRM_EXCEL_SAVE. "儲存至檔案
PERFORM FRM_EXCEL_FREE. "釋放資源
ENDFORM. " FRM_DC_EXCEL
*&---------------------------------------------------------------------*
*& Form frm_mod_data_set
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_COND text
* -->P_IT_ITEM text
* -->P_IT_FOOTER text
*----------------------------------------------------------------------*
FORM FRM_MOD_DATA_SET TABLES P_IT_COND STRUCTURE ZST_EXP_EXCEL_FILE_COND
P_IT_ITEM STRUCTURE ZST_EXP_EXCEL_TAB_FD_TITLE
P_IT_FOOTER STRUCTURE ZST_EXP_EXCEL_FILE_COND.
IT_EXCEL_QUERY_COND[] = P_IT_COND[].
IT_EXCEL_TAB_DATA_TITLE[] = P_IT_ITEM[].
IT_EXCEL_FOOTER[] = P_IT_FOOTER[].
CLEAR EXCEL_OTHER_ROWCNTS.
"表頭
EXCEL_OTHER_ROWCNTS = LINES( IT_EXCEL_TITLE ).
"查詢條件
PERFORM FRM_QUERY_COND_OR_FOOTER_SET TABLES IT_EXCEL_QUERY_COND.
"清單擡頭資料
PERFORM FRM_TABLE_FIELD_TEXT_SET TABLES IT_EXCEL_TAB_DATA_TITLE.
"表腳下資料
PERFORM FRM_QUERY_COND_OR_FOOTER_SET TABLES IT_EXCEL_FOOTER.
ENDFORM. "frm_mod_data_set
*&---------------------------------------------------------------------*
*& Form FRM_QUERY_COND_OR_FOOTER_SET
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_DATA text
*----------------------------------------------------------------------*
FORM FRM_QUERY_COND_OR_FOOTER_SET TABLES P_IT_DATA STRUCTURE ZST_EXP_EXCEL_FILE_COND.
DATA:FRM_MAX_ROWID TYPE I.
CLEAR FRM_MAX_ROWID.
LOOP AT P_IT_DATA.
IF P_IT_DATA-BG_COLNAME IS INITIAL OR P_IT_DATA-ED_COLNAME IS INITIAL.
IF P_IT_DATA-BG_COLNAME IS INITIAL AND P_IT_DATA-ED_COLNAME IS INITIAL.
DELETE P_IT_DATA.
CONTINUE.
ENDIF.
IF P_IT_DATA-BG_COLNAME IS INITIAL.
P_IT_DATA-BG_COLNAME = P_IT_DATA-ED_COLNAME.
ENDIF.
IF P_IT_DATA-ED_COLNAME IS INITIAL.
P_IT_DATA-ED_COLNAME = P_IT_DATA-BG_COLNAME.
ENDIF.
MODIFY P_IT_DATA.
ENDIF.
IF FRM_MAX_ROWID < P_IT_DATA-ROWID.
FRM_MAX_ROWID = P_IT_DATA-ROWID.
ENDIF.
ENDLOOP.
EXCEL_OTHER_ROWCNTS = EXCEL_OTHER_ROWCNTS + FRM_MAX_ROWID.
ENDFORM. "FRM_QUERY_COND_OR_FOOTER_SET
*&---------------------------------------------------------------------*
*& Form frm_table_field_text_set
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_FIELD_TITLE text
*----------------------------------------------------------------------*
FORM FRM_TABLE_FIELD_TEXT_SET TABLES P_IT_FIELD_TITLE STRUCTURE IT_EXCEL_TAB_DATA_TITLE.
DATA:FRM_IT_EXCEL_TAB_DATA_TITLE LIKE IT_EXCEL_TAB_DATA_TITLE OCCURS 0 WITH HEADER LINE,
FRM_WA_TITLE LIKE LINE OF FRM_IT_EXCEL_TAB_DATA_TITLE.
DATA:FRM_BG_ROW TYPE I,FRM_ED_ROW TYPE I,FRM_CNT TYPE STRING.
DATA:FRM_BG_COLNAME(10),FRM_ED_COLNAME(10).
DATA:FRM_ROWID TYPE I.
DATA:FRM_MAX_ROWID TYPE I.
DATA:FRM_ID_TMP(50).
DATA:FRM_KEY_FDVALUE LIKE IT_EXCEL_TAB_DATA_TITLE-KEY_FDVALUE.
CLEAR FRM_ROWID.
CLEAR FRM_MAX_ROWID.
LOOP AT P_IT_FIELD_TITLE.
MOVE-CORRESPONDING P_IT_FIELD_TITLE TO FRM_WA_TITLE.
AT NEW ID.
CLEAR FRM_ROWID.
ENDAT.
IF FRM_KEY_FDVALUE <> FRM_WA_TITLE-KEY_FDVALUE.
CLEAR FRM_ROWID.
FRM_KEY_FDVALUE = FRM_WA_TITLE-KEY_FDVALUE.
ENDIF.
"行号
IF P_IT_FIELD_TITLE-BG_ROW IS INITIAL AND P_IT_FIELD_TITLE-ED_ROW IS INITIAL.
FRM_BG_ROW = 1.
FRM_ED_ROW = 1.
ELSEIF P_IT_FIELD_TITLE-BG_ROW IS NOT INITIAL AND P_IT_FIELD_TITLE-ED_ROW IS INITIAL.
FRM_BG_ROW = P_IT_FIELD_TITLE-BG_ROW.
FRM_ED_ROW = P_IT_FIELD_TITLE-BG_ROW.
ELSEIF P_IT_FIELD_TITLE-BG_ROW IS INITIAL AND P_IT_FIELD_TITLE-ED_ROW IS NOT INITIAL.
FRM_BG_ROW = P_IT_FIELD_TITLE-ED_ROW.
FRM_ED_ROW = P_IT_FIELD_TITLE-ED_ROW.
ELSE.
FRM_BG_ROW = P_IT_FIELD_TITLE-BG_ROW.
FRM_ED_ROW = P_IT_FIELD_TITLE-ED_ROW.
ENDIF.
P_IT_FIELD_TITLE-BG_ROW = FRM_BG_ROW.
P_IT_FIELD_TITLE-ED_ROW = FRM_ED_ROW.
"列名
IF FRM_BG_COLNAME <> FRM_ED_COLNAME AND FRM_BG_ROW = 1 AND FRM_ED_ROW = 1. "同在第一行且起始列名與截止列名不一緻,要合并多列
CLEAR P_IT_FIELD_TITLE-NAME. "對應的列字段名稱
ENDIF.
IF P_IT_FIELD_TITLE-BG_COLNAME IS INITIAL AND P_IT_FIELD_TITLE-BG_COLNAME IS INITIAL.
ADD 1 TO FRM_ROWID.
FRM_CNT = FRM_ROWID.
CONDENSE FRM_CNT NO-GAPS.
PERFORM FRM_GET_EXCEL_END_WORD CHANGING FRM_CNT.
FRM_BG_COLNAME = FRM_CNT.
FRM_ED_COLNAME = FRM_CNT.
ELSEIF P_IT_FIELD_TITLE-BG_COLNAME IS NOT INITIAL AND P_IT_FIELD_TITLE-ED_COLNAME IS INITIAL.
FRM_BG_COLNAME = P_IT_FIELD_TITLE-BG_COLNAME.
FRM_ED_COLNAME = P_IT_FIELD_TITLE-BG_COLNAME.
ELSEIF P_IT_FIELD_TITLE-BG_COLNAME IS INITIAL AND P_IT_FIELD_TITLE-ED_COLNAME IS NOT INITIAL.
FRM_BG_COLNAME = P_IT_FIELD_TITLE-ED_COLNAME.
FRM_ED_COLNAME = P_IT_FIELD_TITLE-ED_COLNAME.
ELSE.
FRM_BG_COLNAME = P_IT_FIELD_TITLE-BG_COLNAME.
FRM_ED_COLNAME = P_IT_FIELD_TITLE-ED_COLNAME.
ENDIF.
P_IT_FIELD_TITLE-BG_COLNAME = FRM_BG_COLNAME.
P_IT_FIELD_TITLE-ED_COLNAME = FRM_ED_COLNAME.
MODIFY P_IT_FIELD_TITLE.
IF FRM_MAX_ROWID < FRM_ED_ROW.
FRM_MAX_ROWID = FRM_ED_ROW.
ENDIF.
ENDLOOP.
EXCEL_OTHER_ROWCNTS = EXCEL_OTHER_ROWCNTS + FRM_MAX_ROWID.
ENDFORM. "frm_table_field_text_set
*&---------------------------------------------------------------------*
*& Form FRM_EXCLE_INIT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SHEETNAME text
*----------------------------------------------------------------------*
FORM FRM_EXCLE_INIT CHANGING P_ERRCODE.
DATA:FRM_VISIBLE VALUE '1',
FRM_SHEED_CNT TYPE P DECIMALS 4.
CREATE OBJECT L_EXCEL 'EXCEL.APPLICATION'.
IF SY-SUBRC NE 0.
CREATE OBJECT L_EXCEL 'ET.APPLICATION'.
ENDIF.
PERFORM FRM_ERR_HDL.
PERFORM FRM_MSG USING ''.
CALL METHOD OF L_EXCEL 'Workbooks' = L_BOOKS NO FLUSH.
" 建立一個sheet
CALL METHOD OF L_BOOKS 'Add' = L_BOOK NO FLUSH.
PERFORM FRM_ERR_HDL.
CALL METHOD OF L_EXCEL 'ActiveWindow' = L_WINDOW NO FLUSH.
PERFORM FRM_ERR_HDL.
GET PROPERTY OF L_BOOK 'ActiveSheet' = L_SHEET NO FLUSH.
PERFORM FRM_ERR_HDL.
"根據資料設定
FRM_SHEED_CNT = EXCEL_DATA_ROWCNTS * ( EXCEL_ITAB_DATA_MAX_CNT + EXCEL_OTHER_ROWCNTS ).
FRM_SHEED_CNT = FRM_SHEED_CNT + ( EXCEL_DATA_ROWCNTS - 1 ) * FORM_ROW_PITCH.
FRM_SHEED_CNT = CEIL( FRM_SHEED_CNT / EXCEL_SHEET_MAX_ROW ).
FRM_SHEED_CNT = FRM_SHEED_CNT * LINES( IT_EXCEL_DATA ).
PERFORM FRM_NEW_SHEET USING FRM_SHEED_CNT.
"檔案是否隐藏,是的話,要求選擇檔案,将資料寫入檔案中
CLEAR P_ERRCODE.
IF SAVE_AS IS NOT INITIAL.
PERFORM FRM_GET_SAVE_FILENAME CHANGING SAVE_FILENAME. "取檔案名
IF SAVE_FILENAME IS INITIAL.
P_ERRCODE = 'X'.
RETURN.
ENDIF.
FRM_VISIBLE = '0'.
ENDIF.
SET PROPERTY OF L_EXCEL 'Visible' = FRM_VISIBLE .
PERFORM FRM_ERR_HDL.
PERFORM FRM_MSG USING '正在建立工作表……'.
ENDFORM. "FRM_EXCLE_INIT
*&---------------------------------------------------------------------*
*& Form FRM_NEW_SHEET
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SHEET_CNT text
*----------------------------------------------------------------------*
FORM FRM_NEW_SHEET USING P_SHEET_CNT.
DATA:FRM_SHEET_CNT1 TYPE I, "建立前SHEET的總數
FRM_SHEET_CNT2 TYPE I, "建立後SHEET的總數
FRM_SHEET_NEW_CNT TYPE I,
FRM_SHEET_CURID TYPE I,
L_NEWSHEET TYPE OLE2_OBJECT.
DATA:FRM_SHEETNAME TYPE STRING.
CALL METHOD OF L_BOOK 'sheets' = L_SHEET. "取SHEET對象
GET PROPERTY OF L_SHEET 'COUNT' = FRM_SHEET_CNT1. "取建立前SHEET的總數
"先建立多個SHEET
FRM_SHEET_NEW_CNT = P_SHEET_CNT.
FRM_SHEET_CURID = FRM_SHEET_NEW_CNT.
DO FRM_SHEET_NEW_CNT TIMES.
CALL METHOD OF L_SHEET 'Add' = L_NEWSHEET .
FRM_SHEETNAME = FRM_SHEET_CURID.
CONDENSE FRM_SHEETNAME NO-GAPS.
CONCATENATE 'SHEET' FRM_SHEETNAME INTO FRM_SHEETNAME .
CONDENSE FRM_SHEETNAME NO-GAPS.
SET PROPERTY OF L_NEWSHEET 'Name' = FRM_SHEETNAME .
FRM_SHEET_CURID = FRM_SHEET_CURID - 1.
ENDDO.
GET PROPERTY OF L_SHEET 'COUNT' = FRM_SHEET_CNT2. "取建立後SHEET的總數
FRM_SHEET_NEW_CNT = FRM_SHEET_CNT2 - FRM_SHEET_CNT1. "建立的SHEET總數 = 建立後SHEET的總數 - 建立前SHEET的總數
"删除建立前的SHEET
DO FRM_SHEET_CNT1 TIMES.
FRM_SHEET_CURID = SY-INDEX + FRM_SHEET_NEW_CNT.
CALL METHOD OF L_EXCEL 'sheets' = L_SHEET
EXPORTING
#1 = FRM_SHEET_CURID.
CALL METHOD OF L_SHEET 'Delete'.
ENDDO.
ENDFORM. "frm_new_sheet
*&---------------------------------------------------------------------*
*& Form frm_refresh
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM FRM_REFRESH.
CLEAR:IT_EXCEL_TAB_DATA_TITLE[],IT_EXCEL_TAB_DATA_TITLE.
CLEAR:IT_EXCEL_TITLE,IT_EXCEL_TITLE[].
CLEAR:IT_EXCLE_TABLE_FIELD,IT_EXCLE_TABLE_FIELD[].
CLEAR:IT_EXCEL_ROWS[],IT_EXCEL_ROWS.
CLEAR:IT_EXCEL_QUERY_COND[],IT_EXCEL_QUERY_COND.
CLEAR:IT_EXCEL_FOOTER[],IT_EXCEL_FOOTER.
CLEAR:IT_TB_CELL_HALIGNMENT[],IT_TB_CELL_HALIGNMENT.
CLEAR:IT_EXCEL_DATA,IT_EXCEL_DATA[].
CLEAR:EXCEL_CUR_SHEETID.
CLEAR:IT_EXP_SHEETNAME,IT_EXP_SHEETNAME[].
CLEAR:IT_MERGECELLS,IT_MERGECELLS[].
ENDFORM. "frm_refresh
*&---------------------------------------------------------------------*
*& Form FRM_ERR_HDL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM FRM_ERR_HDL.
DATA:FRM_ERRTXT(50),FRM_SUB(4).
IF SY-SUBRC <> 0.
FRM_ERRTXT = 'Fehler bei OLE-Automation: '.
CASE SY-SUBRC.
WHEN 1.
CONCATENATE FRM_ERRTXT '1: , SY-MSGLI' INTO FRM_ERRTXT.
WHEN 2.
CONCATENATE FRM_ERRTXT '2 : A method call resulted in an error' INTO FRM_ERRTXT.
WHEN 3.
CONCATENATE FRM_ERRTXT '3 : Setting a property resulted in an error.' INTO FRM_ERRTXT.
WHEN 4.
CONCATENATE FRM_ERRTXT '4 : Reading a property resulted in an error.' INTO FRM_ERRTXT.
WHEN OTHERS.
FRM_SUB = SY-SUBRC.
CONCATENATE FRM_ERRTXT '錯誤碼:' FRM_SUB INTO FRM_ERRTXT.
ENDCASE.
CONDENSE FRM_ERRTXT NO-GAPS.
MESSAGE S001(00) WITH FRM_ERRTXT DISPLAY LIKE 'E'.
STOP.
ENDIF.
ENDFORM. "FRM_ERR_HDL
*&---------------------------------------------------------------------*
*& Form FRM_MSG
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_MSG text
*----------------------------------------------------------------------*
FORM FRM_MSG USING P_MSG.
IF P_MSG IS INITIAL.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
TEXT = '正在為輸出到EXCEL作準備……'
EXCEPTIONS
OTHERS = 1.
ELSE.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
TEXT = P_MSG
EXCEPTIONS
OTHERS = 1.
ENDIF.
ENDFORM . "frm_msg
*&---------------------------------------------------------------------*
*& Form FRM_MOVE_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_DATA text
* -->P_ID text
* -->P_AUTO_TOTAL text
* -->P_MODEL text
* -->P_DYN_DATA text
*----------------------------------------------------------------------*
FORM FRM_MOVE_DATA TABLES P_IT_DATA
USING P_ID P_AUTO_TOTAL P_MODEL
CHANGING P_DYN_DATA TYPE REF TO DATA
P_DYN_DATA_HJ TYPE REF TO DATA.
FIELD-SYMBOLS: <FRM_DYN_TABLE> TYPE STANDARD TABLE,
<FRM_DYN_TABLE_HJ> TYPE STANDARD TABLE,
<FRM_DYN_WA>,<FRM_DYN_WA_HJ>,<FRM_DATA_WA>,<FRM_FIELD_FJ>,
<FRM_FIELD>,<FRM_FIELD_HJ>.
DATA:FRM_DYN_WA TYPE REF TO DATA,
FRM_DYN_WA_HJ TYPE REF TO DATA,
FRM_ITAB_DATA_CNT TYPE I,
FRM_ITAB_DATA_MAX_ROWS TYPE P DECIMALS 0,
FRM_ITAB_DATA_ROWS TYPE P DECIMALS 0,
FRM_IT_EXCLE_TABLE_FIELD LIKE IT_EXCLE_TABLE_FIELD OCCURS 0 WITH HEADER LINE.
IF P_MODEL IS INITIAL.
IF P_IT_DATA[] IS INITIAL.
RETURN.
ENDIF.
ENDIF.
PERFORM FRM_CREATE_ITAB TABLES P_IT_DATA IT_EXCLE_TABLE_FIELD CHANGING P_DYN_DATA FRM_DYN_WA.
ASSIGN P_DYN_DATA->* TO <FRM_DYN_TABLE>.
ASSIGN FRM_DYN_WA->* TO <FRM_DYN_WA>.
PERFORM FRM_CREATE_ITAB TABLES P_IT_DATA FRM_IT_EXCLE_TABLE_FIELD CHANGING P_DYN_DATA_HJ FRM_DYN_WA_HJ.
ASSIGN P_DYN_DATA_HJ->* TO <FRM_DYN_TABLE_HJ>.
ASSIGN FRM_DYN_WA_HJ->* TO <FRM_DYN_WA_HJ>.
READ TABLE IT_KEY_FD WITH KEY ID = P_ID.
READ TABLE IT_EXCLE_TABLE_FIELD WITH KEY NAME = IT_KEY_FD-FIELD.
IF SY-SUBRC NE 0.
CLEAR IT_KEY_FD.
ENDIF.
CLEAR FRM_ITAB_DATA_CNT.
CLEAR <FRM_DYN_TABLE>[].
CLEAR:FRM_ITAB_DATA_MAX_ROWS,FRM_ITAB_DATA_ROWS.
LOOP AT P_IT_DATA ASSIGNING <FRM_DATA_WA>.
IF IT_KEY_FD-FIELD IS NOT INITIAL.
AT NEW (IT_KEY_FD-FIELD).
ADD 1 TO FRM_ITAB_DATA_CNT.
ENDAT.
ENDIF.
ADD 1 TO FRM_ITAB_DATA_ROWS.
IF IT_KEY_FD-FIELD IS NOT INITIAL.
AT END OF (IT_KEY_FD-FIELD)..
IF FRM_ITAB_DATA_MAX_ROWS < FRM_ITAB_DATA_ROWS .
FRM_ITAB_DATA_MAX_ROWS = FRM_ITAB_DATA_ROWS .
ENDIF.
CLEAR FRM_ITAB_DATA_ROWS .
ENDAT.
ENDIF.
CLEAR <FRM_DYN_WA>.
MOVE-CORRESPONDING <FRM_DATA_WA> TO <FRM_DYN_WA>.
ASSIGN COMPONENT ITAB_DATA_FJ_FDNAME OF STRUCTURE <FRM_DYN_WA> TO <FRM_FIELD_FJ>.
ASSIGN COMPONENT IT_KEY_FD-FIELD OF STRUCTURE <FRM_DYN_WA> TO <FRM_FIELD>.
<FRM_FIELD_FJ> = <FRM_FIELD>.
APPEND <FRM_DYN_WA> TO <FRM_DYN_TABLE>.
"合計行
IF P_AUTO_TOTAL IS NOT INITIAL.
MOVE-CORRESPONDING <FRM_DATA_WA> TO <FRM_DYN_WA_HJ>.
LOOP AT FRM_IT_EXCLE_TABLE_FIELD.
CASE FRM_IT_EXCLE_TABLE_FIELD-TYPE_KIND.
WHEN 'I' OR 'P'.
WHEN OTHERS.
ASSIGN COMPONENT FRM_IT_EXCLE_TABLE_FIELD-NAME OF STRUCTURE <FRM_DYN_WA_HJ> TO <FRM_FIELD_HJ>.
CLEAR <FRM_FIELD_HJ>.
ENDCASE.
ENDLOOP.
ASSIGN COMPONENT ITAB_DATA_FJ_FDNAME OF STRUCTURE <FRM_DYN_WA_HJ> TO <FRM_FIELD_HJ>.
<FRM_FIELD_HJ> = <FRM_FIELD_FJ>.
COLLECT <FRM_DYN_WA_HJ> INTO <FRM_DYN_TABLE_HJ>.
ENDIF.
ENDLOOP.
"儲存最大的資料表記錄數
IF FRM_ITAB_DATA_CNT IS INITIAL.
FRM_ITAB_DATA_CNT = 1.
ENDIF.
IF FRM_ITAB_DATA_MAX_ROWS IS INITIAL.
FRM_ITAB_DATA_MAX_ROWS = FRM_ITAB_DATA_ROWS.
ENDIF.
IF P_AUTO_TOTAL IS NOT INITIAL.
FRM_ITAB_DATA_MAX_ROWS = FRM_ITAB_DATA_MAX_ROWS + 1.
ENDIF.
EXCEL_ITAB_DATA_MAX_CNT = FRM_ITAB_DATA_MAX_ROWS.
EXCEL_DATA_ROWCNTS = EXCEL_DATA_ROWCNTS + FRM_ITAB_DATA_CNT.
ENDFORM. " FRM_MOVE_DATA
*&---------------------------------------------------------------------*
*& Form FRM_GET_MERGECELLS
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_DYN_DATA text
*----------------------------------------------------------------------*
FORM FRM_GET_MERGECELLS USING P_DYN_DATA TYPE REF TO DATA.
DATA:FRM_ROWID TYPE P DECIMALS 0,
FRM_IT_EXCEL_TAB_DATA_TITLE LIKE IT_EXCEL_TAB_DATA_TITLE OCCURS 0 WITH HEADER LINE,
FRM_FJ_FDNAME_TMP(20),
FRM_FDNAME_TMP(20),
FRM_EXE.
FIELD-SYMBOLS:<FRM_TABLE> TYPE STANDARD TABLE,<FRM_WA1>,<FRM_WA2>,<FRM_FJ_FDNAME>,<FRM_FDNAME>.
FRM_IT_EXCEL_TAB_DATA_TITLE[] = IT_EXCEL_TAB_DATA_TITLE[].
DELETE FRM_IT_EXCEL_TAB_DATA_TITLE WHERE NAME IS INITIAL.
SORT FRM_IT_EXCEL_TAB_DATA_TITLE BY NAME.
ASSIGN P_DYN_DATA->* TO <FRM_TABLE>.
READ TABLE <FRM_TABLE> ASSIGNING <FRM_WA2> INDEX 1.
CLEAR:IT_MERGECELLS[],IT_MERGECELLS.
LOOP AT FRM_IT_EXCEL_TAB_DATA_TITLE WHERE MERGCELL IS NOT INITIAL.
CLEAR IT_MERGECELLS.
CLEAR:FRM_FJ_FDNAME_TMP,FRM_FDNAME_TMP.
LOOP AT <FRM_TABLE> ASSIGNING <FRM_WA1>.
FRM_ROWID = SY-TABIX.
MOVE-CORRESPONDING <FRM_WA1> TO <FRM_WA2>.
ASSIGN COMPONENT ITAB_DATA_FJ_FDNAME OF STRUCTURE <FRM_WA1> TO <FRM_FJ_FDNAME>.
ASSIGN COMPONENT FRM_IT_EXCEL_TAB_DATA_TITLE-NAME OF STRUCTURE <FRM_WA1> TO <FRM_FDNAME>.
IF FRM_FJ_FDNAME_TMP = <FRM_FJ_FDNAME>.
ENDIF.
IF FRM_FDNAME_TMP <> <FRM_FDNAME>.
IF FRM_FDNAME_TMP IS NOT INITIAL.
IF IT_MERGECELLS-ED_ROWID IS INITIAL.
IT_MERGECELLS-ED_ROWID = IT_MERGECELLS-BG_ROWID .
ENDIF.
IF IT_MERGECELLS-ED_COLNAME IS INITIAL.
IT_MERGECELLS-ED_COLNAME = IT_MERGECELLS-BG_COLNAME.
ENDIF.
IF IT_MERGECELLS-BG_ROWID <> IT_MERGECELLS-ED_ROWID.
APPEND IT_MERGECELLS.
ENDIF.
ENDIF.
CLEAR IT_MERGECELLS.
IT_MERGECELLS-BG_ROWID = FRM_ROWID. "不相等行首行
IT_MERGECELLS-BG_COLNAME = FRM_IT_EXCEL_TAB_DATA_TITLE-BG_COLNAME.
FRM_FDNAME_TMP = <FRM_FDNAME>.
ELSE.
IF IT_MERGECELLS-BG_ROWID IS INITIAL.
IT_MERGECELLS-BG_ROWID = FRM_ROWID.
IT_MERGECELLS-BG_COLNAME = FRM_IT_EXCEL_TAB_DATA_TITLE-ED_COLNAME.
ELSE.
IT_MERGECELLS-ED_ROWID = FRM_ROWID.
IT_MERGECELLS-ED_COLNAME = FRM_IT_EXCEL_TAB_DATA_TITLE-ED_COLNAME.
ENDIF.
FRM_EXE = 'X'.
ENDIF.
ENDLOOP.
IF FRM_EXE IS NOT INITIAL.
IF IT_MERGECELLS-ED_ROWID IS INITIAL.
IT_MERGECELLS-ED_ROWID = IT_MERGECELLS-BG_ROWID .
ENDIF.
IF IT_MERGECELLS-ED_COLNAME IS INITIAL.
IT_MERGECELLS-ED_COLNAME = IT_MERGECELLS-BG_COLNAME.
ENDIF.
IF IT_MERGECELLS-BG_ROWID <> IT_MERGECELLS-ED_ROWID.
APPEND IT_MERGECELLS.
ENDIF.
ENDIF.
ENDLOOP.
ENDFORM. "frm_get_MERGECELLS
*&---------------------------------------------------------------------*
*& Form frm_set_mergecells
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_BG_ROWID text
* -->P_ED_ROWID text
* -->P_BG_COLNAME text
* -->P_ED_COLNAME text
*----------------------------------------------------------------------*
FORM FRM_SET_MERGECELLS USING P_BG_ROWID P_ED_ROWID P_BG_COLNAME P_ED_COLNAME.
IF P_BG_ROWID > 0 AND P_BG_ROWID <> P_ED_ROWID.
ENDIF.
ENDFORM. "frm_set_mergecells
*&---------------------------------------------------------------------*
*& Form FRM_CREATE_ITAB
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_DATA text
* -->P_IT_FIELD text
* -->P_DYN_TABLE text
* -->FIELD-SYMBOLS text
* -->: text
* --><FRM_DYN_TABL> text
* -->, text
* --><FRM_WA> text
* -->, text
* --><FRM_FIELD> text
*----------------------------------------------------------------------*
FORM FRM_CREATE_ITAB TABLES P_IT_DATA
P_IT_FIELD STRUCTURE IT_EXCLE_TABLE_FIELD
CHANGING P_DYN_TABLE TYPE REF TO DATA P_DYN_WA TYPE REF TO DATA.
FIELD-SYMBOLS:<FRM_DYN_TABL> TYPE STANDARD TABLE,<FRM_WA>,<FRM_FIELD>.
DATA: FRM_DY_TABLE TYPE REF TO DATA,
"FRM_DY_LINE TYPE REF TO DATA,
FRM_IFC TYPE LVC_T_FCAT,
FRM_XFC TYPE LVC_S_FCAT.
DATA:FRM_ITAB_COMPONET TYPE REF TO CL_ABAP_STRUCTDESCR.
FRM_ITAB_COMPONET ?= CL_ABAP_TYPEDESCR=>DESCRIBE_BY_DATA( P_IT_DATA ).
CLEAR:P_IT_FIELD.
"附加一個字段
FRM_XFC-FIELDNAME = ITAB_DATA_FJ_FDNAME.
FRM_XFC-OUTPUTLEN = 20.
FRM_XFC-INTTYPE = 'C'.
FRM_XFC-DECIMALS = 0.
APPEND FRM_XFC TO FRM_IFC.
LOOP AT FRM_ITAB_COMPONET->COMPONENTS ASSIGNING <FRM_WA>. "列的處理
CLEAR: FRM_XFC.
ASSIGN COMPONENT 'NAME' OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
FRM_XFC-FIELDNAME = <FRM_FIELD>.
ASSIGN COMPONENT 'LENGTH' OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
FRM_XFC-INTLEN = <FRM_FIELD>.
ASSIGN COMPONENT 'TYPE_KIND' OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
FRM_XFC-INTTYPE = <FRM_FIELD>.
IF <FRM_FIELD> EQ 'h'.
CONTINUE.
ENDIF.
ASSIGN COMPONENT 'DECIMALS' OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
FRM_XFC-DECIMALS = <FRM_FIELD>.
CASE FRM_XFC-INTTYPE. "将日期轉換成字元串 add by wkb 2015-01-09
WHEN 'D'.
FRM_XFC-INTTYPE = 'C'.
FRM_XFC-INTLEN = '20'.
WHEN 'T'.
FRM_XFC-INTTYPE = 'C'.
FRM_XFC-INTLEN = '20'.
ENDCASE.
APPEND FRM_XFC TO FRM_IFC.
CLEAR: FRM_XFC.
CLEAR P_IT_FIELD.
MOVE-CORRESPONDING <FRM_WA> TO P_IT_FIELD.
APPEND P_IT_FIELD.
ENDLOOP.
"建立内表
CALL METHOD CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE
EXPORTING
IT_FIELDCATALOG = FRM_IFC
I_LENGTH_IN_BYTE = 'X'
IMPORTING
EP_TABLE = FRM_DY_TABLE.
P_DYN_TABLE = FRM_DY_TABLE.
ASSIGN P_DYN_TABLE->* TO <FRM_DYN_TABL>.
CREATE DATA P_DYN_WA LIKE LINE OF <FRM_DYN_TABL>.
ENDFORM. "frm_create_itab
*&---------------------------------------------------------------------*
*& Form FRM_EXP_DC_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SHEETID text
* -->P_TITLEID text
* -->P_DYN_DATA text
* -->P_DYN_DATA_HJ text
*----------------------------------------------------------------------*
FORM FRM_EXP_DC_DATA USING P_SHEETID P_TITLEID
P_DYN_DATA TYPE REF TO DATA
P_DYN_DATA_HJ TYPE REF TO DATA.
DATA:FRM_CURROWID TYPE I.
FIELD-SYMBOLS:<FRM_TAB> TYPE STANDARD TABLE,
<FRM_WA>,
<FRM_FDNAME>.
IF P_DYN_DATA IS INITIAL.
RETURN.
ENDIF.
"PERFORM FRM_GET_MERGECELLS USING P_DYN_DATA.
CLEAR:IT_EXCEL_ROWS,IT_EXCEL_ROWS[].
ASSIGN P_DYN_DATA->* TO <FRM_TAB>.
PERFORM FRM_SET_FILE_EXP_MOD USING P_SHEETID P_TITLEID. "設定導出模式
SORT IT_TB_CELL_HALIGNMENT BY NAME.
"LOOP導出檔案
LOOP AT <FRM_TAB> ASSIGNING <FRM_WA>.
ASSIGN COMPONENT ITAB_DATA_FJ_FDNAME OF STRUCTURE <FRM_WA> TO <FRM_FDNAME>.
AT NEW (ITAB_DATA_FJ_FDNAME).
PERFORM FRM_SET_FILE_TITLE USING P_SHEETID P_TITLEID <FRM_FDNAME>. "表頭
PERFORM FRM_SET_COND_DATA USING P_SHEETID P_TITLEID <FRM_FDNAME>. "查詢條件
PERFORM FRM_SET_TAB_DATA_TITLE USING P_SHEETID P_TITLEID <FRM_FDNAME>. "表格擡頭描述
PERFORM FRM_GET_CURROW CHANGING FRM_CURROWID. "取當行行号
ENDAT.
PERFORM FRM_SET_TAB_DATA USING P_SHEETID <FRM_WA> '' <FRM_FDNAME> CHANGING FRM_CURROWID. "表裡面的資料
AT END OF (ITAB_DATA_FJ_FDNAME).
PERFORM FRM_SET_TAB_HJ_DATA USING P_DYN_DATA_HJ P_SHEETID P_TITLEID <FRM_FDNAME> CHANGING FRM_CURROWID. "合計資料
PERFORM FRM_EXCLE_SAVE_ROW USING P_SHEETID EXCEL_TDATA FRM_CURROWID FRM_CURROWID <FRM_FDNAME>. "儲存最後的行号
PERFORM FRM_SET_FOOTER_DATA USING P_SHEETID P_TITLEID <FRM_FDNAME>. "表腳資料
PERFORM FRM_EXCEL_SET_BORDER USING P_SHEETID <FRM_FDNAME>. "邊框
ENDAT.
ENDLOOP.
ENDFORM. "FRM_EXP_dc_DATA
*&---------------------------------------------------------------------*
*& Form frm_set_tab_hj_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --><FRM_WA> text
*----------------------------------------------------------------------*
FORM FRM_SET_TAB_HJ_DATA USING P_DYN_DATA_HJ TYPE REF TO DATA
P_SHEETID P_TITLEID P_KEY_FIELD "合計資料
CHANGING P_ROWID.
DATA:FRM_CURROWID TYPE I.
FIELD-SYMBOLS:<FRM_TABLE> TYPE STANDARD TABLE,
<FRM_WA>,<FRM_FIELD>.
ASSIGN P_DYN_DATA_HJ->* TO <FRM_TABLE>.
IF <FRM_TABLE>[] IS INITIAL.
RETURN.
ENDIF.
READ TABLE <FRM_TABLE> ASSIGNING <FRM_WA> WITH KEY (ITAB_DATA_FJ_FDNAME) = P_KEY_FIELD.
IF SY-SUBRC NE 0.
RETURN.
ENDIF.
DATA:FRM_IT_TITLE LIKE ZST_EXP_EXCEL_TAB_FD_TITLE OCCURS 0 WITH HEADER LINE.
PERFORM FRM_GET_ITAB_TITLE TABLES FRM_IT_TITLE
USING P_TITLEID P_KEY_FIELD.
PERFORM FRM_SET_TAB_DATA USING P_SHEETID <FRM_WA> 'HJ' P_KEY_FIELD CHANGING P_ROWID. "表裡面的資料
ENDFORM. "frm_set_tab_hj_data
*&---------------------------------------------------------------------*
*& Form frm_del_null_sheets
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM FRM_DEL_NULL_SHEETS.
DATA:FRM_NO_NULL_SHEET_CNT TYPE I,
FRM_ACTIVE_SHEET_CNT TYPE I,
FRM_NULL_SHEET_CNT TYPE I,
FRM_SHEET_CURID TYPE I.
CALL METHOD OF L_EXCEL 'sheets' = L_SHEET.
GET PROPERTY OF L_SHEET 'COUNT' = FRM_ACTIVE_SHEET_CNT. "取有效SHEET的總數
FRM_NO_NULL_SHEET_CNT = LINES( IT_EXP_SHEETNAME ).
FRM_NULL_SHEET_CNT = FRM_ACTIVE_SHEET_CNT - FRM_NO_NULL_SHEET_CNT. "空白的SHEET總數 = 有效SHEET的總數 - 寫入SHEET的總數
DO FRM_NULL_SHEET_CNT TIMES.
FRM_SHEET_CURID = FRM_ACTIVE_SHEET_CNT - ( SY-INDEX - 1 ).
CALL METHOD OF L_EXCEL 'sheets' = L_SHEET
EXPORTING
#1 = FRM_SHEET_CURID.
CALL METHOD OF L_SHEET 'Delete'.
ENDDO.
ENDFORM. "frm_del_null_sheets
*&---------------------------------------------------------------------*
*& Form FRM_EXCEL_SAVE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM FRM_EXCEL_SAVE.
DATA: FRM_FNAME LIKE RLGRAP-FILENAME,
FRM_PATH_LEN TYPE I.
IF SAVE_FILENAME IS INITIAL.
RETURN.
ENDIF.
GET PROPERTY OF L_EXCEL 'ActiveSheet' = L_SHEET.
FREE OBJECT L_SHEET.
FREE OBJECT L_BOOKS.
GET PROPERTY OF L_EXCEL 'ActiveWorkbook' = L_BOOKS.
CALL METHOD OF L_BOOKS 'SAVEAS'
EXPORTING
#1 = SAVE_FILENAME
#2 = 1.
CALL METHOD OF L_BOOKS 'CLOSE'.
CALL METHOD OF L_EXCEL 'SAVE'.
CALL METHOD OF L_EXCEL 'QUIT'.
MESSAGE S001(00) WITH '檔案儲存成功'.
ENDFORM. "FRM_EXCEL_SAVE
*&---------------------------------------------------------------------*
*& Form FRM_EXCEL_SET_BORDER
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SHEETID text
* -->P_KEY_FDVALUE text
*----------------------------------------------------------------------*
FORM FRM_EXCEL_SET_BORDER USING P_SHEETID P_KEY_FDVALUE.
DATA:FRM_IT_EXCEL_ROWS LIKE IT_EXCEL_ROWS OCCURS 0 WITH HEADER LINE,
FRM_IT_TITLE LIKE IT_EXCEL_TAB_DATA_TITLE OCCURS 0 WITH HEADER LINE,
FRM_IT_TITLE2 LIKE IT_EXCEL_TAB_DATA_TITLE OCCURS 0 WITH HEADER LINE,
FRM_WA_TBTIL_ROWS LIKE LINE OF IT_EXCEL_ROWS,
FRM_WA_TDATA_ROWS LIKE LINE OF IT_EXCEL_ROWS,
FRM_WA_TITLE LIKE LINE OF IT_EXCEL_TAB_DATA_TITLE,
FRM_BG_CELL(6),FRM_ED_CELL(6),
FRM_LINECOUNT TYPE I,
FRM_I1 TYPE I,FRM_I2 TYPE I.
FRM_IT_EXCEL_ROWS[] = IT_EXCEL_ROWS[].
DELETE FRM_IT_EXCEL_ROWS WHERE KEY_FDVALUE <> P_KEY_FDVALUE.
DELETE FRM_IT_EXCEL_ROWS WHERE ROWTYP <> EXCEL_TBTIL AND ROWTYP <> EXCEL_TDATA AND
ROWTYP(4) <> EXCEL_CONDBD AND ROWTYP(4) <> EXCEL_FOOTBD.
FRM_IT_TITLE[] = IT_EXCEL_TAB_DATA_TITLE[].
DELETE FRM_IT_TITLE WHERE ID <> P_SHEETID.
IF FRM_IT_TITLE[] IS INITIAL.
FRM_IT_TITLE[] = IT_EXCEL_TAB_DATA_TITLE[].
DELETE FRM_IT_TITLE WHERE ID IS NOT INITIAL.
FRM_IT_TITLE2[] = FRM_IT_TITLE[].
DELETE FRM_IT_TITLE WHERE KEY_FDVALUE <> P_KEY_FDVALUE.
IF FRM_IT_TITLE[] IS INITIAL.
FRM_IT_TITLE[] = FRM_IT_TITLE2[].
DELETE FRM_IT_TITLE WHERE KEY_FDVALUE IS NOT INITIAL.
ENDIF.
ENDIF.
FRM_I1 = 1. FRM_I2 = 2.
DO.
CLEAR:FRM_WA_TBTIL_ROWS, FRM_WA_TDATA_ROWS.
"取每次個表頭擡的行号資料
READ TABLE FRM_IT_EXCEL_ROWS INTO FRM_WA_TBTIL_ROWS INDEX FRM_I1.
IF SY-SUBRC NE 0.
EXIT.
ENDIF.
"取每次個表資料的最大行号資料
READ TABLE FRM_IT_EXCEL_ROWS INTO FRM_WA_TDATA_ROWS INDEX FRM_I2.
"取第一個字段的列名
READ TABLE FRM_IT_TITLE INTO FRM_WA_TITLE INDEX 1.
FRM_BG_CELL = FRM_WA_TITLE-BG_COLNAME.
CONDENSE FRM_BG_CELL NO-GAPS.
"取最後一個字段的列名
FRM_LINECOUNT = LINES( FRM_IT_TITLE ).
READ TABLE FRM_IT_TITLE INTO FRM_WA_TITLE INDEX FRM_LINECOUNT.
FRM_ED_CELL = FRM_WA_TITLE-ED_COLNAME.
CONDENSE FRM_ED_CELL NO-GAPS.
IF FRM_WA_TBTIL_ROWS-BG_ROWID IS NOT INITIAL AND FRM_WA_TDATA_ROWS-ED_ROWID IS INITIAL.
FRM_WA_TDATA_ROWS-ED_ROWID = FRM_WA_TBTIL_ROWS-BG_ROWID.
ENDIF.
IF FRM_WA_TBTIL_ROWS-BG_ROWID IS INITIAL AND FRM_WA_TDATA_ROWS-ED_ROWID IS NOT INITIAL.
FRM_WA_TBTIL_ROWS-BG_ROWID = FRM_WA_TDATA_ROWS-ED_ROWID.
ENDIF.
PERFORM FRM_EXCEL_BORDER USING FRM_WA_TBTIL_ROWS-BG_ROWID FRM_WA_TDATA_ROWS-ED_ROWID FRM_BG_CELL FRM_ED_CELL.
ADD 2 TO FRM_I1.
ADD 2 TO FRM_I2.
ENDDO.
ENDFORM. "FRM_EXCEL_SET_BORDER
*&---------------------------------------------------------------------*
*& Form FRM_EXCEL_BORDER
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->X text
* -->Y text
* -->M text
* -->N text
*----------------------------------------------------------------------*
FORM FRM_EXCEL_BORDER USING X Y M N.
DATA: L_C1(4),
L_C2(4).
L_C1 = X.
L_C2 = Y.
CONDENSE L_C1 NO-GAPS.
CONDENSE L_C2 NO-GAPS.
CONCATENATE M L_C1 INTO L_C1.
CONCATENATE N L_C2 INTO L_C2.
CALL METHOD OF L_SHEET 'Range' = L_RANGE NO FLUSH
EXPORTING #1 = L_C1 #2 = L_C2.
PERFORM FRM_ERR_HDL.
* 設定邊界線
**設定外框左邊(xlEdgeLeft)線條的線型和粗細
CALL METHOD OF L_RANGE 'BORDERS' = L_BORDER
EXPORTING
#1 = '7'.
SET PROPERTY OF L_BORDER 'LineStyle' = '1'.
SET PROPERTY OF L_BORDER 'WEIGHT' = 3. "4=max
**設定外框上邊(xlEdgeTop)線條的線型和粗細
CALL METHOD OF L_RANGE 'BORDERS' = L_BORDER
EXPORTING
#1 = '8'.
SET PROPERTY OF L_BORDER 'LineStyle' = '1'.
SET PROPERTY OF L_BORDER 'WEIGHT' = 3. "4=max
**設定外框下邊(xlEdgeBottom)線條的線型和粗細
CALL METHOD OF L_RANGE 'BORDERS' = L_BORDER
EXPORTING
#1 = '9'.
SET PROPERTY OF L_BORDER 'LineStyle' = '1'.
SET PROPERTY OF L_BORDER 'WEIGHT' = 3. "4=max
**設定外框右邊(xlEdgeRight)線條的線型和粗細
CALL METHOD OF L_RANGE 'BORDERS' = L_BORDER
EXPORTING
#1 = '10'.
SET PROPERTY OF L_BORDER 'LineStyle' = '1'.
SET PROPERTY OF L_BORDER 'WEIGHT' = 3. "4=max
**設定内框垂直(xlInsideVertical)線條的線型和粗細
CALL METHOD OF L_RANGE 'BORDERS' = L_BORDER
EXPORTING
#1 = '11'.
SET PROPERTY OF L_BORDER 'LineStyle' = '1'.
SET PROPERTY OF L_BORDER 'WEIGHT' = 2. "4=max
**設定内框水準(xlInsideHorizontal)線條的線型和粗細
CALL METHOD OF L_RANGE 'BORDERS' = L_BORDER
EXPORTING
#1 = '12'.
SET PROPERTY OF L_BORDER 'LineStyle' = '1'.
SET PROPERTY OF L_BORDER 'WEIGHT' = 2. "4=max
ENDFORM. "FRM_EXCEL_BORDER
*&---------------------------------------------------------------------*
*& Form FRM_SET_FILE_EXP_MOD
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SHEETID text
* -->P_TITLEID text
*----------------------------------------------------------------------*
FORM FRM_SET_FILE_EXP_MOD USING P_SHEETID P_TITLEID.
DATA:FRM_LS_SHEET TYPE OLE2_OBJECT,
FRM_SHEETNAME TYPE STRING.
CALL METHOD OF L_EXCEL 'sheets' = L_SHEET
EXPORTING
#1 = P_SHEETID.
PERFORM FRM_ERR_HDL.
CALL METHOD OF L_SHEET 'select' = FRM_LS_SHEET
EXPORTING
#1 = P_SHEETID.
PERFORM FRM_SET_SHEET_NAME USING P_SHEETID P_TITLEID CHANGING FRM_SHEETNAME.
IF FRM_SHEETNAME IS NOT INITIAL.
SET PROPERTY OF L_SHEET 'Name' = FRM_SHEETNAME NO FLUSH.
PERFORM FRM_ERR_HDL.
ENDIF.
" 設定所有單元格的屬性
CALL METHOD OF L_SHEET 'Cells' = L_CELL NO FLUSH.
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_CELL 'HorizontalAlignment' = 3 NO FLUSH .
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_CELL 'VerticalAlignment' = 2 NO FLUSH .
PERFORM FRM_ERR_HDL.
GET PROPERTY OF L_CELL 'Font' = L_FONT NO FLUSH .
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_FONT 'Name' = FONT_NAME NO FLUSH.
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_FONT 'Size' = FONT_SIZE NO FLUSH.
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_CELL 'WrapText' = 1 NO FLUSH.
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_CELL 'ROWHEIGHT' = 18 NO FLUSH.
PERFORM FRM_ERR_HDL.
ENDFORM. "frm_set_file_exp_mod
*&---------------------------------------------------------------------*
*& Form FRM_SET_SHEET_NAME
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SHEETID text
* -->P_TITLEID text
* -->P_SHEETNAME text
*----------------------------------------------------------------------*
FORM FRM_SET_SHEET_NAME USING P_SHEETID P_TITLEID CHANGING P_SHEETNAME.
DATA: FRM_SHEETID TYPE STRING,
FRM_IT_EXCEL_DATA LIKE IT_EXCEL_DATA OCCURS 0 WITH HEADER LINE,
FRM_WA_EXP_SHEETNAME LIKE LINE OF IT_EXP_SHEETNAME.
DATA: FRM_SHEETNAME_TMP(5) VALUE 'SHEET',
FRM_ROWID TYPE I.
CLEAR P_SHEETNAME.
FRM_IT_EXCEL_DATA[] = IT_EXCEL_DATA[].
READ TABLE FRM_IT_EXCEL_DATA WITH KEY ID = P_TITLEID.
IF FRM_IT_EXCEL_DATA-SHEETNAME IS INITIAL.
FRM_IT_EXCEL_DATA-SHEETNAME = FRM_IT_EXCEL_DATA-TITLE1.
ENDIF.
CLEAR FRM_WA_EXP_SHEETNAME.
LOOP AT IT_EXP_SHEETNAME INTO FRM_WA_EXP_SHEETNAME WHERE MS = FRM_IT_EXCEL_DATA-SHEETNAME.
ENDLOOP.
IF FRM_WA_EXP_SHEETNAME IS INITIAL.
P_SHEETNAME = FRM_IT_EXCEL_DATA-SHEETNAME.
ELSE.
FRM_SHEETID = FRM_WA_EXP_SHEETNAME-ID + 1.
CONCATENATE FRM_IT_EXCEL_DATA-SHEETNAME '(' FRM_SHEETID ')' INTO P_SHEETNAME.
CONDENSE P_SHEETNAME NO-GAPS.
ENDIF.
"儲存已導過的SHEETNAME
PERFORM FRM_SAVE_EXP_SHEETNAME USING FRM_SHEETID FRM_IT_EXCEL_DATA-SHEETNAME.
ENDFORM. "frm_set_sheet_name
*&---------------------------------------------------------------------*
*& Form frm_save_EXP_SHEETNAME
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_ID text
* -->P_MS text
*----------------------------------------------------------------------*
FORM FRM_SAVE_EXP_SHEETNAME USING P_ID P_MS.
CLEAR IT_EXP_SHEETNAME.
IF P_ID IS INITIAL OR P_ID = 0.
IT_EXP_SHEETNAME-ID = 1.
ELSE.
IT_EXP_SHEETNAME-ID = P_ID.
ENDIF.
CONDENSE IT_EXP_SHEETNAME-ID NO-GAPS.
IT_EXP_SHEETNAME-MS = P_MS.
COLLECT IT_EXP_SHEETNAME.
ENDFORM. "frm_save_EXP_SHEETNAME
*&---------------------------------------------------------------------*
*& Form FRM_GET_SAVE_FILENAME
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_FILENAME text
*----------------------------------------------------------------------*
FORM FRM_GET_SAVE_FILENAME CHANGING P_FILENAME.
DATA:FRM_FILENAME TYPE STRING,
FRM_PATH TYPE STRING,
FRM_FULLPATH TYPE STRING.
IF SAVE_FILENAME IS NOT INITIAL.
P_FILENAME = SAVE_FILENAME.
RETURN.
ENDIF.
CLEAR P_FILENAME.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
EXPORTING
WINDOW_TITLE = '儲存目錄...'
FILE_FILTER = 'excel檔案 (*.xls)|*.XLS'
CHANGING
FILENAME = FRM_FILENAME
PATH = FRM_PATH
FULLPATH = FRM_FULLPATH
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4.
IF SY-SUBRC <> 0.
RETURN.
ENDIF.
P_FILENAME = FRM_FULLPATH.
ENDFORM. "frm_get_save_filename
*&---------------------------------------------------------------------*
*& Form FRM_EXCEL_FREE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM FRM_EXCEL_FREE.
FREE OBJECT L_EXCEL.
FREE OBJECT L_WINDOW.
FREE OBJECT L_BOOKS.
FREE OBJECT L_BOOK.
FREE OBJECT L_CELL.
FREE OBJECT L_FONT.
FREE OBJECT L_COLUMN.
FREE OBJECT L_ROW.
FREE OBJECT L_RANGE.
FREE OBJECT L_BORDER.
FREE OBJECT L_SHEET.
FREE DYN_DATA1.
FREE DYN_DATA2.
FREE DYN_DATA3.
ENDFORM. "frm_excel_free
*&---------------------------------------------------------------------*
*& Form FRM_SET_FILE_TITLE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SHEETID text
* -->P_TITLEID text
* -->P_KEY_FDVALUE text
*----------------------------------------------------------------------*
FORM FRM_SET_FILE_TITLE USING P_SHEETID P_TITLEID P_KEY_FDVALUE.
DATA:FRM_ROWID TYPE I,
FRM_CURROWID TYPE I.
DATA:FRM_IT_EXCEL_TITLE LIKE IT_EXCEL_TITLE OCCURS 0 WITH HEADER LINE.
"當寫入EXCEL的行号超過或等于設定的最大行數時,自動往下個SHEET寫資料
PERFORM FRM_GET_CURROW CHANGING FRM_ROWID .
IF FRM_ROWID => EXCEL_SHEET_MAX_ROW.
PERFORM FRM_EXCEL_SET_BORDER USING P_SHEETID P_KEY_FDVALUE. "邊框
ADD 1 TO EXCEL_CUR_SHEETID.
PERFORM FRM_SET_FILE_EXP_MOD USING EXCEL_CUR_SHEETID P_TITLEID. "設定導出模式
CLEAR:IT_EXCEL_ROWS[],IT_EXCEL_ROWS.
ENDIF.
"取出行号
PERFORM FRM_GET_CURROW CHANGING FRM_ROWID.
IF FRM_ROWID IS NOT INITIAL OR FRM_ROWID > 0.
FRM_ROWID = FRM_ROWID + FORM_ROW_PITCH. "加上指定間隔的行數
ENDIF.
FRM_IT_EXCEL_TITLE[] = IT_EXCEL_TITLE[].
DELETE FRM_IT_EXCEL_TITLE WHERE ID <> P_TITLEID.
IF FRM_IT_EXCEL_TITLE[] IS INITIAL.
FRM_IT_EXCEL_TITLE[] = IT_EXCEL_TITLE[].
DELETE FRM_IT_EXCEL_TITLE WHERE ID IS NOT INITIAL.
ENDIF.
PERFORM FRM_GET_EXCEL_FILE_TITLE TABLES FRM_IT_EXCEL_TITLE USING P_TITLEID P_KEY_FDVALUE.
LOOP AT FRM_IT_EXCEL_TITLE.
FRM_CURROWID = SY-TABIX.
IF FRM_CURROWID = 1. "主表頭描述
FRM_CURROWID = FRM_CURROWID + FRM_ROWID.
PERFORM FRM_SET_TITLE_FORMAT USING P_TITLEID FRM_CURROWID EXCEL_TITLE1_ROWHIGH
EXCEL_TITLE1_SIZE FRM_IT_EXCEL_TITLE-MS P_KEY_FDVALUE. "設定表頭的格式
ELSE.
"除了主表頭描述以外的表頭描述
FRM_CURROWID = FRM_CURROWID + FRM_ROWID.
PERFORM FRM_SET_TITLE_FORMAT USING P_TITLEID FRM_CURROWID EXCEL_TITLE2_ROWHIGH
EXCEL_TITLE2_SIZE FRM_IT_EXCEL_TITLE-MS P_KEY_FDVALUE. "設定表頭的格式
ENDIF.
"儲存目前的行号
PERFORM FRM_EXCLE_SAVE_ROW USING P_SHEETID FRM_IT_EXCEL_TITLE-MS FRM_CURROWID FRM_CURROWID P_KEY_FDVALUE. "記錄行号
ENDLOOP.
ENDFORM. "frm_set_file_title
*&---------------------------------------------------------------------*
*& Form FRM_GET_EXCEL_FILE_TITLE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_EXCEL_TITLE text
* -->P_TITLEID text
* -->P_KEY_FDVALUE text
*----------------------------------------------------------------------*
FORM FRM_GET_EXCEL_FILE_TITLE TABLES P_IT_EXCEL_TITLE STRUCTURE IT_EXCEL_TITLE USING P_TITLEID P_KEY_FDVALUE.
DATA:FRM_IT_EXCEL_FILE_TITLE LIKE IT_EXCEL_FILE_TITLE OCCURS 0 WITH HEADER LINE.
FRM_IT_EXCEL_FILE_TITLE[] = IT_EXCEL_FILE_TITLE[].
IF FRM_IT_EXCEL_FILE_TITLE[] IS INITIAL.
RETURN.
ENDIF.
SORT FRM_IT_EXCEL_FILE_TITLE[] BY ID KEY_FDVALUE.
READ TABLE FRM_IT_EXCEL_FILE_TITLE WITH KEY ID = P_TITLEID KEY_FDVALUE = P_KEY_FDVALUE BINARY SEARCH.
IF SY-SUBRC NE 0.
SORT FRM_IT_EXCEL_FILE_TITLE[] BY KEY_FDVALUE.
READ TABLE FRM_IT_EXCEL_FILE_TITLE WITH KEY KEY_FDVALUE = P_KEY_FDVALUE BINARY SEARCH.
IF SY-SUBRC NE 0.
RETURN.
ENDIF.
ENDIF.
IF FRM_IT_EXCEL_FILE_TITLE-TITLE1 IS NOT INITIAL.
P_IT_EXCEL_TITLE-MS = FRM_IT_EXCEL_FILE_TITLE-TITLE1.
MODIFY P_IT_EXCEL_TITLE TRANSPORTING MS WHERE TYP = 'M'.
ENDIF.
IF FRM_IT_EXCEL_FILE_TITLE-TITLE2 IS NOT INITIAL.
P_IT_EXCEL_TITLE-MS = FRM_IT_EXCEL_FILE_TITLE-TITLE2.
MODIFY P_IT_EXCEL_TITLE TRANSPORTING MS WHERE TYP IS INITIAL.
ENDIF.
ENDFORM. "FRM_GET_EXCEL_FILE_TITLE
*&---------------------------------------------------------------------*
*& Form FRM_SET_FILE_TITLE_VALUE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_BG_CELL_NAME text
* -->P_ED_CELL_NAME text
* -->P_ROWHIGH text
* -->P_EXCEL_TITLE text
*----------------------------------------------------------------------*
FORM FRM_SET_FILE_TITLE_VALUE USING P_BG_CELL_NAME P_ED_CELL_NAME P_ROWHIGH P_EXCEL_TITLE.
DATA:FRM_ROWID TYPE I.
FRM_ROWID = P_BG_CELL_NAME+1.
SET PROPERTY OF L_RANGE 'Value' = P_EXCEL_TITLE NO FLUSH .
PERFORM FRM_ERR_HDL.
PERFORM ROW_HIGH USING FRM_ROWID P_ROWHIGH.
ENDFORM. "frm_set_file_title_value
*&---------------------------------------------------------------------*
*& Form ROW_HIGH
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->X text
* -->I_HIGH text
*----------------------------------------------------------------------*
FORM ROW_HIGH USING X I_HIGH.
DATA:FRM_HIGH TYPE I.
IF I_HIGH IS NOT INITIAL.
FRM_HIGH = I_HIGH.
ELSE.
FRM_HIGH = 25.
ENDIF.
CALL METHOD OF L_SHEET 'Rows' = L_ROW NO FLUSH
EXPORTING
#1 = X.
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_ROW 'RowHeight' = FRM_HIGH NO FLUSH.
PERFORM FRM_ERR_HDL.
ENDFORM. "row_high
*&---------------------------------------------------------------------*
*& Form frm_get_title_bg_cell
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_CELL_NAME text
*----------------------------------------------------------------------*
FORM FRM_GET_TITLE_BG_CELL CHANGING P_CELL_NAME.
DATA:FRM_CELL_NAME TYPE STRING,FRM_ROWID TYPE I,FRM_ROWIDS TYPE STRING.
CLEAR P_CELL_NAME.
PERFORM FRM_GET_CURROW CHANGING FRM_ROWID.
FRM_ROWIDS = FRM_ROWID.
CONCATENATE 'A' FRM_ROWIDS INTO FRM_CELL_NAME.
CONDENSE FRM_CELL_NAME NO-GAPS.
P_CELL_NAME = FRM_CELL_NAME.
ENDFORM. "frm_get_title_bg_cell
*&---------------------------------------------------------------------*
*& Form FRM_GET_TITLE_ED_CELL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_BG_CELL_NAME text
* -->P_ED_CELL_NAME text
*----------------------------------------------------------------------*
FORM FRM_GET_TITLE_ED_CELL USING P_BG_CELL_NAME CHANGING P_ED_CELL_NAME.
DATA: FRM_LANGE2(10),
FRM_BG_COL_NAME(10),
FRM_ED_COL_NAME(10),
FRM_ED_COL_NAME_TMP(10),
FRM_BG_COL_NAME_LEN TYPE I.
CONDENSE P_ED_CELL_NAME NO-GAPS.
FRM_ED_COL_NAME = P_ED_CELL_NAME.
CLEAR FRM_ED_COL_NAME_TMP.
CONDENSE FRM_ED_COL_NAME NO-GAPS.
FRM_BG_COL_NAME = P_BG_CELL_NAME .
CONDENSE FRM_BG_COL_NAME NO-GAPS.
PERFORM FRM_GET_EXCEL_END_WORD CHANGING FRM_ED_COL_NAME.
IF FRM_ED_COL_NAME IS INITIAL.
P_ED_CELL_NAME = P_BG_CELL_NAME.
RETURN.
ENDIF.
FRM_BG_COL_NAME_LEN = STRLEN( FRM_BG_COL_NAME ) - 1.
CONCATENATE FRM_ED_COL_NAME FRM_BG_COL_NAME+1(FRM_BG_COL_NAME_LEN) INTO FRM_ED_COL_NAME_TMP.
CONDENSE FRM_ED_COL_NAME_TMP NO-GAPS.
P_ED_CELL_NAME = FRM_ED_COL_NAME_TMP.
ENDFORM. "FRM_GET_TITLE_ed_CELL
*&---------------------------------------------------------------------*
*& Form FRM_EXCLE_GET_ED_ROWID
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_ROWTYP text
* -->P_ROWID text
*----------------------------------------------------------------------*
FORM FRM_EXCLE_GET_ED_ROWID USING P_ROWTYP CHANGING P_ROWID .
PERFORM FRM_EXCLE_GET_ROWID USING P_ROWTYP 'ED_ROWID' CHANGING P_ROWID .
ENDFORM. "FRM_EXCLE_GET_ED_ROWID
*&---------------------------------------------------------------------*
*& Form FRM_EXCLE_GET_ROWID
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_ROWTYP text
* -->P_ROWNAME text
* -->P_ROWID text
*----------------------------------------------------------------------*
FORM FRM_EXCLE_GET_ROWID USING P_ROWTYP P_ROWNAME CHANGING P_ROWID .
FIELD-SYMBOLS:<FRM_WA>,<FRM_FIELD>.
CLEAR P_ROWID.
IF IT_EXCEL_ROWS[] IS INITIAL.
P_ROWID = 0.
RETURN.
ENDIF.
READ TABLE IT_EXCEL_ROWS ASSIGNING <FRM_WA> WITH KEY ROWTYP = P_ROWTYP.
IF SY-SUBRC EQ 0.
ASSIGN COMPONENT P_ROWNAME OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
P_ROWID = <FRM_FIELD>.
ELSE.
P_ROWID = LINES( IT_EXCEL_ROWS ).
READ TABLE IT_EXCEL_ROWS ASSIGNING <FRM_WA> INDEX P_ROWID.
IF SY-SUBRC EQ 0.
ASSIGN COMPONENT P_ROWNAME OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
P_ROWID = <FRM_FIELD>.
ELSE.
P_ROWID = 0.
ENDIF.
ENDIF.
ENDFORM. "FRM_EXCLE_GET_ROWID
*&---------------------------------------------------------------------*
*& Form FRM_EXCLE_SAVE_ROW
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_ID text
* -->P_ROWTYP text
* -->P_BGROW text
* -->P_EDROW text
*----------------------------------------------------------------------*
FORM FRM_EXCLE_SAVE_ROW USING P_ID
P_ROWTYP
P_BGROW
P_EDROW
P_KEY_FDVALUE.
DATA:FRM_ROWCNT TYPE I,
FRM_WA_EXCEL_ROWS LIKE LINE OF IT_EXCEL_ROWS.
CLEAR:IT_EXCEL_ROWS.
IT_EXCEL_ROWS-ID = P_ID.
IF IT_EXCEL_ROWS-ID IS INITIAL.
"IT_EXCEL_ROWS-ID = 1.
ENDIF.
IT_EXCEL_ROWS-KEY_FDVALUE = P_KEY_FDVALUE.
IT_EXCEL_ROWS-ROWTYP = P_ROWTYP.
CONDENSE IT_EXCEL_ROWS-ROWTYP NO-GAPS.
IF P_BGROW IS INITIAL OR P_EDROW IS INITIAL.
FRM_ROWCNT = LINES( IT_EXCEL_ROWS ).
READ TABLE IT_EXCEL_ROWS INTO FRM_WA_EXCEL_ROWS INDEX FRM_ROWCNT.
IF SY-SUBRC EQ 0.
IT_EXCEL_ROWS-BG_ROWID = FRM_WA_EXCEL_ROWS-BG_ROWID.
IT_EXCEL_ROWS-ED_ROWID = FRM_WA_EXCEL_ROWS-ED_ROWID.
ELSE.
IT_EXCEL_ROWS-BG_ROWID = 1.
IT_EXCEL_ROWS-ED_ROWID = 1.
ENDIF.
ELSE.
IT_EXCEL_ROWS-BG_ROWID = P_BGROW.
IT_EXCEL_ROWS-ED_ROWID = P_EDROW.
ENDIF.
APPEND IT_EXCEL_ROWS.
ENDFORM. " FRM_EXCLE_SAVE_ROW
*&---------------------------------------------------------------------*
*& Form FRM_GET_EXCEL_END_WORD
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_WORD text
*----------------------------------------------------------------------*
FORM FRM_GET_EXCEL_END_WORD CHANGING P_WORD.
CASE P_WORD.
WHEN '0'.
RETURN.
WHEN '1'.
P_WORD = 'A'.
WHEN '2'.
P_WORD = 'B'.
WHEN '3'.
P_WORD = 'C'.
WHEN '4'.
P_WORD = 'D'.
WHEN '5'.
P_WORD = 'E'.
WHEN '6'.
P_WORD = 'F'.
WHEN '7'.
P_WORD = 'G'.
WHEN '8'.
P_WORD = 'H'.
WHEN '9'.
P_WORD = 'I'.
WHEN '10'.
P_WORD = 'J'.
WHEN '11'.
P_WORD = 'K'.
WHEN '12'.
P_WORD = 'L'.
WHEN '13'.
P_WORD = 'M'.
WHEN '14'.
P_WORD = 'N'.
WHEN '15'.
P_WORD = 'O'.
WHEN '16'.
P_WORD = 'P'.
WHEN '17'.
P_WORD = 'Q'.
WHEN '18'.
P_WORD = 'R'.
WHEN '19'.
P_WORD = 'S'.
WHEN '20'.
P_WORD = 'T'.
WHEN '21'.
P_WORD = 'U'.
WHEN '22'.
P_WORD = 'V'.
WHEN '23'.
P_WORD = 'W'.
WHEN '24'.
P_WORD = 'X'.
WHEN '25'.
P_WORD = 'Y'.
WHEN '26'.
P_WORD = 'Z'.
WHEN OTHERS.
DATA:FRM_WORD_I TYPE I,
FRM_INDEX(10),
FRM_WORD_C(10),
FRM_MOD_I TYPE I,
FRM_MOD_C(2),
FRM_DIV_I TYPE I,
FRM_DIV_C(2).
FRM_WORD_I = P_WORD.
FRM_MOD_C = FRM_WORD_I MOD 26.
FRM_DIV_C = TRUNC( FRM_WORD_I / 26 ).
PERFORM FRM_GET_EXCEL_END_WORD CHANGING FRM_DIV_C. "取出第一位
PERFORM FRM_GET_EXCEL_END_WORD CHANGING FRM_MOD_C. "取出第二位
CONCATENATE FRM_DIV_C FRM_MOD_C INTO FRM_WORD_C.
CONDENSE FRM_WORD_C NO-GAPS.
P_WORD = FRM_WORD_C.
ENDCASE.
ENDFORM. " FRM_GET_EXCEL_END_WORD
*&---------------------------------------------------------------------*
*& Form frm_get_excel_end_num
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_WORD text
*----------------------------------------------------------------------*
FORM FRM_GET_EXCEL_END_NUM CHANGING P_WORD.
CONDENSE P_WORD NO-GAPS.
CASE P_WORD.
WHEN '0'.
RETURN.
WHEN 'A'.
P_WORD = '1'.
WHEN 'B'.
P_WORD = '2'.
WHEN 'C'.
P_WORD = '3'.
WHEN 'D'.
P_WORD = '4'.
WHEN 'E'.
P_WORD = '5'.
WHEN 'F'.
P_WORD = '6'.
WHEN 'G'.
P_WORD = '7'.
WHEN 'H'.
P_WORD = '8'.
WHEN 'I'.
P_WORD = '9'.
WHEN 'J'.
P_WORD = '10'.
WHEN 'K'.
P_WORD = '11'.
WHEN 'L'.
P_WORD = '12'.
WHEN 'M'.
P_WORD = '13'.
WHEN 'N'.
P_WORD = '14'.
WHEN 'O'.
P_WORD = '15'.
WHEN 'P'.
P_WORD = '16'.
WHEN 'Q'.
P_WORD = '17'.
WHEN 'R'.
P_WORD = '18'.
WHEN 'S'.
P_WORD = '19'.
WHEN 'T'.
P_WORD = '20'.
WHEN 'U'.
P_WORD = '21'.
WHEN 'V'.
P_WORD = '22'.
WHEN 'W'.
P_WORD = '23'.
WHEN 'X'.
P_WORD = '24'.
WHEN 'Y'.
P_WORD = '25'.
WHEN 'Z'.
P_WORD = '26'.
WHEN OTHERS.
DATA:FRM_CHAR TYPE I,
FRM_CHAR1.
CLEAR FRM_CHAR.
DO 2 TIMES.
FRM_CHAR1 = P_WORD(1).
PERFORM FRM_GET_EXCEL_END_NUM CHANGING FRM_CHAR1.
FRM_CHAR = FRM_CHAR * 26 + FRM_CHAR1.
P_WORD = P_WORD+1.
ENDDO.
P_WORD = FRM_CHAR.
ENDCASE.
ENDFORM. "frm_get_excel_end_num
*&---------------------------------------------------------------------*
*& Form FRM_SET_TITLE_FORMAT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_TITLEID text
* -->P_ROWID text
* -->P_ROW_HIGHT text
* -->P_SIZE text
* -->P_TITLE_TEXT text
*----------------------------------------------------------------------*
FORM FRM_SET_TITLE_FORMAT USING P_TITLEID P_ROWID P_ROW_HIGHT P_SIZE P_TITLE_TEXT P_KEY_FDVALUE.
DATA:FRM_IT_EXCEL_TAB_DATA_TITLE LIKE IT_EXCEL_TAB_DATA_TITLE OCCURS 0 WITH HEADER LINE,
FRM_IT_EXCEL_TAB_DATA_TITLE2 LIKE IT_EXCEL_TAB_DATA_TITLE OCCURS 0 WITH HEADER LINE.
DATA:FRM_BG_CELL_NAME TYPE STRING,
FRM_ED_CELL_NAME TYPE STRING.
DATA:FRM_ROWID TYPE STRING.
FRM_IT_EXCEL_TAB_DATA_TITLE[] = IT_EXCEL_TAB_DATA_TITLE[].
DELETE FRM_IT_EXCEL_TAB_DATA_TITLE WHERE NAME IS INITIAL.
IF P_TITLEID IS NOT INITIAL.
DELETE FRM_IT_EXCEL_TAB_DATA_TITLE WHERE ID <> P_TITLEID.
IF FRM_IT_EXCEL_TAB_DATA_TITLE[] IS INITIAL.
FRM_IT_EXCEL_TAB_DATA_TITLE[] = IT_EXCEL_TAB_DATA_TITLE[].
DELETE FRM_IT_EXCEL_TAB_DATA_TITLE WHERE NAME IS INITIAL.
DELETE FRM_IT_EXCEL_TAB_DATA_TITLE WHERE ID IS NOT INITIAL.
FRM_IT_EXCEL_TAB_DATA_TITLE2[] = FRM_IT_EXCEL_TAB_DATA_TITLE[].
DELETE FRM_IT_EXCEL_TAB_DATA_TITLE WHERE KEY_FDVALUE <> P_KEY_FDVALUE.
IF FRM_IT_EXCEL_TAB_DATA_TITLE[] IS INITIAL.
FRM_IT_EXCEL_TAB_DATA_TITLE[] = FRM_IT_EXCEL_TAB_DATA_TITLE2[].
DELETE FRM_IT_EXCEL_TAB_DATA_TITLE WHERE KEY_FDVALUE IS NOT INITIAL.
ENDIF.
ENDIF.
ENDIF.
FRM_ED_CELL_NAME = LINES( FRM_IT_EXCEL_TAB_DATA_TITLE ).
FRM_ROWID = P_ROWID.
CONCATENATE 'A' FRM_ROWID INTO FRM_BG_CELL_NAME. "取表頭描述的起始行列名
CONDENSE FRM_BG_CELL_NAME NO-GAPS.
PERFORM FRM_GET_TITLE_ED_CELL USING FRM_BG_CELL_NAME CHANGING FRM_ED_CELL_NAME. "取表頭描述的截止行列名
CALL METHOD OF L_SHEET 'Range' = L_RANGE NO FLUSH
EXPORTING
#1 = FRM_BG_CELL_NAME "'A1'
#2 = FRM_ED_CELL_NAME.
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_RANGE 'MergeCells' = 1 NO FLUSH .
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_RANGE 'HorizontalAlignment' = 3 NO FLUSH .
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_RANGE 'VerticalAlignment' = 2 NO FLUSH .
PERFORM FRM_ERR_HDL.
GET PROPERTY OF L_RANGE 'Font' = L_FONT NO FLUSH .
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_FONT 'Bold' = 1 NO FLUSH .
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_FONT 'Size' = P_SIZE NO FLUSH .
PERFORM FRM_ERR_HDL.
PERFORM FRM_SET_FILE_TITLE_VALUE USING FRM_BG_CELL_NAME FRM_ED_CELL_NAME P_ROW_HIGHT P_TITLE_TEXT. "将表頭描述寫入檔案
ENDFORM. "FRM_SET_TITLE_format
*&---------------------------------------------------------------------*
*& Form frm_get_currow
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_ROWID text
*----------------------------------------------------------------------*
FORM FRM_GET_CURROW CHANGING P_ROWID.
PERFORM FRM_EXCLE_GET_ED_ROWID USING '' CHANGING P_ROWID.
ENDFORM. "frm_get_currow
*&---------------------------------------------------------------------*
*& Form FRM_SET_COND_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SHEETID text
* -->P_TITLEID text
* -->P_KEY_FIELD text
*----------------------------------------------------------------------*
FORM FRM_SET_COND_DATA USING P_SHEETID P_TITLEID P_KEY_FIELD.
PERFORM FRM_SET_COND_OR_FOOTER TABLES IT_EXCEL_QUERY_COND USING P_SHEETID P_TITLEID P_KEY_FIELD EXCEL_COND.
ENDFORM. "frm_set_cond_data
*&---------------------------------------------------------------------*
*& Form FRM_SET_TAB_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SHEETID text
* -->P_WA_DATA text
* -->P_HJ text
* -->P_KEY_FDVALUE text
* -->P_ROWID text
*----------------------------------------------------------------------*
FORM FRM_SET_TAB_DATA USING P_SHEETID P_WA_DATA P_HJ P_KEY_FDVALUE CHANGING P_ROWID.
DATA:FRM_NEW_ROWID TYPE I,
FRM_ALIGHT,
FRM_ALIGHT_DEF,
FRM_CURRENTCELL TYPE I,
FRM_SPLIT_TYPE_P_VAL1 TYPE STRING,
FRM_SPLIT_TYPE_P_VAL2 TYPE STRING,
FRM_SPLIT_TYPE_P_VAL2_LEN TYPE I.
DATA:FRM_IT_EXCEL_TAB_DATA_TITLE LIKE IT_EXCEL_TAB_DATA_TITLE OCCURS 0 WITH HEADER LINE,
FRM_IT_EXCEL_TAB_DATA_TITLE2 LIKE IT_EXCEL_TAB_DATA_TITLE OCCURS 0 WITH HEADER LINE,
FRM_IT_EXCLE_TABLE_FIELD LIKE IT_EXCLE_TABLE_FIELD OCCURS 0 WITH HEADER LINE.
FIELD-SYMBOLS:<FRM_FIELD>.
FRM_NEW_ROWID = P_ROWID + 1 .
FRM_IT_EXCEL_TAB_DATA_TITLE[] = IT_EXCEL_TAB_DATA_TITLE[].
IF P_SHEETID IS NOT INITIAL.
DELETE FRM_IT_EXCEL_TAB_DATA_TITLE WHERE ID <> P_SHEETID.
IF FRM_IT_EXCEL_TAB_DATA_TITLE[] IS INITIAL.
FRM_IT_EXCEL_TAB_DATA_TITLE[] = IT_EXCEL_TAB_DATA_TITLE[].
DELETE FRM_IT_EXCEL_TAB_DATA_TITLE WHERE ID IS NOT INITIAL.
FRM_IT_EXCEL_TAB_DATA_TITLE2[] = FRM_IT_EXCEL_TAB_DATA_TITLE[].
DELETE FRM_IT_EXCEL_TAB_DATA_TITLE WHERE KEY_FDVALUE <> P_KEY_FDVALUE.
IF FRM_IT_EXCEL_TAB_DATA_TITLE[] IS INITIAL.
FRM_IT_EXCEL_TAB_DATA_TITLE[] = FRM_IT_EXCEL_TAB_DATA_TITLE2[].
DELETE FRM_IT_EXCEL_TAB_DATA_TITLE WHERE KEY_FDVALUE IS NOT INITIAL.
ENDIF.
ENDIF.
ENDIF.
FRM_IT_EXCLE_TABLE_FIELD[] = IT_EXCLE_TABLE_FIELD[].
SORT FRM_IT_EXCLE_TABLE_FIELD BY NAME.
SORT IT_EXCLE_TABLE_FIELD BY NAME.
SORT IT_TB_CELL_HALIGNMENT BY NAME.
LOOP AT FRM_IT_EXCEL_TAB_DATA_TITLE WHERE NAME IS NOT INITIAL.
READ TABLE FRM_IT_EXCLE_TABLE_FIELD WITH KEY NAME = FRM_IT_EXCEL_TAB_DATA_TITLE-NAME BINARY SEARCH.
IF SY-SUBRC NE 0.
MESSAGE S001(00) WITH '字段' FRM_IT_EXCEL_TAB_DATA_TITLE-NAME '不存在' DISPLAY LIKE 'E'.
STOP.
ENDIF.
ADD 1 TO FRM_CURRENTCELL.
ASSIGN COMPONENT FRM_IT_EXCEL_TAB_DATA_TITLE-NAME OF STRUCTURE P_WA_DATA TO <FRM_FIELD>.
CLEAR IT_EXCLE_TABLE_FIELD.
READ TABLE IT_EXCLE_TABLE_FIELD WITH KEY NAME = FRM_IT_EXCEL_TAB_DATA_TITLE-NAME BINARY SEARCH.
CLEAR FRM_ALIGHT_DEF.
READ TABLE IT_TB_CELL_HALIGNMENT WITH KEY NAME = FRM_IT_EXCEL_TAB_DATA_TITLE-NAME BINARY SEARCH.
IF SY-SUBRC EQ 0.
FRM_ALIGHT = IT_TB_CELL_HALIGNMENT-ALIGHT.
FRM_ALIGHT_DEF = FRM_ALIGHT.
ELSE.
CASE IT_EXCLE_TABLE_FIELD-TYPE_KIND.
WHEN 'C'.
FRM_ALIGHT = 1.
WHEN 'D'.
FRM_ALIGHT = 1.
WHEN 'P' OR 'I'.
FRM_ALIGHT = 4.
WHEN OTHERS.
FRM_ALIGHT = 3.
ENDCASE.
ENDIF.
IF <FRM_FIELD> IS INITIAL.
CASE IT_EXCLE_TABLE_FIELD-TYPE_KIND.
WHEN 'N'.
ASSIGN '' TO <FRM_FIELD>.
ENDCASE.
ENDIF.
IF FRM_IT_EXCEL_TAB_DATA_TITLE-NO_ZERO IS NOT INITIAL.
IF <FRM_FIELD> IS INITIAL.
ASSIGN '' TO <FRM_FIELD>.
ELSE.
CLEAR IT_EXCLE_TABLE_FIELD.
READ TABLE IT_EXCLE_TABLE_FIELD WITH KEY NAME = FRM_IT_EXCEL_TAB_DATA_TITLE-NAME.
CASE IT_EXCLE_TABLE_FIELD-TYPE_KIND.
WHEN 'C'.
SHIFT <FRM_FIELD> LEFT DELETING LEADING '0'.
WHEN 'P'.
FRM_SPLIT_TYPE_P_VAL1 = <FRM_FIELD>.
SPLIT FRM_SPLIT_TYPE_P_VAL1 AT '.' INTO FRM_SPLIT_TYPE_P_VAL1 FRM_SPLIT_TYPE_P_VAL2.
CONDENSE FRM_SPLIT_TYPE_P_VAL2 NO-GAPS.
FRM_SPLIT_TYPE_P_VAL2_LEN = STRLEN( FRM_SPLIT_TYPE_P_VAL2 ).
DO.
IF FRM_SPLIT_TYPE_P_VAL2_LEN EQ 0.
EXIT.
ENDIF.
FRM_SPLIT_TYPE_P_VAL2_LEN = FRM_SPLIT_TYPE_P_VAL2_LEN - 1.
IF FRM_SPLIT_TYPE_P_VAL2+FRM_SPLIT_TYPE_P_VAL2_LEN(1) EQ '0'.
FRM_SPLIT_TYPE_P_VAL2 = FRM_SPLIT_TYPE_P_VAL2(FRM_SPLIT_TYPE_P_VAL2_LEN).
FRM_SPLIT_TYPE_P_VAL2_LEN = STRLEN( FRM_SPLIT_TYPE_P_VAL2 ).
ELSE.
EXIT.
ENDIF.
ENDDO.
IF FRM_SPLIT_TYPE_P_VAL2 IS NOT INITIAL.
CONCATENATE FRM_SPLIT_TYPE_P_VAL1 '.' FRM_SPLIT_TYPE_P_VAL2 INTO FRM_SPLIT_TYPE_P_VAL1.
CONDENSE FRM_SPLIT_TYPE_P_VAL1 NO-GAPS.
ENDIF.
ASSIGN FRM_SPLIT_TYPE_P_VAL1 TO <FRM_FIELD>.
ENDCASE.
ENDIF.
ENDIF.
"合計行資料
IF P_HJ IS NOT INITIAL.
IF FRM_CURRENTCELL = 1.
ASSIGN '合計' TO <FRM_FIELD>.
ELSE.
IF FRM_IT_EXCEL_TAB_DATA_TITLE-TEXT CA '價' OR FRM_IT_EXCEL_TAB_DATA_TITLE-TEXT CS '折扣'.
ASSIGN '' TO <FRM_FIELD>.
CLEAR FRM_IT_EXCEL_TAB_DATA_TITLE-FORMAT.
IT_EXCLE_TABLE_FIELD-TYPE_KIND = 'C'.
FRM_ALIGHT = 1.
ENDIF.
ENDIF.
ENDIF.
PERFORM DOWN USING FRM_NEW_ROWID FRM_CURRENTCELL <FRM_FIELD> FRM_ALIGHT FRM_IT_EXCEL_TAB_DATA_TITLE-FORMAT IT_EXCLE_TABLE_FIELD-TYPE_KIND.
IF FRM_ALIGHT_DEF IS NOT INITIAL.
SET PROPERTY OF L_CELL 'HorizontalAlignment' = FRM_ALIGHT_DEF NO FLUSH .
PERFORM FRM_ERR_HDL.
ENDIF.
"設定高度
IF IT_EXCLE_TABLE_FIELD-TYPE_KIND = 'C'.
PERFORM FRM_EXCEL_SET_ROW_HEIGHT USING FRM_IT_EXCEL_TAB_DATA_TITLE-LEN <FRM_FIELD>.
ENDIF.
ENDLOOP.
P_ROWID = FRM_NEW_ROWID.
ENDFORM. "FRM_SET_TAB_DATA
*&---------------------------------------------------------------------*
*& Form FRM_EXCEL_SET_ROW_HEIGHT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_CELL_HEIGHT text
* -->P_VALUE text
*----------------------------------------------------------------------*
FORM FRM_EXCEL_SET_ROW_HEIGHT USING P_CELL_HEIGHT
P_VALUE.
DATA:FRM_LEN TYPE I,
FRM_MIN_ZS TYPE P DECIMALS 4,
FRM_VALUE TYPE STRING.
FRM_VALUE = P_VALUE.
CONDENSE FRM_VALUE NO-GAPS.
FRM_LEN = CL_ABAP_LIST_UTILITIES=>DYNAMIC_OUTPUT_LENGTH( FRM_VALUE ). "包含中文字元串
"FRM_LEN = FRM_LEN .
FRM_MIN_ZS = CEIL( FRM_LEN / P_CELL_HEIGHT ).
IF FRM_MIN_ZS > 1.
FRM_LEN = FRM_MIN_ZS * 12.
SET PROPERTY OF L_CELL 'ROWHEIGHT' = FRM_LEN NO FLUSH.
PERFORM FRM_ERR_HDL.
ENDIF.
ENDFORM. "FRM_EXCEL_SET_ROW_HEIGHT
*&---------------------------------------------------------------------*
*& Form frm_get_itab_title
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_TITLE text
* -->P_TITLEID text
* -->P_KEY_FIELD text
*----------------------------------------------------------------------*
FORM FRM_GET_ITAB_TITLE TABLES P_IT_TITLE STRUCTURE ZST_EXP_EXCEL_TAB_FD_TITLE
USING P_TITLEID P_KEY_FIELD.
DATA:FRM_IT_TITLE LIKE ZST_EXP_EXCEL_TAB_FD_TITLE OCCURS 0 WITH HEADER LINE.
FRM_IT_TITLE[] = IT_EXCEL_TAB_DATA_TITLE[].
DELETE FRM_IT_TITLE WHERE ID <> P_TITLEID.
IF FRM_IT_TITLE[] IS INITIAL.
FRM_IT_TITLE[] = IT_EXCEL_TAB_DATA_TITLE[].
DELETE FRM_IT_TITLE WHERE ID IS NOT INITIAL.
DELETE FRM_IT_TITLE WHERE KEY_FDVALUE <> P_KEY_FIELD.
IF FRM_IT_TITLE[] IS INITIAL.
FRM_IT_TITLE[] = IT_EXCEL_TAB_DATA_TITLE[].
DELETE FRM_IT_TITLE WHERE ID IS NOT INITIAL OR KEY_FDVALUE IS NOT INITIAL.
ENDIF.
ENDIF.
P_IT_TITLE[] = FRM_IT_TITLE[].
ENDFORM. "frm_get_itab_title
*&---------------------------------------------------------------------*
*& Form FRM_SET_TAB_DATA_TITLE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SHEETID text
* -->P_TITLEID text
* -->P_KEY_FIELD text
*----------------------------------------------------------------------*
FORM FRM_SET_TAB_DATA_TITLE USING P_SHEETID P_TITLEID P_KEY_FIELD.
DATA:FRM_BG_ROWID_FIRST(6),
FRM_BG_ROWID(6),
FRM_ED_ROWID(6),
FRM_ROWID TYPE I,
FRM_BG_COLNAME(8),FRM_ED_COLNAME(8),
FRM_CELLNAME_TMP LIKE FRM_BG_COLNAME,
FRM_CELLID TYPE I,
FRM_CURROWID TYPE I,
FRM_MAX_TITLE_ROWID TYPE I,
FRM_IT_TITLE LIKE IT_EXCEL_TAB_DATA_TITLE OCCURS 0 WITH HEADER LINE,
FRM_IT_TITLE2 LIKE IT_EXCEL_TAB_DATA_TITLE OCCURS 0 WITH HEADER LINE.
PERFORM FRM_GET_CURROW CHANGING FRM_ROWID.
FRM_IT_TITLE[] = IT_EXCEL_TAB_DATA_TITLE[].
DELETE FRM_IT_TITLE WHERE ID <> P_TITLEID.
IF FRM_IT_TITLE[] IS INITIAL.
FRM_IT_TITLE[] = IT_EXCEL_TAB_DATA_TITLE[].
DELETE FRM_IT_TITLE WHERE ID IS NOT INITIAL.
FRM_IT_TITLE2[] = FRM_IT_TITLE[].
DELETE FRM_IT_TITLE WHERE KEY_FDVALUE <> P_KEY_FIELD.
IF FRM_IT_TITLE[] IS INITIAL.
FRM_IT_TITLE[] = FRM_IT_TITLE2[].
DELETE FRM_IT_TITLE WHERE KEY_FDVALUE IS NOT INITIAL.
ENDIF.
ENDIF.
LOOP AT FRM_IT_TITLE.
FRM_BG_ROWID = FRM_IT_TITLE-BG_ROW + FRM_ROWID.
CONDENSE FRM_BG_ROWID NO-GAPS.
AT FIRST.
FRM_BG_ROWID_FIRST = FRM_BG_ROWID.
ENDAT.
CONCATENATE FRM_IT_TITLE-BG_COLNAME FRM_BG_ROWID INTO FRM_BG_COLNAME.
CONDENSE FRM_BG_COLNAME NO-GAPS.
FRM_ED_ROWID = FRM_IT_TITLE-ED_ROW + FRM_ROWID.
CONDENSE FRM_ED_ROWID NO-GAPS.
CONCATENATE FRM_IT_TITLE-ED_COLNAME FRM_ED_ROWID INTO FRM_ED_COLNAME.
CONDENSE FRM_ED_COLNAME NO-GAPS.
PERFORM FRM_MERGECELLS USING FRM_BG_COLNAME FRM_ED_COLNAME. "合并列
"檢查開始列名是否改變,每次将開始列名儲存 FRM_CELLNAME_TMP用于跟下一次循環的開始列名比較,
"不同的話,列号加1,否則不加
IF FRM_CELLNAME_TMP <> FRM_IT_TITLE-BG_COLNAME.
ADD 1 TO FRM_CELLID.
FRM_CELLNAME_TMP = FRM_IT_TITLE-BG_COLNAME.
"設定列寬
CALL METHOD OF L_SHEET 'Columns' = L_COLUMN NO FLUSH
EXPORTING
#1 = FRM_CELLID .
SET PROPERTY OF L_COLUMN 'ColumnWidth' = FRM_IT_TITLE-LEN NO FLUSH.
ENDIF.
PERFORM DOWN USING FRM_BG_ROWID FRM_CELLID FRM_IT_TITLE-TEXT 3 FRM_IT_TITLE-FORMAT 'C'.
GET PROPERTY OF L_CELL 'Font' = L_FONT NO FLUSH .
SET PROPERTY OF L_FONT 'Bold' = EXCEL_TB_CELL_TITLE_FONT_BOLD NO FLUSH .
SET PROPERTY OF L_CELL 'ROWHEIGHT' = 26 NO FLUSH.
"保字段的顯示位置格式
PERFORM FRM_EXCEL_SET_TB_CELL_HALIGHT USING FRM_IT_TITLE-NAME FRM_IT_TITLE-ALIGHT.
"儲存最大的行号
FRM_CURROWID = FRM_IT_TITLE-ED_ROW + FRM_ROWID.
IF FRM_MAX_TITLE_ROWID < FRM_CURROWID.
FRM_MAX_TITLE_ROWID = FRM_CURROWID.
ENDIF.
ENDLOOP.
FRM_ED_ROWID = FRM_MAX_TITLE_ROWID.
PERFORM FRM_EXCLE_SAVE_ROW USING P_SHEETID EXCEL_TBTIL FRM_BG_ROWID_FIRST FRM_ED_ROWID P_KEY_FIELD. "儲存目前的行号
ENDFORM. "frm_set_tab_data_title
*&---------------------------------------------------------------------*
*& Form FRM_EXCEL_SET_TB_CELL_HALIGHT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_NAME text
* -->P_ALIGHT text
*----------------------------------------------------------------------*
FORM FRM_EXCEL_SET_TB_CELL_HALIGHT USING P_NAME P_ALIGHT.
CLEAR IT_TB_CELL_HALIGNMENT.
IF P_NAME IS INITIAL OR P_ALIGHT IS INITIAL.
RETURN.
ENDIF.
IT_TB_CELL_HALIGNMENT-NAME = P_NAME.
CASE P_ALIGHT.
WHEN 'L'.
IT_TB_CELL_HALIGNMENT-ALIGHT = 2.
WHEN 'C'.
IT_TB_CELL_HALIGNMENT-ALIGHT = 3.
WHEN 'R'.
IT_TB_CELL_HALIGNMENT-ALIGHT = 4.
WHEN OTHERS.
IT_TB_CELL_HALIGNMENT-ALIGHT = 2.
ENDCASE.
APPEND IT_TB_CELL_HALIGNMENT.
ENDFORM. "FRM_EXCEL_SET_TB_CELL_HALIGHT
*&---------------------------------------------------------------------*
*& Form DOWN
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->X text
* -->Y text
* -->I_VALUE text
* -->I_ALIGN text
* -->FORMART text
*----------------------------------------------------------------------*
FORM DOWN USING X Y I_VALUE I_ALIGN FORMART TYPE.
DATA:FRM_STRING TYPE STRING,
FRM_IT_POS_ROW TYPE I,
FRM_IT_POS TYPE STRING OCCURS 0 WITH HEADER LINE.
DATA:FRM_VALUE TYPE STRING,
FRM_VALUE_LEN TYPE I,
FRM_VALUE_FORMART TYPE STRING,
FRM_IN_CHINESE.
CALL METHOD OF L_SHEET 'CELLS' = L_CELL NO FLUSH
EXPORTING
#1 = X
#2 = Y.
SET PROPERTY OF L_CELL 'HorizontalAlignment' = I_ALIGN NO FLUSH .
PERFORM FRM_ERR_HDL.
"設定值
CASE TYPE.
WHEN 'D'.
IF I_VALUE IS INITIAL.
" FRM_VALUE = '0000/00/00'.
ELSE.
CONCATENATE I_VALUE(4) '/' I_VALUE+4(2) '/' I_VALUE+6(2) INTO FRM_VALUE.
CONDENSE FRM_VALUE NO-GAPS.
ENDIF.
WHEN 'T'.
IF I_VALUE IS INITIAL.
" FRM_VALUE = '0000/00/00'.
ELSE.
CONCATENATE I_VALUE(2) ':' I_VALUE+2(2) ':' I_VALUE+4(2) INTO FRM_VALUE.
CONDENSE FRM_VALUE NO-GAPS.
ENDIF.
WHEN 'P'.
PERFORM FRM_CHANGE_SIGN USING I_VALUE CHANGING FRM_VALUE.
IF FORMART CS '%'.
FRM_VALUE = FRM_VALUE / 100.
ENDIF.
WHEN OTHERS.
FRM_VALUE = I_VALUE.
ENDCASE.
SET PROPERTY OF L_CELL 'VALUE' = FRM_VALUE NO FLUSH.
"取目前單元格設定文本格式
CALL METHOD OF L_SHEET 'Columns' = L_COLUMN NO FLUSH
EXPORTING
#1 = Y.
PERFORM FRM_ERR_HDL.
CASE I_ALIGN.
WHEN 4.
FRM_STRING = I_VALUE.
SPLIT FRM_STRING AT '.' INTO TABLE FRM_IT_POS.
FRM_IT_POS_ROW = LINES( FRM_IT_POS ).
IF FRM_IT_POS_ROW = 1.
"SET PROPERTY OF L_COLUMN 'NumberFormatLocal' = '0' NO FLUSH.
SET PROPERTY OF L_CELL 'NumberFormatLocal' = '0' NO FLUSH.
ELSE.
" SET PROPERTY OF L_COLUMN 'NumberFormatLocal' = '0.00' NO FLUSH.
SET PROPERTY OF L_CELL 'NumberFormatLocal' = '0.00' NO FLUSH.
ENDIF.
PERFORM FRM_ERR_HDL.
WHEN OTHERS.
CASE TYPE.
WHEN 'D'.
SET PROPERTY OF L_CELL 'NumberFormatLocal' = 'YYYY/MM/DD' NO FLUSH.
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_CELL 'HorizontalAlignment' = '3' NO FLUSH .
PERFORM FRM_ERR_HDL.
WHEN 'T'.
SET PROPERTY OF L_CELL 'NumberFormatLocal' = 'hh:mm:ss' NO FLUSH.
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_CELL 'HorizontalAlignment' = '3' NO FLUSH .
PERFORM FRM_ERR_HDL.
WHEN OTHERS.
"檢查是否包含中文字元
FRM_VALUE = I_VALUE.
CONDENSE FRM_VALUE NO-GAPS.
CALL FUNCTION 'ZF_CHECK_STR_NO_IN_CHINESE'
EXPORTING
STR = FRM_VALUE
IMPORTING
RET_IN = FRM_IN_CHINESE.
"SET PROPERTY OF L_COLUMN 'NumberFormatLocal' = '@' NO FLUSH.
IF FRM_IN_CHINESE IS INITIAL.
FRM_VALUE_LEN = STRLEN( FRM_VALUE ).
CLEAR FRM_VALUE_FORMART.
DO FRM_VALUE_LEN TIMES.
CONCATENATE FRM_VALUE_FORMART '0' INTO FRM_VALUE_FORMART.
ENDDO.
CONDENSE FRM_VALUE_FORMART NO-GAPS.
SET PROPERTY OF L_CELL 'NumberFormatLocal' = FRM_VALUE_FORMART NO FLUSH.
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_CELL 'HorizontalAlignment' = '2' NO FLUSH .
PERFORM FRM_ERR_HDL.
"超過15位長度,前面加逗号 add begin by wkb 2016-09-06 DEVK931616
IF FRM_VALUE_LEN > 15.
"CONCATENATE '''' FRM_VALUE INTO FRM_VALUE.
"CONDENSE FRM_VALUE NO-GAPS.
FRM_VALUE_FORMART = '@'.
SET PROPERTY OF L_CELL 'NumberFormatLocal' = FRM_VALUE_FORMART NO FLUSH.
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_CELL 'VALUE' = FRM_VALUE NO FLUSH.
ENDIF.
"超過15位長度,前面加逗号 add end by wkb 2016-09-06 DEVK931616
ELSE.
SET PROPERTY OF L_CELL 'NumberFormatLocal' = '@' NO FLUSH.
PERFORM FRM_ERR_HDL.
ENDIF.
ENDCASE.
ENDCASE.
PERFORM FRM_ERR_HDL.
IF FORMART IS NOT INITIAL.
SET PROPERTY OF L_CELL 'NumberFormatLocal' = FORMART NO FLUSH.
ENDIF.
ENDFORM. "DOWN
*&---------------------------------------------------------------------*
*& Form FRM_CHANGE_SIGN
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_VALUE1 text
* -->P_VALUE2 text
*----------------------------------------------------------------------*
FORM FRM_CHANGE_SIGN USING P_VALUE1 CHANGING P_VALUE2.
IF P_VALUE1 < 0.
P_VALUE2 = ABS( P_VALUE1 ).
CONCATENATE '-' P_VALUE2 INTO P_VALUE2.
CONDENSE P_VALUE2 NO-GAPS.
ELSE.
P_VALUE2 = ABS( P_VALUE1 ).
ENDIF.
ENDFORM. "FRM_CHANGE_SIGN
*&---------------------------------------------------------------------*
*& Form FRM_SET_FOOTER_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SHEETID text
* -->P_TITLEID text
* -->P_KEY_FIELD text
*----------------------------------------------------------------------*
FORM FRM_SET_FOOTER_DATA USING P_SHEETID P_TITLEID P_KEY_FIELD.
PERFORM FRM_SET_COND_OR_FOOTER TABLES IT_EXCEL_FOOTER USING P_SHEETID P_TITLEID P_KEY_FIELD EXCEL_FOOT.
ENDFORM. "frm_set_footer_data
*&---------------------------------------------------------------------*
*& Form FRM_SET_COND_OR_FOOTER
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_DATA text
* -->P_ID text
* -->P_TITLEID text
* -->P_KEY_FIELD text
* -->P_TYPE text
*----------------------------------------------------------------------*
FORM FRM_SET_COND_OR_FOOTER TABLES P_IT_DATA STRUCTURE ZST_EXP_EXCEL_FILE_COND
USING P_ID P_TITLEID P_KEY_FIELD P_TYPE.
DATA:FRM_ROWID(3),
FRM_BORDER,
FRM_TYPEID TYPE N LENGTH 2,
FRM_DATATYPE LIKE EXCEL_TITLE1.
DATA:FRM_BG_COLNAME(5),FRM_ED_COLNAME(5).
DATA:FRM_BG_ROWID TYPE I,FRM_ED_ROWID TYPE I.
DATA:FRM_ROWID_TMP TYPE I.
DATA:FRM_IT_DATA LIKE ZST_EXP_EXCEL_FILE_COND OCCURS 0 WITH HEADER LINE,
FRM_WA_DATA LIKE ZST_EXP_EXCEL_FILE_COND.
DATA:BEGIN OF FRM_IT_BRORDER_ROW OCCURS 0,
BORDER LIKE ZST_EXP_EXCEL_FILE_COND-BORDER,
TYPE LIKE EXCEL_TITLE1,
BG_ROWID TYPE I,
ED_ROWID TYPE I,
END OF FRM_IT_BRORDER_ROW,
FRM_WA_BRORDER_ROW LIKE LINE OF FRM_IT_BRORDER_ROW.
IF P_IT_DATA[] IS INITIAL.
RETURN.
ENDIF.
PERFORM FRM_GET_CURROW CHANGING FRM_ROWID_TMP.
"過濾ID号
FRM_IT_DATA[] = P_IT_DATA[].
DELETE FRM_IT_DATA WHERE ID <> P_TITLEID.
IF FRM_IT_DATA[] IS INITIAL.
FRM_IT_DATA[] = P_IT_DATA[].
DELETE FRM_IT_DATA WHERE ID IS NOT INITIAL.
"過濾關鍵字段
IF P_KEY_FIELD IS NOT INITIAL.
DELETE FRM_IT_DATA WHERE KEY_FDVALUE <> P_KEY_FIELD.
IF FRM_IT_DATA[] IS INITIAL.
FRM_IT_DATA[] = P_IT_DATA[].
DELETE FRM_IT_DATA WHERE ID IS NOT INITIAL OR KEY_FDVALUE IS NOT INITIAL.
ENDIF.
ENDIF.
ENDIF.
CLEAR FRM_TYPEID.
CLEAR FRM_IT_BRORDER_ROW[].
SORT FRM_IT_DATA BY ROWID.
LOOP AT FRM_IT_DATA.
FRM_BORDER = FRM_IT_DATA-BORDER.
FRM_ROWID = FRM_IT_DATA-ROWID + FRM_ROWID_TMP.
CONDENSE FRM_ROWID NO-GAPS.
AT FIRST.
IF FRM_BORDER IS NOT INITIAL. "第一次執行,如果要求加邊框而且是腳資料,FRM_ROWID_TMP跳一個空行.
IF P_TYPE EQ EXCEL_FOOT.
ADD 1 TO FRM_ROWID_TMP.
ADD 1 TO FRM_ROWID.
ENDIF.
ENDIF.
FRM_BG_ROWID = FRM_ROWID. "記錄行号
ENDAT.
CONCATENATE FRM_IT_DATA-BG_COLNAME FRM_ROWID INTO FRM_BG_COLNAME.
CONDENSE FRM_BG_COLNAME NO-GAPS.
CONCATENATE FRM_IT_DATA-ED_COLNAME FRM_ROWID INTO FRM_ED_COLNAME.
CONDENSE FRM_ED_COLNAME NO-GAPS.
PERFORM FRM_MERGECELLS USING FRM_BG_COLNAME FRM_ED_COLNAME. "合并列
CASE FRM_IT_DATA-ALIGHT.
WHEN 'C'.
FRM_IT_DATA-ALIGHT = 3.
WHEN 'L'.
FRM_IT_DATA-ALIGHT = 2.
WHEN 'R'.
FRM_IT_DATA-ALIGHT = 4.
WHEN OTHERS.
FRM_IT_DATA-ALIGHT = 2.
ENDCASE.
GET PROPERTY OF L_RANGE 'Font' = L_FONT NO FLUSH .
PERFORM FRM_ERR_HDL.
IF FRM_IT_DATA-BOLD IS INITIAL.
SET PROPERTY OF L_FONT 'Bold' = 0 NO FLUSH .
ELSE.
SET PROPERTY OF L_FONT 'Bold' = 1 NO FLUSH .
ENDIF.
PERFORM FRM_ERR_HDL.
IF FRM_IT_DATA-ROWHEIGHT > 0.
SET PROPERTY OF L_RANGE 'ROWHEIGHT' = FRM_IT_DATA-ROWHEIGHT NO FLUSH.
ENDIF.
SET PROPERTY OF L_RANGE 'Value' = FRM_IT_DATA-TEXT NO FLUSH .
PERFORM FRM_ERR_HDL.
PERFORM FRM_SET_CELL_FORMAT USING FRM_IT_DATA-BG_COLNAME FRM_ROWID FRM_IT_DATA-TEXT. "設定格式類型
SET PROPERTY OF L_RANGE 'HorizontalAlignment' = FRM_IT_DATA-ALIGHT NO FLUSH .
PERFORM FRM_ERR_HDL.
"類型轉換
IF FRM_IT_DATA-BORDER IS NOT INITIAL.
ADD 1 TO FRM_TYPEID.
IF P_TYPE EQ EXCEL_COND.
CONCATENATE EXCEL_CONDBD FRM_TYPEID INTO FRM_DATATYPE.
ELSE.
CONCATENATE EXCEL_FOOTBD FRM_TYPEID INTO FRM_DATATYPE.
ENDIF.
CONDENSE FRM_DATATYPE NO-GAPS.
ELSE.
FRM_DATATYPE = P_TYPE.
ENDIF.
CLEAR FRM_IT_BRORDER_ROW.
FRM_IT_BRORDER_ROW-BORDER = FRM_IT_DATA-BORDER.
FRM_IT_BRORDER_ROW-TYPE = FRM_DATATYPE.
FRM_IT_BRORDER_ROW-BG_ROWID = FRM_ROWID.
FRM_IT_BRORDER_ROW-ED_ROWID = FRM_ROWID.
APPEND FRM_IT_BRORDER_ROW.
ENDLOOP.
"儲存行号,以及可能需要加邊框的處理
LOOP AT FRM_IT_BRORDER_ROW.
MOVE-CORRESPONDING FRM_IT_BRORDER_ROW TO FRM_WA_BRORDER_ROW.
AT NEW BORDER.
"儲存目前的行号
PERFORM FRM_EXCLE_SAVE_ROW USING P_ID
FRM_WA_BRORDER_ROW-TYPE
FRM_WA_BRORDER_ROW-BG_ROWID
FRM_WA_BRORDER_ROW-ED_ROWID
P_KEY_FIELD.
ENDAT.
AT END OF BORDER.
"儲存目前的行号
PERFORM FRM_EXCLE_SAVE_ROW USING P_ID
FRM_WA_BRORDER_ROW-TYPE
FRM_WA_BRORDER_ROW-BG_ROWID
FRM_WA_BRORDER_ROW-ED_ROWID
P_KEY_FIELD.
ENDAT.
ENDLOOP.
ENDFORM. "frm_set_cond_or_footer
*&---------------------------------------------------------------------*
*& Form FRM_SET_CELL_FORMAT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_COLNAME text
* -->P_ROWID text
* -->P_TEXT text
*----------------------------------------------------------------------*
FORM FRM_SET_CELL_FORMAT USING P_COLNAME P_ROWID P_TEXT.
DATA:FRM_CELLID(2),
FRM_TEXT_STR TYPE STRING,
FRM_TEXT_NUM TYPE N LENGTH 10,
FRM_TEXT_LEN TYPE I,
FRM_TEXT_FORMAT TYPE STRING.
DATA:FRM_OBJ_CELL TYPE OLE2_OBJECT.
FRM_TEXT_LEN = STRLEN( P_TEXT ).
IF P_TEXT(1) = '0'.
CLEAR FRM_TEXT_FORMAT.
DO FRM_TEXT_LEN TIMES.
CONCATENATE FRM_TEXT_FORMAT '0' INTO FRM_TEXT_FORMAT.
CONDENSE FRM_TEXT_FORMAT NO-GAPS.
ENDDO.
ELSE.
IF P_TEXT+4(1) EQ '-' AND P_TEXT+7(1) EQ '-' .
FRM_TEXT_FORMAT = 'yyyy-mm-dd'.
ENDIF.
ENDIF.
IF FRM_TEXT_FORMAT IS INITIAL.
RETURN.
ENDIF.
SET PROPERTY OF L_RANGE 'NumberFormatLocal' = FRM_TEXT_FORMAT NO FLUSH.
PERFORM FRM_ERR_HDL.
ENDFORM. "FRM_SET_CELL_FORMAT
*&---------------------------------------------------------------------*
*& Form FRM_MERGECELLS
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_CELL1 text
* -->P_CELL2 text
*----------------------------------------------------------------------*
FORM FRM_MERGECELLS USING P_CELL1 P_CELL2.
CONDENSE P_CELL1 NO-GAPS.
CONDENSE P_CELL2 NO-GAPS.
CALL METHOD OF L_SHEET 'Range' = L_RANGE NO FLUSH
EXPORTING
#1 = P_CELL1
#2 = P_CELL2.
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_RANGE 'MergeCells' = 1 NO FLUSH .
PERFORM FRM_ERR_HDL.
ENDFORM. "FRM_MERGECELLS
*&---------------------------------------------------------------------*
*& Form FRM_KSDC_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_COND text
* -->P_IT_TAB_FIELD text
* -->P_IT_FOOTER text
*----------------------------------------------------------------------*
FORM FRM_KSDC_EXCEL TABLES P_IT_COND STRUCTURE ZST_EXP_EXCEL_FILE_COND
P_IT_ITEM STRUCTURE ZST_EXP_EXCEL_TAB_FD_TITLE
P_IT_FOOTER STRUCTURE ZST_EXP_EXCEL_FILE_COND.
DATA:FRM_FILENAME TYPE STRING.
DATA:FRM_IT_DOWNLOAD TYPE STRING OCCURS 0 WITH HEADER LINE.
FIELD-SYMBOLS:<FRM_ITAB> TYPE STANDARD TABLE,
<FRM_WA>,<FRM_KEYVALUE>.
PERFORM FRM_GET_SAVE_FILENAME CHANGING FRM_FILENAME. "取檔案
IF FRM_FILENAME IS INITIAL.
RETURN.
ENDIF.
IF P_IT_ITEM[] IS INITIAL.
LOOP AT IT_EXCLE_TABLE_FIELD.
CLEAR P_IT_ITEM.
P_IT_ITEM-NAME = IT_EXCLE_TABLE_FIELD-NAME.
P_IT_ITEM-TEXT = IT_EXCLE_TABLE_FIELD-NAME.
APPEND P_IT_ITEM.
ENDLOOP.
ENDIF.
SORT IT_EXCEL_DATA BY ID KEY_FIELD.
CLEAR FRM_IT_DOWNLOAD[].
LOOP AT IT_EXCEL_DATA.
ASSIGN IT_EXCEL_DATA-DYN_ITAB->* TO <FRM_ITAB>.
CLEAR FRM_IT_DOWNLOAD.
LOOP AT <FRM_ITAB> ASSIGNING <FRM_WA>.
ASSIGN COMPONENT ITAB_DATA_FJ_FDNAME OF STRUCTURE <FRM_WA> TO <FRM_KEYVALUE>.
AT NEW (ITAB_DATA_FJ_FDNAME).
"表頭
PERFORM FRM_GET_KSDC_FILE_TITLE TABLES FRM_IT_DOWNLOAD USING IT_EXCEL_DATA <FRM_KEYVALUE>.
"條件
PERFORM FRM_GET_CON_AND_FOOTER_DATA TABLES P_IT_COND FRM_IT_DOWNLOAD USING IT_EXCEL_DATA-ID <FRM_KEYVALUE>.
"表格擡頭描述
PERFORM FRM_GET_ITAB_TITLE_DATA TABLES P_IT_ITEM FRM_IT_DOWNLOAD USING IT_EXCEL_DATA-ID <FRM_KEYVALUE>.
ENDAT.
"表格資料
PERFORM FRM_GET_ITAB_ITEM_DATA TABLES P_IT_ITEM FRM_IT_DOWNLOAD USING <FRM_WA> '' IT_EXCEL_DATA-ID <FRM_KEYVALUE>.
AT END OF (ITAB_DATA_FJ_FDNAME).
"合計行資料
PERFORM FRM_GET_TAB_HJ_DATA TABLES P_IT_ITEM FRM_IT_DOWNLOAD
USING IT_EXCEL_DATA-DYN_ITAB_HJ IT_EXCEL_DATA-ID <FRM_KEYVALUE>.
"表腳資料
PERFORM FRM_GET_CON_AND_FOOTER_DATA TABLES P_IT_FOOTER FRM_IT_DOWNLOAD USING IT_EXCEL_DATA-ID <FRM_KEYVALUE>.
"每個表格資料列印完後輸出指定的空行
DO FORM_ROW_PITCH TIMES.
CLEAR FRM_IT_DOWNLOAD. APPEND FRM_IT_DOWNLOAD.
ENDDO.
ENDAT.
ENDLOOP.
ENDLOOP.
PERFORM FRM_DOWNLOAD TABLES FRM_IT_DOWNLOAD USING FRM_FILENAME.
ENDFORM. " FRM_KSDC_EXCEL
*&---------------------------------------------------------------------*
*& Form FRM_GET_KSDC_FILE_TITLE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_DATA text
* -->P_IT_DOWNLOAD text
* -->P_ID text
* -->P_KEY_FDVALUE text
*----------------------------------------------------------------------*
FORM FRM_GET_KSDC_FILE_TITLE TABLES P_IT_DOWNLOAD
USING P_WA_DATA STRUCTURE IT_EXCEL_DATA
P_KEY_FDVALUE.
DATA:FRM_IT_EXCEL_TITLE LIKE IT_EXCEL_TITLE OCCURS 0 WITH HEADER LINE.
IF P_WA_DATA-TITLE1 IS NOT INITIAL.
CLEAR FRM_IT_EXCEL_TITLE.
FRM_IT_EXCEL_TITLE-ID = P_WA_DATA-ID.
FRM_IT_EXCEL_TITLE-TYP = 'M'.
FRM_IT_EXCEL_TITLE-MS = P_WA_DATA-TITLE1.
APPEND FRM_IT_EXCEL_TITLE.
ENDIF.
IF P_WA_DATA-TITLE2 IS NOT INITIAL.
CLEAR FRM_IT_EXCEL_TITLE.
FRM_IT_EXCEL_TITLE-ID = P_WA_DATA-ID.
FRM_IT_EXCEL_TITLE-MS = P_WA_DATA-TITLE2.
APPEND FRM_IT_EXCEL_TITLE.
ENDIF.
PERFORM FRM_GET_EXCEL_FILE_TITLE TABLES FRM_IT_EXCEL_TITLE USING P_WA_DATA-ID P_KEY_FDVALUE.
SORT FRM_IT_EXCEL_TITLE BY TYP DESCENDING.
LOOP AT FRM_IT_EXCEL_TITLE.
P_IT_DOWNLOAD = FRM_IT_EXCEL_TITLE-MS. APPEND P_IT_DOWNLOAD.
ENDLOOP.
ENDFORM. "FRM_GET_KSDC_FILE_TITLE
*&---------------------------------------------------------------------*
*& Form FRM_get_TAB_HJ_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_ITEM text
* -->P_IT_DOWNLOAD text
* -->P_DYN_DATA_HJ text
* -->P_KEY_FIELD text
*----------------------------------------------------------------------*
FORM FRM_GET_TAB_HJ_DATA TABLES P_IT_ITEM P_IT_DOWNLOAD
USING P_DYN_DATA_HJ TYPE REF TO DATA P_ID P_KEY_FIELD.
DATA:FRM_CURROWID TYPE I.
FIELD-SYMBOLS:<FRM_TABLE> TYPE STANDARD TABLE,
<FRM_WA>,<FRM_FIELD>.
ASSIGN P_DYN_DATA_HJ->* TO <FRM_TABLE>.
IF <FRM_TABLE>[] IS INITIAL.
RETURN.
ENDIF.
READ TABLE <FRM_TABLE> ASSIGNING <FRM_WA> WITH KEY (ITAB_DATA_FJ_FDNAME) = P_KEY_FIELD.
IF SY-SUBRC NE 0.
RETURN.
ENDIF.
PERFORM FRM_GET_ITAB_ITEM_DATA TABLES P_IT_ITEM P_IT_DOWNLOAD USING <FRM_WA> 'X' P_ID P_KEY_FIELD.
ENDFORM. "FRM_get_TAB_HJ_DATA
*&---------------------------------------------------------------------*
*& Form FRM_GET_ITAB_ITEM_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_ITEM text
* -->P_IT_DOWNLOAD text
* -->P_WA_DATA text
* -->P_HJ text
* -->P_ID text
* -->P_KEY_FDVALUE text
*----------------------------------------------------------------------*
FORM FRM_GET_ITAB_ITEM_DATA TABLES P_IT_ITEM STRUCTURE ZST_EXP_EXCEL_TAB_FD_TITLE
P_IT_DOWNLOAD
USING P_WA_DATA P_HJ P_ID P_KEY_FDVALUE.
DATA:FRM_TAB_CHAR VALUE CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB."取跳格鍵
DATA:FRM_TEXT TYPE STRING.
DATA:FRM_ROWID TYPE I.
DATA:FRM_IT_ITEM LIKE ZST_EXP_EXCEL_TAB_FD_TITLE OCCURS 0 WITH HEADER LINE,
FRM_IT_ITEM2 LIKE ZST_EXP_EXCEL_TAB_FD_TITLE OCCURS 0 WITH HEADER LINE,
FRM_IT_EXCLE_TABLE_FIELD LIKE IT_EXCLE_TABLE_FIELD OCCURS 0 WITH HEADER LINE.
FIELD-SYMBOLS:<FRM_FIELD>.
FRM_IT_ITEM[] = P_IT_ITEM[].
DELETE FRM_IT_ITEM WHERE ID <> P_ID.
IF FRM_IT_ITEM[] IS INITIAL.
FRM_IT_ITEM[] = P_IT_ITEM[].
DELETE FRM_IT_ITEM WHERE ID IS NOT INITIAL.
FRM_IT_ITEM2[] = FRM_IT_ITEM[].
DELETE FRM_IT_ITEM WHERE KEY_FDVALUE <> P_KEY_FDVALUE.
IF FRM_IT_ITEM[] IS INITIAL.
FRM_IT_ITEM[] = FRM_IT_ITEM2[].
DELETE FRM_IT_ITEM WHERE KEY_FDVALUE IS NOT INITIAL.
ENDIF.
ENDIF.
DELETE FRM_IT_ITEM WHERE NAME IS INITIAL.
FRM_IT_EXCLE_TABLE_FIELD[] = IT_EXCLE_TABLE_FIELD[].
SORT FRM_IT_EXCLE_TABLE_FIELD BY NAME.
CLEAR P_IT_DOWNLOAD.
LOOP AT FRM_IT_ITEM.
READ TABLE FRM_IT_EXCLE_TABLE_FIELD WITH KEY NAME = FRM_IT_ITEM-NAME BINARY SEARCH.
IF SY-SUBRC NE 0.
MESSAGE S001(00) WITH '字段' FRM_IT_ITEM-NAME '不存在' DISPLAY LIKE 'E'.
STOP.
ENDIF.
FRM_ROWID = SY-TABIX.
ASSIGN COMPONENT FRM_IT_ITEM-NAME OF STRUCTURE P_WA_DATA TO <FRM_FIELD>.
CASE FRM_IT_EXCLE_TABLE_FIELD-TYPE_KIND.
WHEN 'P'. "P類型當為負數時,負号提前 add by wkb 2014-04-11
IF <FRM_FIELD> < 0.
<FRM_FIELD> = ABS( <FRM_FIELD> ).
FRM_TEXT = <FRM_FIELD>.
CONCATENATE '-' FRM_TEXT INTO FRM_TEXT.
CONDENSE FRM_TEXT NO-GAPS.
ELSE.
FRM_TEXT = <FRM_FIELD>.
ENDIF.
WHEN 'D'. "合計行類型為初始值,置空值 add by wkb 2014-04-11
IF ( <FRM_FIELD> IS INITIAL OR <FRM_FIELD> EQ '00000000' ).
FRM_TEXT = ''.
ELSE.
FRM_TEXT = <FRM_FIELD>.
ENDIF.
WHEN 'N'. "合計行類型為初始值,置空值 add by wkb 2014-04-11
IF P_HJ EQ 'X' AND <FRM_FIELD> IS INITIAL.
FRM_TEXT = ''.
ELSE.
FRM_TEXT = <FRM_FIELD>.
ENDIF.
WHEN OTHERS.
FRM_TEXT = <FRM_FIELD>.
ENDCASE.
IF P_HJ IS NOT INITIAL. "合計行資料
IF FRM_IT_ITEM-NAME <> FRM_IT_ITEM-TEXT.
IF FRM_ROWID = 1.
FRM_TEXT = '合計'.
ELSE.
IF FRM_IT_ITEM-TEXT CA '價'.
FRM_TEXT = ''.
ENDIF.
ENDIF.
ELSE.
RETURN.
ENDIF.
ENDIF.
CONDENSE FRM_TEXT NO-GAPS.
CONCATENATE P_IT_DOWNLOAD FRM_TEXT FRM_TAB_CHAR INTO P_IT_DOWNLOAD.
CONDENSE P_IT_DOWNLOAD NO-GAPS.
ENDLOOP.
APPEND P_IT_DOWNLOAD.
ENDFORM. "frm_get_itab_item_data
*&---------------------------------------------------------------------*
*& Form FRM_GET_ITAB_TITLE_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_DATA text
* -->P_IT_DOWNLOAD text
* -->P_ID text
*----------------------------------------------------------------------*
FORM FRM_GET_ITAB_TITLE_DATA TABLES P_IT_DATA STRUCTURE ZST_EXP_EXCEL_TAB_FD_TITLE
P_IT_DOWNLOAD
USING P_ID P_KEY_FIELD.
DATA:FRM_IT_DATA LIKE ZST_EXP_EXCEL_TAB_FD_TITLE OCCURS 0 WITH HEADER LINE.
DATA:FRM_TAB_CHAR VALUE CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB."取跳格鍵
DATA:FRM_TEXT TYPE STRING.
FRM_IT_DATA[] = P_IT_DATA[].
DELETE FRM_IT_DATA WHERE ID <> P_ID.
IF FRM_IT_DATA[] IS INITIAL.
FRM_IT_DATA[] = P_IT_DATA[].
DELETE FRM_IT_DATA WHERE ID IS NOT INITIAL.
DELETE FRM_IT_DATA WHERE KEY_FDVALUE <> P_KEY_FIELD.
IF FRM_IT_DATA[] IS INITIAL..
FRM_IT_DATA[] = P_IT_DATA[].
DELETE FRM_IT_DATA WHERE ID IS NOT INITIAL OR KEY_FDVALUE IS NOT INITIAL.
ENDIF.
ENDIF.
IF FRM_IT_DATA[] IS INITIAL.
RETURN.
ENDIF.
CLEAR P_IT_DOWNLOAD.
LOOP AT FRM_IT_DATA WHERE NAME IS NOT INITIAL.
IF FRM_IT_DATA-TEXT_FJQZ IS NOT INITIAL.
CONCATENATE FRM_IT_DATA-TEXT_FJQZ FRM_IT_DATA-TEXT INTO FRM_TEXT.
ELSE.
FRM_TEXT = FRM_IT_DATA-TEXT.
ENDIF.
CONDENSE FRM_TEXT NO-GAPS.
CONCATENATE P_IT_DOWNLOAD FRM_TEXT FRM_TAB_CHAR INTO P_IT_DOWNLOAD.
CONDENSE P_IT_DOWNLOAD NO-GAPS.
ENDLOOP.
APPEND P_IT_DOWNLOAD.
ENDFORM. "FRM_GET_ITAB_TITLE_DATA
*&---------------------------------------------------------------------*
*& Form frm_get_con_and_footer_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_DATA text
* -->P_IT_DOWNLOAD text
* -->P_ID text
*----------------------------------------------------------------------*
FORM FRM_GET_CON_AND_FOOTER_DATA TABLES P_IT_DATA STRUCTURE ZST_EXP_EXCEL_FILE_COND
P_IT_DOWNLOAD
USING P_ID P_KEY_FIELD.
DATA:FRM_IT_DATA LIKE ZST_EXP_EXCEL_FILE_COND OCCURS 0 WITH HEADER LINE.
DATA:FRM_TAB_CHAR VALUE CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB."取跳格鍵
FRM_IT_DATA[] = P_IT_DATA[].
DELETE FRM_IT_DATA WHERE ID <> P_ID.
IF FRM_IT_DATA[] IS INITIAL.
FRM_IT_DATA[] = P_IT_DATA[].
DELETE FRM_IT_DATA WHERE ID IS NOT INITIAL.
DELETE FRM_IT_DATA WHERE KEY_FDVALUE <> P_KEY_FIELD.
IF FRM_IT_DATA[] IS INITIAL.
FRM_IT_DATA[] = P_IT_DATA[].
DELETE FRM_IT_DATA WHERE ID IS NOT INITIAL OR KEY_FDVALUE IS NOT INITIAL.
ENDIF.
ENDIF.
IF FRM_IT_DATA[] IS INITIAL.
RETURN.
ENDIF.
SORT FRM_IT_DATA BY ROWID.
CLEAR P_IT_DOWNLOAD.
LOOP AT FRM_IT_DATA.
CONCATENATE P_IT_DOWNLOAD FRM_IT_DATA-TEXT FRM_TAB_CHAR INTO P_IT_DOWNLOAD.
CONDENSE P_IT_DOWNLOAD NO-GAPS.
AT END OF ROWID.
APPEND P_IT_DOWNLOAD.
CLEAR P_IT_DOWNLOAD.
ENDAT.
ENDLOOP.
ENDFORM. "frm_get_con_and_footer_data
*&---------------------------------------------------------------------*
*& Form frm_download
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_TABLE text
* -->P_FILENAME text
*----------------------------------------------------------------------*
FORM FRM_DOWNLOAD TABLES P_IT_TABLE USING P_FILENAME.
DATA:FRM_FILENAME LIKE RLGRAP-FILENAME.
FRM_FILENAME = P_FILENAME.
CALL FUNCTION 'WS_DOWNLOAD'
EXPORTING
FILENAME = FRM_FILENAME
FILETYPE = 'ASC'
TABLES
DATA_TAB = P_IT_TABLE
EXCEPTIONS
FILE_OPEN_ERROR = 1
FILE_WRITE_ERROR = 2
INVALID_FILESIZE = 3
INVALID_TYPE = 4
NO_BATCH = 5
UNKNOWN_ERROR = 6
INVALID_TABLE_WIDTH = 7
GUI_REFUSE_FILETRANSFER = 8
CUSTOMER_ERROR = 9
NO_AUTHORITY = 10
OTHERS = 11.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
ENDFORM. "frm_download
三、函數相關結構: