Multi Sheet를 포함하는 특정 엑셀 템플릿에 데이터를 다운로드/업로드 하는 로직.
(반드시 Excel Sheet Name 앞뒤 공백을 제거해야 함!)
*--> Excel Download Variable. DATA: excel TYPE ole2_object, workbook TYPE ole2_object, books TYPE ole2_object, book TYPE ole2_object, sheets TYPE ole2_object, sheet TYPE ole2_object, activesheet TYPE ole2_object, cells TYPE ole2_object, cell TYPE ole2_object, row TYPE ole2_object, rowheight TYPE ole2_object, buffer TYPE ole2_object, columnwidth TYPE ole2_object, rowwidth TYPE ole2_object. CREATE OBJECT excel 'EXCEL.APPLICATION'. DATA: lv_row(5) TYPE i. CONCATENATE 'c:\temp\' gv_filename_pr '.xlsx' INTO gv_file_dest. CONDENSE gv_filename_pr NO-GAPS. PERFORM download_template USING 'ZLD5BIA0040_SAMPLE01' gv_file_dest CHANGING gv_filename_pr. PERFORM open_excel_template USING gv_filename_pr. PERFORM change_sheet USING 1. * InfoCube, MultiCube Text. IF pa_mt = 'X'. lv_row = c_header2. LOOP AT gt_cub_p. ADD 1 TO lv_row. PERFORM fill_cells USING lv_row 1 gt_cub_p-infocube. PERFORM fill_cells USING lv_row 2 gt_cub_p-txtsh_k. PERFORM fill_cells USING lv_row 3 gt_cub_p-txtlg_k. PERFORM fill_cells USING lv_row 4 gt_cub_p-txtsh_e. PERFORM fill_cells USING lv_row 5 gt_cub_p-txtlg_e. PERFORM fill_cells USING lv_row 6 gt_cub_p-txtsh_c. PERFORM fill_cells USING lv_row 7 gt_cub_p-txtlg_c. ENDLOOP. ENDIF. PERFORM change_sheet USING 2. * PERFORM copy_line USING line. * PERFORM dele_line USING line. CALL METHOD OF workbook 'SAVE'. CALL METHOD OF workbook 'CLOSE'. *&---------------------------------------------------------------------* *& Form DOWNLOAD_TEMPLATE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* FORM download_template USING pv_id pv_dest CHANGING pv_filename. DATA: wwwdata_item LIKE wwwdatatab. DATA: rc TYPE i. CALL FUNCTION 'WS_FILE_DELETE' EXPORTING file = pv_filename IMPORTING return = rc. SELECT SINGLE * FROM wwwdata INTO CORRESPONDING FIELDS OF wwwdata_item WHERE objid = pv_id. CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' EXPORTING key = wwwdata_item destination = pv_dest CHANGING temp = pv_filename. ENDFORM. " DOWNLOAD_TEMPLATE *&---------------------------------------------------------------------* *& Form OPEN_EXCEL_TEMPLATE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* FORM open_excel_template USING p_filename. IF sy-subrc NE 0. MESSAGE i000(z1) WITH sy-msgli. EXIT . CALL METHOD OF workbook 'Sheets' = sheets. CALL METHOD OF sheets 'item' = sheet EXPORTING #1 = 1. " sheet number. ENDIF. ENDFORM. " OPEN_EXCEL_TEMPLATE *&---------------------------------------------------------------------* *& Form CHANGE_SHEET *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* FORM change_sheet USING sheetnum. CALL METHOD OF excel 'SHEETS' = sheet EXPORTING #1 = sheetnum. CALL METHOD OF sheet 'SELECT'. ENDFORM. " CHANGE_SHEET *&---------------------------------------------------------------------* *& Form FILL_CELLS *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* FORM fill_cells USING i j val. DATA : ls_font TYPE ole2_object. DATA : ls_inte TYPE ole2_object. DATA : ls_boder TYPE ole2_object. CALL METHOD OF excel 'CELLS' = cell EXPORTING #1 = i #2 = j. SET PROPERTY OF cell 'VALUE' = val. ENDFORM. " FILL_CELLS *&---------------------------------------------------------------------* *& Form COPY_LINE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* FORM copy_line USING p_row. DATA i_cnt TYPE i. i_cnt = p_row. DO 1 TIMES. CALL METHOD OF excel 'ROWS' = cell EXPORTING #1 = i_cnt. CALL METHOD OF cell 'SELECT'. CALL METHOD OF sheet 'CELL' = rowheight EXPORTING #1 = 1600. CALL METHOD OF excel 'SELECTION' = buffer. CALL METHOD OF buffer 'COPY'. i_cnt = i_cnt + 1. CALL METHOD OF excel 'ROWS' = cell EXPORTING #1 = i_cnt. CALL METHOD OF cell 'SELECT'. CALL METHOD OF excel 'ActiveSheet' = sheet. CALL METHOD OF excel 'SELECTION' = buffer. CALL METHOD OF buffer 'Entirerow' = buffer. CALL METHOD OF buffer 'Insert' = buffer. ENDDO. CLEAR i_cnt. ENDFORM. " COPY_LINE *&---------------------------------------------------------------------* *& Form DELE_LINE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* FORM dele_line USING p_row. CALL METHOD OF excel 'ROWS' = cell EXPORTING #1 = p_row. CALL METHOD OF cell 'SELECT'. CALL METHOD OF excel 'SELECTION' = buffer. CALL METHOD OF buffer 'DELETE'. ENDFORM. " DELE_LINE *&---------------------------------------------------------------------* *& Form GET_EXCEL_DATA *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* FORM get_excel_data USING pv_file pv_sheet pv_row pv_col. " 파일명, 시트번호, 행, 열 * DEFINE SCREEN CONTAINER DATA: lo_container TYPE REF TO cl_gui_custom_container. DATA: lo_error TYPE REF TO i_oi_error, lo_control TYPE REF TO i_oi_container_control, lo_document TYPE REF TO i_oi_document_proxy, lo_spreadsheet TYPE REF TO i_oi_spreadsheet, lo_retcode(256) TYPE c, ls_data TYPE soi_generic_item. DATA: lt_sheets TYPE soi_sheets_table. DATA: ls_sheets LIKE LINE OF lt_sheets. * Data declarations. DATA: lv_doc_name TYPE char256, lv_changed TYPE int4, lt_ranges TYPE soi_range_list, ls_ranges TYPE soi_range_item. CLEAR: gt_excel_data, gt_excel_data[]. * Create Instance control for container CALL METHOD c_oi_container_control_creator=>get_container_control IMPORTING control = lo_control error = lo_error. CHECK lo_error->has_failed NE c_x. * Create generic container linked to container in screen 100 CREATE OBJECT lo_container EXPORTING container_name = 'CONTAINER' EXCEPTIONS cntl_error = 1 cntl_system_error = 2 create_error = 3 lifetime_error = 4 lifetime_dynpro_dynpro_link = 5 OTHERS = 6. * Establish connection to GUI Control CALL METHOD lo_control->init_control EXPORTING r3_application_name = 'Excel Document Container' inplace_enabled = 'X' parent = lo_container IMPORTING error = lo_error. CHECK lo_error->has_failed NE c_x. * Create Document Proxy CALL METHOD lo_control->get_document_proxy EXPORTING document_type = soi_doctype_excel_sheet IMPORTING document_proxy = lo_document error = lo_error. CHECK lo_error->has_failed NE c_x. CONCATENATE 'FILE://' pv_file INTO lv_doc_name. * Open Spreadsheet in SAPWORKDIR CALL METHOD lo_document->open_document EXPORTING open_inplace = 'X' document_title = 'Excel' document_url = lv_doc_name no_flush = '' IMPORTING error = lo_error. CHECK lo_error->has_failed NE c_x. * Open Spreadsheet interface CALL METHOD lo_document->get_spreadsheet_interface EXPORTING no_flush = '' IMPORTING sheet_interface = lo_spreadsheet error = lo_error. CHECK lo_error->has_failed NE c_x. * Get Sheets. CALL METHOD lo_spreadsheet->get_sheets EXPORTING no_flush = '' updating = '' IMPORTING sheets = lt_sheets. READ TABLE lt_sheets into ls_sheets index pv_sheet. * Select Sheet. CALL METHOD lo_spreadsheet->select_sheet EXPORTING name = ls_sheets-sheet_name IMPORTING error = lo_error retcode = lo_retcode. CHECK lo_error->has_failed NE c_x. * Set selection for 1000 rows CALL METHOD lo_spreadsheet->set_selection EXPORTING top = 2 <-- 시작 포지션. left = 1 <-- rows = pv_row columns = pv_col. * Define Range in spreadsheet CALL METHOD lo_spreadsheet->insert_range EXPORTING name = 'Test' rows = pv_row columns = pv_col no_flush = '' IMPORTING error = lo_error. CHECK lo_error->has_failed NE c_x. ls_ranges-name = 'Test'. ls_ranges-rows = pv_row. ls_ranges-columns = pv_col. APPEND ls_ranges TO lt_ranges. * Get data CALL METHOD lo_spreadsheet->get_ranges_data EXPORTING all = '' no_flush = '' IMPORTING contents = gt_excel_data error = lo_error CHANGING ranges = lt_ranges. CHECK lo_error->has_failed NE c_x. * Close the document CALL METHOD lo_document->close_document EXPORTING do_save = '' no_flush = '' IMPORTING has_changed = lv_changed error = lo_error. CHECK lo_error->has_failed NE c_x. * Clear Document Resources CALL METHOD lo_document->release_document EXPORTING no_flush = '' IMPORTING error = lo_error. CHECK lo_error->has_failed NE c_x. * Clear table of file names FREE: lo_control.
댓글