sap excel 操作的基类
来源：互联网 发布：电影人间喜剧 知乎 编辑：程序博客网 时间：2024/06/01 09:56
*----------------------------------------------------------------------*
* INCLUDE Z_EXCEL *
*----------------------------------------------------------------------*
data: w_xl type ole2_object,
w_cl type ole2_object,
w_wkbook type ole2_object,
w_ws type ole2_object.
data: w_success type c.
data: columnsobj type ole2_object, " Columns object
rowsobj type ole2_object, " Range object
bordersobj type ole2_object, " Borders object
entirecolumnobj type ole2_object,
fontobj type ole2_object. " Font object.
*&---------------------------------------------------------------------*
*& Form open_excel
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_P_FILE text
*----------------------------------------------------------------------*
form open_excel using p_file.
create object w_xl 'Excel.Application'.
if sy-subrc ne 0.
write: / 'Excel init failed, return code is', sy-subrc.
exit.
else.
set property of w_xl 'Visible' = 0.
call method of w_xl 'Workbooks' = w_wkbook.
call method of w_wkbook 'Open' = w_wkbook
exporting #1 = p_file.
call method of w_wkbook 'Worksheets' = w_ws
exporting #1 = 'Sheet1'.
call method of w_ws 'Activate'.
endif.
endform. " open_excel
*&---------------------------------------------------------------------*
*& Form GET_CELL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_W_ROW text
* -->P_2 text
* -->P_I_BASEDATA_ORGEN2 text
*----------------------------------------------------------------------*
form get_cell using p_row
p_column
changing p_record.
call method of w_ws 'cells' = w_cl
exporting #1 = p_row #2 = p_column.
get property of w_cl 'value' = p_record.
endform. " GET_CELL
*&---------------------------------------------------------------------*
*& Form GET_CELL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_W_ROW text
* -->P_2 text
* -->P_I_BASEDATA_ORGEN2 text
*----------------------------------------------------------------------*
form get_cellt using p_row
p_column
changing p_record.
call method of w_ws 'cells' = w_cl
exporting #1 = p_row #2 = p_column.
get property of w_cl 'text' = p_record.
endform. " GET_CELL
*&---------------------------------------------------------------------*
*& Form CLOSE_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form close_excel.
call method of w_wkbook 'Save'.
call method of w_wkbook 'Close' exporting #1 = 0.
call method of w_xl 'Quit'.
free object w_cl.
free object w_ws.
free object w_wkbook.
free object w_xl.
endform. " CLOSE_EXCEL
*&---------------------------------------------------------------------*
*& Form SET_CELL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_W_ROW text
* -->P_1 text
* -->P_W_XUHAO text
*----------------------------------------------------------------------*
form set_cell using p_row
p_colum
p_value.
call method of w_ws 'cells' = w_cl exporting #1 = p_row #2 = p_colum.
set property of w_cl 'value' = p_value.
endform. " SET_CELL
*&---------------------------------------------------------------*
*& Form FILL_XLS
*&---------------------------------------------------------------*
* 将数据放入EXCEL的单元格中并设置属性
* -->P_ROW 行
* -->P_COLUMN 列
* -->P_DATUM 数据
*----------------------------------------------------------------*
form fill_xls using value(p_row) value(p_column) value(p_datum)
value(p_color) value(p_size) value(p_bold)
value(is_number).
if is_number = 1
* AND p_datum <> 0.
and p_datum <> space.
call method of w_ws
'CELLS' = w_cl
exporting #1 = p_row
#2 = p_column.
set property of w_cl 'VALUE' = p_datum.
call method of w_cl 'Select'.
call method of w_cl 'Font' = fontobj.
set property of fontobj 'ColorIndex' = p_color.
set property of fontobj 'Size' = p_size.
set property of fontobj 'Bold' = p_bold.
elseif is_number = 0.
call method of w_ws
'CELLS' = w_cl
exporting #1 = p_row
#2 = p_column.
set property of w_cl 'VALUE' = p_datum.
call method of w_cl 'Select'.
call method of w_cl 'Font' = fontobj.
set property of fontobj 'ColorIndex' = p_color.
set property of fontobj 'Size' = p_size.
set property of fontobj 'Bold' = p_bold.
endif.
endform. " FILL_XLS
*&---------------------------------------------------------------------*
*& Form display_seting
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form display_seting.
*设置标题
*合并单元格
perform set_merge using 'A1' 'P1' 0 0 .
call method of w_ws
'Range' = rowsobj
exporting #1 = 'A1'.
call method of rowsobj 'Select'.
set property of rowsobj 'HorizontalAlignment' = 3."平中
call method of rowsobj 'Font' = fontobj.
set property of fontobj 'Name' = '楷体_GB2312'.
set property of fontobj 'Size' = 18.
*设置列宽度--自动
perform set_column_width_autofit using 'A' .
perform set_column_width_autofit using 'B' .
perform set_column_width_autofit using 'C' .
perform set_column_width_autofit using 'D' .
perform set_column_width_autofit using 'E' .
perform set_column_width_autofit using 'F' .
perform set_column_width_autofit using 'G' .
perform set_column_width_autofit using 'H' .
perform set_column_width_autofit using 'I' .
perform set_column_width_autofit using 'J' .
perform set_column_width_autofit using 'K' .
perform set_column_width_autofit using 'L' .
perform set_column_width_autofit using 'M' .
perform set_column_width_autofit using 'N' .
perform set_column_width_autofit using 'O' .
perform set_column_width_autofit using 'P' .
endform. " display_seting
*&---------------------------------------------------------------------*
*& Form set_merge
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_0321 text
* -->P_0322 text
* -->P_0 text
* -->P_0 text
*----------------------------------------------------------------------*
form set_merge using value(start_cellname) value(end_cellname)
value(h_alignment) value(v_alignment).
call method of w_ws
'Range' = rowsobj
exporting #1 = start_cellname
#2 = end_cellname.
call method of rowsobj 'Select'.
call method of rowsobj 'Merge'.
*H_Alignment和V_Alignment = 0时不作处理
if h_alignment > 0.
set property of rowsobj 'HorizontalAlignment' = h_alignment.
endif.
if v_alignment > 0.
set property of rowsobj 'VerticalAlignment' = v_alignment.
endif.
endform. " set_merge
*&---------------------------------------------------------------------*
*& Form set_column_width_autofit
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_0372 text
*----------------------------------------------------------------------*
form set_column_width_autofit using value(column_name).
call method of w_ws
'Columns' = columnsobj
exporting #1 = column_name.
call method of columnsobj 'EntireColumn' = entirecolumnobj.
call method of entirecolumnobj 'AutoFit'.
endform. " SET_COLUMN_WIDTH_AutoFit
*&-----------------------------------------------------------------*
*& Form SET_COLUMN_WIDTH
*&-----------------------------------------------------------------*
form set_column_width using value(column_name) value(column_width).
call method of w_ws
'Columns' = columnsobj
exporting #1 = column_name.
set property of columnsobj 'ColumnWidth' = column_width.
endform. " SET_COLUMN_WIDTH
*&---------------------------------------------------------------------*
*& Form display_border
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_2406 text
* -->P_W_LASTCELL text
*----------------------------------------------------------------------*
form display_border using value(start_cellname) value(end_cellname).
call method of w_ws
'Range' = rowsobj
exporting #1 = start_cellname
#2 = end_cellname.
call method of rowsobj 'Select'.
call method of rowsobj 'Borders' = bordersobj.
set property of bordersobj 'LineStyle' = 1.
call method of w_ws
'Range' = rowsobj
exporting #1 = start_cellname.
call method of rowsobj 'Select'.
endform. " display_border
*&---------------------------------------------------------------------*
*& Form open_excel1
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_W_FILE text
*----------------------------------------------------------------------*
form open_excel1 using p_file.
create object w_xl 'Excel.Application'.
if sy-subrc ne 0.
* WRITE: / 'Excel init failed, return code is', sy-subrc.
* EXIT.
w_success = 'N'.
else.
set property of w_xl 'Visible' = 0.
call method of w_xl 'Workbooks' = w_wkbook.
call method of w_wkbook 'Open' = w_wkbook
exporting #1 = p_file.
call method of w_wkbook 'Worksheets' = w_ws
exporting #1 = 'Sheet1'.
call method of w_ws 'Activate'.
endif.
endform. " open_excel1
*&---------------------------------------------------------------------*
*& Form open_excel2
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_P_FILE text
*----------------------------------------------------------------------*
form open_excel2 using p_file.
create object w_xl 'Excel.Application'.
if sy-subrc ne 0.
* WRITE: / 'Excel init failed, return code is', sy-subrc.
* EXIT.
w_success = 'N'.
else.
set property of w_xl 'Visible' = 0.
call method of w_xl 'Workbooks' = w_wkbook.
call method of w_wkbook 'Open' = w_wkbook
exporting #1 = p_file.
get property of w_xl 'ACTIVECELL' = w_ws.
* CALL METHOD OF w_wkbook 'Worksheets' = w_ws
* EXPORTING #1 = 'Sheet1'.
* CALL METHOD OF w_ws 'Activate'.
endif.
endform. " open_excel2
*&---------------------------------------------------------------------*
*& Form open_excel3
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_P_FILE text
*----------------------------------------------------------------------*
form open_excel3 using p_file."add by lujs 20050114
create object w_xl 'Excel.Application'.
if sy-subrc ne 0.
w_success = 'N'.
else.
set property of w_xl 'Visible' = 0.
call method of w_xl 'Workbooks' = w_wkbook.
call method of w_wkbook 'Open' = w_wkbook
exporting #1 = p_file.
call method of w_wkbook 'Worksheets' = w_ws
exporting #1 = 'Sheet1'.
* GET PROPERTY OF w_xl 'ACTIVECELL' = w_ws.
* EXPORTING #1 = 'Sheet1'.
endif.
endform. " open_excel3
===============================================================
===============================================================
*----------------------------------------------------------------------*
* INCLUDE ZPUBLIC_EXCEL
*----------------------------------------------------------------------*
data: w_xl type ole2_object,
w_cl type ole2_object,
w_wkbook type ole2_object,
w_comment type ole2_object,
w_numberformat type ole2_object,
w_column type ole2_object,
w_ws type ole2_object.
data: w_success type c.
data: columnsobj type ole2_object, " Columns object
rowsobj type ole2_object, " Range object
bordersobj type ole2_object, " Borders object
entirecolumnobj type ole2_object,
fontobj type ole2_object. " Font object.
*&---------------------------------------------------------------------*
*& Form open_excel
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_P_FILE text
*----------------------------------------------------------------------*
form open_excel using p_file.
create object w_xl 'Excel.Application'.
if sy-subrc ne 0.
write: / 'Excel init failed, return code is', sy-subrc.
exit.
else.
call method of w_xl 'Workbooks' = w_wkbook.
call method of w_wkbook 'Open' = w_wkbook
exporting #1 = p_file.
get property of w_xl 'ACTIVECELL' = w_ws.
* SET PROPERTY OF w_xl 'Visible' = 0.
* CALL METHOD OF w_wkbook 'Worksheets' = w_ws
* EXPORTING #1 = 'Sheet1'.
* CALL METHOD OF w_ws 'Activate'.
endif.
endform. " open_excel
*&---------------------------------------------------------------------*
*& Form open_excel_sheet
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_P_FILE text
* -->P_P_SHEET text
*----------------------------------------------------------------------*
form open_excel_sheet using p_file p_sheet.
create object w_xl 'Excel.Application'.
if sy-subrc ne 0.
write: / 'Excel init failed, return code is', sy-subrc.
exit.
else.
set property of w_xl 'Visible' = 0.
call method of w_xl 'Workbooks' = w_wkbook.
call method of w_wkbook 'Open' = w_wkbook
exporting #1 = p_file.
call method of w_wkbook 'Worksheets' = w_ws
exporting #1 = p_sheet.
call method of w_ws 'Activate'.
endif.
endform. " open_excel_sheet
*&---------------------------------------------------------------------*
*& Form GET_CELL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_W_ROW text
* -->P_2 text
* -->P_I_BASEDATA_ORGEN2 text
*----------------------------------------------------------------------*
form get_cell using p_row
p_column
changing p_record.
call method of w_ws 'cells' = w_cl
exporting #1 = p_row #2 = p_column.
get property of w_cl 'value' = p_record.
endform. " GET_CELL
*&---------------------------------------------------------------------*
*& Form GET_CELL_TEXT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_W_ROW text
* -->P_2 text
* -->P_I_BASEDATA_ORGEN2 text
*----------------------------------------------------------------------*
form get_cell_text using p_row
p_column
changing p_record.
call method of w_ws 'cells' = w_cl
exporting #1 = p_row #2 = p_column.
get property of w_cl 'text' = p_record.
condense p_record no-gaps.
endform. " GET_CELL_TEXT
*&---------------------------------------------------------------------*
*& Form CLOSE_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form close_excel.
call method of w_wkbook 'Save'.
call method of w_wkbook 'Close' exporting #1 = 0.
call method of w_xl 'Quit'.
free object w_cl.
free object w_ws.
free object w_wkbook.
free object w_xl.
endform. " CLOSE_EXCEL
*&---------------------------------------------------------------------*
*& Form SET_CELL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_W_ROW text
* -->P_1 text
* -->P_W_XUHAO text
*----------------------------------------------------------------------*
form set_cell using p_row
p_colum
p_value.
call method of w_ws 'cells' = w_cl exporting #1 = p_row #2 = p_colum.
set property of w_cl 'value' = p_value.
endform. " SET_CELL
*&---------------------------------------------------------------*
*& Form FILL_XLS
*&---------------------------------------------------------------*
* 将数据放入EXCEL的单元格中并设置属性
* -->P_ROW 行
* -->P_COLUMN 列
* -->P_DATUM 数据
*----------------------------------------------------------------*
form fill_xls using value(p_row) value(p_column) value(p_datum)
value(p_color) value(p_size) value(p_bold)
value(is_number).
if is_number = 1
* AND p_datum <> 0.
and p_datum <> space.
call method of w_ws
'CELLS' = w_cl
exporting #1 = p_row
#2 = p_column.
set property of w_cl 'VALUE' = p_datum.
call method of w_cl 'Select'.
call method of w_cl 'Font' = fontobj.
set property of fontobj 'ColorIndex' = p_color.
set property of fontobj 'Size' = p_size.
set property of fontobj 'Bold' = p_bold.
elseif is_number = 0.
call method of w_ws
'CELLS' = w_cl
exporting #1 = p_row
#2 = p_column.
set property of w_cl 'VALUE' = p_datum.
call method of w_cl 'Select'.
call method of w_cl 'Font' = fontobj.
set property of fontobj 'ColorIndex' = p_color.
set property of fontobj 'Size' = p_size.
set property of fontobj 'Bold' = p_bold.
endif.
endform. " FILL_XLS
*&---------------------------------------------------------------------*
*& Form display_seting
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form display_seting.
*设置标题
*合并单元格
perform set_merge using 'A1' 'P1' 0 0 .
call method of w_ws
'Range' = rowsobj
exporting #1 = 'A1'.
call method of rowsobj 'Select'.
set property of rowsobj 'HorizontalAlignment' = 3."平中
call method of rowsobj 'Font' = fontobj.
set property of fontobj 'Name' = '楷体_GB2312'.
set property of fontobj 'Size' = 18.
*设置列宽度--自动
perform set_column_width_autofit using 'A' .
perform set_column_width_autofit using 'B' .
perform set_column_width_autofit using 'C' .
perform set_column_width_autofit using 'D' .
perform set_column_width_autofit using 'E' .
perform set_column_width_autofit using 'F' .
perform set_column_width_autofit using 'G' .
perform set_column_width_autofit using 'H' .
perform set_column_width_autofit using 'I' .
perform set_column_width_autofit using 'J' .
perform set_column_width_autofit using 'K' .
perform set_column_width_autofit using 'L' .
perform set_column_width_autofit using 'M' .
perform set_column_width_autofit using 'N' .
perform set_column_width_autofit using 'O' .
perform set_column_width_autofit using 'P' .
endform. " display_seting
*&---------------------------------------------------------------------*
*& Form set_merge
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_0321 text
* -->P_0322 text
* -->P_0 text
* -->P_0 text
*----------------------------------------------------------------------*
form set_merge using value(start_cellname) value(end_cellname)
value(h_alignment) value(v_alignment).
call method of w_ws
'Range' = rowsobj
exporting #1 = start_cellname
#2 = end_cellname.
call method of rowsobj 'Select'.
call method of rowsobj 'Merge'.
*H_Alignment和V_Alignment = 0时不作处理
if h_alignment > 0.
set property of rowsobj 'HorizontalAlignment' = h_alignment.
endif.
if v_alignment > 0.
set property of rowsobj 'VerticalAlignment' = v_alignment.
endif.
endform. " set_merge
*&---------------------------------------------------------------------*
*& Form set_column_width_autofit
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_0372 text
*----------------------------------------------------------------------*
form set_column_width_autofit using value(column_name).
call method of w_ws
'Columns' = columnsobj
exporting #1 = column_name.
call method of columnsobj 'EntireColumn' = entirecolumnobj.
call method of entirecolumnobj 'AutoFit'.
endform. " SET_COLUMN_WIDTH_AutoFit
*&-----------------------------------------------------------------*
*& Form SET_COLUMN_WIDTH
*&-----------------------------------------------------------------*
form set_column_width using value(column_name) value(column_width).
call method of w_ws
'Columns' = columnsobj
exporting #1 = column_name.
set property of columnsobj 'ColumnWidth' = column_width.
endform. " SET_COLUMN_WIDTH
*&---------------------------------------------------------------------*
*& Form display_border
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_2406 text
* -->P_W_LASTCELL text
*----------------------------------------------------------------------*
form display_border using value(start_cellname) value(end_cellname).
call method of w_ws
'Range' = rowsobj
exporting #1 = start_cellname
#2 = end_cellname.
call method of rowsobj 'Select'.
call method of rowsobj 'Borders' = bordersobj.
set property of bordersobj 'LineStyle' = 1.
call method of w_ws
'Range' = rowsobj
exporting #1 = start_cellname.
call method of rowsobj 'Select'.
endform. " display_border
*&---------------------------------------------------------------------*
*& Form open_excel1
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_W_FILE text
*----------------------------------------------------------------------*
form open_excel1 using p_file.
create object w_xl 'Excel.Application'.
if sy-subrc ne 0.
* WRITE: / 'Excel init failed, return code is', sy-subrc.
* EXIT.
w_success = 'N'.
else.
set property of w_xl 'Visible' = 0.
call method of w_xl 'Workbooks' = w_wkbook.
call method of w_wkbook 'Open' = w_wkbook
exporting #1 = p_file.
call method of w_wkbook 'Worksheets' = w_ws
exporting #1 = 'Sheet1'.
call method of w_ws 'Activate'.
endif.
endform. " open_excel1
- sap excel 操作的基类
- sap doi技术操作excel的方法
- SAP官方关于ABAP操作EXCEL的例子
- SAP ABAP OLE 操作上传的EXCEL模板（转）
- SAP ABAP OLE 操作EXCEL小结
- SAP ABAP OLE 操作EXCEL小结
- SAP BOM的操作
- Excel操作基类
- php操作excel的类
- C# 操作Excel的类
- C# 操作Excel的类
- .net 操作Excel的类
- C# 操作Excel的类
- C#的EXCEL操作类
- C# 操作Excel的类
- C# 操作Excel的类
- C# 操作Excel的类
- 操作Excel的经典类
- POA简介
- CL.exe的全部命令
- 一个用户注册常用表单验证
- nmake使用的makefile
- 唐骏跳槽福建民企新华都出任总裁
- sap excel 操作的基类
- vb.net仿EXCEL的自动填充功能
- 图解Struts,Hibernate工作原理
- Hibernate+ehcache二级缓存技术
- CSS:css滑动门技术
- 学习一下ibatis
- Ajax 和 XML: 用于表格的 Ajax
- Lucene中文分词组件 JE-Analysis 1.5.1
- 转载：c++builder多线程程序
