Posted: Fri Jan 18, 2008 1:17 pm Post subject: Примеры работы с Excel через OLE2
Code:
*----------------------------------------------------------------------*
* INCLUDE Z__SAP2EXCEL *
*----------------------------------------------------------------------*
INCLUDE OLE2INCL.
DATA: APPLICATION TYPE OLE2_OBJECT,
WORKBOOK TYPE OLE2_OBJECT,
CELL TYPE OLE2_OBJECT,
SHEET TYPE OLE2_OBJECT,
FILEOPEN TYPE OLE2_OBJECT.
DATA: DIR_TRANS_PATH(2000) TYPE C,
EXCEL_XLS_PATH(2000) TYPE C,
FULL_PATH(2000) TYPE C,
EXCEL_SHEET_NAME(255) TYPE C.
*-----------------------------------------------------------------------
* Создание объекта EXCEL
*-----------------------------------------------------------------------
FORM CREATE_EXCEL_OBJECT.
CREATE OBJECT APPLICATION 'EXCEL.APPLICATION'.
IF SY-SUBRC NE 0.
MESSAGE ID 'CE' TYPE 'E' NUMBER '517'
WITH 'Невозможно создать объект EXCEL.APPLICATION'.
ENDIF.
CALL METHOD OF APPLICATION 'WORKBOOKS' = WORKBOOK.
IF SY-SUBRC NE 0.
MESSAGE ID 'CE' TYPE 'E' NUMBER '517'
WITH 'Невозможно инициализировать WORKBOOK'.
ENDIF.
PERFORM FIND_DIR_FULL.
CALL METHOD OF WORKBOOK 'OPEN' EXPORTING #1 = FULL_PATH.
IF SY-SUBRC NE 0.
MESSAGE ID 'CE' TYPE 'E' NUMBER '517'
WITH 'Невозможно открыть WORKBOOK'.
ENDIF.
ENDFORM.
*-----------------------------------------------------------------------
* Уничтожение объекта EXCEL
*-----------------------------------------------------------------------
FORM DELETE_EXCEL_OBJECT.
SET PROPERTY OF APPLICATION 'VISIBLE' = 1.
FREE OBJECT APPLICATION.
APPLICATION-HANDLE = -1.
ENDFORM.
*-----------------------------------------------------------------------
* Инициализация листа книги
*-----------------------------------------------------------------------
FORM OPEN_EXCEL_SHEET.
GET PROPERTY OF APPLICATION 'SHEETS' = SHEET
EXPORTING #1 = EXCEL_SHEET_NAME.
IF SY-SUBRC NE 0.
MESSAGE ID 'CE' TYPE 'E' NUMBER '517'
WITH 'Невозможно открыть лист'.
ENDIF.
CALL METHOD OF SHEET 'ACTIVATE'.
IF SY-SUBRC NE 0.
MESSAGE ID 'CE' TYPE 'E' NUMBER '517'
WITH 'Невозможно активировать лист'.
ENDIF.
ENDFORM.
*-----------------------------------------------------------------------
* Инициализация активированного листа книги
*-----------------------------------------------------------------------
FORM OPEN_EXCEL_ACTIVE_SHEET.
GET PROPERTY OF APPLICATION 'ACTIVESHEET' = SHEET.
IF SY-SUBRC NE 0.
MESSAGE ID 'CE' TYPE 'E' NUMBER '517'
WITH 'Невозможно открыть активный лист'.
ENDIF.
ENDFORM.
*-----------------------------------------------------------------------
* Определение каталога DIR_TRANS
*-----------------------------------------------------------------------
FORM FIND_DIR_TRANS.
DATA SEQ_IN LIKE RPRXXXXX-SEQ_IN.
CALL 'C_SAPGPARAM' ID 'NAME' FIELD 'DIR_TRANS'
ID 'VALUE' FIELD SEQ_IN.
DIR_TRANS_PATH = SEQ_IN.
SHIFT DIR_TRANS_PATH LEFT DELETING LEADING SPACE.
ENDFORM.
*-----------------------------------------------------------------------
* Определение каталога c XLS
*-----------------------------------------------------------------------
FORM FIND_DIR_FULL.
PERFORM FIND_DIR_TRANS.
SHIFT DIR_TRANS_PATH RIGHT DELETING TRAILING SPACE.
SHIFT DIR_TRANS_PATH LEFT DELETING LEADING SPACE.
CONCATENATE DIR_TRANS_PATH EXCEL_XLS_PATH INTO FULL_PATH.
ENDFORM.
FORM ERRORS.
IF SY-SUBRC NE 0.
WRITE: / 'Error in OLE call', SY-MSGLI.
EXIT.
ENDIF.
ENDFORM.
*-----------------------------------------------------------------------
* Выводит в ячейку строку
*-----------------------------------------------------------------------
FORM PUTC USING XCELL TYPE I
YCELL TYPE I
VAL TYPE C.
DATA CELL TYPE OLE2_OBJECT.
CALL METHOD OF SHEET 'CELLS' = CELL EXPORTING #1 = YCELL #2 = XCELL.
SET PROPERTY OF CELL 'VALUE' = VAL.
ENDFORM.
*-----------------------------------------------------------------------
* Выводит в ячейку число
*-----------------------------------------------------------------------
FORM PUTP USING XCELL TYPE I
YCELL TYPE I
VAL TYPE P.
DATA CELL TYPE OLE2_OBJECT.
CALL METHOD OF SHEET 'CELLS' = CELL EXPORTING #1 = YCELL #2 = XCELL.
SET PROPERTY OF CELL 'VALUE' = VAL.
ENDFORM.
*-----------------------------------------------------------------------
* Отменяет WRAP TEXT на диапазоне ячеек
*-----------------------------------------------------------------------
FORM NOWRAP USING ROW TYPE I
AREA TYPE I.
DATA: P1(300) TYPE C, P2(300) TYPE C, R1(300) TYPE C, R2(300) TYPE C,
USL(1000) TYPE C, LOCAL_AREA TYPE I.
DATA: RANGE TYPE OLE2_OBJECT, WRAPTEXT TYPE OLE2_OBJECT.
CLEAR P1.
MOVE ROW TO P1.
SHIFT P1 LEFT DELETING LEADING SPACE.
CONCATENATE 'A' P1 INTO R1.
CLEAR P2.
LOCAL_AREA = ROW + AREA.
MOVE LOCAL_AREA TO P2.
SHIFT P2 LEFT DELETING LEADING SPACE.
CONCATENATE 'Z' P2 INTO R2.
CLEAR P1.
CONCATENATE R1 ':' R2 INTO P1.
CALL METHOD OF SHEET 'RANGE' = RANGE
EXPORTING #1 = P1.
SET PROPERTY OF RANGE 'WRAPTEXT' = 'False'.
ENDFORM.
*-----------------------------------------------------------------------
* Удаляет часть сторк
*-----------------------------------------------------------------------
FORM KILLROW USING SHEET TYPE OLE2_OBJECT
ROW TYPE I
AREA TYPE I.
DATA: RANGE TYPE OLE2_OBJECT,
ROWS TYPE OLE2_OBJECT, TN TYPE I,
P1(30) TYPE C, P2(30) TYPE C.
MOVE ROW TO P1.
SHIFT P1 LEFT DELETING LEADING SPACE.
TN = ROW + AREA.
MOVE TN TO P2.
SHIFT P2 LEFT DELETING LEADING SPACE.
CONCATENATE P1 ':' P2 INTO P1.
GET PROPERTY OF SHEET 'ROWS' = ROWS EXPORTING
#1 = P1.
CALL METHOD OF ROWS 'DELETE'.
ENDFORM.
*-----------------------------------------------------------------------
* Вcтавляет в лист строку
*-----------------------------------------------------------------------
FORM INSERT USING YCELL TYPE I.
DATA: RANGE TYPE OLE2_OBJECT, FONT TYPE OLE2_OBJECT,
ROWS TYPE OLE2_OBJECT,
P1(30) TYPE C, P2(30) TYPE C.
GET PROPERTY OF SHEET 'ROWS' = ROWS EXPORTING
#1 = YCELL.
CALL METHOD OF ROWS 'INSERT'.
ENDFORM.
FORM SHOW_PROGRESS USING __TEXT.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING TEXT = __TEXT.
ENDFORM.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
