ABAP 編程過程中難免遇到各種和微軟EXCEL交互的場景，因此本人將EXCEL操作封裝成類，方便調(diào)用

*———————————————————————*
*&? ZEXCELCLASS
*&———————————————————————*
INCLUDE OLE2INCL.

*———————————————————————-*
*?????? CLASS EXCEL_PROXY DEFINITION
*———————————————————————-*
*
*———————————————————————-*
CLASS EXCEL_PROXY DEFINITION.
PUBLIC SECTION.
METHODS:
*?? 構造方法,創(chuàng)建一個EXCEL實例.
CONSTRUCTOR,
*?? 從外部對象加載EXCEL
SETEXCEL IMPORTING EXCELOBJECT TYPE OLE2_OBJECT ,
*?? 打開EXCEL模版文件并將返回的WORKBOOK放入成員變量
OPENEXCELTEMPLATEBYURI IMPORTING URI TYPE C,
*?? 將內(nèi)表快速粘貼到EXCEL中
EXPORTTABTOEXCEL IMPORTING SHEETNO TYPE I “Sheet參數(shù)
LINE TYPE I? “RANGE行參數(shù)
COLUMN TYPE I “RANGE列參數(shù)
TAB TYPE STANDARD TABLE,
*?? 將一個數(shù)據(jù)寫入EXCEL相應的格子中
APPENDDATA IMPORTING??? SHEETNO TYPE I
TEXT TYPE C
BEGIN_ROW TYPE I
BEGIN_COL TYPE I
END_ROW?? TYPE I
END_COL?? TYPE I,
*??? 加入EXCEL一行
APPENDLINE IMPORTING??? SHEETNO TYPE I
BEGIN_ROW TYPE I
BEGIN_COL TYPE I
END_ROW?? TYPE I
END_COL?? TYPE I,
*格式化單元格
EXCEL_FORMAT_RANGE IMPORTING
SHEETNO TYPE I
BEGIN_ROW TYPE I
BEGIN_COL TYPE I
END_ROW?? TYPE I
END_COL?? TYPE I
FONT_NAME TYPE C
FONT_SIZE TYPE I
FONT_STYLE TYPE C
HALIGNMENT TYPE I
VALIGNMENT TYPE I
BORDER_WEIGHT TYPE I,
*格式化單元格2
EXCEL_FORMAT_RANGE2 IMPORTING
SHEETNO TYPE I
BEGIN_ROW TYPE I
BEGIN_COL TYPE I
END_ROW?? TYPE I
END_COL?? TYPE I,

*調(diào)整行高
EXCEL_ROWHEIGHT IMPORTING
P_BEGROW? TYPE C
P_ENDROW? TYPE C
P_ROWHEIGHT TYPE I,
*調(diào)整列寬
EXCEL_COLWIDTH IMPORTING
P_BEGCOL TYPE C
P_ENDCOL TYPE C
P_COLWIDTH TYPE I,
*測試
EXCEL_CALL_METHOD,
*以文本格式輸出,用于身份號碼輸出
EXCEL_TXT_FORMAT IMPORTING
SHEETNO TYPE I
BEGIN_ROW TYPE I
BEGIN_COL TYPE I
END_ROW?? TYPE I
END_COL?? TYPE I.

PRIVATE SECTION.
*?? 私有成員變量中記錄著各種對象的引用
DATA:
M_EXCEL TYPE OLE2_OBJECT,??????? “Excel對象
M_WORKBOOKS TYPE OLE2_OBJECT,??? “Workbooks List對象
M_WORKBOOK TYPE OLE2_OBJECT,???? “Workbook對象
M_SHEETS TYPE OLE2_OBJECT,?????? “SHEETS對象
M_SHEET1 TYPE OLE2_OBJECT.
ENDCLASS.??????????????????? “EXCEL_PROXY DEFINITION

*———————————————————————-*
*?????? CLASS EXCEL_PROXY IMPLEMENTATION
*———————————————————————-*
*————————EXCEL代理————————————-*
*———————————————————————-*
CLASS EXCEL_PROXY IMPLEMENTATION.
*———————————————————————-*
*——————————構造方法——————————–*
*———————————————————————-*
METHOD CONSTRUCTOR.
*? 獲取Excel對象
CREATE OBJECT M_EXCEL ‘EXCEL.APPLICATION’.
SET PROPERTY OF M_EXCEL ‘Visible’ = 1.
CALL METHOD OF M_EXCEL ‘Workbooks’ = M_WORKBOOKS.
*? 打開下載的Excel Book對象
CALL METHOD OF M_WORKBOOKS ‘Add’ = M_WORKBOOK.
ENDMETHOD.??????????????????? “CONSTRUCTOR
“constructor

METHOD SETEXCEL.
M_EXCEL = EXCELOBJECT.
SET PROPERTY OF M_EXCEL ‘Visible’ = 1.
CALL METHOD OF M_EXCEL ‘Workbooks’ = M_WORKBOOKS.
*? 打開下載的Excel Book對象
CALL METHOD OF M_WORKBOOKS ‘Add’ = M_WORKBOOK.

ENDMETHOD.??????????????????? “SetExcel

*———————————————————————-*
*—————————–打開一個EXCEL模版文件——————–*
*———————————————————————-*
METHOD OPENEXCELTEMPLATEBYURI.
CALL METHOD OF M_WORKBOOKS ‘Open’ = M_WORKBOOK EXPORTING #1 = URI.
ENDMETHOD.??????????????????? “OpenExcelTemplateByUri
*———————————————————————-*
*—————————–* 將內(nèi)表快速粘貼到EXCEL中—————-*
*———————————————————————-*
METHOD EXPORTTABTOEXCEL.
DATA:?? M_RANGE TYPE OLE2_OBJECT.
* 1 獲得一個可以放入剪切版的TABLE
TYPES: DATA1(2000) TYPE C,
TY TYPE TABLE OF DATA1.
DATA:IT TYPE TY.
DATA IT_REC TYPE DATA1.
DATA: COUNT TYPE I.

FIELD-SYMBOLS: <FS> TYPE ANY ,
<DYN_TABLE> TYPE STANDARD TABLE,
<DYN_WA> TYPE ANY .

ASSIGN TAB TO <DYN_TABLE>.

LOOP AT <DYN_TABLE> ASSIGNING <DYN_WA> .

DO.
ASSIGN COMPONENT SY-INDEX
OF STRUCTURE <DYN_WA> TO <FS>.
IF SY-SUBRC <> 0.
APPEND IT_REC TO IT.
CLEAR IT_REC.
EXIT.
ENDIF.
IF IT_REC IS INITIAL.
IT_REC = <FS>.
ELSE.
CONCATENATE
IT_REC
<FS>
INTO IT_REC
SEPARATED BY CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB.
ENDIF.
ENDDO.
ENDLOOP.

* 2 將TAB放入剪切版
CALL METHOD CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT
IMPORTING
DATA???????????????? = IT[]
CHANGING
RC?????????????????? = COUNT
EXCEPTIONS
CNTL_ERROR?????????? = 1
ERROR_NO_GUI???????? = 2
NOT_SUPPORTED_BY_GUI = 3
OTHERS?????????????? = 4.

*3獲得一個EXCELSheet對象
CALL METHOD OF M_WORKBOOK ‘Worksheets’ = M_SHEETS.

*CALL METHOD OF M_sheets ‘Item’ = M_sheet1 EXPORTING #1 = SheetNo.

CALL METHOD OF M_WORKBOOK ‘WorkSheets’ = M_SHEET1
EXPORTING
#1 = SHEETNO.

SET PROPERTY OF M_RANGE ‘NumberFormatLocal’ = ‘@’.

CALL METHOD OF M_SHEET1 ‘Activate’.

CALL METHOD OF M_SHEET1 ‘Cells’ = M_RANGE
EXPORTING
#1 = LINE
#2 = COLUMN.

SET PROPERTY OF M_RANGE ‘NumberFormatLocal’ = ‘@’.

CALL METHOD OF M_RANGE ‘Select’.

CALL METHOD OF M_SHEET1 ‘Paste’.

ENDMETHOD.??????????????????? “EXPORTTABTOEXCEL

METHOD? APPENDDATA.

DATA:?? L_RANGE TYPE OLE2_OBJECT,??????? “Range對象
L_CELL1 TYPE OLE2_OBJECT,??????? “Cell對象
L_CELL2 TYPE OLE2_OBJECT,??????? “Cell對象
L_BORDER TYPE OLE2_OBJECT.?????? “BORDER對象

CALL METHOD OF M_WORKBOOK ‘WorkSheets’ = M_SHEET1
EXPORTING
#1 = SHEETNO.

CALL METHOD OF M_SHEET1 ‘Cells’ = L_CELL1
EXPORTING
#1 = BEGIN_ROW
#2 = BEGIN_COL.
CALL METHOD OF M_SHEET1 ‘Cells’ = L_CELL2
EXPORTING
#1 = END_ROW
#2 = END_COL.
CALL METHOD OF M_SHEET1 ‘Range’ = L_RANGE
EXPORTING
#1 = L_CELL1
#2 = L_CELL2.
*? 合并單元格
SET PROPERTY OF L_RANGE ‘MergeCells’ = ‘True’.
*? 以文本形式顯示
SET PROPERTY OF L_RANGE ‘NumberFormatLocal’ = ‘@’.
*? 輸出文字
SET PROPERTY OF L_RANGE ‘Value’ = TEXT.

ENDMETHOD.??????????????????? “appendDATA

METHOD APPENDLINE.

DATA:?? L_RANGE TYPE OLE2_OBJECT,??????? “Range對象
L_CELL1 TYPE OLE2_OBJECT,??????? “Cell對象
L_CELL2 TYPE OLE2_OBJECT,??????? “Cell對象
L_BORDER TYPE OLE2_OBJECT.?????? “BORDER對象

CALL METHOD OF M_WORKBOOK ‘WorkSheets’ = M_SHEET1
EXPORTING
#1 = SHEETNO.

CALL METHOD OF M_SHEET1 ‘Cells’ = L_CELL1
EXPORTING
#1 = BEGIN_ROW
#2 = BEGIN_COL.
CALL METHOD OF M_SHEET1 ‘Cells’ = L_CELL2
EXPORTING
#1 = END_ROW
#2 = END_COL.
CALL METHOD OF M_SHEET1 ‘Range’ = L_RANGE
EXPORTING
#1 = L_CELL1
#2 = L_CELL2.
CALL METHOD OF L_RANGE ‘Insert’
EXPORTING
#1 = -4121.

ENDMETHOD.??????????????????? “APPENDLINE

* 設置單元格格式方法
METHOD EXCEL_FORMAT_RANGE.

DATA:?? L_RANGE TYPE OLE2_OBJECT,??????? “Range對象
L_CELL1 TYPE OLE2_OBJECT,??????? “Cell對象
L_CELL2 TYPE OLE2_OBJECT,??????? “Cell對象
L_FONT TYPE OLE2_OBJECT,???????? “Font對象
L_BORDER TYPE OLE2_OBJECT.?????? “BORDER對象

CALL METHOD OF M_WORKBOOK ‘WorkSheets’ = M_SHEET1
EXPORTING
#1 = SHEETNO.

CALL METHOD OF M_SHEET1 ‘Cells’ = L_CELL1
EXPORTING
#1 = BEGIN_ROW
#2 = BEGIN_COL.
CALL METHOD OF M_SHEET1 ‘Cells’ = L_CELL2
EXPORTING
#1 = END_ROW
#2 = END_COL.
CALL METHOD OF M_SHEET1 ‘Range’ = L_RANGE
EXPORTING
#1 = L_CELL1
#2 = L_CELL2.

*? 水平對齊
IF HALIGNMENT <> 0.
SET PROPERTY OF L_RANGE ‘HorizontalAlignment’ = HALIGNMENT.
ENDIF.

*? 垂直對齊
IF VALIGNMENT <> 0.
SET PROPERTY OF L_RANGE ‘VerticalAlignment’ = VALIGNMENT.
ENDIF.

*? 字體設置
IF FONT_NAME <> ” OR FONT_SIZE <> 0 OR FONT_STYLE <> ”.
GET PROPERTY OF L_RANGE ‘Font’ = L_FONT.
SET PROPERTY OF L_FONT ‘Name’ = FONT_NAME.
SET PROPERTY OF L_FONT ‘Size’ = FONT_SIZE.
SET PROPERTY OF L_FONT ‘FontStyle’ = FONT_STYLE.
ENDIF.

*? 邊框
IF BORDER_WEIGHT <> 0.
GET PROPERTY OF L_RANGE ‘Borders’ = L_BORDER EXPORTING #1 = ‘7′.
SET PROPERTY OF L_BORDER ‘Weight’ = BORDER_WEIGHT.
GET PROPERTY OF L_RANGE ‘Borders’ = L_BORDER EXPORTING #1 = ‘8′.
SET PROPERTY OF L_BORDER ‘Weight’ = BORDER_WEIGHT.
GET PROPERTY OF L_RANGE ‘Borders’ = L_BORDER EXPORTING #1 = ‘9′.
SET PROPERTY OF L_BORDER ‘Weight’ = BORDER_WEIGHT.
GET PROPERTY OF L_RANGE ‘Borders’ = L_BORDER EXPORTING #1 = ‘10′.
SET PROPERTY OF L_BORDER ‘Weight’ = BORDER_WEIGHT.
GET PROPERTY OF L_RANGE ‘Borders’ = L_BORDER EXPORTING #1 = ‘11′.
SET PROPERTY OF L_BORDER ‘Weight’ = BORDER_WEIGHT.
GET PROPERTY OF L_RANGE ‘Borders’ = L_BORDER EXPORTING #1 = ‘12′.
SET PROPERTY OF L_BORDER ‘Weight’ = BORDER_WEIGHT.
ENDIF.

ENDMETHOD.??????????????????? “EXCEL_FORMAT_RANGE

* 設置行高
METHOD EXCEL_ROWHEIGHT.

DATA L_C TYPE OLE2_OBJECT.??????? “Rows對象
DATA L_COLLETTER(30) TYPE C.

CONCATENATE P_BEGROW ‘:’ P_ENDROW INTO L_COLLETTER.
CALL METHOD OF M_EXCEL ‘Rows’ = L_C
EXPORTING
#1 = L_COLLETTER.
SET PROPERTY OF L_C ‘RowHeight’ = P_ROWHEIGHT.
ENDMETHOD.??????????????????? “EXCEL_ROWHEIGHT

* 設置列寬
METHOD EXCEL_COLWIDTH.

DATA L_C TYPE OLE2_OBJECT.??????? “Columns對象
DATA L_COLLETTER(30) TYPE C.

CONCATENATE P_BEGCOL ‘:’ P_ENDCOL INTO L_COLLETTER.
CALL METHOD OF M_EXCEL ‘Columns’ = L_C
EXPORTING
#1 = L_COLLETTER.
SET PROPERTY OF L_C ‘ColumnWidth’ = P_COLWIDTH.
ENDMETHOD.??????????????????? ” EXCEL_COLWIDTH

METHOD EXCEL_FORMAT_RANGE2.

DATA: P_BEGCELL_ROW TYPE I,
P_BEGCELL_COL TYPE I,
P_ENDCELL_ROW TYPE I,
P_ENDCELL_COL TYPE I,
L_CELL1 TYPE OLE2_OBJECT,
L_CELL2 TYPE OLE2_OBJECT,
L_RANGE TYPE OLE2_OBJECT.

CALL METHOD OF M_WORKBOOK ‘WorkSheets’ = M_SHEET1
EXPORTING
#1 = SHEETNO.
CALL METHOD OF M_SHEET1 ‘Cells’ = L_CELL1
EXPORTING
#1 = BEGIN_ROW
#2 = BEGIN_COL.
CALL METHOD OF M_SHEET1 ‘Cells’ = L_CELL2
EXPORTING
#1 = END_ROW
#2 = END_COL.
CALL METHOD OF M_SHEET1 ‘Range’ = L_RANGE
EXPORTING
#1 = L_CELL1
#2 = L_CELL2.

**? 合并單元格
*? SET PROPERTY OF l_range ‘MergeCells’ = ‘True’.
**? 以文本形式顯示
*? SET PROPERTY OF l_range ‘NumberFormatLocal’ = ‘@’.
**? 輸出文字
*? SET PROPERTY OF l_range ‘Value’ = p_appendtext.
*
SET PROPERTY OF L_RANGE ‘WrapText’ = ‘True’.
*
*??? SET PROPERTY OF L_RANGE ‘ShrinkToFit’ = ‘False’.
ENDMETHOD.??????????????????? “EXCEL_FORMAT_RANGE2

METHOD EXCEL_CALL_METHOD.

CALL METHOD OF M_WORKBOOK ‘WorkSheets’ = M_SHEET1
EXPORTING
#1 = 1.

DATA A TYPE C.
A = ‘Array(10,20,30)’.
CALL METHOD OF M_SHEET1 ‘a(chǎn)bc’
EXPORTING
#1 = A.

ENDMETHOD.

METHOD EXCEL_TXT_FORMAT.
DATA:
L_CELL1 TYPE OLE2_OBJECT,
L_CELL2 TYPE OLE2_OBJECT,
L_RANGE TYPE OLE2_OBJECT.

CALL METHOD OF M_WORKBOOK ‘WorkSheets’ = M_SHEET1
EXPORTING
#1 = SHEETNO.
CALL METHOD OF M_SHEET1 ‘Cells’ = L_CELL1
EXPORTING
#1 = BEGIN_ROW
#2 = BEGIN_COL.
CALL METHOD OF M_SHEET1 ‘Cells’ = L_CELL2
EXPORTING
#1 = END_ROW
#2 = END_COL.
CALL METHOD OF M_SHEET1 ‘Range’ = L_RANGE
EXPORTING
#1 = L_CELL1
#2 = L_CELL2.

CALL METHOD OF L_RANGE ‘Select’.
SET PROPERTY OF L_RANGE ‘NumberFormatLocal’ = ‘@’.

ENDMETHOD.

ENDCLASS.??????????????????? “EXCEL_PROXY IMPLEMENTATION

總結

以上是生活随笔為你收集整理的当ABAP遇到OLE的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。