report y_deal_Excel.
data:application type ole2_object, "excel object
workbook type ole2_object, "excel workbook objcet
sheet type ole2_object, "workbook SHEET object
columns type ole2_object, "sheet col objcet
rows type ole2_object, "sheet row objcet
range type ole2_object, "range
range1 type ole2_object, "range1
font type ole2_object, "font
cell type ole2_object, "cell
cell1 type ole2_object,
gs_interior type ole2_object , "cell1
sheet1 type ole2_object, "workbook sheet object
borders type ole2_object, "borders
pictures type ole2_object ,
lc_file type string ,
gv_line_cntr type i ."EXCEL 下载后的放置路径
start-of-selection .
"下载Excel
perform frm_down_excel using 'ZBOM' 'EXCEL操作总结' changing lc_file. "ZBOM为SMW0 的模板名称 EXCEL操作总结为下载后的命名
" 创建excel应用程序
perform create_application.
"打开Excel 第一个页签
perform open_excel1 using lc_file 1 'Sheet2'."Sheet2表示在哪一个页签上填写内容 ，这里实在sheet2上添加内容
" 调整列宽
perform frm_tzlk.
" 添加数据
perform frm_tj_data .
" 添加数据后直接保存
perform frm_close_excel .
*&---------------------------------------------------------------------*
*& Form FRM_DOWN_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_TEXT_020 text
* -->P_TEXT_021 text
* <--P_LC_FILE text
*----------------------------------------------------------------------*
form frm_down_excel using excel_name i_file changing lc_file .
data: o_succ type char01 ,
lc_result type c , "标识: 空表示文件不存在;否则,表示存在 I_FILE
ls_key type wwwdatatab,
lc_dest type localfile, "目标文件,
lv_subrc type sy-subrc.
concatenate 'C:\TEMP1\' i_file '.xlsx' into lc_file .
******************下载模板代码***************************************************
" 这里我们自动创建一个EXCEL 然后进行处理
* clear lc_result.
* o_succ = 'X'.
* ls_key-relid = 'MI'.
* ls_key-objid = excel_name . "下载服务器Excel 名称
* concatenate i_file '.xlsx' into lc_dest."文件地址 默认桌面
* lc_dest = lc_file .
*
*
*
* call function 'DOWNLOAD_WEB_OBJECT'
* exporting
* key = ls_key
* destination = lc_dest
* importing
* rc = lv_subrc.
*
* if lv_subrc <> 0.
** MESSAGE'模板文件不存在或文件路径错误' TYPE 'S' DISPLAY LIKE 'E'.
* message text-031 type 'S' display like 'E'.
* exit.
* endif.
******************下载模板代码***************************************************
endform.
*&---------------------------------------------------------------------*
*& Form CREATE_APPLICATION
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form create_application .
if application-handle le 0.
create object application 'EXCEL.APPLICATION'.
endif.
endform.
form open_excel1 using lc_file
value(pv_visible) type int1
p_sheet_name type string.
data: gs_cell1 type ole2_object,
gs_cell2 type ole2_object,
gs_cells type ole2_object,
gs_excel type ole2_object.
"1前台运行。为0时表示为后台运行。
set property of application 'Visible' = 1."PV_VISIBLE."1 为打开Excel
call method of application 'Workbooks' = workbook.
"创建EXCEL
call method of workbook 'Add' = workbook .
*"打开Excel
* call method of workbook 'Open' = workbook
* exporting
* #1 = p_file.
"创建一个页签 没有在那个sheet 上写内容的代码的话就会在新增的页签上填写内容
call method of application 'sheets' = sheet .
call method of sheet 'Add' .
* "在哪一个sheet上填写内容
* call method of workbook 'Worksheets' = sheet
* exporting
* #1 = p_sheet_name.
"激活
call method of sheet 'Activate'.
set property of sheet 'NAME' = '库存物料表'.
endform .
*&---------------------------------------------------------------------*
*& Form FRM_TZLK
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form frm_tzlk .
* 第一列
call method of application 'COLUMNS' = columns
exporting
#1 = 1. "the column number 列数 第1列
set property of columns 'ColumnWidth' = 20. "宽度
set property of columns 'NumberFormat' = '@'.
*第二例
call method of application 'COLUMNS' = columns
exporting
#1 = 2. "the column number 列数 第二列
set property of columns 'ColumnWidth' = 39. "宽度30
set property of columns 'NumberFormat' = '@'."将数字以文本形式显示
endform.
*&---------------------------------------------------------------------*
*& Form FRM_TJ_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form frm_tj_data .
data: num type i .
gv_line_cntr = 1.
"* 合并单元格
perform hb_excel using 1 2.
* 加边框
perform jbk_excel using 1 1 ."开始行 结束行
* 加边框
perform jbk_excel using 15 15 ."开始行 结束行
"填入数据
perform fill_cell using gv_line_cntr 1 1 '文本标题FGHJ'."行 列 字体粗细 内容
* 加边框
perform jbk_excel using 3 3 ."开始行 结束行
"填入数据
gv_line_cntr = gv_line_cntr 2 .
perform fill_cell using gv_line_cntr 1 1 '123456789123456789123456789123456789'."行 列 字体粗细 内容
"切换sheet
call method of application 'Worksheets' = sheet
exporting #1 = 'sheet3'.
call method of sheet 'Activate'.
"这里 gv_line_cntr = 3
perform fill_cell using gv_line_cntr 1 1 '从第创建的SHEET4写完内容后切换到SHEET3继续写内容'."行 列 字体粗细 内容
"插入行
num = 2 .
do 5 times.
num = num 1 .
perform insert using num."插入第xx行
perform fill_cell using num 5 1 '在插入行填写内容'."行 列 字体粗细 内容
enddo.
perform fill_cell1 using 1 1 1 'copy测试'."行 列 字体粗细 内容
"复制选中区域
perform frm_copy using 1 1 . "开始行 结束行
"粘贴到知道区域 call method of sheet 'paste'.
perform frm_paste using 12 12 ."开始行 结束行
"下拉框
perform frm_list .
"删除 sheet1
call method of application 'Worksheets' = sheet
exporting #1 = 'sheet1'.
call method of sheet 'Delete'.
endform.
*&---------------------------------------------------------------------*
*& Form FRM_CLOSE_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form frm_close_excel .
get property of application 'ACTIVESHEET' = sheet."激活工作簿
get property of application 'ACTIVEWORKBOOK' = workbook."激活工作区
call method of workbook 'SaveAs'
exporting
#1 = lc_file.
* call method of workbook 'Save'.
"保存Excel后关闭并退出EXCEL
* call method of workbook 'CLOSE'. "关闭工作区
* call method of application 'QUIT'."退出excel
"释放资源
free object workbook.
free object sheet.
free object application.
endform.
form hb_excel using gv_line_cntr_star type i
gv_line_cntr_end type i .
data: gs_cell1 type ole2_object,
gs_cell2 type ole2_object,
gs_cells type ole2_object,
gs_excel type ole2_object.
call method of application 'Cells' = gs_cell1
exporting
#1 = gv_line_cntr_star "开始行
#2 = 1."开始列
call method of application 'Cells' = gs_cell2
exporting
#1 = gv_line_cntr_end"结束行
#2 = 3."结束列
call method of application 'Range' = gs_cells
exporting
#1 = gs_cell1
#2 = gs_cell2.
call method of gs_cells 'Select' .
"--Merging合并
call method of gs_cells 'Merge' .
"清除range 内容
call method of range 'ClearContents'.
endform .
form jbk_excel using gv_line_cntr_star type i
gv_line_cntr_end type i .
data: gs_cell1 type ole2_object,
gs_cell2 type ole2_object,
gs_cells type ole2_object,
gs_excel type ole2_object.
call method of application 'Cells' = gs_cell1
exporting
#1 = gv_line_cntr_star "开始行
#2 = 1."开始列
call method of application 'Cells' = gs_cell2
exporting
#1 = gv_line_cntr_end"结束行
#2 = 3."结束列
call method of application 'Range' = gs_cells
exporting
#1 = gs_cell1
#2 = gs_cell2.
call method of gs_cells 'Select' .
call method of gs_cells 'BorderAround'
exporting
#1 = 1 "continuous line
#2 = 2. "thick
call method of gs_cells 'BORDERS'= borders
exporting
#1 = '1'.
set property of borders 'Linestyle' = 1 .
set property of borders 'Weight' = -1 .
"清除range 内容
call method of range 'ClearContents'.
endform .
form fill_cell using i_row i_col bold p_value.
data: lo_interior type ole2_object.
check p_value ne '0'.
call method of application 'CELLS' = cell
exporting
#1 = i_row
#2 = i_col.
set property of cell 'VALUE' = p_value."内容
get property of cell 'FONT' = font.
set property of font 'SIZE' = 18.
set property of font 'BOLD' = bold."是否是粗体
set property of cell 'HorizontalAlignment' = 3."居中 2 左 4 右
"字体颜色
set property of font 'Color' = -16776961.
set property of font 'TintAndShade' = 0.
set property of font 'Italic' = 1."斜体
set property of font 'Underline' = 2. "下划线
"单元格颜色
call method of cell 'Interior' = lo_interior.
set property of lo_interior 'Color' = 15773696.
endform.
form fill_cell1 using i_row i_col bold p_value.
data: lo_interior type ole2_object.
check p_value ne '0'.
call method of application 'CELLS' = cell
exporting
#1 = i_row
#2 = i_col.
set property of cell 'VALUE' = p_value."内容
get property of cell 'FONT' = font.
set property of font 'SIZE' = 10.
set property of font 'BOLD' = bold."是否是粗体
set property of cell 'HorizontalAlignment' = 3."居中 2 左 4 右
endform .
*&---------------------------------------------------------------------*
*& Form INSERT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_2 text
*----------------------------------------------------------------------*
form insert using gv_line_cntr type i.
data: gs_cells type ole2_object .
call method of application 'ROWS' = gs_cells
exporting
#1 = gv_line_cntr.
call method of gs_cells 'Insert'. "插入一行
endform .
*&---------------------------------------------------------------------*
*& Form FRM_COPY
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_3 text
*----------------------------------------------------------------------*
form frm_copy using gv_line_cntr_star type i
gv_line_cntr_end type i .
data: gs_cell1 type ole2_object,
gs_cell2 type ole2_object,
gs_cells type ole2_object,
gs_excel type ole2_object.
call method of application 'Cells' = gs_cell1
exporting
#1 = gv_line_cntr_star "开始行
#2 = 1."开始列
call method of application 'Cells' = gs_cell2
exporting
#1 = gv_line_cntr_end"结束行
#2 = 1."结束列
call method of application 'Range' = gs_cells
exporting
#1 = gs_cell1
#2 = gs_cell2.
call method of gs_cells 'Select' ."生成区域
call method of gs_cells 'Copy' .
"清除range 内容
call method of range 'ClearContents'.
endform .
*&---------------------------------------------------------------------*
*& Form FRM_PASTE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_12 text
* -->P_12 text
*----------------------------------------------------------------------*
form frm_paste using gv_line_cntr_star type i
gv_line_cntr_end type i .
data: gs_cell1 type ole2_object,
gs_cell2 type ole2_object,
gs_cells type ole2_object,
gs_excel type ole2_object.
call method of application 'Cells' = gs_cell1
exporting
#1 = gv_line_cntr_star "开始行
#2 = 8."开始列
call method of application 'Cells' = gs_cell2
exporting
#1 = gv_line_cntr_end"结束行
#2 = 8."结束列
call method of application 'Range' = gs_cells
exporting
#1 = gs_cell1
#2 = gs_cell2.
call method of gs_cells 'Select' .
call method of gs_cells 'PasteSpecial' .
"清除range 内容
call method of range 'ClearContents'.
endform .
*&---------------------------------------------------------------------*
*& Form FRM_LIST
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form frm_list .
data: lv_range_name type char24 value 'Values'.
data: gs_cell1 type ole2_object,
gs_cell2 type ole2_object,
gs_cells type ole2_object,
lo_selection type ole2_object ,
lo_validation type ole2_object .
* Fill the cells with the values;
data: lv_row type i,
lv_cont(4) type n value '0040',
lv_num(4),
lv_char.
* Go to sheet1 跳转到那一页签
"将sheet1 上的1-7行 10 列 的数据作为下拉框在sheet2
"1行 3 位置进行输出
call method of application 'Worksheets' = sheet
exporting #1 = 'sheet4'.
call method of sheet 'Activate'.
do 7 times.
add 1 to: lv_cont, lv_row.
call method of sheet 'Cells' = gs_cells
exporting
#1 = lv_row "Row 行
#2 = 10. "Column 列
* Convert num to ascii
lv_num = lv_cont.
lv_char = cl_abap_conv_in_ce=>uccp( lv_num )."将数字转换为字母
set property of gs_cells 'Value' = lv_char.
enddo.
* Select the range and set a name;
* 1. Select starting cell
call method of sheet 'Cells' = gs_cell1
exporting
#1 = 1"
#2 = 10.
* 2. Select ending cell
call method of sheet 'Cells' = gs_cell2
exporting
#1 = lv_cont "Row
#2 = 10.
* Select the Range:
call method of sheet 'RANGE' = gs_cells
exporting
#1 = gs_cell1
#2 = gs_cell2.
call method of gs_cells 'select'.
* Set a name to this Range
set property of gs_cells 'Name' = lv_range_name.
call method of application 'Worksheets' = sheet
exporting #1 = 'sheet2'.
call method of sheet 'Activate'.
* Select the cell A1
call method of sheet 'Cells' = gs_cells
exporting
#1 = 1 "Row
#2 = 3. "Column
call method of gs_cells 'select'.
call method of application 'selection' = lo_selection.
call method of lo_selection 'Validation' = lo_validation.
concatenate '=' lv_range_name into lv_range_name.
call method of lo_validation 'Add'
exporting
#1 = 3 "'xlValidateList'
#2 = 1 "'xlValidAlertStop'
#3 = 1 "'xlBetween'
#4 = lv_range_name.
"清除range 内容
call method of range 'ClearContents'.
endform.
Copyright © 2024 妖气游戏网 www.17u1u.com All Rights Reserved
