ABAP 编程过程中难免遇到各种和微软EXCEL交互的场景，因此本人将EXCEL操作封装成类，方便调用
*———————————————————————*
*& ZEXCELCLASS
*&———————————————————————*
INCLUDE OLE2INCL.
*———————————————————————-*
* CLASS EXCEL_PROXY DEFINITION
*———————————————————————-*
*
*———————————————————————-*
CLASS EXCEL_PROXY DEFINITION.
PUBLIC SECTION.
METHODS:
* 构造方法,创建一个EXCEL实例.
CONSTRUCTOR,
* 从外部对象加载EXCEL
SETEXCEL IMPORTING EXCELOBJECT TYPE OLE2_OBJECT ,
* 打开EXCEL模版文件并将返回的WORKBOOK放入成员变量
OPENEXCELTEMPLATEBYURI IMPORTING URI TYPE C,
* 将内表快速粘贴到EXCEL中
EXPORTTABTOEXCEL IMPORTING SHEETNO TYPE I “Sheet参数
LINE TYPE I “RANGE行参数
COLUMN TYPE I “RANGE列参数
TAB TYPE STANDARD TABLE,
* 将一个数据写入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,
*调整行高
EXCEL_ROWHEIGHT IMPORTING
P_BEGROW TYPE C
P_ENDROW TYPE C
P_ROWHEIGHT TYPE I,
*调整列宽
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
*———————————————————————-*
*—————————–* 将内表快速粘贴到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 ‘abc’
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