ABAP使用OLE2对象创建EXCEL文件
正文
厌倦了总是下载一模一样的EXCEL文档？没有颜色，边框，有效性验证....
让我们看看怎样用OLE2对象来创造可爱的EXCEL工作表吧！（效果如下）
首先你需要知道微软EXCEL中的不同部分的名称，每个部分在我们的程序中都代表一个OLE2对象
本文地址：http://www.cnblogs.com/hhelibeb/p/5787396.html
原文地址：Using ole2 objects for create an excel file
转载请注明
2018.03.29：做了一年多ABAP开发工作之后，译者认为OLE是个很难用很烦人的东西，强烈推荐使用XLSX Workbench进行EXCEL表单/报表的开发工作：
开始
所有例子都使用了下面这个模板报表。你只需要复制代码，并且粘贴到为它预留的空白位置。
在这个报表当中，你将会看到如何创建一个新文档，如何保存它，以及如何关闭它。
REPORT zric_ole2.
TYPE-POOLS: soi,ole2.
DATA: lo_application TYPE ole2_object,
lo_workbook TYPE ole2_object,
lo_workbooks TYPE ole2_object,
lo_range TYPE ole2_object,
lo_worksheet TYPE ole2_object,
lo_worksheets TYPE ole2_object,
lo_column TYPE ole2_object,
lo_row TYPE ole2_object,
lo_cell TYPE ole2_object,
lo_font TYPE ole2_object.
DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_validation TYPE ole2_object.
DATA: lv_selected_folder TYPE string,
lv_complete_path TYPE char256,
lv_titulo TYPE string.
CALL METHOD cl_gui_frontend_services=>directory_browse
EXPORTING
window_title = lv_titulo
initial_folder = 'C:\'
CHANGING
selected_folder = lv_selected_folder
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
OTHERS = 3.
CHECK NOT lv_selected_folder IS INITIAL.
CREATE OBJECT lo_application 'Excel.Application'.
CALL METHOD OF lo_application 'Workbooks' = lo_workbooks.
CALL METHOD OF lo_workbooks 'Add' = lo_workbook.
SET PROPERTY OF lo_application 'Visible' = 0.
GET PROPERTY OF lo_application 'ACTIVESHEET' = lo_worksheet.
* ----------
* ---- PASTE HERE THE CODE
* ----------
CONCATENATE lv_selected_folder '\Test' INTO lv_complete_path.
CALL METHOD OF lo_workbook 'SaveAs'
EXPORTING
#1 = lv_complete_path.
IF sy-subrc EQ 0.
MESSAGE 'File downloaded successfully' TYPE 'S'.
ELSE.
MESSAGE 'Error downloading the file' TYPE 'E'.
ENDIF.
CALL METHOD OF lo_application 'QUIT'.
FREE OBJECT lo_worksheet.
FREE OBJECT lo_workbook.
FREE OBJECT lo_application.
基本动作
Select a cell Select range of cells Select a column Select a row Get the selection reference Change the active worksheet Change the name of worksheet Add worksheet
Zoom
（译者注：原文的Add worksheet代码有遗漏，本人进行了补正）
修改内容
我认为理解这部分的工作原理的最佳方式是在EXCEL中创建一个宏，观察它的Visual Basic代码，以此“翻译”成ABAP代码。
要创建一个宏，首先你需要激活开发者标签，下面的链接解释了如何做这件事：
创建一个宏很简单，你可以按照这个链接中的办法做:
http://office.microsoft.com/en-us/excel-help/create-or-delete-a-macro-HP010014111.aspx
我也建议你下载一份VB语言参考作为指导：
http://msdn.microsoft.com/en-us/library/aa220733(v=office.11).aspx
对比VB代码和ABAP代码，你会理解它的工作原理。你不需要在任何情形下都把VB代码完全转换为ABAP，只转换你需要的部分。
1 - 选取一个单元格，设置值:
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
#1 = 1 "Row
#2 = 2. "Column
SET PROPERTY OF lo_cell 'Value' = 'Hello World'.
结果：
2- 修改字体大小
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
#1 = 1 "Row
#2 = 2. "Column
SET PROPERTY OF lo_cell 'Value' = 'Hello World'.
CALL METHOD OF lo_cell 'FONT' = lo_font.
SET PROPERTY OF lo_font 'Name' = 'Arial'.
SET PROPERTY OF lo_font 'Size' = 15.
结果：
3- 颜色，粗体，下划线，斜体:
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
#1 = 1 "Row
#2 = 2. "Column
SET PROPERTY OF lo_cell 'Value' = 'Hello World'.
CALL METHOD OF lo_cell 'FONT' = lo_font.
SET PROPERTY OF lo_font 'Color' = -16776961.
SET PROPERTY OF lo_font 'TintAndShade' = 0.
SET PROPERTY OF lo_font 'Bold' = 1.
SET PROPERTY OF lo_font 'Italic' = 1.
SET PROPERTY OF lo_font 'Underline' = 2. "xlUnderlineStyleSingle
DATA: lo_interior TYPE ole2_object.
CALL METHOD OF lo_cell 'Interior' = lo_interior.
SET PROPERTY OF lo_interior 'Color' = 15773696.
结果：
4- 添加边框
DATA: lo_borders TYPE ole2_object.
CALL METHOD OF lo_cell 'Borders' = lo_borders EXPORTING #1 = '7'. "xlEdgeLeft
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_cell 'Borders' = lo_borders EXPORTING #1 = '8'. "xlEdgeTop
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_cell 'Borders' = lo_borders EXPORTING #1 = '9'. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
CALL METHOD OF lo_cell 'Borders' = lo_borders EXPORTING #1 = '10'. "xlEdgeRight
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous
* Increase the weight of the border if you want, in this case only for EdgeRight:
SET PROPERTY OF lo_borders 'WEIGHT' = 4. "xlThick
结果：
5 - 修改单元格格式
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
#1 = 1 "Row
#2 = 1. "Column
SET PROPERTY OF lo_cell 'Value' = '1.23'.
SET PROPERTY OF lo_cell 'NumberFormat' = '0.00'.
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
#1 = 3 "Row
#2 = 1. "Column
SET PROPERTY OF lo_cell 'Value' = '02/01/2012'.
SET PROPERTY OF lo_cell 'NumberFormat' = 'm/d/yyyy'.
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
#1 = 5 "Row
#2 = 1. "Column
SET PROPERTY OF lo_cell 'NumberFormat' = '0.00'.
SET PROPERTY OF lo_cell 'Value' = '1/2'.
SET PROPERTY OF lo_cell 'NumberFormat' = '# ?/?'.
结果：
6 - 添加有效性验证
比如说，只允许2000.01和2010.01之间的日期，如果在这个范围之外，提示错误。
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
#1 = 1 "Row
#2 = 1. "Column
CALL METHOD OF lo_cell 'select'.
CALL METHOD OF lo_application 'selection' = lo_selection.
CALL METHOD OF lo_selection 'Validation' = lo_validation.
CALL METHOD OF lo_validation 'Add'
EXPORTING
#1 = 4 "Type = xlValidateDate
#2 = 1 "AlertStype = xlValidAlertStop
#3 = 1 "Operator = xlBetween
#4 = '1/1/2000' "Formula1
#5 = '1/1/2010'."Formula2
SET PROPERTY OF lo_validation 'ErrorMessage' = 'Enter a valid date'.
结果：
7 - 创建一个包含其它工作簿中的值的下拉菜单:
这里有一个在EXCEL里面创建的例子:
http://office.microsoft.com/en-us/excel-help/create-or-remove-a-drop-down-list-HP005202215.aspx
ABAP结果：
改善性能
如果你想要下载大量数据，会花费很多时间。为了提高性能，我们从abap复制数据到剪切板，然后把它粘贴到excel里面。
比较两种方式的运行时间（代码已折叠）：
Cell by Cell一个单元格一个单元格地写入，要花费约145秒
Copy-Paste使用复制粘贴的方法，花费的时间少于4秒！
有用的子程序
我已经创建了一个包含文件，其中含有多个有用的子程序。包含文件的代码放在本文的末尾。你可以测试报表程序，观察它们是如何运行的。
Test report: Example of use the include ZRIC_OLE2_UTILS
运行后，你可以下载到这样的一个excel：
Code of include ZRIC_OLE2_UTILS相关内容:
http://help.sap.com/printdocu/core/print46c/en/data/pdf/bcfesde6/bcfesde6.pdf
https://msdn.microsoft.com/en-us/library/office/ff840772.aspx
译者注：如果读者不知道某些属性的枚举值应如何设定，可以参考相关内容中的微软文档中的内容。
浙公网安备 33010602011771号