指定格式或许快速导出的EXCEL文件函数
DATA: IT_FIELD LIKE ZST_EXP_EXCEL_FILE_COND OCCURS 0 WITH HEADER LINE.
CLEAR:IT_FIELD[],IT_FIELD.
...
IT_FIELD-NAME = 'MATNR'.
IT_FIELD-TEXT = '商品编码'.
IT_FIELD-LEN = 10
IT_FIELD-BG_ROW = 1
IT_FIELD-ED_ROW = 1.
IT_FIELD-BG_COLNAME = 'A'
IT_FIELD-ED_COLNAME = 'A'
IT_FIELD-ALIGHT = 'C'. (L,C,R)
APPENDIT_FIELD.
...
"调用函数另存文件
CALL FUNCTION 'ZF_EXP_EXCEL_FILE'
EXPORTING
TITLE = '报表名称'
BORDER = 'X'
ADD_TOTAL_ROW = 'X'
TABLES
IT_TAB_FIELD = IT_FIELD
IT_DATA = IT_DATA.
FUNCTION ZF_EXP_EXCEL_FILE.
*"----------------------------------
*"*"Local interface:
*" IMPORTING
*" REFERENCE(TITLE) TYPE CHAR256 OPTIONAL "表头描述
*" REFERENCE(BORDER) TYPE CHAR1 OPTIONAL "边框('空':无边框,否有边框)(可选)
*" REFERENCE(SPECIFY_FORMAT) TYPE CHAR1 OPTIONAL "指定格式('X'按格式导出)(可选)
*" REFERENCE(QUICK_FORMAT) TYPE CHAR1 OPTIONAL "快速保存('X'快速导出)(可选)
*" REFERENCE(ADD_TOTAL_ROW) TYPE CHAR1 OPTIONAL "导出时自动加上合计('X'自动加上)(可选)
*" TABLES
*" 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_DATA "要被导出的任何数据内表(必填)
*"----------------------------------
DATA:FRM_SELECTLIST LIKE SPOPLI OCCURS 5 WITH HEADER LINE.
DATA:FRM_ANTWORT TYPE C.
DATA:FRM_SELECT_MOD.
CLEAR FRM_SELECT_MOD.
IF IT_DATA[] IS INITIAL.
MESSAGE S001(00) WITH '没有要导出的数据'.
RETURN.
ENDIF.
IF IT_TAB_FIELD[] IS NOT INITIAL.
FRM_SELECT_MOD = 'X'.
ENDIF.
IF SPECIFY_FORMAT IS NOT INITIAL. "指定格式导出
PERFORM FRM_DC_EXCEL TABLES IT_COND IT_TAB_FIELD IT_FOOTER IT_DATA
USING TITLE BORDER ADD_TOTAL_ROW.
RETURN.
ENDIF.
IF QUICK_FORMAT IS NOT INITIAL. "快速格式导出
PERFORM FRM_KSDC_EXCEL TABLES IT_COND IT_TAB_FIELD IT_FOOTER IT_DATA
USING TITLE ADD_TOTAL_ROW.
RETURN.
ENDIF.
IF FRM_SELECT_MOD IS NOT INITIAL.
CLEAR:FRM_SELECTLIST[],FRM_SELECTLIST.
FRM_SELECTLIST-VAROPTION = '按指定格式导出'.
FRM_SELECTLIST-SELFLAG = 'X'. APPEND FRM_SELECTLIST.
CLEAR FRM_SELECTLIST.
FRM_SELECTLIST-VAROPTION = '快速导出'. APPEND FRM_SELECTLIST.
CALL FUNCTION 'POPUP_TO_DECIDE_LIST'
EXPORTING
MARK_MAX = 1
START_COL = 15
START_ROW = 3
TEXTLINE1 = '文件的格式:'
TITEL = '选择导出'
IMPORTING
ANSWER = FRM_ANTWORT
TABLES
T_SPOPLI = FRM_SELECTLIST
EXCEPTIONS
NOT_ENOUGH_ANSWERS = 1
TOO_MUCH_ANSWERS = 2
TOO_MUCH_MARKS = 3
OTHERS = 4.
CASE FRM_ANTWORT.
WHEN '1'.
PERFORM FRM_DC_EXCEL TABLES IT_COND IT_TAB_FIELD IT_FOOTER IT_DATA
USING TITLE BORDER ADD_TOTAL_ROW.
WHEN '2'.
PERFORM FRM_KSDC_EXCEL TABLES IT_COND IT_TAB_FIELD IT_FOOTER IT_DATA
USING TITLE ADD_TOTAL_ROW.
ENDCASE.
ELSE.
PERFORM FRM_KSDC_EXCEL TABLES IT_COND IT_TAB_FIELD IT_FOOTER IT_DATA
USING TITLE ADD_TOTAL_ROW.
ENDIF.
ENDFUNCTION.
"***********将以下代码写入函数INCLUDE的TOP里面***********************
FUNCTION-POOL ZFG_EXP_FILE. "MESSAGE-ID ..
* EXCEL 输出准备
DATA: EXCEL_TITLE(5) VALUE 'TITLE',
EXCEL_COND(5) VALUE 'COND',
EXCEL_TBTIL(5) VALUE 'TBTIL',
EXCEL_TDATA(5) VALUE 'TDATA',
EXCEL_FOOT(5) VALUE 'FOOT'.
DATA: EXCEL_TB_CELL_TITLE_FONT_BOLD TYPE I VALUE 1.
DATA: BEGIN OF IT_EXCEL_ROWS OCCURS 0,
ROWTYP(5), "取值:TITLE,COND,TBTIL,TDATA,FOOT
BG_ROWID TYPE I,
ED_ROWID TYPE I,
END OF IT_EXCEL_ROWS.
DATA: BEGIN OF IT_EXCEL_TAB_DATA_TITLE OCCURS 0,
BG_ROW TYPE I,
ED_ROW TYPE I,
BG_CELL(4),
ED_CELL(4),
CELL_WIDTH TYPE I,
FIELDNAME(10),
VALUE(20),
MERGCELL, "相同行合并
ALIGHT, "居中
END OF IT_EXCEL_TAB_DATA_TITLE.
DATA: BEGIN OF IT_EXCEL_CX_COND OCCURS 0,
ROWID TYPE I,
BG_CELL(4),
ED_CELL(4),
VALUE(100),
END OF IT_EXCEL_CX_COND,
IT_EXCEL_FOOTER LIKE IT_EXCEL_CX_COND OCCURS 0 WITH HEADER LINE.
DATA: FONT_NAME(20) VALUE '宋体',
FONT_SIZE TYPE I VALUE 10.
DATA: BEGIN OF IT_TB_CELL_HALIGNMENT OCCURS 0,
NAME(20),
ALIGHT TYPE I,
END OF IT_TB_CELL_HALIGNMENT.
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.
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_FORMAT TYPE OLE2_OBJECT, " RANGE OBJECT
L_BORDER TYPE OLE2_OBJECT, " BORDER OBJECT
L_EDGEBOTTOM TYPE OLE2_OBJECT, " BORDER OBJECT
L_SHEET TYPE OLE2_OBJECT, " cell object
L_PAGESETUP TYPE OLE2_OBJECT, " font object
L_INTERIOR TYPE OLE2_OBJECT,
G_CELL TYPE OLE2_OBJECT,
V_EXCEL TYPE OLE2_OBJECT.
DATA: L_EXCLE_TITLE(100).
DATA: AUTO_TOTAL,
AUTO_TOTAL_TEXT(4) VALUE '合计'.
DATA: IT_DATA_FIELD LIKE IT_EXCLE_TABLE_FIELD OCCURS 0 WITH HEADER LINE.
DATA: DYN_DATA TYPE REF TO DATA,
DYN_WA TYPE REF TO DATA.
*&---------------------------------*
*& 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_excel_set_font_name
*&---------------------------------*
* text
*----------------------------------*
* -->P_FONT_NAME text
*----------------------------------*
FORM FRM_EXCEL_SET_FONT_NAME USING P_FONT_NAME.
FONT_NAME = P_FONT_NAME.
ENDFORM. "frm_excel_set_font_name
*&---------------------------------*
*& Form FRM_EXCEL_SET_FONT_size
*&---------------------------------*
* text
*----------------------------------*
* -->P_FONT_SIZE text
*----------------------------------*
FORM FRM_EXCEL_SET_FONT_SIZE USING P_FONT_SIZE.
FONT_SIZE = P_FONT_SIZE.
ENDFORM. "FRM_EXCEL_SET_FONT_size
*&---------------------------------*
*& Form FRM_EXCLE_INIT
*&---------------------------------*
* text
*----------------------------------*
* -->P_SHEETNAME text
* -->P_FONT_NAME text
* -->P_FONT_SIZE text
*----------------------------------*
FORM FRM_EXCLE_INIT USING P_SHEETNAME.
DATA:FRM_SHEETNAME(50).
CLEAR:IT_EXCEL_TAB_DATA_TITLE[],IT_EXCEL_TAB_DATA_TITLE.
CLEAR:IT_EXCEL_ROWS[],IT_EXCEL_ROWS.
CLEAR:IT_EXCEL_CX_COND[],IT_EXCEL_CX_COND.
CLEAR:IT_EXCEL_FOOTER[],IT_EXCEL_FOOTER.
CLEAR:IT_TB_CELL_HALIGNMENT[],IT_TB_CELL_HALIGNMENT.
IF P_SHEETNAME IS INITIAL.
FRM_SHEETNAME(6) = 'sheet1'.
ELSE.
FRM_SHEETNAME(50) = P_SHEETNAME.
L_EXCLE_TITLE = FRM_SHEETNAME.
PERFORM FRM_EXCLE_SAVE_ROW USING 'TITLE' 1 1. "记录行号
ENDIF.
CREATE OBJECT L_EXCEL 'EXCEL.APPLICATION'.
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.
SET PROPERTY OF L_WINDOW 'DisplayZeros' = 0.
PERFORM FRM_ERR_HDL.
PERFORM FRM_MSG USING '正在创建工作表……'.
ENDFORM. "FRM_EXCLE_INIT
*&---------------------------------*
*& Form frm_excel_set_init_value
*&---------------------------------*
* text
*----------------------------------*
FORM FRM_EXCEL_SET_INIT_VALUE.
SET PROPERTY OF L_EXCEL 'Visible' = 1 .
PERFORM FRM_ERR_HDL.
DATA S_SHEET TYPE OLE2_OBJECT.
GET PROPERTY OF L_BOOK 'ActiveSheet' = L_SHEET NO FLUSH.
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_SHEET 'Name' = L_EXCLE_TITLE NO FLUSH.
PERFORM FRM_ERR_HDL.
" 设置所有单元格的属性
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.
SET PROPERTY OF L_CELL 'NumberFormatLocal' =
'_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * ""-""??_ ;_ @' NO FLUSH.
PERFORM FRM_ERR_HDL.
ENDFORM. "frm_excle_init
*&---------------------------------*
*& 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_FORMAT.
FREE OBJECT L_BORDER.
FREE OBJECT L_EDGEBOTTOM.
FREE OBJECT L_SHEET.
FREE OBJECT L_PAGESETUP.
FREE OBJECT L_INTERIOR.
FREE OBJECT G_CELL.
FREE OBJECT V_EXCEL.
ENDFORM. "frm_excel_free
*&---------------------------------*
*& Form FRM_EXCEL_SET_DATA
*&---------------------------------*
* text
*----------------------------------*
* -->P_TABLE text
*----------------------------------*
FORM FRM_EXCEL_SET_DATA TABLES P_TABLE USING P_BORDER.
PERFORM FRM_EXCEL_SET_INIT_VALUE.
PERFORM FRM_EXCEL_SET_CELL_INI.
"表头
PERFORM FRM_EXCLE_SET_TITLE.
"设置查询条件
PERFORM FRM_EXCLE_SET_CX_COND.
"数据表格的表头
PERFORM FRM_EXCEL_SET_DATA_TITLE.
"表格中的数据
PERFORM FRM_EXCEL_SET_TABDATA.
"表格设置线框
IF P_BORDER IS NOT INITIAL.
PERFORM FRM_EXCEL_SET_BORDER.
ENDIF.
"表脚数据
PERFORM FRM_EXCEL_SET_FOOTER_DATA.
ENDFORM. "FRM_EXCEL_SET_DATA
*&---------------------------------*
*& Form down
*&---------------------------------*
* text
*----------------------------------*
* -->X text
* -->Y text
* -->I_VALUE text
* -->I_ALIGN text
*----------------------------------*
FORM DOWN USING X Y I_VALUE I_ALIGN.
* call method of l_excel 'CELLS' = l_cell no flush
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.
SET PROPERTY OF L_CELL 'VALUE' = I_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.
SET PROPERTY OF L_COLUMN 'NumberFormatLocal' = '0.00' NO FLUSH.
WHEN OTHERS.
SET PROPERTY OF L_COLUMN 'NumberFormatLocal' = '@' NO FLUSH.
ENDCASE.
PERFORM FRM_ERR_HDL.
ENDFORM. "down
*&---------------------------------*
*& Form FRM_EXCEL_SET_CELL_INI
*&---------------------------------*
* text
*----------------------------------*
* -->P_TABLE text
*----------------------------------*
FORM FRM_EXCEL_SET_CELL_INI.
DATA: FRM_CELLID TYPE I,
FRM_LANGE2(10),
FRM_END_WORD(4).
FIELD-SYMBOLS:<FRM_WA>,<FRM_FIELD>.
REFRESH IT_EXCLE_TABLE_FIELD.
IT_EXCLE_TABLE_FIELD[] = IT_DATA_FIELD[].
CLEAR FRM_CELLID .
LOOP AT IT_EXCEL_TAB_DATA_TITLE.
READ TABLE IT_EXCLE_TABLE_FIELD ASSIGNING <FRM_WA>
WITH KEY NAME = IT_EXCEL_TAB_DATA_TITLE-FIELDNAME.
IF IT_EXCEL_TAB_DATA_TITLE-FIELDNAME IS NOT INITIAL.
ADD 1 TO FRM_CELLID.
ENDIF.
CALL METHOD OF L_SHEET 'Columns' = L_COLUMN NO FLUSH
EXPORTING
#1 = FRM_CELLID .
PERFORM FRM_ERR_HDL.
ASSIGN COMPONENT 'LENGTH' OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
IF IT_EXCEL_TAB_DATA_TITLE-FIELDNAME IS NOT INITIAL.
SET PROPERTY OF L_COLUMN 'ColumnWidth' = IT_EXCEL_TAB_DATA_TITLE-CELL_WIDTH NO FLUSH.
PERFORM FRM_ERR_HDL.
ENDIF.
ASSIGN COMPONENT 'TYPE_KIND' OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
CASE <FRM_FIELD> .
WHEN 'C'.
SET PROPERTY OF L_COLUMN 'NumberFormatLocal' = '@' NO FLUSH.
PERFORM FRM_ERR_HDL.
ENDCASE.
ENDLOOP.
FRM_END_WORD = FRM_CELLID.
CONDENSE FRM_END_WORD NO-GAPS.
PERFORM FRM_GET_EXCEL_END_WORD CHANGING FRM_END_WORD.
DATA:FRM_LEN TYPE I.
FRM_LEN = STRLEN( FRM_END_WORD ).
IF FRM_LEN = 1.
CONCATENATE FRM_END_WORD '1' INTO FRM_LANGE2.
CONDENSE FRM_LANGE2 NO-GAPS.
ENDIF.
CALL METHOD OF L_SHEET 'Range' = L_RANGE NO FLUSH
EXPORTING
#1 = 'A1'
#2 = FRM_LANGE2.
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' = 18 NO FLUSH .
PERFORM FRM_ERR_HDL.
ENDFORM. "FRM_EXCEL_SET_CELL_INI
*&---------------------------------*
*& 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 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_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_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 = 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_SET_TAB_DATA_TITLE
*&---------------------------------*
* text
*----------------------------------*
* -->P_FIELDNAME text 对应内表字段名
* -->P_TITLE text 显示的文本
* -->P_CELL_WIDTH text 列宽
* -->P_BG_ROW text 起始行
* -->P_ED_ROW text 终止行
* -->P_BG_CELL text 超始列
* -->P_ED_CELL text 终止列
* -->P_MERGCELL text 合并行(值'X'合并,否则不合并)
* -->P_ALIGHT text 位置
* 调用示例:PERFORM FRM_SET_TAB_DATA_TITLE USING 'AKONT' '统驭科目代码' 14 1 2 'A' 'A' 'X'.
*----------------------------------*
FORM FRM_SET_TAB_DATA_TITLE USING P_FIELDNAME P_TEXT P_CELL_WIDTH
P_BG_ROW P_ED_ROW P_BG_CELL P_ED_CELL P_MERGCELL P_ALIGHT.
DATA:FRM_BG_ROW TYPE I,FRM_ED_ROW TYPE I,
FRM_BG_CELL(10),FRM_ED_CELL(10).
CLEAR IT_EXCEL_TAB_DATA_TITLE.
"行号
IF P_BG_ROW IS INITIAL AND P_ED_ROW IS INITIAL.
FRM_BG_ROW = 1.
FRM_ED_ROW = 1.
ELSEIF P_BG_ROW IS NOT INITIAL AND P_ED_ROW IS INITIAL.
FRM_BG_ROW = P_BG_ROW.
FRM_ED_ROW = P_BG_ROW.
ELSEIF P_BG_ROW IS INITIAL AND P_ED_ROW IS NOT INITIAL.
FRM_BG_ROW = P_ED_ROW.
FRM_ED_ROW = P_ED_ROW.
ELSE.
FRM_BG_ROW = P_BG_ROW.
FRM_ED_ROW = P_ED_ROW.
ENDIF.
IT_EXCEL_TAB_DATA_TITLE-BG_ROW = FRM_BG_ROW.
IT_EXCEL_TAB_DATA_TITLE-ED_ROW = FRM_ED_ROW.
"列名
IF P_BG_CELL IS INITIAL AND P_ED_CELL IS INITIAL.
RETURN.
ELSEIF P_BG_CELL IS NOT INITIAL AND P_ED_CELL IS INITIAL.
FRM_BG_CELL = P_BG_CELL.
FRM_ED_CELL = P_BG_CELL.
ELSEIF P_BG_CELL IS INITIAL AND P_ED_CELL IS NOT INITIAL.
FRM_BG_CELL = P_ED_CELL.
FRM_ED_CELL = P_ED_CELL.
ELSE.
FRM_BG_CELL = P_BG_CELL.
FRM_ED_CELL = P_ED_CELL.
ENDIF.
IT_EXCEL_TAB_DATA_TITLE-BG_CELL = FRM_BG_CELL.
IT_EXCEL_TAB_DATA_TITLE-ED_CELL = FRM_ED_CELL.
IT_EXCEL_TAB_DATA_TITLE-VALUE = P_TEXT. "列的描述
IT_EXCEL_TAB_DATA_TITLE-CELL_WIDTH = P_CELL_WIDTH. "列宽
IT_EXCEL_TAB_DATA_TITLE-FIELDNAME = P_FIELDNAME. "对应的列字段名称
IT_EXCEL_TAB_DATA_TITLE-MERGCELL = P_MERGCELL. "列合并(X-合并,空不合并)
IT_EXCEL_TAB_DATA_TITLE-ALIGHT = P_ALIGHT. "位置(L-靠左,C-居中,R-靠右)
APPEND IT_EXCEL_TAB_DATA_TITLE.
ENDFORM. "frm_set_tab_data_title
*&---------------------------------*
*& Form FRM_EXCLE_SAVE_ROW
*&---------------------------------*
* text
*----------------------------------*
* -->P_0456 text
* -->P_1 text
* -->P_1 text
*----------------------------------*
FORM FRM_EXCLE_SAVE_ROW USING P_ROWTYP
P_BGROW
P_EDROW.
DATA:FRM_ROWCNT TYPE I,
FRM_WA_EXCEL_ROWS LIKE LINE OF IT_EXCEL_ROWS.
CLEAR:IT_EXCEL_ROWS.
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.
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 = P_BGROW.
IT_EXCEL_ROWS-ED_ROWID = P_EDROW.
ENDIF.
APPEND IT_EXCEL_ROWS.
ENDFORM. " FRM_EXCLE_SAVE_ROW
*&---------------------------------*
*& Form frm_excle_set_title
*&---------------------------------*
* text
*----------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------*
FORM FRM_EXCLE_SET_TITLE .
IF L_EXCLE_TITLE IS NOT INITIAL.
SET PROPERTY OF L_RANGE 'Value' = L_EXCLE_TITLE NO FLUSH .
PERFORM FRM_ERR_HDL.
PERFORM ROW_HIGH USING 1 '32'.
ENDIF.
ENDFORM. " frm_excle_set_title
*&---------------------------------*
*& Form FRM_EXCLE_SET_CX_COND
*&---------------------------------*
* text
*----------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------*
FORM FRM_EXCLE_SET_CX_COND .
DATA:FRM_ROWID(3).
DATA:FRM_BG_CELL(5),FRM_ED_CELL(5).
DATA:FRM_BG_ROWID TYPE I,FRM_ED_ROWID TYPE I.
DATA:FRM_ROWID_TMP TYPE I.
PERFORM FRM_EXCLE_GET_ED_ROWID USING EXCEL_TITLE CHANGING FRM_ROWID_TMP.
SORT IT_EXCEL_CX_COND BY ROWID.
LOOP AT IT_EXCEL_CX_COND.
FRM_ROWID = IT_EXCEL_CX_COND-ROWID + FRM_ROWID_TMP.
CONDENSE FRM_ROWID NO-GAPS.
CONCATENATE IT_EXCEL_CX_COND-BG_CELL FRM_ROWID INTO FRM_BG_CELL.
CONDENSE FRM_BG_CELL NO-GAPS.
CONCATENATE IT_EXCEL_CX_COND-ED_CELL FRM_ROWID INTO FRM_ED_CELL.
CONDENSE FRM_ED_CELL NO-GAPS.
PERFORM FRM_MERGECELLS USING FRM_BG_CELL FRM_ED_CELL. "合并列
SET PROPERTY OF L_RANGE 'HorizontalAlignment' = 1 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' = 0 NO FLUSH .
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_RANGE 'Value' = IT_EXCEL_CX_COND-VALUE NO FLUSH .
AT FIRST.
FRM_BG_ROWID = FRM_ROWID. "记录行号
ENDAT.
ENDLOOP.
PERFORM FRM_EXCLE_SAVE_ROW USING EXCEL_COND FRM_BG_ROWID FRM_ROWID. "保存当前的行号
ENDFORM. " FRM_EXCLE_SET_CX_COND
*&---------------------------------*
*& Form FRM_EXCEL_SET_CX_COND
*&---------------------------------*
* text
*----------------------------------*
* -->P_1 text
* -->P_2212 text
* -->P_2213 text
* -->P_FRM_MSG text
*----------------------------------*
FORM FRM_EXCEL_SET_CX_COND USING P_ROWID
P_BG_CELL
P_ED_CELL
P_VALUE.
CLEAR:IT_EXCEL_CX_COND.
CONDENSE P_VALUE NO-GAPS.
IT_EXCEL_CX_COND-ROWID = P_ROWID.
IT_EXCEL_CX_COND-BG_CELL = P_BG_CELL.
IT_EXCEL_CX_COND-ED_CELL = P_ED_CELL.
IT_EXCEL_CX_COND-VALUE = P_VALUE.
APPEND IT_EXCEL_CX_COND.
ENDFORM. " FRM_EXCEL_SET_CX_COND
*&---------------------------------*
*& Form FRM_EXCEL_SET_FOOTER
*&---------------------------------*
* text
*----------------------------------*
* -->P_ROWID text
* -->P_BG_CELL text
* -->P_ED_CELL text
* -->P_VALUE text
*----------------------------------*
FORM FRM_EXCEL_SET_FOOTER USING P_ROWID
P_BG_CELL
P_ED_CELL
P_VALUE.
CLEAR:IT_EXCEL_FOOTER.
CONDENSE P_VALUE NO-GAPS.
IT_EXCEL_FOOTER-ROWID = P_ROWID .
IT_EXCEL_FOOTER-BG_CELL = P_BG_CELL.
IT_EXCEL_FOOTER-ED_CELL = P_ED_CELL.
IT_EXCEL_FOOTER-VALUE = P_VALUE.
APPEND IT_EXCEL_FOOTER.
ENDFORM. "FRM_EXCEL_SET_FOOTER
*&---------------------------------*
*& Form FRM_EXCLE_GET_BG_ROWID
*&---------------------------------*
* text
*----------------------------------*
* -->P_ROWTYP text
* -->P_ROWID text
*----------------------------------*
FORM FRM_EXCLE_GET_BG_ROWID USING P_ROWTYP CHANGING P_ROWID .
PERFORM FRM_EXCLE_GET_ROWID USING P_ROWTYP 'BG_ROWID' CHANGING P_ROWID .
ENDFORM. "FRM_EXCLE_GET_BG_ROWID
*&---------------------------------*
*& 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.
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>.
ENDIF.
ENDFORM. "FRM_EXCLE_GET_ROWID
*&---------------------------------*
*& Form FRM_EXCEL_SET_DATA_TITLE
*&---------------------------------*
* text
*----------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------*
FORM FRM_EXCEL_SET_DATA_TITLE .
DATA:FRM_BG_ROWID_FIRST(3).
DATA:FRM_BG_ROWID(3),FRM_ED_ROWID(3).
DATA:FRM_BG_CELL(5),FRM_ED_CELL(5).
DATA:FRM_SAVE_BG_ROWID TYPE I,FRM_SAVE_ED_ROWID TYPE I.
DATA:FRM_CELLNAME_TMP LIKE FRM_BG_CELL.
DATA:FRM_CELLID TYPE I.
DATA:FRM_ROWID TYPE I.
PERFORM FRM_EXCLE_GET_ED_ROWID USING EXCEL_COND CHANGING FRM_ROWID.
CLEAR FRM_CELLID.
LOOP AT IT_EXCEL_TAB_DATA_TITLE.
FRM_BG_ROWID = IT_EXCEL_TAB_DATA_TITLE-BG_ROW + FRM_ROWID.
CONDENSE FRM_BG_ROWID NO-GAPS.
AT FIRST.
FRM_BG_ROWID_FIRST = FRM_BG_ROWID.
ENDAT.
CONCATENATE IT_EXCEL_TAB_DATA_TITLE-BG_CELL FRM_BG_ROWID INTO FRM_BG_CELL.
CONDENSE FRM_BG_CELL NO-GAPS.
FRM_ED_ROWID = IT_EXCEL_TAB_DATA_TITLE-ED_ROW + FRM_ROWID.
CONDENSE FRM_ED_ROWID NO-GAPS.
CONCATENATE IT_EXCEL_TAB_DATA_TITLE-ED_CELL FRM_ED_ROWID INTO FRM_ED_CELL.
CONDENSE FRM_ED_CELL NO-GAPS.
PERFORM FRM_MERGECELLS USING FRM_BG_CELL FRM_ED_CELL. "合并列
IF FRM_CELLNAME_TMP <> IT_EXCEL_TAB_DATA_TITLE-BG_CELL.
ADD 1 TO FRM_CELLID.
FRM_CELLNAME_TMP = IT_EXCEL_TAB_DATA_TITLE-BG_CELL.
ELSE.
ENDIF.
PERFORM DOWN USING FRM_BG_ROWID FRM_CELLID IT_EXCEL_TAB_DATA_TITLE-VALUE 3.
GET PROPERTY OF L_CELL 'Font' = L_FONT NO FLUSH .
SET PROPERTY OF L_FONT 'Bold' = EXCEL_TB_CELL_TITLE_FONT_BOLD NO FLUSH .
"保字段的显示位置格式
PERFORM FRM_EXCEL_SET_TB_CELL_HALIGHT USING IT_EXCEL_TAB_DATA_TITLE-FIELDNAME
IT_EXCEL_TAB_DATA_TITLE-ALIGHT.
ENDLOOP.
PERFORM FRM_EXCLE_SAVE_ROW USING EXCEL_TBTIL FRM_BG_ROWID_FIRST FRM_ED_ROWID. "保存当前的行号
ENDFORM. " FRM_EXCEL_SET_DATA_TITLE
*&---------------------------------*
*& Form FRM_EXCEL_TB_TITLE_NO_BOLD
*&---------------------------------*
* text
*----------------------------------*
FORM FRM_EXCEL_TB_TITLE_NO_BOLD.
CLEAR EXCEL_TB_CELL_TITLE_FONT_BOLD.
ENDFORM. "FRM_EXCEL_TB_TITLE_NO_BOLD
*&---------------------------------*
*& Form FRM_EXCEL_SET_TABDATA
*&---------------------------------*
* text
*----------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------*
FORM FRM_EXCEL_SET_TABDATA.
DATA:FRM_ROWID TYPE I.
DATA:FRM_BG_ROWID TYPE I.
DATA:FRM_CURRENTROW TYPE I,
FRM_CURRENTCELL TYPE I,
FRM_ALIGHT TYPE I.
DATA:FRM_ROWID_C(5),FRM_BG_MERGCELL(10),FRM_ED_MERGCELL(10).
DATA:BEGIN OF FRM_IT_MERGECELLS OCCURS 0,
BG_ROWID TYPE I,
ED_ROWID TYPE I,
BG_CELL(5),
ED_CELL(5),
END OF FRM_IT_MERGECELLS.
DATA:FRM_ITAB_ROWCNT TYPE I,
FRM_LOOP_ROWID TYPE I.
FIELD-SYMBOLS:<FRM_WA>,<FRM_FIELD>,<FRM_FIELD2>,
<FRM_EXP_DATA> TYPE STANDARD TABLE.
PERFORM FRM_EXCLE_GET_ED_ROWID USING EXCEL_TBTIL CHANGING FRM_ROWID.
REFRESH FRM_IT_MERGECELLS.
ASSIGN DYN_DATA->* TO <FRM_EXP_DATA>.
FRM_ITAB_ROWCNT = LINES( <FRM_EXP_DATA> ). "内表记录数
LOOP AT <FRM_EXP_DATA> ASSIGNING <FRM_WA>.
FRM_LOOP_ROWID = SY-TABIX.
FRM_CURRENTROW = SY-TABIX + FRM_ROWID.
AT FIRST.
FRM_BG_ROWID = FRM_CURRENTROW.
ENDAT.
CLEAR FRM_CURRENTCELL.
LOOP AT IT_EXCEL_TAB_DATA_TITLE WHERE FIELDNAME IS NOT INITIAL.
ADD 1 TO FRM_CURRENTCELL.
ASSIGN COMPONENT IT_EXCEL_TAB_DATA_TITLE-FIELDNAME OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
READ TABLE IT_TB_CELL_HALIGNMENT WITH KEY NAME = IT_EXCEL_TAB_DATA_TITLE-FIELDNAME.
IF SY-SUBRC NE 0.
READ TABLE IT_EXCLE_TABLE_FIELD WITH KEY NAME = IT_EXCEL_TAB_DATA_TITLE-FIELDNAME.
CASE IT_EXCLE_TABLE_FIELD-TYPE_KIND.
WHEN 'C'.
FRM_ALIGHT = 1.
CONDENSE <FRM_FIELD> NO-GAPS.
PERFORM FRM_EXCEL_SET_ROW_WIDTH USING IT_EXCEL_TAB_DATA_TITLE-CELL_WIDTH <FRM_FIELD>.
WHEN 'D'.
FRM_ALIGHT = 1.
WHEN 'P'.
FRM_ALIGHT = 4.
WHEN OTHERS.
FRM_ALIGHT = 3.
ENDCASE.
ELSE.
FRM_ALIGHT = IT_TB_CELL_HALIGNMENT-ALIGHT.
ENDIF.
IF <FRM_FIELD> IS INITIAL.
ASSIGN '' TO <FRM_FIELD>.
ENDIF.
IF FRM_ITAB_ROWCNT = FRM_LOOP_ROWID AND AUTO_TOTAL IS NOT INITIAL.
ASSIGN AUTO_TOTAL_TEXT TO <FRM_FIELD> .
CLEAR AUTO_TOTAL.
ENDIF.
PERFORM DOWN USING FRM_CURRENTROW FRM_CURRENTCELL <FRM_FIELD> FRM_ALIGHT.
ENDLOOP.
ENDLOOP.
PERFORM FRM_EXCLE_SAVE_ROW USING EXCEL_TDATA FRM_BG_ROWID FRM_CURRENTROW. "保存当前的行号
DATA:FRM_NULL VALUE '',
FRM_EXE.
FIELD-SYMBOLS:<FRM_MERGVALUE>.
ASSIGN FRM_NULL TO <FRM_MERGVALUE>.
LOOP AT IT_EXCEL_TAB_DATA_TITLE WHERE MERGCELL IS NOT INITIAL.
ASSIGN FRM_NULL TO <FRM_MERGVALUE>.
CLEAR FRM_EXE.
LOOP AT <FRM_EXP_DATA> ASSIGNING <FRM_WA>.
FRM_CURRENTROW = SY-TABIX + FRM_ROWID.
ASSIGN COMPONENT IT_EXCEL_TAB_DATA_TITLE-FIELDNAME OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
IF <FRM_MERGVALUE> <> <FRM_FIELD>.
IF <FRM_MERGVALUE> IS NOT INITIAL.
APPEND FRM_IT_MERGECELLS.
ENDIF.
FRM_IT_MERGECELLS-BG_ROWID = FRM_CURRENTROW. "不相等行首行
FRM_IT_MERGECELLS-BG_CELL = IT_EXCEL_TAB_DATA_TITLE-BG_CELL.
ASSIGN <FRM_FIELD> TO <FRM_MERGVALUE>.
ELSE.
FRM_IT_MERGECELLS-ED_ROWID = FRM_CURRENTROW.
FRM_IT_MERGECELLS-ED_CELL = IT_EXCEL_TAB_DATA_TITLE-ED_CELL.
ENDIF.
FRM_EXE = 'X'.
ENDLOOP.
IF FRM_EXE IS NOT INITIAL.
APPEND FRM_IT_MERGECELLS.
ENDIF.
ENDLOOP.
"合并相同的行
LOOP AT FRM_IT_MERGECELLS.
FRM_ROWID_C = FRM_IT_MERGECELLS-BG_ROWID.
CONDENSE FRM_ROWID_C NO-GAPS.
CONCATENATE FRM_IT_MERGECELLS-BG_CELL FRM_ROWID_C INTO FRM_BG_MERGCELL.
FRM_ROWID_C = FRM_IT_MERGECELLS-ED_ROWID.
CONDENSE FRM_ROWID_C NO-GAPS.
CONCATENATE FRM_IT_MERGECELLS-ED_CELL FRM_ROWID_C INTO FRM_ED_MERGCELL.
PERFORM FRM_MERGECELLS USING FRM_BG_MERGCELL FRM_ED_MERGCELL.
ENDLOOP.
ENDFORM. " FRM_EXCEL_SET_TABDATA
*&---------------------------------*
*& 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_EXCEL_SET_CELL_WIDTH
*&---------------------------------*
* text
*----------------------------------*
* -->P_CELLID text
* -->P_WIDTH text
*----------------------------------*
FORM FRM_EXCEL_SET_CELL_WIDTH USING P_CELLID P_WIDTH.
CALL METHOD OF L_SHEET 'Columns' = L_COLUMN NO FLUSH
EXPORTING
#1 = P_CELLID .
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_COLUMN 'ColumnWidth' = P_WIDTH NO FLUSH.
PERFORM FRM_ERR_HDL.
ENDFORM. " frm_excel_set_cell_width
*&---------------------------------*
*& 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_excel 'Range' = l_range no flush
CALL METHOD OF L_SHEET 'Range' = L_RANGE NO FLUSH
* EXPORTING #1 = 'A1' #2 = 'L5'.
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_EXCEL_SET_BORDER
*&---------------------------------*
* text
*----------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------*
FORM FRM_EXCEL_SET_BORDER .
DATA:FRM_BG_ROWID TYPE I,FRM_ED_ROWID TYPE I.
DATA:FRM_BG_CELL(5),FRM_ED_CELL(5).
DATA:FRM_WA_TITLE LIKE IT_EXCEL_TAB_DATA_TITLE.
DATA:FRM_LINECOUNT TYPE I.
PERFORM FRM_EXCLE_GET_BG_ROWID USING EXCEL_TBTIL CHANGING FRM_BG_ROWID.
PERFORM FRM_EXCLE_GET_ED_ROWID USING EXCEL_TDATA CHANGING FRM_ED_ROWID.
CLEAR:FRM_BG_CELL,FRM_ED_CELL.
READ TABLE IT_EXCEL_TAB_DATA_TITLE INTO FRM_WA_TITLE INDEX 1.
FRM_BG_CELL = FRM_WA_TITLE-BG_CELL.
CONDENSE FRM_BG_CELL NO-GAPS.
FRM_LINECOUNT = LINES( IT_EXCEL_TAB_DATA_TITLE ).
READ TABLE IT_EXCEL_TAB_DATA_TITLE INTO FRM_WA_TITLE INDEX FRM_LINECOUNT.
FRM_ED_CELL = FRM_WA_TITLE-ED_CELL.
CONDENSE FRM_ED_CELL NO-GAPS.
IF FRM_BG_CELL IS INITIAL OR FRM_ED_CELL IS INITIAL.
RETURN.
ENDIF.
PERFORM FRM_EXCEL_BORDER USING FRM_BG_ROWID FRM_ED_ROWID FRM_BG_CELL FRM_ED_CELL.
ENDFORM. " FRM_EXCEL_SET_BORDER
*&---------------------------------*
*& Form FRM_EXCEL_SET_FOOTER_DATA
*&---------------------------------*
* text
*----------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------*
FORM FRM_EXCEL_SET_FOOTER_DATA .
DATA:FRM_ROWID(3).
DATA:FRM_BG_CELL(5),FRM_ED_CELL(5).
DATA:FRM_BG_ROWID TYPE I,FRM_ED_ROWID TYPE I.
DATA:FRM_ROWID_TMP TYPE I.
PERFORM FRM_EXCLE_GET_ED_ROWID USING EXCEL_TDATA CHANGING FRM_ROWID_TMP.
SORT IT_EXCEL_FOOTER BY ROWID.
LOOP AT IT_EXCEL_FOOTER.
FRM_ROWID = IT_EXCEL_FOOTER-ROWID + FRM_ROWID_TMP.
CONDENSE FRM_ROWID NO-GAPS.
CONCATENATE IT_EXCEL_FOOTER-BG_CELL FRM_ROWID INTO FRM_BG_CELL.
CONDENSE FRM_BG_CELL NO-GAPS.
CONCATENATE IT_EXCEL_FOOTER-ED_CELL FRM_ROWID INTO FRM_ED_CELL.
CONDENSE FRM_ED_CELL NO-GAPS.
PERFORM FRM_MERGECELLS USING FRM_BG_CELL FRM_ED_CELL. "合并列
SET PROPERTY OF L_RANGE 'HorizontalAlignment' = 1 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' = 0 NO FLUSH .
PERFORM FRM_ERR_HDL.
SET PROPERTY OF L_RANGE 'Value' = IT_EXCEL_FOOTER-VALUE NO FLUSH .
AT FIRST.
FRM_BG_ROWID = FRM_ROWID. "记录行号
ENDAT.
ENDLOOP.
PERFORM FRM_EXCLE_SAVE_ROW USING EXCEL_COND FRM_BG_ROWID FRM_ROWID. "保存当前的行号
ENDFORM. " FRM_EXCEL_SET_FOOTER_DATA
*&---------------------------------*
*& 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 = P_ALIGHT.
ENDCASE.
APPEND IT_TB_CELL_HALIGNMENT.
ENDFORM. "FRM_EXCEL_SET_TB_CELL_HALIGHT
*&---------------------------------*
*& Form frm_excel_set_row_width
*&---------------------------------*
* text
*----------------------------------*
* -->P_IT_EXCEL_TAB_DATA_TITLE_CELL_W text
* -->P_<FRM_FIELD> text
*----------------------------------*
FORM FRM_EXCEL_SET_ROW_WIDTH USING P_CELL_WIDTH
P_VALUE.
DATA:FRM_LEN TYPE I,
FRM_MIN_ZS TYPE P DECIMALS 4.
FRM_LEN = CL_ABAP_LIST_UTILITIES=>DYNAMIC_OUTPUT_LENGTH( P_VALUE ). "包含中文字符串
"FRM_LEN = FRM_LEN .
FRM_MIN_ZS = CEIL( FRM_LEN / P_CELL_WIDTH ).
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_width
*&---------------------------------*
*& Form FRM_DC_EXCEL
*&---------------------------------*
* text
*----------------------------------*
* -->P_IT_COND text
* -->P_IT_ITEM text
* -->P_IT_DATA text
* -->P_TITLE text
* -->P_BORDER 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
P_IT_DATA
USING P_TITLE P_BORDER P_ADD_TOTAL_ROW.
"文件名称
PERFORM FRM_EXCLE_INIT USING P_TITLE."设置表头
"查询条件
CLEAR:IT_EXCEL_CX_COND[].
LOOP AT P_IT_COND.
PERFORM FRM_EXCEL_SET_CX_COND USING P_IT_COND-ROWID P_IT_COND-BG_COLNAME
P_IT_COND-ED_COLNAME P_IT_COND-TEXT.
ENDLOOP.
"表数据抬头
CLEAR IT_EXCEL_TAB_DATA_TITLE[].
LOOP AT P_IT_ITEM.
PERFORM FRM_SET_TAB_DATA_TITLE USING P_IT_ITEM-NAME P_IT_ITEM-TEXT P_IT_ITEM-LEN
P_IT_ITEM-BG_ROW P_IT_ITEM-ED_ROW
P_IT_ITEM-BG_COLNAME P_IT_ITEM-ED_COLNAME
P_IT_ITEM-MERGCELL P_IT_ITEM-ALIGHT.
ENDLOOP.
"将要导出的内表数据转到动态内表中
PERFORM FRM_MOVE_DATA TABLES P_IT_DATA..
"表脚数据
CLEAR:IT_EXCEL_FOOTER,IT_EXCEL_FOOTER[].
LOOP AT P_IT_FOOTER.
PERFORM FRM_EXCEL_SET_FOOTER USING P_IT_FOOTER-ROWID P_IT_FOOTER-BG_COLNAME
P_IT_FOOTER-ED_COLNAME P_IT_FOOTER-TEXT.
ENDLOOP.
"自动计算并加上合计行
PERFORM FRM_COMPUTER_TOTAL TABLES P_IT_DATA USING P_ADD_TOTAL_ROW.
PERFORM FRM_EXCEL_SET_DATA TABLES P_IT_DATA USING P_BORDER.
ENDFORM. " FRM_DC_EXCEL
*&---------------------------------*
*& Form FRM_KSDC_EXCEL
*&---------------------------------*
* text
*----------------------------------*
* -->P_IT_COND text
* -->P_IT_ITEM text
* -->P_IT_FOOTER text
* -->P_IT_DATA text
* -->P_TITLE 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
P_IT_DATA
USING P_TITLE P_ADD_TOTAL_ROW.
DATA:FRM_IT_FIELDTEXT TYPE STRING OCCURS 0 WITH HEADER LINE,
FRM_TEXT LIKE ZST_EXP_EXCEL_TAB_FD_TITLE-TEXT.
CLEAR:FRM_IT_FIELDTEXT[].
CLEAR IT_EXCEL_TAB_DATA_TITLE[].
"表格抬头
LOOP AT P_IT_ITEM.
IF P_IT_ITEM-NAME IS NOT INITIAL.
CONCATENATE P_IT_ITEM-TEXT_FJQZ P_IT_ITEM-TEXT INTO FRM_IT_FIELDTEXT.
APPEND FRM_IT_FIELDTEXT.
ENDIF.
"转换数据
PERFORM FRM_SET_TAB_DATA_TITLE USING P_IT_ITEM-NAME P_IT_ITEM-TEXT P_IT_ITEM-LEN
P_IT_ITEM-BG_ROW P_IT_ITEM-ED_ROW
P_IT_ITEM-BG_COLNAME P_IT_ITEM-ED_COLNAME
P_IT_ITEM-MERGCELL P_IT_ITEM-ALIGHT.
ENDLOOP.
DATA:FRM_FILENAME TYPE STRING,
FRM_PATH TYPE STRING,
FRM_FULLPATH TYPE STRING.
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.
"将要导出的内表数据转到动态内表中
PERFORM FRM_MOVE_DATA TABLES P_IT_DATA..
"自动计算并加上合计行
PERFORM FRM_COMPUTER_TOTAL TABLES P_IT_DATA USING P_ADD_TOTAL_ROW.
PERFORM FRM_DOWNLOAD TABLES P_IT_COND FRM_IT_FIELDTEXT P_IT_FOOTER
USING P_TITLE FRM_FULLPATH.
ENDFORM. " FRM_KSDC_EXCEL
*&---------------------------------*
*& Form FRM_DOWNLOAD
*&---------------------------------*
* text
*----------------------------------*
* -->P_IT_TABLE text
* -->P_IT_COND text
* -->P_IT_FIELDTEXT text
* -->P_IT_FOOTER text
* -->P_TITLE text
* -->P_FILENAME text
*----------------------------------*
FORM FRM_DOWNLOAD TABLES P_IT_COND STRUCTURE ZST_EXP_EXCEL_FILE_COND
P_IT_FIELDTEXT
P_IT_FOOTER STRUCTURE ZST_EXP_EXCEL_FILE_COND
USING P_TITLE P_FILENAME.
DATA:FRM_FILENAME LIKE RLGRAP-FILENAME.
DATA:FRM_IT_DOWNLOAD TYPE STRING OCCURS 0 WITH HEADER LINE.
"DATA:FRM_ITAB_COMPONET TYPE REF TO CL_ABAP_STRUCTDESCR.
DATA:FRM_TAB_CHAR.
DATA:FRM_VALUE TYPE STRING.
DATA:FRM_ITAB_ROWCNT TYPE I, FRM_LOOP_ROWID TYPE I.
FIELD-SYMBOLS:<FRM_WA>,<FRM_FIELD>,<FRM_VALUE>,
<FRM_EXP_DATA> TYPE STANDARD TABLE,<FRM_EXP_WA>.
IF P_FILENAME IS INITIAL.
RETURN.
ENDIF.
"FRM_ITAB_COMPONET ?= CL_ABAP_TYPEDESCR=>DESCRIBE_BY_DATA( P_IT_TABLE ).
FRM_TAB_CHAR = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB."取跳格键
CLEAR:FRM_IT_DOWNLOAD,FRM_IT_DOWNLOAD[].
"抬头描述
IF P_TITLE IS NOT INITIAL.
CLEAR FRM_IT_DOWNLOAD.
FRM_IT_DOWNLOAD = P_TITLE. APPEND FRM_IT_DOWNLOAD.
ENDIF.
"查询条件
SORT P_IT_COND BY ROWID.
LOOP AT P_IT_COND.
CLEAR FRM_IT_DOWNLOAD.
FRM_IT_DOWNLOAD = P_IT_COND-TEXT. APPEND FRM_IT_DOWNLOAD.
ENDLOOP.
"表格抬头
CLEAR FRM_IT_DOWNLOAD.
IF P_IT_FIELDTEXT[] IS NOT INITIAL.
LOOP AT P_IT_FIELDTEXT.
CONCATENATE FRM_IT_DOWNLOAD P_IT_FIELDTEXT FRM_TAB_CHAR INTO FRM_IT_DOWNLOAD.
ENDLOOP.
APPEND FRM_IT_DOWNLOAD.
ELSE.
LOOP AT IT_DATA_FIELD ASSIGNING <FRM_WA>.
ASSIGN COMPONENT 'NAME' OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
CONCATENATE FRM_IT_DOWNLOAD <FRM_FIELD> FRM_TAB_CHAR INTO FRM_IT_DOWNLOAD.
ENDLOOP.
APPEND FRM_IT_DOWNLOAD.
ENDIF.
"表格内的数据
ASSIGN DYN_DATA->* TO <FRM_EXP_DATA>.
FRM_ITAB_ROWCNT = LINES( <FRM_EXP_DATA> ).
LOOP AT <FRM_EXP_DATA> ASSIGNING <FRM_EXP_WA>.
FRM_LOOP_ROWID = SY-TABIX.
CLEAR FRM_IT_DOWNLOAD.
LOOP AT IT_DATA_FIELD ASSIGNING <FRM_WA>.
ASSIGN COMPONENT 'NAME' OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
IF IT_EXCEL_TAB_DATA_TITLE[] IS NOT INITIAL.
READ TABLE IT_EXCEL_TAB_DATA_TITLE WITH KEY FIELDNAME = <FRM_FIELD>.
IF SY-SUBRC NE 0.
CONTINUE.
ENDIF.
ENDIF.
ASSIGN COMPONENT <FRM_FIELD> OF STRUCTURE <FRM_EXP_WA> TO <FRM_VALUE>.
IF <FRM_VALUE> IS INITIAL.
FRM_VALUE = ''.
ELSE.
ASSIGN COMPONENT 'TYPE_KIND' OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
CASE <FRM_FIELD>.
WHEN 'C' OR 'D'.
FRM_VALUE = <FRM_VALUE>.
WHEN OTHERS.
IF <FRM_VALUE> < 0.
FRM_VALUE = ABS( <FRM_VALUE> ).
CONCATENATE '-' FRM_VALUE INTO FRM_VALUE.
CONDENSE FRM_VALUE NO-GAPS.
ELSE.
FRM_VALUE = <FRM_VALUE>.
ENDIF.
ENDCASE.
CONDENSE FRM_VALUE NO-GAPS.
ENDIF.
IF FRM_ITAB_ROWCNT = FRM_LOOP_ROWID AND AUTO_TOTAL IS NOT INITIAL.
FRM_VALUE = AUTO_TOTAL_TEXT.
CLEAR AUTO_TOTAL.
ENDIF.
CONCATENATE FRM_IT_DOWNLOAD FRM_VALUE FRM_TAB_CHAR INTO FRM_IT_DOWNLOAD.
ENDLOOP.
CONDENSE FRM_IT_DOWNLOAD NO-GAPS.
APPEND FRM_IT_DOWNLOAD.
ENDLOOP.
"表脚数据
SORT P_IT_FOOTER BY ROWID.
LOOP AT P_IT_FOOTER.
CLEAR FRM_IT_DOWNLOAD.
FRM_IT_DOWNLOAD = P_IT_FOOTER-TEXT. APPEND FRM_IT_DOWNLOAD.
ENDLOOP.
FRM_FILENAME = P_FILENAME.
CALL FUNCTION 'WS_DOWNLOAD'
EXPORTING
FILENAME = FRM_FILENAME
FILETYPE = 'ASC'
TABLES
DATA_TAB = FRM_IT_DOWNLOAD
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
*&---------------------------------*
*& Form FRM_COMPUTER_TOTAL
*&---------------------------------*
* text
*----------------------------------*
* -->P_IT_DATA text
* -->P_ADD_TOTAL_ROW text
*----------------------------------*
FORM FRM_COMPUTER_TOTAL TABLES P_IT_DATA USING P_ADD_TOTAL_ROW.
FIELD-SYMBOLS:<FRM_TABLE> TYPE STANDARD TABLE,<FRM_WA>,<FRM_FIELD>,
<FRM_EXP_DATA> TYPE STANDARD TABLE.
DATA: FRM_DYN_TABLE TYPE REF TO DATA,
FRM_DYN_WA TYPE REF TO DATA,
FRM_IT_DATA_FIELD LIKE IT_DATA_FIELD OCCURS 0 WITH HEADER LINE.
CLEAR:AUTO_TOTAL.
IF P_ADD_TOTAL_ROW IS INITIAL.
RETURN.
ENDIF.
PERFORM FRM_CREATE_ITAB TABLES P_IT_DATA FRM_IT_DATA_FIELD CHANGING FRM_DYN_TABLE FRM_DYN_WA.
ASSIGN FRM_DYN_TABLE->* TO <FRM_TABLE>.
ASSIGN FRM_DYN_WA->* TO <FRM_WA>.
"检查是否存在P类型可自动合计的字段
SORT FRM_IT_DATA_FIELD BY TYPE_KIND.
READ TABLE FRM_IT_DATA_FIELD WITH KEY TYPE_KIND = 'P' BINARY SEARCH.
IF SY-SUBRC NE 0. "没有P类型
RETURN.
ENDIF.
CLEAR:<FRM_TABLE>,<FRM_WA>.
LOOP AT P_IT_DATA.
MOVE-CORRESPONDING P_IT_DATA TO <FRM_WA>.
LOOP AT IT_DATA_FIELD WHERE TYPE_KIND <> 'P'.
ASSIGN COMPONENT IT_DATA_FIELD-NAME OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
CLEAR <FRM_FIELD>.
ENDLOOP.
COLLECT <FRM_WA> INTO <FRM_TABLE>.
ENDLOOP.
ASSIGN DYN_DATA->* TO <FRM_EXP_DATA>.
INSERT LINES OF <FRM_TABLE> INTO TABLE <FRM_EXP_DATA>.
AUTO_TOTAL = 'X'. "标志有自动合计
ENDFORM. " FRM_COMPUTER_TOTAL
*&---------------------------------*
*& Form frm_create_itab
*&---------------------------------*
* text
*----------------------------------*
FORM FRM_CREATE_ITAB TABLES P_IT_DATA
P_IT_FIELD STRUCTURE IT_DATA_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[],P_IT_FIELD.
LOOP AT FRM_ITAB_COMPONET->COMPONENTS ASSIGNING <FRM_WA>. "列的处理
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>.
ASSIGN COMPONENT 'DECIMALS' OF STRUCTURE <FRM_WA> TO <FRM_FIELD>.
FRM_XFC-DECIMALS = <FRM_FIELD>.
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_MOVE_DATA
*&---------------------------------*
* text
*----------------------------------*
* -->P_IT_DATA text
*----------------------------------*
FORM FRM_MOVE_DATA TABLES P_IT_DATA .
FIELD-SYMBOLS: <FRM_DYN_TABLE> TYPE STANDARD TABLE,<FRM_DYN_WA>,<FRM_DATA_WA>.
PERFORM FRM_CREATE_ITAB TABLES P_IT_DATA IT_DATA_FIELD
CHANGING DYN_DATA DYN_WA.
ASSIGN DYN_DATA->* TO <FRM_DYN_TABLE>.
ASSIGN DYN_WA->* TO <FRM_DYN_WA>.
CLEAR <FRM_DYN_TABLE>[].
LOOP AT P_IT_DATA ASSIGNING <FRM_DATA_WA>.
CLEAR <FRM_DYN_WA>.
MOVE-CORRESPONDING <FRM_DATA_WA> TO <FRM_DYN_WA>.
APPEND <FRM_DYN_WA> TO <FRM_DYN_TABLE>.
ENDLOOP.
ENDFORM. " FRM_MOVE_DATA
"************* 结构定义 *******************
1、ZST_EXP_EXCEL_FILE_COND
ROWID AM_LINENUMBER INT2 5 0 行号
BG_COLNAME CHAR5 起始列名
ED_COLNAME CHAR5 末列名
TEXT CHAR256 值
2、ZST_EXP_EXCEL_TAB_FD_TITLE
NAME CHAR30 字段名
TEXT CHAR30 表字段简介(限30字符)
TEXT_FJQZ CHAR30 附加前缀描述
LEN NUMC4 输入长度
BG_ROW NUMC3 起始行
ED_ROW NUMC3 末行
BG_COLNAME CHAR5 起始列名
ED_COLNAME CHAR 5 末列名
MERGCELL CHAR1 合并列
ALIGHT CHAR1 显示位置(L,C,R)