Customized grouping

Posted by: mkperumal on 8 September 2017, 3:20 pm EST

  • Posted 8 September 2017, 3:20 pm EST

    hi,

    I have gone through entire forum for grouping with particular column. i don't want to give user to select the column to group. i want to specified in the program itself while after load the data.

    Is there any sample available.. please..

    thanks and regards
    perumal
  • Replied 8 September 2017, 3:20 pm EST

    Hi Perumal,

    Yes, there is a sample which shows how to use the grouping APIs.
    Please refer to the Spread Windows Forms Sample Explorer in the Grouping demo:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using FarPoint.Win.Spread.Model;
    using FarPoint.Win.Spread;
    namespace SpreadWinDemo.Grouping
    {
    public partial class Grouping : Form
    {
    public Grouping()
    {
    InitializeComponent();
    txtGroupBarText.KeyUp += new KeyEventHandler(txtGroupBarText_KeyUp);
    txtGroupBarText.Leave += new EventHandler(txtGroupBarText_Leave);
    }
    void txtGroupBarText_Leave(object sender, EventArgs e)
    {
    if (txtGroupBarText.Text != null)
    {
    FpSpread1.ActiveSheet.GroupBarInfo.Text = txtGroupBarText.Text;
    }
    }
    void txtGroupBarText_KeyUp(object sender, KeyEventArgs e)
    {
    if ((e.KeyCode == Keys.Enter) & (txtGroupBarText.Text != null))
    {
    FpSpread1.ActiveSheet.GroupBarInfo.Text = txtGroupBarText.Text;
    }
    }
    private void Grouping_Load(object sender, EventArgs e)
    {
    FpSpread1.DataSource = Databinding.Databinding.GetNWINDData("Customers");
    FpSpread1.AllowColumnMove = true;
    FpSpread1.ScrollBarTrackPolicy = FarPoint.Win.Spread.ScrollBarTrackPolicy.Both;
    FpSpread1.Sheets[0].AllowGroup = true;
    FpSpread1.Sheets[0].GroupBarVisible = true;
    for (int intC = 0; intC < FpSpread1.Sheets[0].ColumnCount; intC++)
    FpSpread1.Sheets[0].Columns[intC].Width = FpSpread1.Sheets[0].GetPreferredColumnWidth(intC);
    FpSpread1.Sheets[0].DefaultStyle.BackColor = Color.AliceBlue;
    }
    private void btnGroup_Click(object sender, EventArgs e)
    {
    if ((FpSpread1.Sheets[0].Models.Data != null) && (FpSpread1.Sheets[0].Models.Data is GroupDataModel))
    {
    GroupDataModel gm = null;
    gm = FpSpread1.Sheets[0].Models.Data as GroupDataModel;
    if (gm.SortInfo.Length < FpSpread1.Sheets[0].GroupMaximumLevel)
    {
    int n = gm.SortInfo.Length;
    FarPoint.Win.Spread.SortInfo[] tmp = new SortInfo[n + 1];
    int j = 0;
    int i = 0;
    while (i < n)
    {
    if (gm.SortInfo[i].Index != FpSpread1.Sheets[0].GetModelColumnFromViewColumn(FpSpread1.Sheets[0].ActiveColumnIndex))
    tmp[j] = gm.SortInfo[i];
    else
    return;
    tmp[j] = new FarPoint.Win.Spread.SortInfo(FpSpread1.Sheets[0].GetModelColumnFromViewColumn(FpSpread1.Sheets[0].ActiveColumnIndex), true);
    tmp[j + 1] = gm.SortInfo[i];
    i = i + 1;
    j = j + 1;
    }
    tmp[j] = new FarPoint.Win.Spread.SortInfo(FpSpread1.Sheets[0].GetModelColumnFromViewColumn(FpSpread1.Sheets[0].ActiveColumnIndex), true);
    n = n + 1;
    if (n > 1)
    {
    GroupingEventArgs fe = new GroupingEventArgs(tmp);
    if (!fe.Cancel)
    {
    gm.Group(tmp, fe.GroupComparer);
    }
    }
    }
    }
    else
    {
    FarPoint.Win.Spread.Model.GroupDataModel gm = new FarPoint.Win.Spread.Model.GroupDataModel(FpSpread1.Sheets[0].Models.Data);
    FarPoint.Win.Spread.SortInfo[] si = new FarPoint.Win.Spread.SortInfo[] { new FarPoint.Win.Spread.SortInfo(FpSpread1.Sheets[0].ActiveColumnIndex, true) };
    gm.Group(si);
    try
    {
    FpSpread1.Sheets[0].Models.Data = gm;
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.Message);
    }
    }
    }
    private void btnRemove_Click(object sender, EventArgs e)
    {
    try
    {
    if ((FpSpread1.Sheets[0].Models.Data != null) && (FpSpread1.Sheets[0].Models.Data is GroupDataModel))
    {
    GroupDataModel gm = null;
    gm = FpSpread1.Sheets[0].Models.Data as GroupDataModel;
    int n = gm.SortInfo.Length;
    FarPoint.Win.Spread.SortInfo[] tmp = new FarPoint.Win.Spread.SortInfo[n - 1];
    int i = 0;
    while (i < n - 1)
    {
    tmp[i] = gm.SortInfo[i];
    i = i + 1;
    }
    n = n - 1;
    if (n >= 1)
    {
    GroupingEventArgs fe = new GroupingEventArgs(tmp);
    if (!fe.Cancel)
    gm.Group(tmp, fe.GroupComparer);
    return;
    }
    else
    FpSpread1.Sheets[0].Models.Data = ((FarPoint.Win.Spread.Model.GroupDataModel)FpSpread1.Sheets[0].Models.Data).TargetModel;
    }
    }
    catch
    {
    }
    }
    private void radioButtonNotSet_CheckedChanged(object sender, EventArgs e)
    {
    if (radioButtonNotSet.Checked == true)
    {
    radioButtonCollapseAll.Checked = false;
    radioButtonExpandAll.Checked = false;
    radioButtonExpandParent.Checked = false;
    FpSpread1.Sheets[0].GroupingPolicy = FarPoint.Win.Spread.Model.GroupingPolicy.NotSet;
    }
    }
    private void radioButtonExpandAll_CheckedChanged(object sender, EventArgs e)
    {
    if (radioButtonExpandAll.Checked == true)
    {
    radioButtonCollapseAll.Checked = false;
    radioButtonNotSet.Checked = false;
    radioButtonExpandParent.Checked = false;
    FpSpread1.Sheets[0].GroupingPolicy = FarPoint.Win.Spread.Model.GroupingPolicy.ExpandAll;
    }
    }
    private void radioButtonCollapseAll_CheckedChanged(object sender, EventArgs e)
    {
    if (radioButtonCollapseAll.Checked == true)
    {
    radioButtonExpandAll.Checked = false;
    radioButtonNotSet.Checked = false;
    radioButtonExpandParent.Checked = false;
    FpSpread1.Sheets[0].GroupingPolicy = FarPoint.Win.Spread.Model.GroupingPolicy.CollapseAll;
    }
    }
    private void radioButtonExpandParent_CheckedChanged(object sender, EventArgs e)
    {
    if (radioButtonExpandParent.Checked == true)
    {
    radioButtonExpandAll.Checked = false;
    radioButtonNotSet.Checked = false;
    radioButtonCollapseAll.Checked = false;
    FpSpread1.Sheets[0].GroupingPolicy = FarPoint.Win.Spread.Model.GroupingPolicy.ExpandParents;
    }
    }
    private void btnBackColor_Click(object sender, EventArgs e)
    {
    colorDialog1.Color = FpSpread1.ActiveSheet.GroupBarInfo.BackColor;
    colorDialog1.ShowDialog();
    if (!colorDialog1.Color.Equals(FpSpread1.ActiveSheet.GroupBarInfo.BackColor))
    {
    FpSpread1.ActiveSheet.GroupBarInfo.BackColor = colorDialog1.Color;
    FpSpread1.Refresh();
    }
    }
    private void numericUpDown1_ValueChanged(object sender, EventArgs e)
    {
    if (numericUpDown1.Value > 0)
    {
    FpSpread1.ActiveSheet.GroupBarInfo.Height = Convert.ToInt32(numericUpDown1.Value);
    }
    }
    private void btnForeColor_Click(object sender, EventArgs e)
    {
    colorDialog1.Color = FpSpread1.ActiveSheet.GroupBarInfo.ForeColor;
    colorDialog1.ShowDialog();
    if (!colorDialog1.Color.Equals(FpSpread1.ActiveSheet.GroupBarInfo.ForeColor))
    {
    FpSpread1.ActiveSheet.GroupBarInfo.ForeColor = colorDialog1.Color;
    FpSpread1.Refresh();
    }
    }
    }
    }


    The Samples Explorer is installed with the product, and also available here as a click-once application:
    http://spread.grapecity.com/demos/winforms/explorer/SampleExplorer.application

    Regards,
    Sean Lawyer
  • Replied 8 September 2017, 3:20 pm EST

    hi, Sean Lawyer

    thank you

  • Replied 8 September 2017, 3:20 pm EST

    hi,

    how to customize the group header.

    Thanks and regards
    perumal
  • Replied 8 September 2017, 3:20 pm EST

    Hello,

    There is no way to load multiple dataset in same SpreadSheet. However, you can try creating a view at database level and then bind Spread control with that view.

    Thanks,
    Reeva
  • Replied 8 September 2017, 3:20 pm EST

    Actually, you can bind multiple data sources in the same spreadsheet using SpreadDataBindingAdapter and cell range binding:
    http://sphelp.grapecity.com/WebHelp/SpreadNet8/WF/webframe.html#spwin-databind-range.html

    Using that feature, you can bind separate data sources to different cell ranges in the same sheet.

    But it might be better to use a data view as Reeva suggests, depending on the application requirements.
    Using sheet-level binding has many advantages over cell range binding, like hierarchy support.

    Regards,
    -Sean
  • Replied 8 September 2017, 3:20 pm EST

    Thanks Sean,

    I will go through and update the status.

    regards
    perumal
  • Replied 8 September 2017, 3:20 pm EST

    Hi ms. Reeva,

    all the links very help full.

    i have one problem, In the group footer i have assigned the sum formula.

    Example :
    ((FarPoint.Win.Spread.Model.IAggregationSupport)g1.GroupFooter.DataModel).SetCellAggregationType(0, 6, FarPoint.Win.Spread.Model.AggregationType.Sum);
    ((FarPoint.Win.Spread.Model.IAggregationSupport)g1.GroupFooter.DataModel).SetCellAggregationFormat(0, 6, "{0n2}");

    but the sum total horizontal alignment become left side.. i want to set it right side of the alignment.

    And i want to set total value 2 decimal value or 4 decimal value.. please let me know how to do this..

    thanks and regards
    perumal


  • Replied 8 September 2017, 3:20 pm EST

    Hello,

    As per my understanding you are planning to customize group bar. Hence, please go through these links for the same:-

    http://sphelp.grapecity.com/WebHelp/SpreadNet8/WF/webframe.html#spwin-group-barcustom.html
    http://sphelp.grapecity.com/2015/07/30/spread-windows-forms-and-grouping/
    http://sphelp.grapecity.com/forums/topic/how-to-display-column-header-not-colnum-on-group-rows/

    Hope it helps. Else please elaborate more on your issue as in what exactly you want to customize. Any image depicting the same would help us in better understanding of the issue.

    Thanks,
    Reeva
  • Replied 8 September 2017, 3:20 pm EST

    hi,

    Thanks for replay, i will update soon,

    Is there any way we can able to load multiple dataset in same grid and same sheet.

    Please let us know any feature available..

    thanks and regards
    perumal
  • Replied 8 September 2017, 3:20 pm EST

    Hello,

    Please go through the attached code for alignment of footer text and also to set number of deciaml places in footer text:-

     fpSpread2.Sheets[0].ColumnFooter.SetAggregationType(0, 1, FarPoint.Win.Spread.Model.AggregationType.Sum);
    fpSpread2.Sheets[0].ColumnFooter.Cells[0, 1].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Right;

    NumberCellType ct = new NumberCellType();
    ct.DecimalPlaces = 4;
    fpSpread2.Sheets[0].Cells[5, 0].CellType = ct;
    fpSpread2.Sheets[0].ColumnFooter.Cells[0, 1].CellType = ct;


    Hope it helps.

    Thanks,
    Reeva


Need extra support?

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

Learn More

Forum Channels