[ABAP] Use OLE to Manipulate EXCEL

透過 SAP GUI 的功能直接操作 EXCEL

---

首先定義 Object :

TYPE-POOLS: ole2.

* OLE objects Declarations
DATA: w_excel       TYPE ole2_object,
      w_workbooks   TYPE ole2_object,
      w_workbook    TYPE ole2_object,
      w_worksheets  TYPE ole2_object,
      w_worksheet   TYPE ole2_object,
      w_columns     TYPE ole2_object,
      w_column_ent  TYPE ole2_object,
      w_cell        TYPE ole2_object,
      w_int         TYPE ole2_object,
      w_range       TYPE ole2_object,
      w_font        TYPE ole2_object,
      w_window      TYPE ole2_object.

接著開啟 Excel :

IMG related tables & functions

與 IMG 相關的表格及功能模組

---

表格

SCUS_HIER                           Replacements table for hierarchies

TNODEIMG                            Node table for the new IMG
TNODEIMGR                           References for the new IMG

CUS_ACTOBJ                          Customizing Activity - Object List
CUS_IMGACH                          IMG Activities

功能模組

S_CUS_IMG_GET_REFERENCE_IMG_ID
S_CUS_IMG_SELECT_NODES

STREE_BROWSER_DYNP_HC
STREE_HIERARCHY_READ

STREE_STRUCTURE_READ_N_TOP_LEV
STREE_STRUCTURE_READ_TOP_NODE
STREE_STRUCTURE_READ_N_LEVEL

When you use ABAP to split a BIG5 string , how to make sure that you have no cut a word of BIG5 in half ?

當你用 ABAP 在分割或刪減包含有 BIG5 的字串時，如何確認你沒有把一個 BIG5 的字切成一半？

---

首先我們要知道 BIG5 的編號規則，BIG5 是 2 bytes code ，分為 High Byte 和 Low Byte:

*      ===========   ===========
*       High Byte     Low Byte
*      ===========   ===========
*       0xA1-0xF9     0x40-0x7E
*                        or
*                     0xA1-0xFE
*      ===========   ===========

依照其編碼規則我們可以發現 high byte 和 low byte 有重疊的部份就是 0xA1-0xF9 的區間，底下的程式假設將一個長字串縮減成 30 個字元，然後判斷最後一個字元是否有將 BIG5 的文字切成一半：

[Link] Remove of non-printable characters from string

移除無法列印的文字

---

這只能用在可以使用 regular expression 的 REPLACE 指令上 

replace all occurrences of regex '[^[:print:]]+(?!$)' in STRING with ' '.
replace all occurrences of regex '[^[:print:]]+$' in STRING with ''.

Link:
http://forums.sdn.sap.com/thread.jspa?threadID=1678318&tstart=0

[Link] Enhancement Framework

NetWeaver 2004s 之後的 Enhancement Framework，值得參考的入門文件：

---

INTRODUCING THE ENHANCEMENT FRAMEWORK: 
THE COOL NEW WAY TO ENHANCE YOUR ABAP SYSTEMS 
http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/bb54c452-0801-0010-0e84-a653307fc6fc 

[Link] Change Material Document DATA Using BADI: MB_DOCUMENT_BADI

BADI: MB_DOCUMENT_BADI 

---

MB_DOCUMENT_BADI 並沒有 Exporting / Changing 任何物料文件的資料結構，但是可以用 Field-Symbols 來修改到其中的資料：

FIELD-SYMBOLS: <ERFMG>   TYPE any,
 
                            <ERFMG1> TYPE any.
 
ASSIGN '(SAPMM07M)MSEG-ERFMG' TO <ERFMG>.
 
ASSIGN (<erfmg>) TO <erfmg1>. 
 

 
<ERFMG1> = '0.001'.

Source URL: http://forums.sdn.sap.com/thread.jspa?threadID=1688671

Reference:
1. http://scn.sap.com/thread/367073 ((SAPMV45A)XVBAP : Table XVBAP)

Getting Sales order -Schedule line field , Delivered Quantity ( VSMNG )

I need to use the Delivered Quantity present in a sales order at schedule line level.

Sheeba Bhaskaran

Posts:	252
Registered:	2/27/07
Forum Points:	302
Re:	Getting Sales order -Schedule line field , Delivered Quantity ( VSMNG ) ??
Posted:	Jan 13, 2009 10:38 AM
Content:	Hi. You can use the function module RV_SCHEDULE_CHECK_DELIVERIES to get the delivered quanity corresponding to schedule line item in sales order. Please refer below code: ie3mdpslx-delnr:   is sales order numer ie3mdpslx-delps:   is line item. internal table tl_da_vbep has got field VSMNG which is the delievred quantity for each line item SELECT * FROM vbfa INTO CORRESPONDING FIELDS OF TABLE tl_xvbfa WHERE vbelv = ie3mdpslx-delnr AND posnv = ie3mdpslx-delps AND vbtyp_v = 'C'. SELECT * FROM vbup INTO CORRESPONDING FIELDS OF TABLE tl_xvbup WHERE vbeln = ie3mdpslx-delnr AND posnr = ie3mdpslx-delps. SELECT * FROM vbep INTO CORRESPONDING FIELDS OF TABLE tl_da_vbep WHERE vbeln = ie3mdpslx-delnr AND posnr = ie3mdpslx-delps. SELECT * FROM vbap INTO CORRESPONDING FIELDS OF TABLE tl_xvbap WHERE vbeln = ie3mdpslx-delnr AND posnr = ie3mdpslx-delps. CALL FUNCTION 'RV_SCHEDULE_CHECK_DELIVERIES' EXPORTING fbeleg = ie3mdpslx-delnr fposnr = ie3mdpslx-delps TABLES fvbfa = tl_xvbfa fvbup = tl_xvbup fxvbep = tl_da_vbep fvbap = tl_xvbap EXCEPTIONS fehler_bei_lesen_fvbup = 1 fehler_bei_lesen_fxvbep = 2 OTHERS = 3.

SE16N active editing functional

First you need input table name, and press Enter.

Next, type "&SAP_EDIT" in transaction code box, and press Enter.

Then you well get an message: SAP editing function is activated.

(In NEW version ECC 6.0, the "&SAP_EDIT" can't activate editing function,

u can use UASE16N to do so.) 

Archive Tips

Archived Sale Document Read

T-Codes

SARA

Archive Administration

SARE

Archive Explorer

SARI

Archive Information System

SARJ

Archive Retrieval Configurator

DB15

Data Archiving: Database Tables

---

Functions

AS_API_READ

Reading of Data from the Archive Information System

ARCHIVE_OPEN_FOR_READ

Open an Existing Archive File for Reading

ARCHIVE_READ_OBJECT

Read a Data Object from an Archive File

ARCHIVE_GET_NEXT_RECORD

Sequential Read of the Records from a Data Object

ARCHIVE_CLOSE_FILE

Open Archive Files Will Be Closed

ARCHIVE_GET_TABLE

Read Records by Structure from the Current Data

KASH_ARCHIVE_DATA_FILE_SHOW

Display tools for archive

---

Tables

ADMI_FILES

Archive Files of Archive Sessions

ARCH_IDX

Index table for data object selection

[Func] Func Group - TXXL

EXCEL - list viewer interface

Function Module:

• XXL_SIMPLE_API: 單個情況 Excel 清單查看器的省略接口
• XXL_FULL_API: Excel 清單瀏覽器的完整界面

---

XXL_SIMPLE_API

Example code:

1. XXLSTEST
2. DATA_Export

*&---------------------------------------------------------------------*
*&      Form  data_export
*&---------------------------------------------------------------------*
*       資料匯出至 Excel
*----------------------------------------------------------------------*
FORM DATA_EXPORT .

DATA: XMPL_HEADER LIKE  GXXLT_P-TEXT
                  VALUE '交貨明細表'.

DATA BEGIN OF XMPLT_O OCCURS 1.
        INCLUDE STRUCTURE GXXLT_O.
DATA END OF XMPLT_O.

DATA BEGIN OF XMPLT_P OCCURS 1.
        INCLUDE STRUCTURE GXXLT_P.
DATA END OF XMPLT_P.

DATA BEGIN OF XMPLT_V OCCURS 1.
        INCLUDE STRUCTURE GXXLT_V.
DATA END OF XMPLT_V.

DATA: BEGIN OF XMPLT_D OCCURS 60,
        VBELN    LIKE LIPS-VBELN,
        POSNR    LIKE LIPS-POSNR,
        KUNNR    LIKE LIKP-KUNNR,
        NAME1    LIKE KNA1-NAME1,
        NAME1_SH LIKE KNA1-NAME1,
        MATNR    LIKE LIPS-MATNR,
        VGBEL    LIKE LIPS-VGBEL,
        KDMAT    LIKE LIPS-KDMAT,
        LFIMG    LIKE LIPS-LFIMG,
        VRKME    LIKE LIPS-VRKME,
        LFDAT    LIKE LIKP-LFDAT,
      END OF XMPLT_D.

* 標題列
CLEAR XMPLT_V.
XMPLT_V-COL_NO     = XMPLT_V-COL_NO + 1.
XMPLT_V-COL_NAME   = '交貨'.
APPEND XMPLT_V.
XMPLT_V-COL_NO     = XMPLT_V-COL_NO + 1.
XMPLT_V-COL_NAME   = '項目'.
APPEND XMPLT_V.
XMPLT_V-COL_NO     = XMPLT_V-COL_NO + 1.
XMPLT_V-COL_NAME   = '收貨人'.
APPEND XMPLT_V.
XMPLT_V-COL_NO     = XMPLT_V-COL_NO + 1.
XMPLT_V-COL_NAME   = '客戶名稱'.
APPEND XMPLT_V.
XMPLT_V-COL_NO     = XMPLT_V-COL_NO + 1.
XMPLT_V-COL_NAME   = '出貨對象名稱'.
APPEND XMPLT_V.
XMPLT_V-COL_NO     = XMPLT_V-COL_NO + 1.
XMPLT_V-COL_NAME   = '物料'.
APPEND XMPLT_V.
XMPLT_V-COL_NO     = XMPLT_V-COL_NO + 1.
XMPLT_V-COL_NAME   = '採購文件'.
APPEND XMPLT_V.
XMPLT_V-COL_NO     = XMPLT_V-COL_NO + 1.
XMPLT_V-COL_NAME   = '客戶物料號碼'.
APPEND XMPLT_V.
XMPLT_V-COL_NO     = XMPLT_V-COL_NO + 1.
XMPLT_V-COL_NAME   = '交貨數量'.
APPEND XMPLT_V.
XMPLT_V-COL_NO     = XMPLT_V-COL_NO + 1.
XMPLT_V-COL_NAME   = 'SU'.
APPEND XMPLT_V.
XMPLT_V-COL_NO     = XMPLT_V-COL_NO + 1.
XMPLT_V-COL_NAME   = '發貨日期'.
APPEND XMPLT_V.

* 資料轉移
LOOP AT GT_ITAB.
  MOVE-CORRESPONDING GT_ITAB TO XMPLT_D.
  APPEND XMPLT_D.
ENDLOOP.
SORT XMPLT_D BY VBELN POSNR.
*
CALL FUNCTION 'XXL_SIMPLE_API'
   EXPORTING
        N_KEY_COLS         = 4
        FILENAME           = 'XMPL0002'
        HEADER             = XMPL_HEADER
   TABLES
        DATA               = XMPLT_D
        ONLINE_TEXT        = XMPLT_O
        PRINT_TEXT         = XMPLT_P
        COL_TEXT           = XMPLT_V.

ENDFORM.                    " data_export

XXL_FULL_API

Example code: XXLFTEST

CALL FUNCTION 'XXL_FULL_API'
   EXPORTING
        N_VRT_KEYS         = 3     " Of the 9 columns in the list
        N_ATT_COLS         = 6     " \ object, 3 are key columns
        N_HRZ_KEYS         = 3     " Header above the non-key-columns
                                   " \ forms a three-level-hierarchy
        DATA_STARTING_AT   = 5     " Only 50 rows out of 60 are
        DATA_ENDING_AT     = 54    " \ really exported
        FILENAME           = 'XMPL0001'
        HEADER_1           = XMPL_HEADER1
        HEADER_2           = XMPL_HEADER2
        SEMA_TYPE          = 'X'    " C-Columns are converted if SEMA
                                    " \ definitions require this
        NO_DIALOG          = SPACE  " XXL itself prompts for FILENAME
   TABLES
        VKEY               = XMPLT_V
        HKEY               = XMPLT_H
        DATA               = XMPLT_D
        SEMA               = XMPLT_S
        ONLINE_TEXT        = XMPLT_O
        PRINT_TEXT         = XMPLT_P
   EXCEPTIONS
        FILE_OPEN_ERROR    = 71
        FILE_WRITE_ERROR   = 72
        INV_WINSYS         = 73
        INV_XXL            = 74
        CANCELLED_BY_USER  = 75
        OTHERS             = 99.

[Func] PUT SIGN IN FRONT

Function: CLOI_PUT_SIGN_IN_FRONT ，將負號放到數字前面

CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT'
  CHANGING
    VALUE         = var_char

注意 ：如果原先負號的後面還有其他的字元(例如 %)，經過處理後會遺漏掉。

[Code] Put Data into Range Object with Macro

Put data into ranges table.

首先是先定義好資料:

TABLES: tvfk.
RANGES: gr_fkart FOR vbrk-fkart.

最重要的 萬用巨集

* -------------
* Define Macro
* -------------
DEFINE put_ranges.
  &1-sign   = &2.
  &1-option = &3.
  &1-low    = &4.
  &1-high   = &5.
  APPEND &1.
END-OF-DEFINITION.

或者是這個「假猴」的 :p

DEFINE put_ranges.
  FIELD-SYMBOLS <PT> LIKE &1.
  ASSIGN &1 TO <PT>.
  <PT>-sign   = &2.
  <PT>-option = &3.
  <PT>-low    = &4.
  <PT>-high   = &5.
  APPEND <PT> TO &1.
END-OF-DEFINITION.

然後是運用的方式:

SELECT fkarts INTO tvfk-fkarts
    FROM tvfk
   WHERE kappl = 'V3'.
  put_ranges gr_fkart 'E' 'EQ' tvfk-fkarts ''.
ENDSELECT.

Distinct vs Single

Select Distinct vs Select Single

SINGLE

The result of the selection should be a single entry. If it is not possible to identify a unique entry, the system uses the first line of the selection. If you use the FOR UPDATE addition, the selected entry is protected against parallel updates from other transactions until the next database commit (see LUW and database lock mechanism). If the database system identifies a deadlock, a runtime error occurs.

DISTINCT

Duplicate entries in the result set are automatically deleted.

[Link] LET US ABAP

SAP AND ABAP TIPS AND FACTS

The link address is: http://abaplovers.blogspot.com/

[Link] sapcommander

Filecommander (Norton-Commander-Clone) for SAP-Systems (ABAP)

The link address is: http://code.google.com/p/sapcommander/

[Link] abap-report-wizard

Project to support future developments of the ABAP Report Wizard

The link address is: http://code.google.com/p/abap-report-wizard/

[Link] SAPlink

SAPlink is an open source project that aims to make it easier to share ABAP developments between programmers. It provides the ability to easily distribute and package custom objects.

The link address is: https://github.com/sapmentors/SAPlink