Hierarchical worksheet/report

mdelvecchio · October 23, 2007, 12:53pm

hello,

i have a question on something my users would like. they would like a “Parent-Child” hierarchical report that shows the cascading relationships of parts to each other. they would like this delievered to them in Excel, using exandable/collaspable rows (excel grouping via plus/minus buttons).

using Oracle, i have created an ordered resultset, making use of its built-in “CONNECT BY PRIOR a.PartID = a.ParentID” clause. works well, and provides me an ordered set of rows that looks like this:

Part_1
Child_1
Grandchild_1
Grandchild_2
GreatGrandchild_1
Child_2
Child_3
Part_2

…it also provides to me a “LEVEL” column that indicates the nested depth of the current row – “1” is a Part, “2” is a Child, “3” is a Grandchild, etc… a simplified version looks like so:

LEVEL PARTID NAME DESCRIPTION PARENTID
-------------------------------------------------------------------------------------------------
1 000001 test3 no parent
2 000002 test4 parent is test3 000001

now my question – using Cells, how can i create the expanding/collasping excel document? i see you have methods such as:

GroupRows(iStart, iEnd, bHidden)

…which is great for manually doing it. but is there a technique for automating this?

thanks!
matt

amjad.sahi · October 23, 2007, 2:36pm

Hi,

Thanks for considering Aspose.

Well, Normally we follow MS Excel standards. I think you may utilize Cells.GroupRows /GroupColumns methods to make a heirachical spreadsheet and mimic your sample. But this technique is manual as you may check in MS Excel too.

But if you want this for web presentation purpose, you may use our Aspose.Grid.Web control, please check the related demos:

github.com

aspose-cells/Aspose.Cells-for-.NET/blob/master/Examples_GridWeb/GridWeb.Net4/CSharp/Miscellaneous/DataBind/ExpandChildView.aspx.cs

using System;
using System.Data;
using Aspose.Cells.GridWeb.Data;
using Aspose.Cells.GridWeb.DemosCS.DataBind;

namespace Aspose.Cells.GridWeb.Examples.CSharp.Miscellaneous.DataBind
{
    public partial class ExpandChildView : System.Web.UI.Page
    {
        protected Aspose.Cells.GridWeb.DemosCS.DataBind.DataSet2 dataSet21;

        protected void Page_Load(object sender, EventArgs e)
        {
            // Put user code to initialize the page here
            if (!IsPostBack && !GridWeb1.IsPostBack)
            {
                BindWithoutInSheetHeaders();
            }
        }

This file has been truncated. show original

github.com

aspose-cells/Aspose.Cells-for-.NET/blob/master/Examples_GridWeb/GridWeb.Net4/CSharp/RowsAndColumns/GroupRows.aspx.cs

using System;
using System.Data;
using Aspose.Cells.GridWeb.Data;
using System.Data.OleDb;

namespace Aspose.Cells.GridWeb.Examples.CSharp.RowsAndColumns
{
    public partial class GroupRows : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnLoad_Click(object sender, EventArgs e)
        {
            GridWeb1.WorkSheets.Clear();

            // Connect database
            System.Data.OleDb.OleDbConnection oleDbConnection1 = new OleDbConnection();

This file has been truncated. show original

Thank you.

mdelvecchio · October 23, 2007, 4:33pm

Well, Normally we follow MS Excel standards.

what do you mean by that?

> But this technique is manual as you may check in MS Excel too.

not sure what you mean by this?

> But if you want this for web presentation purpose, you may use our Aspose.Grid.Web control

…it might be used for presentation, but our users prefer Excel as their report platform.

if it were going to use Aspose.Grid… i see one can setup multiple tables and create a relationship for them. but what about when there is only one table, w/ a self-referential ID, such as in my example data? that is, each row has a column that states whether it has a parentID, and what that ID is if it does. (this type of data & query is common in, say, HR databases…where theres only one table of all employees, and the hierarchy is represented by a ManagerID column that represents the unique ID of another row in the employee table, etc…)

thanks
matt

Laurence · October 23, 2007, 11:24pm

Hi Matt,

Aspose.Cells is a spreadsheet engine. So if you can create a file manually in MS Excel, Aspose.Cells can do it.

For your case, Aspose.Cells allows you to create reports according to your need but you have to use you own code to manage data and relationship.

mdelvecchio · October 24, 2007, 9:47am

> Aspose.Cells allows you
to create reports according to your need but you have to use you
> own
code to manage data and relationship.

i see. well if i may, it sure would be a sweet feather in the cap of Cells if it could do this sort of relationship management for me. Telerik has a tree-control that does just that – you give it the dataset, then tell it the relationship, and voilla, it builds the tree.

i realize that probably wasnt one of the things you had in mind when building Cells… but believe it or not, Excel is the preferred report delivery format for all of the engineers at our major oil & gas company. they dont like web reports, they like Excel. being able to deliver an exapanding/collasping hierarhical report in Excel would be a very nice feature indeed.

thanks,
matt

amjad.sahi · October 24, 2007, 12:24pm

Hi,

Thanks for considering Aspose.

Aspose.Cells is not a control rather a spreadheet generation and management library which has his own formula calculation engine, formatting ingrediences and other features same like MS Excel provide. We always try to implement all those features which MS Excel provides. For this feature, I think MS Excel don't provide this feasibility to directly generate a heirachical report. Still we will check if we can implement this feature.

Thank you.