|
*&---------------------------------------------------------------------*
*& Form FRM_DOWNLOAD_DATA *&---------------------------------------------------------------------* * 下载数据到xls文件 *----------------------------------------------------------------------* FORM get_download_data. DATA: lv_excel TYPE ole2_object, lv_sheet TYPE ole2_object, lv_cell TYPE ole2_object, lv_workbook TYPE ole2_object, lv_xlsname TYPE string, lv_line TYPE i , * VALUE 1, "行号 lv_cols TYPE i VALUE 0, "行号 lv_ntgew TYPE zbj_importh-ntgew, lv_inco1 TYPE vbrk-inco1, lv_unitin LIKE t006-msehi, lv_unitout LIKE t006-msehi. DATA: lw_alvdata LIKE t_download, ls_alvdata LIKE t_download. DATA: lt_list TYPE STANDARD TABLE OF t_list. DATA: lv_idx TYPE sy-tabix, lv_index TYPE sy-tabix, lv_vbeln TYPE likp-vbeln, lv_kunnr TYPE likp-kunnr. lt_list[] = gt_list[]. CLEAR:gs_list. LOOP AT gt_weight INTO gs_weight. * get net weight gs_vbco3-vbeln = gs_weight-vbeln. CALL FUNCTION '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. LOOP AT gt_vbplk INTO gs_vbplk. CLEAR: lv_ntgew. SELECT SINGLE brgew INTO lv_ntgew FROM zbox_weight WHERE vhilm = gs_vbplk-vhilm AND gewei = gs_vbplk-gewei. gs_vbplk-ntgew = gs_vbplk-ntgew - lv_ntgew. IF gs_vbplk-gewei <> 'KG'. CLEAR lv_ntgew. lv_unitin = gs_vbplk-gewei. lv_unitout = 'KG'. CALL FUNCTION '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. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-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. MODIFY gt_weight FROM gs_weight. ENDLOOP. CLEAR:gs_list,lv_ntgew. LOOP AT gt_list INTO gs_list. * get amount gs_list-netwr = gs_list-quantity * gs_list-price. MOVE-CORRESPONDING gs_list TO ls_alvdata. * get invoice # SELECT SINGLE vbfa~vbeln INTO ls_alvdata-vbelv FROM vbfa JOIN vbrk ON vbfa~vbeln EQ vbrk~vbeln WHERE vbelv = gs_list-vbeln AND vbtyp_v = 'J' *Changed by cherry 12/05/2013 start AND ( vbtyp_n = 'M' OR vbtyp_n = '5') AND vbrk~fksto NE 'X'. IF gv_vbelv IS INITIAL. CLEAR: gv_vbelv,gv_waers. SELECT SINGLE vbfa~vbeln INTO ls_alvdata-vbelv FROM vbfa JOIN vbrk ON vbfa~vbeln EQ vbrk~vbeln WHERE vbelv = gs_list-vbeln AND vbtyp_v = 'J' AND ( vbtyp_n = 'U' ) AND vbrk~fksto NE 'X'. ENDIF. * get net weight CLEAR:gs_weight. READ TABLE gt_weight INTO gs_weight WITH KEY vbeln = gs_list-vbeln . CLEAR:lv_index. lv_index = gs_weight-add - lv_idx. IF gs_weight-add = 1 . ls_alvdata-ntgew = gs_weight-ntgew. ELSEIF gs_weight-add > 1. IF lv_index > 1. IF lv_vbeln IS NOT INITIAL AND gs_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. ELSEIF lv_index = 1. CLEAR:lv_idx. ls_alvdata-ntgew = gs_weight-ntgew - lv_ntgew. ENDIF. ENDIF. * get COO & Incoterms SELECT SINGLE kunnr inco1 INTO (lv_kunnr,lv_inco1 ) FROM likp WHERE vbeln = gs_list-vbeln. SELECT SINGLE land1 INTO ls_alvdata-land1 FROM vbpa WHERE vbeln = gs_list-vbeln AND kunnr = lv_kunnr. APPEND ls_alvdata TO gt_download. CLEAR:ls_alvdata. ENDLOOP. * 获取下载文件完整路径 PERFORM get_save_path CHANGING lv_xlsname. * 启动Excel CREATE OBJECT lv_excel 'EXCEL.APPLICATION'. IF sy-subrc <> 0. WRITE: / '启动Excel失败。'. STOP. ENDIF. CALL METHOD OF lv_excel 'WORKBOOKS' = lv_workbook. * 使excel 可视 SET PROPERTY OF lv_excel 'VISIBLE' = 0. "0：不可视，1：可视 SET PROPERTY OF lv_excel 'SHEETSINNEWWORKBOOK' = 1. "如果是读取excel文件中的内容 则是直接打开工作簿第一页 CALL METHOD OF lv_workbook 'ADD'. * 例如：CALL * METHOD OF EXCEL 'WORKSHEETS' = SHEET EXPORTING #1 = 1. * set PERFORM set_excel_cell USING lv_excel '1' '提运单号' '1'. PERFORM set_excel_cell USING lv_excel '1' s_track-low '2'. PERFORM set_excel_cell USING lv_excel '3' '贸易术语' '1'. PERFORM set_excel_cell USING lv_excel '3' lv_inco1 '2'. PERFORM set_excel_cell USING lv_excel '1' '进出口标志' '3'. PERFORM set_excel_cell USING lv_excel '1' 'E' '4'. PERFORM set_excel_cell USING lv_excel '7' '手册编号' '5'. PERFORM set_excel_cell USING lv_excel '7' p_hbook '6'. * CLEAR LV_LINE. lv_line = '25'. LOOP AT gt_download INTO lw_alvdata. lv_cols = 1. * 列名的设定 IF sy-tabix = 1. * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line '发票号' lv_cols. * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line '供应商' lv_cols. * PERFORM set_excel_cell USING lv_excel lv_line '其他费用' lv_cols. * * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line '合同号(表头订单号)' lv_cols. * * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line '订单号(表体)' lv_cols. * * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line '物料编号' lv_cols. * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line '成品版本号' lv_cols. * 设定单元格-列（ PERFORM set_excel_cell USING lv_excel lv_line '数量' lv_cols. * * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line '净重(kg)' lv_cols. * * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line '总价' lv_cols. * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line '币制' lv_cols. * 定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line '国别' lv_cols. ENDIF. lv_line = lv_line + 1. "Excel 中行号从1开始 lv_cols = 1. * 设定单元格 PERFORM set_excel_cell USING lv_excel lv_line lw_alvdata-vbelv lv_cols. * 设定单元格 PERFORM set_excel_cell USING lv_excel lv_line '' lv_cols. * 设定单元格 PERFORM set_excel_cell USING lv_excel lv_line ' ' lv_cols. * * 设定单元格 PERFORM set_excel_cell USING lv_excel lv_line ' ' lv_cols. * * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line ' ' lv_cols. * * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line lw_alvdata-matnr lv_cols. * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line lw_alvdata-bomtype lv_cols. * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line lw_alvdata-quantity lv_cols. * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line lw_alvdata-ntgew lv_cols. * * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line lw_alvdata-netwr lv_cols. * * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line lw_alvdata-waers lv_cols. * 设定单元格-列 PERFORM set_excel_cell USING lv_excel lv_line lw_alvdata-land1 lv_cols. ENDLOOP. GET PROPERTY OF lv_excel 'ACTIVESHEET' = lv_sheet. "激活工作簿 GET PROPERTY OF lv_excel 'ACTIVEWORKBOOK' = lv_workbook. "激活工作区 CALL METHOD OF lv_workbook 'SAVEAS' EXPORTING #1 = lv_xlsname #2 = 1. "将excel文件保存 CALL METHOD OF lv_workbook 'CLOSE'. "关闭工作区 CALL METHOD OF lv_excel 'QUIT'. "退出excel *WRITE:/ XLSNAME,'DONE'. "退出成功，输出done FREE OBJECT lv_sheet. "释放操作 FREE OBJECT lv_workbook. FREE OBJECT lv_excel. ENDFORM. " FRM_DOWNLOAD_DATA *&---------------------------------------------------------------------* *& Form SET_EXCEL_CELL *&---------------------------------------------------------------------* * 设定单元格-列 *----------------------------------------------------------------------* * -->P_IN_EXCEL excel对象 * -->P_IN_LINE 行号 * -->P_IN_FIELD 项目 * <--P_OUT_COLS 列号 *----------------------------------------------------------------------* FORM set_excel_cell USING p_in_excel TYPE ole2_object p_in_line TYPE i p_in_field TYPE any p_out_cols TYPE i. DATA: lv_cell TYPE ole2_object. * 指定单元格 CALL METHOD OF p_in_excel 'CELLS' = lv_cell EXPORTING #1 = p_in_line #2 = p_out_cols. * 写入值 IF p_in_field is NOT INITIAL. SET PROPERTY OF lv_cell 'VALUE' = p_in_field. ENDIF. p_out_cols = p_out_cols + 1. ENDFORM. " SET_EXCEL_CELL *&---------------------------------------------------------------------* *& Form GET_SAVE_PATH *&---------------------------------------------------------------------* * 获取下载文件完整路径 *----------------------------------------------------------------------* * <--P_IN_PATH text *----------------------------------------------------------------------* FORM get_save_path CHANGING p_in_path TYPE string. DATA: lv_filename TYPE string, lv_path TYPE string, lv_name TYPE string. lv_name = s_track-low. CALL METHOD cl_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. IF sy-subrc <> 0. * Implement suitable error handling here ENDIF. ENDFORM. " GET_SAVE_PATH |
|
|
