导语:前面再做EPIC_PROC增强导出EXCEL,其中银行卡号特别长,导致导入到EXCEL使用科学计数法显示的,但是这样会导致数据不准确。
错误样例:
解决办法:
通过SMW0上载一个EXCEL文件,这个文件里设置好所有的EXCEL格式,然后在程序中再调用函数进行下载,把内表下载到EXCEL会从第一行下载,所以需要在内表里把表头也填充好,模板文件只需要把会出现科学计数法的那一列的单元格格式修改一下就可以了。
示例代码:
"使用标准程序内表 GT_GRID_DATA TYPE EPIC_T_ITEMS
IF gt_grid_data IS INITIAL.
MESSAGE '没有可以导出的数据,请检查!' TYPE 'I' DISPLAY LIKE 'E'.
ELSE.
DATA : l_str TYPE string.
DATA : lv_str TYPE string.
DATA : g_file TYPE sapb-sappfad.
DATA : gv_file TYPE rlgrap-filename.
DATA : lv_text TYPE char50.
l_str = '导出文件'.
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
def_filename = l_str "
def_path = g_file
mask = '*.XLSX,*.XLS,*.XLS,*.XLSX.'
mode = 'S'
title = '保存路径'
IMPORTING
filename = g_file
EXCEPTIONS
inv_winsys = 1
no_batch = 2
selection_cancel = 3
selection_error = 4
OTHERS = 5.
lv_text = '正在下载请稍等.....'.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
text = lv_text.
DATA : BEGIN OF gs_but000,
partner TYPE but000-partner,
bu_sort1 TYPE but000-bu_sort1,
END OF gs_but000.
DATA : gt_but000 LIKE TABLE OF gs_but000.
DATA : BEGIN OF gs_t042zt,
zlsch TYPE t042zt-zlsch,
text2 TYPE t042zt-text2,
END OF gs_t042zt.
DATA : gt_t042zt LIKE TABLE OF gs_t042zt.
DATA : BEGIN OF gs_out,
no TYPE char10, "序号
ckzh TYPE char50, "出款账号
lifnr TYPE char10, "供应商编号
bu_sort1 TYPE char20, "供应商简称
name1 TYPE char35, "供应商全称
txt20 TYPE char20, "付款类型
brnch TYPE char40, "收款银行
zbnky TYPE char40, "银行号
vtext TYPE char20, "付款条件
text2 TYPE char20, "付款方式
zskzh TYPE char40, "收款账号
waers TYPE char10, "币种
wrbtr_1 TYPE char20, "原币应付金额
wrbtr_2 TYPE char20, "原币预付金额
wrbtr_3 TYPE char20, "原币预付未核销金额
wrbtr_4 TYPE char20, "其他
END OF gs_out.
DATA : gt_out LIKE STANDARD TABLE OF gs_out.
DATA : lv_lines TYPE i.
DATA: lo_objdata TYPE wwwdatatab.
DATA: gv_objid TYPE wwwdatatab-objid.
DATA: gv_objnam TYPE string.
DATA: li_rc LIKE sy-subrc.
"提前上载好模板进行下载,否则数字过长EXCEL会使用科学计数法,导致数据错误
gv_file = g_file.
gv_objid = 'ZEPIC_PROC'.
CONCATENATE gv_objid '.XLS' INTO gv_objnam.
CONDENSE gv_objnam NO-GAPS.
SELECT SINGLE
relid
objid
INTO CORRESPONDING FIELDS OF lo_objdata
FROM wwwdata
WHERE srtf2 = 0
AND relid = 'MI'
AND objid = gv_objid.
IF sy-subrc <> 0 OR lo_objdata-objid IS INITIAL.
MESSAGE '模板文件【ZEPIC_PROC】不存在,请使用TCODE:SMW0进行加载' TYPE 'I'.
EXIT.
ENDIF.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
key = lo_objdata
destination = gv_file
IMPORTING
rc = li_rc.
IF li_rc <> 0.
MESSAGE '模板文件下载失败' TYPE 'E'.
ENDIF.
IF gt_grid_data IS NOT INITIAL.
SELECT partner,
bu_sort1
INTO CORRESPONDING FIELDS OF TABLE @gt_but000
FROM but000
FOR ALL ENTRIES IN @gt_grid_data
WHERE partner = @gt_grid_data-lifnr.
SORT gt_but000 BY partner.
SELECT zlsch,
text2
INTO CORRESPONDING FIELDS OF TABLE @gt_t042zt
FROM t042zt
FOR ALL ENTRIES IN @gt_grid_data
WHERE zlsch = @gt_grid_data-rzawe
AND land1 = 'CN'
AND spras = '1'.
SORT gt_t042zt BY zlsch.
ENDIF.
"把内表下载到EXCEL会从第一行下载,所以需要在内表里把表头也填充好。
"表头添加
gs_out-no = '序号'.
gs_out-ckzh = '出款账号'.
gs_out-lifnr = '供应商'.
gs_out-bu_sort1 = '供应商简称'.
gs_out-name1 = '供应商全称 '.
gs_out-txt20 = '付款类型'.
gs_out-brnch = '收款银行'.
gs_out-zbnky = '银行号'.
gs_out-vtext = '付款条件'.
gs_out-text2 = '付款方式'.
gs_out-zskzh = '收款账号'.
gs_out-waers = '币种'.
gs_out-wrbtr_1 = '原币应付金额'.
gs_out-wrbtr_2 = '原币预付金额'.
gs_out-wrbtr_3 = '原币预付未核'.
gs_out-wrbtr_4 = '其他'.
APPEND gs_out TO gt_out.
CLEAR gs_out.
"表体添加
LOOP AT gt_grid_data INTO DATA(gs_data).
lv_lines = lv_lines + 1.
gs_out-no = lv_lines.
gs_out-ckzh = gs_data-epic_bankno_ref. "出款账号
gs_out-lifnr = gs_data-lifnr.
READ TABLE gt_but000 INTO gs_but000 WITH KEY partner = gs_data-lifnr BINARY SEARCH.
IF sy-subrc = 0.
gs_out-bu_sort1 = gs_but000-bu_sort1.
ENDIF.
gs_out-name1 = gs_data-epic_vendor_name.
gs_out-txt20 = gs_data-txt20.
gs_out-brnch = gs_data-zbnky.
gs_out-vtext = gs_data-vtext.
READ TABLE gt_t042zt INTO gs_t042zt WITH KEY zlsch = gs_data-rzawe BINARY SEARCH.
IF sy-subrc = 0.
gs_out-text2 = gs_t042zt-text2.
ENDIF.
CONCATENATE gs_data-zbnky gs_data-bkref INTO gs_out-zskzh.
gs_out-waers = gs_data-waers.
IF gs_data-hkont = '2202020100' OR gs_data-hkont = '2202010100' OR gs_data-hkont = '2205010100'..
gs_out-wrbtr_1 = gs_data-wrbtr.
ENDIF.
IF gs_data-umskz = 'A'.
gs_out-wrbtr_2 = gs_data-wrbtr.
ENDIF.
IF gs_out-wrbtr_1 IS INITIAL AND gs_out-wrbtr_2 IS INITIAL.
gs_out-wrbtr_3 = gs_data-wrbtr.
ENDIF.
IF gs_out-wrbtr_1 < 0.
gs_out-wrbtr_1 = 0 - gs_out-wrbtr_1.
CONDENSE gs_out-wrbtr_1.
gs_out-wrbtr_1 = '-' && gs_out-wrbtr_1.
ENDIF.
IF gs_out-wrbtr_2 < 0.
gs_out-wrbtr_2 = 0 - gs_out-wrbtr_2.
CONDENSE gs_out-wrbtr_2.
gs_out-wrbtr_2 = '-' && gs_out-wrbtr_2.
ENDIF.
IF gs_out-wrbtr_3 < 0.
gs_out-wrbtr_3 = 0 - gs_out-wrbtr_3.
CONDENSE gs_out-wrbtr_3.
gs_out-wrbtr_3 = '-' && gs_out-wrbtr_3.
ENDIF.
CONDENSE :gs_out-no,gs_out-lifnr,gs_out-ckzh,gs_out-wrbtr_1,gs_out-wrbtr_2,gs_out-wrbtr_3.
APPEND gs_out TO gt_out.
CLEAR gs_out.
ENDLOOP.
"表尾
APPEND gs_out TO gt_out.
gs_out-no = '汇总'.
APPEND gs_out TO gt_out.
CLEAR gs_out.
gs_out-no = '制单:'.
gs_out-bu_sort1 = '财务审核:'.
gs_out-name1 = '审批:'.
APPEND gs_out TO gt_out.
CLEAR gs_out.
gs_out-no = '注:'.
gs_out-ckzh = '①若需批核但金额未特别注明,则按原币应付金额支付;有特别注明的,需经办采购员签字确认,且特别注明金额。'.
APPEND gs_out TO gt_out.
CLEAR gs_out.
gs_out-ckzh = '②需批金额不能大于原币应付金额。'.
APPEND gs_out TO gt_out.
CLEAR gs_out.
"导出EXCEL
CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
EXPORTING
i_filename = gv_file
TABLES
i_tab_sap_data = gt_out
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc = 0.
MESSAGE '导出成功!' TYPE 'S'.
ELSE.
MESSAGE '导出失败!' TYPE 'S' DISPLAY LIKE 'E'.
ENDIF.
CLEAR : gt_out,gs_out,gt_but000,gs_but000,gt_t042zt,gs_t042zt.
ENDIF.
结果展示:
作者:小飞猪猪猪猪猪猪猪–CSDN