아휴 이제 다신 백지에서 안짠다
매번 필드에 값을 일일이 넣어줘서 그리는데 한세월이었는데
속도가 많이 개선되어서 저장해놓으려고 한다.
소스 하단에는 결과물과 ColorIndex 첨부.
셀별 값 입력에서 Clipboard 복붙으로 변경으로 속도개선
필드 값 표기 속성 추가 : Decimals, Bold, Size
실행순서
1. 셀 컬러 및 테두리 레이아웃 설정
2. 데이터 COPY&PASTE
3. 필드 값 표기 속성 설정
65 * 27 = 1,755개 필드 값 실행속도
30초 -> 3초로 줄어들었음.. 데이터 양이 늘어날수록 개선효과가 더 클듯
헌데.. 일정 이상이 되면 클립보드에 다 안들어가지 않을지도..
이때는 LT_CHAR의 필드길이를 가늠해서 조절해보자.
-파일은 아래 내용과 동일-
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'.
endform.
결과물
ColorIndex 참조
내용추가(22.05.26)
컬러인덱스 말고 그냥 Color값을 주면 모든 색을 다 쓸 수 있다.
다만 변환을 거쳐야 하는데.. 6자리 HEX값을 가지고 Longname을 찾아보도록 하자.
첨부한 엑셀을 통해 확인할 수 있다.
2번째 시트에는 셀별 정렬 서식을 확인할 수 있다.
내용추가(22.06.17)
이외에도 사용되는 엑셀 상수 리스트
xlLeft 등 이름으로 입력하면 안먹으니, 변환된 상수로 입력해주자.