EXCEL和内表的简单数据交互
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.
“接收excel数据的内表
DATA: gt_data TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE.
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.
*&EXCEL相关定义
*&———————————————————————*
DATA: excel TYPE ole2_object,
workbook TYPE ole2_object,
sheet TYPE ole2_object,
cell TYPE ole2_object.
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.
CREATE OBJECT excel \’EXCEL.APPLICATION\’. “Create EXCEL OBJECT
IF sy–subrc NE 0.
EXIT.
ENDIF.
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\’.
“设定sheet名称
SET PROPERTY OF sheet \’NAME\’ =
\’sheet名字\’.
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\’.
CALL METHOD OF
excel
\’QUIT\’.
FREE OBJECT cell.
FREE OBJECT sheet.
FREE OBJECT workbook.
FREE OBJECT excel.
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