class ZCL_EXCEL_DOWNLOAD definition public final create public . public section. data G_FILEPATH type RLGRAP-FILENAME . methods CONSTRUCTOR importing value(I_DEFAULT_FILENAME) type STRING optional value(I_OBJID) type W3OBJID optional value(I_FILEPATH) type RLGRAP-FILENAME optional . class-methods DOWNLOAD_TEMPLATE importing value(I_DEFAULT_FILENAME) type STRING optional value(I_OBJID) type WWWPARAMS-OBJID optional exporting value(E_PATH) type RLGRAP-FILENAME . methods OPEN_EXCEL importing value(I_SHEET_NAME) type STRING optional . methods SET_SINGLE_CELL importing value(I_ROW) type I value(I_COL) type I value(I_VALUE) type ANY default 3 value(I_HA) type I optional . methods SET_ROW_CELL importing !LT_ITAB type STANDARD TABLE value(I_ROW) type I value(I_COL) type I . methods SET_RANGE_CELL importing !LT_ITAB type STANDARD TABLE value(I_ROW) type I value(I_COL) type I . methods CLOSE_EXCEL importing !I_CLOSE type C default 'X' . methods INS_ROW importing value(COPY) type I value(ROW) type I value(COUNT) type I . protected section. private section. data G_EXCEL type OLE2_OBJECT . data G_WORKBOOKS type OLE2_OBJECT . data G_WORKBOOK type OLE2_OBJECT . data G_SHEET type OLE2_OBJECT . ENDCLASS. CLASS ZCL_EXCEL_DOWNLOAD IMPLEMENTATION. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Instance Public Method ZCL_EXCEL_DOWNLOAD->CLOSE_EXCEL * +-------------------------------------------------------------------------------------------------+ * | [--->] I_CLOSE TYPE C (default ='X') * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD close_excel. SET PROPERTY OF g_excel 'DisplayAlerts' = 'false'. "关闭保存提示。 CALL METHOD OF g_workbook 'SAVEAS' EXPORTING #1 = g_filepath. " #2 = 1. IF i_close = 'X'. CALL METHOD OF g_workbook 'CLOSE'. "关闭工作区 CALL METHOD OF g_excel 'QUIT'."退出excel ENDIF . FREE OBJECT g_excel . FREE OBJECT g_workbooks . FREE OBJECT g_workbook . FREE OBJECT g_sheet . CLEAR:g_filepath . ENDMETHOD. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Instance Public Method ZCL_EXCEL_DOWNLOAD->CONSTRUCTOR * +-------------------------------------------------------------------------------------------------+ * | [--->] I_DEFAULT_FILENAME TYPE STRING(optional) * | [--->] I_OBJID TYPE W3OBJID(optional) * | [--->] I_FILEPATH TYPE RLGRAP-FILENAME(optional) * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD constructor. DATA: l_filename TYPE string, " 文件名 l_extension TYPE string , "文件后缀 l_path TYPE string, " 路径 l_fullpath TYPE string. " 全路径 DATA:lv_title TYPE string, lv_filename TYPE string. DATA:ls_template TYPE wwwdatatab."WEB资源库文件信息 DATA:lv_subrc TYPE sysubrc . DATA:lv_file_filter TYPE string . IF i_filepath IS NOT INITIAL . g_filepath = i_filepath . RETURN . ENDIF . CHECK i_objid IS NOT INITIAL . CALL METHOD download_template EXPORTING i_default_filename = i_default_filename i_objid = i_objid IMPORTING e_path = g_filepath. ENDMETHOD. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Static Public Method ZCL_EXCEL_DOWNLOAD=>DOWNLOAD_TEMPLATE * +-------------------------------------------------------------------------------------------------+ * | [--->] I_DEFAULT_FILENAME TYPE STRING(optional) * | [--->] I_OBJID TYPE WWWPARAMS-OBJID(optional) * | [<---] E_PATH TYPE RLGRAP-FILENAME * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD download_template. DATA: l_filename TYPE string, " 文件名 l_extension TYPE string , "文件后缀 l_path TYPE string, " 路径 l_fullpath TYPE string. " 全路径 DATA:lv_title TYPE string, lv_filename TYPE string. DATA:ls_template TYPE wwwdatatab."WEB资源库文件信息 DATA:lv_subrc TYPE sysubrc . DATA:lv_file_filter TYPE string . DATA: lv_directory TYPE string, lv_result TYPE abap_bool. DATA:lv_extension(64). CALL FUNCTION 'WWWPARAMS_READ' EXPORTING relid = 'MI' objid = i_objid name = 'fileextension' IMPORTING value = lv_extension EXCEPTIONS entry_not_exists = 1 OTHERS = 2. IF sy-subrc <> 0. * 实例不存在！ MESSAGE s139(zmsg) DISPLAY LIKE 'E'. RETURN. ENDIF. IF lv_extension = '.xlsx' OR lv_extension = '.XLSX'. lv_file_filter = '*.xlsx|*.xlsx|*.xls|*.xls' . ELSEIF lv_extension = '.xls' OR lv_extension = '.XLS' . lv_file_filter = '*.xls|*.xls|*.xlsx|*.xlsx' . ENDIF . l_extension = lv_extension. lv_directory = 'D:\' . * call method cl_gui_frontend_services=>directory_exist * exporting * directory = lv_directory * receiving * result = lv_result * exceptions * cntl_error = 1 * error_no_gui = 2 * wrong_parameter = 3 * not_supported_by_gui = 4 * others = 5. * if lv_result = abap_false. * clear:lv_directory. * endif. CALL METHOD cl_gui_frontend_services=>file_save_dialog "保存文件路径对话框 EXPORTING window_title = 'Download Template' default_extension = l_extension default_file_name = i_default_filename file_filter = lv_file_filter initial_directory = lv_directory CHANGING filename = l_filename path = l_path fullpath = l_fullpath EXCEPTIONS cntl_error = 1 error_no_gui = 2 not_supported_by_gui = 3 OTHERS = 4. CHECK l_fullpath IS NOT INITIAL. CLEAR ls_template. ls_template-relid = 'MI'. ls_template-objid = i_objid. DATA:lv_filepath TYPE rlgrap-filename. e_path = lv_filepath = l_fullpath . CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' EXPORTING key = ls_template destination = lv_filepath IMPORTING rc = lv_subrc. IF lv_subrc NE 0 . * 下载模板失败！ MESSAGE s140(zmsg) DISPLAY LIKE 'E'. ENDIF . ENDMETHOD. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Instance Public Method ZCL_EXCEL_DOWNLOAD->INS_ROW * +-------------------------------------------------------------------------------------------------+ * | [--->] COPY TYPE I * | [--->] ROW TYPE I * | [--->] COUNT TYPE I * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD ins_row. DATA: lc_range TYPE ole2_object. DO count TIMES. CALL METHOD OF g_sheet 'Rows' = lc_range EXPORTING #1 = copy. CALL METHOD OF lc_range 'Copy'. CALL METHOD OF g_sheet 'Rows' = lc_range EXPORTING #1 = row. CALL METHOD OF lc_range 'Insert'. CALL METHOD OF lc_range 'ClearContents'. ENDDO. ENDMETHOD. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Instance Public Method ZCL_EXCEL_DOWNLOAD->OPEN_EXCEL * +-------------------------------------------------------------------------------------------------+ * | [--->] I_SHEET_NAME TYPE STRING(optional) * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD open_excel. CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING * PERCENTAGE = 0 text = 'Data Loading.....'. CREATE OBJECT g_excel 'EXCEL.APPLICATION'. SET PROPERTY OF g_excel 'VISIBLE' = 0. CALL METHOD OF g_excel 'WORKBOOKS' = g_workbooks. CALL METHOD OF g_workbooks 'OPEN' = g_workbook EXPORTING #1 = g_filepath. "有多个sheet页时。 IF i_sheet_name IS NOT INITIAL . CALL METHOD OF g_workbook 'WORKSHEETS' = g_sheet EXPORTING #1 = i_sheet_name. CALL METHOD OF g_sheet 'Activate'. ELSE. CALL METHOD OF g_workbook 'ActiveSheet' = g_sheet. ENDIF . ENDMETHOD. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Instance Public Method ZCL_EXCEL_DOWNLOAD->SET_RANGE_CELL * +-------------------------------------------------------------------------------------------------+ * | [--->] LT_ITAB TYPE STANDARD TABLE * | [--->] I_ROW TYPE I * | [--->] I_COL TYPE I * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD set_range_cell. CONSTANTS: lv_seperator TYPE c VALUE cl_abap_char_utilities=>horizontal_tab. " 值：# *接口数据表相关 DATA:wa_itab TYPE REF TO data, "接口内表工作区, descr_wa TYPE REF TO cl_abap_structdescr, "接口内表工作区结构信息 field_no TYPE i, "接口内表工作区组件数量 field_string TYPE string. "行结构组件的值 FIELD-SYMBOLS: <wa_itab> TYPE any, "接口内表工作区 <field> TYPE any. "接口内表工作区中的组件 *粘贴内表 DATA: BEGIN OF ls_data , record(4096) TYPE c, END OF ls_data. DATA: lt_data LIKE TABLE OF ls_data, l_rc TYPE i. *EXCEL 对象 DATA: excel_cell TYPE ole2_object. CHECK lt_itab[] IS NOT INITIAL. *创建接口数据表工作区 CREATE DATA wa_itab LIKE LINE OF lt_itab. ASSIGN wa_itab->* TO <wa_itab>. *获取内表工作区的组件数量 descr_wa ?= cl_abap_typedescr=>describe_by_data( <wa_itab> ). DESCRIBE TABLE descr_wa->components LINES field_no. *将接口表数据转存到粘贴内表中 LOOP AT lt_itab INTO <wa_itab> . CLEAR:ls_data. IF field_no = 1. * ls_data-record = <wa_itab>. ASSIGN COMPONENT 1 OF STRUCTURE <wa_itab> TO <field>. field_string = <field>. ls_data-record = field_string. ELSE. DO field_no TIMES. ASSIGN COMPONENT sy-index OF STRUCTURE <wa_itab> TO <field>. * IF sy-subrc <> 0. * RAISE field_not_assigned. * ENDIF. field_string = <field>. IF sy-index = 1. ls_data-record = field_string. ELSE. CONCATENATE ls_data-record field_string INTO ls_data-record SEPARATED BY lv_seperator. ENDIF. ENDDO. ENDIF. APPEND ls_data TO lt_data. ENDLOOP. cl_gui_frontend_services=>clipboard_export( IMPORTING data = lt_data CHANGING rc = l_rc ). *设定单元格 CALL METHOD OF g_sheet 'Cells' = excel_cell EXPORTING #1 = i_row #2 = i_col. *选中单元格操作 CALL METHOD OF excel_cell 'Select'. *粘贴操作 CALL METHOD OF g_sheet 'Paste'. ENDMETHOD. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Instance Public Method ZCL_EXCEL_DOWNLOAD->SET_ROW_CELL * +-------------------------------------------------------------------------------------------------+ * | [--->] LT_ITAB TYPE STANDARD TABLE * | [--->] I_ROW TYPE I * | [--->] I_COL TYPE I * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD set_row_cell. CONSTANTS: lv_seperator TYPE c VALUE cl_abap_char_utilities=>horizontal_tab. " 值：# *接口数据表相关 DATA:wa_itab TYPE REF TO data, "接口内表工作区, descr_wa TYPE REF TO cl_abap_structdescr, "接口内表工作区结构信息 field_no TYPE i, "接口内表工作区组件数量 field_string TYPE string. "行结构组件的值 FIELD-SYMBOLS: <wa_itab> TYPE any, "接口内表工作区 <field> TYPE any. "接口内表工作区中的组件 *粘贴内表 DATA: BEGIN OF ls_data , record(4096) TYPE c, END OF ls_data. DATA: lt_data LIKE TABLE OF ls_data, l_rc TYPE i. *EXCEL 对象 DATA: excel_cell TYPE ole2_object. CHECK lt_itab[] IS NOT INITIAL. *创建接口数据表工作区 CREATE DATA wa_itab LIKE LINE OF lt_itab. ASSIGN wa_itab->* TO <wa_itab>. *将接口表数据转存到粘贴内表中 LOOP AT lt_itab INTO <wa_itab> . IF sy-tabix = 1 . ls_data-record = <wa_itab>. ELSE. field_string = <wa_itab>. CONCATENATE ls_data-record field_string INTO ls_data-record SEPARATED BY lv_seperator. ENDIF. ENDLOOP. APPEND ls_data TO lt_data. cl_gui_frontend_services=>clipboard_export( IMPORTING data = lt_data CHANGING rc = l_rc ). *设定单元格 CALL METHOD OF g_sheet 'Cells' = excel_cell EXPORTING #1 = i_row #2 = i_col. *选中单元格操作 CALL METHOD OF excel_cell 'Select'. *粘贴操作 CALL METHOD OF g_sheet 'Paste'. ENDMETHOD. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Instance Public Method ZCL_EXCEL_DOWNLOAD->SET_SINGLE_CELL * +-------------------------------------------------------------------------------------------------+ * | [--->] I_ROW TYPE I * | [--->] I_COL TYPE I * | [--->] I_VALUE TYPE ANY (default =3) * | [--->] I_HA TYPE I(optional) * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD set_single_cell. DATA:lo_cell TYPE ole2_object, lo_font TYPE ole2_object. CHECK i_value IS NOT INITIAL. CALL METHOD OF g_excel 'CELLS' = lo_cell EXPORTING #1 = i_row #2 = i_col. SET PROPERTY OF lo_cell 'VALUE' = i_value. * 设置列宽 * SET PROPERTY OF lo_cell 'COLUMNWIDTH' = 0. * 对齐方式 3为居中 * SET PROPERTY OF lo_cell 'HORIZONTALALIGNMENT' = i_ha. ** 水平对齐方式 * SET PROPERTY OF lo_cell 'VERTICALALIGNMENT' = 2. * * SET PROPERTY OF lo_cell 'WRAPTEXT' = 0. * * GET PROPERTY OF lo_cell 'FONT' = lo_font. ** 字体加粗 * SET PROPERTY OF lo_font 'BOLD' = 0. **字体大小 * SET PROPERTY OF lo_font 'SIZE' = 11. FREE lo_font. FREE lo_cell. ENDMETHOD. ENDCLASS.