ABAP 自定义简易读取EXCEL函数

FUNCTION Z_EXCEL_READ4.
*"----------------------------------------------------------------------
*"*"本地接口：
*" IMPORTING
*" VALUE(FILEPATH) "文件路径
*" VALUE(BEGINPOS_ROW) TYPE I DEFAULT 1 "开始行
*" VALUE(BEGINPOS_COL) TYPE I DEFAULT 1 "开始列
*" VALUE(COL_DETECT) TYPE I DEFAULT 0
*" VALUE(READROWS) TYPE I DEFAULT 1
*" VALUE(READCOLS) TYPE I DEFAULT 1
*" VALUE(SHOWEXCEL) TYPE I DEFAULT 0
*" VALUE(CLOSE_AFTER_EXIT) TYPE I DEFAULT 1
*" VALUE(SHEETNAME) TYPE C DEFAULT '#ACTI'
*" TABLES
*" IRESULT TYPE TABLE　　　　"返回表
*" EXCEPTIONS
*" INVALIDSHEETNAME
*" OLEERROR
*"----------------------------------------------------------------------

INCLUDE ole2incl.
DATA: h_excel TYPE ole2_object, " Excel object
h_mapl TYPE ole2_object, " list of workbooks
h_map TYPE ole2_object, " workbook
h_sheets TYPE ole2_object, "sheets
h_sheet TYPE ole2_object, "sheet
h_zl TYPE ole2_object. " cell
DATA: wa_cell LIKE zexcelop_items.
DATA: m_counter TYPE i. "循环计数器
* DATA: m_itembuffer(200) TYPE c. "用于自动行判断的单元格数据缓存
*data: ig_cell like sorted table of wa_cell with unique key row col .
DATA: m_col TYPE i VALUE 1, " 行坐标
m_row TYPE i VALUE 1. "列坐标
CONSTANTS: tmpfname(20) TYPE c VALUE 'D:\TMPBUF.TXT'.
*----------------
*初始化Excel对象
*----------------
CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
PERFORM err_hdl.
SET PROPERTY OF h_excel 'DisplayAlerts' = 'false'. "关闭弹出提示
PERFORM err_hdl.

IF showexcel = '1'.
SET PROPERTY OF h_excel 'Visible' = 1.
ELSE.
SET PROPERTY OF h_excel 'Visible' = 0.
ENDIF.
PERFORM err_hdl.
CALL METHOD OF h_excel 'Workbooks' = h_mapl.
PERFORM err_hdl.
CALL METHOD OF h_mapl 'open' = h_map
EXPORTING
#1 = filepath.
PERFORM err_hdl.
CALL METHOD OF h_excel 'Sheets' = h_sheets.
PERFORM err_hdl.

IF sheetname <> '#ACTI'. "如果指定了工作表名称
IF sheetname = ''.
RAISE invalidsheetname.
ELSE.
CALL METHOD OF h_map 'Sheets' = h_sheets. "取得当前工作簿列表
IF sy-subrc <> 0 .
RAISE invalidsheetname.
ENDIF.
GET PROPERTY OF h_sheets 'Item' = h_sheet
exporting
#1 = sheetname .
CALL METHOD OF h_sheet 'Activate'.
PERFORM err_hdl.
ENDIF.
ELSE.
CALL METHOD OF h_excel 'ActiveSheet' = h_sheet. "取得当前工作簿
PERFORM err_hdl.

ENDIF.

*----------------
*读入数据填充
*----------------
IF col_detect <> 0.

*------------------
* 进行自动数据探测，找出不为空的连续行
*------------------
m_row = beginpos_row.
m_col = col_detect.
CALL METHOD OF h_excel 'Cells' = h_zl
EXPORTING
#1 = m_row
#2 = m_col.
PERFORM err_hdl.
GET PROPERTY OF h_zl 'Value' = wa_cell-cellvalue.
PERFORM err_hdl.
WHILE wa_cell-cellvalue <> ''.
m_row = m_row + 1.
CALL METHOD OF h_excel 'Cells' = h_zl
EXPORTING
#1 = m_row
#2 = m_col.
PERFORM err_hdl.
GET PROPERTY OF h_zl 'Value' = wa_cell-cellvalue.
PERFORM err_hdl.
ENDWHILE.
readrows = m_row - beginpos_row.
ENDIF.
*----------------------
*进行数据表格读取
*----------------------
*进行块复制
DATA: h_range TYPE ole2_object, "临时Range对象
h_rangel TYPE ole2_object, "左上角坐标的Cell对象
h_ranger TYPE ole2_object. "右下角坐标的Range对象
DATA: m_rows TYPE i,
m_cols TYPE i.
m_rows = beginpos_row + readrows - 1 .
m_cols = beginpos_col + readcols - 1.
CALL METHOD OF h_sheet 'Cells' = h_rangel
EXPORTING
#1 = beginpos_row
#2 = beginpos_col. "得到左上角坐标对象
PERFORM err_hdl.
CALL METHOD OF h_sheet 'Cells' = h_ranger
EXPORTING
#1 = m_rows
#2 = m_cols. "得到右下角坐标对象
PERFORM err_hdl.
CALL METHOD OF h_sheet 'Range' = h_range
EXPORTING
#1 = h_rangel
#2 = h_ranger.
PERFORM err_hdl.
CALL METHOD OF h_range 'Copy'.
PERFORM err_hdl.
*构造新工作簿
DATA: h_tmpsheet TYPE ole2_object.
CALL METHOD OF h_sheets 'Add' = h_tmpsheet.
PERFORM err_hdl.
CALL METHOD OF h_tmpsheet 'Activate'.
PERFORM err_hdl.

*块粘贴
CALL METHOD OF h_tmpsheet 'PasteSpecial'.
PERFORM err_hdl.
*保存为文本文件
CALL METHOD OF h_map 'SaveAs'
EXPORTING
#1 = tmpfname
#2 = -4158. "输出格式为TAB分割文本文件
PERFORM err_hdl.
*读入文本文件

*wa_cell-zrow = 1.
*wa_cell-zcol = 1.
* DO readrows TIMES.
* m_counter = sy-index.
* DO readcols TIMES.
* m_row = beginpos_row + m_counter - 1 .
* "计算出当前读取单元格的行坐标
* m_col = beginpos_col + sy-index - 1.
**------------读取单元格数据------
* CALL METHOD OF h_excel 'Cells' = h_zl
* EXPORTING
* #1 = m_row
* #2 = m_col.
* PERFORM err_hdl.
* GET PROPERTY OF h_zl 'Value' = wa_cell-cellvalue.
* PERFORM err_hdl.
**--------------------------------
* APPEND wa_cell TO iresult.
* add 1 to wa_cell-zcol.
* ENDDO.
* wa_cell-zcol = 1.
* add 1 to wa_cell-zrow.
* ENDDO.
*

*---------------
*释放对象
*---------------
IF close_after_exit = 1 OR showexcel <> 1 .
CALL METHOD OF h_excel 'quit'.
ENDIF.
FREE: OBJECT h_tmpsheet , h_range, h_rangel, h_ranger,h_zl , h_map , h_mapl , h_excel.
DATA myfname TYPE string.
MOVE tmpfname TO myfname.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = myfname
* FILETYPE = 'ASC'
has_field_separator = 'X'
* HEADER_LENGTH = 0
* READ_BY_LINE = 'X'
dat_mode = 'X'
* CODEPAGE = ' '
* IGNORE_CERR = ABAP_TRUE
* REPLACEMENT = '#'
* CHECK_BOM = ' '
* IMPORTING
* FILELENGTH =
* HEADER =
TABLES
data_tab = iresult
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
OTHERS = 17
.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

*删除临时文件
DATA m_t TYPE rlgrap-filename.
WRITE tmpfname TO m_t.
CALL FUNCTION 'GUI_DELETE_FILE'
EXPORTING
file_name = m_t
EXCEPTIONS
failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
* MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
* WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

 

ENDFUNCTION.

*&--------------------------------------------------------------------*
*& Form ERR_HDL
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
FORM err_hdl.
* IF sy-subrc <> 0.
* RAISE 1.
* STOP.
* ENDIF.

*----------------------MARK BY MEIDL
IF sy-subrc <> 0.
* WRITE: / 'Error during OLE-Automation:'(010), SY-SUBRC.
MESSAGE e000 WITH 'EXCEL操作失败，错误代码：' sy-subrc.
ENDIF.
*----------------------MARK BY MEIDL
ENDFORM. "ERR_HDL