Regarding collapsing grouped rows and columns in workbook

Aspose.Cells Product Family
1

Hi,

I have a query regarding collapsing grouped rows and columns in excel sheet in C#. I know about HideGroupDetail method but that needs the index of row or column to be collapsed. But is there anyway to collapse all the groups of rows and columns present in a workbook all together at once? Also how can we set the position of the bottom scroll bar in a workbook. I am creating a workbook from a template and while saving I want to keep the bottom scroll to extreme left position.

Please clarify me on this.

2

Hi,

  1. Well, you have to loop through all the rows/cols to hide grouped rows/cols details in all the levels accordingly for your needs, there is no single API or option to do it. You may simply loop through your respective rows/cols and collapse/ hide the grouped details in row/cols levels accordingly.

  2. To set the position of the horizontal scrollbar, you may simply try to use WorkbookSettings.FirstVisibleTab attribute to accomplish the task for your needs.

Please see the sample code on how to do do 1) and 2) for your needs for your reference. I used a simple template file (attached) in the code segment, you may refer to it and may update/ change the code segments accordingly for your requirements:

e.g

Sample code:

Workbook book = new Workbook(“e:\test2\mygroupungroup.xlsx”);

Worksheet sheet = book.Worksheets[0];

//1)

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

{

int rowOutlineLevel = sheet.Cells.GetGroupedRowOutlineLevel(i);

//if (rowOutlineLevel == 2)//zero-based

//{

// sheet.Cells.HideRow(i);

//}

sheet.Cells.HideGroupDetail(true, i);

sheet.Cells.HideGroupDetail(false, i); //hide column wise group details

//To Show or expand the grouped rows.

//sheet.Cells.ShowGroupDetail(true, i);

}

//2)

book.Worksheets.ActiveSheetIndex = 0;

sheet.ActiveCell = “D20”;

sheet.FirstVisibleColumn = 0;

sheet.FirstVisibleRow = 0;

**book.Settings.FirstVisibleTab = 0;**

book.Save(“e:\test2\out1.xlsx”);

Hope, it helps you a bit.

Thank you.

3

Hi,

Thanks for the reply.

2)WorkbookSettings.FirstVisibleTab is not setting scroll bar to extreme left when the sheet has a vertical freeze pain. Anything that can be done in this case?\

Thank you

4

Hi,


2) Probably you not using/ setting a valid license at the start before using my code segment (see the lines of code below). Well, you need to have and set valid license before activating sheets in the workbook otherwise, the extra evaluation watermark sheet would always be active in the workbook.
e.g
Sample code:

Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(“Aspose.Cells.lic”);
…

Thank you.
5

Hi,

I understand what you are saying. It is not that. The issue is if there is a vertical freeze pain in the worksheet then the scroll position is not set. Otherwise it works fine.

6

Hi,


I think you may try to add the lines as well if it works for your needs (you may update the code segment accordingly as per your needs):
PaneCollection panes = workbook.Worksheets[0].GetPanes();
panes.FirstVisibleColumnOfRightPane = 0;
panes.FirstVisibleRowOfBottomPane = 9;
…
workbook.Settings.FirstVisibleTab = 0;

If you still have any issue, kindly update my attached template file to freeze some panes in the first worksheet and then attach the updated file here, we will check it soon.

Thank you.