原文：http://blog.sina.com.cn/s/blog_7229b9c00100opx2.html
----------------------------------------------------------------------------
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() TYPE C.
DATA: GV_OUTER_INDEX LIKE SY-INDEX.
DATA: GV_INTEX() 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(),VALUE1().
DATA: NAME(),SIZE().
DATA: BOLD TYPE P, UNDERLINE TYPE P, ALIGNMENT TYPE P.
DATA: ROW TYPE P VALUE , LINE TYPE P VALUE . "循环次数
DATA: ROW1 TYPE P,LINE1 TYPE P.
DATA: QUANTITY(),AMOUNT().
DATA: ROWHEIGHT(),COLUMNWIDTH().
DATA: PAGE TYPE P, PG_INDEX TYPE P.
*-----------------------------------------------------------
*-----------------------------------------------------------
CREATE OBJECT GS_EXCEL 'EXCEL.APPLICATION'.
SET PROPERTY OF GS_EXCEL 'VISIBLE' = .
GET PROPERTY OF GS_EXCEL 'WORKBOOKS' = GS_WBOOKLIST.
GET PROPERTY OF GS_WBOOKLIST 'APPLICATION' = GS_APPLICATION.
SET PROPERTY OF GS_APPLICATION 'SHEETSINNEWWORKBOOK' = .
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' = ''.
SET PROPERTY OF GS_PAGE 'BOTTOMMARGIN' = ''.
SET PROPERTY OF GS_PAGE 'LEFTMARGIN' = ''.
SET PROPERTY OF GS_PAGE 'RIGHTMARGIN' = ''. *--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' = ''. "NOT ITALIC
SET PROPERTY OF GS_FONT 'UNDERLINE' = UNDERLINE. "NOT UNDERLINED
SET PROPERTY OF GS_CELL1 'WRAPTEXT' = .
SET PROPERTY OF GS_CELL1 'HORIZONTALALIGNMENT' = ALIGNMENT.
" && 水平方向 2左对齐，3居中，4右对齐
SET PROPERTY OF GS_CELL1 'VERTICALALIGNMENT' = .
"&& 垂直方向 1靠上 ，2居中，3靠下
SET PROPERTY OF GS_CELL1 'NUMBERFORMATLOCAL' = '@'.
"&& 设置数据格式
SET PROPERTY OF GS_CELL1 'VALUE' = VALUE.
ENDFORM. "FORMAT
*------------------------------
*该函数用来合并EXCEL单元格
*------------------------------
FORM MERGED USING X1 Y1 X2 Y2.
*--SELECTING CELL AREA TO BE MERGED.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1
EXPORTING
# = X1
# = Y1.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X2
# = Y2.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = 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
# = X1
# = Y1.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X2
# = Y2.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = 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' = ''.
SET PROPERTY OF GS_RANGE 'LINESTYLE' = ''.
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
# = X1
# = Y1.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X2
# = Y2.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = GS_CELL2.
CALL METHOD OF GS_CELLS 'SELECT'.
*--MERGING
CALL METHOD OF GS_CELLS 'MERGE' .
CALL METHOD OF GS_CELLS 'BORDERS' = GS_BORDERS
EXPORTING
# = ''.
SET PROPERTY OF GS_BORDERS 'LINESTYLE' = ''.
SET PROPERTY OF GS_BORDERS 'WEIGHT' = .
FREE OBJECT GS_BORDERS. CALL METHOD OF GS_CELLS 'BORDERS' = GS_BORDERS
EXPORTING
# = ''.
SET PROPERTY OF GS_BORDERS 'LINESTYLE' = ''.
*& (其中BORDERS参数：1－左、2－右、3－顶、4－底、5－斜、6－斜/； *&LINESTYLE值：1与7－细实、2－细虚、4－点虚、9－双细实线) SET PROPERTY OF GS_BORDERS 'WEIGHT' = .
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
# = X1
# = Y1.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X2
# = Y2.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = GS_CELL2.
CALL METHOD OF GS_CELLS 'SELECT'.
*--MERGING
CALL METHOD OF GS_CELLS 'MERGE' .
CALL METHOD OF GS_CELLS 'BORDERS' = GS_BORDERS
EXPORTING
# = ''.
SET PROPERTY OF GS_BORDERS 'LINESTYLE' = ''.
SET PROPERTY OF GS_BORDERS 'WEIGHT' = .
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
# = X1
# = Y1.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X2
# = Y2.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = GS_CELL2.
CALL METHOD OF GS_CELLS 'SELECT'.
*--MERGING
CALL METHOD OF GS_CELLS 'MERGE' .
CALL METHOD OF GS_CELLS 'BORDERS' = GS_BORDERS
EXPORTING
# = ''.
SET PROPERTY OF GS_BORDERS 'LINESTYLE' = ''.
SET PROPERTY OF GS_BORDERS 'WEIGHT' = .
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
# = X1
# = Y1.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X2
# = Y2.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = GS_CELL2.
CALL METHOD OF GS_CELLS 'SELECT'.
*--MERGING
CALL METHOD OF GS_CELLS 'MERGE' .
CALL METHOD OF GS_CELLS 'BORDERS' = GS_BORDERS
EXPORTING
# = ''.
SET PROPERTY OF GS_BORDERS 'LINESTYLE' = ''.
SET PROPERTY OF GS_BORDERS 'WEIGHT' = .
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
# = X1
# = Y1.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X2
# = Y2.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = GS_CELL2.
CALL METHOD OF GS_CELLS 'SELECT'.
*--MERGING
CALL METHOD OF GS_CELLS 'MERGE' .
CALL METHOD OF GS_CELLS 'BORDERS' = GS_BORDERS
EXPORTING
# = ''.
SET PROPERTY OF GS_BORDERS 'LINESTYLE' = ''.
SET PROPERTY OF GS_BORDERS 'WEIGHT' = .
FREE OBJECT GS_BORDERS.
ENDFORM. "MERGED *-------------------------------------------------
*设置行高和列宽。
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
# = X1
# = Y1.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X2
# = Y2.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = 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
# = X1
# = Y1.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X2
# = Y2.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = 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
# = X1
# = Y1.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X2
# = Y2.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = 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 = .
Y1 = .
X2 = X.
Y2 = Y.
*DO PAGE TIMES.
*_-CREATE EXCEL
CONCATENATE 'SHEET' '' INTO GV_SHEET_NAME.
CREATE OBJECT GS_EXCEL 'EXCEL.APPLICATION'.
SET PROPERTY OF GS_EXCEL 'VISIBLE' = .
GET PROPERTY OF GS_EXCEL 'WORKBOOKS' = GS_WBOOKLIST.
*----COPY
CALL METHOD OF GS_WBOOKLIST 'OPEN' = GS_RANGE
EXPORTING
# = L_FILE.
CALL METHOD OF GS_EXCEL 'WORKSHEETS' = GS_BORDERS
EXPORTING # = .
CALL METHOD OF GS_BORDERS 'ACTIVATE'.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1
EXPORTING
# =
# = .
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X
# = Y.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = 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
# =
# = .
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X
# = Y.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = 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 = .
X2 = X2 + X.
Y2 = Y.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1
EXPORTING
# =
# = .
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X
# = Y.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = GS_CELL2.
CALL METHOD OF GS_CELLS 'COPY'.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1
EXPORTING
# = X1
# = Y1.
CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2
EXPORTING
# = X2
# = Y2.
CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS
EXPORTING
# = GS_CELL1
# = GS_CELL2.
CALL METHOD OF GS_CELLS 'PASTESPECIAL'.
ENDDO.
ENDFORM.
if p_file is initial.
message '请输入导入文件的路径' type 'I'.
leave to transaction sy-tcode.
endif.
data: begin of i_excel occurs 0.
* ROW(4) TYPE N,
* COL(4) TYPE N,
* VALUE(150),
include structure alsmex_tabline.
data: end of i_excel.
data: l_answer(1) type c.
data: l_column type i.
field-symbols: <fs>.
data: l_pathname like rlgrap-filename.
data:
c_begin_row type i value 4, "Beginning row of excel file
c_begin_col type i value 2, "Beginning column of excel file
c_end_row type i value 10000, "Ending row of excel file
c_end_col type i value 11. "Ending column of excel file
move p_file to l_pathname.
call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
exporting
filename = l_pathname
i_begin_col = c_begin_col
i_begin_row = c_begin_row
i_end_col = c_end_col
i_end_row = c_end_row
tables
intern = i_excel
exceptions
inconsistent_parameters = 1
upload_ole = 2
others = 3.