ABAP 使用ABAP2XLSX操作EXCEL

最新推荐文章于 2024-10-19 13:56:26 发布

尤其是十月的风

最新推荐文章于 2024-10-19 13:56:26 发布

阅读量6k

点赞数 4

分类专栏： ABAP基础文章标签： SAP ABAP ABAP2XLSX

本文链接：https://blog.csdn.net/qq_34060435/article/details/121697307

版权

ABAP基础专栏收录该内容

49 篇文章

订阅专栏

本文详细介绍了如何使用ABAP2XLSX库在ABAP编程环境中操作Excel，包括创建工作表、设置边框样式、列宽和行高，添加内容、合并单元格，插入图片以及导出定制化的Excel文件。通过实例展示了如何设置工作簿、选择工作表并完成复杂的数据格式化和美化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用ABAP2XLSX操作EXCEL

前言：可以参考网址（ABAP2XLSX,操作excel），其中有更多的操作

数据声明

DATA:lo_excel     TYPE REF TO zcl_excel,
     lo_worksheet TYPE REF TO zcl_excel_worksheet.
DATA:
    lo_worksheet          TYPE REF TO zcl_excel_worksheet,
    lo_column             TYPE REF TO zcl_excel_column,
    lo_border             TYPE REF TO zcl_excel_style_border,
    lo_style              TYPE REF TO zcl_excel_style,
    lv_style_01           TYPE zexcel_cell_style,
    lv_style_02           TYPE zexcel_cell_style,
    lv_style_03           TYPE zexcel_cell_style,
    lv_style_04           TYPE zexcel_cell_style,
    lv_style_05           TYPE zexcel_cell_style,
    lv_style_06           TYPE zexcel_cell_style,
    lv_style_07           TYPE zexcel_cell_style,
    lv_style_08           TYPE zexcel_cell_style,
    lv_style_09           TYPE zexcel_cell_style,
    lv_style_10           TYPE zexcel_cell_style,
    lv_style_11           TYPE zexcel_cell_style,
    lv_row                TYPE syindex,
    lv_index              TYPE syindex,
    lv_init_total_formula TYPE string,
    lv_total_formula      TYPE string,
    lv_fname              TYPE fieldname,
    lv_numc2              TYPE numc2.

创建EXCEL对线和WorkSheet

"设置EXCEL
        CREATE OBJECT lo_excel.
        " First Worksheet
        lo_worksheet = lo_excel->get_active_worksheet( ).
        lo_worksheet->set_title( TEXT-041 ). 

        " Second Worksheet
        lo_worksheet = lo_excel->add_new_worksheet( ).
        lo_worksheet->set_title( TEXT-042 ). 

        " Third Worksheet
        lo_worksheet = lo_excel->add_new_worksheet( ).
        lo_worksheet->set_title( TEXT-043 ). 

        " Third Worksheet
        lo_worksheet = lo_excel->add_new_worksheet( ).
        lo_worksheet->set_title( TEXT-044 ).

选择要操作的Sheet页

lo_excel->set_active_sheet_index( '3' ).
  "Get active sheet
  lo_worksheet = lo_excel->get_active_worksheet( ).

设置边框

***设置边框
  "边框样式——黑色细线条
  CREATE OBJECT lo_border.
  lo_border->border_color-rgb = zcl_excel_style_color=>c_black.
  lo_border->border_style     = zcl_excel_style_border=>c_border_medium.

设置样式

***设置样式
  "设置样式1——无背景，粗体，11号，水平居中，垂直居中,Arial
  lo_style                        = lo_excel->add_new_style( ).
  lo_style->fill->filltype        = zcl_excel_style_fill=>c_fill_none.
  lo_style->font->bold            = abap_true.
  lo_style->font->size            = 20.
  lo_style->font->name   = zcl_excel_style_font=>c_name_arial.
  lo_style->font->scheme = zcl_excel_style_font=>c_scheme_none.
  lo_style->alignment->horizontal = zcl_excel_style_alignment=>c_horizontal_center.
  lo_style->alignment->vertical   = zcl_excel_style_alignment=>c_vertical_center.
  lv_style_01                     = lo_style->get_guid( ).

设置列宽

***设置列宽（自适应列宽只会对英文正确计算宽度，中文往往更长，所以这里不适用自适应列宽）
  lo_worksheet->set_column_width( ip_column = 'A' ip_width_fix = 8 ).
  lo_worksheet->set_column_width( ip_column = 'B' ip_width_fix = 17 ).
  lo_worksheet->set_column_width( ip_column = 'C' ip_width_fix = 34 ).
  lo_worksheet->set_column_width( ip_column = 'D' ip_width_fix = 17 ).
  lo_worksheet->set_column_width( ip_column = 'E' ip_width_fix = 11 ).
  lo_worksheet->set_column_width( ip_column = 'F' ip_width_fix = 11 ).
  lo_worksheet->set_column_width( ip_column = 'G' ip_width_fix = 13 ).
  lo_worksheet->set_column_width( ip_column = 'H' ip_width_fix = 19 ).
  lo_worksheet->set_column_width( ip_column = 'I' ip_width_fix = 19 ).

添加行

ADD 1 TO lv_row.

设置单元格内容

lo_worksheet->set_cell( ip_column = 'A' ip_row = lv_row ip_value = TEXT-467 ip_style = lv_style_11 ).

设置行高

lo_worksheet->set_row_height( ip_row = lv_row ip_height_fix = 30 ).

合并单元格

"合并单元格
      lo_worksheet->set_merge( ip_row = lv_row ip_row_to = lv_row ip_column_start = 'A' ip_column_end = 'I' ).

设置图片LOGO

DATA: lo_drawing TYPE REF TO zcl_excel_drawing, ls_key TYPE wwwdatatab.
        DATA: lo_drawing1 TYPE REF TO zcl_excel_drawing.

        lo_drawing = lo_excel->add_new_drawing( ).
        lo_drawing->set_position( ip_from_row = lv_row - 6
                                  ip_from_col = 'H' ).

        ls_key-relid = 'MI'.
        ls_key-objid = 'ZSDR002_PNG2'.
        lo_drawing->set_media_www( ip_key = ls_key
                                   ip_width = 241
                                   ip_height = 167 ).

        " assign drawing to the worksheet
        lo_worksheet->add_drawing( lo_drawing ).


        lo_drawing1 = lo_excel->add_new_drawing( ).
        lo_drawing1->set_position( ip_from_row = 1
                                  ip_from_col = 'H' ).
        ls_key-relid = 'MI'.
        ls_key-objid = 'ZSDR002_PNG4'.


        lo_drawing1->set_media_www( ip_key = ls_key
                                   ip_width = 165
                                   ip_height = 55  ).

        " assign drawing to the worksheet
        lo_worksheet->add_drawing( lo_drawing1 ).

设置文件名，导出EXCEL文件

DATA: xdata       TYPE xstring,
              t_rawdata   TYPE solix_tab,
              bytecount   TYPE i,
              cl_writer   TYPE REF TO zif_excel_writer,
              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
              lv_column   TYPE zexcel_cell_column_alpha.

        "文件名称
        DATA:lv_fpath TYPE string.
        DATA:filename TYPE string.
        READ TABLE lt_itab INTO ls_itab INDEX 1.
        IF sy-subrc = 0.
          CONCATENATE ls_itab-vbeln ls_itab-name1 INTO filename SEPARATED BY '_'.
          CONCATENATE filename '.xlsx' INTO filename .
        ENDIF.

        cl_gui_frontend_services=>get_desktop_directory(
              CHANGING desktop_directory = lv_fpath
              ).
        cl_gui_cfw=>update_view( ).
        PERFORM frm_join_filepath
          USING lv_fpath filename
          CHANGING filename.
***文件数据
        CREATE OBJECT cl_writer TYPE zcl_excel_writer_2007.
        xdata = cl_writer->write_file( lo_excel ).
        t_rawdata = cl_bcs_convert=>xstring_to_solix( iv_xstring  = xdata ).
        bytecount = xstrlen( xdata ).
***下载EXCEL
        cl_gui_frontend_services=>gui_download(
          EXPORTING bin_filesize = bytecount
                  filename       = filename
                  filetype       = 'BIN'
          CHANGING data_tab      = t_rawdata
          EXCEPTIONS access_denied = 1
                     OTHERS = 2
          ).
        IF sy-subrc <> 0.
          "MESSAGE '保存Excel失败，请尝试关闭Excel进程' TYPE 'E'.
          MESSAGE e013(zsd01).
          "MESSAGE e001(00) WITH  sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
          STOP.
        ENDIF.