Groupped rows and columns collection

Aspose.Cells Product Family
1

Hi.

I could not find any way to recieve a collection of groupped columns/rows on sheet. Is there some API for this?

Best regards. Alexey

2

@makarovalv,

You may try to use Cells.getGroupedRowOutlineLevel() and Cells.getGroupedColumnOutlineLevel() attributes to get outline level of the grouped rows and columns. You need to traverse through the cells ranges (in the worksheet) to retrieve the data in grouped rows and columns. See the sample code for your reference:
e.g.
Sample code:

        Workbook book = new Workbook("e:\\test2\\Bk_readgrouped.xlsx");
        Worksheet sheet = book.getWorksheets().get(0);

        int maxRow = sheet.getCells().getMaxDataRow();
        int maxCol = sheet.getCells().getMaxDataColumn();

        int chk = 0;
        boolean pname = false;

        System.out.println("Retrieving each group's data in grouped rows");

        for (int i = 0; i <= maxRow; i++)
        {

            int rowOutlineLevel = sheet.getCells().getGroupedRowOutlineLevel(i);
            if (rowOutlineLevel > 0)
            {

                pname = true;
                if (pname== true & chk != rowOutlineLevel)
                {
                    System.out.println("\n");
                    System.out.println("Group:" + rowOutlineLevel);
                    pname = false;
                    chk = rowOutlineLevel;
                }
                for (int j = 0; j <= maxCol; j++)
                {
         
                    System.out.println(sheet.getCells().get(i, j).getStringValue() + "\t");
                }
                System.out.println();
            }

        }

You may refer to the above code segment and write your own code accordingly for your underlying workbook for your needs.

Hope, this helps a bit.

3

Hi.

Thanks for quick answer. Ok. So is there some APIs to expand/collapse these groups?

Best regards. Alexey

4

@makarovalv,

Please refer to and try the following sample code with template file (attached) to accomplish your task. The sample code collapses all rows whose group level >1, i.e., inner grouped rows. The sample code is in .NET but you may easily convert to Java (if required)
e.g.
Sample code:

Workbook book = new Workbook("e:\\test2\\book.xlsx");
            Worksheet sheet = book.Worksheets[0];

            for (int i = 0; i <= sheet.Cells.MaxRow; i++)
            {

                int rowOutlineLevel = sheet.Cells.GetGroupedRowOutlineLevel(i);
                if (rowOutlineLevel > 1)
                {

                    sheet.Cells.HideGroupDetail(true, i);
                }
            }
            book.Save("e:\\test2\\out1.xlsx");

Also, find attached the output file for your reference. Moreover, please use the following sample code to expand/show the grouped details (rows).

//To Show or expand the grouped rows.
sheet.Cells.ShowGroupDetail(true, i);

Hope, this helps a bit.
files1.zip (18.4 KB)