Formulas in column footer

Posted by: ondrejs on 8 September 2017, 2:16 pm EST

  • Posted 8 September 2017, 2:16 pm EST

    Hello,


    Can I add formula to column footer? I decide to use formula SUBTOTAL in column footer instead of method SetAggregationType. But it doesn't work.


    Thank you very much


    Ondrej 


     

  • Replied 8 September 2017, 2:16 pm EST

    Hello,


    You can try using the last row of the Spread as ColumnFooter using FrozenTrailingRowCount property and use the SubTotal formula in that row. Code for the same is given below:



                fpSpread1.ActiveSheet.FrozenTrailingRowCount = 1;
                int num = fpSpread1.Sheets[0].RowCount - 1;
                fpSpread1.Sheets[0].Cells[fpSpread1.ActiveSheet.Rows.Count-1,1].Formula = "SUBTOTAL(1,B1:B" + num + ")";


    Hoe this will help you. Thanks

  • Replied 8 September 2017, 2:16 pm EST

    Hello,


    thank you for your answer. Your code works OK. But, I didn't say that I needed apply excel's formula SUBTOTAL(109, $B:$B). I need add summary row which will return summary of numeric columms. Total must ignore rows which will be hidden when I use filter


    I hope that I will use ColumnFooter and add the formula SUBTOTAL(109, ...) for columns with numeric values to the ColumnFooter property Formula.

  • Replied 8 September 2017, 2:16 pm EST

    Hello,


    Here is a sample which implements the similar kind of functonality using Sum formula on filtering the rows. However, you need to implement the similar kind of logic to implement the required behaviour.


    Hope it will help you. Thanks.


    2010/03/Copy of VBColumnFooter.zip
  • Replied 8 September 2017, 2:16 pm EST

    Hello,


    Thank you very much for you sample. I've implemented my solution based on sample. It runs, but the SheetView is rendered very SLOWLY, when I apply filter.


    For your idea I have two sheets on Win.Spread. Sheets have columns binded on .NET typed dataset. I use one DataTable for both sheets.  Sheets have 10 columns that can be filtered. These columns are binded in design. Numeric columns are generated dynamically in code. These column are binded too on the same DataTable. The first sheet contains 36 numeric columns and the second sheet contains  24 columns. Some columns of the second sheet are referenced to columns on the first sheet. For each numeric column I apply formula SUMIF based on you sample.


    Is there any way how to optimize performance?  I have idea apply SUM formula after filtering is finished. Is it possible?


    Thanks


    Ondrej

  • Replied 8 September 2017, 2:16 pm EST

    Hello Ondrej,


    Yes, you can apply Sum formula after filtering is finished on button click event but then formulas wont be automatically updated. However, this can be achievable by saving the e.FilterString value during AutoFilteredColumn event and then using it on button click event to apply the formula.


    Other performance improvement measures are :-


    1) If you are not using sticky notes, then you can set AutoUpdateNotes to false to prevent the component from checking for sticky notes that need to be made visible or hidden or moved.


    2) If you are using AllowCellOverflow, turning that off increases the performance of the layout calculations, because that feature requires lots of text width calculations on each change to the data in a cell.


    3) If you are using formulas, setting AutoCalculation to false before your updates and then setting it back to true and calling Recalculate afterwards eliminates redundant intermediate recalculations of your formulas.


    Hope this will help you. Thanks.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels