最近知道了OLE这个概念, 打算学习学习
新建excel, 改变字体颜色, 填充单元格颜色; 下面两段代码效果一样
代码1
PARAMETERS: pr_xls(64) TYPE c DEFAULT 'D:\ZEROTEST.XLS'.INCLUDE ole2incl.DATA: excelTYPE ole2_object,workbook TYPE ole2_object,sheetTYPE ole2_object,cellTYPE ole2_object,row TYPE ole2_object,fontTYPE ole2_object,int TYPE ole2_object.DATA: lv_rc TYPE c.CREATE OBJECT excel 'EXCEL.APPLICATION'.GET PROPERTY OF excel 'WORKBOOKS' = workbook.CALL METHOD OF workbook 'ADD'.CALL METHOD OF excel 'Worksheets' = sheetEXPORTING #1 = 1.CALL METHOD OF sheet 'Activate'. " should be active otherwise can't be savedSET PROPERTY OF sheet 'Name' = 'Sheet1'.* Select cell and modify font colorCALL METHOD OF sheet 'CELLS' = cellEXPORTING #1 = 2 #2 = 3.CALL METHOD OF cell 'FONT' = font.SET PROPERTY OF font 'SIZE' = '24'.SET PROPERTY OF cell 'VALUE' = 'zero' .SET PROPERTY OF font 'COLORINDEX' = '4'.FREE OBJECT CELL.* Fill the cell colorCALL METHOD OF sheet 'CELLS' = cellEXPORTING #1 = 2 #2 = 4.CALL METHOD OF cell 'INTERIOR' = int.SET PROPERTY OF int 'ColorIndex' = '4'.* release and exit Excel.CALL METHOD OF sheet 'SAVEAS'EXPORTING#1 = pr_xls.CALL METHOD OF excel 'QUIT'.* Free all objectsFREE OBJECT font.FREE OBJECT cell.FREE OBJECT sheet.FREE OBJECT workbook.FREE OBJECT excel.excel-handle = -1.FREE OBJECT row.
代码2
INCLUDE ole2incl.DATA: excel TYPE ole2_object,workbookTYPE ole2_object,sheet TYPE ole2_object,cellTYPE ole2_object,fontTYPE ole2_object,int TYPE ole2_object.PARAMETERS p_file TYPE string DEFAULT 'D:\ZEROTEST.XLS'.* Run ExcelCREATE OBJECT excel 'EXCEL.APPLICATION'.* get reference to workbook list in application* it is empty right nowCALL METHOD OF excel 'WORKBOOKS' = workbook.CALL METHOD OF workbook 'ADD'.* get refer to active workbookGET PROPERTY OF excel 'ACTIVEWORKBOOK' = workbook.* Select cell and modify font colorCALL METHOD OF excel 'CELLS' = cellEXPORTING #1 = 2 #2 = 3.CALL METHOD OF cell 'FONT' = font.SET PROPERTY OF font 'SIZE' = '24'.SET PROPERTY OF cell 'VALUE' = 'zero' .SET PROPERTY OF font 'COLORINDEX' = '4'.FREE OBJECT CELL.* Fill the cell colorCALL METHOD OF excel 'CELLS' = cellEXPORTING #1 = 2 #2 = 4.CALL METHOD OF cell 'INTERIOR' = int.SET PROPERTY OF int 'COLORINDEX' = '4'.* save workbookCALL METHOD OF workbook 'SAVEAS'EXPORTING#1 = p_file.* quit excelCALL METHOD OF excel 'QUIT'.* Free all objectsFREE OBJECT font.FREE OBJECT cell.FREE OBJECT workbook.FREE OBJECT excel.excel-handle = -1.
打开excel, insert行, 填写数据
PARAMETERS: pr_xls(64) TYPE c DEFAULT 'D:\ZEROTEST.XLS'.INCLUDE ole2incl.TYPES: BEGIN OF ty_person,name(8) TYPE c,location(2) TYPE c,END OF ty_person.DATA: excelTYPE ole2_object,workbook TYPE ole2_object,sheetTYPE ole2_object,cellTYPE ole2_object,row TYPE ole2_object.DATA: lt_person TYPE STANDARD TABLE OF ty_person,lw_person TYPE ty_person.CLEAR lw_person.lw_person-name = 'Palm'.lw_person-location = '31F'.APPEND lw_person TO lt_person.CLEAR lw_person.lw_person-name = 'Luke'.lw_person-location = '13F'.APPEND lw_person TO lt_person.CLEAR lw_person.lw_person-name = 'Zero'.lw_person-location = '14F'.APPEND lw_person TO lt_person.* Create an Excel object and start Excel.CREATE OBJECT excel 'EXCEL.APPLICATION'.IF sy-subrc <> 0.MESSAGE 'The excel object can''t be created' TYPE 'I'.ENDIF.* Create an Excel workbook Object.CALL METHOD OF excel 'WORKBOOKS' = workbook .* Transfer the header line to Excel.CALL METHOD OF workbook 'OPEN' EXPORTING #1 = pr_xls.CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'A1'.SET PROPERTY OF cell 'VALUE' = 'Name' .CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'B1'.SET PROPERTY OF cell 'VALUE' = 'Location' .LOOP AT lt_person INTO lw_person.CALL METHOD OF excel 'ROWS' = row EXPORTING #1 = '2' .CALL METHOD OF row 'INSERT'.CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'A2' .SET PROPERTY OF cell 'VALUE' = lw_person-name.CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'B2' .SET PROPERTY OF cell 'VALUE' = lw_person-location.ENDLOOP.* release and exit Excel.CALL METHOD OF excel 'SAVE'.CALL METHOD OF excel 'QUIT'.* Free all objectsFREE OBJECT cell.FREE OBJECT workbook.FREE OBJECT excel.excel-handle = -1.FREE OBJECT row.
当我写完这段代码后, 发现弹出一个框a file named 'resume.xlw' already exists.对Workbook的save会同时保存工作区, 默认保存在My document里, 第二次运行这个程序，对话框就会弹出来, 而对于Active workbook的save就不会保存工作区.
这个是excel的工作区(workspace), 它的作用是保存Excel编辑的布局，比如我打开了两个Excel文件, zero1.xls和zero2.xls, 我可以把布局保存成下图这样. 下次我们打开文件'resume.xlw', 就会自动打开这两个文件并且恢复这个布局.
下面是解决方法, 改了两行代码
PARAMETERS: pr_xls(64) TYPE c DEFAULT 'D:\ZEROTEST.XLS'.INCLUDE ole2incl.TYPES: BEGIN OF ty_person,name(8) TYPE c,location(2) TYPE c,END OF ty_person.DATA: excelTYPE ole2_object,workbook TYPE ole2_object,sheetTYPE ole2_object,cellTYPE ole2_object,row TYPE ole2_object.DATA: lt_person TYPE STANDARD TABLE OF ty_person,lw_person TYPE ty_person.CLEAR lw_person.lw_person-name = 'Palm'.lw_person-location = '31F'.APPEND lw_person TO lt_person.CLEAR lw_person.lw_person-name = 'Luke'.lw_person-location = '13F'.APPEND lw_person TO lt_person.CLEAR lw_person.lw_person-name = 'Zero'.lw_person-location = '14F'.APPEND lw_person TO lt_person.* Create an Excel object and start Excel.CREATE OBJECT excel 'EXCEL.APPLICATION'.IF sy-subrc <> 0.MESSAGE 'The excel object can''t be created' TYPE 'I'.ENDIF.* Create an Excel workbook Object.CALL METHOD OF excel 'WORKBOOKS' = workbook .* Transfer the header line to Excel.CALL METHOD OF workbook 'OPEN' EXPORTING #1 = pr_xls.CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'A1'.SET PROPERTY OF cell 'VALUE' = 'Name' .CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'B1'.SET PROPERTY OF cell 'VALUE' = 'Location' .LOOP AT lt_person INTO lw_person.CALL METHOD OF excel 'ROWS' = row EXPORTING #1 = '2' .CALL METHOD OF row 'INSERT'.CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'A2' .SET PROPERTY OF cell 'VALUE' = lw_person-name.CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'B2' .SET PROPERTY OF cell 'VALUE' = lw_person-location.ENDLOOP.GET PROPERTY OF excel 'ACTIVEWORKBOOK' = workbook.* release and exit Excel.CALL METHOD OF workbook 'SAVE'.CALL METHOD OF excel 'QUIT'.* Free all objectsFREE OBJECT cell.FREE OBJECT workbook.FREE OBJECT excel.excel-handle = -1.FREE OBJECT row.
如果觉得《OLE操作EXCEL》对你有帮助，请点赞、收藏，并留下你的观点哦！
