How do i calculate subtotals for columns through abap2xlsx?

[Former Member]

Hi all,

I am using abap2xlsx for creating excel file and mailing it as an attachment.

I am using bind_table method in abap2xlsx to create attachment directly through internal table along with passing field catalog.

Now I want to show subtotals for amount column.

I calculated sub total in abap itself based on preferred conditions, but when I passed sum_total function in fieldcatalog total value displays along with subtotal which is incorrect.

I am also not able to highlight certain rows inside table based on conditions.

Is there a way to calculate subtotals from abap2xlsx ??

Where can I find more demos for abap2xlsx other than already provided?

Regards,

[Sandra_Rossi]

You say "fieldcatalog", if you mean you are exporting an ALV table to XLSX, then do the subtotals in the ALV and use a standard export class, everything will be done automatically.

PS: if it's not an ALV, then to answer your question about subtotals, why don't you calculate them in ABAP and store them in the XLSX, is that really a problem?

[Former Member]

Hi Sandra,

Thanks for your response.

I have actually calculated subtotals in abap itself.

I am not exporting ALV I am using BIND_TABLE method and passing internal table itself

Now the problem comes in this step ::

""it_Final is my internal table with subtotals calculated based on one column

lt_field_catalog = zcl_excel_common=>get_fieldcatalog( ip_table = it_final ).



  LOOP AT lt_field_catalog ASSIGNING <fs_field_catalog>.

*    ASSIGN COMPONENT <fs_field_catalog>-fieldname TO <fieldval>.

*    IF <fieldval> IS NOT INITIAL.

      CASE <fs_field_catalog>-fieldname.

        WHEN 'REGION'.

          <fs_field_catalog>-scrtext_l   = 'Region'.

          <fs_field_catalog>-scrtext_m   = 'Region'.



          <fs_field_catalog>-dynpfld    = abap_true.

          <fs_field_catalog>-style_header      = lo_style->get_guid( ).

        WHEN 'PREV_SALES'.

*        <fs_field_catalog>-scrtext_l   = f1.

          <fs_field_catalog>-scrtext_m   = f1.

          <fs_field_catalog>-dynpfld    = abap_true.

*        <fs_field_catalog>-style      = lo_style->get_guid( ).

*          <fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.

          <fs_field_catalog>-style_total      = lo_style->get_guid( ).

          <fs_field_catalog>-style_header      = lo_style->get_guid( ).

        WHEN 'PREV_MTD'.

*        <fs_field_catalog>-scrtext_l   = f2.

          <fs_field_catalog>-scrtext_m   = f2.

          <fs_field_catalog>-dynpfld    = abap_true.

*        <fs_field_catalog>-style      = lo_style_date->get_guid( ).

*          <fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.

          <fs_field_catalog>-style_header     = lo_style->get_guid( ).

          <fs_field_catalog>-style_total      = lo_style->get_guid( ).

        WHEN 'TARGET'.

*        <fs_field_catalog>-scrtext_l   = f3.

          <fs_field_catalog>-scrtext_m   = f3.

          <fs_field_catalog>-dynpfld    = abap_true.

*        <fs_field_catalog>-style      = lo_style_editable->get_guid( ).

*          <fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.

          <fs_field_catalog>-style_header      = lo_style->get_guid( ).

          <fs_field_catalog>-style_total      = lo_style->get_guid( ).

        WHEN 'MTD_SALES'.

          <fs_field_catalog>-scrtext_l   = f4.

          <fs_field_catalog>-scrtext_m   = f4.

          <fs_field_catalog>-dynpfld    = abap_true.

*        <fs_field_catalog>-style      = lo_style_editable->get_guid( ).

*          <fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.

          <fs_field_catalog>-style_header      = lo_style->get_guid( ).

          <fs_field_catalog>-style_total      = lo_style->get_guid( ).

        WHEN 'CURRENT_ACH'.

*        <fs_field_catalog>-scrtext_l   = f5.

          <fs_field_catalog>-scrtext_m   = f5.

          <fs_field_catalog>-dynpfld    = abap_true.

*        <fs_field_catalog>-style      = lo_style_editable->get_guid( ).

*          <fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.

          <fs_field_catalog>-style_header      = lo_style->get_guid( ).

          <fs_field_catalog>-style_total      = lo_style->get_guid( ).

        WHEN 'GROWTH_MTD'.

*        <fs_field_catalog>-scrtext_l   = f6.

          <fs_field_catalog>-scrtext_m   = f6.

          <fs_field_catalog>-dynpfld    = abap_true.

*        <fs_field_catalog>-style      = lo_style_editable->get_guid( ).

*          <fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.

          <fs_field_catalog>-style_header      = lo_style->get_guid( ).

          <fs_field_catalog>-style_total      = lo_style->get_guid( ).

        WHEN 'PREVDAY_SALES'.

*        <fs_field_catalog>-scrtext_l   = 'Previous Day Sales'.

          <fs_field_catalog>-scrtext_m   = 'Previous Day Sales'.

          <fs_field_catalog>-dynpfld    = abap_true.

*        <fs_field_catalog>-style      = lo_style_editable->get_guid( ).

*          <fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.

          <fs_field_catalog>-style_header      = lo_style->get_guid( ).

          <fs_field_catalog>-style_total      = lo_style->get_guid( ).

        WHEN 'PREVYEARSALES'.

*        <fs_field_catalog>-scrtext_l   = f7.

          <fs_field_catalog>-scrtext_m   = f7.

          <fs_field_catalog>-dynpfld    = abap_true.

*        <fs_field_catalog>-style      = lo_style_editable->get_guid( ).

*          <fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.

          <fs_field_catalog>-style_header      = lo_style->get_guid( ).

          <fs_field_catalog>-style_total      = lo_style->get_guid( ).

        WHEN 'CURRENTYEARSALES'.

*        <fs_field_catalog>-scrtext_l   = f8.

          <fs_field_catalog>-scrtext_m   = f8.

          <fs_field_catalog>-dynpfld    = abap_true.

*        <fs_field_catalog>-style      = lo_style_editable->get_guid( ).

*          <fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.

          <fs_field_catalog>-style_header      = lo_style->get_guid( ).

          <fs_field_catalog>-style_total      = lo_style->get_guid( ).

        WHEN 'GROWTH_YTD'.

*        <fs_field_catalog>-scrtext_l   = 'Growth YTD'.

          <fs_field_catalog>-scrtext_m   = 'Growth YTD'.

          <fs_field_catalog>-dynpfld    = abap_true.

*        <fs_field_catalog>-style      = lo_style_editable->get_guid( ).

*          <fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.

          <fs_field_catalog>-style_header      = lo_style->get_guid( ).

          <fs_field_catalog>-style_total      = lo_style->get_guid( ).

        WHEN OTHERS.

          <fs_field_catalog>-dynpfld = abap_false.

*        <fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.

      ENDCASE.

*    ELSE.

*    ENDIF.

  ENDLOOP.

in this section if I uncomment totals_function_sum in fieldcatalog,
it sums up all values in my internal table including subtotal that I manually calculated.

[VenkatRamesh_V]

Try,

data lv_lines type i.

lv_lines = lines( internal table ).

loop.

if lv_lines = sy-tabix.

continue.

endif.

endloop.

hope it helpful,

Regards,

Venkat.

[Sandra_Rossi]

It seems that you can't define subtotals with abap2xlsx, and you can't use pivot tables.

The only solution left is to calculate yourself the subtotals + totals (and let "totals_function" initial in the field catalog).

Or you may fill the table cell after cell if you want a beautiful rendering, and eventually use "formulas", but it's far more complex.

[Former Member]

Hi Sandra,

I actually calculated subtotal and grand total manually and created excel via set_cell method one by one.

Now I am getting desired results and getting more to learn in abap2xlsx.

Regards,