Hi,
I am trying to convet following VB code into aspose cell using java.
Vb
ActiveWorkbook.Sheets(shtdata.Name).Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
please let us know what is the equaling Aspose api code. I could not find it from the document.
thanks
You may try to use Cells.getGroupedRowOutlineLevel() and Cells.getGroupedColumnOutlineLevel() attributes to know the outline level of the grouped rows and columns. You need to traverse through the cells ranges (in the worksheet) to hide, show or retrieve grouped rows and columns details. See the sample code just for your reference and write your own code after understanding it:
e.g.
Sample code:
Workbook book = new Workbook("e:\\test2\\Bk_groupedrowscols.xlsx");
Worksheet sheet = book.getWorksheets().get(0);
int maxRow = sheet.getCells().getMaxDataRow();
int maxCol = sheet.getCells().getMaxDataColumn();
for (int c = 0; c <= maxCol; c++)
{
int colOutlineLevel = sheet.getCells().getGroupedColumnOutlineLevel(c);
if (colOutlineLevel ==1)
{
//show grouped column details
sheet.getCells().showGroupDetail(false, c);
}
}
for (int r = 0; r <= maxRow; r++)
{
int rowOutlineLevel = sheet.getCells().getGroupedRowOutlineLevel(r);
if (rowOutlineLevel > 0)
{
//hide grouped rows details
sheet.getCells().hideGroupDetail(true, r);
}
}
Hope, this helps a bit.
Thanks very much for your help. I will try out.
I create several groups expanded by default and I would like to collapse some of them in a second hand.
Here is the idea, I tried variations on the level index for each condition following your solution, unfortunately I am not able to collapse groups automatically.
class TestClass(){
static Workbook workbook = null;
private static String workbookPath = "sampledatainsurance.xlsx";
public static void main(String[] args) {
//loading workbook
try {
workbook = new Workbook(new FileInputStream(workbookPath));
} catch (FileNotFoundException e) {
System.err.println("pkg.ExcelInterpreterAsposeTest.loadWorkbook"+ e.getMessage()+ e);
} catch (Exception e) {
System.err.println("pkg.ExcelInterpreterAsposeTest.loadWorkbook"+ e.getMessage()+ e);
}
setGroupLevels("GROUP_LEVELS","1","1");//supposed to collapse all
}
public static void setGroupLevels(String sheetName, String rowLevels, String columnLevels) {
try {
Worksheet sheet = workbook.getWorksheets().get(sheetName);
int maxRow = sheet.getCells().getMaxDataRow();
int maxCol = sheet.getCells().getMaxDataColumn();
int rowLevelsInt = Integer.parseInt(rowLevels);
int columnLevelsInt = Integer.parseInt(columnLevels);
for (int c = 0; c <= maxCol; c++) {
int colOutlineLevel = sheet.getCells().getGroupedColumnOutlineLevel(c);
if (colOutlineLevel >= columnLevelsInt){//>= instead of ==
//show grouped column details
sheet.getCells().showGroupDetail(false, c);
break;
} else {
}
}
for (int r = 0; r <= maxRow; r++){
int rowOutlineLevel = sheet.getCells().getGroupedRowOutlineLevel(r);
if (rowOutlineLevel >= rowLevelsInt){//>= instead of >
//hide grouped rows details
sheet.getCells().hideGroupDetail(true, r);
break;
}
}
} catch(Exception e) {
String parameters = " sheetName : "+sheetName+"\n"+
"rowLevels : "+rowLevels+"\n"+
"columnLevels : "+columnLevels;
System.err.println("excelinterpreter.ExcelInterpreter.setGroupLevels"+ e.getMessage()+parameters+ e);
}
}
}
Here is the workbook : sampledatainsuranceGroupLevels.zip (219.4 KB)
Please advise if any solution
I tested using the following sample code with your template Excel file. I found rows are collapsed fine but columns are not collapsed in the pivot table:
e.g.
Sample code:
//loading workbook
Workbook workbook = new Workbook(new FileInputStream("f:\\files\\sampledatainsurance.xlsx"));
//setGroupLevels("GROUP_LEVELS","1","1");//supposed to collapse all
Worksheet sheet = workbook.getWorksheets().get("GROUP_LEVELS");
sheet.refreshPivotTables();
int maxRow = sheet.getCells().getMaxDataRow();
int maxCol = sheet.getCells().getMaxDataColumn();
int rowLevelsInt = Integer.parseInt("1");
int columnLevelsInt = Integer.parseInt("1");
for (int c = 0; c <= maxCol; c++) {
int colOutlineLevel = sheet.getCells().getGroupedColumnOutlineLevel(c);
if (colOutlineLevel >= columnLevelsInt){//>= instead of ==
//show grouped column details
sheet.getCells().showGroupDetail(false, c);
break;
} else {
}
}
for (int r = 0; r <= maxRow; r++){
int rowOutlineLevel = sheet.getCells().getGroupedRowOutlineLevel(r);
if (rowOutlineLevel >= rowLevelsInt){//>= instead of >
//hide grouped rows details
sheet.getCells().hideGroupDetail(true, r);
break;
}
}
workbook.save("f:\\files\\out1.xlsx");
Please find attached the output Excel file for your reference. I am using the latest version/fix: Aspose.Cells for .NET v23.5. Could you share your expected file, we will check it further.
out1.zip (211.9 KB)
Yes, I got the same result as you. This is what I expect : sampledatainsuranceGroupLevelsExpected.zip (219.6 KB)
So collapsed on Level 1 for Rows and Columns
Thanks for the sample file.
I spotted an issue with your original code segment. Please try to change the line of code:
for (int c = 0; c <= maxCol; c++) {
int colOutlineLevel = sheet.getCells().getGroupedColumnOutlineLevel©;
if (colOutlineLevel >= columnLevelsInt){//>= instead of ==
sheet.getCells().showGroupDetail(false, c);
break;
}
with (see the line of code in bold):
for (int c = 0; c <= maxCol; c++) {
int colOutlineLevel = sheet.getCells().getGroupedColumnOutlineLevel©;
if (colOutlineLevel >= columnLevelsInt){//>= instead of ==
sheet.getCells().hideGroupDetail(false, c);
break;
}
I tested and it works as expected.
Let us know if you still find the issue.
You’re right that your correction works for this test :
setGroupLevels("GROUP_LEVELS","1","1");//supposed to collapse all
But I ran this other test :
setGroupLevels("GROUP_LEVELS","1","2");//supposed to collapse all rows and level 2 columns groups
And it did not return the correct result. I have 2 groups on level 2 of Columns. So running this command is supposed to collapse the two groups on level 2 and let the group on level 1 expanded. But only the first group of level 2 was collapsed
It is because of the “break” command, it must be removed in order to process several groups on the same level. So this works in my case :
for (int c = 0; c <= maxCol; c++) {
int colOutlineLevel = sheet.getCells().getGroupedColumnOutlineLevel©;
if (colOutlineLevel >= columnLevelsInt){//>= instead of ==
sheet.getCells().hideGroupDetail(false, c);
//break;
}
And I remove the “break” from rows group processing as well to be consistent, of course
Thank you for your help
Good to know that your issue is sorted out now. Feel free to write us back if you have further queries or comments, we will be happy to assist you soon.