转 abap 用excel将alv上展示的数据导出（基本知识）
abap 用excel将alv上展示的数据导出（基本知识）
转自：http://blog.sina.com.cn/s/blog_98dd7e1101013crl.html
form user_command using r_ucomm like sy-ucomm
rs_selfield type slis_selfield .
case r_ucomm.
when 'EX'.（alv菜单栏上的按钮）
perform excel_data.
endcase.
endform.
*&-------------------------------------------------------------------
form excel_data .
perform def_excel.
perform sho_excel.
endform. " EXECL_DATA
*--------------------------------------------------------------------
form def_excel .
（以下的代码是必须写的，比较固定，用的时候直接写就行）
define fill_cell.
call method of v_excel 'CELLS' = v_cell "单元格位置
exporting
#1 = &1
#2 = &2.
set property of v_cell 'value' = &3.
call method of v_cell 'FONT' = v_font.
set property of v_font 'Bold' = &4.
set property of v_font 'size' = &5.
end-of-definition.
create object v_excel 'Excel.Application'.
call method of
v_excel
'Workbooks' = v_book.
call method of
v_book
'ADD'.
*给第一行填充要写的东西，一下的第一个‘1’表示第一行，第二个‘1’表示第一列。
fill_cell 1 1 '生产情况形象进度统计表' 1 20.
set property of v_cell 'HorizontalAlignment' = -4108.（这句是居中）
set property of v_font 'UNDERLINE' = 2.（下划线）
以下代码主要作用是定义第二行各列和第三行各列、第四行各列：
fill_cell 2 1 '' 1 10.
fill_cell 2 2 '' 1 10.
fill_cell 2 3 '' 1 10.
fill_cell 2 4 '' 1 10.
fill_cell 2 5 '' 1 10.
fill_cell 2 6 '' 1 10.
fill_cell 2 7 '统计时间' 1 10.
fill_cell 2 8 '' 1 10.
fill_cell 2 9 '' 1 10.
fill_cell 2 10 '' 1 10.
* fill_cell 3 10 '' 1 10.
fill_cell 2 11 '' 1 10.
fill_cell 2 12 '' 1 10.
fill_cell 2 13 '' 1 10.
fill_cell 2 14 '' 1 10.
fill_cell 3 1 '' 1 10.
fill_cell 3 2 '' 1 10.
fill_cell 3 3 '' 1 10.
fill_cell 3 4 '' 1 10.
fill_cell 3 5 '' 1 10.
fill_cell 3 6 '' 1 10.
fill_cell 3 7 '' 1 10.
fill_cell 3 8 '' 1 10.
fill_cell 3 9 '' 1 10.
fill_cell 3 10 '编号' 1 10.
* fill_cell 3 10 '' 1 10.
fill_cell 3 11 ex_itab-num1 1 10.
fill_cell 3 12 '' 1 10.
fill_cell 3 13 '' 1 10.
fill_cell 3 14 '' 1 10.
*
fill_cell 4 1 '序号' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
fill_cell 4 2 '工厂' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
fill_cell 4 3 '生产订单' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
fill_cell 4 4 '订单描述' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
fill_cell 4 5 '计划下达时间' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
fill_cell 4 6 '实际下达时间' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
fill_cell 4 7 '计划完工时间' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
fill_cell 4 8 '实际完工时间' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
fill_cell 4 9 '完工工时' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
fill_cell 4 10 '总工时' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
fill_cell 4 11 '完成进度' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
fill_cell 4 12 '计划资金' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
fill_cell 4 13 '进度资金' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
fill_cell 4 14 '备注' 1 10.
set property of v_cell 'HorizontalAlignment' = -4108.
*************先把第一行的所有列都合并****************************
call method of
v_excel
'Range' = v_range
exporting
#1 = 'A1'（这里的意思是从A1到N1这个范围合并）
#2 = 'N1'.
call method of
v_range
'Select'.
set property of v_range 'MergeCells' = 1.
**************************一行一列合并
call method of
v_excel
'COLUMNS' = v_column
exporting
#1 = 'E'."列编号
set property of v_column 'ColumnWidth' = 10.
set property of v_column 'NumberFormat' = '@'.
endform. " DEF_EXCEL
*&-------------------------------------------------------------------*--------------------------------------------------------------------
form sho_excel .
field-symbols : type any ,
type any.
data: lv_txt(50) type c.
data: row_i type i, 定义行和列
col_i type i.
*** 从内表循环数据，按顺序填到单元表中
loop at alv_itab.（将alv的数据传到为excel定义的内表中）
move alv_itab-num to ex_itab-num.
move alv_itab-werks to ex_itab-werks.
move alv_itab-aufnr to ex_itab-aufnr.
move alv_itab-maktx to ex_itab-maktx.
move alv_itab-ftrms to ex_itab-ftrms.
move alv_itab-ftrmi to ex_itab-ftrmi.
move alv_itab-gltrs to ex_itab-gltrs.
move alv_itab-idat2 to ex_itab-idat2.
move alv_itab-ism01 to ex_itab-ism01.
move alv_itab-vgw01 to ex_itab-vgw01.
move alv_itab-rate to ex_itab-rate.
move alv_itab-jhzj to ex_itab-jhzj.
move alv_itab-jdzj to ex_itab-jdzj.
move alv_itab-beizhu to ex_itab-beizhu.
move alv_itab-zijin1 to ex_itab-zijin1.
move alv_itab-zijin2 to ex_itab-zijin2.
move alv_itab-num1 to ex_itab-num1.
append ex_itab.
endloop.
* loop at ex_itab assigning .（表示是分配一个工作区>）
row_i = sy-tabix + 4.（意思是从第五行开始添加循环传进去的数据）
do 13 times.（循环13列，切记，不是行，是列）
col_i = sy-index .
assign component sy-index of structure to .
lv_txt = .
fill_cell row_i col_i lv_txt 0 10.
enddo.
》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》
这段代码的意思大致就是保持格式的意思吧~~~~我也不是特别理解，但是对它的重要性深有体会啊啊啊啊啊！！！！！！！
call method of
v_excel
'COLUMNS' = v_column
exporting
#1 = 12.（12指的是12列，意思就是第12列的数据按char型导出，长短什么的都不发生变化，若是想要输出的字段是资金型的，那这段代码就必不可少了，O(∩_∩)O~）
set property of v_column 'ColumnWidth' = 10.
set property of v_column 'NumberFormat' = '@'.
》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》
ENDDO.
endloop.
fill_cell 3 11 ex_itab-num1 1 10.（这里的ex_itab-num1是excel内表字段）
fill_cell 2 8 sy-datum 1 8.
row_i = row_i + 1.
fill_cell row_i 11 '合计:' 1 10 .
fill_cell row_i 12 ex_itab-zijin1 1 10.
fill_cell row_i 13 ex_itab-zijin2 1 10.
row_i = row_i + 1.
fill_cell row_i 2 '审核:' 1 10.
fill_cell row_i 11 '制编人:' 1 10.
data: post type string,
col type c value 'N',（‘N’表示excel上最后一列的号）
row type string.
row_i = row_i - 2.（减去二的意思是：最后两行不用划线）
move row_i to row.
concatenate 'N' row into post.
call method of
v_excel
'Range' = v_range
exporting
#1 = 'A4'"A4表示从A4开始画表格线
#2 = post.
call method of
v_range
'BORDERS' = v_border.
set property of v_border 'LineStyle' = '1'.
set property of v_border 'WEIGHT' = '2 '.
set property of v_border 'ColorIndex' = '1'.
free object v_border.
free object v_cell.
call method of
v_excel
'Columns' = v_column.
call method of
v_column
'Autofit'.
*** 设置excel为可见
set property of v_excel 'Visible' = 1.
endform. "sho_excel
展示结果如下：
若是在表头部分有合并单元格的，那么代码如下。
****一行一列和二行一列合并
call method of
v_excel
'Range' = v_range
exporting
#1 = 'A1'（意思是A1和A2合并了）
#2 = 'A2'.
call method of
v_range
'Select'.
set property of v_range 'MergeCells' = 1.
*****
call method of
v_excel
'Range' = v_range
exporting
#1 = 'B1'
#2 = 'B2'.
call method of
v_range
'Select'.
set property of v_range 'MergeCells' = 1.
****
call method of
v_excel
'Range' = v_range
exporting
#1 = 'C1'
#2 = 'C2'.
call method of
v_range
'Select'.
set property of v_range 'MergeCells' = 1.
***
call method of
v_excel
'Range' = v_range
exporting
#1 = 'D1'
#2 = 'D2'.
call method of
v_range
'Select'.
set property of v_range 'MergeCells' = 1.
****
call method of
v_excel
'Range' = v_range
exporting
#1 = 'E1'
#2 = 'E2'.
call method of
v_range
'Select'.
set property of v_range 'MergeCells' = 1.
***
call method of
v_excel
'Range' = v_range
exporting
#1 = 'H1'
#2 = 'H2'.
call method of
v_range
'Select'.
set property of v_range 'MergeCells' = 1.
***
call method of
v_excel
'Range' = v_range
exporting
#1 = 'I1'
#2 = 'I2'.
call method of
v_range
'Select'.
set property of v_range 'MergeCells' = 1.
***
call method of
v_excel
'Range' = v_range
exporting
#1 = 'F1'（意思是F1和G1合并了）
#2 = 'G1'.
set property of v_cell 'HorizontalAlignment' = -4108.
call method of
v_range
'Select'.
set property of v_range 'MergeCells' = 1.
call method of
v_excel
'Range' = v_range
exporting
#1 = 'J1'（意思是J1和K1合并了）
#2 = 'K1'.
set property of v_cell 'HorizontalAlignment' = -4108.
call method of
v_range
'Select'.
set property of v_range 'MergeCells' = 1.
结果展示如下：
