Auto-sum in the selected regions

Posted by: anton-timofeev on 8 September 2017, 2:22 pm EST

  • Posted 8 September 2017, 2:22 pm EST

    Hi,

    I'm using Spread for Windows Forms 5.

    Is it possible to show the sum of all numeric values inside the selected ranges (like MS Excel does in the status bar)? I was trying to implement this by handling SelectionChanged/SelectionChanging events. It works in many cases but not always. When I start selecting a new range by holding the mouse button none of these two events trigger until I release the mouse button or move the mouse outside of the first cell in the new range. Basically to reproduce you simply need to place the cursor over a cell (or a column header) and press the mouse button (don't release it and don't move the mouse) - no events trigger and I can't update the sum.

     Any ideas?

  • Replied 8 September 2017, 2:22 pm EST

    Hello Anton,


    You can use Spread's CellClick event and EnterCell event as well which triggers as you will click on the Cell and then you can get the clicked cell indices as well.


    Hope this will help you. Thanks.

  • Replied 8 September 2017, 2:22 pm EST

    Thank you, I'm using EnterCell, additionally to the two other events and this seeems to be enough.

    Below is my final code in case someone needs it. It's pretty straightforward however I had to experiment with the "SelectionChanging" event since it does not commit changes to the "GetSelections" result.

     

    private void Spreadsheet_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
        UpdateTotal(null);
    }

    private void Spreadsheet_SelectionChanging(object sender, SelectionChangingEventArgs e)
    {
        UpdateTotal(e.Range);
    }

    private void Spreadsheet_EnterCell(object sender, EnterCellEventArgs e)
    {
        UpdateTotal(null);
    }

    private void UpdateTotal(CellRange changedRange)
    {
        try
        {
            int lastRow = _spreadsheet.ActiveSheet.GetLastNonEmptyRow(NonEmptyItemFlag.Data);
            int lastCol = _spreadsheet.ActiveSheet.GetLastNonEmptyColumn(NonEmptyItemFlag.Data);

            decimal res = 0m;
            bool changedRangeProcessed = changedRange == null;
            foreach (CellRange range in _spreadsheet.ActiveSheet.GetSelections())
            {
                if (changedRange != null && range.Row == changedRange.Row && range.Column == changedRange.Column)
                {
                    res += GetRangeTotal(changedRange, lastRow, lastCol);
                    changedRangeProcessed = true;
                }
                else
                {
                    res += GetRangeTotal(range, lastRow, lastCol);
                }
            }

            if (!changedRangeProcessed)
            {
                res += GetRangeTotal(changedRange, lastRow, lastCol);
            }

            _label_Total.Text = "Total: " + res;
        }
        catch
        {
            _label_Total.Text = "Total: " + "";
        }
    }

    private decimal GetRangeTotal(CellRange range, int lastRow, int lastCol)
    {
        decimal res = 0m;
        int rowStart = range.Row == -1 ? 0 : range.Row;
        int rowEnd = range.RowCount == -1 ? lastRow + 1 : range.Row + range.RowCount;

        for (int row = rowStart; row < rowEnd; row++)
        {
            int colStart = range.Column == -1 ? 0 : range.Column;
            int colEnd = range.ColumnCount == -1 ? lastCol + 1 : range.Column + range.ColumnCount;

            for (int col = colStart; col < colEnd; col++)
            {
                object val = _spreadsheet.ActiveSheet.Cells[row, col].Value;
                if (val is decimal)
                {
                    res += (decimal)val;
                }
            }
        }

        return res;
    }

  • Replied 8 September 2017, 2:22 pm EST

    The above code proved to be buggy, the new code is below. It works well in most cases but sometimes still produces incorrect results. This seems to happen when I select complex overlapping regions using the ctrl+mouse. I can't finish this up myself without understanding of the internal selection mechanics. Any help from the devs?

     

    private void Spreadsheet_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
        UpdateTotal(null);
    }

    private void Spreadsheet_SelectionChanging(object sender, SelectionChangingEventArgs e)
    {
        UpdateTotal(e.Range);
    }

    private void Spreadsheet_EnterCell(object sender, EnterCellEventArgs e)
    {
        UpdateTotal(null);
    }

    private void UpdateTotal(CellRange changedRange)
    {
        try
        {
            Dictionary visitedCells = new Dictionary();
            int lastRow = _spreadsheet.ActiveSheet.GetLastNonEmptyRow(NonEmptyItemFlag.Data);
            int lastCol = _spreadsheet.ActiveSheet.GetLastNonEmptyColumn(NonEmptyItemFlag.Data);

            decimal res = 0m;
            foreach (CellRange range in _spreadsheet.ActiveSheet.GetSelections())
            {
                if (changedRange != null && (changedRange.Contains(range) || range.Contains(changedRange)))
                {
                    continue;
                }
                res += GetRangeTotal(visitedCells, range, lastRow, lastCol);
            }

            if (changedRange != null)
            {
                res += GetRangeTotal(visitedCells, changedRange, lastRow, lastCol);
            }

            _label_Total.Text = "Total: " + res.ToString("N0");
        }
        catch
        {
            _label_Total.Text = "Total: " + "";
        }
    }

    private decimal GetRangeTotal(Dictionary visitedCells, CellRange range, int lastRow, int lastCol)
    {
        decimal res = 0m;
        int rowStart = range.Row == -1 ? 0 : range.Row;
        int rowEnd = range.RowCount == -1 ? lastRow + 1 : range.Row + range.RowCount;

        for (int row = rowStart; row < rowEnd; row++)
        {
            int colStart = range.Column == -1 ? 0 : range.Column;
            int colEnd = range.ColumnCount == -1 ? lastCol + 1 : range.Column + range.ColumnCount;

            for (int col = colStart; col < colEnd; col++)
            {
                if (visitedCells.ContainsKey(new Point(row, col)))
                {
                    continue;
                }
                visitedCells[new Point(row, col)] = true;
                object val = _spreadsheet.ActiveSheet.Cells[row, col].Value;
                if (val is decimal)
                {
                    res += (decimal)val;
                }
            }
        }

        return res;
    }

  • Replied 8 September 2017, 2:22 pm EST

    Hi,

    SheetView.GetSelections returns an ordered array of ranges with minimal overlapping, rather than the original order and actual ranges selected, using the IDisjointSelections.GetSelections implementation in DefaultSheetSelectionModel.  That is why you are seeing unexpected results from your code.  For more information, please refer to the documentation for GetSelections.

    You can get the actual ranges in their original order by enumerating them in the selection model directly with SheetView.Models.Selection.GetEnumerator.

  • Replied 8 September 2017, 2:22 pm EST

    Thanks, using SheetView.Models.Selection solved the problem. Updated loop for the UpdateTotal function below.

     

    for (int i = 0; i < _spreadsheet.ActiveSheet.Models.Selection.Count; i++)
    {
        if (changedRange != null && i == _spreadsheet.ActiveSheet.Models.Selection.Count - 1)
        {
            res += GetRangeTotal(visitedCells, changedRange, lastRow, lastCol);
            continue;
        }

        CellRange range = _spreadsheet.ActiveSheet.Models.SelectionIdea;
        res += GetRangeTotal(visitedCells, range, lastRow, lastCol);
    }

Need extra support?

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

Learn More

Forum Channels