How do I group rows or columns with FlexCel, so that a + Symbol is shown to the left or top?
How do I show details (expand a grouped row or column) with FlexCel, so that a - Symbol is shown to the left or top?
How do I hide details (collapse a grouped row or column) with FlexCel, so that a + Symbol is shown to the left or top?
Take a look at APIMate, it will show you the code. As always, create a file in Excel with the groups, and open it in APIMate and it will show you the correct calls.
Just for the record, the methods you search for have "outline" in their names. You can use CollapseOutlineRows/Cols to show or hide details, and SetRowOutlineLevel to create the groups.
There is also a "Subtotal" method that works like Excel->Data->Subtotal and automatically groups the data adding totals and subtotal rows.
The list of outline commands is below, but you should only need what is mentioned above.
/// <summary>
/// Returns the Outline level for a row.
/// </summary>
/// <param name="row">Row index (1 based)</param>
/// <returns>Outline level for a row. It is a number between 0 and 7.</returns>
public abstract int GetRowOutlineLevel(int row);
/// <summary>
/// Sets the Outline level for a row.
/// </summary>
/// <param name="row">Row index (1 based)</param>
/// <param name="level">Outline level. must be between 0 and 7.</param>
public void SetRowOutlineLevel(int row, int level)
/// <summary>
/// Sets the Outline level for a row range.
/// </summary>
/// <param name="firstRow">Row index of the first row on the range. (1 based)</param>
/// <param name="lastRow">Row index of the last row on the range. (1 based)</param>
/// <param name="level">Outline level. must be between 0 and 7.</param>
public abstract void SetRowOutlineLevel(int firstRow, int lastRow, int level);
/// <summary>
/// Returns the Outline level for a column.
/// </summary>
/// <param name="col">Column index (1 based)</param>
/// <returns>Outline level for a column. It is a number between 0 and 7.</returns>
public abstract int GetColOutlineLevel(int col);
/// <summary>
/// Sets the Outline level for a column.
/// </summary>
/// <param name="col">Column index (1 based)</param>
/// <param name="level">Outline level. must be between 0 and 7.</param>
public void SetColOutlineLevel(int col, int level)
/// <summary>
/// Sets the Outline level for a column range.
/// </summary>
/// <param name="firstCol">Column index of the first column on the range. (1 based)</param>
/// <param name="lastCol">Column index of the last column on the range. (1 based)</param>
/// <param name="level">Outline level. must be between 0 and 7.</param>
public abstract void SetColOutlineLevel(int firstCol, int lastCol, int level);
/// <summary>
/// Determines whether the summary rows should be below or above details on outline.
/// </summary>
public abstract bool OutlineSummaryRowsBelowDetail{get;set;}
/// <summary>
/// Determines whether the summary columns should be right to or left to the details on outline.
/// </summary>
public abstract bool OutlineSummaryColsRightToDetail{get;set;}
/// <summary>
/// This handles the setting of Automatic Styles inside the outline options.
/// </summary>
public abstract bool OutlineAutomaticStyles{get;set;}
/// <summary>
/// Collapses or expands the row outlines in a sheet to the specified level. It is equivalent to pressing the
/// numbers at the top of the outline gutter in Excel.
/// </summary>
/// <param name="level">Level that we want to show of the outline. (1 based).
/// For example, setting Level = 3 is the same as pressing the "3" number at the top of the outline gutter in Excel.
/// Setting Level = 1 will collapse all groups, Level = 8 will expand all groups.</param>
/// <param name="collapseChildren">Determines if the children of the collapsed nodes will be collapsed too.</param>
public void CollapseOutlineRows(int level, TCollapseChildrenMode collapseChildren)
/// <summary>
/// Collapses or expands the row outlines in a sheet to the specified level. It is equivalent to pressing the
/// numbers at the top of the outline gutter in Excel.
/// </summary>
/// <param name="level">Level that we want to show of the outline. (1 based).
/// For example, setting Level = 3 is the same as pressing the "3" number at the top of the outline gutter in Excel.
/// Setting Level = 1 will collapse all groups, Level = 8 will expand all groups.</param>
/// <param name="collapseChildren">Determines if the children of the collapsed nodes will be collapsed too.</param>
/// <param name="firstRow">This defines the first row of the range to collapse/expand. Only rows inside that range will be modified.</param>
/// <param name="lastRow">This defines the last row of the range to collapse/expand. Only rows inside that range will be modified.</param>
public abstract void CollapseOutlineRows(int level, TCollapseChildrenMode collapseChildren, int firstRow, int lastRow);
/// <summary>
/// Collapses or expands the column outlines in a sheet to the specified level. It is equivalent to pressing the
/// numbers at the left of the outline gutter in Excel.
/// </summary>
/// <param name="level">Level that we want to display from the outline. (1 based).
/// For example, setting Level = 3 is the same as pressing the "3" number at the left of the outline gutter in Excel.
/// Setting Level = 1 will collapse all groups, Level = 8 will expand all groups.</param>
/// <param name="collapseChildren">Determines if the children of the collapsed nodes will be collapsed too.</param>
public void CollapseOutlineCols(int level, TCollapseChildrenMode collapseChildren)
/// <summary>
/// Collapses or expands the column outlines in a sheet to the specified level. It is equivalent to pressing the
/// numbers at the top of the outline gutter in Excel.
/// </summary>
/// <param name="level">Level that we want to display from the outline. (1 based).
/// For example, setting Level = 3 is the same as pressing the "3" number at the left of the outline gutter in Excel.
/// Setting Level = 1 will collapse all groups, Level = 8 will expand all groups.</param>
/// <param name="collapseChildren">Determines if the children of the collapsed nodes will be collapsed too.</param>
/// <param name="firstCol">This defines the first column of the range to collapse/expand. Only columns inside that range will be modified.</param>
/// <param name="lastCol">This defines the last column of the range to collapse/expand. Only columns inside that range will be modified.</param>
public abstract void CollapseOutlineCols(int level, TCollapseChildrenMode collapseChildren, int firstCol, int lastCol);
/// <summary>
/// Returns true when the row is the one that is used for collapsing an outline. (it has a "+" at the left).
/// </summary>
/// <param name="row">Row to test (1 based)</param>
/// <returns>True if the node has a "+" mark.</returns>
public abstract bool IsOutlineNodeRow(int row);
/// <summary>
/// Returns true when the column is the one that is used for collapsing an outline. (it has a "+" at the top).
/// </summary>
/// <param name="col">Column to test (1 based)</param>
/// <returns>True if the node has a "+" mark.</returns>
public abstract bool IsOutlineNodeCol(int col);
/// <summary>
/// Returns true when the row is an outline node (it has a "+" at the left) and it is closed (all children are hidden).
/// </summary>
/// <param name="row">Row to test (1 based)</param>
/// <returns>True if the row contains a node and it is collapsed, false otherwise.</returns>
public abstract bool IsOutlineNodeCollapsedRow(int row);
/// <summary>
/// Returns true when the column is an outline node (it has a "+" at the top) and it is closed (all children are hidden).
/// </summary>
/// <param name="col">Column to test (1 based)</param>
/// <returns>True if the column contains a node and it is collapsed, false otherwise.</returns>
public abstract bool IsOutlineNodeCollapsedCol(int col);
/// <summary>
/// Use this method to collapse a node of the outline. If the row is not a node (<see cref="IsOutlineNodeRow"/> is false) this method does nothing.
/// While this method allows a better control of the rows expanded and collapsed, you will normally use <see cref="CollapseOutlineRows(int,TCollapseChildrenMode)"/> to collapse or
/// expand all rows in a sheet.
/// </summary>
/// <param name="row">Row to expand or collapse (1 based)</param>
/// <param name="collapse">If true, the node will be collapsed, else it will be expanded.</param>
public abstract void CollapseOutlineNodeRow(int row, bool collapse);
/// <summary>
/// Use this method to collapse a node of the outline. If the column is not a node (<see cref="IsOutlineNodeCol"/> is false) this method does nothing.
/// While this method allows a better control of the columns expanded and collapsed, you will normally use <see cref="CollapseOutlineCols(int,TCollapseChildrenMode)"/> to collapse or
/// expand all columns in a sheet.
/// </summary>
/// <param name="col">Column to expand or collapse. (1 based)</param>
/// <param name="collapse">If true, the node will be collapsed, else it will be expanded.</param>
public abstract void CollapseOutlineNodeCol(int col, bool collapse);
/// <summary>
/// Returns the Outline level for a row.
/// </summary>
/// <param name="row">Row index (1 based)</param>
/// <returns>Outline level for a row. It is a number between 0 and 7.</returns>
public abstract int GetRowOutlineLevel(int row);
/// <summary>
/// Sets the Outline level for a row.
/// </summary>
/// <param name="row">Row index (1 based)</param>
/// <param name="level">Outline level. must be between 0 and 7.</param>
public void SetRowOutlineLevel(int row, int level)
/// <summary>
/// Sets the Outline level for a row range.
/// </summary>
/// <param name="firstRow">Row index of the first row on the range. (1 based)</param>
/// <param name="lastRow">Row index of the last row on the range. (1 based)</param>
/// <param name="level">Outline level. must be between 0 and 7.</param>
public abstract void SetRowOutlineLevel(int firstRow, int lastRow, int level);
/// <summary>
/// Returns the Outline level for a column.
/// </summary>
/// <param name="col">Column index (1 based)</param>
/// <returns>Outline level for a column. It is a number between 0 and 7.</returns>
public abstract int GetColOutlineLevel(int col);
/// <summary>
/// Sets the Outline level for a column.
/// </summary>
/// <param name="col">Column index (1 based)</param>
/// <param name="level">Outline level. must be between 0 and 7.</param>
public void SetColOutlineLevel(int col, int level)
{
SetColOutlineLevel(col, col, level);
}
/// <summary>
/// Sets the Outline level for a column range.
/// </summary>
/// <param name="firstCol">Column index of the first column on the range. (1 based)</param>
/// <param name="lastCol">Column index of the last column on the range. (1 based)</param>
/// <param name="level">Outline level. must be between 0 and 7.</param>
public abstract void SetColOutlineLevel(int firstCol, int lastCol, int level);
/// <summary>
/// Determines whether the summary rows should be below or above details on outline.
/// </summary>
public abstract bool OutlineSummaryRowsBelowDetail{get;set;}
/// <summary>
/// Determines whether the summary columns should be right to or left to the details on outline.
/// </summary>
public abstract bool OutlineSummaryColsRightToDetail{get;set;}
/// <summary>
/// This handles the setting of Automatic Styles inside the outline options.
/// </summary>
public abstract bool OutlineAutomaticStyles{get;set;}
/// <summary>
/// Collapses or expands the row outlines in a sheet to the specified level. It is equivalent to pressing the
/// numbers at the top of the outline gutter in Excel.
/// </summary>
/// <param name="level">Level that we want to show of the outline. (1 based).
/// For example, setting Level = 3 is the same as pressing the "3" number at the top of the outline gutter in Excel.
/// Setting Level = 1 will collapse all groups, Level = 8 will expand all groups.</param>
/// <param name="collapseChildren">Determines if the children of the collapsed nodes will be collapsed too.</param>
public void CollapseOutlineRows(int level, TCollapseChildrenMode collapseChildren)
/// <summary>
/// Collapses or expands the row outlines in a sheet to the specified level. It is equivalent to pressing the
/// numbers at the top of the outline gutter in Excel.
/// </summary>
/// <param name="level">Level that we want to show of the outline. (1 based).
/// For example, setting Level = 3 is the same as pressing the "3" number at the top of the outline gutter in Excel.
/// Setting Level = 1 will collapse all groups, Level = 8 will expand all groups.</param>
/// <param name="collapseChildren">Determines if the children of the collapsed nodes will be collapsed too.</param>
/// <param name="firstRow">This defines the first row of the range to collapse/expand. Only rows inside that range will be modified.</param>
/// <param name="lastRow">This defines the last row of the range to collapse/expand. Only rows inside that range will be modified.</param>
public abstract void CollapseOutlineRows(int level, TCollapseChildrenMode collapseChildren, int firstRow, int lastRow);
/// <summary>
/// Collapses or expands the column outlines in a sheet to the specified level. It is equivalent to pressing the
/// numbers at the left of the outline gutter in Excel.
/// </summary>
/// <param name="level">Level that we want to display from the outline. (1 based).
/// For example, setting Level = 3 is the same as pressing the "3" number at the left of the outline gutter in Excel.
/// Setting Level = 1 will collapse all groups, Level = 8 will expand all groups.</param>
/// <param name="collapseChildren">Determines if the children of the collapsed nodes will be collapsed too.</param>
public void CollapseOutlineCols(int level, TCollapseChildrenMode collapseChildren)
/// <summary>
/// Collapses or expands the column outlines in a sheet to the specified level. It is equivalent to pressing the
/// numbers at the top of the outline gutter in Excel.
/// </summary>
/// <param name="level">Level that we want to display from the outline. (1 based).
/// For example, setting Level = 3 is the same as pressing the "3" number at the left of the outline gutter in Excel.
/// Setting Level = 1 will collapse all groups, Level = 8 will expand all groups.</param>
/// <param name="collapseChildren">Determines if the children of the collapsed nodes will be collapsed too.</param>
/// <param name="firstCol">This defines the first column of the range to collapse/expand. Only columns inside that range will be modified.</param>
/// <param name="lastCol">This defines the last column of the range to collapse/expand. Only columns inside that range will be modified.</param>
public abstract void CollapseOutlineCols(int level, TCollapseChildrenMode collapseChildren, int firstCol, int lastCol);
/// <summary>
/// Returns true when the row is the one that is used for collapsing an outline. (it has a "+" at the left).
/// </summary>
/// <param name="row">Row to test (1 based)</param>
/// <returns>True if the node has a "+" mark.</returns>
public abstract bool IsOutlineNodeRow(int row);
/// <summary>
/// Returns true when the column is the one that is used for collapsing an outline. (it has a "+" at the top).
/// </summary>
/// <param name="col">Column to test (1 based)</param>
/// <returns>True if the node has a "+" mark.</returns>
public abstract bool IsOutlineNodeCol(int col);
/// <summary>
/// Returns true when the row is an outline node (it has a "+" at the left) and it is closed (all children are hidden).
/// </summary>
/// <param name="row">Row to test (1 based)</param>
/// <returns>True if the row contains a node and it is collapsed, false otherwise.</returns>
public abstract bool IsOutlineNodeCollapsedRow(int row);
/// <summary>
/// Returns true when the column is an outline node (it has a "+" at the top) and it is closed (all children are hidden).
/// </summary>
/// <param name="col">Column to test (1 based)</param>
/// <returns>True if the column contains a node and it is collapsed, false otherwise.</returns>
public abstract bool IsOutlineNodeCollapsedCol(int col);
/// <summary>
/// Use this method to collapse a node of the outline. If the row is not a node (<see cref="IsOutlineNodeRow"/> is false) this method does nothing.
/// While this method allows a better control of the rows expanded and collapsed, you will normally use <see cref="CollapseOutlineRows(int,TCollapseChildrenMode)"/> to collapse or
/// expand all rows in a sheet.
/// </summary>
/// <param name="row">Row to expand or collapse (1 based)</param>
/// <param name="collapse">If true, the node will be collapsed, else it will be expanded.</param>
public abstract void CollapseOutlineNodeRow(int row, bool collapse);
/// <summary>
/// Use this method to collapse a node of the outline. If the column is not a node (<see cref="IsOutlineNodeCol"/> is false) this method does nothing.
/// While this method allows a better control of the columns expanded and collapsed, you will normally use <see cref="CollapseOutlineCols(int,TCollapseChildrenMode)"/> to collapse or
/// expand all columns in a sheet.
/// </summary>
/// <param name="col">Column to expand or collapse. (1 based)</param>
/// <param name="collapse">If true, the node will be collapsed, else it will be expanded.</param>
public abstract void CollapseOutlineNodeCol(int col, bool collapse);