Hierarchy Grid export to excel

Aspose.Cells Product Family
1

Hi! Aspose team,
I want to export Hierarchy grid to excel.
I am trying to achieve it by grouping the rows feature of aspose cells for java.
But for Parent in the hierarchy I need to give extra empty cell range to make it work properly in the excel i.e. expand and collapse feature.
But it should be the end of the cell where the data ends.
Any idea! How can I achieve this.

I have attached the snippet code for the issue.
Workbook workbook = new Workbook();

	// Accessing the first worksheet in the Excel file
	Worksheet worksheet = workbook.getWorksheets().get(0);
	Cells cells = worksheet.getCells();

	Cell cell1 = cells.get(0,0);
	cell1.setValue("Description");
	
	Cell cell2 = cells.get(1,0);
	cell2.setValue("Products");
	
	Cell cell3 = cells.get(2,0);
	cell3.setValue("Groceries");
	
	Cell cell4 = cells.get(3,0);
	cell4.setValue("Foods");
	
	Cell cell5 = cells.get(4,0);
	cell5.setValue("Fruits");
	
	Cell cell6 = cells.get(5,0);
	cell6.setValue("CATEGORY");
	
	Cell cell7 = cells.get(6,0);
	cell7.setValue("Apple");
	
	Cell cell8 = cells.get(7,0);
	cell8.setValue("A1 - Apple");
	
	Cell cell9 = cells.get(8,0);
	cell9.setValue("A2 - Apple");
	
	Cell cell10 = cells.get(9,0);
	cell10.setValue("A3 - Apple");
	
	Cell cell11 = cells.get(10,0);
	cell11.setValue("A4 - Apple");
	
	Cell cell12 = cells.get(11,0);
	cell12.setValue("A5 - Apple");
	
	// Grouping first six rows (from 0 to 5) and making them hidden by
	// passing true
	cells.groupRows(2, 16, true);
	cells.groupRows(3, 15, true);
	cells.groupRows(4, 14, true);
	cells.groupRows(5, 13, true);
	cells.groupRows(6, 12, true);
	cells.groupRows(7, 11, true);

	workbook.save(dataDir + "GroupingRows.xlsx");
2

@Rohan_Wankar,

Could you please create your desired hierarchy grid in MS Excel manually using grouping rows/cols feature, save the Excel file and provide us, we will check on how to do it via Aspose.Cells APIs.

3

Hi! Amjad
I have added manually created excel.
thanks!ManualHierarchySample.zip (7.5 KB)

4

@Rohan_Wankar,

Thanks for the sample file.

See the following sample code to accomplish your task. I opened your sample file and removed grouped outlines manually in MS Excel and saved it as “ManualHierarchySample_data1.xlsx”, this would be data file which I used in my code. I am using latest version of the API, i.e., Aspose.Cells for Java v22.7:
e.g.
Sample code:


         Workbook workbook = new Workbook("f:\\files\\ManualHierarchySample_data1.xlsx");

     	// Accessing the first worksheet in the Excel file
     	Worksheet worksheet = workbook.getWorksheets().get(0);
     	Cells cells = worksheet.getCells();

     	cells.groupRows(2, 9, true);
     	cells.groupRows(3, 5, true);
     	cells.groupRows(7, 9, true);
     	
     	workbook.save("f:\\files\\out1.xlsx");

Please find attached both files in the zipped archive for your reference.
files1.zip (15.2 KB)

5

Hi! @Amjad_Sahi
If you see my previous example, the expand and collapse button is coming from top in ManualHierarchy but for my previous sample it is coming from bottom.
Could help me in inversing the group keys in excel.
thanks!

6

@Rohan_Wankar,

Did you check the output file (“out1.xlsx”) in the zipped archive attached in the pervious post. While comparing it with “ManualHierarchySample.xlsx”, I could not notice any difference for expand/collapse buttons and when clicking them, they work as expected. Could you elaborate and provide more details and screenshots while comparing your sample file with “out1.xlsx”, so we could understand your needs and can look into it soon.