excel 单sheet 操作 + 复杂的操作

最新推荐文章于 2023-03-17 12:27:09 发布

原创最新推荐文章于 2023-03-17 12:27:09 发布 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #object #insert #report #initialization #include

SAP 专栏收录该内容

181 篇文章

订阅专栏

report z_test1 .

include ole2incl.
data: excel type ole2_object,
      workbook type ole2_object,    " Workbook object
       book type ole2_object,
      sheet type ole2_object,   " Worksheet object
      range type ole2_object,     " Range object
      cell type ole2_object.      " Cell object

data: sheet_name(20) value 'BS'. "Excel文件里的sheet name
data local_file like rcgfiletr-ftfront."下载文件类型
local_file = 'c:/1.xls'.

start-of-selection.

data: c1 type i,
          c2 type i,
          c3 type i,
          c4 type i.

create object excel 'EXCEL.APPLICATION'.   "CREATE EXCEL OBJECT
if sy-subrc ne 0.
    exit.
endif.
set property of excel 'Visible' = 1.        "是否显示EXCEL
call method of excel 'Workbooks' = workbook.
data h type i.

call method of workbook 'Open'              "打开创建的excel
   exporting
   #1 = local_file.

call method of excel 'Sheets' = sheet
exporting
#1 = 1.

call method of sheet 'SELECT' . "选择excel

*定位,填充抬头此处要再加一个字体的参数
perform fill_range using 1 3 'F_aaaaaa'. "第一行第三列
perform fill_range using 1 4 'F_cccccc'. "第一行第四列

get property of excel 'ActiveWorkbook' = sheet.

* 对EXCEL 的各种操作
* CALL METHOD OF sheet 'printout'.                  "打印
* call method of excel 'SAVE'.                      "保存
* CALL METHOD OF excel 'Quit'.                      "退出

free object cell.
free object range.
free object sheet.
free object workbook.
free object excel.

*-------------------------------------------------------------------
*    perform fill_range.
*
*--------------------------------------------------------------------
*    text
*--------------------------------------------------------------------
form fill_range using value(f_row)
                      value(f_col)
                      value(f_value).

call method of excel 'CELLS' = cell
    exporting
    #1 = f_row
    #2 = f_col.
set property of cell 'VALUE' = f_value.

endform.

================================================================

report z_test1 .

include ole2incl.
data: excel_obj     type ole2_object,
      workbook_obj type ole2_object,
      sheet_obj     type ole2_object,
      sheet_obj2    type ole2_object,
      cell_obj      type ole2_object,
      column_obj    type ole2_object,
      range_obj     type ole2_object,
      borders_obj   type ole2_object,
      button_obj    type ole2_object,
      int_obj       type ole2_object,
      font_obj      type ole2_object,
      row_obj       type ole2_object.
data: celldata(40) type c .
data: begin of itab occurs 0 ,
        ff1(10) type c,
        ff2(10) type c,
      end of itab.

start-of-selection.
itab-ff1 = 'A1'.
itab-ff2 = 'A2'.
append itab.

itab-ff1 = 'B1'.
itab-ff2 = 'B2'.
append itab.

itab-ff1 = 'C1'.
itab-ff2 = 'C2'.
append itab.

itab-ff1 = 'D1'.
itab-ff2 = 'D2'.
append itab.

itab-ff1 = 'E1'.
itab-ff2 = 'E2'.
append itab.

*创建Excel对象
perform open_excel_file using excel_obj
                                 workbook_obj
                                 sheet_obj
                                 'C:/1.XLS'
                                 'TestSheet'
                                 '1' .
*写数据至Range
perform excel_range_write using excel_obj
                                  'A1:B2'
                                  'abcd'.

*插入N行
perform excel_row_insert using sheet_obj
'1'
'2'.
*

*插入N列
perform excel_column_insert using sheet_obj
'A'
'2'.

*行高设置
perform rowheight using excel_obj
'1:6'
'30'.

*列宽设置
perform columnwidth using excel_obj
'A:C'
'20'.

*读取单元格的数据
perform excel_cell_read using excel_obj                   "格子为1A
                                '1'
                                '1'
                                celldata .

*写数据至Range
perform excel_range_write using excel_obj
'C1:D2'
celldata .

*增加一个Sheet
perform excel_sheet_add using workbook_obj
sheet_obj2
'Test02'.

*写数据至Range
perform excel_range_write using excel_obj
'C1:D2'
'aaaaa'.

*添加Range的边框
perform borderrange using excel_obj
'A1:C3'.

*执行Excel的宏
perform runmacro using excel_obj
'Macro1'.

*把内表依次放入Cell
perform fill_sheet_itab using 2 3.

call method of sheet_obj 'Activate' .
get property of excel_obj 'ActiveWorkbook' = workbook_obj.
perform excel_save using excel_obj.
* call method of workbook_obj 'CLOSE'.
* call method of excel_obj 'QUIT'.
*&---------------------------------------------------------------------*
*& 创建Excel对象
*&---------------------------------------------------------------------*
form open_excel_file using lcobj_excel
                            lcobj_workbook
                            lcobj_sheet
                            lc_filename
                            lc_sheetname
                            lc_visible.
create object lcobj_excel 'Excel.Application'.
if sy-subrc ne 0.
    message e796(f9) with '不能创建Excel对象'.
endif.
call method of lcobj_excel 'Workbooks' = lcobj_workbook.
call method of lcobj_workbook 'Open' = lcobj_workbook
    exporting #1 = lc_filename.
if sy-subrc ne 0.
    message e796(f9) with '打开文件错误'.
endif.
set property of lcobj_excel 'Visible' = lc_visible.
call method of lcobj_workbook 'Sheets' = lcobj_sheet
    exporting #1 = 1.
set property of lcobj_sheet 'Name' = lc_sheetname.

endform.                    "excel_initialization
*&---------------------------------------------------------------------*
*& 读取单元格的数据
*&---------------------------------------------------------------------*
form excel_cell_read using lcobj_excel
                           lc_row
                           lc_col
                           lc_value.

data: lc_cell type ole2_object.
get property of lcobj_excel 'Cells' = lc_cell
exporting #1 = lc_row
#2 = lc_col.
get property of lc_cell 'Value' = lc_value.

endform. "excel_cell_read

*&---------------------------------------------------------------------*
*& 写数据至Range
*&---------------------------------------------------------------------*
form excel_range_write using lcobj_excel
                             lc_range
                             lc_value.
data: lc_cell type ole2_object.
call method of lcobj_excel 'RANGE' = lc_cell
    exporting
      #1 = lc_range.
perform font using lc_cell 1 '30'.
set property of lc_cell 'VALUE' = lc_value.

endform. "excel_cell_write

*&---------------------------------------------------------------------*
*& 插入N行
*&---------------------------------------------------------------------*
form excel_row_insert using lcobj_sheet
lc_row
lc_count.
data lc_range type ole2_object.

do lc_count times.

call method of lcobj_sheet 'Rows' = lc_range
exporting #1 = lc_row.

call method of lc_range 'Copy'.

call method of lcobj_sheet 'Rows' = lc_range
exporting #1 = lc_row.

call method of lc_range 'Insert'.
call method of lc_range 'ClearContents'. "是否需要清空Cell

enddo.

endform.                    "excel_row_insert
*&---------------------------------------------------------------------*
*& 插入N列
*&---------------------------------------------------------------------*
form excel_column_insert using lcobj_sheet
                                lc_col
                                lc_count.
data lc_range type ole2_object.

call method of lcobj_sheet 'COLUMNS' = lc_range
exporting #1 = lc_col.

call method of lc_range 'Copy'.

call method of lcobj_sheet 'COLUMNS' = lc_range
exporting #1 = lc_col.

do lc_count times.
call method of lc_range 'Insert'.
enddo.

call method of lc_range 'ClearContents'.
endform.                    "excel_column_insert
*&---------------------------------------------------------------------*
*& 增加一个Sheet
*&---------------------------------------------------------------------*
form excel_sheet_add using lcobj_workbook
                            lcobj_sheet
                            lc_newsheetname.

data lc_newsheet type ole2_object.

get property of lcobj_workbook 'Sheets' = lcobj_sheet.

call method of lcobj_sheet 'Add' = lc_newsheet.

set property of lc_newsheet 'Name' = lc_newsheetname.

endform.                    "excel_sheet_add
*&---------------------------------------------------------------------*
*& 增加一个Workbook
*&---------------------------------------------------------------------*
form excel_workbook_add using lcobj_excel
                              lcobj_workbook.
data lc_newworkbook type ole2_object.
get property of lcobj_excel 'Workbooks' = lcobj_workbook.
call method of lcobj_workbook 'Add' = lc_newworkbook.
endform.                    "excel_workbook_add
*&---------------------------------------------------------------------*
*& 设置Cell的字体
*&---------------------------------------------------------------------*
form font using lcobj_cell
                bold
                size.

data lc_font type ole2_object.
call method of lcobj_cell 'FONT' = lc_font.
set property of lc_font 'BOLD' = bold.
set property of lc_font 'SIZE' = size.
free object lc_font.

endform.                    "font
*&---------------------------------------------------------------------*
*& 行高
*&---------------------------------------------------------------------*
form rowheight using lcobj_excel
                     row
                     height .

data lc_row type ole2_object .
call method of lcobj_excel 'ROWS' = lc_row
exporting #1 = row.

set property of lc_row 'RowHeight' = height .
free object lc_row.

endform .                    "rowheight
*&---------------------------------------------------------------------*
*& 列宽
*&---------------------------------------------------------------------*
form columnwidth using lcobj_excel
                       column
                       width .
data ls_col type ole2_object .

call method of lcobj_excel 'COLUMNS' = ls_col
exporting
#1 = column.
set property of ls_col 'columnwidth' = width .
free object ls_col.

endform .                    "columnwidth
*---------------------------------------------------------------------*
* 添加Range的边框                                                     *
*---------------------------------------------------------------------*
form borderrange using lcobj_excel
                       range .
data: lc_cell type ole2_object ,
        lc_borders type ole2_object .

call method of lcobj_excel 'RANGE' = lc_cell
exporting
#1 = range.

do 4 times .
call method of lc_cell 'BORDERS' = lc_borders
exporting #1 = sy-index.

    set property of lc_borders 'LineStyle' = '1'.
    set property of lc_borders 'WEIGHT' = 2.                "4=max
    set property of lc_borders 'ColorIndex' = '1'.

enddo.

free object lc_borders.
free object lc_cell.

endform.                    "borderrange
*---------------------------------------------------------------------*
* FORM fill_sheet_itab                                                *
*---------------------------------------------------------------------*
* 把内表依次放入Cell                                                  *
*---------------------------------------------------------------------*
form fill_sheet_itab using rowindex
                            colindex.
data: row    type i ,
        col    type i .
data: lcobj_cell type ole2_object .
field-symbols: <name> .
row = rowindex .
loop at itab .
    col = colindex .
    do 20 times.
      assign component sy-index of structure itab to <name> .
      if sy-subrc <> 0 .
        exit.
      endif.
      call method of excel_obj 'Cells' = lcobj_cell
        exporting #1 = row
                  #2 = col.
      set property of lcobj_cell 'Value' = <name>.
      col = col + 1.
    enddo.
    row = row + 1.
endloop.
endform.
*---------------------------------------------------------------------*
* 执行Excel的宏                                                       *
*---------------------------------------------------------------------*
form runmacro using lcobj_excel
                    macroname.
call method of lcobj_excel 'RUN'
      exporting
          #1 = macroname.
endform.
*&---------------------------------------------------------------------*
*& 保存Excel
*&---------------------------------------------------------------------*
form excel_save using lcobj_excel.
set property of lcobj_excel 'DisplayAlerts' = 0.
call method of lcobj_excel 'Save'.
* CALL METHOD OF workbook_obj 'SAVEAS' "另存
*    EXPORTING
*      #1 = 'C:/Test.xls'
*      #2 = 1.
endform.

=======================================================================

report ztest1.

data: itab like alsmex_tabline occurs 0 with header line.

types: begin of st,
           project(10) type c,
           zbje type p decimals 2,
       end of st.

data: it_tab type table of st with header line,
st_tab type st.

data: sum type p decimals 2.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
     exporting
          filename                = 'c:/BOOK1.xls'
          i_begin_col             = 1
          i_begin_row             = 1
          i_end_col               = 7
          i_end_row               = 1000
     tables
          intern                  = itab
     exceptions
          inconsistent_parameters = 1
          upload_ole              = 2
          others                  = 3.

if sy-subrc = 0.
loop at itab.

    case itab-col.
      when 3. st_tab-project = itab-value.
      when 7.
        st_tab-zbje    = itab-value.
        sum = sum + st_tab-zbje.

append st_tab to it_tab.
endcase.

endloop.
endif.

loop at it_tab.

write:/ it_tab-project, it_tab-zbje, sum.

endloop.