엑셀 Multi Sheet Upload/Download Logic
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.
Subscribe
Login
0 Comments
Oldest
