EXCEL OPRATION
来源：互联网 发布：在线提取网站源码 编辑：程序博客网 时间：2024/04/30 05:03
***INCLUDE Z_EXCEL_HEADER .
INCLUDE ole2incl.
CONSTANTS: xlleft VALUE '0',
xlcenter VALUE '3',
xlright VALUE '4',
xltop VALUE '1',
xlmiddle VALUE '2',
xlbottom VALUE '0',
xlfill VALUE '5'.
DATA : excel TYPE ole2_object,
books TYPE ole2_object,
book TYPE ole2_object,
sheet TYPE ole2_object,
cell TYPE ole2_object,
cell1 TYPE ole2_object,
cell2 TYPE ole2_object,
range TYPE ole2_object,
line TYPE i,
row TYPE i,
col TYPE i.
* VARIABLES FROM INCLUDE FILE EXCLDEF .
DATA: macro TYPE ole2_object, "MACRO object
w_book TYPE ole2_object, "list of workbooks
w_sheet TYPE ole2_object, "list of sheet
seletion TYPE ole2_object, "Selection
value TYPE ole2_object, "value
cells TYPE ole2_object, "cellS
celle TYPE ole2_object, "cellE
font TYPE ole2_object, "font
border TYPE ole2_object, "Border
column TYPE ole2_object, "Column
interior TYPE ole2_object . "Interior
*---------------------------------------------------------------------*
* FORM ERR_HDL *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
FORM err_hdl.
IF sy-subrc <> 0.
WRITE: / 'Error Code in OLE-Automation:'(010), sy-subrc.
STOP.
ENDIF.
ENDFORM. " ERR_HDL
*---------------------------------------------------------------------*
* FORM READ_CELL *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> I *
* --> J *
* --> VAL *
*---------------------------------------------------------------------*
FORM read_cell USING i j val .
CALL METHOD OF excel 'Cells' = cell EXPORTING #1 = i #2 = j.
CALL METHOD OF cell 'Value' = val .
ENDFORM. "read_cell
*---------------------------------------------------------------------*
* FORM READ_CELL *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> I *
* --> J *
* <-- VAL *
*---------------------------------------------------------------------*
FORM read_cell_text USING i j CHANGING val .
CALL METHOD OF excel 'Cells' = cell EXPORTING #1 = i #2 = j.
CALL METHOD OF cell 'Text' = val .
ENDFORM. "read_cell_text
*---------------------------------------------------------------------*
* FORM FILL_CELL *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> I *
* --> J *
* --> VAL *
*---------------------------------------------------------------------*
FORM fill_cell USING i j val.
CALL METHOD OF excel 'CELLS' = cell EXPORTING #1 = i #2 = j.
SET PROPERTY OF cell 'VALUE' = val .
ENDFORM. "fill_cell
*---------------------------------------------------------------------*
* FORM fill_cell_addcol *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> I *
* --> J *
* --> VAL *
*---------------------------------------------------------------------*
FORM fill_cell_addcol USING i j val.
CALL METHOD OF excel 'CELLS' = cell EXPORTING #1 = i #2 = j.
SET PROPERTY OF cell 'VALUE' = val .
ADD 1 TO j.
ENDFORM. "fill_cell_addcol
*---------------------------------------------------------------------*
* FORM fill_cell_addrow *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> I *
* --> J *
* --> VAL *
*---------------------------------------------------------------------*
FORM fill_cell_addrow USING i j val.
CALL METHOD OF excel 'CELLS' = cell EXPORTING #1 = i #2 = j.
SET PROPERTY OF cell 'VALUE' = val .
ADD 1 TO i.
ENDFORM. "fill_cell_addcol
*&---------------------------------------------------------------------*
*& Form fill_cell_with_border
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->I text
* -->J text
* -->VAL text
* -->EGYN text
*----------------------------------------------------------------------*
FORM fill_cell_with_border USING i j val.
CALL METHOD OF excel 'Cells' = cell EXPORTING #1 = i #2 = j.
SET PROPERTY OF cell 'Value' = val .
CALL METHOD OF cell 'Borders' = border .
SET PROPERTY OF border 'LineStyle' = 1. "1 -- 实线, 2 -- 虚线
SET PROPERTY OF border 'Weight' = 2. "边框线的粗细
ENDFORM. "fill_cell_with_border
*---------------------------------------------------------------------*
* FORM set_range *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> LINE1 *
* --> ROW1 *
* --> LINE2 *
* --> ROW2 *
* --> P_RANGE *
*---------------------------------------------------------------------*
FORM set_range USING line1 row1 line2 row2
CHANGING p_range.
DATA: l_cell1 TYPE ole2_object,
l_cell2 TYPE ole2_object.
CALL METHOD OF excel 'CELLS' = l_cell1
EXPORTING #1 = line1 #2 = row1.
CALL METHOD OF excel 'CELLS' = l_cell2
EXPORTING #1 = line2 #2 = row2.
CALL METHOD OF excel 'Range' = p_range
EXPORTING #1 = l_cell1 #2 = l_cell2.
ENDFORM. "set_range
*---------------------------------------------------------------------*
* FORM merge_cell *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> LINE1 *
* --> ROW1 *
* --> LINE2 *
* --> ROW2 *
*---------------------------------------------------------------------*
FORM merge_cell USING line1 row1 line2 row2 halign valign.
DATA: l_range TYPE ole2_object.
PERFORM set_range
USING line1 row1
line2 row2
CHANGING l_range.
SET PROPERTY OF l_range 'MergeCells' = 1.
IF halign <> '-1'.
SET PROPERTY OF l_range 'HorizontalAlignment' = halign.
ENDIF.
IF valign <> '-1'.
SET PROPERTY OF l_range 'VerticalAlignment' = valign.
ENDIF.
ENDFORM. "merge_cell
*---------------------------------------------------------------------*
* FORM read_from_file *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> P_FILE *
*---------------------------------------------------------------------*
FORM open_from_template USING p_file.
CREATE OBJECT excel 'EXCEL.APPLICATION'.
SET PROPERTY OF excel 'VISIBLE' = 0. "1 -- Visible, 0 -- invisiable
CALL METHOD OF excel 'Workbooks' = books.
CALL METHOD OF books 'Open' EXPORTING #1 = p_file .
CALL METHOD OF excel 'WORKSHEETS' = sheet EXPORTING #1 = 1 .
IF sy-subrc <> 0.
CALL METHOD OF excel 'Quit'.
FREE OBJECT excel .
MESSAGE e398(00) WITH '没有找到EXCEL文件!' '' '' ''.
ENDIF.
ENDFORM. "open_from_template
*---------------------------------------------------------------------*
* FORM open_blank_book *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
FORM open_blank_books.
* 启动EXCEL
CREATE OBJECT excel 'EXCEL.APPLICATION'.
SET PROPERTY OF excel 'VISIBLE' = 1.
CALL METHOD OF excel 'Workbooks' = books.
CALL METHOD OF books 'ADD' = book.
CALL METHOD OF book 'SHEETS' = sheet EXPORTING #1 = 1 .
CALL METHOD OF sheet 'ACTIVATE'.
ENDFORM. "open_blank_books
*---------------------------------------------------------------------*
* FORM open_invisible_books *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
FORM open_invisible_books.
* 启动EXCEL
CREATE OBJECT excel 'EXCEL.APPLICATION'.
SET PROPERTY OF excel 'VISIBLE' = 0.
CALL METHOD OF excel 'Workbooks' = books.
CALL METHOD OF books 'ADD' = book.
CALL METHOD OF book 'SHEETS' = sheet EXPORTING #1 = 1 .
CALL METHOD OF sheet 'ACTIVATE'.
ENDFORM. "open_invisible_books
*---------------------------------------------------------------------*
* FORM quit_excel *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
FORM close_excel.
SET PROPERTY OF excel 'DisplayAlerts' = 0.
CALL METHOD OF excel 'Quit'.
FREE OBJECT excel .
ENDFORM. "close_excel
*---------------------------------------------------------------------*
* FORM SELSHEET *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> P_SHEET *
*---------------------------------------------------------------------*
FORM selsheet USING p_sheet .
CALL METHOD OF excel 'WORKSheets' = sheet EXPORTING #1 = p_sheet .
CALL METHOD OF sheet 'SELECT' .
CALL METHOD OF sheet 'ACTIVATE'.
ENDFORM . "selsheet
*---------------------------------------------------------------------*
* FORM set_cell_border *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> P_ROW *
* --> P_COL *
* --> P_STYLE *
* --> P_WEIGHT *
*---------------------------------------------------------------------*
FORM set_cell_border USING p_row1 p_col1
p_row2 p_col2
p_style p_weight.
DATA: l_range TYPE ole2_object,
l_border TYPE ole2_object.
PERFORM set_range
USING p_row1
p_col1
p_row2
p_col2
CHANGING l_range.
CALL METHOD OF l_range 'Borders' = l_border .
SET PROPERTY OF l_border 'LineStyle' = p_style.
SET PROPERTY OF l_border 'Weight' = p_weight.
ENDFORM. "set_cell_border
*---------------------------------------------------------------------*
* FORM load_from_excel_file *
*---------------------------------------------------------------------*
* 将EXCEL文件内容导入内部表 *
*---------------------------------------------------------------------*
* --> I_ITAB *
* --> P_FILE *
* --> P_COLS: 需要导入EXCEL的列数,以空格分割 *
* 如果未输入列数,导入所有有表头的列
* --> P_FIELDS: 对应内部字段的列数,以空格分割 *
* 如果未输入字段列数,默认为从第一个字段依次导入 *
*---------------------------------------------------------------------*
FORM load_from_excel_file TABLES i_itab
USING p_file
p_cols
p_fields.
DATA: BEGIN OF i_col OCCURS 0,
number(3) TYPE n,
END OF i_col.
DATA: BEGIN OF i_field OCCURS 0,
number(3) TYPE n,
END OF i_field.
DATA: cell_value TYPE string,
tabix LIKE sy-tabix.
FIELD-SYMBOLS: <fs> TYPE ANY,
<f1> TYPE ANY.
SPLIT: p_cols AT space INTO TABLE i_col,
p_fields AT space INTO TABLE i_field.
PERFORM open_from_template USING p_file.
* 如果未输入列数,导入所有有表头的列
* 对应的列每一列必须有表头
IF i_col[] IS INITIAL.
DO.
PERFORM read_cell_text
USING 1 sy-index
CHANGING cell_value.
IF cell_value = space.
EXIT.
ENDIF.
CLEAR i_col.
i_col-number = sy-index.
APPEND i_col.
ENDDO.
ENDIF.
* 如果未输入字段列数,默认为从第一个字段依次导入
IF i_field[] IS INITIAL.
ASSIGN i_itab TO <fs>.
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE <fs> TO <f1>.
IF sy-subrc <> 0.
EXIT.
ENDIF.
CLEAR i_field.
i_field-number = sy-index.
APPEND i_field.
ENDDO.
ENDIF.
row = 2.
DO.
PERFORM read_cell_text
USING row 1
CHANGING cell_value.
IF cell_value = space.
EXIT.
ENDIF.
CLEAR: tabix,
i_itab.
LOOP AT i_col.
ADD 1 TO tabix.
col = i_col-number.
PERFORM read_cell_text
USING row col
CHANGING cell_value.
ASSIGN i_itab TO <fs>.
READ TABLE i_field INDEX tabix.
IF sy-subrc = 0.
ASSIGN COMPONENT i_field-number OF STRUCTURE <fs> TO <f1>.
<f1> = cell_value.
ENDIF.
ENDLOOP.
APPEND i_itab.
ADD 1 TO row.
ENDDO.
PERFORM close_excel.
ENDFORM. "load_from_excel_file
- EXCEL OPRATION
- Opration Procedure of Early Computers
- C 左移 右移 opration
- TortoiseSVN 连接资源库时 opration in progerss
- swift 利用Opration和OprationQueue来下载网络图片
- apache配置php的LoadModule php5_module "XX"引起apache启动服务opration failed错误解决
- Excel
- Excel
- ExCel
- excel
- Excel
- EXCEL
- excel
- excel
- EXCEL
- Excel
- excel
- Excel
- VC/VS 注释快捷键
- 程序员应知——你有几种武器
- CLUSTER TABLE
- Struts2拦截器(Interceptor)
- Struts2 json plugin实战3 - JSON RPC
- EXCEL OPRATION
- 用OpenInventor实现的NeHe OpenGL教程－第十课
- inside C++笔记之拷贝构造函数
- access数据库转换成sql2005
- Windows 7安全指南
- 77种互联网盈利创新模式(2)
- 怎样保持Oracle数据库SQL性能的稳定性(转ITPUB一片文章)
- alv
- 我国现在整个银行业及和它相关的行业（证券，保险，IT业）的一些大概情况
