有时候需要读取EXCEL或者是写入EXCEL
CALL
FUNCTION
'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename
=
p_file
i_begin_col
=
'1'
"这俩参数表示从第二行的第一列开始读,因为第一行是标题
i_begin_row
=
'2'
i_end_col
=
'4'
"读到第4列
i_end_row
=
'10000'
TABLES
intern
=
gt_data .
"数据
"将数据转换成一般处理内表结构的数据
LOOP
AT
gt_data .
CASE
gt_data -col .
"列
WHEN
1 .
l_wa_upload - no
=
gt_data - value .
"第一列的值,
WHEN
2 .
l_wa_upload -bukrs
=
gt_data - value .
"第二列的值,
WHEN
3 .
l_wa_upload -gjahr
=
gt_data - value .
WHEN
4 .
l_wa_upload -user
=
gt_data - value .
APPEND
l_wa_upload
TO
g_it_upload .
"将转换结构的数据保存到内表
WHEN
OTHERS .
ENDCASE .
ENDLOOP .
ENDFORM .
DATA:
p_file
TYPE
ibipparms-path.
DATA:
lc_filename
TYPE
string
VALUE
'zbkt_accout',
"默认下载名称
lc_fullpath
TYPE
string
VALUE
'D:\'
,
lc_path
TYPE
string
VALUE
'D:\'
,
ls_destination
LIKE
rlgrap-filename.
DATA:
l_wa_data
LIKE
str_account_infor.
DATA:
row
TYPE
int4.
"1、创建excel对象
CREATE
OBJECT
excel
'EXCEL.APPLICATION'.
"Create
EXCEL
OBJECT
IF
sy-subrc
NE
0.
EXIT.
ENDIF.
"2、创建工作薄
CALL
METHOD
OF
excel
'Workbooks'
=
workbook.
"3、打开工作薄
CALL
METHOD
OF
workbook
'Open'
EXPORTING
#1
=
p_file.
"打开上面下载路径下的excel文件
"4、创建sheet
CALL
METHOD
OF
excel
'Sheets'
=
sheet
EXPORTING
#1
=
1.
CALL
METHOD
OF
sheet
'Select'.
"5、sheet 激活
CALL
METHOD
OF
sheet
'ACTIVATE'.
"设定sheet名称
SET
PROPERTY
OF
sheet
'NAME'
= 'sheet名字'.
"6、开始填充数据
"填充头行
PERFORM
fill_range
USING
2
6
sy-uname.
PERFORM
fill_range
USING
2
8
sy-datum.
"填充数据行
LOOP
AT
g_it_account
INTO
l_wa_data.
row
=
sy-tabix
+
4.
"从地5行开始的
"填充所插入行的每个单元格的数据
PERFORM
fill_range
USING
row
1
l_wa_data-belnr.
"row行,第1列的值是
l_wa_data
-
belnr
PERFORM
fill_range
USING
row
2
l_wa_data-bukrs.
PERFORM
fill_range
USING
row
3
l_wa_data-gjahr.
ENDLOOP.
SET
PROPERTY
OF
excel
'Visible'
=
1.
"1显示EXCEL 0不显示
"7、保存工作薄
CALL
METHOD
OF
workbook
'SAVEAS'
"saveas是另存为
p_file
EXPORTING
#1
=
p_file
#2
=
1.
"8、关闭sheet
CALL
METHOD
OF
sheet
'CLOSE'.
"9、关闭工作薄
CALL
METHOD
OF
workbook
'CLOSE'.
"10、退出excel
CALL
METHOD
OF
excel
'QUIT'.
"11、释放
FREE
OBJECT
cell.
FREE
OBJECT
sheet.
FREE
OBJECT
workbook.
FREE
OBJECT
excel.
1、将EXCEL数据写入内表:
ALSM_EXCEL_TO_INTERNAL_TABLE
假设EXCEL有4列,
"获取文件地址
SELECTION-SCREEN
BEGIN
OF
BLOCK
b1
WITH
FRAME
TITLE
text - 001 . .
PARAMETER :p_file
TYPE
ibipparms -path
DEFAULT
'C:\Users\pc\Desktop\上传模板.xlsx' .
SELECTION-SCREEN END
OF
BLOCK
b1 .
AT SELECTION-SCREEN
ON
VALUE-REQUEST
FOR
p_file .
CALL
FUNCTION
'F4_FILENAME'
IMPORTING
file_name
=
p_file .
PARAMETER :
SELECTION-SCREEN
AT
"将数据读入内表
FORM
upload_itab
.
"接收excel数据的内表
DATA :
gt_data
TYPE
alsmex_tabline
OCCURS
0
WITH
HEADER
LINE .
ENDFORM .
2、将内表写入EXCEL
*&---------------------------------------------------------------------*
*&EXCEL相关定义
*&---------------------------------------------------------------------*
DATA:
excel
TYPE
ole2_object,
workbook
TYPE
ole2_object,
sheet
TYPE
ole2_object,
cell
TYPE
ole2_object.
*&EXCEL相关定义
*&---------------------------------------------------------------------*
其中,填充数据的FORM
FORM
fill_range
USING
value (f_row )
value (f_col )
value (f_value ) .
DATA :
row
TYPE
i ,
col
TYPE
i .
row
=
f_row .
col
=
f_col .
CALL
METHOD
OF
excel
'CELLS'
=
cell
EXPORTING
#1
=
row
#2
=
col .
SET
PROPERTY
OF
cell
'VALUE'
=
f_value .
ENDFORM .
"fill_range
ENDFORM .
最后又个问题,我一直没解决掉,
就是执行后,一直会有个excel在资源管理器中打开
开始的时候,我设置
SET
PROPERTY
OF
excel
'Visible'
=
0
.
这样执行完了不显示打开的excel,但是他却在后台打开了。
一直执行,一直会在资源管理器中打开excel,
如果谁有解决办法,请留言,谢谢!
暂时我只能是数据到excel后,打开显示,手动保存关闭。然后资源管理器中就不再有了。