SAP ABAP EXCEL export （ many sheet page ）

Preface

Sometimes we need to divide the data sheet Page export , and SAP The standard export function of cannot meet our needs

At this time, we need to develop our own export function

Code Overview

DATA: v_filename  TYPE string,
      wa_excel TYPE ole2_object,
      wa_book  TYPE ole2_object,
      wa_sheet TYPE ole2_object,
      wa_cell  TYPE ole2_object.

DATA: v_index       TYPE i VALUE IS INITIAL,
      v_sheet_index TYPE i VALUE IS INITIAL.

CALL METHOD cl_gui_frontend_services=>directory_browse
  CHANGING
    selected_folder = v_filename.

CONCATENATE v_filename '\ xxx surface ' '_' sy-datum '_' sy-uzeit
            '.xls'
INTO v_filename.

CREATE OBJECT wa_excel 'EXCEL.APPLICATION'.
SET PROPERTY OF wa_excel 'Visible' = 0.

CALL METHOD OF wa_excel 'Workbooks' = wa_book.
SET PROPERTY OF wa_excel 'SHEETSINNEWWORKBOOK' = 1.

CALL METHOD OF wa_book 'ADD'.

v_sheet_index = 1.

CALL METHOD OF wa_excel 'WORKSHEETS' = wa_sheet
  EXPORTING
    #1 = v_sheet_index.

CALL METHOD OF wa_sheet 'ACTIVATE'.
SET PROPERTY OF wa_sheet 'NAME' = 'sheetname'. 

CLEAR v_index.
LOOP AT it_fieldcat INTO wa_fieldcat. " EXCEL  Look up 
  v_index = v_index + 1.
  
  CALL METHOD OF wa_excel 'Cells' = wa_cell
  EXPORTING #1 = v_index #2 = sy-index.
  SET PROPERTY OF wa_cell 'Value' = wa_fieldcat-seltext_l.

ENDLOOP.

GET PROPERTY OF wa_excel 'ActiveWorkbook' = wa_book.
CALL METHOD OF wa_sheet 'SAVEAS'
  EXPORTING
    #1 = v_filename
    #2 = 1.
CALL METHOD OF wa_book 'CLOSE'.
CALL METHOD OF wa_excel 'QUIT'.

MESSAGE ' export  ' && v_filename && '  Completed ！ Please sprinkle flowers to celebrate ！' TYPE 'S'.

Code parsing

Variable definitions

DATA: v_filename  TYPE string,
      wa_excel TYPE ole2_object,
      wa_book  TYPE ole2_object,
      wa_sheet TYPE ole2_object,
      wa_cell  TYPE ole2_object.
DATA: v_index       TYPE i VALUE IS INITIAL,
      v_sheet_index TYPE i VALUE IS INITIAL.

establish Excel And Book Workbook object

The file name here can be defined at the end of the program

CREATE OBJECT wa_excel ‘EXCEL.APPLICATION’ Create a EXCEL object

CALL METHOD OF wa_excel ‘Workbooks’ = wa_book call EXCEL Object's Workbooks The method is to EXCEL Object to add a workbook object

CALL METHOD cl_gui_frontend_services=>directory_browse
  CHANGING
    selected_folder = v_filename.

CONCATENATE v_filename '\ xxx surface ' '_' sy-datum '_' sy-uzeit
            '.xls'
INTO v_filename.

CREATE OBJECT wa_excel 'EXCEL.APPLICATION'.
SET PROPERTY OF wa_excel 'Visible' = 0.

CALL METHOD OF wa_excel 'Workbooks' = wa_book.
SET PROPERTY OF wa_excel 'SHEETSINNEWWORKBOOK' = 1.

CALL METHOD OF wa_book 'ADD'.

newly build sheet Page and fill in the cell contents

ALL METHOD OF wa_excel ‘WORKSHEETS’ call EXCEL Of ‘WORKSHEETS’ Method to pass in parameters sheet The page number is added to the workbook sheet page

CALL METHOD OF wa_sheet ‘ACTIVATE’ Select the currently added sheet object

SET PROPERTY OF wa_sheet ‘NAME’ = ‘sheetname’ Modify the current sheet Page name

Here we cycle ALV A list of , Add the list names to our cell In cell

CALL METHOD OF wa_excel ‘Cells’ = wa_cell Pass in the row and column to get the specified cell object

SET PROPERTY OF wa_cell ‘Value’ = wa_fieldcat-seltext_l Set the properties of the cell object Value The value is our ALV List name

v_sheet_index = 1.

CALL METHOD OF wa_excel 'WORKSHEETS' = wa_sheet
  EXPORTING
    #1 = v_sheet_index.

CALL METHOD OF wa_sheet 'ACTIVATE'.
SET PROPERTY OF wa_sheet 'NAME' = 'sheetname'. 

CLEAR v_index.
LOOP AT it_fieldcat INTO wa_fieldcat. " EXCEL  Look up 
  v_index = v_index + 1.
  
  CALL METHOD OF wa_excel 'Cells' = wa_cell
EXPORTING #1 = v_index #2 = sy-index.
  SET PROPERTY OF wa_cell 'Value' = wa_fieldcat-seltext_l.

ENDLOOP.

Excel Save and export

CALL METHOD OF wa_sheet ‘SAVES’ call sheet Object's SAVES Method passes in the file name to save the file

Then close the workbook one by one and exit EXCEL process

GET PROPERTY OF wa_excel 'ActiveWorkbook' = wa_book.
CALL METHOD OF wa_sheet 'SAVEAS'
  EXPORTING
    #1 = v_filename
    #2 = 1.
CALL METHOD OF wa_book 'CLOSE'.
CALL METHOD OF wa_excel 'QUIT'.
MESSAGE ' export  ' && v_filename && '  Completed ！ Please sprinkle flowers to celebrate ！' TYPE 'S'.