SAP PP bom历史导出 ALV 及XLSX 带ECN号

bom总数 104W PS超过XLSX上限，那就分文件
*&---------------------------------------------------------------------*
*& Report ZRPT_PP_BOM_HIS_ECN
*&---------------------------------------------------------------------*
*& tcode:zpp023
*& 读取历史
*&---------------------------------------------------------------------*
REPORT ZRPT_PP_BOM_HIS_ECN.

TABLES:MAST,MARA,STPO,STAS.

SELECTION-SCREEN BEGIN OF BLOCK 001 WITH FRAME TITLE TEXT-001.
  SELECT-OPTIONS S_MATNR FOR MAST-MATNR.          "物料代码
  SELECT-OPTIONS S_WERKS FOR MAST-WERKS.          "工厂
  SELECT-OPTIONS S_MTART FOR MARA-MTART.          "物料类型
  SELECT-OPTIONS S_MATKL FOR MARA-MATKL.          "物料组
  SELECT-OPTIONS S_STLAN FOR MAST-STLAN.
  SELECT-OPTIONS S_STLAL FOR MAST-STLAL.

  PARAMETERS:PDATEV TYPE CSAP_MBOM-DATUV DEFAULT SY-DATUM.
  PARAMETERS:PDATEB TYPE CSAP_MBOM-DATUB DEFAULT SY-DATUM.

  PARAMETERS:RBALV RADIOBUTTON GROUP G1 DEFAULT 'X' USER-COMMAND U1,
             RBCSV RADIOBUTTON GROUP G1.
  PARAMETERS:PSHMSG AS CHECKBOX DEFAULT 'X'.
  PARAMETERS:PCNT TYPE I DEFAULT 1000000."Excel 100W上线
  PARAMETERS:POUTPATH TYPE RLGRAP-FILENAME.
SELECTION-SCREEN END OF BLOCK 001.

DATA: LV_FULL_PATH      TYPE STRING,
      LV_WORKDIR        TYPE STRING,
      LV_FILE_SEPARATOR TYPE C.

DATA: GT_FIELDCAT TYPE LVC_T_FCAT,
      GS_FIELDCAT TYPE LVC_S_FCAT,
      GS_LAYOUT   TYPE LVC_S_LAYO.

DATA: LO_EXCEL                  TYPE REF TO ZCL_EXCEL,
      LO_EXCEL_WRITER           TYPE REF TO ZIF_EXCEL_WRITER,
      LO_WORKSHEET              TYPE REF TO ZCL_EXCEL_WORKSHEET,
      LO_STYLE_BOLD_BORDER      TYPE REF TO ZCL_EXCEL_STYLE,
      LV_STYLE_BOLD_BORDER_GUID TYPE ZEXCEL_CELL_STYLE,

      LO_BORDER_DARK            TYPE REF TO ZCL_EXCEL_STYLE_BORDER.

DATA: LV_FILE      TYPE XSTRING,
      LV_BYTECOUNT TYPE I,
      LT_FILE_TAB  TYPE SOLIX_TAB.


DATA:BEGIN OF GT_TAB OCCURS 0,
       STLNR LIKE MAST-STLNR, "物料清单
       WERKS LIKE MAST-WERKS, "
       MATNR LIKE MAST-MATNR,
       IDNRK LIKE STPO-IDNRK,
       POSNR LIKE STPO-POSNR,
       SORTF LIKE STPO-SORTF, "排序字符串
       STLTY LIKE STPO-STLTY, " 物料清单类别
       MENGE LIKE STPO-MENGE,
       MEINS LIKE STPO-MEINS,
       POSTP LIKE STPO-POSTP, "项目类别
       DATUV LIKE STPO_API02-VALID_FROM, "有效起始日期
       STVKN LIKE STPO-STVKN, "BOM 项目节点号
       DATAB LIKE STPO_API02-VALID_FROM,
       AENNR LIKE STPO-AENNR,
       ECNTO LIKE STPO-AENNR,
       LKENZ LIKE STPO-LKENZ,
       ITMID LIKE STPO-ITMID, "项目ID
     END OF GT_TAB.


AT SELECTION-SCREEN ON VALUE-REQUEST FOR POUTPATH.
  LV_WORKDIR = POUTPATH.
  CL_GUI_FRONTEND_SERVICES=>DIRECTORY_BROWSE( EXPORTING INITIAL_FOLDER  = LV_WORKDIR
                                              CHANGING  SELECTED_FOLDER = LV_WORKDIR ).
  POUTPATH = LV_WORKDIR.

INITIALIZATION.

  CL_GUI_FRONTEND_SERVICES=>GET_DESKTOP_DIRECTORY( CHANGING DESKTOP_DIRECTORY = LV_WORKDIR ).
*  CL_GUI_FRONTEND_SERVICES=>GET_SAPGUI_WORKDIR( CHANGING SAPWORKDIR = LV_WORKDIR ).
  CL_GUI_CFW=>FLUSH( ).
  POUTPATH = LV_WORKDIR.

START-OF-SELECTION.
  DATA:BEGIN OF GT_MAST OCCURS 0,
         MATNR LIKE MAST-MATNR,
         WERKS LIKE MAST-WERKS,
         STLAN LIKE MAST-STLAN,
         STLAL LIKE MAST-STLAL,
       END OF GT_MAST.

  SELECT MAST~MATNR MAST~WERKS MAST~STLAN MAST~STLAL INTO TABLE GT_MAST
    FROM MAST JOIN MARA ON MARA~MATNR = MAST~MATNR
    WHERE MAST~MATNR IN S_MATNR
       AND MAST~WERKS IN S_WERKS
       AND MAST~STLAN IN S_STLAN
       AND MAST~STLAL IN S_STLAL.

  DATA:GT_STPO LIKE TABLE OF STPO_API02 WITH HEADER LINE.
  DATA:LL_TABIX(10),"循环标号
       LL_LINES(10),"行数
       LL_PECNT     TYPE P LENGTH 6 DECIMALS 2, "百分比
       LL_PECET(6),"百分数
       LL_TEXT(40)."消息

  DATA:LVSTR(50) TYPE C.

  DESCRIBE TABLE GT_MAST LINES LL_LINES.
  LOOP AT GT_MAST.
    IF PSHMSG = 'X'.
      LL_TABIX = SY-TABIX."当前处理的行号
      LL_PECNT = LL_TABIX / LL_LINES * 100."已处理百分比
      LL_PECET = LL_PECNT.
      "拼接显示消息
      CONCATENATE 'BOM展开已完成' LL_PECET '%,共' LL_TABIX '条/' LL_LINES '条' INTO LL_TEXT.
      CONDENSE LL_TEXT."整理格式，去掉多余空格，格式更整齐
      CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' "调用进度条函数，传入百分比和消息
        EXPORTING
          PERCENTAGE = LL_PECNT
          TEXT       = LL_TEXT.
    ENDIF.
    CLEAR GT_STPO[].
    CALL FUNCTION 'CSAP_MAT_BOM_READ'
      EXPORTING
        MATERIAL    = GT_MAST-MATNR
        PLANT       = GT_MAST-WERKS
        BOM_USAGE   = GT_MAST-STLAN
        ALTERNATIVE = GT_MAST-STLAL
        VALID_FROM  = PDATEV
        VALID_TO    = PDATEB
      TABLES
        T_STPO      = GT_STPO.
    LOOP AT GT_STPO.
      GT_TAB-STLNR = GT_STPO-BOM_NO. "物料清单
      GT_TAB-WERKS = GT_MAST-WERKS.", "
      GT_TAB-MATNR = GT_MAST-MATNR.
      GT_TAB-IDNRK = GT_STPO-COMPONENT.
      GT_TAB-POSNR = GT_STPO-ITEM_NO.
      GT_TAB-SORTF = GT_STPO-SORTSTRING. "排序字符串
      GT_TAB-STLTY =  GT_STPO-COMP_QTY." 物料清单类别
      LVSTR = GT_STPO-COMP_QTY.
      PERFORM RV_MARK_OF_THOUSANDTH(ZABAP_COMMON_PROGRAM) USING LVSTR CHANGING LVSTR.
      GT_TAB-MENGE = LVSTR.
      GT_TAB-MEINS = GT_STPO-COMP_UNIT.
      GT_TAB-POSTP = GT_STPO-ITEM_CATEG. "项目类别
      GT_TAB-DATUV = GT_STPO-VALID_FROM. "有效起始日期
      GT_TAB-STVKN = GT_STPO-ITEM_NODE . "BOM 项目节点号
      GT_TAB-DATAB = GT_STPO-VALID_TO.
      GT_TAB-AENNR = GT_STPO-CHANGE_NO.
      GT_TAB-ECNTO = GT_STPO-CHG_NO_TO.
      GT_TAB-LKENZ = GT_STPO-CHG_NO_TO.
      GT_TAB-ITMID = GT_STPO-ITM_IDENT."项目ID
      APPEND GT_TAB.
    ENDLOOP.
  ENDLOOP.

  IF RBALV =  'X'.
    PERFORM  DISPLAY_DATA.
  ELSE. "XLS
    DATA:FILENAME TYPE STRING.
    DATA:PROW TYPE I.
    DATA: LS_LINES(10),"行数
          LS_TABIX(10),"循环标号
          LS_PECNT     TYPE P LENGTH 6 DECIMALS 2, "百分比
          LS_PECET(6),"百分数
          LS_TEXT(40)."消息

    CREATE OBJECT LO_BORDER_DARK.
    LO_BORDER_DARK->BORDER_COLOR-RGB = ZCL_EXCEL_STYLE_COLOR=>C_BLACK.
    LO_BORDER_DARK->BORDER_STYLE = ZCL_EXCEL_STYLE_BORDER=>C_BORDER_THIN.

    DATA:CNT       TYPE I,
         NCNT      TYPE I,
         XLSROW    TYPE I,
         SHTIDX    TYPE I,
         SHTNM(31) TYPE C.
    DESCRIBE TABLE GT_TAB LINES NCNT.
    LL_LINES = NCNT.
    LOOP AT GT_TAB.
      PROW = SY-TABIX .
      IF PSHMSG = 'X'.
        LL_TABIX = SY-TABIX."当前处理的行号
        LL_PECNT = LL_TABIX / LL_LINES * 100."已处理百分比
        LL_PECET = LL_PECNT.
        "拼接显示消息
        CONCATENATE 'BOM展开已完成' LL_PECET '%,共' LL_TABIX '条/' LL_LINES '条' INTO LL_TEXT.
        CONDENSE LL_TEXT."整理格式，去掉多余空格，格式更整齐
        CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' "调用进度条函数，传入百分比和消息
          EXPORTING
            PERCENTAGE = LL_PECNT
            TEXT       = LL_TEXT.
      ENDIF.
      IF PROW = 1  OR ( PROW MOD PCNT ) = 1.
        CREATE OBJECT LO_EXCEL.
        CREATE OBJECT LO_EXCEL_WRITER TYPE ZCL_EXCEL_WRITER_2007.
        SHTNM = 'SHEET1'.
        LO_WORKSHEET = LO_EXCEL->GET_ACTIVE_WORKSHEET( ).
        LO_WORKSHEET->SET_TITLE( SHTNM ).

*  抬头剧中
        LO_STYLE_BOLD_BORDER = LO_EXCEL->ADD_NEW_STYLE( ).
        LO_STYLE_BOLD_BORDER->FONT->BOLD = ABAP_TRUE.
        LO_STYLE_BOLD_BORDER->FONT->ITALIC = ABAP_FALSE.
        LO_STYLE_BOLD_BORDER->FONT->COLOR-RGB = ZCL_EXCEL_STYLE_COLOR=>C_BLACK.
        LO_STYLE_BOLD_BORDER->ALIGNMENT->HORIZONTAL = ZCL_EXCEL_STYLE_ALIGNMENT=>C_HORIZONTAL_CENTER.
        LO_STYLE_BOLD_BORDER->BORDERS->ALLBORDERS = LO_BORDER_DARK.
        LV_STYLE_BOLD_BORDER_GUID = LO_STYLE_BOLD_BORDER->GET_GUID( ).

        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'A' IP_VALUE = 'BOM清单'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'B' IP_VALUE = '工厂'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'C' IP_VALUE = '物料'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'D' IP_VALUE = '组件'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'E' IP_VALUE = '排序字符串'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'F' IP_VALUE = '物料清单类别'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'G' IP_VALUE = '数量'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'H' IP_VALUE = '单位'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'I' IP_VALUE = '项目类别'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'J' IP_VALUE = '有效起始日期'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'K' IP_VALUE = '项目节点号'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'L' IP_VALUE = '有效截止日期'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'M' IP_VALUE = '变更号自'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'N' IP_VALUE = '变更号至'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'O' IP_VALUE = '删除标记'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'P' IP_VALUE = '外部项目ID'  ).
        LO_WORKSHEET->SET_CELL( IP_ROW = 1 IP_COLUMN = 'Q' IP_VALUE = '项目号'  ).
      ENDIF.
      XLSROW = PROW MOD PCNT  + 1.

      IF XLSROW = 1.
        XLSROW = PCNT + 1.
      ENDIF.

      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'A' IP_VALUE = GT_TAB-STLNR  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'B' IP_VALUE = GT_TAB-WERKS  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'C' IP_VALUE = GT_TAB-MATNR  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'D' IP_VALUE = GT_TAB-IDNRK  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'E' IP_VALUE = GT_TAB-SORTF  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'F' IP_VALUE = GT_TAB-STLTY  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'G' IP_VALUE = GT_TAB-MENGE  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'H' IP_VALUE = GT_TAB-MEINS  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'I' IP_VALUE = GT_TAB-POSTP  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'J' IP_VALUE = GT_TAB-DATUV  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'K' IP_VALUE = GT_TAB-STVKN  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'L' IP_VALUE = GT_TAB-DATAB  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'M' IP_VALUE = GT_TAB-AENNR  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'N' IP_VALUE = GT_TAB-ECNTO  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'O' IP_VALUE = GT_TAB-LKENZ  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'P' IP_VALUE = GT_TAB-ITMID  ).
      LO_WORKSHEET->SET_CELL( IP_ROW = XLSROW IP_COLUMN = 'Q' IP_VALUE = GT_TAB-POSNR  ).
      IF  XLSROW = PCNT + 1 OR PROW >= NCNT.
        LV_FILE = LO_EXCEL_WRITER->WRITE_FILE( LO_EXCEL ).
        " Convert to binary
        CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
          EXPORTING
            BUFFER        = LV_FILE
          IMPORTING
            OUTPUT_LENGTH = LV_BYTECOUNT
          TABLES
            BINARY_TAB    = LT_FILE_TAB.
        SHTIDX = CEIL( PROW / PCNT ) + 1.
        FILENAME = SY-TCODE && '/' && SHTIDX && '.xlsx'.
        IF POUTPATH IS INITIAL.
          POUTPATH = LV_WORKDIR.
        ENDIF.
        CL_GUI_FRONTEND_SERVICES=>GET_FILE_SEPARATOR( CHANGING FILE_SEPARATOR = LV_FILE_SEPARATOR ).
        CONCATENATE POUTPATH LV_FILE_SEPARATOR FILENAME INTO LV_FULL_PATH.

        " Save the file
        CL_GUI_FRONTEND_SERVICES=>GUI_DOWNLOAD( EXPORTING BIN_FILESIZE = LV_BYTECOUNT
                                                          FILENAME     = LV_FULL_PATH
                                                          FILETYPE     = 'BIN'
                                                CHANGING  DATA_TAB     = LT_FILE_TAB ).
        CLEAR:LV_BYTECOUNT,LO_WORKSHEET.
        WRITE:/ '已经写入文件' && LV_FULL_PATH && '的' && SHTNM.
      ENDIF.
    ENDLOOP.
  ENDIF.



FORM DISPLAY_DATA.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'STLNR' '' '' 'STPO' 'STLNR' '' '' 'BOM清单' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'WERKS' '' '' 'MARC' 'WERKS' '' '' '工厂' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'MATNR' '' '' 'MARC' 'MATNR' '' '' '物料' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'POSNR' '' '' 'STPO' 'POSNR' '' '' '项目号' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'IDNRK' '' '' 'STPO' 'IDNRK' '' '' '组件' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'SORTF' '' '' 'STPO' 'SORTF' '' '' '排序字符串' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'STLTY' '' '' 'STPO' 'STLTY' '' '' '物料清单类别' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'MENGE' '' '' 'STPO' 'MENGE' '' '' '数量' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'MEINS' '' '' 'STPO' 'MEINS' '' '' '单位' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'POSTP' '' '' 'STPO' 'POSTP' '' '' '项目类别' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'DATUV' '' '' 'STPO' 'DATUV' '' '' '有效起始日期' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'STVKN' '' '' 'STPO' 'STVKN' '' '' '项目节点号' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'DATAB' '' '' 'STPO' 'DATAB' '' '' '有效截止日期' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'AENNR' '' '' 'STPO' 'AENNR' '' '' '变更号自' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'ECNTO' '' '' 'STPO' 'ECNTO' '' '' '变更号至' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'LKENZ' '' '' 'STPO' 'LKENZ' '' '' '删除标记' 'X' 'X' CHANGING GT_FIELDCAT.
  PERFORM BUILD_FIELDCAT(ZRPT_MM_AQKC) USING 'ITMID' '' '' 'STPO' 'ITMID' '' '' '外部项目ID' 'X' 'X' CHANGING GT_FIELDCAT.

  GS_LAYOUT-CWIDTH_OPT = 'X'.
  GS_LAYOUT-ZEBRA = 'X'.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
    EXPORTING
      I_CALLBACK_PROGRAM      = SY-REPID
"     I_CALLBACK_PF_STATUS_SET = 'PF_STATUS_ALV'
      I_CALLBACK_USER_COMMAND = 'USER_COMMAND_ALV'
      IT_FIELDCAT_LVC         = GT_FIELDCAT
      IS_LAYOUT_LVC           = GS_LAYOUT
      I_DEFAULT               = 'X'
      I_SAVE                  = 'A'
    TABLES
      T_OUTTAB                = GT_TAB
    EXCEPTIONS
      OTHERS                  = 1.
ENDFORM.
分文件导出
利用SAP标准BAPI CSAP_MAT_BOM_READ
写于20241229 浙江台州