2015年9月17日 星期四

ABAP/4 : How to use DOI to export internal table to EXCEL and set cell to string

User 在使用報表時，通常都希望系統能直接將結果導出成 Excel，以方便後續報編的編制，通常會使用 GUI_Download 產生以 Tab 分隔的檔案，但問題是產生的只是純文字檔，會造成文字檔轉 Excel 很容易發生 00001 -> 1 的情況



這種情況則可透過 DOI (Desktop Office Integration) 產生 Excel，將 Internal Table 中的值匯出到 Excel，並且透過 i_oi_spreadsheet-> set_format_string  的功能把 cell format 成 String


CALL METHOD i_oi_spreadsheet-> set_format_string
  EXPORTING
    rangename = 'range name'
    formatstring = '@'
  IMPORTING
    error = iref_error.


先透過 SE11 建立 Structure，設定 F01 ~ F60，表示目前產出的 Excel 可以 60 欄，此 Structure 主要是用來使用於 Method : i_oi_spreadsheet->insert_one_table 的 DDIC_NAME 參數用，即輸出 Excel 的總欄位數共 F60 欄

以下語法用於將整個 Internal Table 一次倒入 Excel 

CALL METHOD iref_spreadsheet->insert_one_table
  EXPORTING
    ddic_name  = 'ZSFIEXCEL'    "Structure
    data_table = ITAB[]         "Internal table
    rangename  = 'TEST'         "Insert 的 Range
    wholetable = 'X'            "Insert 整個 Table
  IMPORTING
    error      = iref_error.

SAMPLE CODE 

REPORT Z_DOI_EXCEL_FORMAT_STRING.

DATA:
  oref_container   TYPE REF TO cl_gui_custom_container,
  iref_control     TYPE REF TO i_oi_container_control,
  iref_document    TYPE REF TO i_oi_document_proxy,
  iref_spreadsheet TYPE REF TO i_oi_spreadsheet,
  iref_error       TYPE REF TO i_oi_error.
DATA: BEGIN OF ITAB OCCURS 0.
        INCLUDE STRUCTURE ZSFIEXCEL.
DATA: END OF ITAB.
DATA: L_ROW TYPE I VALUE 2.
DATA: FILENAME TYPE CHAR256 VALUE 'D:\TEST.xlsx'.
DATA: wa_range TYPE SOI_RANGE_ITEM,
      i_range TYPE SOI_RANGE_LIST.

"產生要輸出 Excel 的內容
ITAB-F01 = 'AAAA'.
ITAB-F02 = 'BBBB'.
ITAB-F03 = 'CCCC'.
APPEND ITAB. CLEAR ITAB.
ITAB-F01 = '00000111'.
ITAB-F02 = '00000222'.
ITAB-F03 = '00000333'.
APPEND ITAB. CLEAR ITAB.

"建立 DOI Container
CALL METHOD c_oi_container_control_creator=>get_container_control
  IMPORTING
    control = iref_control
    error   = iref_error.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
ENDIF.

CREATE OBJECT oref_container
  EXPORTING
    container_name              = 'CONT'
  EXCEPTIONS
    cntl_error                  = 1
    cntl_system_error           = 2
    create_error                = 3
    lifetime_error              = 4
    lifetime_dynpro_dynpro_link = 5
    OTHERS                      = 6.

IF sy-subrc <> 0.
  MESSAGE e001(00) WITH 'Error while creating container'.
ENDIF.

"建立 Excel 的 Container
CALL METHOD iref_control->init_control
  EXPORTING
    inplace_enabled      = 'X'
    r3_application_name  = 'EXCEL CONTAINER'
    parent               = oref_container
  IMPORTING
    error                = iref_error
  EXCEPTIONS
*     javabeannotsupported = 1
    OTHERS               = 2.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
 ENDIF.

CALL METHOD iref_control->get_document_proxy
  EXPORTING
    document_type  = soi_doctype_excel_sheet
  IMPORTING
    document_proxy = iref_document
    error          = iref_error.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
ENDIF.

"建立 Excel Document
CALL METHOD iref_document->create_document
  EXPORTING
    document_title = 'Excel'
    open_inplace   = 'X'
  IMPORTING
    error          = iref_error.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'I'.
ENDIF.

"建立 Excel Sheet
CALL METHOD iref_document->get_spreadsheet_interface
  EXPORTING
    no_flush        = ' '
  IMPORTING
    error           = iref_error
    sheet_interface = iref_spreadsheet.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
  LEAVE LIST-PROCESSING.
ENDIF.

"產生一個叫 TEST Range
CALL METHOD iref_spreadsheet->insert_range
  EXPORTING
    name    = 'TEST'
    rows    = L_ROW
    columns = 60        "此處的 60 決定於 ZSFIEXCEL 設定的 COLUMN
  IMPORTING
    error   = iref_error.
IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'I'.
ENDIF.

"設定這個範圍內的資料格式為字串
CALL METHOD iref_spreadsheet->set_format_string
  EXPORTING
    rangename = 'TEST'
    formatstring = '@'
  IMPORTING
    error = iref_error.

"把 Internal Table 寫入指定的 Range
CALL METHOD iref_spreadsheet->insert_one_table
  EXPORTING
    ddic_name  = 'ZSFIEXCEL'
    data_table = ITAB[]
    rangename  = 'TEST'
    wholetable = 'X'
  IMPORTING
    error      = iref_error.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'I'.
ENDIF.

"將 Excel 存檔
CALL METHOD iref_document->save_as
  EXPORTING
    file_name = FILENAME
  IMPORTING
    error     = iref_error.

IF iref_error->has_failed = 'X'.
  "CALL METHOD iref_error->raise_message
  "  EXPORTING
  "    type = 'I'.
  RAISE SAVE_EXCEPTION.
ENDIF.

CLEAR: i_range[].
CLEAR: wa_range.
wa_range-name = 'TEST'.
wa_range-rows = L_ROW.
wa_range-columns = 60.
APPEND wa_range to i_range.
"將之前建立的 Range 物件清除，要存檔後刪除
CALL METHOD iref_spreadsheet->delete_ranges
  EXPORTING
    NO_FLUSH = ''
    RANGES   = i_range
  IMPORTING
    error = iref_error.
IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
  LEAVE LIST-PROCESSING.
ENDIF.

CALL METHOD iref_document->close_document
  IMPORTING
    error = iref_error.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'I'.

  LEAVE LIST-PROCESSING.
ENDIF.

CALL METHOD iref_document->release_document
  IMPORTING
    error = iref_error.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'I'.
  LEAVE LIST-PROCESSING.
ENDIF.

最後輸出的檔案，格式為 Excel，且欄位的值設定為字串

讓 A2, B2, C2 是字串是在下面程式，設定變數在 L_ROW 是 2，產生一個叫 TEST Range，Range 範圍在 A1 ~ BH2 (columns = 60)

CALL METHOD iref_spreadsheet->insert_range
  EXPORTING
    name    = 'TEST'
    rows    = L_ROW
    columns = 60        "此處的 60 決定於 ZSFIEXCEL 設定的 COLUMN
  IMPORTING
    error   = iref_error.
將 TEST 的 Range 格式為字串 

CALL METHOD iref_spreadsheet->set_format_string
  EXPORTING
    rangename = 'TEST'
    formatstring = '@'
  IMPORTING
    error = iref_error.

使用 i_oi_spreadsheet->insert_range 有一個缺點，就是設定的 Range 一般都是從 A1 到你指定的位置全都設定為字串格式，有時只要換 COLUMN A & COLUMN C 換成字串，則不能使用 insert_rage 指定的情況，則可以使用 i_oi_spreadsheet->insert_range_dim 與 i_oi_spreadsheet->delete_range。


REPORT Z_DOI_EXCEL_FORMAT_STRING2.

DATA:
  oref_container   TYPE REF TO cl_gui_custom_container,
  iref_control     TYPE REF TO i_oi_container_control,
  iref_document    TYPE REF TO i_oi_document_proxy,
  iref_spreadsheet TYPE REF TO i_oi_spreadsheet,
  iref_error       TYPE REF TO i_oi_error.
DATA: BEGIN OF ITAB OCCURS 0.
        INCLUDE STRUCTURE ZSFIEXCEL.
DATA: END OF ITAB.
DATA: L_ROW TYPE I VALUE 2.
DATA: FILENAME TYPE CHAR256 VALUE 'D:\TEST.xlsx'.
DATA: wa_range TYPE soi_range_item,
      i_range TYPE soi_range_list.

"產生要輸出 Excel 的內容
ITAB-F01 = '00000111'.
ITAB-F02 = '00000222'.
ITAB-F03 = '00000333'.
APPEND ITAB. CLEAR ITAB.
ITAB-F01 = '00000111'.
ITAB-F02 = '00000222'.
ITAB-F03 = '00000333'.
APPEND ITAB. CLEAR ITAB.
ITAB-F01 = '00000111'.
ITAB-F02 = '00000222'.
ITAB-F03 = '00000333'.
APPEND ITAB. CLEAR ITAB.
ITAB-F01 = '00000111'.
ITAB-F02 = '00000222'.
ITAB-F03 = '00000333'.
APPEND ITAB. CLEAR ITAB.
ITAB-F01 = '00000111'.
ITAB-F02 = '00000222'.
ITAB-F03 = '00000333'.
APPEND ITAB. CLEAR ITAB.

"建立 DOI Container
CALL METHOD c_oi_container_control_creator=>get_container_control
  IMPORTING
    control = iref_control
    error   = iref_error.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
ENDIF.

CREATE OBJECT oref_container
  EXPORTING
    container_name              = 'CONT'
  EXCEPTIONS
    cntl_error                  = 1
    cntl_system_error           = 2
    create_error                = 3
    lifetime_error              = 4
    lifetime_dynpro_dynpro_link = 5
    OTHERS                      = 6.

IF sy-subrc <> 0.
  MESSAGE e001(00) WITH 'Error while creating container'.
ENDIF.

"建立 Excel 的 Container
CALL METHOD iref_control->init_control
  EXPORTING
    inplace_enabled      = 'X'
    r3_application_name  = 'EXCEL CONTAINER'
    parent               = oref_container
  IMPORTING
    error                = iref_error
  EXCEPTIONS
*     javabeannotsupported = 1
    OTHERS               = 2.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
 ENDIF.

CALL METHOD iref_control->get_document_proxy
  EXPORTING
    document_type  = soi_doctype_excel_sheet
  IMPORTING
    document_proxy = iref_document
    error          = iref_error.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
ENDIF.

"建立 Excel Document
CALL METHOD iref_document->create_document
  EXPORTING
    document_title = 'Excel'
    open_inplace   = 'X'
  IMPORTING
    error          = iref_error.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'I'.
ENDIF.

"建立 Excel Sheet
CALL METHOD iref_document->get_spreadsheet_interface
  EXPORTING
    no_flush        = ' '
  IMPORTING
    error           = iref_error
    sheet_interface = iref_spreadsheet.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
  LEAVE LIST-PROCESSING.
ENDIF.

"產生一個叫 TEST Range
call method iref_spreadsheet->insert_range_dim
  exporting
    name     = 'TEST'
    no_flush = ' '
    top      = 1
    left     = 1
    columns  = 1
    rows     = 5
  importing
    error    = iref_error.
IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
  LEAVE LIST-PROCESSING.
ENDIF.

"設定這個範圍內的資料格式為字串
CALL METHOD iref_spreadsheet->set_format_string
  EXPORTING
    rangename = 'TEST'
    formatstring = '@'
  IMPORTING
    error = iref_error.
IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
  LEAVE LIST-PROCESSING.
ENDIF.

"產生一個叫 TEST2 Range
call method iref_spreadsheet->insert_range_dim
  exporting
    name     = 'TEST2'
    no_flush = ' '
    top      = 1
    left     = 3
    columns  = 3
    rows     = 5
  importing
    error    = iref_error.
IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
  LEAVE LIST-PROCESSING.
ENDIF.

"設定這個範圍內的資料格式為字串
CALL METHOD iref_spreadsheet->set_format_string
  EXPORTING
    rangename = 'TEST2'
    formatstring = '@'
  IMPORTING
    error = iref_error.
IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
  LEAVE LIST-PROCESSING.
ENDIF.

"把 Internal Table 寫入指定的 Range
CALL METHOD iref_spreadsheet->insert_one_table
  EXPORTING
    ddic_name  = 'ZSFIEXCEL'
    data_table = ITAB[]
    rangename  = 'TEST'
    wholetable = 'X'
  IMPORTING
    error      = iref_error.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'I'.
ENDIF.

"將 Excel 存檔
CALL METHOD iref_document->save_as
  EXPORTING
    file_name = FILENAME
  IMPORTING
    error     = iref_error.

IF iref_error->has_failed = 'X'.
  "CALL METHOD iref_error->raise_message
  "  EXPORTING
  "    type = 'I'.
  RAISE SAVE_EXCEPTION.
ENDIF.

CLEAR: i_range[].
CLEAR: wa_range.
wa_range-name = 'TEST'.
APPEND wa_range to i_range.
wa_range-name = 'TEST2'.
APPEND wa_range to i_range.
"將之前建立的 Range 物件清除
CALL METHOD iref_spreadsheet->delete_content_ranges
  EXPORTING
    NO_FLUSH = ''
    RANGES   = i_range
  IMPORTING
    error = iref_error.
IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'E'.
  LEAVE LIST-PROCESSING.
ENDIF.

CALL METHOD iref_document->close_document
  IMPORTING
    error = iref_error.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'I'.

  LEAVE LIST-PROCESSING.
ENDIF.

CALL METHOD iref_document->release_document
  IMPORTING
    error = iref_error.

IF iref_error->has_failed = 'X'.
  CALL METHOD iref_error->raise_message
    EXPORTING
      type = 'I'.
  LEAVE LIST-PROCESSING.
ENDIF.

即可依 COLUMN 指定要轉換格式

於
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)

How to install & specified python version or distreibtuion package version in google colab

在買了 RTX 3080 要來 挖礦...  嗯~是跑機器學習後，結果發現了 GOOGLE COLAB，其實之前在「GAN 對抗式生成網路」一書就有看到，但資訊人就是什麼都想自己安裝，在本機用 Anaconda + pyCharm 弄了 GPU 環境，結果有天從新竹回家發現家裡沒...