需求
今日需要对excel的部分内容进行强调色展示（填充背景色）、设置border等等，发现网上都没有找到我想要的，然后自己找到了demo供大家参考
一、效果
运行程序，执行
excel效果
二、代码
新建REPT程序 ZDEMO_EXCEL2
代码如下（示例）：
*&---------------------------------------------------------------------*
*& Report ZDEMO_EXCEL2
*& Test Styles for ABAP2XLSX
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT zdemo_excel2.
DATA: lo_excel TYPE REF TO zcl_excel,
lo_worksheet TYPE REF TO zcl_excel_worksheet,
lo_style_bold TYPE REF TO zcl_excel_style,
lo_style_underline TYPE REF TO zcl_excel_style,
lo_style_filled TYPE REF TO zcl_excel_style,
lo_style_border TYPE REF TO zcl_excel_style,
lo_style_button TYPE REF TO zcl_excel_style,
lo_border_dark TYPE REF TO zcl_excel_style_border,
lo_border_light TYPE REF TO zcl_excel_style_border.
DATA: lv_style_bold_guid TYPE zexcel_cell_style,
lv_style_underline_guid TYPE zexcel_cell_style,
lv_style_filled_guid TYPE zexcel_cell_style,
lv_style_filled_green_guid TYPE zexcel_cell_style,
lv_style_border_guid TYPE zexcel_cell_style,
lv_style_button_guid TYPE zexcel_cell_style,
lv_style_filled_turquoise_guid TYPE zexcel_cell_style.
DATA: lv_file TYPE xstring,
lv_bytecount TYPE i,
lt_file_tab TYPE solix_tab.
DATA: lv_full_path TYPE string,
lv_workdir TYPE string,
lv_file_separator TYPE c.
CONSTANTS: gc_save_file_name TYPE string VALUE '02_Styles.xlsx'.
INCLUDE zdemo_excel_outputopt_incl.
START-OF-SELECTION.
" Creates active sheet
CREATE OBJECT lo_excel.
" Create border object
CREATE OBJECT lo_border_dark.
lo_border_dark->border_color-rgb = zcl_excel_style_color=>c_black.
lo_border_dark->border_style = zcl_excel_style_border=>c_border_thin.
CREATE OBJECT lo_border_light.
lo_border_light->border_color-rgb = zcl_excel_style_color=>c_gray.
lo_border_light->border_style = zcl_excel_style_border=>c_border_thin.
" Create a bold / italic style
lo_style_bold = lo_excel->add_new_style( ).
lo_style_bold->font->bold = abap_true.
lo_style_bold->font->italic = abap_true.
lo_style_bold->font->name = zcl_excel_style_font=>c_name_arial.
lo_style_bold->font->scheme = zcl_excel_style_font=>c_scheme_none.
lo_style_bold->font->color-rgb = zcl_excel_style_color=>c_red.
lv_style_bold_guid = lo_style_bold->get_guid( ).
" Create an underline double style
lo_style_underline = lo_excel->add_new_style( ).
lo_style_underline->font->underline = abap_true.
lo_style_underline->font->underline_mode = zcl_excel_style_font=>c_underline_double.
lo_style_underline->font->name = zcl_excel_style_font=>c_name_roman.
lo_style_underline->font->scheme = zcl_excel_style_font=>c_scheme_none.
lo_style_underline->font->family = zcl_excel_style_font=>c_family_roman.
lv_style_underline_guid = lo_style_underline->get_guid( ).
" Create filled style yellow
lo_style_filled = lo_excel->add_new_style( ).
lo_style_filled->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
lo_style_filled->fill->fgcolor-theme = zcl_excel_style_color=>c_theme_accent6.
lv_style_filled_guid = lo_style_filled->get_guid( ).
" Create border with button effects
lo_style_button = lo_excel->add_new_style( ).
lo_style_button->borders->right = lo_border_dark.
lo_style_button->borders->down = lo_border_dark.
lo_style_button->borders->left = lo_border_light.
lo_style_button->borders->top = lo_border_light.
lv_style_button_guid = lo_style_button->get_guid( ).
"Create style with border
lo_style_border = lo_excel->add_new_style( ).
lo_style_border->borders->allborders = lo_border_dark.
lo_style_border->borders->diagonal = lo_border_dark.
lo_style_border->borders->diagonal_mode = zcl_excel_style_borders=>c_diagonal_both.
lv_style_border_guid = lo_style_border->get_guid( ).
" Create filled style green
lo_style_filled = lo_excel->add_new_style( ).
lo_style_filled->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
lo_style_filled->fill->fgcolor-rgb = zcl_excel_style_color=>c_green.
lo_style_filled->font->name = zcl_excel_style_font=>c_name_cambria.
lo_style_filled->font->scheme = zcl_excel_style_font=>c_scheme_major.
lv_style_filled_green_guid = lo_style_filled->get_guid( ).
" Create filled style turquoise using legacy excel ver <= 2003 palette. (https://code.sdn.sap.com/spaces/abap2xlsx/tickets/92)
lo_style_filled = lo_excel->add_new_style( ).
lo_excel->legacy_palette->set_color( "replace built-in color from palette with out custom RGB turquoise
ip_index = 16
ip_color = '0040E0D0' ).
lo_style_filled->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
lo_style_filled->fill->fgcolor-indexed = 16.
lv_style_filled_turquoise_guid = lo_style_filled->get_guid( ).
" Get active sheet
lo_worksheet = lo_excel->get_active_worksheet( ).
lo_worksheet->set_title( ip_title = 'Styles' ).
lo_worksheet->set_cell( ip_column = 'B' ip_row = 2 ip_value = 'Hello world' ).
lo_worksheet->set_cell( ip_column = 'C' ip_row = 3 ip_value = 'Bold text' ip_style = lv_style_bold_guid ).
lo_worksheet->set_cell( ip_column = 'D' ip_row = 4 ip_value = 'Underlined text' ip_style = lv_style_underline_guid ).
lo_worksheet->set_cell( ip_column = 'B' ip_row = 5 ip_value = 'Filled text' ip_style = lv_style_filled_guid ).
lo_worksheet->set_cell( ip_column = 'C' ip_row = 6 ip_value = 'Borders' ip_style = lv_style_border_guid ).
lo_worksheet->set_cell( ip_column = 'D' ip_row = 7 ip_value = 'I''m not a button :)' ip_style = lv_style_button_guid ).
lo_worksheet->set_cell( ip_column = 'B' ip_row = 9 ip_value = 'Modified color for Excel 2003' ip_style = lv_style_filled_turquoise_guid ).
" Fill the cell and apply one style
lo_worksheet->set_cell( ip_column = 'B' ip_row = 6 ip_value = 'Filled text' ip_style = lv_style_filled_guid ).
" Change the style
lo_worksheet->set_cell_style( ip_column = 'B' ip_row = 6 ip_style = lv_style_filled_green_guid ).
" Add Style to an empty cell to test Fix for Issue
"#44 Exception ZCX_EXCEL thrown when style is set for an empty cell
" https://code.sdn.sap.com/spaces/abap2xlsx/tickets/44-exception-zcx_excel-thrown-when-style-is-set-for-an-empty-cell
lo_worksheet->set_cell_style( ip_column = 'E' ip_row = 6 ip_style = lv_style_filled_green_guid ).
* CREATE OBJECT lo_excel_writer TYPE zcl_excel_writer_2007.
* lv_file = lo_excel_writer->write_file( lo_excel ).
*
* " Convert to binary
* CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
* EXPORTING
* buffer = lv_file
* IMPORTING
* output_length = lv_bytecount
* TABLES
* binary_tab = lt_file_tab.
** " This method is only available on AS ABAP > 6.40
** lt_file_tab = cl_bcs_convert=>xstring_to_solix( iv_xstring = lv_file ).
** lv_bytecount = xstrlen( lv_file ).
*
* " Save the file
* cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = lv_bytecount
* filename = lv_full_path
* filetype = 'BIN'
* CHANGING data_tab = lt_file_tab ).
lcl_output=>output( lo_excel ).
包含include zdemo_excel_outputopt_incl
*&---------------------------------------------------------------------*
*& Include ZDEMO_EXCEL_OUTPUTOPT_INCL
*&---------------------------------------------------------------------*
CLASS lcl_output DEFINITION CREATE PRIVATE.
PUBLIC SECTION.
CLASS-METHODS: output IMPORTING cl_excel TYPE REF TO zcl_excel,
f4_path RETURNING value(selected_folder) TYPE string,
parametertexts.
PRIVATE SECTION.
METHODS: download_frontend,
download_backend,
display_online,
send_email.
DATA: xdata TYPE xstring, " Will be used for sending as email
t_rawdata TYPE solix_tab, " Will be used for downloading or open directly
bytecount TYPE i. " Will be used for downloading or open directly
ENDCLASS. "lcl_output DEFINITION
SELECTION-SCREEN BEGIN OF BLOCK bl1 WITH FRAME TITLE txt_bl1.
PARAMETERS: rb_down RADIOBUTTON GROUP rb1 DEFAULT 'X' USER-COMMAND space.
PARAMETERS: rb_back RADIOBUTTON GROUP rb1.
PARAMETERS: rb_show RADIOBUTTON GROUP rb1.
PARAMETERS: rb_send RADIOBUTTON GROUP rb1.
PARAMETERS: p_path TYPE string LOWER CASE MODIF ID pat.
PARAMETERS: p_email TYPE string LOWER CASE MODIF ID ema.
PARAMETERS: p_backfn TYPE text40 NO-DISPLAY.
SELECTION-SCREEN END OF BLOCK bl1.
AT SELECTION-SCREEN OUTPUT.
LOOP AT SCREEN.
IF rb_down IS INITIAL AND screen-group1 = 'PAT'.
screen-input = 0.
screen-invisible = 1.
ENDIF.
IF rb_send IS INITIAL AND screen-group1 = 'EMA'.
screen-input = 0.
screen-invisible = 1.
ENDIF.
MODIFY SCREEN.
ENDLOOP.
INITIALIZATION.
IF sy-batch IS INITIAL.
cl_gui_frontend_services=>get_sapgui_workdir( CHANGING sapworkdir = p_path ).
cl_gui_cfw=>flush( ).
ENDIF.
lcl_output=>parametertexts( ). " If started in language w/o textelements translated set defaults
sy-title = gc_save_file_name.
txt_bl1 = 'Output options'(bl1).
p_backfn = gc_save_file_name. " Use as default if nothing else is supplied by submit
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_path.
p_path = lcl_output=>f4_path( ).
*----------------------------------------------------------------------*
* CLASS lcl_output IMPLEMENTATION
*----------------------------------------------------------------------*
CLASS lcl_output IMPLEMENTATION.
METHOD output.
DATA: cl_output TYPE REF TO lcl_output,
cl_writer TYPE REF TO zif_excel_writer.
CREATE OBJECT cl_output.
CREATE OBJECT cl_writer TYPE zcl_excel_writer_2007.
cl_output->xdata = cl_writer->write_file( cl_excel ).
* After 6.40 via cl_bcs_convert
cl_output->t_rawdata = cl_bcs_convert=>xstring_to_solix( iv_xstring = cl_output->xdata ).
cl_output->bytecount = xstrlen( cl_output->xdata ).
* before 6.40
* CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
* EXPORTING
* buffer = cl_output->xdata
* IMPORTING
* output_length = cl_output->bytecount
* TABLES
* binary_tab = cl_output->t_rawdata.
CASE 'X'.
WHEN rb_down.
IF sy-batch IS INITIAL.
cl_output->download_frontend( ).
ELSE.
MESSAGE e001(00) WITH 'Frontenddownload impossible in background processing'.
ENDIF.
WHEN rb_back.
cl_output->download_backend( ).
WHEN rb_show.
IF sy-batch IS INITIAL.
cl_output->display_online( ).
ELSE.
MESSAGE e001(00) WITH 'Online display absurd in background processing'.
ENDIF.
WHEN rb_send.
cl_output->send_email( ).
ENDCASE.
ENDMETHOD. "output
METHOD f4_path.
DATA: new_path TYPE string,
repid TYPE syrepid,
dynnr TYPE sydynnr,
lt_dynpfields TYPE TABLE OF dynpread,
ls_dynpfields LIKE LINE OF lt_dynpfields.
* Get current value
dynnr = sy-dynnr.
repid = sy-repid.
ls_dynpfields-fieldname = 'P_PATH'.
APPEND ls_dynpfields TO lt_dynpfields.
CALL FUNCTION 'DYNP_VALUES_READ'
EXPORTING
dyname = repid
dynumb = dynnr
TABLES
dynpfields = lt_dynpfields
EXCEPTIONS
invalid_abapworkarea = 1
invalid_dynprofield = 2
invalid_dynproname = 3
invalid_dynpronummer = 4
invalid_request = 5
no_fielddescription = 6
invalid_parameter = 7
undefind_error = 8
double_conversion = 9
stepl_not_found = 10
OTHERS = 11.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
EXIT.
ENDIF.
READ TABLE lt_dynpfields INTO ls_dynpfields INDEX 1.
new_path = ls_dynpfields-fieldvalue.
selected_folder = new_path.
cl_gui_frontend_services=>directory_browse(
EXPORTING
window_title = 'Select path to download EXCEL-file'
initial_folder = new_path
CHANGING
selected_folder = new_path
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4
).
cl_gui_cfw=>flush( ).
CHECK new_path IS NOT INITIAL.
selected_folder = new_path.
ENDMETHOD. "f4_path
METHOD parametertexts.
* If started in language w/o textelements translated set defaults
* Furthermore I don't have to change the selectiontexts of all demoreports.
DEFINE default_parametertext.
if %_&1_%_app_%-text = '&1' or
%_&1_%_app_%-text is initial.
%_&1_%_app_%-text = &2.
endif.
END-OF-DEFINITION.
default_parametertext: rb_down 'Save to frontend',
rb_back 'Save to backend',
rb_show 'Direct display',
rb_send 'Send via email',
p_path 'Frontend-path to download to',
p_email 'Email to send xlsx to'.
ENDMETHOD. "parametertexts
METHOD: download_frontend.
DATA: filename TYPE string.
* I don't like p_path here - but for this include it's ok
filename = p_path.
* Add trailing "\" or "/"
IF filename CA '/'.
REPLACE REGEX '([^/])\s*$' IN filename WITH '$1/' .
ELSE.
REPLACE REGEX '([^\\])\s*$' IN filename WITH '$1\\'.
ENDIF.
CONCATENATE filename gc_save_file_name INTO filename.
* Get trailing blank
cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = bytecount
filename = filename
filetype = 'BIN'
CHANGING data_tab = t_rawdata ).
ENDMETHOD. "download_frontend
METHOD download_backend.
DATA: bytes_remain TYPE i.
FIELD-SYMBOLS: <rawdata> LIKE LINE OF t_rawdata.
OPEN DATASET p_backfn FOR OUTPUT IN BINARY MODE.
CHECK sy-subrc = 0.
bytes_remain = bytecount.
LOOP AT t_rawdata ASSIGNING <rawdata>.
AT LAST.
CHECK bytes_remain >= 0.
TRANSFER <rawdata> TO p_backfn LENGTH bytes_remain.
EXIT.
ENDAT.
TRANSFER <rawdata> TO p_backfn.
SUBTRACT 255 FROM bytes_remain. " Solix hat L盲nge 255
ENDLOOP.
CLOSE DATASET p_backfn.
IF sy-calld = 'X'. " no need to display anything if download was selected and report was called for demo purposes
LEAVE PROGRAM.
ELSE.
MESSAGE 'Data transferred to default backend directory' TYPE 'I'.
ENDIF.
ENDMETHOD. "download_backend
METHOD display_online.
DATA:error TYPE REF TO i_oi_error,
t_errors TYPE STANDARD TABLE OF REF TO i_oi_error WITH NON-UNIQUE DEFAULT KEY,
cl_control TYPE REF TO i_oi_container_control,"OIContainerCtrl
cl_document TYPE REF TO i_oi_document_proxy. "Office Dokument
c_oi_container_control_creator=>get_container_control( IMPORTING control = cl_control
error = error ).
APPEND error TO t_errors.
cl_control->init_control( EXPORTING inplace_enabled = 'X'
no_flush = 'X'
r3_application_name = 'Demo Document Container'
parent = cl_gui_container=>screen0
IMPORTING error = error
EXCEPTIONS OTHERS = 2 ).
APPEND error TO t_errors.
cl_control->get_document_proxy( EXPORTING document_type = 'Excel.Sheet' " EXCEL
no_flush = ' '
IMPORTING document_proxy = cl_document
error = error ).
APPEND error TO t_errors.
* Errorhandling should be inserted here
cl_document->open_document_from_table( EXPORTING document_size = bytecount
document_table = t_rawdata
open_inplace = 'X' ).
WRITE: '.'. " To create an output. That way screen0 will exist
ENDMETHOD. "display_online
METHOD send_email.
* Needed to send emails
DATA: bcs_exception TYPE REF TO cx_bcs,
errortext TYPE string,
cl_send_request TYPE REF TO cl_bcs,
cl_document TYPE REF TO cl_document_bcs,
cl_recipient TYPE REF TO if_recipient_bcs,
cl_sender TYPE REF TO cl_cam_address_bcs,
t_attachment_header TYPE soli_tab,
wa_attachment_header LIKE LINE OF t_attachment_header,
attachment_subject TYPE sood-objdes,
sood_bytecount TYPE sood-objlen,
mail_title TYPE so_obj_des,
t_mailtext TYPE soli_tab,
wa_mailtext LIKE LINE OF t_mailtext,
send_to TYPE adr6-smtp_addr,
sent TYPE os_boolean.
mail_title = 'Mail title'.
wa_mailtext = 'Mailtext'.
APPEND wa_mailtext TO t_mailtext.
TRY.
* Create send request
cl_send_request = cl_bcs=>create_persistent( ).
* Create new document with mailtitle and mailtextg
cl_document = cl_document_bcs=>create_document( i_type = 'RAW' "#EC NOTEXT
i_text = t_mailtext
i_subject = mail_title ).
* Add attachment to document
* since the new excelfiles have an 4-character extension .xlsx but the attachment-type only holds 3 charactes .xls,
* we have to specify the real filename via attachment header
* Use attachment_type xls to have SAP display attachment with the excel-icon
attachment_subject = gc_save_file_name.
CONCATENATE '&SO_FILENAME=' attachment_subject INTO wa_attachment_header.
APPEND wa_attachment_header TO t_attachment_header.
* Attachment
sood_bytecount = bytecount. " next method expects sood_bytecount instead of any positive integer *sigh*
cl_document->add_attachment( i_attachment_type = 'XLS' "#EC NOTEXT
i_attachment_subject = attachment_subject
i_attachment_size = sood_bytecount
i_att_content_hex = t_rawdata
i_attachment_header = t_attachment_header ).
* add document to send request
cl_send_request->set_document( cl_document ).
* set sender in case if no own email is availabe
* cl_sender = cl_cam_address_bcs=>create_internet_address( 'sender@sender.sender' ).
* cl_send_request->set_sender( cl_sender ).
* add recipient(s) - here only 1 will be needed
send_to = p_email.
IF send_to IS INITIAL.
send_to = 'no_email@no_email.no_email'. " Place into SOST in any case for demonstration purposes
ENDIF.
cl_recipient = cl_cam_address_bcs=>create_internet_address( send_to ).
cl_send_request->add_recipient( cl_recipient ).
* Und abschicken
sent = cl_send_request->send( i_with_error_screen = 'X' ).
COMMIT WORK.
IF sent IS INITIAL.
MESSAGE i500(sbcoms) WITH p_email.
ELSE.
MESSAGE s022(so).
MESSAGE 'Document ready to be sent - Check SOST or SCOT' TYPE 'I'.
ENDIF.
CATCH cx_bcs INTO bcs_exception.
errortext = bcs_exception->if_message~get_text( ).
MESSAGE errortext TYPE 'I'.
ENDTRY.
ENDMETHOD. "send_email
ENDCLASS. "lcl_output IMPLEMENTATION
寄语
希望对你有所帮助
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
