brunch

라이킷 6 댓글

You can make anything
by writing

C.S.Lewis

계정을 잊어버리셨나요?

by 동네아밥 May 20. 2022

EXCEL OLE

아휴 이제 다신 백지에서 안짠다

매번 필드에 값을 일일이 넣어줘서 그리는데 한세월이었는데

속도가 많이 개선되어서 저장해놓으려고 한다.

소스 하단에는 결과물과 ColorIndex 첨부.

셀별 값 입력에서 Clipboard 복붙으로 변경으로 속도개선

필드 값 표기 속성 추가 : Decimals, Bold, Size

실행순서

1. 셀 컬러 및 테두리 레이아웃 설정

2. 데이터 COPY&PASTE

3. 필드 값 표기 속성 설정

65 * 27 = 1,755개 필드 값 실행속도

30초 -> 3초로 줄어들었음.. 데이터 양이 늘어날수록 개선효과가 더 클듯

헌데.. 일정 이상이 되면 클립보드에 다 안들어가지 않을지도..

이때는 LT_CHAR의 필드길이를 가늠해서 조절해보자.

-파일은 아래 내용과 동일-

get_xls.txt

form get_xls using    value(pt_mplan) like gt_mplan[].

   data: begin of ls_check,
           matnr     like ztppgt00060-matnr,
           maktx     like makt-maktx,
           longm     like makt-zz_maktx,
           verid     like ztppgt00060-verid,
           bomc      like ztppgt00060-bomc,
           mdv01     like ztppgt00060-mdv01,
           idnrk     like ztppgt00060-idnrk,
           maktx1    like makt-maktx,
           longm1    like makt-zz_maktx,
           power(10),
           gtext(15),
         end of ls_check,

         lv_fildname type char70,
         lv_val      type char70,
         lv_rc       type i,

         begin of ls_char,
           line(3000),
         end of ls_char,
         lt_char like table of ls_char,

         lt_desc type abap_component_tab,
         lo_type type ref to cl_abap_datadescr,
         lo_struc type ref to cl_abap_structdescr,
         lo_table type ref to cl_abap_tabledescr,
         lo_data type ref to data.

   data: lv_excel    type ole2_object, " Excel object
         lv_wbl      type ole2_object, " List of workbooks
         lv_wb       type ole2_object, " Workbook
         lv_cell     type ole2_object, " Cell
         lv_font     type ole2_object, " Font
         lv_interior type ole2_object, " Interior
         lv_columns type ole2_object, " Columns
         lv_border   type ole2_object, " Borders
         lv_begin    type ole2_object, " Borders
         lv_end      type ole2_object. " Borders

   data: lv_title    type i value 15, " Light Gray
         lv_total    type i value 50, " Green
         lv_subtotal type i value 35, " Light Green
         lv_conf     type i value 36. " Light Yellow

   field-symbols: <lv_val> type any,
                  <lt_tab> type standard table.

*--------------------------------------------------------------------*
   "FILL OUTTAB Contents
   loop at pt_mplan into data(ls_mplan).

     if ls_mplan-idnrk is not initial.

       move: ls_mplan-idnrk to ls_check-idnrk,
             ls_mplan-maktx1 to ls_check-maktx1,
             ls_mplan-longm1 to ls_check-longm1,
             ls_mplan-power to ls_check-power.

     elseif ls_mplan-verid is not initial.

       move: ls_mplan-verid to ls_check-verid,
             ls_mplan-bomc   to ls_check-bomc ,
             ls_mplan-mdv01 to ls_check-mdv01.

       move: ls_mplan-idnrk to ls_check-idnrk ,
             ls_mplan-maktx1 to ls_check-maktx1,
             ls_mplan-longm1 to ls_check-longm1,
             ls_mplan-power to ls_check-power .

     elseif ls_mplan-matnr is not initial.

       move-corresponding ls_mplan to ls_check.

     endif.

     modify pt_mplan from corresponding #( base ( ls_mplan ) ls_check ).

   endloop.

*--------------------------------------------------------------------*
   "GET Column List
   go_grid4->get_frontend_fieldcatalog(
     importing
       et_fieldcatalog = data(lt_fcat)
   ).

   delete lt_fcat where no_out = 'X'.
   sort lt_fcat by col_pos.
   data(lv_cols) = lines( lt_fcat ).

   call function 'SAPGUI_PROGRESS_INDICATOR'
     exporting
       text       = 'Exporting Data to Excel...'.

*--------------------------------------------------------------------*
   "OLE CONTROL
   create object lv_excel 'EXCEL.APPLICATION'.
   call method of lv_excel 'Workbooks' = lv_wbl.
   call method of lv_wbl 'Add' = lv_wb.
   get property of lv_excel 'ActiveSheet' = lv_wb.

*--------------------------------------------------------------------*
   "CREATE Header
   loop at lt_fcat into data(ls_fcat).

     data(lv_col) = sy-tabix.

     call method of lv_excel 'Cells' = lv_cell
       exporting
         #1 = 1 "ROW
         #2 = lv_col. "COL

     if ls_fcat-coltext is initial.
       ls_fcat-coltext = ls_fcat-scrtext_s.
     endif.

     set property of lv_cell 'Value' = ls_fcat-coltext no flush.

     get property of lv_cell 'Interior' = lv_interior.
     set property of lv_interior 'ColorIndex' = lv_title no flush.

     get property of lv_cell 'Font' = lv_font.
     set property of lv_font 'Bold' = 1.
     set property of lv_font 'Size' = 11.

   endloop.

*--------------------------------------------------------------------*
   "TITLE + CONTENTS RowNO.
   data(lv_rows) = lines( pt_mplan ) + 1.

   "GET MENA COLUMN No.: to set decimals property
   data(lt_mena) = lt_fcat.
   delete lt_mena where fieldname(4) <> 'MENA'.
   read table lt_mena into ls_fcat index 1.
   data(lv_mena_s) = ls_fcat-col_pos.
   read table lt_mena into ls_fcat index lines( lt_mena ).
   data(lv_mena_e) = ls_fcat-col_pos.

*--------------------------------------------------------------------*
   call function 'CONTROL_FLUSH'.

   loop at pt_mplan into ls_mplan.

     data(lv_row) = sy-tabix + 1.

     loop at lt_fcat into ls_fcat.

       lv_col = sy-tabix.

       "CREATE VALUE & SET FORMAT
       lv_fildname = 'LS_MPLAN-' && ls_fcat-fieldname.
       assign (lv_fildname) to <lv_val>.

       case ls_fcat-fieldname(4).
         when 'MENA'.
           write <lv_val> to lv_val decimals 3 left-justified.
         when others.
           write <lv_val> to lv_val left-justified.
       endcase.

*--------------------------------------------------------------------*
       "SET EXPORT TAB
       ls_char-line = ls_char-line && lv_val && cl_abap_char_utilities=>horizontal_tab.
       at last.
         append ls_char to lt_char. clear ls_char.
       endat.

*--------------------------------------------------------------------*

  "SET MENA~ as TEXT, Right justified
   call method of lv_wb 'Cells' = lv_begin
     exporting
       #1 = 2
       #2 = lv_mena_s.
   call method of lv_wb 'Cells' = lv_end
     exporting
       #1 = lv_rows
       #2 = lv_mena_e.
   call method of lv_wb 'Range' = lv_cell
     exporting
       #1 = lv_begin
       #2 = lv_end.

   call method of lv_cell 'Select'.
   set property of lv_cell 'NumberFormat' = '@' no flush.
   set property of lv_cell 'HorizontalAlignment' = -4152.
*--------------------------------------------------------------------*
       "SET COLOR
       read table ls_mplan-fcol into data(ls_fcol) with key fname = ls_fcat-fieldname.
       if sy-subrc = 0.
         call method of lv_excel 'Cells' = lv_cell
           exporting
             #1 = lv_row "ROW
             #2 = lv_col. "COL
         "FIELDCOLOR => CONFIRMED
         get property of lv_cell 'Interior' = lv_interior.
         set property of lv_interior 'ColorIndex' = lv_conf no flush.
       else.
         if ls_mplan-lcol = 'C510'.
           call method of lv_excel 'Cells' = lv_cell
             exporting
               #1 = lv_row "ROW
               #2 = lv_col. "COL
           "ROWCOLOR => TOTAL
           get property of lv_cell 'Interior' = lv_interior.
           set property of lv_interior 'ColorIndex' = lv_total no flush.
         elseif ls_mplan-lcol = 'C500'.
           call method of lv_excel 'Cells' = lv_cell
             exporting
               #1 = lv_row "ROW
               #2 = lv_col. "COL
           "ROWCOLOR => SUBTOTAL
           get property of lv_cell 'Interior' = lv_interior.
           set property of lv_interior 'ColorIndex' = lv_subtotal no flush.
         endif.
       endif.

     endloop.

   endloop.

   cl_gui_frontend_services=>clipboard_export(
     exporting
       no_auth_check = 'X'
     importing
       data                 = lt_char
     changing
       rc                   = lv_rc
     exceptions
       cntl_error           = 1
       error_no_gui         = 2
       not_supported_by_gui = 3
       no_authority         = 4
       others               = 5
   ).

*--------------------------------------------------------------------*
   "PASTE Contents
   call method of lv_wb 'Cells' = lv_begin
     exporting
       #1 = 2
       #2 = 1.
   call method of lv_wb 'Cells' = lv_end
     exporting
       #1 = lv_rows
       #2 = lv_cols.
   call method of lv_wb 'Range' = lv_cell
     exporting
       #1 = lv_begin
       #2 = lv_end.

   call method of lv_cell 'Select'.
   call method of lv_wb 'Paste'.

   get property of lv_cell 'Font' = lv_font.
   set property of lv_font 'Size' = 10.

*--------------------------------------------------------------------*
   "SET Border
   call method of lv_wb 'Cells' = lv_begin
     exporting
       #1 = 1
       #2 = 1.
   call method of lv_wb 'Cells' = lv_end
     exporting
       #1 = lv_rows
       #2 = lv_cols.
   call method of lv_wb 'Range' = lv_cell
     exporting
       #1 = lv_begin
       #2 = lv_end.

   call method of lv_cell 'Select'.

   call method of lv_cell 'Borders' = lv_border
     exporting
       #1 = '7'. " xlEdgeLeft
   set property of lv_border 'LineStyle' = 1 no flush.

   call method of lv_cell 'Borders' = lv_border
     exporting
       #1 = '8'. " xlEdgeTop
   set property of lv_border 'LineStyle' = 1 no flush.

   call method of lv_cell 'Borders' = lv_border
   exporting
     #1 = '9'. " xlEdgeTop
   set property of lv_border 'LineStyle' = 1 no flush.

   call method of lv_cell 'Borders' = lv_border
   exporting
     #1 = '10'. " xlEdgeTop
   set property of lv_border 'LineStyle' = 1 no flush.

   call method of lv_cell 'Borders' = lv_border
   exporting
     #1 = '11'. " xlInsideVertical
   set property of lv_border 'LineStyle' = 1 no flush.

   call method of lv_cell 'Borders' = lv_border
   exporting
     #1 = '12'. " xlInsideHorizontal
   set property of lv_border 'LineStyle' = 1 no flush.

*--------------------------------------------------------------------*
   "SET MENA~ Decimals 3
   call method of lv_wb 'Cells' = lv_begin
     exporting
       #1 = 1
       #2 = lv_mena_s.
   call method of lv_wb 'Cells' = lv_end
     exporting
       #1 = lv_rows
       #2 = lv_mena_e.
   call method of lv_wb 'Range' = lv_cell
     exporting
       #1 = lv_begin
       #2 = lv_end.

   call method of lv_cell 'Select'.
   set property of lv_cell 'NumberFormat' = '0,000.000' no flush.

*--------------------------------------------------------------------*
   "OPTIMIZE All Cols
   call method of lv_wb 'Columns' = lv_columns.
   call method of lv_columns 'Autofit' no flush.
   set property of lv_excel 'Visible' = 1.

   "SET Cursor
   call method of lv_excel 'Cells' = lv_cell
     exporting
       #1 = 1
       #2 = 1.
   call method of lv_cell 'Select'.
*--------------------------------------------------------------------*
   "CLEAR Clipboard
   refresh: lt_char.
   cl_gui_frontend_services=>clipboard_export(
     exporting
       no_auth_check = 'X'
     importing
       data                 = lt_char
     changing
       rc                   = lv_rc
     exceptions
       cntl_error           = 1
       error_no_gui         = 2
       not_supported_by_gui = 3
       no_authority         = 4
       others               = 5
   ).

*--------------------------------------------------------------------*
   call function 'CONTROL_FLUSH'.