ABAP操作EXECL詳細

一騎當(dāng)千_30 2018-09-03
展開全文
REPORT  ZVR013.
INCLUDE ole2incl.
DATA: gs_excel          TYPE ole2_object,
      gs_wbooklist      TYPE ole2_object,
      gs_application    TYPE ole2_object,
      gs_wbook          TYPE ole2_object,
      gs_activesheet    TYPE ole2_object,
      gs_sheets         TYPE ole2_object,
      gs_newsheet       TYPE ole2_object,
      gs_cell1          TYPE ole2_object,
      gs_cell2          TYPE ole2_object,
      gs_cells          TYPE ole2_object,
      gs_font           TYPE ole2_object,
      gs_page           TYPE ole2_object,
      gs_range type ole2_object,
      gs_borders type ole2_object.
DATA: gv_sheet_name(20) TYPE c.
DATA: gv_outer_index    LIKE sy-index.
DATA: gv_intex(2)       TYPE c.
DATA: gv_line_cntr TYPE i.   'line counter
DATA: gv_linno TYPE i.       'line number
DATA: gv_colno TYPE i.       'column number
DATA: gv_value TYPE i.       'data
data: x1 type p, x2 type p, y1 type p, y2 type p.
data: value(100),value1(100).
data: name(20),size(2).
data: bold type p, underline type p, Alignment type p.
data: row type p value 11, line type p value 1. '循環(huán)次數(shù)
data: row1 type p,line1 type p.
data: quantity(20),amount(20).
data: rowheight(10),columnwidth(10).
data: page type p, pg_index type p.
*-----------------------------------------------------------
*-----------------------------------------------------------
CREATE OBJECT gs_excel 'EXCEL.APPLICATION'.
SET PROPERTY OF gs_excel 'Visible' = 1.
GET PROPERTY OF gs_excel 'Workbooks' = gs_wbooklist.
GET PROPERTY OF gs_wbooklist 'Application' = gs_application.
SET PROPERTY OF gs_application 'SheetsInNewWorkbook' = 1.
CALL METHOD OF gs_wbooklist 'Add' = gs_wbook.
GET PROPERTY OF gs_application 'ActiveSheet' = gs_activesheet.
SET PROPERTY OF gs_activesheet 'Name' = gv_sheet_name.
get property of gs_activesheet 'pagesetup' = gs_page.
set property of gs_page 'TopMargin' = '25'.
set property of gs_page 'BottomMargin' = '25'.
set property of gs_page 'LeftMargin' = '25'.
set property of gs_page 'RightMargin' = '25'.
*--Formatting the title
form format using gs_cell1 value name size bold alignment underline.
  condense value.
  condense name.
  condense size.
  GET PROPERTY OF gs_cell1 'Font' = gs_font .
  SET PROPERTY OF gs_font 'Name' = name.
  SET PROPERTY OF gs_font 'Size' = size.
  SET PROPERTY OF gs_font 'Bold' = bold.           'Not bold
  SET PROPERTY OF gs_font 'Italic' = '1'.         'Not Italic
  SET PROPERTY OF gs_font 'Underline' = underline.  'Not underlined
  SET PROPERTY OF gs_cell1 'WrapText' = 1.
  SET PROPERTY OF gs_cell1 'horizontalAlignment' = alignment.
  ' && 水平方向　2左對齊，3居中,，4右對齊
  SET PROPERTY OF gs_cell1 'VerticalAlignment' = 2.
  '&& 垂直方向　1靠上　，2居中，3靠下
  set property of gs_cell1 'NumberFormatLocal' = '@'.
  '&& 設(shè)置數(shù)據(jù)格式
  SET PROPERTY OF gs_cell1 'Value' = value.
endform.                    'format
*------------------------------
*該函數(shù)用來合并excel單元格
*------------------------------
form merged using x1 y1 x2 y2.
*--Selecting cell area to be merged.
  CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = x1
      #2 = y1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x2
      #2 = y2.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
  CALL METHOD OF gs_cells 'Select'.
*--Merging
  CALL METHOD OF gs_cells 'Merge' .
endform.                    'merged
*---------------------------------
*生成四周有邊框的單元格
*---------------------------------
form merged_borders using x1 y1 x2 y2.
*--Selecting cell area to be merged.
  CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = x1
      #2 = y1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x2
      #2 = y2.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
  CALL METHOD OF gs_cells 'Select'.
*--Merging
  CALL METHOD OF gs_cells 'Merge' .
  GET PROPERTY OF gs_cells 'borders' = gs_range .
  SET PROPERTY OF gs_range 'weight' = '2'.
  SET PROPERTY OF gs_range 'linestyle' = '1'.
  FREE OBJECT gs_range.
endform.                    'merged
*--------------------------------------
*---------------------------------
*生成左右有邊框的單元格
*---------------------------------
form sides_borders using x1 y1 x2 y2.
*--Selecting cell area to be merged.
  CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = x1
      #2 = y1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x2
      #2 = y2.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
  CALL METHOD OF gs_cells 'Select'.
*--Merging
  CALL METHOD OF gs_cells 'Merge' .
  CALL METHOD OF gs_cells 'BORDERS' = gs_borders
    EXPORTING
      #1 = '1'.
  SET PROPERTY OF gs_borders 'LINESTYLE' = '1'.
  SET PROPERTY OF gs_borders 'WEIGHT' = 2.
  FREE OBJECT gs_borders.
 
  CALL METHOD OF gs_cells 'BORDERS' = gs_borders
    EXPORTING
      #1 = '2'.
  SET PROPERTY OF gs_borders 'LINESTYLE' = '7'.
*& (其中Borders參數(shù)：1－左,、2－右,、3－頂、4－底,、5－斜、6－斜/,；
 
*&LineStyle值：1與7－細實,、2－細虛、4－點虛,、9－雙細實線)
 
  SET PROPERTY OF gs_borders 'WEIGHT' = 2.
  FREE OBJECT gs_borders.
endform.                    'merged
*---------------------------------
form left_borders using x1 y1 x2 y2.
*--Selecting cell area to be merged.
  CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = x1
      #2 = y1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x2
      #2 = y2.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
  CALL METHOD OF gs_cells 'Select'.
*--Merging
  CALL METHOD OF gs_cells 'Merge' .
  CALL METHOD OF gs_cells 'BORDERS' = gs_borders
    EXPORTING
      #1 = '1'.
  SET PROPERTY OF gs_borders 'LINESTYLE' = '1'.
  SET PROPERTY OF gs_borders 'WEIGHT' = 2.
  FREE OBJECT gs_borders.
endform.                    'merged
form right_borders using x1 y1 x2 y2.
*--Selecting cell area to be merged.
  CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = x1
      #2 = y1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x2
      #2 = y2.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
  CALL METHOD OF gs_cells 'Select'.
*--Merging
  CALL METHOD OF gs_cells 'Merge' .
  CALL METHOD OF gs_cells 'BORDERS' = gs_borders
    EXPORTING
      #1 = '2'.
  SET PROPERTY OF gs_borders 'LINESTYLE' = '1'.
  SET PROPERTY OF gs_borders 'WEIGHT' = 2.
  FREE OBJECT gs_borders.
endform.                    'merged
*---------------------------------
*生成bottom有邊框的單元格
*---------------------------------
form bottom_borders using x1 y1 x2 y2.
*--Selecting cell area to be merged.
  CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = x1
      #2 = y1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x2
      #2 = y2.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
  CALL METHOD OF gs_cells 'Select'.
*--Merging
  CALL METHOD OF gs_cells 'Merge' .
  CALL METHOD OF gs_cells 'BORDERS' = gs_borders
    EXPORTING
      #1 = '4'.
  SET PROPERTY OF gs_borders 'LINESTYLE' = '7'.
  SET PROPERTY OF gs_borders 'WEIGHT' = 2.
  FREE OBJECT gs_borders.
endform.                    'merged
 
form bottom_lines using x1 y1 x2 y2.
*--Selecting cell area to be merged.
  CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = x1
      #2 = y1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x2
      #2 = y2.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
  CALL METHOD OF gs_cells 'Select'.
*--Merging
  CALL METHOD OF gs_cells 'Merge' .
  CALL METHOD OF gs_cells 'BORDERS' = gs_borders
    EXPORTING
      #1 = '4'.
  SET PROPERTY OF gs_borders 'LINESTYLE' = '9'.
  SET PROPERTY OF gs_borders 'WEIGHT' = 5.
  FREE OBJECT gs_borders.
endform.                    'merged
*-------------------------------------------------
*設(shè)置行高和列寬,。
form row_column using x1 y1 x2 y2 rowheight columnwidth.
*--Selecting cell area to be merged.
  CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = x1
      #2 = y1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x2
      #2 = y2.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
  CALL METHOD OF gs_cells 'Select'.
*--Merging
  CALL METHOD OF gs_cells 'Merge' .
  get property of gs_cells 'rows' = gs_borders.
  SET PROPERTY OF gs_borders 'rowheight' = rowheight.
  GET PROPERTY OF gs_cells 'columns' = gs_range .
  SET PROPERTY OF gs_range 'columnwidth' = columnwidth.
  FREE OBJECT gs_borders.
  FREE OBJECT gs_range.
endform.                    'merged
form rowheight using x1 y1 x2 y2 rowheight.
*--Selecting cell area to be merged.
  CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = x1
      #2 = y1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x2
      #2 = y2.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
  CALL METHOD OF gs_cells 'Select'.
*--Merging
  CALL METHOD OF gs_cells 'Merge' .
  get property of gs_cells 'rows' = gs_borders.
  SET PROPERTY OF gs_borders 'rowheight' = rowheight.
*  GET PROPERTY OF gs_cells 'columns' = gs_range .
*  SET PROPERTY OF gs_range 'columnwidth' = columnwidth.
  FREE OBJECT gs_borders.
  FREE OBJECT gs_range.
endform.                    'merged
form columnwidth using x1 y1 x2 y2 columnwidth.
*--Selecting cell area to be merged.
  CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = x1
      #2 = y1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x2
      #2 = y2.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
  CALL METHOD OF gs_cells 'Select'.
*--Merging
  CALL METHOD OF gs_cells 'Merge' .
*  get property of gs_cells 'rows' = gs_borders.
*  SET PROPERTY OF gs_borders 'rowheight' = rowheight.
  GET PROPERTY OF gs_cells 'columns' = gs_range .
  SET PROPERTY OF gs_range 'columnwidth' = columnwidth.
  FREE OBJECT gs_borders.
  FREE OBJECT gs_range.
endform.                    'merged
********************************************************
form pages using l_file x y.
x1 = 1.
y1 = 1.
x2 = x.
y2 = y.
*do page times.
*_-create excel
      CONCATENATE 'Sheet' '1' INTO gv_sheet_name.
      CREATE OBJECT gs_excel 'EXCEL.APPLICATION'.
      SET PROPERTY OF gs_excel 'Visible' = 1.
      GET PROPERTY OF gs_excel 'Workbooks' = gs_wbooklist.
*----copy
CALL METHOD OF gs_wbooklist 'open' = gs_range
      EXPORTING
      #1 = l_file.
  CALL METHOD OF gs_excel 'Worksheets' = gs_borders
      EXPORTING #1 = 1.
  CALL METHOD OF gs_borders 'Activate'.
  CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = 1
      #2 = 1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x
      #2 = y.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
CALL METHOD OF gs_cells 'copy'.
*----PasteSpecial
*-create
  CALL METHOD OF gs_wbooklist 'Add' = gs_wbook.
  GET PROPERTY OF gs_application 'ActiveSheet' = gs_activesheet.
  SET PROPERTY OF gs_activesheet 'Name' = gv_sheet_name.
CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = 1
      #2 = 1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x
      #2 = y.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
CALL METHOD OF gs_cells 'PasteSpecial'.
CALL METHOD OF gs_cells 'copy'.
call method OF gs_range 'Activate'.
CALL METHOD OF gs_range 'close'.
call method OF gs_activesheet 'Activate'.
endform.
*-------------------------------------------------
form pages1 using x y.
do page times.
      x1 = x1   x.
      y1 = 1.
      x2 = x2   x.
      y2 = y.
CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = 1
      #2 = 1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x
      #2 = y.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
CALL METHOD OF gs_cells 'copy'.
   CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
      #1 = x1
      #2 = y1.
  CALL METHOD OF gs_excel 'Cells' = gs_cell2
    EXPORTING
      #1 = x2
      #2 = y2.
  CALL METHOD OF gs_excel 'Range' = gs_cells
    EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
CALL METHOD OF gs_cells 'PasteSpecial'.
enddo.
endform.
本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布,，不代表本站觀點,。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息,，謹(jǐn)防詐騙,。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點擊一鍵舉報,。
轉(zhuǎn)藏 分享
QQ空間 QQ好友新浪微博微信
獻花（0） +1
來自：一騎當(dāng)千_30 > 《abap》
舉報/認(rèn)領(lǐng)