Performance issue using zcl_excel_writer_2007

[MLDOliveira]

Hi.

I'm starting to use ABAP2XLSX and my first was great, but I'm trying to generate a new XLSX file with 300.000,00 lines and the process is going really slow.

I'm using the code from ZDEMO_EXCEL_OUTPUTOPT_INCL.

This is a summary that I'm using in my code.

CREATE OBJECT go_excel.
gs_table_settings-table_style = zcl_excel_table=>builtinstyle_medium2.
gs_table_settings-show_row_stripes = abap_true.
gs_table_settings-nofilters = abap_true.

"After fisrt declarations I fill the internal table and use bind method
go_worksheet = go_excel->get_active_worksheet( ).
go_worksheet->set_title( "TABLE1" ).
go_worksheet->bind_table( ip_table = it_001_saida10_exc
is_table_settings = gs_table_settings ).

"After that I basically use the code below.
lcl_output=>output( go_excel ).

"I'm doing a test right now using the ZCL_EXCEL_WRITER_HUGE_FILE class with the code below instead of code above

lcl_output=>output( cl_excel = go_excel
iv_writerclass_name = 'ZCL_EXCEL_WRITER_HUGE_FILE' ).

My question is, there is a better way to work with huge files? What is the right way to export big files with many tabs?

@ivanfemia @larshp could you help me?

[ivanfemia]

Hi, big files can have performances issue, I usually consider a batch process for those. Btw the class ZCL_EXCEL_WRITER_HUGE_FILE is the one ​ ​ that I ​ use ​ ​ at the moment ​ ​ in case of big files. If anyone has a better idea to improve this process, I'm available to discuss. Ivan

…

On Fri, Mar 16, 2018 at 12:49 PM, Mateus Oliveira ***@***.***> wrote: Hi. I'm starting to use ABAP2XLSX and my first was great, but I'm trying to generate a new XLSX file with 300.000,00 lines and the process is going really slow. I'm using the code from ZDEMO_EXCEL_OUTPUTOPT_INCL. This is a summary that I'm using in my code. CREATE OBJECT go_excel. gs_table_settings-table_style = zcl_excel_table=>builtinstyle_medium2. gs_table_settings-show_row_stripes = abap_true. gs_table_settings-nofilters = abap_true. "After fisrt declarations I fill the internal table and use bind method go_worksheet = go_excel->get_active_worksheet( ). go_worksheet->set_title( "TABLE1" ). go_worksheet->bind_table( ip_table = it_001_saida10_exc is_table_settings = gs_table_settings ). "After that I basically use the code below. lcl_output=>output( go_excel ). "I'm doing a test right now using the ZCL_EXCEL_WRITER_HUGE_FILE class with the code below instead of code above lcl_output=>output( cl_excel = go_excel iv_writerclass_name = 'ZCL_EXCEL_WRITER_HUGE_FILE' ). My question is, there is a better way to work with huge files? What is the right way to export big files with many tabs? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <https://github.com/ivanfemia/abap2xlsx/issues/528>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ADC72WC_qZymzOrypinqOwHjQ1sPd6C_ks5te_tCgaJpZM4SuMb5> .

[MLDOliveira]

@ivanfemia do you have an example of using the class ZCL_EXCEL_WRITER_HUGE_FILE?
I'm trying to use it but I'm not moving forward

[ivanfemia]

Just use that class instead of the classical writer. It has some limitation on the features available

…

On Fri, Mar 16, 2018 at 3:27 PM, Mateus Oliveira ***@***.***> wrote: @ivanfemia <https://github.com/ivanfemia> do you have an example of using the class ZCL_EXCEL_WRITER_HUGE_FILE? I'm trying to use it but I'm not moving forward — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://github.com/ivanfemia/abap2xlsx/issues/528#issuecomment-373835484>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ADC72WwIuL7SwSGoHMSrfbMARfcDmck3ks5tfCBBgaJpZM4SuMb5> .

[MLDOliveira]

@ivanfemia the problem is when I use the code below I get a erro message saing that "the method CREATE_XL_SHEET is protected"

CREATE OBJECT cl_writer_huge TYPE zcl_excel_writer_huge_file.
cl_output->xdata = cl_writer_huge->create_xl_sheet( cl_excel ).

[ivanfemia]

Huge file writer doesn’t support multi sheets; there are some limitations for using that.

…

On Mar 19, 2018, at 6:18 AM, Mateus Oliveira ***@***.***> wrote: @ivanfemia the problem is when I use the code below I get a erro message saing that "the method CREATE_XL_SHEET is protected" CREATE OBJECT cl_writer_huge TYPE zcl_excel_writer_huge_file. cl_output->xdata = cl_writer_huge->create_xl_sheet( cl_excel ). — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[Pacojka]

Hallo Ivan,

i am facing the same problem.
The main performance issue comes from the getter methods (e.g. ZCL_EXCEL_WORKSHEET->GET_ROW) searching for the right object using an object collection (while/has_next, get_next, if statement).

One quick approach would be to use the object collection and a Hash Table simultaneously. If an object is added to the collection, we add it to the hash table (e.g. key row_id and a reference to the row object). If we need to iterate over all rows, we can still use the object collection. But if we know which object we need, we get it from the hash table with a simple read.

In MLDOliveira´s case, this saves up to 299.999 iterations while calling ZCL_EXCEL_WORKSHEET ->get_row( 300000 ) once. The performance gain while working with big tables should be huge.

I try to find some time to contribute in this great project.

Patrick

[ivanfemia]

Hi there was a previous commit that solved that issue, but it was reverted for a syntax error. You can start from that commitment, it would be really great! Ivan

…

On Jun 28, 2018, at 9:11 AM, Pacojka ***@***.***> wrote: Hallo Ivan, i am facing the same problem. The main performance issue comes from the getter methods (e.g. ZCL_EXCEL_WORKSHEET->GET_ROW) searching for the right object using an object collection (while/has_next, get_next, if statement). One quick approach would be to use the object collection and a Hash Table simultaneously. If an object is added to the collection, we add it to the hash table (e.g. key row_id and a reference to the row object). If we need to iterate over all rows, we can still use the object collection. But if we know which object we need, we get it from the hash table with a simple read. In MLDOliveira´s case, this saves up to 299.999 iterations while calling ZCL_EXCEL_WORKSHEET ->get_row( 300000 ) once. The performance gain while working with big tables should be huge. I try to find some time to contribute in this great project. Patrick — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[larshp]

Guess the PR is https://github.com/ivanfemia/abap2xlsx/pull/532

[ivanfemia]

Correct... I was on my iPhone and couldn't check. Thank Lars

…

On Thu, Jun 28, 2018 at 8:44 AM Lars Hvam ***@***.***> wrote: Guess the PR is #532 <https://github.com/ivanfemia/abap2xlsx/pull/532> — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://github.com/ivanfemia/abap2xlsx/issues/528#issuecomment-401039596>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ADC72TVf_DiYXxepOEDy3lVeCcW4Rzayks5uBN25gaJpZM4SuMb5> .

[larshp]

guess this issue can be closed?

[MLDOliveira]

I didn't test after the merge on July, but I read the comments and apparenty the problem was solved.

[sandraros]

Let's close the issue then.