1, 问题描述
用excel上载大数据量的BOM数据到SAP系统中，一次上载的数量级别在10万条左右，这时用ALSM_EXCEL_TO_INTERNAL_TABLE上载时会dump，查看了代码发现ALSM_EXCEL_TO_INTERNAL_TABLE中是将数据先保存到剪贴板(Clippboard )上,然后在保存到内表中，很不幸剪贴板大小有限制，超过就会dump。
2, 问题解决
解决方法比较简单，就是在循环中调用ALSM_EXCEL_TO_INTERNAL_TABLE，将一次上载的内容分成多次，以便减少保存到剪贴板中的内容。
代码如下：
下面代码中，每次上载40000条数据，循环10次，最多可以上载40万，当然循环次数还可以按需求扩大。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 |
REPORT ztest_upload_excel. DATA:g_begin_row TYPE i, g_end_row TYPE i. DATA:git_temp TYPE TABLE OF alsmex_tabline, gwa_temp TYPE alsmex_tabline. TYPES:BEGIN OF ty_upload, werks TYPE ymoe_compalloc-werks, "Plant root TYPE ymoe_compalloc-root, "Root material maktxr TYPE makt-maktx, "Description ( R ) parent TYPE ymoe_compalloc-parent, "Parent maktxp TYPE makt-maktx, "Description ( P ) beskz TYPE marc-beskz, "Procurement Type sobsl TYPE marc-sobsl, "Special Procurement Type component TYPE ymoe_compalloc-component, "Component maktxc TYPE makt-maktx, "Description ( C ) prodline TYPE ymoe_compalloc-prodline, "Production Line workstation TYPE ymoe_compalloc-workstation, "Work Station END OF ty_upload. DATA:git_upload TYPE STANDARD TABLE OF ty_upload, gwa_upload LIKE LINE OF git_upload. PARAMETERS:p_file TYPE rlgrap-filename DEFAULT 'C:\temp\upload.xlsx' OBLIGATORY. "File Name AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file. PERFORM frm_get_filename. *----------------------------------------------------------------------* * Start-of-Selection *----------------------------------------------------------------------* START-OF-SELECTION. g_begin_row = 2. g_end_row = g_begin_row + 40000. DO 10 TIMES. REFRESH:git_temp. * Get EXCEL-Sheet in internal table CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE' EXPORTING filename = p_file i_begin_col = '1' i_begin_row = g_begin_row i_end_col = '11' i_end_row = g_end_row TABLES intern = git_temp EXCEPTIONS inconsistent_parameters = 1 upload_ole = 2 OTHERS = 3. IF git_temp[] IS INITIAL. EXIT. ELSE. LOOP AT git_temp INTO gwa_temp. AT NEW row. CLEAR gwa_upload. ENDAT. CASE gwa_temp-col. WHEN '1'. gwa_upload-werks = gwa_temp-value. WHEN '2'. gwa_upload-root = gwa_temp-value. WHEN '3'. gwa_upload-maktxr = gwa_temp-value. WHEN '4'. gwa_upload-parent = gwa_temp-value. WHEN '5'. gwa_upload-maktxp = gwa_temp-value. WHEN '6'. gwa_upload-beskz = gwa_temp-value. WHEN '7'. gwa_upload-sobsl = gwa_temp-value. WHEN '8'. gwa_upload-component = gwa_temp-value. WHEN '9'. gwa_upload-maktxc = gwa_temp-value. WHEN '10'. gwa_upload-prodline = gwa_temp-value. WHEN '11'. gwa_upload-workstation = gwa_temp-value. ENDCASE. AT END OF row. APPEND gwa_upload TO git_upload. ENDAT. ENDLOOP. g_begin_row = g_end_row + 1. g_end_row = g_begin_row + 40000. ENDIF. ENDDO. *&---------------------------------------------------------------------* *& Form FRM_GET_FILENAME *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM frm_get_filename . DATA:l_filter TYPE string, lit_file TYPE STANDARD TABLE OF file_table, lwa_file LIKE LINE OF lit_file, l_rc TYPE i. CLEAR:p_file. l_filter = 'Microsoft Excel Files (*.XLS;*.XLSX;*.XLSM)|*.XLS;*.XLSX;*.XLSM|'. CALL METHOD cl_gui_frontend_services=>file_open_dialog EXPORTING * window_title = default_extension = '.xlsx' * default_filename = file_filter = l_filter * with_encoding = * initial_directory = * multiselection = CHANGING file_table = lit_file rc = l_rc * user_action = * file_encoding = EXCEPTIONS file_open_dialog_failed = 1 cntl_error = 2 error_no_gui = 3 not_supported_by_gui = 4 OTHERS = 5. IF sy-subrc = 0. READ TABLE lit_file INTO lwa_file INDEX 1. IF sy-subrc = 0. p_file = lwa_file-filename. ENDIF. ENDIF. ENDFORM. |
以上。
发表评论