Skip to Content

How do i calculate subtotals for columns through abap2xlsx?

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,

Add comment
10|10000 characters needed characters exceeded

  • 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?

  • 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.
    
    
  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 12 at 02:45 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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,

  • Jan 12 at 08:22 AM
    -1

    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.

    Add comment
    10|10000 characters needed characters exceeded