*&---------------------------------------------------------------------*
*& Report ZTEST_OLE01
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ztest_ole01.
TYPES : BEGIN OF ty_alv,
bukrs TYPE vbrk-bukrs,
gjahr TYPE vbrk-gjahr,
kunrg TYPE vbrk-kunrg,
fkdat TYPE vbrk-fkdat,
name1 TYPE kna1-name1,
zchar TYPE char12,
END OF ty_alv.
DATA:
gt_dalv TYPE TABLE OF ty_alv WITH HEADER LINE,
gs_dalv TYPE ty_alv.
* bigen EXCEL------------------------------------------------
"excel大标题
TYPES: ty_head(65535) TYPE c.
DATA: gt_excel_data TYPE TABLE OF ty_head,
gs_excel_data TYPE ty_head.
"excel表格标题
TYPES: BEGIN OF ty_s_line,
value TYPE char255,
END OF ty_s_line.
DATA: gt_lines TYPE TABLE OF ty_s_line,
gs_lines TYPE ty_s_line.
DATA:go_application TYPE ole2_object,
go_workbook TYPE ole2_object,
go_workbooks TYPE ole2_object,
go_worksheet TYPE ole2_object,
go_worksheets TYPE ole2_object,
go_newsheet TYPE ole2_object.
DATA:gs_cell1 TYPE ole2_object,
gs_cell2 TYPE ole2_object,
gs_cells TYPE ole2_object,
gs_excel TYPE ole2_object.
* end EXCEL------------------------------------------------
*&---------------------------------------------------------------------*
DATA:lv_fpath TYPE rlgrap-filename, "下载路径
lv_name TYPE string, "文件名称
lv_NAME1 TYPE string,
lv_FKDAT TYPE c LENGTH 12,
lv_bukrs TYPE bukrs, "公司代码
lv_bukrs_name TYPE butxt, "公司名称
lv_body_row TYPE i, " 表身行数
lv_sheet_name TYPE string, " sheet 从命名名称
lv_datum TYPE sy-datum, "过账日期
lv_ch_datum TYPE sy-datum. "输入日期最后一天
PERFORM frm_get_fpath CHANGING lv_fpath."下载路径
LOOP AT gt_dalv.
lv_NAME1 = gt_dalv-name1.
lv_bukrs = gt_dalv-bukrs.
CONCATENATE gt_dalv-fkdat(4) '年' gt_dalv-fkdat+4(2) '月' INTO lv_FKDAT.
AT NEW kunrg.
CLEAR:lv_body_row,lv_bukrs_name.
PERFORM frm_create_document. "创建文档
PERFORM frm_Sheet_header USING lv_NAME1 lv_FKDAT . "标头
CONCATENATE lv_NAME1 lv_FKDAT '对账单' INTO lv_sheet_name.
SELECT SINGLE butxt INTO lv_bukrs_name FROM t001 WHERE bukrs EQ lv_bukrs.
ENDAT.
lv_datum = gt_dalv-fkdat.
lv_body_row = lv_body_row + 1.
PERFORM frm_sheet_body USING gt_dalv. "表身
AT END OF kunrg.
PERFORM call_BKK_GET_MONTH_LASTDAY USING lv_datum
CHANGING lv_ch_datum.
PERFORM frm_sheet_tail USING lv_bukrs_name lv_NAME1 lv_ch_datum. "尾
PERFORM frm_sheet_style USING lv_body_row lv_sheet_name. "设置单元格样式
PERFORM frm_download_excel USING lv_fpath lv_sheet_name. "下载Excel
CLEAR:gt_excel_data[].
* PERFORM frm_add_document USING 1 . "新增sheet1 + n
ENDAT.
ENDLOOP.
*&---------------------------------------------------------------------*
*& Form frm_download_excel
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_download_excel USING us_fpath us_name.
DATA: lv_complete_path TYPE char256.
* File name 保存路径及文件名
CONCATENATE us_fpath us_name '_对账单'
INTO lv_complete_path.
* Save the document 保存文件
CALL METHOD OF go_workbook 'SaveAs'
EXPORTING
#1 = lv_complete_path.
IF sy-subrc EQ 0.
MESSAGE 'File downloaded successfully' TYPE 'S'.
ELSE.
MESSAGE 'Error downloading the file' TYPE 'E'.
ENDIF.
* Close the document and free memory 关闭并释放
PERFORM frm_close_document.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_create_document
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_create_document .
CREATE OBJECT go_application 'Excel.Application'.
SET PROPERTY OF go_application 'Visible' = 0.
CALL METHOD OF go_application 'Workbooks' = go_workbooks.
CALL METHOD OF go_workbooks 'Add' = go_workbook.
GET PROPERTY OF go_application 'ACTIVESHEET' = go_worksheet.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_Sheet_header
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_Sheet_header USING us_NAME1 us_FKDAT.
gs_lines-value = us_NAME1. APPEND gs_lines TO gt_lines.
PERFORM frm_add_line2print_from_table.
gs_lines-value = us_FKDAT && '对账单'. APPEND gs_lines TO gt_lines.
PERFORM frm_add_line2print_from_table.
"日期
gs_lines-value = '日期'. APPEND gs_lines TO gt_lines.
PERFORM frm_add_line2print_from_table.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_sheet_body
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_sheet_body USING us_s_alv TYPE ty_alv.
DATA:lv_len TYPE i.
lv_len = strlen( us_s_alv-zchar ).
"日期
IF lv_len = 10.
gs_lines-value = us_s_alv-zchar. APPEND gs_lines TO gt_lines.
ELSE.
gs_lines-value = ''. APPEND gs_lines TO gt_lines.
ENDIF.
PERFORM frm_add_line2print_from_table.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_sheet_tail
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_sheet_tail USING us_bukrs_name us_NAME1 us_datum.
DATA: lo_columns1 TYPE ole2_object.
us_datum = us_datum + 1.
gs_lines-value = '尊敬的客户：'. APPEND gs_lines TO gt_lines.
PERFORM frm_add_line2print_from_table.
CALL METHOD OF go_application 'COLUMNS' = lo_columns1
EXPORTING
#1 = 2.
* Copy-paste the data from cell A1 从单元格复制粘贴数据
PERFORM frm_paste_clipboard USING 1 1.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_sheet_style
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_sheet_style USING us_body_row us_sheet_name.
CONSTANTS:
lv_center TYPE i VALUE -4108,
lv_left TYPE i VALUE -4131,
lv_right TYPE i VALUE -4152.
DATA:lv_sum_row TYPE i,
lv_tail_row TYPE i.
"总行数 = 表标题行数 + 表身行数 + 表尾行数
lv_sum_row = 3 + us_body_row + 6.
lv_tail_row = 3 + us_body_row + 1.
* Set the width to the second column 设置列宽
PERFORM frm_column_width USING 1 '10'.
* 设置行高 开始 行 列 结束 行 列
PERFORM frm_cells_height USING 1 1 2 15 '23'.
*Selecting cell area to be merged 合并单元格
* 合并开始的行 列 结束行 列
PERFORM frm_selecting_merged USING 1 1 1 15.
* Bold the header: 加粗标题
PERFORM frm_change_format USING 1 1 2 15 "Range of cells 单元格范围
0 space "Font Colour 字体颜色
0 space "Background Colour 背景颜色
20 'X' "Size 字体大小
1 'X'. "Bold 字体加粗
* Align centered the two first columns of the item table 居中
" 开始 行 列 结束 行 列
PERFORM frm_align_cells USING 1 1 1 15 lv_center.
* 自动换行
"开始 行 列 结束 行 列
* PERFORM frm_cells_format USING 9 2 9 2 0 1.
*按照单元格大小缩放字体大小,自动换行
"开始 行 列 结束 行 列
* PERFORM frm_cells_format USING 1 6 1 6 0 1.
* Add borders 添加边框
"开始 行 列 结束 行 列
PERFORM frm_add_border USING 1 1 lv_sum_row 15.
* 下线框
"开始 行 列 结束 行 列
* PERFORM frm_add_border_edge USING 1 1 1 15.
* 外边框
PERFORM frm_add_border_luter USING 1 1 lv_sum_row 15.
* 隐藏列
* PERFORM frm_column_hidden USING 'O:P' 1.
** Change the name of the worksheet:更改工作表的名称
SET PROPERTY OF go_worksheet 'Name' = us_sheet_name .
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_close_document
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_close_document .
CALL METHOD OF go_workbooks 'CLOSE'.
CALL METHOD OF go_application 'QUIT'.
FREE OBJECT go_worksheet.
FREE OBJECT go_workbook.
FREE OBJECT go_workbooks.
FREE OBJECT go_application.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_column_width
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> P_1
*& --> P_
*&---------------------------------------------------------------------*
FORM frm_column_width USING us_column TYPE i
us_width TYPE p.
DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_column TYPE ole2_object.
* Select the Column
CALL METHOD OF go_worksheet 'Columns' = lo_column
EXPORTING
#1 = us_column.
CALL METHOD OF lo_column 'select'.
CALL METHOD OF go_application 'selection' = lo_selection.
SET PROPERTY OF lo_column 'ColumnWidth' = us_width.
ENDFORM. "Column_width
*&---------------------------------------------------------------------*
*& Form frm_cells_height
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> P_1
*& --> P_1
*& --> P_2
*& --> P_15
*& --> P_
*&---------------------------------------------------------------------*
FORM frm_cells_height USING us_rowini us_colini
us_rowend us_colend
us_height TYPE p.
DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object.
* Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
#1 = us_rowini
#2 = us_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = us_rowend
#2 = us_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
* Format:
CALL METHOD OF lo_range 'select'.
SET PROPERTY OF lo_range 'RowHeight' = us_height ."行高
ENDFORM. "行高
*&---------------------------------------------------------------------*
*& Form frm_selecting_merged
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> P_1
*& --> P_1
*& --> P_1
*& --> P_15
*&---------------------------------------------------------------------*
FORM frm_selecting_merged USING VALUE(us_brow)
VALUE(us_bcloumn)
VALUE(us_erow)
VALUE(us_ecloumn).
CALL METHOD OF
go_application
'Cells' = gs_cell1
EXPORTING
#1 = us_erow"合并结束行
#2 = us_bcloumn."合并起始列
CALL METHOD OF
go_application
'Cells' = gs_cell2
EXPORTING
#1 = us_brow "合并起始行
#2 = us_ecloumn."合并结束列
CALL METHOD OF
go_application
'Range' = gs_cells
EXPORTING
#1 = gs_cell1
#2 = gs_cell2.
CALL METHOD OF
gs_cells
'Select'.
*--Merging 开始合并中
CALL METHOD OF
gs_cells
'Merge'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_change_format
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> P_1
*& --> P_1
*& --> P_2
*& --> P_2
*& --> P_0
*& --> SPACE
*& --> P_0
*& --> SPACE
*& --> P_12
*& --> P_
*& --> P_1
*& --> P_
*&---------------------------------------------------------------------*
FORM frm_change_format USING us_rowini us_colini
us_rowend us_colend
us_colour TYPE i
us_colourx TYPE char1
us_bkg_col TYPE i
us_bkg_colx TYPE char1
us_size TYPE i
us_sizex TYPE char1
us_bold TYPE i
us_boldx TYPE char1.
DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object,
lo_font TYPE ole2_object,
lo_interior TYPE ole2_object.
* Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
#1 = us_rowini
#2 = us_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = us_rowend
#2 = us_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
* Format:
CALL METHOD OF lo_range 'FONT' = lo_font.
SET PROPERTY OF lo_font 'NAME' = 'Times New Roman'.
* Colour:
IF us_colourx EQ 'X'.
SET PROPERTY OF lo_font 'ColorIndex' = us_colour.
ENDIF.
* Background colour;
IF us_bkg_colx EQ 'X'.
CALL METHOD OF lo_range 'Interior' = lo_interior.
SET PROPERTY OF lo_interior 'ColorIndex' = us_bkg_col.
ENDIF.
* Size
IF us_sizex EQ 'X'.
SET PROPERTY OF lo_font 'SIZE' = us_size.
ENDIF.
* Bold
IF us_boldx EQ 'X'.
SET PROPERTY OF lo_font 'BOLD' = us_bold.
ENDIF.
ENDFORM. "文字调整
*&---------------------------------------------------------------------*
*& Form frm_align_cells
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> P_1
*& --> P_1
*& --> P_2
*& --> P_15
*& --> LV_CENTER
*&---------------------------------------------------------------------*
FORM frm_align_cells USING us_rowini us_colini
us_rowend us_colend
us_align.
DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object.
* Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
#1 = us_rowini
#2 = us_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = us_rowend
#2 = us_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'select'.
SET PROPERTY OF lo_range 'HorizontalAlignment' = us_align.
ENDFORM. "align_cells
*&---------------------------------------------------------------------*
*& Form frm_cells_format
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> P_9
*& --> P_2
*& --> P_9
*& --> P_2
*& --> P_0
*& --> P_1
*&---------------------------------------------------------------------*
FORM frm_cells_format USING us_rowini us_colini
us_rowend us_colend
us_fit TYPE i
us_wrap TYPE i.
DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object.
* Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
#1 = us_rowini
#2 = us_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = us_rowend
#2 = us_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
* Format:
CALL METHOD OF lo_range 'select'.
SET PROPERTY OF lo_range 'WrapText' = us_wrap ."自动换行
SET PROPERTY OF lo_range 'ShrinkToFit' = us_fit ."缩小字体填充
ENDFORM. "单元格调整
*&---------------------------------------------------------------------*
*& Form frm_add_border
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> P_1
*& --> P_2
*& --> P_20
*& --> P_15
*&---------------------------------------------------------------------*
FORM frm_add_border USING us_rowini us_colini
us_rowend us_colend.
DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object,
lo_borders TYPE ole2_object.
* Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
#1 = us_rowini
#2 = us_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = us_rowend
#2 = us_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'Borders' = lo_borders
EXPORTING #1 = '7'. "xlEdgeLeft
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders
EXPORTING #1 = '8'. "xlEdgeTop
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders
EXPORTING #1 = '9'. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders
EXPORTING #1 = '10'. "xlEdgeRight
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders
EXPORTING #1 = '11'. "xlInsideVertical
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders
EXPORTING #1 = '12'. "xlInsideHorizontal
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
ENDFORM. "Add Border
*&---------------------------------------------------------------------*
*& Form frm_add_border_edge
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> P_1
*& --> P_1
*& --> P_1
*& --> P_15
*&---------------------------------------------------------------------*
FORM frm_add_border_edge USING us_rowini us_colini
us_rowend us_colend.
DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object,
lo_borders TYPE ole2_object.
* Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
#1 = us_rowini
#2 = us_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = us_rowend
#2 = us_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'Borders' = lo_borders
EXPORTING #1 = '9'. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
ENDFORM. "emove the inner border and add the outer border
*&---------------------------------------------------------------------*
*& Form frm_add_border_luter
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> P_1
*& --> P_1
*& --> P_1
*& --> P_15
*&---------------------------------------------------------------------*
FORM frm_add_border_luter USING us_rowini us_colini
us_rowend us_colend.
DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object,
lo_borders TYPE ole2_object.
* Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
#1 = us_rowini
#2 = us_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = us_rowend
#2 = us_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.
CALL METHOD OF lo_range 'Borders' = lo_borders
EXPORTING #1 = '7'. "xlEdgeLeft
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders
EXPORTING #1 = '8'. "xlEdgeTop
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders
EXPORTING #1 = '9'. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_range 'Borders' = lo_borders
EXPORTING #1 = '10'. "xlEdgeRight
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
ENDFORM. "emove the inner border and add the outer border
*&---------------------------------------------------------------------*
*& Form frm_column_hidden
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> P_
*& --> P_1
*&---------------------------------------------------------------------*
FORM frm_column_hidden USING us_column
us_true TYPE i.
DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_column TYPE ole2_object.
* Select the Column
CALL METHOD OF go_worksheet 'Columns' = lo_column
EXPORTING
#1 = us_column.
CALL METHOD OF lo_column 'Select'.
CALL METHOD OF go_application 'selection' = lo_selection.
SET PROPERTY OF lo_column 'Hidden' = us_true.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_add_document
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_add_document USING us_snumber.
DATA: l_sheenumber(20).
CONCATENATE 'Sheet' us_snumber INTO l_sheenumber.
CONDENSE l_sheenumber NO-GAPS.
CALL METHOD OF go_workbook 'Sheets' = go_worksheets.
CALL METHOD OF go_worksheets 'Add' = go_newsheet.
CALL METHOD OF go_workbook 'Worksheets' = go_worksheet
EXPORTING #1 = l_sheenumber.
CALL METHOD OF go_worksheet 'Activate'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_add_line2print_from_table
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_add_line2print_from_table .
CLEAR gs_excel_data.
LOOP AT gt_lines INTO gs_lines.
CONCATENATE gs_excel_data gs_lines-value INTO gs_excel_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
ENDLOOP.
* 推出第一个选项卡:
SHIFT gs_excel_data BY 1 PLACES LEFT.
APPEND gs_excel_data TO gt_excel_data.
CLEAR: gs_excel_data,gt_lines[].
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_paste_clipboard
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> P_1
*& --> P_1
*&---------------------------------------------------------------------*
FORM frm_paste_clipboard USING us_row TYPE i
us_col TYPE i.
DATA: lo_cell TYPE ole2_object.
* Copy to clipboard into ABAP
CALL FUNCTION 'CONTROL_FLUSH'
EXCEPTIONS
OTHERS = 3.
CALL FUNCTION 'CLPB_EXPORT'
TABLES
data_tab = gt_excel_data
EXCEPTIONS
clpb_error = 1
OTHERS = 2.
* Select the cell A1
CALL METHOD OF go_worksheet 'Cells' = lo_cell
EXPORTING
#1 = us_row
#2 = us_col.
* Paste clipboard from cell A1
CALL METHOD OF lo_cell 'SELECT'.
CALL METHOD OF go_worksheet 'PASTE'.
ENDFORM. " PASTE_CLIPBOARD
*&---------------------------------------------------------------------*
*& Form call_BKK_GET_MONTH_LASTDAY
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> LV_DATUM
*& <-- CH_DATUM
*&---------------------------------------------------------------------*
FORM call_BKK_GET_MONTH_LASTDAY USING us_lv_datum
CHANGING ch_ex_datum.
"返回输入日期最后一天 BKK_GET_MONTH_LASTDA
CALL FUNCTION 'BKK_GET_MONTH_LASTDAY'
EXPORTING
i_date = us_lv_datum
IMPORTING
e_date = ch_ex_datum.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_get_fpath
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& <-- LV_FPATH
*&---------------------------------------------------------------------*
FORM frm_get_fpath CHANGING ch_ex_fpath.
*--> 路径变量
DATA: lv_fpath TYPE string, " file path
lv_len TYPE i. " file path length
CALL METHOD cl_gui_frontend_services=>directory_browse
CHANGING
selected_folder = lv_fpath
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
* -最後一個字元不為'\'，補上'\'-
IF lv_fpath IS NOT INITIAL.
lv_len = strlen( lv_fpath ) - 1.
IF lv_fpath+lv_len(1) NE '\'.
ch_ex_fpath = lv_fpath && |\\|.
ELSE.
ch_ex_fpath = lv_fpath.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_open_document
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_open_document .
CREATE OBJECT go_application 'EXCEL.APPLICATION'. "Create EXCEL OBJECT
IF sy-subrc NE 0.
EXIT.
ENDIF.
SET PROPERTY OF go_application 'Visible' = 0. "1/0 是否显示EXCEL
CALL METHOD OF go_application 'Workbooks' = go_workbook.
CALL METHOD OF go_workbook 'Open'
EXPORTING
#1 = gv_destination. ."打开上面下载路径下的excel文件
CALL METHOD OF go_application 'Sheets' = go_worksheet
EXPORTING
#1 = 1.
CALL METHOD OF go_worksheet 'Select' .
CALL METHOD OF go_worksheet 'ACTIVATE'. "sheet **
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_download_excel
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_download_excel2.
GET PROPERTY OF go_application 'ActiveSheet' = go_worksheet. "获取活动SHEET
FREE OBJECT go_worksheet.
FREE OBJECT go_workbook.
GET PROPERTY OF go_application 'ActiveWorkbook' = go_workbook.
CALL METHOD OF go_workbook 'SAVE'.
SET PROPERTY OF go_application 'Visible' = 0. "是否显示EXCEL 此处显示不退出
CALL METHOD OF go_workbook 'CLOSE'.
CALL METHOD OF go_application 'QUIT'. "注释部分为不显示直接退出
FREE OBJECT go_worksheet.
FREE OBJECT go_workbook.
FREE OBJECT go_application.
* Close the document and free memory 关闭并释放
* PERFORM frm_close_document.
ENDFORM.
