OLE 操作Excel 詳解(轉)
摘自 http://blog.chinaunix.net/u2/62750/showa…
使用Excel模板進行報表的開發.
今年搞的Excel比較多,總結了一下,相信常用的操作包含的差不多了。
可以首先定義一個無內容的Excel報表模板檔案.
通過Tcode SMW0 上傳至SAP資料庫中備用.(注: Web物件應該選擇’WebRFC 應用程式的二進位制資料’)
開發程式:
在程式中需要首先匯入下面兩項.
INCLUDE ole2incl. “ 此項根據需要匯入不同的物件類
INCLUDE officeintegrationinclude.
在INITIALIZATION 中建立檔案物件與連結伺服器.
參考http://help.sap.com/saphelp_40b/helpdata/en/e9/0be7ed408e11d1893b0000e8323c4f/content.htm
選擇需要的報表資料.
檢查目標檔案是否已經存在,若存在將其刪除.
使用FUNCTION ‘SAP_OI_LOAD_MIME_DATA’ 從SAP資料庫中得到報表模板資料.
使用METHOD factory->get_document_proxy建立文件例項.
使用METHOD document->play_document_from_table 用報表模板資料填入文件內容.
使用METHOD document->save_copy_as 將建立的文件另存為本地檔案.
關閉釋放文件物件:
METHOD document->is_destroyed
METHOD document->close_document
METHOD document->release_document
FREE document.
關閉釋放連線伺服器:
METHOD link_server->stop_link_server
FREE link_server.
關閉釋放代理物件:
METHOD factory->stop_factory
FREE factory.
下面開始處理儲存到本地的報表模板,向其填寫具體資料內容.
CREATE OBJECT h_excel ‘EXCEL.APPLICATION’. “ 新建OLE物件
SET PROPERTY OF h_excel ‘Visible’ = 0. “ 定義其不可見
CALL METHOD OF h_excel ‘Workbooks’ = h_mapl. “ 得到活動excel物件
CALL METHOD OF h_mapl ‘Open’ “ 開啟此活動excel
EXPORTING
#1 = p_file. “ 本地模板檔案路徑
CALL METHOD OF h_excel ‘WORKSHEETS’ = H_SHEET. “ 得到活動的worksheet
如果需要生成多張報表則需要建立多個Sheet .
首先判斷相應名字的sheet是否已經存在:
GET PROPERTY OF WORKSHEETS ‘COUNT’ = SHEETCOUNT. “ 得到sheet數量
DO SHEETCOUNT TIMES. “ 迴圈判斷sheetname是否已經存在,若已經存在則不再建立
I = I + 1.
CALL METHOD OF EXCEL ‘WORKSHEETS’ = TMPSHEET
EXPORTING #1 = I.
GET PROPERTY OF TMPSHEET ‘NAME’ = TMPNAME.
IF TMPNAME = SHEETNAME.
EXISTFLAG = 1.
EXIT.
ENDIF.
ENDDO.
若sheetname不存在則建立
IF EXISTFLAG = 0.
CALL METHOD OF EXCEL ‘WORKSHEETS’ = MODELSHEET “ 第一個sheet
EXPORTING #1 = ‘Sheet1′.
* EXPORTING #1 = ‘模板’.
PERFORM. ERR_HDL.
CALL METHOD OF EXCEL ‘WORKSHEETS’ = TMPSHEET “ 最後一個sheet
EXPORTING #1 = SHEETCOUNT.
PERFORM. ERR_HDL.
CALL METHOD OF MODELSHEET ‘COPY’ “ Copy一個新的sheet
EXPORTING #1 = TMPSHEET.
PERFORM. ERR_HDL.
GET PROPERTY OF WORKSHEETS ‘COUNT’ = NEWSHEETCOUNT. “ 重新得到Sheet數量
PERFORM. ERR_HDL.
IF NEWSHEETCOUNT > SHEETCOUNT. “ 判斷是否建立sheet成功
CALL METHOD OF EXCEL ‘WORKSHEETS’ = NEWSHEET “ 如果建立成功則改Sheet的名字
EXPORTING #1 = SHEETCOUNT.
PERFORM. ERR_HDL.
SET PROPERTY OF NEWSHEET ‘NAME’ = SHEETNAME. “注:此處修改的是倒數第二個sheet
PERFORM. ERR_HDL.
* SET PROPERTY OF NEWSHEET ‘SCENARIOS’ = 0.
PERFORM. ERR_HDL.
ENDIF.
ENDIF.
將所有的sheet建立完畢後開始逐一向每個sheet新增報表內容.
CALL METHOD OF h_excel ‘WORKSHEETS’ = h_sheet
EXPORTING
#1 = ‘Sheet1′. “ 首先將第一個sheet也改名字
CALL METHOD OF h_sheet ‘ACTIVATE’.
CALL METHOD OF h_excel ‘ACTIVEWINDOW’ = activewindow. “ 得到活動視窗物件
SET PROPERTY OF activewindow ‘DISPLAYGRIDLINES’ = 0. “ 隱藏格線(虛線)
SET PROPERTY OF h_sheet ‘NAME’ = sheet_name. “ 修改sheet name
LOOP AT TAB_ALL.
CLEAR sheet_name.
sheet_name+0(10) = TAB_ALL-NAME.
sheet_name+10(1) = ‘-’.
sheet_name+11(8) = TAB_ALL-pernr.
CALL METHOD OF h_excel ‘WORKSHEETS’ = h_sheet
EXPORTING
#1 = sheet_name.
CALL METHOD OF h_sheet ‘ACTIVATE’. “ 逐個sheet啟用
PERFORM. fill_cell USING 2 3 1 tab_all-name. “ 向各個欄位新增數值
PERFORM. fill_cell USING 2 5 1 tab_all-xb. “ 向各個欄位新增數值
… … … …
… … … …
ENDLOOP.
SET PROPERTY OF h_excel ‘Visible’ = 1. “ 取消隱藏
CALL METHOD OF h_sheet ‘CLOSE’. “ 關閉
FREE OBJECT h_excel. “ 釋放
FREE OBJECT h_mapl. “ 釋放
FREE OBJECT h_sheet. “ 釋放
FREE OBJECT activewindow.
呼叫巨集(帶引數)
CALL METHOD OF excel ‘RUN’ EXPORTING #1 = ‘ZMACRO1′
#2 = param1. “ 帶引數
根據列數(1,2,3… …)換算Excel列名(a,b,c… …)
CALL FUNCTION ‘ZHRIS_GET_EXCEL_COLUMN’
EXPORTING
p_column = l_int
IMPORTING
f_column = col.
FUNCTION ZHRIS_GET_EXCEL_COLUMN.
*”———————————————————————-
*”*”Local interface:
*” IMPORTING
*” REFERENCE(P_COLUMN) TYPE I
*” EXPORTING
*” REFERENCE(F_COLUMN) TYPE C
*”———————————————————————-
data : l_col type string .
data : l_cyc type i .
data : l_mod type i .
data : c1, c2 .
l_col = ‘abcdefghijklmnopqrstuvwxyz’ .
if p_column >= 1 .
l_cyc = p_column div 26 .
l_mod = p_column mod 26 .
if l_cyc > 1 .
* l_cyc = l_cyc - 1 .
endif.
if l_mod > 1 .
l_mod = l_mod - 1 .
endif.
if l_mod = 0 .
l_cyc = l_cyc - 1 .
l_mod = 25.
endif.
if l_cyc >= 1 .
l_cyc = l_cyc - 1 .
c1 = l_col+l_cyc(1).
endif.
if l_mod >= 1 .
if l_mod = 1 .
l_mod = l_mod - 1 .
endif.
c2 = l_col+l_mod(1).
endif.
concatenate c1 c2 into F_COLUMN .
condense F_COLUMN no-gaps .
endif.
ENDFUNCTION.
選擇Excel中某個區域
CALL METHOD OF h_sheet ‘range’ = range
EXPORTING
#1 = ‘a3′
#2 = ‘b10′.
CALL METHOD OF range ‘Select’ NO FLUSH. “ 選擇
GET PROPERTY OF range ‘borders’ = h_borders no flush. “ 加邊框
* SET PROPERTY OF h_borders ‘weight’ = ‘2′ no flush.
SET PROPERTY OF h_borders ‘linestyle’ = ‘1′ no flush. “ 框線格式
CALL METHOD OF excel ‘Columns’ = column “ 選定列
EXPORTING
#1 = 1.
SET PROPERTY OF column ‘ColumnWidth’ = 3. “ 定義列寬
SET PROPERTY OF column ‘rowheight’ = 30. “ 定義行高
CALL METHOD OF range ‘ClearContents’. “ 清空內容
SET PROPERTY OF range ‘MergeCells’ = 1. “ 合併單元格
SET PROPERTY OF range ‘HorizontalAlignment’ = 3. “ 對齊方式-縱向
SET PROPERTY OF range ‘ShrinkToFit’ = 0 . “ 取消自動縮小字型
CALL METHOD OF h_mapl ‘SAVEAS’ “ 儲存Excel
EXPORTING
#1 = ‘C:\工資明細表.xls’
#2 = 1.
FREE OBJECT excel.
FREE OBJECT h_sheet.
*********** 調整Sheet 之間的順序 *****************
sheet_name+0(9) = ‘通知書’.
sheet_name+9(1) = ‘-’.
sheet_name+10(2) = ‘01′.
CALL METHOD OF h_excel ‘WORKSHEETS’ = h_sheet
EXPORTING
#1 = sheet_name.
sheet_name1+0(9) = ‘通知書’.
sheet_name1+9(1) = ‘-’.
sheet_name1+10(2) = ‘02′.
CALL METHOD OF h_excel ‘WORKSHEETS’ = h_move
EXPORTING
#1 = sheet_name1.
CALL METHOD OF h_sheet ‘Move’ EXPORTING #1 = h_move.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/16794144/viewspace-750586/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- OLE程式開發利用(開發EXCEL) (轉)Excel
- C#操作Excel詳解C#Excel
- PHP對redis操作詳解[轉]PHPRedis
- excel轉json操作ExcelJSON
- java 讀取excel 檔案 Unable to recognize OLE stream 錯誤JavaExcel
- maatwebsite/excel3.1 匯入Excel詳解WebExcel
- 如何在Form中正確使用Ole2訪問Excel,將資料輸入ExcelORMExcel
- 【轉】javascript日期操作詳解(指令碼之家整理)JavaScript指令碼
- OLE SDK——框架介面(二):IOleInPlaceFrame::SetStatusText (轉)框架
- OLE SDK——框架介面(三):IOleInPlaceFrame::RemoveMenus (轉)框架REM
- OLE SDK——框架介面(五):IOleInPlaceFrame::SetMenu (轉)框架
- 轉java操作excel匯入匯出JavaExcel
- 詳解 RestTemplate 操作REST
- Excel公式大全詳解 excel表格計算公式大全Excel公式
- dotnet OpenXML 讀取 PPT 內嵌 ole 格式 Excel 表格的資訊XMLExcel
- 常用Excel圖表型別詳解Excel型別
- Oracle資料操作和控制語言詳解(轉)Oracle
- OLE SDK——框架介面(四):IOleInPlaceFrame::EnableModeless (轉)框架
- OLE SDK——框架介面(六):IOleInPlaceFrame::InsertMenus (轉)框架
- WPS騷操作之嵌入ole物件繫結木馬物件
- PHP操作xml詳解PHPXML
- 操作符詳解
- javascript操作iframe詳解JavaScript
- IO流操作詳解
- memcached 命令操作詳解
- MongoDB 基本操作詳解MongoDB
- JS 操作Cookie詳解JSCookie
- 詳解Windows非法操作的具體含義(轉)Windows
- EXCEL操作公式Excel公式
- java操作excelJavaExcel
- 一組有用的操作Excel的函式 (轉)Excel函式
- VB千里行-操作Word與Excel (轉)Excel
- python操作Redis詳解PythonRedis
- Scala檔案操作詳解
- java 操作符詳解Java
- Java 8 聚合操作詳解Java
- git 遠端操作詳解Git
- SAP 替代物料操作詳解