SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

Примеры работы с Excel через OLE2



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1639

PostPosted: Fri Jan 18, 2008 1:17 pm    Post subject: Примеры работы с Excel через OLE2 Reply with quote

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.

*=======================================================================
* Обработка ошибок чтения файла EXCEL
*=======================================================================

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
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.