Excel file creation takes extremely long time

[TAD247]

Hi,

my intention is to schedule the Controlling period closing reports via background jobs and send the results as attachements in e-mails.

The excel creation for small result table works fine. But with larger results (5757 rows and 57-60 columns) i have massive problems. I have already tried to use ZCL_EXCEL_WRITER_2007 and also ZCL_EXCEL_WRITER_HUGE_FILE. I cancelled both background jobs after 60.000 seconds without any result.

When i run the report online the extraction ALV GRID to Excel only takes a view seconds.

This is my coding (see also URL: https://blogs.sap.com/2021/07/28/sending-spool-list-into-excel-format-through-email-attachment/, comment from Sandra Rossi July 28, 2021 at 5:49 pm)

"=================`
" EXTRACT ALV DATA
"=================
types TY_RANGE_AGR_NAME type range of AGR_NAME.
field-symbols <TABLE> type TABLE.

CL_SALV_BS_RUNTIME_INFO=>SET( DISPLAY  = ABAP_FALSE METADATA = ABAP_FALSE DATA = ABAP_TRUE ).
submit  SAPKKA00BG  using selection-set 'TEST' exporting list to memory
  and return.
CL_SALV_BS_RUNTIME_INFO=>GET_DATA_REF( importing R_DATA = data(LR_DATA) ).
assign LR_DATA->* to <TABLE>.


  "==================
  " CREATE EXCEL FILE
  "==================
  data(WORKBOOK) = new ZCL_EXCEL( ).
  data(WORKSHEET) = WORKBOOK->GET_ACTIVE_WORKSHEET( ).
  WORKSHEET->BIND_TABLE( IP_TABLE = <TABLE> ).
*  data(XLSX_GENERATOR) = cast ZIF_EXCEL_WRITER( new ZCL_EXCEL_WRITER_2007( ) ).
  data(XLSX_GENERATOR) = cast ZIF_EXCEL_WRITER( new ZCL_EXCEL_WRITER_HUGE_FILE( ) ).
  data(XLSX_DATA) = XLSX_GENERATOR->WRITE_FILE( WORKBOOK ).


  "=================
  " SEND EMAIL
  "=================
  data(EMAIL) = CL_DOCUMENT_BCS=>CREATE_DOCUMENT(
                    I_SUBJECT = 'CO Period-Closing-File' I_TYPE = 'TXT'
                    I_TEXT = value #( ( LINE = |See attachment| ) ) ).
  EMAIL->ADD_ATTACHMENT( I_ATTACHMENT_TYPE = 'XLS' I_ATTACHMENT_SUBJECT = 'Attachment'
                    I_ATTACHMENT_SIZE = |{ XSTRLEN( XLSX_DATA ) }|
                    I_ATT_CONTENT_HEX = CL_BCS_CONVERT=>XSTRING_TO_SOLIX( XLSX_DATA )
                    I_ATTACHMENT_HEADER = value #( ( LINE = |&SO_FILENAME=TEST.xlsx| ) ) ).

  data(BCS) = CL_BCS=>CREATE_PERSISTENT( ).
  BCS->SET_DOCUMENT( EMAIL ).
  BCS->ADD_RECIPIENT( I_RECIPIENT = CL_CAM_ADDRESS_BCS=>CREATE_INTERNET_ADDRESS( 'max.mustermann@mustermail.com' ) ).
  BCS->SET_SEND_IMMEDIATELY( ABAP_TRUE ). " prefer avoiding that, batch it instead
  BCS->SEND( ).
  commit work.

The attached file is an export from the result of report: SAPKKA00BG
EXPORT.XLSX

Do you have any advice for me?

Thank you very much.

Best regards,

Marc

[sandraros]

abap2xlsx is quite slow but more than 60.000 seconds for 5757 rows and 57-60 columns seems exaggerated for abap2xlsx alone.

First, make sure that you have cloned the latest version of abap2xlsx.

Secondly, do a test with abap2xlsx alone. The program below took 32 seconds in my case. Make sure you have at least 6000 rows in MARA, or choose any other big table.

SELECT * FROM mara UP TO 6000 ROWS INTO TABLE @DATA(table).
DATA(workbook) = NEW zcl_excel( ).
DATA(worksheet) = workbook->get_active_worksheet( ).
worksheet->bind_table( ip_table = table ).
DATA(xlsx_generator) = CAST zif_excel_writer( NEW zcl_excel_writer_huge_file( ) ).
DATA(xlsx_data) = xlsx_generator->write_file( workbook ).

Thank you.

[TAD247]

Hi Sandra,
thank you very much for your quick response.
I implemented the abap2xlsx last week, so i guess it is the latest version.
I have tested your code and it really took about 30 seconds.

But when i use table COSS instead of MARA (also with 6000 rows) - it takes much longer.

Table COSS is much more similar to my example and has a lot of CURR-typed columns. COSS has 166 columns and MARA has 231 columns in our system.

Example-Coding:

SELECT * FROM coss UP TO 6000 ROWS INTO TABLE @DATA(table).
DATA(workbook) = NEW zcl_excel( ).
DATA(worksheet) = workbook->get_active_worksheet( ).
worksheet->bind_table( ip_table = table ).
DATA(xlsx_generator) = CAST zif_excel_writer( NEW zcl_excel_writer_huge_file( ) ).
DATA(xlsx_data) = xlsx_generator->write_file( workbook ).

The program already took 1 hour and is still running.

Could you also try to reproduce this behaviour?

Thank you.

[sandraros]

Thank you very much, it doesn't end even with 1 row. There's a bug in method NORMALIZE_COLUMN_HEADING_TEXTS of ZCL_EXCEL_WORKSHEET which does an endless loop. I'll fix it quickly tonight.

1. First bug is wrong suffix added to columns with same text, like WTG001 to WTG016 with same texts Value TranCurr, WTG002 will have new text Value TranCurr 1, WTG003 will have new text Value TranCurr 1 2 instead of Value TranCurr 2, WTG004 will have new text Value TranCurr 1 2 3, etc.
2. Second bug is when the text has 39 or 40 characters, it gets into an endless loop, as it tries to find a unique text by adding a suffix which in fact is not added.

[sandraros]

Minimal program to reproduce the bug (endless loop during bind_table):

DATA(gc_save_file_name) = 'bug_1043.xlsx'.
INCLUDE zdemo_excel_outputopt_incl.

START-OF-SELECTION.
  DATA(workbook) = NEW zcl_excel( ).
  TYPES: BEGIN OF ty_excel_table_line,
           field_1  TYPE i,
           field_2  TYPE i,
           field_3  TYPE i,
         END OF ty_excel_table_line,
         ty_excel_table TYPE STANDARD TABLE OF ty_excel_table_line WITH EMPTY KEY.
  DATA(excel_table) = VALUE ty_excel_table( ).
  DATA(field_catalog) = zcl_excel_common=>get_fieldcatalog( ip_table = excel_table ).
  LOOP AT field_catalog ASSIGNING FIELD-SYMBOL(<field_catalog_line>).
    <field_catalog_line>-scrtext_l = 'Text Text Text Text Text Text Text Tex'.
  ENDLOOP.
  DATA(worksheet) = workbook->get_active_worksheet( ).
  worksheet->bind_table( ip_table          = excel_table
                         it_field_catalog  = field_catalog
                         is_table_settings = VALUE #( top_left_column = 'A' top_left_row = 1 )
                         iv_default_descr  = 'L' ).

[TAD247]

Hi Sandra,

thank you very much for your help, the new code fixed my issue.

Best regards,

Marc