Download to excel from SAP.docx
《Download to excel from SAP.docx》由会员分享，可在线阅读，更多相关《Download to excel from SAP.docx（16页珍藏版）》请在冰豆网上搜索。
DownloadtoexcelfromSAP
DownloadtoexcelfromSAP
SPAN{
font-family:
"CourierNew";
font-size:
10pt;
color:
#000000;
background:
#FFFFFF;
}
.L0S31{
font-style:
italic;
color:
#808080;
}
.L0S32{
color:
#3399FF;
}
.L0S33{
color:
#4DA619;
}
.L0S52{
color:
#0000FF;
}
.L0S55{
color:
#800080;
}
.L0S70{
color:
#808080;
}
*&---------------------------------------------------------------------*
*&FormFRM_DOWNLOAD_DATA
*&---------------------------------------------------------------------*
*下载数据到xls文件
*----------------------------------------------------------------------*
FORMget_download_data.
DATA:
lv_excelTYPEole2_object,
lv_sheetTYPEole2_object,
lv_cellTYPEole2_object,
lv_workbookTYPEole2_object,
lv_xlsnameTYPEstring,
lv_lineTYPEi,
*VALUE1,"行号
lv_colsTYPEiVALUE0,"行号
lv_ntgewTYPEzbj_importh-ntgew,
lv_inco1TYPEvbrk-inco1,
lv_unitinLIKEt006-msehi,
lv_unitoutLIKEt006-msehi.
DATA:
lw_alvdataLIKEt_download,
ls_alvdataLIKEt_download.
DATA:
lt_listTYPESTANDARDTABLEOFt_list.
DATA:
lv_idxTYPEsy-tabix,
lv_indexTYPEsy-tabix,
lv_vbelnTYPElikp-vbeln,
lv_kunnrTYPElikp-kunnr.
lt_list[]=gt_list[].
CLEAR:
gs_list.
LOOPATgt_weightINTOgs_weight.
*getnetweight
gs_vbco3-vbeln=gs_weight-vbeln.
CALLFUNCTION'SD_PACKING_PRINT_VIEW'
EXPORTING
comwa=gs_vbco3
TABLES
vbplk_tab=gt_vbplk
vbplp_tab=gt_vbplp
vbpls_tab=gt_vbpls
EXCEPTIONS
object_not_found=1
OTHERS=2.*gs_weight-vbeln=gs_list-vbeln.
LOOPATgt_vbplkINTOgs_vbplk.CLEAR:
lv_ntgew.
SELECTSINGLEbrgew
INTOlv_ntgew
FROMzbox_weight
WHEREvhilm=gs_vbplk-vhilmAND
gewei=gs_vbplk-gewei.gs_vbplk-ntgew=gs_vbplk-ntgew-lv_ntgew.
IFgs_vbplk-gewei<>'KG'.CLEARlv_ntgew.
lv_unitin=gs_vbplk-gewei.
lv_unitout='KG'.
CALLFUNCTION'UNIT_CONVERSION_SIMPLE'
EXPORTING
input=gs_vbplk-ntgew
unit_in=lv_unitin
unit_out=lv_unitout
IMPORTING
output=lv_ntgew
EXCEPTIONS
conversion_not_found=1
division_by_zero=2
input_invalid=3
output_invalid=4
overflow=5
type_invalid=6
units_missing=7
unit_in_not_found=8
unit_out_not_found=9
OTHERS=10.
IFsy-subrc<>0.
MESSAGEIDsy-msgidTYPEsy-msgtyNUMBERsy-msgno
WITHsy-msgv1sy-msgv2sy-msgv3sy-msgv4.ENDIF.
ELSE.
lv_ntgew=gs_vbplk-ntgew.
ENDIF.
*ls_weight-brgew=ls_weight-brgew+lv_brgew.
gs_weight-ntgew=gs_weight-ntgew+lv_ntgew.
ENDLOOP.
*gs_weight-ntgew=lv_ntgew.
gs_weight-ntgewp=gs_weight-ntgew/gs_weight-quantity.
MODIFYgt_weightFROMgs_weight.ENDLOOP.CLEAR:
gs_list,lv_ntgew.
LOOPATgt_listINTOgs_list.*getamount
gs_list-netwr=gs_list-quantity*gs_list-price.MOVE-CORRESPONDINGgs_listTOls_alvdata.*getinvoice#
SELECTSINGLEvbfa~vbeln
INTOls_alvdata-vbelv
FROMvbfa
JOINvbrk
ONvbfa~vbelnEQvbrk~vbeln
WHEREvbelv=gs_list-vbeln
ANDvbtyp_v='J'
*Changedbycherry12/05/2013start
AND（vbtyp_n='M'ORvbtyp_n='5'）
ANDvbrk~fkstoNE'X'.IFgv_vbelvISINITIAL.CLEAR:
gv_vbelv,gv_waers.
SELECTSINGLEvbfa~vbeln
INTOls_alvdata-vbelv
FROMvbfa
JOINvbrk
ONvbfa~vbelnEQvbrk~vbeln
WHEREvbelv=gs_list-vbeln
ANDvbtyp_v='J'
AND（vbtyp_n='U'）
ANDvbrk~fkstoNE'X'.
ENDIF.
*getnetweightCLEAR:
gs_weight.
READTABLEgt_weightINTOgs_weight
WITHKEYvbeln=gs_list-vbeln.
CLEAR:
lv_index.
lv_index=gs_weight-add-lv_idx.IFgs_weight-add=1.
ls_alvdata-ntgew=gs_weight-ntgew.ELSEIFgs_weight-add>1.
IFlv_index>1.
IFlv_vbelnISNOTINITIALANDgs_list-vbeln<>lv_vbeln.
CLEAR:
lv_ntgew,lv_idx.
ENDIF.
lv_idx=lv_idx+1.
ls_alvdata-ntgew=gs_list-quantity*gs_weight-ntgewp.lv_ntgew=ls_alvdata-ntgew+lv_ntgew.
ELSEIFlv_index=1.
CLEAR:
lv_idx.
ls_alvdata-ntgew=gs_weight-ntgew-lv_ntgew.
ENDIF.ENDIF.
*getCOO&Incoterms
SELECTSINGLEkunnrinco1INTO（lv_kunnr,lv_inco1）
FROMlikp
WHEREvbeln=gs_list-vbeln.SELECTSINGLEland1
INTOls_alvdata-land1
FROMvbpa
WHEREvbeln=gs_list-vbeln
ANDkunnr=lv_kunnr.APPENDls_alvdataTOgt_download.
CLEAR:
ls_alvdata.
ENDLOOP.*获取下载文件完整路径
PERFORMget_save_pathCHANGINGlv_xlsname.*启动Excel
CREATEOBJECTlv_excel'EXCEL.APPLICATION'.IFsy-subrc<>0.
WRITE:
/'启动Excel失败。
'.
STOP.
ENDIF.CALLMETHODOF
lv_excel
'WORKBOOKS'=lv_workbook.*使excel可视
SETPROPERTYOFlv_excel'VISIBLE'=0."0：
不可视，1：
可视SETPROPERTYOFlv_excel'SHEETSINNEWWORKBOOK'=1.
"如果是读取excel文件中的内容则是直接打开工作簿第一页
CALLMETHODOF
lv_workbook
'ADD'.
*例如：
CALL
*METHODOFEXCEL'WORKSHEETS'=SHEETEXPORTING#1=1.
*setPERFORMset_excel_cellUSINGlv_excel
'1'
'提运单号'
'1'.
PERFORMset_excel_cellUSINGlv_excel
'1'
s_track-low
'2'.
PERFORMset_excel_cellUSINGlv_excel
'3'
'贸易术语'
'1'.
PERFORMset_excel_cellUSINGlv_excel
'3'
lv_inco1
'2'.
PERFORMset_excel_cellUSINGlv_excel
'1'
'进出口标志'
'3'.
PERFORMset_excel_cellUSINGlv_excel
'1'
'E'
'4'.
PERFORMset_excel_cellUSINGlv_excel
'7'
'手册编号'
'5'.
PERFORMset_excel_cellUSINGlv_excel
'7'
p_hbook
'6'.
*CLEARLV_LINE.
lv_line='25'.
LOOPATgt_downloadINTOlw_alvdata.
lv_cols=1.
*列名的设定
IFsy-tabix=1.*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
'发票号'
lv_cols.*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
'供应商'
lv_cols.
*
PERFORMset_excel_cellUSINGlv_excel
lv_line
'其他费用'
lv_cols.
*
*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
'合同号（表头订单号）'
lv_cols.
*
*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
'订单号（表体）'
lv_cols.
*
*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
'物料编号'
lv_cols.*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
'成品版本号'
lv_cols.*设定单元格-列（
PERFORMset_excel_cellUSINGlv_excel
lv_line
'数量'
lv_cols.
*
*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
'净重（kg）'
lv_cols.
*
*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
'总价'
lv_cols.
*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
'币制'
lv_cols.*定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
'国别'
lv_cols.ENDIF.lv_line=lv_line+1."Excel中行号从1开始
lv_cols=1.*设定单元格
PERFORMset_excel_cellUSINGlv_excel
lv_line
lw_alvdata-vbelv
lv_cols.*设定单元格
PERFORMset_excel_cellUSINGlv_excel
lv_line
''
lv_cols.*设定单元格
PERFORMset_excel_cellUSINGlv_excel
lv_line
''
lv_cols.
*
*设定单元格
PERFORMset_excel_cellUSINGlv_excel
lv_line
''
lv_cols.
*
*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
''
lv_cols.
*
*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
lw_alvdata-matnr
lv_cols.*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
lw_alvdata-bomtype
lv_cols.*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
lw_alvdata-quantity
lv_cols.*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
lw_alvdata-ntgew
lv_cols.
*
*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
lw_alvdata-netwr
lv_cols.
*
*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
lw_alvdata-waers
lv_cols.
*设定单元格-列
PERFORMset_excel_cellUSINGlv_excel
lv_line
lw_alvdata-land1
lv_cols.ENDLOOP.GETPROPERTYOFlv_excel'ACTIVESHEET'=lv_sheet."激活工作簿
GETPROPERTYOFlv_excel'ACTIVEWORKBOOK'=lv_workbook."激活工作区CALLMETHODOF
lv_workbook
'SAVEAS'EXPORTING
#1=lv_xlsname
#2=1.
"将excel文件保存
CALLMETHODOFlv_workbook'CLOSE'."关闭工作区
CALLMETHODOF
lv_excel
'QUIT'.
"退出excel*WRITE:
/XLSNAME,'DONE'.
"退出成功，输出doneFREEOBJECTlv_sheet."释放操作
FREEOBJECTlv_workbook.
FREEOBJECTlv_excel.ENDFORM."FRM_DOWNLOAD_DATA
*&---------------------------------------------------------------------*
*&FormSET_EXCEL_CELL
*&---------------------------------------------------------------------*
*设定单元格-列
*----------------------------------------------------------------------*
*-->P_IN_EXCELexcel对象
*-->P_IN_LINE行号
*-->P_IN_FIELD项目
*<--P_OUT_COLS列号
*----------------------------------------------------------------------*
FORMset_excel_cellUSINGp_in_excelTYPEole2_object
p_in_lineTYPEi
p_in_fieldTYPEany
p_out_colsTYPEi.DATA:
lv_cellTYPEole2_object.*指定单元格
CALLMETHODOF
p_in_excel
'CELLS'=lv_cell
EXPORTING
#1=p_in_line
#2=p_out_cols.
*写入值
IFp_in_fieldisNOTINITIAL.
SETPROPERTYOFlv_cell
'VALUE'=p_in_field.
ENDIF.p_out_cols=p_out_cols+1.ENDFORM."SET_EXCEL_CELL
*&---------------------------------------------------------------------*
*&FormGET_SAVE_PATH
*&---------------------------------------------------------------------*
*获取下载文件完整路径
*----------------------------------------------------------------------*
*<--P_IN_PATHtext
*----------------------------------------------------------------------*
FORMget_save_pathCHANGINGp_in_pathTYPEstring.
DATA:
lv_filenameTYPEstring,
lv_pathTYPEstring,
lv_nameTYPEstring.
lv_name=s_track-low.
CALLMETHODcl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title='下载文件'
file_filter='*.xls'
default_file_name=lv_name
CHANGING
filename=lv_filename
path=lv_path
fullpath=p_in_path
EXCEPTIONS
cntl_error=1
error_no_gui=2
not_supported_by_gui=3
OTHERS=4.IFsy-subrc<>0.
*Implementsuitableerrorhandlinghere
ENDIF.ENDFORM."GET_SAVE_PATH
升级会员 